Retrieving commands' defaults, CLOB-ing it

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:

1. WKCLOB_LEN:  Length of the string within the CLOB variable
2. 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:

1. CHGJOB the API had returned blanks in the previous program
2. 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.