I have DB2 Stored procedure which does the following:
1. Defines a cursor to select data from a table.
2. Opens the cursor
Now in my CICS-TS application program, I issue a call to this stored procedure to access the data returned. Could you please brief me on the programming steps to use a Result-set-locator variable for accessing this data?
1: Define the parm list for the stored procedure in SYSIBM.PROCEDURES: the RESULT_SETS column must be set to the maximum number of result sets that can be returned 2: in the Stored Procedure 2a: specify WITH RETURN on each OPEN cusrsor statement for a result set that is to be returned from the stored procedure 2b: do not close these cursors in the stored procedure 3: in the Application program running the stored procedure (SP) 3a: define a result set locator variable (one for each returned result set) as EXEC SQL 01 LV1 SQL TYPE IS RESULT-SET-LOCATOR VARYING 3b: call the stored procedure via EXEC SQL CALL SP(:parm) 3c: The Stored Procedure ends without closing these cursors, so returning the result set to CICS 3d: do EXEC SQL ASSOCIATE LOCATOR(:LV1) WITH PROCEDURE SP which sets up the pointer (and for each result set) 3e: do EXEC SQL ALLOCATE RESULTC1 CURSOR FOR RESULT SET :LV1 (and again for each result set) to associaite the query with the result set 3f: then do the usual looping processing to process the result sets and close the resultsets/cursors when finished.
Dig Deeper on IBM system z and mainframe systems
Related Q&A from Robert Crawford
For better mainframe capacity planning, how do I convert CPU hours to MIPS? And is there a way to calculate the relationship between MIPS and MSUs? Continue Reading
I have two years of experience in mainframe technology, currently working as a mainframe developer. I want to change to Java technology. Continue Reading
I want to replicate DB2 from the mainframe to an AIX box since it's cheaper and the copy can be used for testing. Is this possible? Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.