Accessing OS 400 Specific Objects

OS/400 is quite different from Unix. One difference is that OS/400 is an object-based operating system, whereas to Unix, everything is a file. For example, Unix-based systems provide message queues or memory-mapped files. OS/400 provides the same APIs for message queues and memory-mapped files, but it also provides a broad and robust set of OS/400-specific objects that can be used for similar tasks . The OS/400 data-queue and data-area objects have long provided similar services to those provided by Unix-based message-queue and memory-mapped files.

Qshell includes non-Unix utilities that allow it to access OS/400-specific objects like the data queue and the data area. This chapter serves two functions: to make you aware of the Qshell interfaces to OS/400 objects, and to serve as a reference to those utilities.

System

The system utility allows you to run a CL command within Qshell. Because Qshell executes commands in batch mode, the CL command must be able to run under the QCMDEXC API in batch mode. To determine whether or not a CL command meets this criterion, use CL's Display Command (DSPCMD) command and look for the values *BATCH and * EXEC in the "Where allowed to run" description. To enable a user -written command to run under the system utility, specify ALLOW(*BATCH *EXEC) in the CL command Create Command (CRTCMD) or Change Command (CHGCMD).

In Figure 20.1, a user tries to start the Programming Development Manager (PDM) within Qshell. However, PDM is an interactive application, so system fails, and error messages are sent to stderr. Notice that the n option can be used to suppress the message ID of the error.

system strpdm CPD0031: Command STRPDM not allowed in this setting. CPF0001: Error found on STRPDM command. /home/jsmith $ system -n strpdm Command STRPDM not allowed in this setting. Error found on STRPDM command. /home/jsmith $

Figure 20.1: Only batch commands can run under the system utility.

By default, the system utility copies spooled files to stdout and deletes them. Error messages are written to stderr. Like any other Qshell utility, this behavior makes the system utility suitable for use with Qshell filters, command substitution, and redirection. The system utility, then, serves as an ideal mechanism to incorporate not only CL commands into Qshell, but your existing applications, as well.

Figure 20.2 shows the Display Database Relations (DSPDBR) command run under Qshell. When DSPDBR runs in a batch job, the output is written to a spooled file. For that reason, the output in Figure 20.2 is sent to stdout.

system dspdbr jsmith/customer 5722SS1 V5R2M0 020719 Display Data Base Relations DSPDBR Command Input File . . . . . . . . . . . . . . . . . . . : FILE CUSTOMER Library . . . . . . . . . . . . . . . . . : JSMITH Member . . . . . . . . . . . . . . . . . . : MBR *NONE Record format . . . . . . . . . . . . . . . : RCDFMT *NONE Output . . . . . . . . . . . . . . . . . . : OUTPUT * Specifications Type of file . . . . . . . . . . . . . . . : Physical File . . . . . . . . . . . . . . . . . . . : CUSTOMER Library . . . . . . . . . . . . . . . . . : JSMITH Member . . . . . . . . . . . . . . . . . : *NONE Record format . . . . . . . . . . . . . . : *NONE Number of dependent files . . . . . . . . : 3 Files Dependent On Specified File Dependent File Library Dependency JREF Constraint CUSTOMER2 JSMITH Data CUSTOMER1A JSMITH Data CUSTOMER3 JSMITH Data

Figure 20.2: The output from a CL command is written to stdout.

Routing spooled data is not limited to the execution of IBM-supplied commands. This behavior occurs for any source of spooled output. For example, Figure 20.3 contains the DDS for an externally described printer file that defines a listing of customers.

A REF(CUSTOMER) A A R H1 SKIPB(1) A H1REPORTID 10 1 A 24'Customer List' A 52DATE EDTWRD(' - - ') A SPACEA(2) A 7'Cmp' A +3'Nbr' A +1'Name' A +17'City' A +13'State' A SPACEA(1) A 7'===' A +1'=====' A +1'====================' A +1'================' A +1'=====' A A R D1 SPACEB(1) A D1COUNT 5 0 1EDTCDE(4) A D1COMPANY R +1REFFLD(COMPANY) A D1CUSTNBR R +1REFFLD(CUSTNBR) A D1CUSTNAMER +1REFFLD(CUSTNAME) A D1CITY R +1REFFLD(CCITY) A D1STATE R +1REFFLD(CSTATE) A A R T1 SPACEB(2) A 1'** End of report **'

Figure 20.3: The source member CUSLISTP defines a report.

Figure 20.4 contains the RPG program that produces the customer-listing report.

Fcustomer if e k disk Fcuslistp o e printer oflind(Overflow) D forever s n inz(*on) D Overflow s n D PSDS sds D ProcName *proc /free h1ReportID = ProcName; write h1; dow forever; read custrec; if %eof; leave; endif; d1count += 1; d1company = company; d1custnbr = custnbr; d1custname = custname; d1city = ccity; d1state = cstate; if OverFlow; write h1; Overflow = *off; endif; write d1; enddo; write t1; *inlr = *on; /end-free

Figure 20.4: Program CUSLIST1 builds a report.

