29
Fri, Nov
0 New Articles

Using Dynamic SQL in CL: Part 1--Running Action Queries

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

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:

D Order_No        S             10I 0
D Inv_No          S             10I 0
D SQL             S           1024    Varying

C                   Eval      SQL='DELETE FROM Invoices '
C                   Select 
C                   When      Inv_No>*Zero 
C                   Eval      SQL=SQL+'WHERE Inv_No='+
C                                     %CHAR(Inv_No) 
C                   When      Order_No>*Zero 
C                   Eval      SQL=SQL+'WHERE Order_No='+
C                                     %CHAR(Order_No)
C                   EndSl

C/EXEC SQL
C+ EXECUTE IMMEDIATE :SQL
C/END-EXEC

C                   If        SQLCOD<*Zero
 * Error Processing
C                   EndIf

 

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.

D parmSQL         S           5000    Varying

C     *Entry        PList 
C                   Parm                    parmSQL 
 * 
 * Execute statement passed from command line
 * 
C/EXEC SQL 
C+ EXECUTE IMMEDIATE :parmSQL 
C/END-EXEC 
C                   If        SQLCOD<*ZERO
C                   CallP(E)  SQLErrorMsg
C                   EndIf

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.

/* Create Work File QTemp/RegionData +
+
NOTE: Prior to V5R2 you would have to execute a +
CREATE TABLE and an INSERT INTO statement */

RUNSQL 'CREATE TABLE QTemp/RegionData AS -
  (SELECT ShipState, ShipCity, SUM(Quantity) AS TotQty -
     FROM Orders -
 GROUP BY ShipState, ShipCity -
 ORDER BY ShipState, ShipCity) -
  WITH DATA'
MONMSG SQL0000

RUNSQL 'LABEL ON TABLE QTemp/RegionData IS –
       ''Sales Summary by Region'''

CALL RPTPGM /* Create Report from work file */

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.

DCL &ERROR *CHAR 1 

/* Drop temp view in case it exists    */ 
RUNSQL 'DROP VIEW QTEMP/OPENAR' 
MONMSG SQL0204                      /* Object not found */

/* Make view to calc elapsed days using today's date */ 
RUNSQL 'CREATE VIEW QTEMP/OPENAR AS (                       -
        SELECT CustomerID, Amount,                          -
               DAYS(CURRENT_DATE) - DAYS(TranDate) AS Days  -
          FROM ARTrans                                      -
         WHERE TranDate<=Current_Date)'                      
     
/* Prepare for transaction processing */
RUNSQL 'SET TRANSACTION ISOLATION LEVEL CHG'

/* Clear Customer Master Balances     */
RUNSQL 'UPDATE CustMast -
           SET (CurrentDue, Over30, Over60, Over90) = (0,0,0,0)'
MONMSG SQL0000 EXEC(CHGVAR &ERROR 'Y')

/* Update Balances for unpaid trans   */ 
RUNSQL 'UPDATE CustMast
           SET (CurrentDue, Over30, Over60, Over90) =                 -
                   (SELECT SUM(CASE WHEN Days BETWEEN  0 AND 30       -
                                    THEN Amount ELSE 0 END) AS Cur,   -
                           SUM(CASE WHEN Days BETWEEN 31 AND 60       -
                                    THEN Amount ELSE 0 END) AS Past30,-
                           SUM(CASE WHEN Days BETWEEN 61 AND 90       -
                                    THEN Amount ELSE 0 END) AS Past60,-
                           SUM(CASE WHEN Days > 90                    -
                                    THEN Amount ELSE 0 END) AS Past90 -
                      FROM OpenAR                                     -
                     WHERE OpenAR.CustomerID=CustMast.CustomerID)     -
         WHERE EXISTS                                                 -
            (SELECT *                                                 -
               FROM OpenAR                                            -
              WHERE OpenAR.CustomerID=CustMast.CustomerID)'
MONMSG SQL0000 EXEC(CHGVAR &ERROR 'Y')

/* Rollback Trans if error occured,   +
   otherwise, commit changes         */ 
IF (&ERROR='Y') THEN(RUNSQL 'ROLLBACK')
ELSE (RUNSQL 'COMMIT')

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

PGM (&WRKDATE)
DCL &WRKDATE *CHAR 7  
DCL &MEDATE  *CHAR 10


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.

/* Convert command date CYYMMDD to ISO */
CVTDAT &WRKDATE &MEDATE *CYMD *ISO '-'

/* Calc elapsed days on user supplied month end date */ 
RUNSQL ('CREATE VIEW QTEMP/OPENAR AS (                       -
         SELECT CustomerID, Amount,                          - 
                DAYS('''||&MEDATE||''') - DAYS(TranDate) AS Days  -
           FROM ARTrans                                      -
          WHERE TranDate<='''||&MEDATE||''')')

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:

DCL &ROWSCHG *DEC (15 0)             

RUNSQLRA SQL('UPDATE ORDERHEADER        +
                 SET IN_PROCESS=''Y''   + 
               WHERE IN_PROCESS=''N''   + 
                 AND STATUS=''RDYTOINV''') +
         ROWSAFFECT(&ROWSCHG)

IF (&ROWSCHG>0) THEN(GOTO NIGHTLY)

NODATA: 
/* Inform user there's nothing to do today... */
GOTO END 

NIGHTLY: 
/* Perform nightly order processing */

END:

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:

*Entry        PList 
              Parm                    parmSQL 
              Parm                    parmRowsAffect

If        %Parms>=2 
Eval      parmRowsAffect=SQLErrD(3)
EndIf

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:

Eval      MsgID='SQL'+%Subst(%EditW(%Abs(
                SQLCOD):'0         '):7)

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:

RSTOBJ OBJ(*ALL)
       SAVLIB(QTEMP)
       DEV(*SAVF)
       SAVF(YOURLIB/RUNSQL)
       RSTLIB(PRODLIB)


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.

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


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

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: