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

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchWindowsServer

SearchServerVirtualization

SearchCloudComputing

Close