In Figure 20.5, system is used to run the CUSLIST1 program from Figure 20.4. Notice that printed output is sent to stdout.

system call cuslist1 CUSLIST1 Customer List 12-25-03 Cmp Nbr Name City State === ===== ==================== ================ ===== 1 001 00044 Joe's Shoes Duluthe GA 2 001 10001 Sue's Bridle Shop Saltillo MS 3 001 20002 Bank of Steele Medina PA 4 001 30003 Snowman Heat & Air Lexington OH 5 002 00001 Xolomon Solutions Tulsa OK 6 002 00003 Donaldson Electric Whittier CA 7 002 00345 Gretta's Gifts Saddle Brook NJ 8 003 00001 Ames Wholesale Tupelo MS 9 003 00003 Army Surplus Brunswick OH 10 003 00056 Jak's Liver Emporium Mexico OH 11 004 40004 Grayson Paul Little City ND 12 007 00222 Sardine Paradise Happah Palloolah CA 13 007 00777 Pretty Boy's Gym Lost Angeles NY 14 008 00001 48% of Nothing Klondike FL 15 008 00002 Robert R. Roberts IV New Yolk CA 16 008 00003 Sal Monella ** End of report **

Figure 20.5: Printed output has been routed to stdout.

Qshell sends the output to stdout after the program closes the printer file. If a program builds more than one spooled file, those spooled files are written to stdout one at a time. That is, the output of two spooled files is not intermingled.

The output of the program may remain in an output queue in addition to, or instead of, being routed to stdout. Three options, described in Table 20.1, control the treatment of printed output.

Table 20.2 shows the effect of these options if used with the preceding example.

Using the CL CALL command is not the only way to call a program object. You will usually call the program object directly, by typing the name of the program in IFS format, as in the following example:

/qsys.lib/jsmith.lib/cuslist1.pgm

However, the two methods are not equivalent. Using system to call a program object lets the system utility process the spooled file data and error messages. Calling a program by entering its name in IFS format does not give Qshell any control of the output. Since the two methods are not interchangeable, use whichever one is appropriate for the program that you're calling.

Liblist

Qshell inherits the library list of the job that started it. You can change the library list within Qshell, but the change only affects the Qshell session and the jobs that are created when you use Qshell utilities and commands.

When Qshell ends, the job's library list will be as it was before Qshell started. Also, if you press the F19 key from within Qshell to access an OS/400 command line, you will find that the library list does not reflect any changes that were made by running liblist within Qshell. This behavior exists because the library list is a job-level resource. (The job-and-process model of Qshell is described in chapter 2.)

If you do not specify any options with liblist , Qshell displays the library list. To modify the library list, use the options shown in Table 20.3.

In Figure 20.6, the user displays the library list, unsets the current library, and adds library QGPL to the end of the library list. The last liblist command displays the library list after modification.

liblist QSYS SYS QUSRSYS SYS QHLPSYS SYS QSHELL PRD JSMITHS CUR QTEMP USR JSMITHO USR JSMITHS USR JSMITHD USR liblist -cd /home/JSMITH $ liblist -al qgpl /home/JSMITH $ /home/JSMITH $ liblist QSYS SYS QUSRSYS SYS QHLPSYS SYS QSHELL PRD QTEMP USR JSMITHO USR JSMITHS USR JSMITHD USR QGPL USR

Figure 20.6: The liblist utility displays and modifies the library list within Qshell.

You may specify more than one library when changing the library list. The libraries are processed in order, as shown in Figure 20.7.

# remove 2 libraries from the library list liblist -d jsmitho jsmithd # add 4 libraries to the beginning of the library list # QGPL becomes the first library because it is added last liblist -a jsmithq jsmitho jsmithd qgpl # add 4 libraries to the end of the library list # QGPL becomes the last library because it is added last liblist -al jsmithq jsmitho jsmithd qgpl

Figure 20.7: You may add or remove multiple libraries with one command.

In Figure 20.8, the name of the current library is written to stdout . The liblist utility pipes the library list to grep , which extracts the line ending with the letters CUR . This finds the line containing the name of the current library, if there is one, but ignores lines that contain CUR within a library name. The stream editor, sed , removes all blanks preceding CUR , as well as the value CUR itself.

liblist QSYS SYS QUSRSYS SYS QHLPSYS SYS QSHELL PRD JSMITHS CUR QTEMP USR JSMITHO USR JSMITHS USR JSMITHD USR CURYR USR ARCURRENT USR TOCCUR USR QGPL USR /home/JSMITH $ liblist grep 'CUR$' sed 's/ *CUR$//' JSMITHS /home/JSMITH $

Figure 20.8: This compound command extracts the name of the current library.

Sysval

An OS/400 system value is a named value of a specific data type. System values provide the ability to read or modify a broad range of system configuration settings. Use the CL command Display System Value (DSPSYSVAL) to display system values outside of Qshell. Use the CL command Change System Value (CHGSYSVAL) to modify system values outside of Qshell.

