Wednesday, May 6, 2020

Adding RPG compiler parameters into the SQL Option statement

sql option compileopt in sql rpgle program

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.

03  C+             COMPILEOPT = 'DBGVIEW(*ALL)'

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.


       DCL-DS SQLCA;
         SQLCAID CHAR(8) INZ(X'0000000000000000');
         SQLCABC INT(10);

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


  1. May also want to include


    if you have any embedded dynamic SQL that should use adopted authority. Most of the time though, I use prepared statements.


  2. Hi,
    if the CRTSQLRPGI already has the DBGVIEW Option without *NONE, there is an Error on Compile ! -> Compile Option used several Times. Greetz

    1. Some has changed your command's defaults. That's fine to do. But I think most IBM i environments do not do that.

  3. Hey Simon,

    I'm modifying someone else's code and they have the following...
    *inlr = *on;
    In Lda;

    // Set the options for SQL by SQL's version of the H-Spec
    Exec Sql
    Set Option Naming = *Sys,
    Commit = *None,
    Usrprf = *User,
    Dynusrprf = *User;

    I've always read that the SET OPTION should be the first executable statement. In the above code I would have put it right after the /Free and before *inlr = *on;
    Do you know if there would be any difference by moving it or what would be the result of moving it or not? As I mentioned I've read the SET OPTION should be the first executable statement but I can't recall why.

  4. The SET OPTION needs to be the first SQL statement.
    That could be the first line of code, or could be what you have shown in your example.


To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.