How to return the result sets from one procedure to the another in case of nested stored procedures in db2 v8 for Z/OS

When you declare a cursor for a result set, you can specify WITHOUT RETURN (which is the default), or WITH RETURN TO CALLER. The latter will ensure that the cursor is not closed at the end of the procedure implicitly. The calling procedure can then use the ASSOCIATE RESULT SET LOCATOR statement to get a handle on the result set returned by the called procedure.

Thanks for the quick response.I have one more question.
As per my knowledge on SQLDA(SQL Descriptor area), it can be used to get the details of number of columns,column names and their data type,length in a result set by using describe cursor statement.Could you throw some light on SQL Descriptor area?Will this be of any use in solving the nested procedures issue?

SQLDA is relevant to host languages only. If your calling procedure is written in SQL, you don't have an SQLCA. Also, result sets from SPs can only be accessed from applications using JDBC or CLI and not embedded SQL.

Thanks for the reply.
Thanks in advance for my next question.
How to proceed with when the number result sets returned by called procedure is not known in the calling procedure?Is there anything like describe procedure or describe cursor.

How can we create the required number of RESULT SET LOCATOR's when the number of result sets returned by called procedure is not known ?