sql api stored-procedures db2 ibm-midrange

Looking for a working example of any OS/400 API wrapped in an external SQL stored procedure wrapped in a user defined SQL function

Having two issues at the moment:

1) The below example of wrapping an OS/400 API with an external SQL stored procedure which is further wrapper in a SQL user defined table function both compiles and runs without error, but it returns blanks and zeroes for the job information when passing '*' for the job name (i.e. current job). Any tips on why would be appreciated. Note: If I pass a non-existent job, the QUSRJOBI api correctly throws an error, so the code is behaving partially correct. If I pass a correct active job name, job user, and job number, no error occurs but blanks and zeroes are still returned. I've tried both CHAR(85) and VARCHAR(85) for RECEIVER_VARIABLE. I'll try BINARY(85) next for RECEIVER_VARIABLE, but converting from BINARY back to CHAR and INT return columns might prove difficult.

2) Some OS/400 API parameters call for using data structures, which DB2 SQL at V7R1 on the System i does not yet directly support (i.e. no direct support yet for structured types). However, this article says they can be implemented using BINARY strings, but does not provide an example :(. After extensive searching, I've not been able to find an example of wrapping an OS400 api using ONLY SQL objects. If anyone has any examples of how to form a BINARY string using SQL only that is comprised of a mixture of CHAR and other data types like especially INT, please post one. The API error code parameter is an example where this is commonly needed. I have the ERROR_CODE related code commented out since it generates error CPF3CF1 "Error code parameter not valid" if that code is reactivated. If anyone can tell what is wrong with how the ERROR_CODE binary string data structure is being formed, please let me know. I've tried both CHAR(16) and BINARY(16) for the ERROR_CODE structure. I've tested the current technique of how ERROR_CODE is being formed dumping the results into a table, and viewing the table results using DSPPFM in hex mode makes it look like the "binary( hex( ERROR_CODE_BYTES_PROVIDED ) )" etc. is working correctly. However, I'm missing something.

I'm aware that there are lots of examples of using RPG to wrap OS/400 api's, but I want to keep these wrappers as SQL code only.

create or replace procedure M_GET_JOB_INFORMATION
               ( out    OUT_RECEIVER_VARIABLE            char(85)
                ,in     IN_LENGTH_OF_RECEIVER_VARIABLE   int
                ,in     IN_FORMAT_NAME                   char(8)
                ,in     IN_QUALIFIED_JOB_NAME            char(26)
                ,in     IN_INTERNAL_JOB_IDENTIFIER       char(16)
           --     ,inout  INOUT_ERROR_CODE                 binary(16)  
               )

  program type main
  external name QSYS/QUSRJOBI

  parameter style general
  not deterministic
  modifies SQL data
  specific M_JOBINFO
  set option dbgview   = *source
            ,commit    = *nc
            ,closqlcsr = *endmod
            ,tgtrls    = V7R1M0 
;


create or replace function M_GET_JOB_INFORMATION_BASIC
                   ( IN_JOB_NAME                 varchar(10)
                    ,IN_JOB_USER                 varchar(10)
                    ,IN_JOB_NUMBER               varchar(6)
                    ,IN_INTERNAL_JOB_IDENTIFIER  varchar(16)
                   )
  returns table( JOB_NAME                 char(10)
                ,JOB_USER                 char(10)
                ,JOB_NUMBER               char(6)
                ,INTERNAL_JOB_IDENTIFIER  char(16)
                ,JOB_STATUS               char(10)
                ,JOB_TYPE                 char(1)
                ,JOB_SUBTYPE              char(1)
                ,RUN_PRIORITY             int
                ,TIME_SLICE               int
                ,DEFAULT_WAIT             int
                ,ELIGIBLE_FOR_PURGE       char(10)
               )

  language SQL
  specific M_JOBINFBF
  not deterministic
  disallow parallel
  no external action
  modifies SQL data
  returns null on null input
  not fenced

  set option dbgview   = *source
            ,commit    = *nc
            ,closqlcsr = *endmod
            ,tgtrls    = V7R1M0
     --       ,output    = *PRINT

begin

declare RECEIVER_VARIABLE             char(85)      default '';          --receives "JOBI0100" format output from API
declare LENGTH_OF_RECEIVER_VARIABLE   int           default 85;          --length of "JOBI0100" Format
declare FORMAT_NAME                   char(8)       default 'JOBI0100';  --basic job information
declare QUALIFIED_JOB_NAME            char(26);
declare INTERNAL_JOB_IDENTIFIER       char(16);
declare ERROR_CODE                    binary(16);  

--ERROR_CODE "ERRC0100" Format:
declare ERROR_CODE_BYTES_PROVIDED               int         default 8;    --Size of API Error Code data structure passed to API
declare ERROR_CODE_BYTES_RETURNED               int         default 0;    --Number of exception data bytes returned by the API
declare ERROR_CODE_EXCEPTION_ID                 char(7)     default '';   --Exception / error message ID returned by the API
declare ERROR_CODE_RESERVED                     char(1)     default '';   --Reserved bytes
declare ERROR_CODE_EXCEPTION_DATA               char(1)     default '';   --Exception data returned by the API

if IN_INTERNAL_JOB_IDENTIFIER = '' then 
   set QUALIFIED_JOB_NAME = char( IN_JOB_NAME, 10 ) || char( IN_JOB_USER, 10 ) || char( IN_JOB_NUMBER, 6 );
   set INTERNAL_JOB_IDENTIFIER = '';
else  
   set QUALIFIED_JOB_NAME = '*INT';
   set INTERNAL_JOB_IDENTIFIER = IN_INTERNAL_JOB_IDENTIFIER;   
end if;

set ERROR_CODE = binary( hex( ERROR_CODE_BYTES_PROVIDED ) ) ||  
                 binary( hex( ERROR_CODE_BYTES_RETURNED ) ) ||  
                 binary( ERROR_CODE_EXCEPTION_ID ) || 
                 binary( ERROR_CODE_RESERVED ) 
          --  ||    binary( ERROR_CODE_EXCEPTION_DATA )
;  

call M_GET_JOB_INFORMATION
        ( RECEIVER_VARIABLE             --out
         ,LENGTH_OF_RECEIVER_VARIABLE   --in
         ,FORMAT_NAME                   --in
         ,QUALIFIED_JOB_NAME            --in
         ,INTERNAL_JOB_IDENTIFIER       --in
      --   ,ERROR_CODE                    --in/out  --Results in error CPF3CF1 "Error code parameter not valid" if code line reactivated
        );

return values( char( substr( RECEIVER_VARIABLE,  8, 10 ), 10 )      --JOB_NAME
              ,char( substr( RECEIVER_VARIABLE, 18, 10 ), 10 )      --JOB_USER
              ,char( substr( RECEIVER_VARIABLE, 28,  6 ),  6 )      --JOB_NUMBER
              ,char( substr( RECEIVER_VARIABLE, 28, 16 ), 16 )      --INTERNAL_JOB_IDENTIFIER
              ,char( substr( RECEIVER_VARIABLE, 50, 10 ), 10 )      --JOB_STATUS
              ,char( substr( RECEIVER_VARIABLE, 60,  1 ),  1 )      --JOB_TYPE
              ,char( substr( RECEIVER_VARIABLE, 61,  1 ),  1 )      --JOB_SUBTYPE
              ,case when substr( RECEIVER_VARIABLE, 64, 4 ) = ''
                    then 0
                    else int( substr( RECEIVER_VARIABLE, 64, 4 ) )
               end                                                  --RUN_PRIORITY
              ,case when substr( RECEIVER_VARIABLE, 68, 4 ) = ''
                    then 0
                    else int( substr( RECEIVER_VARIABLE, 68, 4 ) )
               end                                                  --TIME_SLICE
              ,case when substr( RECEIVER_VARIABLE, 72, 10 ) = ''
                    then 0
                    else int( substr( RECEIVER_VARIABLE, 72, 4 ) )
               end                                                  --DEFAULT_WAIT
              ,char( substr( RECEIVER_VARIABLE, 76, 10 ), 10 )      --ELIGIBLE_FOR_PURGE
             )
;

end    
;
select * from table( M_GET_JOB_INFORMATION_BASIC( '*', '', '', '' ) ) as JOB_INFO
;

Solution

I use this on i 6.1 to call the QDBRTVFD API:

CREATE PROCEDURE SQLEXAMPLE.DBRTVFD ( 
    INOUT FD       CHAR(1024) , 
    IN    SZFD     INTEGER , 
    INOUT RTNFD    CHAR(20) , 
    IN    FORMAT   CHAR(8) , 
    IN    QF       CHAR(20) , 
    IN    "RCDFMT" CHAR(10) , 
    IN    OVRPRC   CHAR(1) , 
    IN    SYSTEM   CHAR(10) , 
    IN    FMTTYP   CHAR(10) , 
    IN    ERRCOD   CHAR(8) ) 
    LANGUAGE CL 
    SPECIFIC SQLEXAMPLE.DBRTVFD 
    NOT DETERMINISTIC 
    NO SQL 
    CALLED ON NULL INPUT 
    EXTERNAL NAME 'QSYS/QDBRTVFD' 
    PARAMETER STYLE GENERAL ;

First, the default is LANGUAGE C, and you probably don't want that for QUSRJOBI which is an OPM program. CL-language parameter passing can be a better choice for predictability here.

Also, you probably want to set this as NO SQL rather than modifies SQL data since you aren't modifying SQL data. It might be necessary to remove the SET OPTION in order to get things down to the minimum.

If you make those changes for your M_GET_JOB_INFORMATION procedure, see if it returns useful values. If it doesn't, we can dig a little deeper.

For your particular API, I used this code to test results on i 6.1:

CREATE PROCEDURE SQLEXAMPLE.M_GET_JOB_INFORMATION ( 
    INOUT OUT_RECEIVER_VARIABLE CHAR(85) , 
    IN IN_LENGTH_OF_RECEIVER_VARIABLE INTEGER , 
    IN IN_FORMAT_NAME CHAR(8) , 
    IN IN_QUALIFIED_JOB_NAME CHAR(26) , 
    IN IN_INTERNAL_JOB_IDENTIFIER CHAR(16) , 
    IN IN_ERROR_CODE CHAR(8) ) 
    LANGUAGE CL 
    SPECIFIC SQLEXAMPLE.M_JOBINFO 
    NOT DETERMINISTIC 
    NO SQL 
    CALLED ON NULL INPUT 
    EXTERNAL NAME 'QSYS/QUSRJOBI' 
    PARAMETER STYLE GENERAL ;

A basic wrapper was created like so:

CREATE PROCEDURE SQLEXAMPLE.GENRJOBI ( 
    INOUT JOBI       VARCHAR(85) , 
    IN       QJOB      VARCHAR(26) ) 
    LANGUAGE SQL 
    SPECIFIC SQLEXAMPLE.GENRJOBI 
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
        ALWCPYDTA = *OPTIMIZE , 
        COMMIT = *NONE , 
        DBGVIEW = *LIST , 
        CLOSQLCSR = *ENDMOD , 
        DECRESULT = (31, 31, 00) , 
        DFTRDBCOL = *NONE , 
        DLYPRP = *NO , 
        DYNDFTCOL = *NO , 
        DYNUSRPRF = *USER , 
        RDBCNNMTH = *RUW , 
        SRTSEQ = *HEX   
    P1 : BEGIN 
DECLARE JOBII CHAR ( 85 ) ; 
DECLARE SZJOBI INTEGER ; 
DECLARE FORMATI CHAR ( 8 ) ; 
DECLARE QJOBI CHAR ( 26 ) ; 
DECLARE JOBIDI CHAR ( 16 ) ; 
DECLARE ERRCODI CHAR ( 8 ) ; 
DECLARE STKCMD CHAR ( 10 ) ; 

SET JOBII = X'00000000' ; 
SET SZJOBI = 85 ;
SET FORMATI = 'JOBI0100' ; 
SET QJOBI = QJOB ; 
SET JOBIDI = '                ' ; 
SET ERRCODI = X'0000000000000000' ; 
SET STKCMD = '*LOG' ; 

CALL SQLEXAMPLE . M_GET_JOB_INFORMATION ( JOBII , SZJOBI ,  FORMATI , QJOBI , JOBIDI , ERRCODI ) ; 
CALL SQLEXAMPLE . LOGSTACK ( STKCMD ) ; 

SET JOBI = JOBII ; 

END P1  ;

The wrapper only provides an example of calling the API proc. It does nothing with the returned structure from the API except pass it back out to its caller. Your original question included bits of code to extract sub-fields from a structure, so I didn't see a point to putting similar code here.

The two procs were tested in iNav's 'Run SQL Scripts' to grab info about an interactive job I was running, and the result looked like this:

JOBI proc output

The output area shows the structure in characters, and the integer sub-fields can be seen mixed with character sub-fields. Deconstruct the structure as needed. I might create an additional proc that takes the structure as input and returns individual structure elements.