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 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.
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
RPGPPOPT parameter. Providing
*LVL2 as the value for
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
One other wrinkle
The Database Embedded SQL Programming manual includes an ominous warning about using
*LVL2 values with
*LVL2is 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
/INCLUDEstatements. 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
*LVL1. However, the Database Embedded SQL Programming manual also briefly mentions the SQL INCLUDE statement. We found that we could swap out
EXEC SQL INCLUDE CustListDS;
and use this call to
CRTSQLRPGI OBJ(RPDREAM/CSTLST) INCFILE(INCLUDES)
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.
If you need to include RPG members that define host structures needed by the SQL precompiler, use
*LVL2. You can probably safely ignore the
/INCLUDE directive. Keep the SQL
INCLUDE statement in mind should you ever exceed resource limits with the