Problems in life are not always simple. This is especially true in the computer programming realm. However, there is one shining exception to this rule, and it lies in the power of SQL's EXECUTE IMMEDIATE statement. This little gem will allow you to unlock untold treasures in your programming world by allowing you to execute dynamic SQL statements anywhere, including CL programs.

As Simple as They Come

EXECUTE IMMEDIATE is grandiose in its simplicity and power. It allows the programmer to run just about any SQL statement that can be executed in a dynamic SQL environment (except for the SELECT statement). I call these kinds of SQL statements "action" statements. Here are the SQL statements that EXECUTE IMMEDIATE can process:

• ALTER
• CALL
• COMMENT
• COMMIT
• CREATE
• DELETE
• DROP
• GRANT
• INSERT
• LABEL
• LOCK TABLE
• RENAME
• REVOKE
• ROLLBACK
• SET PATH
• SET TRANSACTION
• UPDATE

What do you need to harness the power of this statement? Nothing, except the DB2 Query Manager and the SQL Development Kit product (5722ST1), a small high-level language (HLL) program with embedded SQL and an SQL statement to run. For those who don't have the SQL Development Kit, I've included here a save file containing the command and program objects shown in this article. Restoring the SAVF requires a minimum OS/400 level of V5R1.

Figure 1 shows an example of how EXECUTE IMMEDIATE can be used in an RPG program:

Figure 1: Use EXECUTE IMMEDIATE in an RPG program.

The only parameter EXECUTE IMMEDIATE expects is a character variable containing an SQL statement (limited to those shown in the list above). In the program snippet in Figure 1, the SQL statement is built dynamically and then processed by EXECUTE IMMEDIATE. The character variable must contain a complete SQL statement without parameters; host variables and parameter markers are forbidden.

In embedded SQL programming, an SQLCOD variable (as shown in Figure 1) contains a return code with the status of the SQL statement:

• A negative SQLCOD indicates that an error occurred with the statement (more on this later).
• A positive SQLCOD indicates that the statement generated a warning message.
• An SQLCOD of zero means that the statement ran without incident.

You don't have to define SQLCOD in your D-specs. The SQL precompiler will create this and other variables automatically inside a special data structure called the SQL communication area (SQLCA). For more information on the SQLCA and the information it provides, see Appendix B of the DB2 Universal Database for iSeries SQL Reference guide. For more information on SQLCODs, see the DB2 UDB for iSeries SQLCODEs and SQLSTATEs manual.

You may be wondering why you would use the slower EXECUTE IMMEDIATE statement in an RPG program when you can just use high-performing embedded SQL. Good question. Here are two reasons for using EXECUTE IMMEDIATE:

• You can use EXECUTE IMMEDIATE when the statement's WHERE criteria is unknown at compile time. In the example above, the DELETE statement's WHERE clause is unknown until run time. Using EXECUTE IMMEDIATE saves the trouble of having to embed SQL statements for every possible condition.
• You can use EXECUTE IMMEDIATE in an RPG program to allow non-SQL environments, such as a CL program, to run SQL statements.

The RUNSQL Utility

The RUNSQL utility leverages the power of EXECUTE IMMEDIATE to allow SQL statements to be run on a command line or in a CL program. The command accepts one parameter: an SQL statement. The source for the RPG program and command interfaces for this utility are included in this article's downloadable code.

Figure 2 shows excerpts from the code for RPG program RUNSQLR.

Figure 2: Here are some snippets from RUNSQLR.

That's about all there is to the program. The program will receive an SQL statement from the command interface and attempt to run it. If the SQL statement fails to execute properly, a subprocedure is called that sends an *Escape message to the calling program. Due to the limitations of the OS/400 command interface, an SQL statement may be no longer than 5,000 characters.

Benefits of RUNSQL

By now, you may be questioning the usefulness of the utility. After all, can't we run SQL statements from a command line or CL by using either the Start Query Management Query (STRQMQRY) or the Run SQL Statement (RUNSQLSTM) command?

Yes, you can, but RUNSQL offers the following advantages over these methods:

