Using a Specific Member in SQL

SQL by default reads the first member of the file(s) being processed. You can get around this limitation by executing the Override Data Base File (OVRDBF) command before you use the Start SQL (STRSQL) command. For example, the following statement makes SQL use member ABC123 if you process file FNAME.

 OVRDBF FILE(FNAME) MBR(ABC123) 

RUNQRY with Record Selection

To create an ad hoc report for any data file-physical or logical-without creating a DFU or Query object, use the QRYFILE parameter of the Run Query (RUNQRY) command. This will display the file in columns, with the column headings defined for the fields in DDS. The file can also be printed or output to an outfile by changing the OUTTYPE parameter to either *PRINTER or *OUTFILE. Using RCDSLT(*YES) will permit record selection based upon field contents.

 RUNQRY QRYFILE(file_name) 

or

 RUNQRY QRYFILE(file_name) OUTTYPE(*PRINTER) RCDSLT(*YES) 

Editor's Note: The RCDSLT(*YES) parameter can only be used if Query/400 is installed on your system.

Selecting Records from a File

Display the contents of a data file selectively for those records that have a specific field value by using a Query Management Query with variables. Create a Query Management Query source member with variables defined for the selection criteria. For example, the following source statement accepts any value for the file name, field name and field value.

 SELECT * FROM &FILE WHERE &FIELD = &VALUE 

The values can be specified as parameters; otherwise, the system will automatically prompt for their entry. Compile it with the Create Query Management Query (CRTQMQRY) command. Here we'll call it DSPFLD. Then, whenever you want to display the contents of any file based on a field's contents use one of the two statements that follow.

 STRQMQRY QMQRY(DSPFLD) 

or

 STRQMQRY QMQRY(DSPFLD) SETVAR((FILE file_name) + (FIELD field_name) (VALUE selection_value)) 

With the first statement, you will be prompted for the file name, the field name and the value of the field.

Getting Back to an Alternate Job

Have you ever mistakenly called the System Request Menu Option 1 (Display sign- on for alternative job)? Here's a way to get back to your original job quickly. First, create a CL program called DFTSIGNOFF which looks like this:

PGM

SIGNOFF

ENDPGM

Next, create a dummy user profile called "X" with a password of "X" with an initial program of DFTSIGNOFF. Now, if you accidentally do a System Request 1, just sign on with User X, Password X. You'll end up right back where you started from.

Run Programs from Within Office Documents

Within a document, the ".run" instruction can be used to execute a CL command that produces a report and includes the report output in the printed document. Each time the document is printed, the report program would execute and the new report would be included in the printed output.

Determining Release Level

By retrieving the data area QSYS/Q5738SS1, you can determine the version, release and modification level of your operating system. The first six bytes will contain your system's release in VxRxMx format.

Copy AS/400 Screens to a File

The Start Copy Screen (STRCPYSCN) command's main function is to monitor a remote workstation, but you can also use it to document step-by-step processing. By issuing the following command, every screen will be copied to an output file called CPYS in QGPL.

 STRCPYSCN SRCDEV(*REQUESTER) OUTDEV(*NONE) + OUTFILE(QGPL/CPYS) 

You will be prompted with a message telling you that a copy screen request has been issued. You can use "C" to cancel or "G" to continue. Every screen you see will be copied to the output file as soon as you press the Enter key. Use the End Copy Screen (ENDCPYSCN) command to end the function. For documentation purposes, the file containing screens can be converted to a source file like this:

 CPYF FROMFILE(QGPL/CPYS) TOFILE(QGPL/QRPGSRC) + TOMBR(MBRNAME) REPLACE(*YES) FMTOPT(*CVTSRC) 

Unfortunately, the file will contain the attribute bytes from the screen.

Search Indexes Galore

The Start Search Index (STRSCHIDX) command can be used for more than the main help index for the AS/400 (search index QHSS1, the default). Other search indexes can be used. The interest area and the corresponding indexes are listed below:

 Search Index Library Name Utilities QPDA QHPW1 Query QQRYLIB QHQU1 RPG QRPG QHRG1 Office Vision QOFC QHWP1 RJE Comm. QRJE QHRJE 