The Qshell sysval utility provides a way to retrieve system values and network attributes. The output of sysval is written to stdout . For example, Figure 20.9 displays the system value QDAYOFWEEK. Without the p option, only the value is displayed. With the p option, the name and value of the system value are displayed in the form of an equation.

sysval qdayofweek *SAT /home/JSMITH $ sysval -p qdayofweek QDAYOFWEEK=*SAT /home/JSMITH $

Figure 20.9: The sysval utility can retrieve system values.

In Figure 20.10, the n option is used to display the name of the iSeries system.

sysval -n sysname MY400 /home/JSMITH $ sysval -np sysname SYSNAME=MY400 /home/JSMITH $

Figure 20.10: The sysval utility can retrieve network settings.

Datarea

An OS/400 data area is a named, persistent object of a specific size and data type. Data areas can contain generic data of your choosing. A data area is like a one-record file, but it's not a file ”it is a special OS/400 object type. Use CL's Display Data Area (DSPDTAARA) command to display a data area outside of Qshell. Use the Change Data Area (CHGDTAARA) command to change a data area outside of Qshell

The Qshell datarea utility, new in V5R2, allows you to retrieve and change the contents of data areas. Notice that the final a of data and the first a of area have been fused to form the name of this utility.

You may specify the names of data areas in either of two forms. First, you may specify the name in full- path form, as the following example shows:

/qsys.lib/mylib.lib/mydtaara.dtaara

Alternatively, you may use a relative path, which is simply the data area name. If you specify a relative name, you must include the l ("ell") option, unless the data area is in the current library.

Table 20.4 briefly describes the options that you may use with datarea .

The following command writes the contents of data area PLANTINFO, in library JSMITHS, to stdout :

datarea -r /qsys.lib/jsmiths.lib/plantinfo.dtaara 1250Lost Angeles, New Yolk 020NN-NNYYNYYY /home/JSMITH $

The data area name is given in full-path form. Notice that dtaara is the extension for data areas.

The command below also writes the contents of data area PLANTINFO to stdout, but uses a relative format:

datarea -r plantinfo 1250Lost Angeles, New Yolk 020NNNNNYYNYYY /home/JSMITH $

Since PLANTINFO is given in relative form and the l option is not specified, PLANTINFO must be in the current library.

The command in the following example again writes the contents of data area PLANTINFO to stdout, using the r option to read a data area:

datarea -rl plantinfo 1250Lost Angeles, New Yolk 020NNNNNYYNYYY /home/JSMITH $

Since PLANTINFO is given in relative form and the l option is specified, the system finds PLANTINFO by scanning the library list.

To place a value into a data area, use the w option. In Figure 20.11, the value of the first parameter is copied into data area PLANTINFO, replacing the previous contents.

print "" "1901Chicargo, Old Mexico" /home/JSMITH $ datarea -wl plantinfo /home/JSMITH $ datarea -rl plantinfo 1901Chicargo, Old Mexico /home/JSMITH $

Figure 20.11: Use the w option to write to a data area.

Use the s option to retrieve part (a substring) of a data area. The argument to s indicates a range of positions to retrieve. The first position of a data area is position 1. The argument may be one of the following:

• A single whole number
• A whole number followed by a hyphen
• Two whole numbers separated by a hyphen
• A hyphen followed by a whole number

These alternatives are explained in Table 20.5. The letters m and n in the table represent the numbers in the range argument.

Figure 20.12 shows how to read part of a data area. In this example, the s option is used to retrieve portions of PLANTINFO.

datarea -rl plantinfo 1250Lost Angeles, New Yolk 020NNNNNYYNYYY /home/JSMITH $ datarea -r -s 5-29 plantinfo Lost Angeles, New Yolk /home/JSMITH $ datarea -r -s 5 plantinfo Lost Angeles, New Yolk 020NNNNNYYNYYY /home/JSMITH $ datarea -r -s 5- plantinfo Lost Angeles, New Yolk 020NNNNNYYNYYY /home/JSMITH $

Figure 20.12: Use the s option to retrieve a portion of a data area.

Figure 20.13 shows how to change part of a data area. A hyphen is placed in position 35 of PLANTINFO, which is in the current library.

datarea -r -s 33-43 plantinfo NNNNNYYNYYY /home/JSMITH $ datarea -w -s 35-35 plantinfo - /home/JSMITH $ datarea -r -s 33-43 plantinfo NN-NNYYNYYY /home/JSMITH $

Figure 20.13: The s option allows you to change part of a data area.

When you are working with data areas, it might be helpful, or even necessary, to find the names of data areas from within Qshell. Use the ls utility for this purpose, as shown in Figure 20.14. Data area objects have an extension of .DTAARA. Be sure to specify the wildcard portion of a path name in all-capital letters when accessing the library file system.

