AS/400-iSeries Starter Kit

Once you've learned to write basic CL programs, you'll probably try to find more ways to use CL as part of your iSeries applications. In contrast to operations languages such as a mainframe's Job Control Language (JCL), which serves primarily to control steps, sorts, and parameters in a job stream, CL offers more. CL is more procedural, supports both database file (read-only) and display file (both read and write) processing, and lets you extend the operating-system command set with your own user-written commands.

In this article, we examine one of those fundamental differences of CL: its ability to process database files. You'll learn how to declare a file, extract the field definitions from a file, read a file sequentially, and position a file by key to read a specific record. With this overview, you should be able to begin processing database files in your next CL program.

Why Use CL to Process Database Files?

Before we talk about how to process database files in CL, let's address the question you're probably asking yourself: "Why would I want to read records in CL instead of in an HLL program?" In most cases, you probably wouldn't. But sometimes, such as when you want to use data from a database file as a substitute value in a CL command, reading records in CL is a sensible programming solution.

Say you want to perform a DspObjD (Display Object Description) command to an output file and then read the records from that output file and process each object using another CL command, such as DspObjAut (Display Object Authority) or MovObj (Move Object). Because executing a CL command is much easier and clearer from a CL program than from an HLL program, you'd probably prefer to write a single CL program that can handle the entire task. We'll show you just such a program a little later, after going over the basics of file processing in CL.

I DCLare!

Perhaps the most crucial point in understanding how CL programs process database files is knowing when you need to declare a file in the program. The rule is simple: If your CL program uses the RcvF (Receive File) command to read a file, you must use the DclF (Declare File) command to declare that file to your program. DclF tells the compiler to retrieve the file and field descriptions during compilation and make the field definitions available to the program. The command has only one required parameter: the file name. To declare a file, you need only code in your program either

DclF File(YourFile)

or

DclF File(YourLib/YourFile)

When using the DclF command, you must remember three implementation rules. First, you can declare only one file - either a database file or a display file - in any CL program. This doesn't mean your program can't operate on other files - for example, using the CpyF (Copy File), OvrDbF (Override with Database File), or OpnQryF (Open Query File) command. It can. However, you can use the RcvF command to process only the file named in the DclF statement.

Second, the DclF statement must come after the Pgm (Program) command in your program and must precede all executable commands (the Pgm and Dcl, or Declare CL Variable, commands are not executable).

The third rule is that the declared file must exist when you compile the CL program. If you don't qualify the file name, the compiler must be able to find the file in the current library list during compilation.

Extracting Field Definitions

When you declare a file to a CL program, the program can access the fields associated with that file. Fields in a declared file automatically become available to the program as CL variables - there's no need to declare the variables separately.

When the file is externally described, the compiler uses the external record-format definition associated with the file object to identify each field and its data type and length. Figure 1 shows the DDS for sample file TestPF. To declare this file in a program, you code

The system then makes the following variables available to the program:

Your program can then use these variables despite the fact that they're not explicitly declared. For instance, you could include in the program the statements

If      ((&Code *Eq 'A') *And +
         (&Number *GT 10))    +
  ChgVar     &Code    ('B')

Notice that when you refer to the field in the program, you must prefix the field name with the ampersand character (&). All CL variables, including those implicitly defined using the DclF command and the file field definitions, require the & prefix when referenced in a program.

What about program-described files - that is, files with no external data definition? Suppose you create the following file using the CrtPF (Create Physical File) command

CrtPF File(DiskFile) RcdLen(258)

and then you declare file DiskFile in your CL program. As it does with externally defined files, the CL compiler automatically provides access to program-described files. Because there's no externally defined record format, however, the compiler recognizes each record in the file as consisting of a single field. That field is always named &FileName, where FileName is the name of the file. Therefore, if you code

DclF DiskFile

your CL program recognizes one field, &DiskFile, with a length equal to DiskFile's record length. You can then extract the subfields with which you need to work. In CL, you extract the fields using the built-in function %Sst (or %Substring). The statements

ChgVar &Field1 (%Sst(&DiskFile  1 10))
ChgVar &Field2 (%Sst(&DiskFile 11 25))
ChgVar &Field3 (%Sst(&DiskFile 50  1))

extract three subfields from &DiskFile's single field.

You'll need to remember two rules when using program-described files. First, you must extract the subfields every time you read a record from the file. Unlike RPG, CL has no global data-structure concept that can be applied for every read. With each read cycle, you must use the %Sst function to retrieve the subfields.

Second, the %Sst function supports only character data. If a certain range of positions holds numeric data, you must retrieve that data as a character field and then move the data into a numeric field. You can use the ChgVar (Change Variable) command to move character data to a numeric field. You must also ensure that the variable's data type matches that of the data you're extracting.

Assume that positions 251-258 in file DiskFile hold a numeric date field eight digits long. To extract that date into a numeric CL variable requires the following Dcl statements and operations:

