Retrieving commands' defaults, CLOB-ing it @ RPGPGM.COM

Last week I wrote about how to use an API to retrieve a command's defaults. I encountered a problem when the string returned by the API would be blank. I did not know what was causing this. Fortunately two people added comments to the post explaining the issue: if the string returned is longer than the size of the result variable blank is returned. I want to thank both of them for letting me know what was causing this problem.

Therefore, if I increased the size of the subfield in the API's result data structure I see the results for those commands I did not see before.

This caused another problem, as I could no longer use the field in the DDS file I had defined before. The maximum size of an alphanumeric field is 32,766 bytes, which proved too small for some of the commands I tested with. My first thought was to use a (SQL) DDL table instead, but the maximum size of a character column is one byte smaller than for the DDS alphanumeric field, 32,765. As these XML strings can be extremely long I will need to insert these into a CLOB, Character Large Object, column as this can contain up to 2,147,483,647 bytes, way more than I could ever need.

My SQL DDL table looks like:

01  CREATE OR REPLACE TABLE MYLIB.CMD_PARMS
02  ( MYCMD  VARCHAR(20) NOT NULL WITH DEFAULT,
03    MYCLOB CLOB(65331) NOT NULL WITH DEFAULT ) ;

My table has two columns:

Line 2: This will contain the parameter string used in the API. This is the name of the command, and the library where it is found.

Line 3: My CLOB column, with a size of 65,331 bytes.

The changes to the program I showed last week are all in the GetData procedure. Therefore, I am only going to show that below. If you are interested in the rest of the program go to the original post.

01  dcl-proc GetData ;
02    dcl-pi *n ;
03      Parm char(20) const ;
04    end-pi ;

05    dcl-ds CMDD0100data ;
06      BytesReturned int(10) ;
07      ByteAvailable int(10) ;
08      XMLdata char(65531) ccsid(1208) ;
09    end-ds ;

10    dcl-s wkCLOB sqltype(CLOB:65531) ;

11    dcl-pr QCDRCMDD extpgm ;
12      *n char(20) const ;                   //Command & library
13      *n int(10) const ;                    //Size of returned DS
14      *n char(8) const ;                    //Destination format name
15      *n char(32767) options(*varsize) ;    //Returned DS
16      *n char(8) const ;                    //Receiver format name
17      *n likeds(QUSEC) options(*varsize) ;  //API error DS
18    end-pr ;

19    /include qsysinc/qrpglesrc,qusec   //API error DS

20    exec sql SET OPTION COMMIT = *NONE ;

21    XMLdata = ' ' ;

22    QCDRCMDD(Parm : %size(CMDD0100data) : 'DEST0100' :
               CMDD0100data : 'CMDD0100' : QUSEC) ;

23    wkCLOB_data = XMLdata ;
24    wkCLOB_len = %len(%trim(XMLdata)) ;

25    exec sql INSERT INTO MYLIB.CMD_PARMS VALUES(:Parm,:wkCLOB) ;

26    return ;
27  end-proc ;

Line 8: I have increased the size of the subfield XMLdata from 1,024 to 65,531 bytes, which was long enough for the results of every command I could think of.

Line 10: This is the first new line. This is how to define a CLOB type field in RPG.

When the program is compiled the SQL precompiler translate this statement to be a data structure:

//*  DCL-S WKCLOB SQLTYPE(CLOB:65531) ;
       DCL-DS WKCLOB;
         WKCLOB_LEN UNS(10);
         WKCLOB_DATA CHAR(65531) CCSID(*JOBRUNMIX);
       END-DS WKCLOB;

Data structure contains two subfields:

WKCLOB_LEN: Length of the string within the CLOB variable
WKCLOB_DATA: Will contain the data in the CLOB itself

Line 20: I always add this to my SQL RPG programs as I do not want the output to the output file to be committed.

Line 23: Here I am moving the XML of the command defaults returned from the API, XMLdata, into the CLOB.

Line 24: I need to give the length of that string too. If I do not I will receive an error when I use the CLOB.

Line 25: This is the SQL statement that performs the actual insert into the output file.

I ran this program for the following two commands:

CHGJOB the API had returned blanks in the previous program
WRKJOB the full results were returned in the previous program

I can use the following SQL statement to see my results:

SELECT * FROM MYLIB.CMD_PARMS

The following is a good example I why you should not be using STRSQL.

If I use the STRSQL command I see the following:

MYCMD            MYCLOB
---------------  --------
CHGJOB    *LIBL  *POINTER
WRKJOB    *LIBL  *POINTER

As I cannot see what the pointer is pointing to this tells me nothing useful, apart from there are two rows/records in the table.

I always use ACS's Run SQL scripts. When I run the statement using that tool I get to see the values in the second column.

MYCMD            MYCLOB
---------------  --------
CHGJOB    *LIBL  <QcdCLCmd DTDVersion="1.0"><Cmd CmdName="CHGJOB...
WRKJOB    *LIBL  <QcdCLCmd DTDVersion="1.0"><Cmd CmdName="WRKJOB...

This is another good example of why you should use Run SQL scripts for all of your IBM i SQL.

Using the larger CLOB column I am getting the results from the API in my output file/table.

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