Looking For A Working Example Of Any Os/400 Api Wrapped In An External Sql Stored Procedure Wrapped In A User Defined Sql Function
Solution 1:
I use this on i 6.1 to call the QDBRTVFD API:
CREATEPROCEDURE 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) ,
INSYSTEMCHAR(10) ,
IN FMTTYP CHAR(10) ,
IN ERRCOD CHAR(8) )
LANGUAGE CL
SPECIFIC SQLEXAMPLE.DBRTVFD
NOTDETERMINISTICNOSQLCALLEDONNULL 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:
CREATEPROCEDURE 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
NOTDETERMINISTICNOSQLCALLEDONNULL INPUT
EXTERNAL NAME 'QSYS/QUSRJOBI'PARAMETER STYLE GENERAL ;
A basic wrapper was created like so:
CREATEPROCEDURE SQLEXAMPLE.GENRJOBI (
INOUT JOBI VARCHAR(85) ,
IN QJOB VARCHAR(26) )
LANGUAGESQLSPECIFIC SQLEXAMPLE.GENRJOBI
NOTDETERMINISTICMODIFIESSQL DATA
CALLEDONNULL 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 : BEGINDECLARE 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:
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.
Solution 2:
Were you aware that IBM has already wrapped the Get Job Info API?
All Services by version / release https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/DB2%20for%20i%20-%20Services
Solution 3:
1/5/2015 Updated version: Enhanced to get ERROR_CODE working properly returning ERROR_ID and ERROR_DATA, and removed some unnecessary related code lines. Added M_HEX_STRING_TO_INTEGER function used to take integers returned from the API in RECEIVER_VARIABLE and turn them into real integers. The binary(hex(my_int)) code wasn't working as I'd hoped, so this version passes the ERROR_CODE "bytes provided" size in hex. To clean that up, I think I need to write a function to take an integer and return a BINARY(4) for use in binary string applications, until IBM gives us structured types on System i. /End of updates.
I figured out the issues. The main issue was the API I was calling is LANGUAGE PLI, and I think I missed trying that language setting. I had other issues including forgetting to convert some offset numbers to byte positions, and the only way I've been able to get this to execute is using VARBINARY for the ERROR_CODE related code. INOUT is needed for OUT_RECEIVER_VARIABLE. If that is set to OUT instead of INOUT, and invalid job information is passed, the API program will return whatever it returned in the prior call (returns residual memory values from prior call not current call). I also had to write a function to take BINARY INT data returned from the API to convert it to a real INTEGER. If we had structured types (user defined data structures) for SQL like other versions of DB2 we probably could avoid most or all of the BINARY stuff, making things a lot simpler. If anyone else uses this code remember to specify schema names for the procedure and function definitions, since I've removed them for this post.
Now that I have the basics of wrapping these APIs sorted out, I should be able to wrap them VERY quickly now :)
--M_GET_JOB_INFORMATION (M_JOBINFO) - external SQL user defined procedure to fetch job information.--Wrapper around IBM api QUSRJOBI (retrieve job information).--May be used for any of the format data structures returned by the api, assuming a UDTF function has been created to consume the desired format data structure.createor replace procedure M_GET_JOB_INFORMATION
( inout RECEIVER_VARIABLE char(86) for bit data --must use inout
,in LENGTH_OF_RECEIVER_VARIABLE int
,in FORMAT_NAME char(8)
,in QUALIFIED_JOB_NAME char(26)
,in INTERNAL_JOB_IDENTIFIER char(16)
,inout ERROR_CODE char(48) for bit data
)
program type main
external name 'QSYS/QUSRJOBI'language PLI
parameter style general
notdeterministicmodifiesSQL data
specific M_JOBINFO
set option dbgview =*source
,commit=*nc
,closqlcsr =*endmod
,tgtrls = V7R1M0
;
--M_GET_JOB_INFORMATION_BASIC (M_JOBINFBF) - SQL user defined Table function to fetch job name, user, number, and other basic job information.--Wrapper around IBM api QUSRJOBI retrieved data format JOBI0100 (basic job information).--From IBM manual QUSRJOBI api:--The JOBI0100 format information is valid for active jobs and jobs on queues.--For jobs on queues, this format returns zeros or blanks for the attributes.--If the Change Job (CHGJOB) command was run against a job on a *JOBQ, the attributes returned are the attributes specified on the CHGJOB command.--If the job status changes to *OUTQ, the status field returned is *OUTQ and the API returns no information other than the number of bytes returned,--the number of bytes available, the qualified job name, the job type, the job subtype, and the internal job identifier.--Examples of use:--select * from table( M_GET_JOB_INFORMATION_BASIC( '*', '', '', '' ) ) as JOB_INFO --Get current job and basic info--select * from table( M_GET_JOB_INFORMATION_BASIC( 'QZDASOINIT', 'QUSER', '123456', '' ) ) as JOB_INFO --Get a specific job's basic infocreateor 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)
)
returnstable( 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)
,ERROR_ID char(7) --returned as blank if valid job requested, else IBM error message ID returned.
,ERROR_DATA char(32) --returned as blank if valid job requested, else IBM error message data returned.
)
languageSQLspecific M_JOBINFBF
notdeterministic
disallow parallel
noexternal action
modifiesSQL data
calledonnull input
not fenced
cardinality1--helps SQL optimizerset option dbgview =*source
,commit=*nc
,closqlcsr =*endmod
,dftrdbcol =*none
,tgtrls = V7R1M0
begindeclare RECEIVER_VARIABLE char(86) for bit data default''; --receives "JOBI0100" format output from APIdeclare LENGTH_OF_RECEIVER_VARIABLE intdefault86; --length of "JOBI0100" Formatdeclare FORMAT_NAME char(8) default'JOBI0100'; --basic job informationdeclare QUALIFIED_JOB_NAME char(26);
declare INTERNAL_JOB_IDENTIFIER char(16);
declare ERROR_CODE char(48) for bit data default''; --ERROR_CODE "ERRC0100" Format - Input: bytes provided (4); Output: bytes available (4), error message ID (7), reserved bytes (1), error data (32) = 48 bytes
if IN_INTERNAL_JOB_IDENTIFIER =''thenset QUALIFIED_JOB_NAME =char( IN_JOB_NAME, 10 ) ||char( IN_JOB_USER, 10 ) ||char( IN_JOB_NUMBER, 6 );
set INTERNAL_JOB_IDENTIFIER ='';
elseset QUALIFIED_JOB_NAME ='*INT';
set INTERNAL_JOB_IDENTIFIER = IN_INTERNAL_JOB_IDENTIFIER;
end if;
set ERROR_CODE = bx'00000030'; --put size of ERROR_CODE here in hex (Bytes Provided) hex 30 = dec 48call M_GET_JOB_INFORMATION
( RECEIVER_VARIABLE --in/out
,LENGTH_OF_RECEIVER_VARIABLE --in
,FORMAT_NAME --in
,QUALIFIED_JOB_NAME --in
,INTERNAL_JOB_IDENTIFIER --in
,ERROR_CODE --in/out
);
returnvalues( char( substr( RECEIVER_VARIABLE, 9, 10 ), 10 ) --JOB_NAME
,char( substr( RECEIVER_VARIABLE, 19, 10 ), 10 ) --JOB_USER
,char( substr( RECEIVER_VARIABLE, 29, 6 ), 6 ) --JOB_NUMBER
,char( substr( RECEIVER_VARIABLE, 35, 16 ), 16 ) --INTERNAL_JOB_IDENTIFIER
,char( substr( RECEIVER_VARIABLE, 51, 10 ), 10 ) --JOB_STATUS
,char( substr( RECEIVER_VARIABLE, 61, 1 ), 1 ) --JOB_TYPE
,char( substr( RECEIVER_VARIABLE, 62, 1 ), 1 ) --JOB_SUBTYPE
,casewhen substr( RECEIVER_VARIABLE, 65, 4 ) =''thenint( 0 )
else M_HEX_STRING_TO_INTEGER( hex( substr( RECEIVER_VARIABLE, 65, 4 ) ) )
end--RUN_PRIORITY
,casewhen substr( RECEIVER_VARIABLE, 69, 4 ) =''thenint( 0 )
else M_HEX_STRING_TO_INTEGER( hex( substr( RECEIVER_VARIABLE, 69, 4 ) ) )
end--TIME_SLICE
,casewhen substr( RECEIVER_VARIABLE, 73, 10 ) =''thenint( 0 )
else M_HEX_STRING_TO_INTEGER( hex( substr( RECEIVER_VARIABLE, 73, 4 ) ) )
end--DEFAULT_WAIT
,char( substr( RECEIVER_VARIABLE, 77, 10 ), 10 ) --ELIGIBLE_FOR_PURGE
,char( substr( ERROR_CODE, 9, min( M_HEX_STRING_TO_INTEGER( hex( substr( ERROR_CODE, 5, 4 ) ) ), 7 ) ), 7 ) --ERROR_ID
,char( substr( ERROR_CODE, 17, min( M_HEX_STRING_TO_INTEGER( hex( substr( ERROR_CODE, 5, 4 ) ) ), 32 ) ), 32 ) --ERROR_DATA
);
end
;
--M_HEX_STRING_TO_INTEGER - M_HEXTOINT - scalar SQL UDF to convert a hex string of an integer to an integer return value--Use for working with binary strings and OS/400 system APIs.--Throws an error if an invalid byte is found inside the input hex string.createor replace function M_HEX_STRING_TO_INTEGER
( IN_HEX_STRING varchar(8) )
returnsintlanguageSQLspecific M_HEXTOINT
notdeterministic
disallow parallel
noexternal action
modifiesSQL data
returnsnullonnull input
not fenced
set option dbgview =*source
,commit=*nc
,closqlcsr =*endmod
,tgtrls = V7R1M0
begindeclare BYTE_CHAR char(1);
declare POS intdefault1;
declare ACCUM_VALUE int;
declare BIGINT_RESULT bigintdefault0;
declare STRING_LENGTH int;
set STRING_LENGTH = length( IN_HEX_STRING );
while POS <= STRING_LENGTH do
set BYTE_CHAR = substr( IN_HEX_STRING, POS, 1 );
if BYTE_CHAR between'0'and'9'thenset ACCUM_VALUE =int( BYTE_CHAR );
elseset ACCUM_VALUE =case BYTE_CHAR
when'A'then10when'B'then11when'C'then12when'D'then13when'E'then14when'F'then15else raise_error( 'MG010', 'Function M_HEX_STRING_TO_INTEGER encountered invalid INT hex byte='|| BYTE_CHAR )
end;
end if;
if ACCUM_VALUE <>0thenset BIGINT_RESULT = BIGINT_RESULT + ( ACCUM_VALUE *power( 16, STRING_LENGTH - POS ) ) ;
end if;
set POS = POS +1;
end while;
returnint( casewhen BIGINT_RESULT >2147483647then BIGINT_RESULT -4294967296else BIGINT_RESULT end );
end
Post a Comment for "Looking For A Working Example Of Any Os/400 Api Wrapped In An External Sql Stored Procedure Wrapped In A User Defined Sql Function"