ls /qsys.lib/js.lib/*.DTAARA /qsys.lib/js.lib/HOLDPAGES.DTAARA /qsys.lib/js.lib/HOLDPAGES2.DTAARA /qsys.lib/js.lib/NEXTORDKEY.DTAARA /qsys.lib/js.lib/PLANTINFO.DTAARA /qsys.lib/js.lib/QQUPRFOPTS.DTAARA /qsys.lib/js.lib/JSMITH.DTAARA /home/JSMITH $ ls -l /qsys.lib/js.lib/plantinfo.dtaara ?rwx---rwx 1 JSMITH 0 4096 Dec 25 02:38 /qsys.lib/js.lib/plantinfo.dtaara

Figure 20.14: Use the ls utility to retrieve the names of data areas.

Dataq

An OS/400 data queue is a named, persistent queue object used to communicate between programs or jobs. Data queues hold messages and have a multitude of processing characteristics and configuration options. They can be local or remote, and the messages in them can be persistent. Use CL's Create Data Queue (CRTDTAQ) command to create a data queue outside of Qshell. Use the Delete Data Queue (DLTDATAQ) command to delete a data queue outside of Qshell. Use the OS/400 data queue API's Receive Data Queue (QRCVDTAQ) or Send Data Queue (QSNDDTAQ) command to send and receive messages to a data queue outside of Qshell.

The dataq utility is also new in V5R2. This utility allows you to read messages on, write messages to, and remove messages from a data queue. Table 20.6 lists the defined options for dataq .

If you read a data queue, you may remove the read messages or leave them on the queue. You may specify a data queue name in either of two formats: full path or relative path .

To send a message to a data queue, use the w option, as in the following example:

dataq -w /qsys.lib/jsmith.lib/xacts.dtaq "$mycmd"

This command places the value of variable mycmd on data queue XACTS in library JSMITH.

To receive a message from a data queue, use the r option. Specify the number of seconds to wait until timing out in the t option. Use the n option to specify the number of messages to receive.

By default, dataq removes the message from the data queue. You may leave the message on the queue by including the p ("peek") option. The dataq utility writes the message to stdout .

Figure 20.15 illustrates the process of receiving data-queue messages. The first dataq command receives a two-byte message from data queue XACTS in JSMITH without removing the message. The second dataq receives the same message again, but removes it from the queue. The last dataq waits two seconds for a command and, receiving none, ends.

dataq -rp -t 2 /qsys.lib/jsmiths.lib/xacts.dtaq GO /home/JSMITH $ dataq -r -t 2 /qsys.lib/jsmiths.lib/xacts.dtaq GO /home/JSMITH $ dataq -r -t 2 /qsys.lib/jsmiths.lib/xacts.dtaq /home/JSMITH $

Figure 20.15: Use the r option to read data queue messages.

If the data queue is in the current library, you may use a relative path for its name. If the data queue is in another library in the library list, you may use a relative path by adding the l ("ell") option. When using a relative path, type the data queue's name in all-capital letters .

For example, in Figure 20.16, the data queue XACTS is in the current library. For that reason, the queue's name can be in relative-path form.

dataq -w XACTS '/home/mydir/file345.txt' /home/JSMITH $ dataq -r XACTS /home/mydir/file345.txt

Figure 20.16: The XACTS data queue is in the current library.

In Figure 20.17, the data queue is not in the current library. Instead, it is in a library in the library list. The l option is needed to tell dataq to search the library list.

dataq -w XACTS '/home/mydir/file345.txt' dataq: 001-2113 Error found getting information for object XACTS. No such path or directory. /home/JSMITH $ dataq -wl XACTS '/home/mydir/file345.txt' /home/JSMITH $ dataq -r XACTS dataq: 001-2113 Error found getting information for object XACTS. No such path or directory. /home/JSMITH $ dataq -rl XACTS /home/mydir/file345.txt /home/JSMITH $

Figure 20.17: The l ("ell") option causes dataq to search the library list for the XACTS data queue.

The dataq utility can receive more than one message at a time, as shown here:

dataq -r -n3 XACTS /home/mydir/file345.txt /home/mydir/file346.txt /home/mydir/file347.txt /home/JSMITH $

Use the c option to clear a data queue of all messages. For example, the following command clears data queue XACTS, which is found by scanning the library list:

dataq -cl XACTS

DB2

Qshell includes two utilities that provide access to the DB2 UDB/400 database: db2 and Rfile .

There are many user interfaces to DB2 UDB/400 that are officially supported and recommended (by IBM). The Qshell db2 utility is not one of those, however, so if you find an error in it, you might or might not be able get IBM support to address the problem. Still, db2 is available in the /usr/bin directory, so if it works for your purposes, you can certainly take advantage of it.

This section discusses the db2 utility as of some significant fixes and enhancements made by IBM in V5R2, PTF 5722SS1-SI07040. (A PTF is a Program Temporary Fix, which contains bug fixes for OS/400.) Table 20.7 lists the options available with the db2 utility, as of this PTF in release V5R2. Earlier versions of db2 might work differently and support different options.

The db2 utility enables you to run SQL commands within Qshell. Table names must be specified in *SQL format (library name, period, table name). If the library name and period are not specified, the table is assumed to be in a library with the same name as the user profile that is running Qshell. The output of SQL select statements is routed to stdout. Error messages are also routed to stdout.

The db2 utility requires that the SQL statement be quoted, with either strong (single) or weak (double) quotes, so that the SQL command is treated as one argument. In Figure 20.18, db2 routes the output of a simple SELECT statement to stdout.

db2 'select company,custnbr,custname,ccity,cstate from jsmiths.customer' COMPANY CUSTNBR CUSTNAME CCITY CSTATE -------- -------- -------------------- ---------------- ------- 1 44 Joe's Shoes Duluthe GA 1 10001 Sue's Bridle Shop Saltillo MS 1 20002 Bank of Steele Medina PA 1 30003 Snowman Heat & Air Lexington OH 2 1 Xolomon Solutions Tulsa OK 2 3 Donaldson Electric Whittier CA 2 345 Gretta's Gifts Saddle Brook NJ 3 1 Ames Wholesale Tupelo MS 3 3 Army Surplus Brunswick OH 3 56 Jak's Liver Emporium Mexico OH 4 40004 Grayson Paul Little City ND 7 777 Pretty Boy's Gym Lost Angeles NY 7 222 Sardine Paradise Happah Palloolah CA 8 1 48% of Nothing Klondike FL 8 2 Robert R. Roberts IV New Yolk CA 8 3 Sal Monella 16 RECORD(S) SELECTED.

Figure 20.18: The db2 utility routes SQL output to stdout.

Add the v option if you want db2 to echo the SQL command to stdout. This is especially useful when part of the SQL command comes from a Qshell variable, and also when SQL commands are read from a file.

In Figure 20.19, the records to be selected are indicated by the value in the Qshell variable company . Notice that the SQL statement is enclosed in weak quotes, so that the $company token will be replaced by the variable's current value. Because of the v option, the db2 utility shows the resolved SQL command.

print $company 3 /home/JSMITH $ db2 -v "select company,custnbr, custname, ccity, cstate > from jsmiths.customer > where company=$company" EXECUTING: select company,custnbr, custname, ccity, cstate from jsmiths.customer where company=3 DONE! COMPANY CUSTNBR CUSTNAME CCITY CSTATE -------- -------- -------------------- ---------------- ------- 3 1 Ames Wholesale Tupelo MS 3 3 Army Surplus Brunswick OH 3 56 Jak's Liver Emporium Mexico OH 3 RECORD(S) SELECTED. /home/JSMITH $

Figure 20.19: The v option displays the resolved SQL command.

Since db2 writes to stdout, Qshell can manipulate the output of SQL commands as it would any other data. In the next -to-last Qshell command in Figure 20.20, the output of the SQL command is routed into the stream editor, sed . The stream editor looks for a line containing two hyphens, which is the line that separates a column heading from the first line of data. When it finds the line of hyphens, it reads the next record, which contains the customer name, and prints the customer name to stdout. The output of sed ”the customer name ”is directed by means of command substitution into the variable custname .

print $comp; print $cust 3 56 /home/JSMITH $ db2 "select custname > from jsmiths.customer > where company=$comp and custnbr=$cust" CUSTNAME -------------------- Jak's Liver Emporium 1 RECORD(S) SELECTED. /home/JSMITH $ custname=$( > db2 "select custname > from jsmiths.customer > where company=$comp and custnbr=$cust" > sed -n '/--/{n; p;}') /home/JSMITH $ print $custname Jak's Liver Emporium /home/JSMITH $

Figure 20.20: The output of an SQL statement is stored in a Qshell variable.

The preceding examples used the SELECT command, but the db2 utility can process any SQL command that can run interactively. The following lines show the three basic file maintenance processes ”changing a record, deleting a record, and adding a record:

db2 "update companies set conam='Lizzy''s Livery Stable' where company=1" db2 "delete from companies where company = 1" db2 "insert into companies values(3, 'Jack''s Java Palace')"

SQL Scripts

Use the f option to execute SQL scripts (SQL commands stored in a file). Here are some things to keep in mind when creating SQL scripts:

• If the file is a database file member in the QSYS.LIB file system, you must specify the member name in IFS format.
• You may store more than one SQL statement in the file.
• You can enter only one statement per record.
• You must enter a complete statement in a single record unless you specify a delimiter using the d , t , or T options. If you specify a delimiter, a single statement may span multiple records.
• Your SQL command cannot contain references to Qshell variables .
• You may use the double-hyphen syntax to include comments in the file.

Several of these points are illustrated in Figure 20.21. The script file is CustListNbr01.sql, and the first two lines contain comments. The SQL command itself spans four lines. This is allowable because of the semicolon delimiter in the last line.

cat CustListNbr01.sql -- to run: db2 -tvf CustListNbr01.sql -- library name must be specified in -f option select cstate, custname,company,custnbr from customer where company=3 order by 1,2; /home/JSMITH $ db2 -tvf CustListNbr01.sql jsmiths EXECUTING: select cstate, custname,company,custnbr from customer where company=3 order by 1,2 DONE! CSTATE CUSTNAME COMPANY CUSTNBR ------- -------------------- -------- -------- MS Ames Wholesale 3 1 OH Army Surplus 3 3 OH Jak's Liver Emporium 3 56 3 RECORD(S) SELECTED. /home/JSMITH $

Figure 20.21: The db2 utility can execute SQL script files.

Figure 20.22 provides an example that uses Qshell I/O and the db2 utility. The file datain.csv contains comma-separated data received via some sort of EDI mechanism. The edi210.qsh Qshell script reads the CSV data and inserts new rows into the database.

cat datain.csv 2260,A192287,A-195,4 2260,A192287,C-267,12 2260,A192287,B-332,101 2260,A192287,N-271,5 2260,A192287,N-105,35 2260,A192287,D-328,17 /home/JSMITH $ cat edi210.qsh IFS=',' while read custnbr ponbr itemnbr qty do db2 "insert into jsmiths.ediin values ($custnbr, '$ponbr', '$itemnbr', $qty)" done /home/JSMITH $ edi210.qsh DB20000I THE SQL COMMAND COMPLETED SUCCESSFULLY. DB20000I THE SQL COMMAND COMPLETED SUCCESSFULLY. DB20000I THE SQL COMMAND COMPLETED SUCCESSFULLY. DB20000I THE SQL COMMAND COMPLETED SUCCESSFULLY. DB20000I THE SQL COMMAND COMPLETED SUCCESSFULLY. DB20000I THE SQL COMMAND COMPLETED SUCCESSFULLY. /home/JSMITH $ db2 "select * from jsmiths.ediin" CUST PONBR ITEM QTY ------- -------- ------ ---- 2260 A192287 A-195 4 2260 A192287 C-267 12 2260 A192287 B-332 101 2260 A192287 N-271 5 2260 A192287 N-105 35 2260 A192287 D-328 17 6 RECORD(S) SELECTED. /home/JSMITH $

Figure 20.22: The db2 utility can be used to update a database table.

DB2 in Interactive Mode

The i option allows you to run db2 interactively. This differs from repeatedly running the db2 command in the following ways:

• The db2 utility is loaded once and remains active between command executions.
• Execution of SQL statements is similar to the way SQL scripts are executed, and is limited in the same ways. For example, interactive SQL statements cannot reference Qshell variables .
• You can use transactions consisting of multiple SQL statements, all of which can be committed or rolled back in their entirety.
• You can use db2 as if it were a filter, accepting a stream of commands and outputting the results as appropriate.

When you enter interactive mode, db2 responds with the DB2 > prompt. The continuation prompt, for incomplete SQL commands, is ?> .

When you run db2 in interactive mode, you are communicating directly with the db2 utility, not with Qshell. Therefore you must use db2 subcommands, rather than Qshell commands. The first db2 subcommand you should learn is help , which may be entered as the complete word "help," or as a single question mark. As Figure 20.23 shows, the db2 utility responds with a "crib sheet."

/home/JSMITH $ db2 -i DB2> help Enter any of the following built-in commands: help or ? Show this brief help -- anything Comment line exit or quit End the DB2 session echo Echo the text listed connect Currently ignored terminate Currently ignored !qshcommand Run a QSH command or utility @clcommand Run a CL command OTHER Run the statement as SQL, reporting results Current options specified are: Connected with default user to *LOCAL database Processing interactive statements Statements delimited by newline Verbose echo of statements is OFF Misc - If using delimited SQL (-t, -d or -T), end lines with to avoid inserting a space (for example, in SQL literals) - If using a user specified SQL delimiter character (-T or -d option), be careful that the delimiter (if its the first character on a line) doesn't match the special built-in command modifiers (comment, CL or QSH command) DB2> exit /home/JSMITH $

Figure 20.23: The db2 utility includes brief, online help text in interactive mode.

Figure 20.24 shows a more extensive db2 session. Here, the user queries data and inserts a row into a table. Notice that the db2 command includes the t switch. Without it, db2 would try to execute an SQL command every time the Enter key was pressed. Thanks to the t , the semicolon delimits the commands, allowing the operator to continue commands across multiple lines of input.

db2 -it DB2> select cstate,custname,company,custnbr ?> from jsmiths.customer ?> order by 1,2; CSTATE CUSTNAME COMPANY CUSTNBR ------- -------------------- -------- -------- Sal Monella 8 3 CA Donaldson Electric 2 3 CA Robert R. Roberts IV 8 2 CA Sardine Paradise 7 222 FL 48% of Nothing 8 1 GA Joe's Shoes 1 44 MS Ames Wholesale 3 1 MS Sue's Bridle Shop 1 10001 ND Grayson Paul 4 40004 NJ Gretta's Gifts 2 345 NY Pretty Boy's Gym 7 777 OH Army Surplus 3 3 OH Jak's Liver Emporium 3 56 OH Snowman Heat & Air 1 30003 OK Xolomon Solutions 2 1 PA Bank of Steele 1 20002 16 RECORD(S) SELECTED. DB2> insert into jsmiths.customer ?> (cstate,custname,company,custnbr) ?> values ('TX','Ben Dover',2,44); DB20000I THE SQL COMMAND COMPLETED SUCCESSFULLY. DB2> select company,custnbr,custname,state ?> from jsmiths.customer where company=2; **** CLI ERROR ***** SQLSTATE: 42703 NATIVE ERROR CODE: -206 Column STATE not in specified tables. DB2> select company,custnbr,custname,cstate ?> from jsmiths.customer where company=2; COMPANY CUSTNBR CUSTNAME CSTATE -------- -------- -------------------- ------- 2 1 Xolomon Solutions OK 2 3 Donaldson Electric CA 2 44 Ben Dover TX 2 345 Gretta's Gifts NJ 4 RECORD(S) SELECTED. DB2> quit /home/JSMITH $

Figure 20.24: The db2 utility, not Qshell, converses with the user in this example.

Rfile

The Rfile (Record File) utility copies data between the standard I/O devices and the database or device files. Some of the same things you can do with the db2 utility you can do with Rfile , while other things you cannot. Unlike an SQL statement with a WHERE clause, Rfile reads or writes all records, not subsets of records. Also, the Rfile utility doesn't do data conversion of non-character fields into displayable text, so results displayed in the terminal might not be what you'd expect. The Rfile utility does, however, allow you to access device files, which SQL cannot access.

Table 20.8 lists the options acceptable to the Rfile utility.

The following Rfile examples use the data file numbers in the JSMITHS library. The data file is shown by the db2 utility in Figure 20.25. It contains a four-byte integer column and a 10-byte character column, with the numbers 1 through 5 in each column.

db2 'select * from jsmiths.numbers' COL1 COL2 ----------- ----------- 1 One 2 Two 3 Three 4 Four 5 Five 5 RECORD(S) SELECTED. /home/JSMITH $

Figure 20.25: This data file is used in the following examples to illustrate Rfile .

Figure 20.26 shows that when the Rfile utility is used to read data from a table, it doesn't perform conversion on the data. Instead, it simply outputs the record, unchanged, to standard output. Although the b option indicates data should be processed as binary, all that really means is that newline characters do not separate records.

rfile -r /qsys.lib/jsmiths.lib/numbers.file/numbers.mbr One Two Three Four Five rfile -br /qsys.lib/jsmiths.lib/numbers.file/numbers.mbr One Two Three Four Five /home/JSMITH $ rfile -r /qsys.lib/jsmiths.lib/numbers.file/numbers.mbr od -tx1c 0000000 000 000 000 001 0d6 095 085 025 000 000 000 002 0e3 0a6 096 025 000 000 000 001 O n e 045 000 000 000 002 T w o 045 0000020 000 000 000 003 0e3 088 099 085 085 025 000 000 000 004 0c6 096 000 000 000 003 T h r e e 045 000 000 000 004 F o 0000040 0a4 099 025 000 000 000 005 0c6 089 0a5 085 025 u r 045 000 000 000 005 F i v e 045 0000054 /home/JSMITH $ rfile -r /qsys.lib/jsmiths.lib/numbers.file/numbers.mbr tr '/[

rfile -r /qsys.lib/jsmiths.lib/numbers.file/numbers.mbr One Two Three Four Five rfile -br /qsys.lib/jsmiths.lib/numbers.file/numbers.mbr One Two Three Four Five /home/JSMITH $ rfile -r /qsys.lib/jsmiths.lib/numbers.file/numbers.mbr od -tx1c 0000000 000 000 000 001 0d6 095 085 025 000 000 000 002 0e3 0a6 096 025 000 000 000 001 O n e 045 000 000 000 002 T w o 045 0000020 000 000 000 003 0e3 088 099 085 085 025 000 000 000 004 0c6 096 000 000 000 003 T h r e e 045 000 000 000 004 F o 0000040 0a4 099 025 000 000 000 005 0c6 089 0a5 085 025 u r 045 000 000 000 005 F i v e 045 0000054 /home/JSMITH $ rfile -r /qsys.lib/jsmiths.lib/numbers.file/numbers.mbr tr '/[00- 11]/' '/[0-9]/' 0001 One 0002 Two 0003 Three 0004 Four 0005 Five /home/JSMITH $

0- 1]/' '/[0-9]/' 0001 One 0002 Two 0003 Three 0004 Four 0005 Five /home/JSMITH $

Figure 20.26: Binary data output from Rfile can be accessed in Qshell.

The first Rfile command does not show both columns . Attempting to use the b option in the second Rfile command to use binary data simply removes the new lines between records; it doesn't generate the desired output.

As a means of debugging, the od command displays the hexadecimal data that Rfile outputs. From it, you can see the binary data for the four-byte integers 1 through 5 that are generated. You can then use another Qshell filter (perhaps the tr utility) to process that binary data. In Figure 20.26, tr converts the binary data ”octal 000 (zero) through octal 011 (nine) ”to the matching character digits.

The Rfile utility can also be used to write records to a database file. If the database file has non-character fields, however, the issue with binary becomes somewhat problematic . The Qshell utility that generates the data needs to generate binary data appropriately for the table.

Figure 20.27 builds on the previous example. It shows an Rfile command that appends the binary data from an existing stream file into the JSMITHS/NUMBERS database file. The database file is specified using OS/400 library-naming convention via the Q option. The binary datafile.dat file matches the required record format of the database file, and can as easily come in a pipeline from a utility that generates it as from a data file. The resulting JSMITHS/NUMBERS file, containing all the records, is displayed in both hexadecimal and converted text format.

cat datafile.dat Six Seven Eight Nine /home/JSMITH $ cat datafile.dat od -tx1c 0000000 000 000 000 006 0e2 089 0a7 025 000 000 000 007 0e2 085 0a5 085 000 000 000 006 S i x 045 000 000 000 007 S e v e 0000020 095 025 000 000 000 008 0c5 089 087 088 0a3 025 000 000 000 009 n 045 000 000 000 010 E i g h t 045 000 000 000 011 0000040 0d5 089 095 085 025 N i n e 045 0000045 /home/JSMITH $ Rfile -awQ JSMITHS/NUMBERS < datafile.dat /home/JSMITH $ rfile -rQ JSMITHS/NUMBERS od -tx1c 0000000 000 000 000 001 0d6 095 085 025 000 000 000 002 0e3 0a6 096 025 000 000 000 001 O n e 045 000 000 000 002 T w o 045 0000020 000 000 000 003 0e3 088 099 085 085 025 000 000 000 004 0c6 096 000 000 000 003 T h r e e 045 000 000 000 004 F o 0000040 0a4 099 025 000 000 000 005 0c6 089 0a5 085 025 000 000 000 006 u r 045 000 000 000 005 F i v e 045 000 000 000 006 0000060 0e2 089 0a7 025 000 000 000 007 0e2 085 0a5 085 095 025 000 000 S i x 045 000 000 000 007 S e v e n 045 000 000 0000100 000 008 0c5 089 087 088 0a3 025 000 000 000 009 0d5 089 095 085 000 010 E i g h t 045 000 000 000 011 N i n e 0000120 025 045 0000121 /home/JSMITH $ rfile -rQ JSMITHS/NUMBERS tr '/[

cat datafile.dat Six Seven Eight Nine /home/JSMITH $ cat datafile.dat od -tx1c 0000000 000 000 000 006 0e2 089 0a7 025 000 000 000 007 0e2 085 0a5 085 000 000 000 006 S i x 045 000 000 000 007 S e v e 0000020 095 025 000 000 000 008 0c5 089 087 088 0a3 025 000 000 000 009 n 045 000 000 000 010 E i g h t 045 000 000 000 011 0000040 0d5 089 095 085 025 N i n e 045 0000045 /home/JSMITH $ Rfile -awQ JSMITHS/NUMBERS < datafile.dat /home/JSMITH $ rfile -rQ JSMITHS/NUMBERS od -tx1c 0000000 000 000 000 001 0d6 095 085 025 000 000 000 002 0e3 0a6 096 025 000 000 000 001 O n e 045 000 000 000 002 T w o 045 0000020 000 000 000 003 0e3 088 099 085 085 025 000 000 000 004 0c6 096 000 000 000 003 T h r e e 045 000 000 000 004 F o 0000040 0a4 099 025 000 000 000 005 0c6 089 0a5 085 025 000 000 000 006 u r 045 000 000 000 005 F i v e 045 000 000 000 006 0000060 0e2 089 0a7 025 000 000 000 007 0e2 085 0a5 085 095 025 000 000 S i x 045 000 000 000 007 S e v e n 045 000 000 0000100 000 008 0c5 089 087 088 0a3 025 000 000 000 009 0d5 089 095 085 000 010 E i g h t 045 000 000 000 011 N i n e 0000120 025 045 0000121 /home/JSMITH $ rfile -rQ JSMITHS/NUMBERS tr '/[00-11]/' '/[0-9]/' 0001One 0002Two 0003Three 0004Four 0005Five 0006Six 0007Seven 0008Eight 0009Nine /home/JSMITH $

0-1]/' '/[0-9]/' 0001One 0002Two 0003Three 0004Four 0005Five 0006Six 0007Seven 0008Eight 0009Nine /home/JSMITH $

Figure 20.27: The tr utility can translate binary data into a human-readable format.

In addition to database files, you can use Rfile to access other device files in the operating system. OS/400 printer files are used to generate print output. For example, the following Rfile command copies the stream file FTPBUILD.QSH to the QSYSPRT printer file:

cat -n ftpbuild.qsh > Rfile -w /qsys.lib/qsysprt.file /home/JSMITH $

The n option of the cat utility numbers the lines as they are printed.

The following is equivalent in function to the preceding command:

cat -n ftpbuild.qsh > Rfile -wQ qsys/qsysprt /home/JSMITH $

In this example, however, the printer file QSYSPRT is written in the traditional OS/400 library-qualified naming in the Rfile command, because of the Q option.

Summary

In V5R2, IBM has done a good job of creating utilities that permit access to OS/400-specific objects. The system utility accesses existing CL command and programs. Rfile and db2 permit access to database files. The datarea utility reads and writes data areas, while dataq reads and writes data queue messages.

Chapter 21 Application Development Tools