Stored Procedures for AUTO BIND

shaik Madar vali · New User Joined: 28 Apr 2005 Posts: 8

Hi,

Is there anyway to REBIND a DB2 program thru Stored Procedures whenever -805 is occured while running a program?

If so, please let me know the approach.

Thanks in advance.

anamikak · New User Joined: 10 May 2006 Posts: 64 Location: Singapore

You can always include the Stored procedure package in your PKLIST of your application plan. The Stored procedure will execute on the same thread as your application plan.

Anamika

shaik Madar vali · New User Joined: 28 Apr 2005 Posts: 8

HI Anamika,

Thanks for your information. But i want to code a stored procedure to REBIND the existing package for the programe which has abended with -805.

EX: In my COBOL program i want to code like this.

EXEC SQL
SELECT COL1 INTO WS-COL1
FROM TAB1
WHERE COL1 = :LS-COL1
END-EXEC.

EVALUATE SQLCODE

WHEN +100
DISPLAY 'NO SUCH ROW EXIST'

WHEN -805
CALL "STPROC" USING WS-PGM-NAME

WHEN OTHERS
CALL "ERROR-RUTINE" USING ABEND-CODE

END-EVALUATE.

==========

CREATE OR REPLACE STORED PROCEDURE
( IN PROGRAM-NAME X(8) )

BEGIN
XXXXXXXXXXX ( help me in writing a REBIND Utility to bind the PROGRAM-NAME passed by the cobol program)
XXXXXXXXX
XXXXXXXXX

END.

anamikak · New User Joined: 10 May 2006 Posts: 64 Location: Singapore

Simply rebinding the existing package will not help you resolve -805. I think that resolving -805 is a slightly eloborate process and it simply cannot be done by wiriting SQL procedure( which you have outlined).

In fact Tools like Platinum detector or BMC Apptune are available which take you step by step to resolve -805. Even if these tools are not supported in your installation, you have to query a series of Db2 catlog tables and then do a bind.

If you want to try writing a stored procedure, notwithstanding the elaborate processes that i mentioned above, you can try writing a REXX stored procedure instead of an SQL procedure.

Anamika

shaik Madar vali · New User Joined: 28 Apr 2005 Posts: 8

Hi Anamika,

Thanks for the above information. And i am new to REXX stored Procedure. Can you please tell me the process in briefly or any documents on the same will be a great helpful to me.

Thanks in advance.