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( and 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



Support MC Press Online





  • White Paper: Node.js for Enterprise IBM i Modernization

    SB Profound WP 5539

    If your business is thinking about modernizing your legacy IBM i (also known as AS/400 or iSeries) applications, you will want to read this white paper first!

    Download this paper and learn how Node.js can ensure that you:
    - Modernize on-time and budget - no more lengthy, costly, disruptive app rewrites!
    - Retain your IBM i systems of record
    - Find and hire new development talent
    - Integrate new Node.js applications with your existing RPG, Java, .Net, and PHP apps
    - Extend your IBM i capabilties to include Watson API, Cloud, and Internet of Things

    Read Node.js for Enterprise IBM i Modernization Now!


  • Profound Logic Solution Guide

    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 companyare not aligned with the current IT environment.

    Get your copy of this important guide today!


  • 2022 IBM i Marketplace Survey Results

    Fortra2022 marks the eighth edition of the IBM i Marketplace Survey Results. Each year, Fortra captures data on how businesses use the IBM i platform and the IT and cybersecurity initiatives it supports.

    Over the years, this survey has become a true industry benchmark, revealing to readers the trends that are shaping and driving the market and providing insight into what the future may bring for this technology.

  • Brunswick bowls a perfect 300 with LANSA!

    FortraBrunswick is the leader in bowling products, services, and industry expertise for the development and renovation of new and existing bowling centers and mixed-use recreation facilities across the entertainment industry. However, the lifeblood of Brunswick’s capital equipment business was running on a 15-year-old software application written in Visual Basic 6 (VB6) with a SQL Server back-end. The application was at the end of its life and needed to be replaced.
    With the help of Visual LANSA, they found an easy-to-use, long-term platform that enabled their team to collaborate, innovate, and integrate with existing systems and databases within a single platform.
    Read the case study to learn how they achieved success and increased the speed of development by 30% with Visual LANSA.


  • Progressive Web Apps: Create a Universal Experience Across All Devices

    LANSAProgressive Web Apps allow you to reach anyone, anywhere, and on any device with a single unified codebase. This means that your applications—regardless of browser, device, or platform—instantly become more reliable and consistent. They are the present and future of application development, and more and more businesses are catching on.
    Download this whitepaper and learn:

    • How PWAs support fast application development and streamline DevOps
    • How to give your business a competitive edge using PWAs
    • What makes progressive web apps so versatile, both online and offline



  • The Power of Coding in a Low-Code Solution

    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:

    • Discover the benefits of Low-code's quick application creation
    • Understand the differences in model-based and language-based Low-Code platforms
    • Explore the strengths of LANSA's Low-Code Solution to Low-Code’s biggest drawbacks



  • Why Migrate When You Can Modernize?

    LANSABusiness 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.
    In this white paper, you’ll learn how to think of these issues as opportunities rather than problems. We’ll explore motivations to migrate or modernize, their risks and considerations you should be aware of before embarking on a (migration or modernization) project.
    Lastly, we’ll discuss how modernizing IBM i applications with optimized business workflows, integration with other technologies and new mobile and web user interfaces will enable IT – and the business – to experience time-added value and much more.


  • UPDATED: Developer Kit: Making a Business Case for Modernization and Beyond

    Profound Logic Software, Inc.Having trouble getting management approval for modernization projects? The problem may be you're not speaking enough "business" to them.

    This Developer Kit provides you study-backed data and a ready-to-use business case template to help get your very next development project approved!

  • What to Do When Your AS/400 Talent Retires

    FortraIT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators is small.

    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:

    • Why IBM i skills depletion is a top concern
    • How leading organizations are coping
    • Where automation will make the biggest impact


  • Node.js on IBM i Webinar Series Pt. 2: Setting Up Your Development Tools

    Profound Logic Software, Inc.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. In Part 2, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Attend this webinar to learn:

    • Different tools to develop Node.js applications on IBM i
    • Debugging Node.js
    • The basics of Git and tools to help those new to it
    • Using as a pre-built development environment



  • Expert Tips for IBM i Security: Beyond the Basics

    SB PowerTech WC GenericIn this session, IBM i security expert Robin Tatam provides a quick recap of IBM i security basics and guides you through some advanced cybersecurity techniques that can help you take data protection to the next level. Robin will cover:

    • Reducing the risk posed by special authorities
    • Establishing object-level security
    • Overseeing user actions and data access

    Don't miss this chance to take your knowledge of IBM i security beyond the basics.



  • 5 IBM i Security Quick Wins

    SB PowerTech WC GenericIn today’s threat landscape, upper management is laser-focused on cybersecurity. You need to make progress in securing your systems—and make it fast.
    There’s no shortage of actions you could take, but what tactics will actually deliver the results you need? And how can you find a security strategy that fits your budget and time constraints?
    Join top IBM i security expert Robin Tatam as he outlines the five fastest and most impactful changes you can make to strengthen IBM i security this year.
    Your system didn’t become unsecure overnight and you won’t be able to turn it around overnight either. But quick wins are possible with IBM i security, and Robin Tatam will show you how to achieve them.

  • Security Bulletin: Malware Infection Discovered on IBM i Server!

    SB PowerTech WC GenericMalicious programs can bring entire businesses to their knees—and IBM i shops are not immune. It’s critical to grasp the true impact malware can have on IBM i and the network that connects to it. Attend this webinar to gain a thorough understanding of the relationships between:

    • Viruses, native objects, and the integrated file system (IFS)
    • Power Systems and Windows-based viruses and malware
    • PC-based anti-virus scanning versus native IBM i scanning

    There are a number of ways you can minimize your exposure to viruses. IBM i security expert Sandi Moore explains the facts, including how to ensure you're fully protected and compliant with regulations such as PCI.



  • Encryption on IBM i Simplified

    SB PowerTech WC GenericDB2 Field Procedures (FieldProcs) were introduced in IBM i 7.1 and have greatly simplified encryption, often without requiring any application changes. Now you can quickly encrypt sensitive data on the IBM i including PII, PCI, PHI data in your physical files and tables.
    Watch this webinar to learn how you can quickly implement encryption on the IBM i. During the webinar, security expert Robin Tatam will show you how to:

    • Use Field Procedures to automate encryption and decryption
    • Restrict and mask field level access by user or group
    • Meet compliance requirements with effective key management and audit trails


  • Lessons Learned from IBM i Cyber Attacks

    SB PowerTech WC GenericDespite the many options IBM has provided to protect your systems and data, many organizations still struggle to apply appropriate security controls.
    In this webinar, you'll get insight into how the criminals accessed these systems, the fallout from these attacks, and how the incidents could have been avoided by following security best practices.

    • Learn which security gaps cyber criminals love most
    • Find out how other IBM i organizations have fallen victim
    • Get the details on policies and processes you can implement to protect your organization, even when staff works from home

    You will learn the steps you can take to avoid the mistakes made in these examples, as well as other inadequate and misconfigured settings that put businesses at risk.



  • The Power of Coding in a Low-Code Solution

    SB PowerTech WC GenericWhen 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:

    • Discover the benefits of Low-code's quick application creation
    • Understand the differences in model-based and language-based Low-Code platforms
    • Explore the strengths of LANSA's Low-Code Solution to Low-Code’s biggest drawbacks



  • Node Webinar Series Pt. 1: The World of Node.js on IBM i

    SB Profound WC GenericHave 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.
    Part 1 will teach you what Node.js is, why it's a great option for IBM i shops, and how to take advantage of the ecosystem surrounding Node.
    In addition to background information, our Director of Product Development Scott Klement will demonstrate applications that take advantage of the Node Package Manager (npm).
    Watch Now.

  • The Biggest Mistakes in IBM i Security

    SB Profound WC Generic The Biggest Mistakes in IBM i Security
    Here’s the harsh reality: cybersecurity pros have to get their jobs right every single day, while an attacker only has to succeed once to do incredible damage.
    Whether that’s thousands of exposed records, millions of dollars in fines and legal fees, or diminished share value, it’s easy to judge organizations that fall victim. IBM i enjoys an enviable reputation for security, but no system is impervious to mistakes.
    Join this webinar to learn about the biggest errors made when securing a Power Systems server.
    This knowledge is critical for ensuring integrity of your application data and preventing you from becoming the next Equifax. It’s also essential for complying with all formal regulations, including SOX, PCI, GDPR, and HIPAA
    Watch Now.

  • Comply in 5! Well, actually UNDER 5 minutes!!

    SB CYBRA PPL 5382

    TRY 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.

    Request your trial now!

  • Backup and Recovery on IBM i: Your Strategy for the Unexpected

    FortraRobot automates the routine 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:
    - Simplified backup procedures
    - Easy data encryption
    - Save media management
    - Guided restoration
    - Seamless product integration
    Make sure your data survives when catastrophe hits. Try the Robot Backup and Recovery Solution FREE for 30 days.

  • Manage IBM i Messages by Exception with Robot

    SB HelpSystems SC 5413Managing messages on your IBM i can be more than a full-time job if you have to do it manually. 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:
    - Automated message management
    - Tailored notifications and automatic escalation
    - System-wide control of your IBM i partitions
    - Two-way system notifications from your mobile device
    - Seamless product integration
    Try the Robot Message Management Solution FREE for 30 days.

  • Easiest Way to Save Money? Stop Printing IBM i Reports

    FortraRobot 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:

    - Automated report distribution
    - View online without delay
    - Browser interface to make notes
    - Custom retention capabilities
    - Seamless product integration
    Rerun another report? Never again. Try the Robot Report Management Solution FREE for 30 days.

  • Hassle-Free IBM i Operations around the Clock

    SB HelpSystems SC 5413For over 30 years, Robot has been a leader in systems management for IBM i.
    Manage your job schedule with the Robot Job Scheduling Solution. Key features include:
    - Automated batch, interactive, and cross-platform scheduling
    - Event-driven dependency processing
    - Centralized monitoring and reporting
    - Audit log and ready-to-use reports
    - Seamless product integration
    Scale your software, not your staff. Try the Robot Job Scheduling Solution FREE for 30 days.