Wednesday, September 23, 2020

Retrieving commands' defaults, CLOB-ing it

using clob with qcdrcmdd api

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.

2 comments:

  1. Thank you Simon for this neat example and illustration, as usual in your blog. As an information that might be useful I noticed that this API doesn't return anything if the xml data exceed the size reserved to it, even the QUSEC doesn't return anything. Then if you receive blank and no error, increase the XMLdata 32767 size in the DS.

    ReplyDelete

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.