Using ILE RPG's /COPY with embedded SQL

We often create ILE RPG applications for our customers to provide the server-side logic and database access for IBM i mobile applications—which we use with ASNA Mobile RPG. We've become big fans of the TR7-introduced full free-format RPG and embedded SQL. The combination of a moderately rational RPG and SQL database access make a great duo for creating effective, performant server-side components.

In a pure world, these applications would be architected with ILE RPG's service programs (and binding directories and other ILE composite goodies). However, we generally hand these finished applications off to customer RPG teams with little or no advanced ILE RPG knowledge. We need to keep these applications simple so we usually use /COPY instead of other ILE RPG partitioning schemes.

We learned a lesson the other day about using /COPY included files with embedded SQL. Consider the following two ILE RPG snippets:

Dcl-DS CustomerList  Dim(50) Qualified;
    CMCustNo     Int(10);
    CMName       Char(40);
    CMCity       Char(30);
    CMState      Char(2);
End-DS; 

Figure 1a. A data structure area definition named CUSTLISTDS and stored in the INCLUDES source physical file.

/COPY INCLUDES,CUSTLISTDS        

EXEC SQL
    DECLARE C_DOC1 CURSOR FOR
    SELECT CMCustNo,
        Trim(CMName), Trim(CMCity), CMState
        FROM Customer   
        ORDER BY LCASE(TRIM(CMName))
        FOR READ ONLY;                   
EXEC SQL
    OPEN C_DOC1;
EXEC SQL
    FETCH C_DOC1 FOR 12 ROWS INTO :CustomerList;
EXEC SQL 
    CLOSE C_DOC1;

Figure 1b. Embedded SQL in RPG putting a result set into the host structure named CustomerList.

The CustomerList data structure array defines an SQL host structure (a replaceable SQL token). In Figure 1b, the SQL statement:

FETCH C_DOC1 FOR 12 ROWS INTO :CustomerList;

puts the fetched result set into the CustomerList host structure.

Using CRTSQLRPGI to compile the RPG program that contained the snippet shown in Figure 1b produced an SQL SQL5011 error ("Host structure array CustomerList was not defined or not usable"). When a member included with /COPY defines a host structure it must be available to the SQL precompiler, and this apparently was not happening.

The fix to this issue was simple, but moderately elusive. Scouring the Database Embedded SQL Programming manual provided a clue (the text in the manual is somewhat ambiguous) regarding CRTSQLRPGI's RPGPPOPT parameter. Providing *LVL1 or *LVL2 as the value for CRTSQLRPGI's RPGPPOPT parameter (as shown below in Figure 3), fixed the problem.

CRTSQLRPGI OBJ(*LIBL/CSTLST) 
           RPGPPOPT(*LVL1)

There is also an /INCLUDE directive available, but it only works when RPGPPOPT = *LVL2.  

One other wrinkle

The Database Embedded SQL Programming manual includes an ominous warning about using *LVL1 and *LVL2 values with RPGPPOPT:

"When *LVL1 or *LVL2 is used, there is a possibility that the expanded source generated by the RPG preprocessor will become very large and reach a resource limit due to the expansion of the /COPY and /INCLUDE statements. If this happens you must either break up your source into smaller pieces, or not use the RPG preprocessor."

Resource limit? We're on an IBM i! Not Windows. Oh, well. We're taking our chances and going to stick with /COPY and RPGPPOPT = *LVL1. However, the Database Embedded SQL Programming manual also briefly mentions the SQL INCLUDE statement. We found that we could swap out

/COPY INCLUDES,CUSTLISTDS 

for

EXEC SQL
    INCLUDE CustListDS;

and use this call to CRTSQLRPGI

CRTSQLRPGI OBJ(RPDREAM/CSTLST) INCFILE(INCLUDES) 

where the INCFILE parameter specifies the source physical file in which to find external members. This compile also worked. We're not going to use this; /COPY works fine for us. However, this SQL INCLUDE may be a work-around should you ever bump into IBM's ominous warning issued about reaching "a resource limit" with the RPGPPOPT parameter. If you do use the SQL INCLUDE statement note that all included members must reside in the same source physical file.

Summary

If you need to include RPG members that define host structures needed by the SQL precompiler, use /COPY with CRTSQLRPGI's RPGPPOPT = *LVL1 or *LVL2. You can probably safely ignore the /INCLUDE directive. Keep the SQL INCLUDE statement in mind should you ever exceed resource limits with the /COPY directive.