Sat, Jul
4 New Articles

Using Dynamic SQL in CL: Part 2--Retrieving a Data Value

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times


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

DCL        VAR(&NO_ORDERS)  TYPE(*DEC) LEN(10 0)

             WHERE('OrderDate=Current_Date') +

/* Convert Alpha result back to numeric  */

/* Skip processing, if no orders were entered 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:

SELECT CAST(COUNT(*) AS CHAR(128)) /*LookupExp */
  INTO &NO_ORDERSA /*ReturnVar */
  FROM ORDERS /*Table     */

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:


/* Get Company and Contact Name from Customer Master */
             || &CUSTOMER |< '''') RETURNVAR(&CUSTDATA)


/* Continue on with e-mailing the spool file */

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:

/* Force VARCHAR variables to fixed length CHAR */
             WHERE('CUSTOMERID = ''' || &CUSTOMER |< + 
             '''') RETURNVAR(&CUSTDATA) 

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


             JOIN ORDERDETAILS B ON + 
             ORDERINFO') WHERE(*none) +

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.

SELECT CAST(lookupexp AS CHAR(128))
  FROM table
 WHERE [where]

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:

C+ FETCH CLOOKUP INTO :parmReturn:NullResult

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.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. He can be contacted at This email address is being protected from spambots. You need JavaScript enabled to view it..



Support MC Press Online

$0.00 Raised:

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: