• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: SUBSET on EXECUTE and OPEN

    June 15, 2020 Paul Tuohy

    The DB2 for i Enhancements in IBM i 7.3 Technology Refresh 8 / IBM i 7.4 Technology Refresh 2 just made dynamic SQL, embedded in RPG, a lot easier to use. It includes an enhancement entitled USING SUBSET on EXECUTE and OPEN with Extended indicators. This enhancement provides a simple and effective solution to a problem that I first highlighted in an article I wrote back in September of 2015. (See A First Look At SQL Descriptors.)

    The “problem” we are dealing with is how to handle a variable number of host variables being used in a dynamic SQL statement. In the aforementioned article, I used the following SQL statement as an example:

    select workDept, empno, firstname, lastname from employee
       where workDept  = ?  and
             hireDate  >= ? and
             birthDate >= ? 
       order by workDept, empno
    

    The difficulty was that comparison values might or might not be provided for the department code, hire date and birth date. Therefore, the where clause (if it is to be included) might be against any combination of the three columns.

    There were three possible coding solutions, each with its own caveats

    • Use static SQL, which (most of the time) will result in the query engine performing unnecessary and inefficient work.
    • Construct a WHERE clause that includes the comparison values, which results in convoluted, error-prone string handling with the (small) potential for an SQL injection attack.
    • Use an SQL Descriptor, which (although better than the previous solutions) is not intuitive and requires a lot of attention to detail, especially for handling the data types of the host variables.

    Code examples of these three solutions may be found in the A First Look At SQL Descriptors article. The new SUBSET option provides a much more eloquent solution.

    SUBSET

    The new SUBSET makes use of SQL indicators (an integer in RPG) associated with the host variable. You will have used one of these SQL indicators if you have ever dealt with null values for columns. Giving a value of -7 to an SQL indicator (for a host variable) is the equivalent of assigning an SQLIND_UNASSIGNED value to the variable.

    This is an example of a program using dynamic SQL to generate the required SQL statement and using the new SUBSET option to assign the required values from host variables. The program defines a cursor for the generated statement and uses a multi row fetch to retrieve the rows. Please refer to the call outs in the code below.

           dcl-s getRows         int(10) inz(%elem(data));
           dcl-s gotRows         int(10);
           dcl-ds data qualified dim(500);
             workDept  char(3);
             empno     char(6);
             firstName varchar(12);
             lastName  varchar(15);
           end-ds;
    
    (A)    dcl-s getDept     char(3);
           dcl-s getHired    date;
           dcl-s getBirth    date;
    
    (B)    dcl-s getDept_ind int(5); 
           dcl-s getHired_ind int(5); 
           dcl-s getBirth_ind int(5); 
    
    (C)    dcl-s myStatement varchar(2000);
           dcl-s myWhere     varchar(100);
           dcl-s pad_And     varchar(5);
    
           dcl-C ADD_AND   ' and ';
           dcl-C ADD_WHERE ' where ';
    (D)    dcl-C ADD_DEPT  ' workDept = ? ';
           dcl-C ADD_HIRE  ' hiredate >= ? ';
           dcl-C ADD_BIRTH ' birthdate >= ? ';
    
           if (getDept <> *blanks);
    (E)      myWhere = ADD_DEPT;
             pad_And = ADD_AND;
             getDept_ind = 0;
           else;
    (F)      getDept_ind = -7;
           endIf;
    
           if (getHired <> d'0001-01-01');
    (G)      myWhere += pad_And + ADD_HIRE;
             pad_And = ADD_AND;
             getHired_ind = 0;
           else;
             getHired_ind = -7;
           endIf;
    
           if (getBirth <> d'0001-01-01');
    (G)      myWhere += pad_And + ADD_BIRTH;
             pad_And = ADD_AND;
             getBirth_ind = 0;
           else;
             getBirth_ind = -7;
           endIf;
    
           if (myWhere <> '');
              myWhere = ADD_WHERE + myWhere;
           endIf;
    
           myStatement = 'select workDept, empno, firstname, lastname ' +
                         ' from employee ' +
                         myWhere +
                         ' order by workDept, empno';
    
           exec SQL
              prepare D1 from :myStatement;
    
           if SQLCode = 0;
              exec SQL
                 declare C1 scroll cursor for D1;
    
              exec SQL
    (H)          open C1 using SUBSET :getDept  :getDept_ind,
                                      :getHired :getHired_ind, 
                                      :getBirth :getBirth_ind;
    
              exec SQL
                 fetch first from C1 for :getRows rows into :data;
    
              gotRows = SQLErrd(3);
    
              exec SQL
                 close C1;
           endIf;
    
           *inLR = *on;
    

    (A) Variables that may contain the required comparison values in the where clause. They would usually be passed as parameters.

    (B) SQL indicators associated with the host variables that will be used in the OPEN statement.

    (C) Work variables used in the construction of the dynamic SQL statement.

    (D) Named constants are used to provide the components of the WHERE clause. Each component has the required parameter marker.

    (E) If the statement is to include a comparison against the department code, then add the comparison to the WHERE clause and set the associated indicator to 0 (to indicate there is a value).

    (F) If the statement will NOT include a comparison against the department code, then set the associated indicator to -7 (to indicate that the variable is to be ignored).

    (G) Repeat the same process for the other two possible comparison values.

    (H) When opening the cursor, you specify all of the host variables in the list and you precede that list with SUBSET. The host variables will have a value or they will be SQLIND_UNASSIGNED. Variables with SQLIND_UNASSIGNED are ignored for assignment. The important thing to note is the sequence of the host variable names must correspond to the sequence they assigned in the WHERE clause.

    The new SUBSET feature make the proper construct (using parameter markers) of a dynamic SQL statement a lot easier and, therefore, makes dynamic SQL a lot easier to use.

    Paul Tuohy, IBM Champion and author of Re-engineering RPG Legacy Applications, is a prominent consultant and trainer for application modernization and development technologies on the IBM Midrange. He is currently CEO of ComCon, a consultancy firm in Dublin, Ireland, and partner at System i Developer. He hosts the RPG & DB2 Summit twice per year with partners Susan Gantner and Jon Paris.

    RELATED STORIES

    A First Look At SQL Descriptors

    Guru: Alternate SQL Row-Selection Criteria Revisited

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, DB2 for i, FHG, Four Hundred, IBM i, IBM i 7.3 Technology Refresh 8, IBM i 7.4 Technology Refresh 2, RPG, SQL

    Sponsored by
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Four Hundred Monitor, June 15 Why You Need To Implement Exit Point Security – Now

    One thought on “Guru: SUBSET on EXECUTE and OPEN”

    • Danny Rodius says:
      October 21, 2020 at 3:51 am

      Good article. Got a first use for it an stumbled already to a problem. When used as a method of filling a subfile and closing the cursor, even with the CLOSQLCSR(*ENDMOD), we are getting an SQL0501 error saying the cursor is still open or in use. Any idea of solving this.

      Reply

    Leave a Reply Cancel reply

TFH Volume: 30 Issue: 37

This Issue Sponsored By

  • Fresche Solutions
  • MITEC
  • TL Ashford
  • Kisco Information Systems
  • WorksRight Software

Table of Contents

  • More Open Source Databases Coming To IBM i
  • Why You Need To Implement Exit Point Security – Now
  • Guru: SUBSET on EXECUTE and OPEN
  • Four Hundred Monitor, June 15
  • IBM i PTF Guide, Volume 22, Number 24

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle