MVSFORUMS.com

vini · Posted: Wed Mar 24, 2004 11:38 am Post subject: Db2 Stored Proc Builder Query

How does one come to know that to which Load Library the Stored Proc is pointing to , when in IBMs Db2 Stored Proc Builder ?! Apparently , I do not see it anywhere in the menu options atleast.
Anyone familiar with this Tool ?
I am trying to test changes to a stored proc ,but encountering -805. I have successfully compiled and binded in the region I wanna test it in.
I asked the DBA here , but havent got any answer from his so far Wink

kolusu · Posted: Wed Mar 24, 2004 11:59 am Post subject:

Vini,

Looking at the sqlcode of -805 , it has got something to do with the Bind.The DB2 stored procedure address space may still have the last copy of your program. I guess you made some changes to the stored procedure and complied it.But your latest copy is not found in the DB2 Address space.

I guess your stored procedure is defined with stay resident option which will keep a stored proc in memory

Change your stored procedure definition to stay resident = n and your changes will be reflected automatically.

Check this link which explains in detail about Refreshing the stored procedures environment

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

vini · Posted: Wed Mar 24, 2004 2:13 pm Post subject:

Kolusu , Appreciate your response . Guess what ? According to the DBA it has already been defined as stayresident=n . As a developer am not authorised to modify any stored proc definitions in db2. I was wondering if there is atleast someway for me to View that? Any system table which keeps this kinda info or any info around stored procs ? All I have access to is the stored procs cobol code.

Anand_R · Intermediate Joined: 24 Dec 2002 Posts: 189 Topics: 60

Vini,

In DB2 v6 , once you bind the stored procedure, the information will be stored sysibm.systrigger and before V6 it would be in sysibm.sysprocedures.

Thanks
Anand

vini · Posted: Thu Mar 25, 2004 4:49 pm Post subject:

This problem was finally resolved by the DBAs. It had something to do with WLM refreshing ,they said Rolling Eyes

and the more I asked their jargon got more n more obscure Wink

Anand, I did not find any stored proc related info in the 2 tables that you mentioned. I think it is the sysibm.sysroutines.