Although the iSeries' Control Language (CL) boasts many features, it does have a few drawbacks. One of these drawbacks is its limited file processing capability--namely, it can process only one "read-only" file per program. If you've done many batch CL programs, you're familiar with this "brick wall" limitation.

To control batch jobs, you often need to read pieces of data from multiple files in order to obtain all of the information necessary for processing your job stream. For example, a CL program may need the current period's ending date retrieved from a G/L control file or an invoice print flag retrieved from a customer master file. Another example could be the need to determine whether there are any records available for processing so that programs don't unnecessarily run when there's no data.

A CL program "smart" enough to look up a date or check a file to see if there are records available for processing would be useful. CL itself doesn't offer much help to deal with these types of issues. Because of CL's single-file limitation and poor key-processing ability, many an RPG program has been written to retrieve simple pieces of information for a CL program. How can we bypass this limitation and retrieve data directly into our CL programs without having to resort to a secondary high-level language (HLL) program?

One possible answer to this quagmire was covered in "Using Dynamic SQL in CL: Part 1--Running Action Queries." In this article, I presented the RUNSQL utility as a way to use SQL to dynamically create a table for processing by a CL program. Remember, a limitation of RUNSQL is that it can run only non-SELECT statements. So to use RUNSQL with CL, you must create a table, populate the table, and then process the table. Therefore, this technique doesn't work well for the task of retrieving unrelated bits of data from multiple files.

Another way of solving this problem is to create a utility that retrieves a value directly from a table into a CL variable. It just so happens that SQL can help us here again with its PREPARE statement. With PREPARE, we can dynamically build a SELECT statement in a string variable, validate the statement, and retrieve the result of the SELECT. By making the PREPARE statement available to a CL program (similar to how the RUNSQL utility made EXECUTE IMMEDIATE available to CL), CL can take advantage of SQL's data retrieval capabilities.

To use SQL's PREPARE, we need to embed SQL in an HLL program, which requires the DB2 Query Manager and SQL Development Kit product (5722ST1). For those of you who don't have this product installed, I've created a save file that contains the utility's program and command objects.

Presented here is a utility called LOOKUP(110903SansoLookup.zip and 110903SansoLookupUtility.zip) that allows us to retrieve a value from a SELECT statement. LOOKUP takes advantage of SQL's data retrieval capability to return a single value from a table. In order to use LOOKUP, we need to pass three parameters: LOOKUPEXP (lookup expression), TABLE, and WHERE:

• LOOKUPEXP--This parameter expects a single column name or expression for a given table. This value can be any data type. Regardless of the data type expressed here, program LOOKUPR will always change the type to CHAR(128). If the data is numeric, for example, it is up to the programmer to issue the CHGVAR command to place the character representation of the data back into an acceptably defined numeric CL variable. If you need to select more than one column from a single row, make sure the data is changed to type CHAR and concatenated together using the concatenate operator (||). LOOKUPEXP is limited to 256 characters.
• TABLE--This parameter expects a table name to be specified for the lookup. Although a single table is acceptable, a table expression can be specified here, provided it is enclosed in parentheses and contains a correlation name. The supplied table is limited to 128 characters.
• WHERE--This parameter expects criteria to be supplied as though it were part of the WHERE clause. Do not include the word WHERE here because the LOOKUPR program adds it automatically. This parameter is optional and is limited to 256 characters. Use the special value *NONE if you don't have criteria to specify.

With these three pieces of information, LOOKUP can construct a SELECT statement to process a request and return the result to a CL variable. A fourth parameter, RETURNVAR, expects a *CHAR 128 variable to hold the result of the LOOKUP. Because of CL's limited ability to handle different data types, all results are forced to character. (More on this later.)

As a side note, Microsoft Access users will immediately recognize that the LOOKUP command was patterned after Access' DLOOKUP domain function, a useful function that allows users to retrieve single pieces of information without going through the pains of coding an entire query to fetch a single column.

Let's look at a few examples of how LOOKUP enhances CL's ability to make intelligent processing decisions.

Example 1: Retrieve count to detect if there are orders to process today

The result of the count is passed back to a *CHAR 128 variable. It doesn't do much good to hold a number in a character variable, so CHGVAR converts from character to numeric in this circumstance. For the longest time, I was under the impression that CHGVAR required character variables to be right-justified and zero-filled to be successful--fortunately, that assumption was false. This extra CHGVAR step is required to convert the character representation of a numeric field back to numeric.

Behind the scenes, LOOKUP actually converts these parameters into an SQL statement similar to the following:

Of course, we can't actually embed a SELECT INTO statement into a CL program, but the LOOKUP utility provides CL this type of functionality.

Example 2: Retrieve multiple columns from a single row
Suppose you have a CL program that calls an RPG program to create and print an invoice for a customer. As e-business blossoms, we now want to email the invoice directly to the customer instead of printing it. The CL program has the customer ID passed as a parameter. In the message body of the email, you want to place the customer's ID, company name, and contact name. Normally, you'd modify the RPG program or write a little utility program to retrieve the name and contact values on behalf of the CL. However, LOOKUP is well-suited for this task:

In this example, it's assumed that columns COMPANYNAME and CONTACTNAME in table CUSTOMERS are defined as CHAR. If they were defined as VARCHAR, they'd have to be CAST to CHAR first because CL works only with fixed-length strings:

As illustrated above, the technique required to fetch multiple columns or expressions from a single row requires three steps:

1. Convert each column to fixed-length character.
2. Use the concatenate operator (||) to combine the values into one long column.
3. Make the CL program segregate the values back into their individual components.

