Reading global temporary table in same SP session

Seya · Post by **Seya** » Fri Feb 17, 2017 9:44 pm

Hi,

I am using a stored procedure stage in datastage to call a db2 Cobol stored procedure .The db2 stored procedure loads the data of to a global temporary table. As datastage ends the db2 session after the call to stored procedure, we are not able to read the data from the global temporary table. Do we have an option in datastage to read the data from the global temporary table in the same session when we are making the stored procedure call?

Thanks!

chulett · Post by **chulett** » Sat Feb 18, 2017 8:29 am

I would imagine what's going on is they're not in fact "in the same session". DataStage makes a separate connection to run the stored procedure, which establishes a new session, and then when the procedure ends and the connection is closed, the session terminates and the table is dropped.

The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current session. The declared temporary table description does not appear in the system catalog. It is not persistent and cannot be shared with other sessions. Each session that defines a declared global temporary table of the same name has its own unique description of the temporary table. When the session terminates, the rows of the table are deleted, and the description of the temporary table is dropped.

Seya · Post by **Seya** » Sat Feb 18, 2017 6:01 pm

Yes Chullet, you got the point. Do we have an option in datastage to read the data from the global temporary table in the same session when we are making the stored procedure call?

chulett · Post by **chulett** » Sat Feb 18, 2017 10:36 pm

Make it... not temporary.

Seya · Post by **Seya** » Tue Feb 21, 2017 10:30 am

I am calling the stored procedure in the Before SQL of an ODBC connector stage. The stored procedure has 5 input/output parameters that are defined. While making the call to stored procedure stage in the before SQL of ODBC connector , I am passing in parameter values. Can we read the output values of the parameter after the SP call to make sure the Stored Proc(SP) status is Success?

Call GMO01.OMSP001('#INOUT1#','#INOUT2#','#INOUT3#','#INOUT4#','#INOUT5#')
INOUT2 parameter would return the status of the SP

Is there a way in datastage to read this value since we are calling the SP in the before SQL?

Thanks for all the reply!

chulett · Post by **chulett** » Tue Feb 21, 2017 2:15 pm

Seya wrote:Is there a way in datastage to read this value since we are calling the SP in the before SQL?

Read it? No, you don't have that ability before or after sql. However, it should know if the procedure completed successfully or aborted unless you're saying the procedure is configured to always "succeed" (i.e. not raise an exception or whatever DB2 calls it) but will pass back a status as a separate output parameter?