Dcl    &DateChar   *Char   (   8   )
Dcl    &DateNbr    *Dec    (   8  0)
. . .
ChgVar &DateChar (%Sst(&DiskFile 251 8))
ChgVar &DateNbr  (&DateChar) 

The first ChgVar command extracts the substring from the single &DiskFile field and places it in variable &DateChar. The second ChgVar places the contents of field &DateChar into numeric field &DateNbr.

When you use this technique, be sure that the substring contains only numeric data. If it doesn't, the system will send your program a distinctly unfriendly error message, and your program will end abnormally.

Reading the Database File

Reading a database file in CL is straightforward. The only complication is that you need a program loop to process records one at a time from the file, and CL doesn't directly support structured operations such as Do-While or Do-Until. However, CL does offer the primitive GoTo command and lets you put labels in your code. Using these two capabilities, you can write the necessary loop.

Figure 2 shows part of a program to process file TestPF. Notice that the program uses a manual loop to return to the ReadBegin label and process the next record in the file. The first time through the loop, the RcvF command opens the file and processes the first record. The loop continues until the RcvF statement causes the system to send error message "CPF0864 End of file detected." When the MonMsg (Monitor Message) command traps this message, control skips to ReadEnd, thus ending the loop.

Unlike HLLs, CL doesn't let you reposition the file for additional processing after the program receives an end-of-file message. Although you can execute an OvrDbF command containing a Position parameter after your program receives an end-of-file message, any ensuing RcvF command simply elicits another end-of-file message. Two possible workarounds to this potential problem exist, but each has its restriction.

You can use the first workaround if, and only if, you can ensure that the data in the file will remain static for the duration of the read cycles. The technique involves use of the RtvMbrD (Retrieve Member Description) command. Using this command's NbrCurRcd (CL variable for NBRCURRCD) parameter, you can retrieve into a program variable the number of records currently in the file. Then, in your loop to read records, you can use another variable to count the number of records read, comparing it with the number of records currently in the file. When the two numbers are equal, the program has read the last record in the file.

Although the program has read the last record, the end-of-file condition is not yet set. The system sets this condition and issues the CPF0864 message indicating end-of-file only after attempting to read a record beyond the last record. Therefore, this technique gives you a way to avoid the end-of-file condition. You can then use the PosDbF (Position Database File) command to set the file cursor back to the beginning of the file. Simply specify *Start for the Position parameter, and you can read the file again! Remember, use this technique only when you can ensure that the data will in no way change while you're reading the file.

The second circumvention is perhaps even trickier because it requires a little application design planning. Consider a simple CL program that does nothing more than perform a loop that reads all the records in a database file and exits when the end-of-file condition occurs (i.e., when the system issues message CPF0864). If you replace the statement

MonMsg (CPF0864) Exec(GoTo End)

with

MonMsg    (CPF0864) Exec(Do)
  If        (&Stop *Eq 'Y') GoTo End
  ChgVar    &Stop ('Y')
  TfrCtl    Pgm(YourPgm) Parm(&Stop)
EndDo

where YourPgm is the name of the program containing the command, the system starts the program over again, thereby reading the file again. Notice that with this technique, you must add code to the program to prevent an infinite loop. In addition to the changes shown above, the program should accept the &Stop parameter. Fail to add these groups of code, and each time the system detects end-of-file, the process restarts. You also must add code to ensure that only those portions of the code that you want to be executed are executed.

When possible, if you need to read a database file multiple times, we advise you to construct your application in such a way that you can call multiple CL programs (or one program multiple times, as appropriate). Each of these programs (or instances of a program) can then process the file once. This approach is the clearest and least error-prone method.

File Positioning

One well-kept secret of CL file processing is that you can use it to retrieve records by key . . . sort of. The OvrDbF command's Position parameter lets you specify the position from which to start retrieving database file records. You can position the file to *Start or *End (you can also use the PosDbF command to position to *Start or *End), to a particular record using relative record number, or to a particular record using a key.

To retrieve records by key, you supply four search values in the Position parameter: a key-search type, the number of key fields, the name of the record format that contains the key fields, and the key value. The key-search type determines where in the file the read-by-key begins by specifying which record the system is to read first. The key-search value specifies one of the following five key-search types:

• *KeyB (key-before) — The first record retrieved is the one that immediately precedes the record identified by the other Position parameter search values.
• *KeyBE (key-before or equal) — The first record retrieved is the one identified by the search values. If no record matches those values, the system retrieves the record that matches the largest previous value.
• *Key (key-equal) — The first record retrieved is the one identified by the search values. (If your CL program calls an HLL program that issues a read-previous operation, the called program will retrieve the preceding record.)
• *KeyAE (key-after or equal) — The first record retrieved is the one identified by the search values. If no record matches those values, the system retrieves the record with the next highest key value.
• *KeyA (key-after) — The first record retrieved is the one that immediately follows the record identified by the search values.