This method has performance advantages over running multiple LOOKUP commands because the PREPARE statement is a relatively big resource hog (so don't overuse it!). You may not place multiple columns in the LOOKUPEXP parameter as you would in a SELECT list because the program is designed to fetch only one column.

Actually, we could take this email example one step further by including the customer's email address in the LOOKUP. If an email address exists in the customer master, then CL will email the invoice. Otherwise, it will print it. This utility adds loads of decision-making capabilities to your CL programs!

Observe that expressions and CL variables are allowed in all of the parameters except RETURNVAR so that criteria and columns may be dynamically expressed. Remember, parameter expressions...

• must be enclosed in parentheses
• may only involve character variables
• must be properly delimited with single quotes when joining literals and variables

Example 3: Using a nested SELECT as the table parameter

In this final example, a table expression is given in the TABLE parameter as denoted by the use of the parentheses and the OrderInfo correlation name.

As with the RUNSQL utility, an advantage of using LOOKUP is that your CL-related processing can stay right in the CL program. You don't have to peek out at other programs or query objects to find out how a simple variable gets its content.

The Internals of LOOKUP

Program LOOKUPR makes use of the SQL PREPARE statement. As previously noted, PREPARE accepts a string variable containing an SQL statement and turns it into a form that can be executed by the DB2 engine. We'll focus on only one use of PREPARE, which involves transforming a SELECT statement string into a cursor. (Actually, PREPARE can do more than this.)

As noted above, the LOOKUP command will create a single-column SELECT statement based on a provided column name, a table name, and optional WHERE criteria in the following manner.

Notice that, regardless of the data type of the lookup expression, the CAST function always forces the data to CHAR(128) so that it can be used by the CL program. CAST will left-justify numeric data. The RPG program places this text statement in variable "SQL."

Once the SELECT string is built, PREPARE is executed to validate the statement and to prepare it to run as a cursor:

Is it just me, or is it just too easy to hand off a text string and get back a prepared statement that can run?

The validation done by PREPARE is more than a mere syntax check; any column names and table names in the statement must be correct. Each prepared statement has a name. In this case, LOOKUP is the name assigned to reference the executable form of the SQL string. (Don't confuse this LOOKUP label with the name of the utility itself!) While PREPARE will normally allow parameter markers--signified by a question mark (?)--the LOOKUP utility isn't designed to utilize them.

When PREPARE is done, a cursor named CLOOKUP is created based on the prepared statement named LOOKUP. Using a cursor in this context means that LOOKUP must be a prepared SELECT statement. Further, this statement is not executable but is necessary for the sake of the pre-compiler so that it knows how the prepared statement will be used.

As expected, once the cursor is defined, we open it:

And we fetch data from it:

As evidenced by the FETCH shown here, only one column will be returned. The NullResult host variable will contain a negative one (-1) if the fetched column happens to be NULL; otherwise, it will contain a zero (0). Once the data is fetched, the cursor is closed and any additional rows are ignored.

While in theory the LOOKUP command can easily be expanded to fetch multiple columns at one time, I opted not to do this for the sake of simplicity and for ease of dealing with NULLs.

Default Options

The same default pre-compiler options and behaviors as enumerated in "Using Dynamic SQL in CL: Part 1--Running Action Queries" apply here as well:

• Commit=*NONE This option specifies that the SELECT statements will not be run under any form of commitment control.
• Naming=*SYS The naming convention controls how qualified table names are coded in an SQL statement. With the *SYS naming convention, the forward slash (/) is used to separate the library and table names. The alternative *SQL naming convention requires that a period (.) be used as the separator.
• DynUsrPrf=*OWNER This setting instructs SQL to run dynamic SQL statements under the program owner's authority. PREPARE is a dynamic statement. For example, if program LOOKUPR is owned by QPGMR, then the SELECT requests will run under QPGMR's authority. If this option is changed to *USRPRF, then the SELECT statements will run under the user's authority. Remember, the UsrPrf keyword, which normally controls program adopted authority, has no effect on the authority granted during the execution of dynamic SQL statements.
• ClsSQLCsr=*ENDMOD This option closes the cursor when the module ends. It is specified to help prevent problems with a cursor being inadvertently left open, which could affect subsequent calls to the program.
• The DATFMT and TIMFMT pre-compiler options are defaulted to *JOB. These settings affect the default format for dates and times.

Error Handling

Further, note the following information, which you will need when monitoring errors:

• If the value from LOOKUP is NULL, message CPF9897 will be issued as an *ESCAPE message.
• If no data is found based on your criteria, message SQL0100 will be issued as an *ESCAPE message.
• Any errors encountered during the PREPARE phase will be propagated back to the calling program in the form of an *ESCAPE message. These messages will normally begin with "SQL."

For more notes on error-handling in an embedded SQL program, refer to the explanations given in Part 1 of this series.

Downloading the Utility

As mentioned earlier, the objects for this utility are available for download in a save file for the benefit of those who don't have the DB2 SQL Development Kit installed. (This product is required to compile the LOOKUPR embedded SQL program but not to run it.) The objects were saved with QPGMR ownership, so take careful note of the security issues with this level of authority in your environment. If necessary, change the owner of the LOOKUPR program (using the CHGOBJOWN command) to use a profile with lesser authority. OS/400 V5R1 or higher is required to restore from the save file.

To restore the utility on your system, upload the save file to your system using FTP and execute the RSTOBJ command:

For help on using FTP to upload a save file to your iSeries, see "FTP: Tricks of the Transfer."

Make Your CL Programs Smarter

The LOOKUP utility allows your CL programs to peer into files, which will better equip them to control batch and interactive processing. Further, LOOKUP eliminates the need to have single-purpose HLL programs do these lookups. This results in two things: fewer programs on your system and CL programs that are easier to understand, since the data retrieval is done right in the CL.

Once again, dynamic SQL makes the programmer's life much easier.

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions