Q
Problem solve Get help with specific problems with your technologies, process and projects.

Stored procedures and accessing data via CICS-TS

I have DB2 Stored procedure which does the following: 1. Defines a cursor to select data from a table. 2. Opens...

the cursor 3. Returns

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.

This was last published in October 2002

Dig Deeper on IBM system z and mainframe systems

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchWindowsServer

SearchServerVirtualization

SearchCloudComputing

Close