Adding RPG compiler parameters into the SQL Option statement

This feature was brought to my attention by someone who was converting a fixed format SQLRPGLE program to modern all free RPG code, and wondered the purpose of this code snippet.

01 C/EXEC SQL 02 C+ SET OPTION COMMIT = *NONE, 03 C+ COMPILEOPT = 'DBGVIEW(*ALL)' 04 C/END-EXEC

She understood the purpose of line 2. It was line 3 she had not seen before. Neither had I, and was intrigued as to what its purpose was and how else could it be used.

To understand its purpose one first needs to understand how SQLRPGLE objects are created. For those of you who already know this you can skip this explanation by clicking here.

When the source member is compiled there are two steps that are performed:

1. SQL precompiler converts the source into a temporary (interim) source member
2. RPGLE compiler that compiles the temporary code into a program or module

When you compile SQLRPGLE source members you will find the temporary source members in the source file QSQLSRC1 in the library QTEMP, the name of the source members match the source members compiled. When you look at these members you can see how the precompiler has converted the code, ready for the RPGLE compiler.

**free /SET CCSID(*CHAR:*JOBRUNMIX) // SQL COMMUNICATION AREA DCL-DS SQLCA; SQLCAID CHAR(8) INZ(X'0000000000000000'); SQLAID CHAR(8) OVERLAY(SQLCAID); SQLCABC INT(10); SQLABC BINDEC(9) OVERLAY(SQLCABC);

If you are unfamiliar with the /SET you can read about it here.

This is the source member the RPGLE compiler uses to create the final object.

The COMPILEOPT can be used in two places:

1. The with the SQL options, as shown above
2. It is also parameter of the CRTSQLRPGI compile command

I am just going to give examples using the SQL Set option, know that the same rules apply to the compile parameter.

The options used here are only for the RPGLE compiler that will be used when the object is being generated. The options look the same as the ones I would use in RPG's control options

Here is an example using the control options I commonly use in my code:

01 exec sql SET OPTION COMMIT = *NONE, 02 DATFMT = *ISO, 03 COMPILEOPT = 'OPTION(*SRCSTMT *NODEBUGIO *NOUNREF) + 04 ALWNULL(*USRCTL) + 05 DFTACTGRP(*NO) + 06 BNDDIR(*LIBL/TEST) + 07 DBGVIEW(*LIST)' ;

Lines 3 – 7: The compile options are in the same format as if I had entered them into the create RPG program command. Notice that each line ends with a plus ( + ) symbol to denote that the options are continued on the next line.

I would never use them in this manner. I prefer to put the compiler options in RPG's Control Options:

01 ctl-opt option(*srcstmt:*nodebugio:*nounref) 02 alwnull(*usrctl) 03 dftactgrp(*no) 04 bnddir('*LIBL/TEST') ; 05 exec sql SET OPTION COMMIT = *NONE, 06 DATFMT = *ISO ;

The only option that does not "translate" to a RPG control option is the DBGVIEW. Perhaps that is a reason to include it in the SQL Set option, which takes us back to the first SQL statement in this post, but modernized.

01 exec sql SET OPTION COMMIT = *NONE, 02 COMPILEOPT = 'DBGVIEW(*ALL)' ;

 

You can learn more about the COMPILEOPT from the IBM website here.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.