This list may be different depending on what licensed products are installed on your system. To find out what search indexes are available, submit the following command:

 DSPOBJD OBJ(*ALL/*ALL) OBJTYPE(*SCHIDX) OUTPUT(*PRINT) 

Create Your Own QTEMP

Create a library (SCRATCH) for submitting queries to batch. Users can create queries in this library and then submit them for batch processing. Clear the library via a nightly/weekly processing routine. Queries can now be submitted instead of running in QTEMP interactively and temporary queries can be cleaned up automatically.

Use this library for all temporary objects such as DFUs and as a work area for restores.

Free SQL in OS/400

Every AS/400 can execute SQL statements whether or not you have SQL/400. Enter a valid SQL statement in a source member (type TXT) in a source file called QQMQRYSRC. Then create a query object (*QMQRY) from the source member by executing the Create Query Management Query (CRTQMQRY) command. To run the SQL statement, execute the Start Query Management Query (STRQMQRY) command. Output from the SQL statement can be directed to the display, printer or database file.

Quick Return from Interrupted Session

If you ever select option 1 from the System Request menu to start a secondary job and then you change your mind, you can return to your primary session without signing on and off. All you need to do is use the Alt + Test key combination (in PC Support, this defaults to Alt + Pause) to get the PRIME Option Menu and then select option C to end. On older keyboards, you need to use the Cmd + Backspace key combination.

Global Time-out

How many times have you asked your AS/400 users to please sign off the system when they leave for a lunch break or for the day? If you have a security problem or your night processing aborts because a user left a workstation signed on somewhere, you can use the following method to solve your troubles.

System value QINACTITV contains the number of minutes an inactive job is allowed to remain on the system before automatic action is taken. (Local jobs that are signed on to a remote system are excluded.) Thirty minutes is perhaps a length of time generous enough. Run the following command:

 CHGSYSVAL SYSVAL(QINACTITV) VALUE('30') 

What action is taken depends on system value QINACTMSGQ. This system value contains either the qualified name of a message queue where a message is sent, or the value *ENDJOB if the job is to be ended, or *DSCJOB if it is to be disconnected. To solve your problem, you could set it to *DSCJOB, as follows:

 CHGSYSVAL SYSVAL(QINACTMSGQ) VALUE('*DSCJOB') 

Because the job is simply disconnected, the user can return to his job and resume it at the exact point where he left by simply signing on to the same workstation, using the same user profile name.

If the job is only disconnected, the night processing may still abort if there is a conflict of object allocations, for example. What we need is to end these jobs. Try the following:

CHGSYSVAL SYSVAL(QDSCJOBITV) VALUE('60')

Now all interactive jobs will end automatically 60 minutes after being disconnected-whether this disconnection was done manually with the Disconnect Job (DSCJOB) command or automatically.

Global Printer File Override

Some AS/400 printers require special print file characteristics in order to print correctly. Print file characteristics such as page size, lines per inch and characters per inch can be changed using the Override Printer File (OVRPRTF) command. However, throughout any given day there may be dozens of different printer file names that need to be overridden. It's impractical to perform overrides for every possible printer file.

No problem. Just use the special value *PRTF for the printer file name in the OVRPRTF command. This method overrides all printer files used by a job. For example, if you happen to use a laser printer which is attached to your AS/400, first issue the following command:

 OVRPRTF FILE(*PRTF) PAGESIZE(88 132) LPI(8) CPI(16.7) + OVRFLW(80) PAGRTT(0) 

This forces all spool files that you create interactively to automatically print in compressed mode. This way, you are able to print a 132-column report on an 8 1/2" x 11" sheet of paper. Even the printer files used by OS/400 commands which allow OUTPUT(*PRINT) are overridden, including the output created by pressing the Print key. This technique could also be used to control spooled file attributes; for example, to create spool files that are automatically placed on hold or in save mode or directed to a particular output queue or device.

Monitoring Job Completion Messages

If you would like to access your completed job messages without having to look for them with a Display Message (DSPMSG) command, and you don't want the annoyance of break messages interrupting your current work, try this message- handling program.

 PGM PARM(&MSGQ &MSGQLIB &MSGK) DCL VAR(&MSGQ) TYPE(*CHAR) LEN(10) DCL VAR(&MSGQLIB) TYPE(*CHAR) LEN(10) DCL VAR(&MSGK) TYPE(*CHAR) LEN(4) DCL VAR(&MSGDTA) TYPE(*CHAR) LEN(132) RCVMSG MSGQ(&MSGQLIB/&MSGQ) MSGKEY(&MSGK) RMV(*NO) + MSG(&MSGDTA) SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA(&MSGDTA) + TOPGMQ(*EXT) MSGTYPE(*STATUS) ENDPGM 

After compiling this program which we'll call MONMSG, execute the following command to cause your completion messages to appear unobtrusively as status messages at the bottom of your screen. You might add this command to your initial program or setup routine.

 CHGMSGQ MSGQ(current_completion_message_queue) + DLVRY(*BREAK) PGM(MONMSG) 

It works by changing a message queue to *BREAK delivery mode and specifying a message-handling program. This will cause OS/400 to call the MONMSG program each time a message arrives at the message queue. MONMSG will then send the message to the external program message queue.

Initial Workstation Program

To run an initial program for a certain terminal, follow these steps:

1. Create a job description (give it the same name as the workstation) and specify the RTGDTA parameter. For example, if the display station is named DSP25, do:

 CRTJOBD JOBD(xxx/DSP25) RTGDTA('DSP25') 

2. Add a workstation entry to the interactive subsystem (QINTER) for the workstation, referencing the job description just created:

 ADDWSE SBSD(QINTER) WRKSTN(DSP25) JOBD(xxx/DSP25) + AT(*SIGNON) 

3. Add a routing entry to the interactive subsystem, referencing the program you want to run:

 ADDRTGE SBSD(QINTER) SEQNBR(xxx) CMPVAL('DSP25') + PGM(program_name) 

What happens when the user signs on is the system checks the workstation entry and discovers that it has a job description attached. It examines the job description and finds routing data DSP25. Then it goes through the routing entries, matching the DSP25 in the CMPVAL parameter, and executes the program named in the PGM parameter of the routing entry.

Duplicating Spool Files

The Duplicate Spooled File (DUPSPLF) command supplied in QUSRTOOL is a very useful tool, but it does have some limitations.

1. There is no easy way of selecting the spooled file that you want to duplicate. You often need to specify a large number of parameters for the system to be able to locate the particular spooled file you want.

2. Some spooled file attributes (variable font, barcodes, and so forth) cannot be duplicated by the DUPSPLF command.

A way of getting around both of these problems is to use the following technique:

1. Ensure that your user profile is enrolled in the system directory using the Work with Directory (WRKDIR) command.

2. From the Work with Output Queue (WRKOUTQ) or Work with Spooled Files (WRKSPLF) panel, select option 1 ("send") on the spooled file you wish to copy.

3. On the Send Network Spooled File (SNDNETSPLF) command prompt that appears, specify your system directory entry in the TOUSRID parameter (User ID and Address), and *ALLDATA in the DTAFMT parameter.

A copy of the spooled file (with all attributes intact) will be placed on the output queue associated with your user profile. Since I use this technique often, I have set up several user profiles [with PASSWORD(*NONE)] to use as targets for the SNDNETSPLF command. Each of these user profiles is enrolled in the system directory, and each of them has specified on the OUTQ parameter the output queue to which I want to send the spooled file. I find it useful to give these user profiles the same name as the desired target output queue.

Eliminate Unwanted Printer Writer Job Logs

If you would like to reduce the amount of job logs generated on your system and see no need for logging printer writers, use the following command. It eliminates the logging of all printer writers by changing the message logging of the job description used by printer writers (QSPLPRTW):

 CHGJOBD JOBD(QSPLPRTW) LOG(0 99 *NOLIST) 

Find Save Information on an Object

When the Display Object Description (DSPOBJD) command is used with the DETAIL(*FULL) parameter it can provide you with a quick and simple method of determining the last time an object was saved and on what medium (tape, diskette, or save file) and where (volume/sequence number) on tape the object is located. The command output can be directed to the screen, the printer, or an outfile that can be read by a program. The Retrieve Object Description (RTVOBJD) command can provide the same information to a CL program. The information is returned to your program as CL variables.

Also, six data areas in QSYS contain save/restore information in their object descriptions. The data areas and the information their descriptions contain are listed below:

 QSAVLIBALL - info on SAVLIB (*NONSYS) operation. QSAVALLUSR - info on SAVLIB (*ALLUSR) operation. QSAVIBM - info on SAVLIB (*IBM) operation. QSAVUSRPRF - last use of SAVSYS, SAVSECDTA and RSTUSRPRF commands. QSAVSYS - info on last use of SAVSYS command. QSAVSTG - info on last use of SAVSTG command. 

Startup Program Customization

Coming into a new AS/400 shop as System Administrator, the first thing I had to do was change the system startup program. The startup program is like the AUTOEXEC.BAT file on a DOS-based PC-the first job that runs when the system is powered up. On the AS/400, the default program called QSTRUP starts all the important subsystems such as QINTER, QBATCH and QSPL, and it also starts up some jobs that need to be run at IPL time such as the automatic cleanup. Like us, you may need to modify the startup program to add programs or start subsystems at IPL. Here's how we did it.

In our case, we had to alter the program to start the RBTSLEEPER subsystem that runs Help Systems' Robot program. Since our startup program is the IBM-supplied program, QSTRUP, and the source code is not supplied for it, we had to retrieve the source code, alter it to start RBTSLEEPER, recompile the program to a different library and indicate to the AS/400 that it should now run the modified code instead of the IBM-supplied program.

In order to retrieve the name of your startup program, run the Display System Value (DSPSYSVAL) command, specifying system value QSTRUPPGM. The system presents a panel which shows the name of the startup program.

If your startup program is QSTRUP in library QSYS, retrieve the source code for it with the Retrieve CL Source (RTVCLSRC) command, as follows:

 RTVCLSRC PGM(QSYS/QSTRUP) SRCFILE(QGPL/QCLSRC) SRCMBR(*PGM) 

This command places the source code in source file QGPL/QCLSRC, member QSTRUP. SEU then allows you to change the code. (You may, of course, use a different source file and library.) We compiled ours to QGPL (instead of QSYS) because we preferred to keep the original version of the program intact. That meant that we needed to change system value QSTRUPPGM to point to the new, updated program in QGPL rather than the original, IBM-supplied program in QSYS.

To change QSTRUPPGM, run the Work with System Values (WRKSYSVAL) command, position the system value list to QSTRUPPGM and place an option 2 next to it. Then type in the new name of the program and its library. In our case, the program name was the same (QSTRUP), but the library was QGPL.

At the next IPL, the operating system looked into system value QSTRUPPGM to determine what program to run at startup, and consequently ran QGPL/QSTRUP. This procedure enabled us to start subsystem RBTSLEEPER at IPL and retrieve the CL code of the startup program for future modifications.

Turn Your Job into a Group Job

It is possible to harness the power of group jobs with the use of the Attention key and one simple CL program. This program lets a user profile start a new group job or transfer to an existing group job by just pressing the Attention key. Specify this program as the Attention key handling program on your user profile.

When the Attention key is pressed the first time, the current job is made into a group job. The Transfer Group Job (TFRGRPJOB) command can then be used (F6) to create another group job by using a new group job name. Defining QCMD as the initial group routing program means that the user profile options are used and any initial program is called. (There is no need to use the Set Attention Program [SETATNPGM] command.) On subsequent uses of the Attention key, the user can start another group job or transfer to an existing one.

 PGM DCL VAR(&GRPJOB) TYPE(*CHAR) LEN(10) RTVGRPA GRPJOB(&GRPJOB) IF COND(&GRPJOB = *NONE) THEN(DO) CHGGRPA GRPJOB(GRPJOB1) TEXT('1st Group Job') ENDDO TRFRGRPJOB GRPJOB(*SELECT) INLGRPPGM(QSYS/QCMD) + TEXT('Another Group Job') ENDPGM 

Sorting on Incomplete Fields with OPNQRYF

You can sort a file by part of a field by creating a record format that will contain all fields needed, including the partial field. When you create the file containing this record format, specify MBR(*NONE).

Let's say you're reading a file called FILEA, which has fields FLD1, FLD2 and FLD3 and you want to sort on a substring of FLD2.

First, create a dummy physical file (let's call it WORKFILE) with fields FLD1, FLD2, FLD3 and SORT. Compile your high-level language program to read WORKFILE.

The example shown below illustrates this technique.

 OVRDBF FILE(WORKFILE) TOFILE(FILEA) SHARE(*YES) OPNQRYF FILE((FILEA)) FORMAT(WORKFILE) + MAPFLD((SORT '%SST(FLD2 2 2)')) + KEYFLD((SORT)) CALL PGM(your_program) CLOF OPNID(FILEA) DLTOVR FILE(WORKFILE) 

Recycling OPNQRYF

There are two ways to reuse an Open Query File (OPNQRYF) command when you want more than one program to use the open data path. The easy way is to use the Position Database File (POSDBF) command between the two calls.

 OPNQRYF ... CALL PGM(PGMA) POSDBF OPNID(XYZ) POSITION(*START) CALL PGM(PGMB) 

The other way is to have the second program reposition the file pointer using a command such as RPG's SETLL or COBOL's START verb.

Running SQL/400 Statements in Batch

There is a problem with SQL/400 in that it only allows you to run SQL statements interactively. Fortunately there is a technique which allows you to submit SQL statements to batch. Here's how it's done. Enter the Start Query Manager (STRQM) command. At the Query Manager menu, select option 1 to work with query manager queries. Press F19 if necessary to be sure that the query creation mode is set to SQL. Use option 1 to create a new query. Here you can enter your SQL statement by using the F4 key to prompt just as in SQL/400. After you have completed entering your SQL statement, press F3 to save and exit. To run the SQL statement in batch, select option 9 next to the query you want to run, change the run query mode parameter to '2' and press Enter.

OPNQRYF QRYSLT Templates

As an OPNQRYF beginner, I stumbled along making the usual mistakes as I tried to create the Query Select (QRYSLT) expression. I often received error message CPD3129 ("Missing operand on expression...") and frequently got lost in quotes and *BCATs. However, I have since discovered a technique that can make building QRYSLT expressions easier.

1. I declare a &QRYSLT variable, giving it an initial value that exactly matches what I'll need in the QRYSLT parameter of the OPNQRYF command. In this initial value, I use X's to substitute any character values and 9's to substitute any numeric values.