• RUNSQLSTM can't receive parameters or have its contents dynamically modified.
• RUNSQLSTM has poor error-handling methods.
• RUNSQLSTM doesn't allow conditional statements to control statement execution.
• RUNSQLSTM doesn't allow a CL program to retrieve the number of rows affected.
• Query Management queries can execute only one statement.
• Query Management queries require the creation of objects external to the CL (in other words, you will have yet more objects on your system, and if you're trying to debug a problem in a CL job stream, you may have to move to the query manager environment just to look at an SQL statement being run).
• Query Management queries don't report the rows affected by a given data modification statement.

This is not to say RUNSQL should be used all the time. Both RUNSQLSTM and QM queries have their place and advantages. For instance, if you have a single SQL statement that will be executed by several CL programs, it would be better to place the SQL statement in a QM query object than to duplicate the same statement in several places with RUNSQL.

Sample Usage

Here are some potential uses of the utility:

• Replace read-only OPNQRYF statements. SQL is generally easier to use than Open Query File (OPNQRYF). Rather than build an OPNQRYF string to select data, simply construct an SQL statement. Note that RUNSQL will be useful in this situation only if your OPNQRYF is creating a read-only open data path (ODP).
• Replace data extraction programs. Before SQL became popular, it was common to have RPG programs build "work files" for extracting and sorting data. These extraction programs can often easily be replaced. Don't ignore the fact that you can use a single SQL statement to replace many aging RPG programs that often run in CL job streams. For example, a few years ago, I was involved in a project to upgrade a client's homegrown software. This software featured many RPG programs that did nothing but loop through records to update a flag--a good candidate for replacement with SQL. I was able to eradicate many of these RPG programs by replacing them with a single SQL statement in the CL.
• Do dynamic file processing in CL. CL programs often loop through a file to process data. If you have work that needs to be done on an existing file (records eliminated or updated or perhaps a view created), RUNSQL can perform these tasks; just issue the RUNSQL command before executing the first RCVF on the file. Keep in mind that CL programs can only process files that limit column data types to CHAR, NUMERIC, or DECIMAL and don't contain NULLs. Additionally, CL can normally just read files, but RUNSQL adds the ability to update files by issuing an UPDATE statement! Further, RUNSQL will allow you to create a work file based on multiple files by doing a join.
• Process several successive SQL statements. For those without the SQL Dev Kit, this CL can do light processing work where an embedded SQL program or several successive QM queries might normally be used.

Figures 3 and 4 show excerpts from two sample CL programs that demonstrate RUNSQL.

Figure 3: These statements can be used as a replacement for an OPNQRYF or as a file to be processed by an RPG program. If you use this technique to process a file in CL, the CREATE TABLE command needs to be executed before compiling.

Figure 4: This month-end job clears and recalculates AR balances on the customer master.

Unfortunately, the formatting of SQL statements inside of a CL program will not always be pretty.

Dynamically Build SQL Statements

As with OPNQRYF, a variable or expression can be used with RUNSQL. Let's look at how this can improve the example in Figure 4. Instead of running the SQL statements against the CURRENT_DATE register, the operator will pass the month-end date via a command (these dates will be formatted as a 7A in CYYMMDD format).

You can convert this work date to an ISO date with separators (10A in YYYY-MM-DD format), which can then be inserted in the SQL statement that controls the date calculation.

As with other command parameters that can accept expressions, make sure to enclose the expression in parentheses, and watch out for the ominous single-quote snafus!

Determining the Number of Rows Affected

Sometimes, it may be necessary to determine how many rows were processed for a given data modification statement (DELETE, INSERT, UPDATE). To facilitate this need, a second command, called RUNSQLRA, is included in the downloadable code.

RUNSQLRA is the same as the RUNSQL command with the exception that it has a second parameter. This second parameter is a DEC(15,0) "return variable" parameter called ROWSAFFECT that will return the rows affected by an SQL statement into a CL variable. Figure 5 shows an example of how it might be used in a nightly processing program where, due to a holiday or some other circumstance, there is no data to be processed:

Figure 5: RUNSQLRA's ROWSAFFECT parameter returns the rows affected by an SQL statement.

When the RPG program issues an EXECUTE IMMEDIATE data modification statement, such as an UPDATE, the number of rows changed by the statement are reported back through an array variable in the SQLCA called SQLErrD. Element three of the array holds the rows affected. The RPG program returns the contents of this variable to the command. For SQL statements that don't modify data, ROWSAFFECT will return a zero.

The RPG program handles returning the rows affected back to the CL program via a second program parameter called parmRowsAffect. The %PARMS built-in function is used to sense the number of parameter passed. If two parameters are passed to the program, it sets the parmRowsAffect variable as follows:

When an RPG program is called, not all of the parms have to be passed. However, the RPG program can't reference a parm that hasn't been passed without causing an error, which is why parmRowsAffect is modified only on the condition that it was passed to the program.

Error Handling

As mentioned earlier, the SQLCOD variable will return a negative number in the event of an error condition. How can you make use of this negative number to communicate an intelligent message to the calling CL program? When an SQL statement fails, SQLCOD contains the message number of a specific error message found in the QSQLMSG message file. The only problem is that the number is negative. However, by ignoring the sign and prefixing the literal 'SQL', you can come up with the message ID of the error message as follows:

Now that you have the message identifier, the SQL communications area supplies you with the message data and message data length in the SQLERM and SQLERL variables. These three pieces of information allow you to successfully relay the SQL error as an *ESCAPE message to the calling program via the QMHSNDPM (Send Program Message) API.

Default Options

The RUNSQLR program has been set with several precompiler options:

• Commit=*NONE--This option specifies that, by default, the SQL statements executed will not be under any form of commitment control. Use RUNSQL to issue the SET TRANSACTION ISOLATION LEVEL statement to override this setting.
• 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 rather than the user's authority. For example, if program RUNSQLR is owned by QPGMR, then all SQL statements will run under QPGMR's authority. Be careful who is allowed to run this command from the command line. For example, a user executing RUNSQL('CALL QCMD') owned by QPGMR will adopt authority to do anything on the command line that QPGMR can do. If this option is set to *USRPRF, then the SQL statements will run under the user's authority. Also, DynUsrPrf is not affected by the UsrPrf keyword, which controls whether the program itself is run under the user's or the owner's authority.
• DATFMT and TIMFMT--The DATFMT and TIMFMT precompiler options are left defaulted to *JOB. Keep this in mind, because it affects how you specify date and time constants in your SQL statements.

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 RUNSQLR embedded SQL program but not to run it.) The objects were saved with QPGMR ownership so take careful note of the security issues. Be sure to change the object ownership to a profile with the security restrictions necessary for your environment.

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.

Combining SQL and CL

Using RUNSQL to unleash the power and utility of SQL directly within a CL program will enhance your environment by...

• Eliminating old RPG programs or unnecessary QM query objects
• Allowing you to view the SQL statements directly within a CL program
• Providing a way to dynamically build statements
• Giving the ability to monitor for error messages
• Allowing transaction-based updates to occur
• Letting the program know how many rows were affected by a given statement

If you're familiar with SQL and do much CL programming, this utility is likely to achieve a prominent place in your CL programs. The best news is that we're not through. In Part 2 of this series, I'll cover another dynamic SQL statement that will allow a CL to dynamically construct and run a limited SELECT statement.

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