As a simple example, let's assume that file TestPF has one key field, Code, and contains the following records:

The statements

OvrDbF Position(*Key 1 TestPFR 'B')
RcvF   RcdFmt(TestPFR)

specify that the record to be retrieved has one key field as defined in DDS record format TestPFR (Figure 1) and that the key field contains the value B. These statements will retrieve the second record (Code = B) from file TestPF. If the key-search type were *KeyB instead of *Key, the same statements would cause the RcvF command to retrieve the first record (Code = A). Key-search types *KeyBE, *KeyAE, and *KeyA would cause the RcvF statement to retrieve records 2 (Code = B), 2 (Code = B), and 3 (Code = C), respectively.

Now let's suppose that the program contains these statements:

OvrDbF Position(&KeySearch 1 TestPFR 'D')
RcvF   RcdFmt(TestPFR)

Here's how each &KeySearch value affects the RcvF results:

• *KeyB — returns record 3 (Code = C)
• *KeyBE — returns record 3 (Code = C)
• *Key — causes an exception error because no match is found
• *KeyAE — returns record 4 (Code = E)
• *KeyA — returns record 4 (Code = E)

Using the Position parameter with a key consisting of more than one field gets tricky, especially when one of the key fields is a packed numeric field. You must code the key string to match the key's definition in the file, and if any key field is other than a character or signed-decimal field, you must code the key string in hexadecimal form.

For example, suppose the key consists of two fields: a one-character field and a five-digit packed numeric field with two decimal positions. You must code the key value in the Position parameter as a hex string equal in length to the length of the two key fields together (i.e., 1 + 3; a packed 5,2 field occupies three positions). For instance, the value

Position(*Key 2 YourFormat X'C323519F')

tells the system to retrieve the record that contains values for the character and packed-numeric key fields of C and 235.19, respectively.

As we've mentioned, a CL program can position the database file and then call an HLL program to process the records. For instance, the CL program can use OvrDbF's Position parameter to set the starting point in a file and then call an RPG program that issues a read or read-previous to start reading records at that position.

Having this capability doesn't necessarily mean you should use it, though. One of our fundamental rules of programming is this: Make your program explicit and its purpose clear. Thus, we avoid using the OvrDbF or PosDbF command to position a file before we process it with an HLL program when we can more explicitly and clearly position the file within the HLL program itself. There's just no good reason to hide the positioning function in a CL program that may not clearly belong with the program that actually reads the file. However, when you process a file in a CL program, positioning the file therein can simplify the solution.

What About Record Output?

Just about the time you get the hang of reading database files, you suddenly realize that your CL program can't perform any other form of I/O with them. CL provides no direct support for updating, writing, or printing records in a database file.

Some programmers use command StrQMQry (Start Query Management Query) to execute a query management query or use the RunSQLStm (Run SQL Statement) command to effect one of these operations from within CL. To use these techniques, you must first create the query management query or enter the SQL source statements to execute with RunSQLStm.

A Useful Example

Now that you know how to process database files in a CL program, let's look at a practical example. Security administrators would likely find a program that prints the object authorities for selected objects in one or more libraries useful. Figure 3A shows the command definition for the PrtObjAut (Print Object Authorities) command, which does just that. Figure 3B shows PrtObjAut's command processing program (CPP), PrtObjAut1.

Notice that the CPP declares file QADspObj in the DclF statement. This IBM-supplied file resides in library QSys and is a model for the output file that the DspObjD command creates. In other words, when you use DspObjD to create an output file, that output file is modeled on QADspObj's record format and associated fields. In the CPP, the DspObjD command creates output file ObjList, whose file description includes record format QLiDObjD and fields from the QADspObj file description.

Because we declare file QADspObj in the program, that's the file we must process. (Remember: You can declare only one file in the program, and file ObjList did not exist at compile time.) The CPP uses the OvrDbF command to override QADspObj to newly created file ObjList in library QTemp. When the RcvF command reads record format QLiDObjD, the override causes the RcvF to read records from file ObjList.

As it reads each record, the CL program substitutes data from the appropriate fields into the DspObjAut command and prints a separate authority report for each object represented in the file.

We're sure you'll find uses for the CL techniques you've learned in this article. Processing database files in CL is a handy ability that, at times, may be just the solution you need.

This article is excerpted from the book Starter Kit for the IBM iSeries and AS/400 by Gary Guthrie and Wayne Madden (29th Street Press, 2001). For more information about the book, see http://www.iseriesnetwork.com/str/books/uniquebook2.cfm?NextBook=187.

Gary Guthrie is a senior technical editor for iSeries NEWS and an iSeries technical support consultant. You can reach him at gguthrie@iseriesnetwork.com.

Wayne Madden is editor in chief of iSeries NEWS and the iSeries Network. He is also vice president and group publisher of Penton Technology Media's IBM Technology Group in Loveland, Colorado.