Hi ,
I have written stored procedures using SQL language in DB2 UDB V8.1. I used DB Artisan to create the stored procedure. It did the precompile and bind. In solaris, i see that it has created P1144436 <name of the package> with.sqc ,.c , .log and one with no extension.

I need to bind this package with different parameters < Validate RUN>. I am searching for the bind file name and location.

I am using Language as SQL, i am confused why this procedure is build as EXTERNAL ??? Please help


Could some one please help me

Thanks
Venkat

If you navigate sqllib/function/routine and its sub-folder you will find the bind file

Cheers
Sathyaram

hi sathyaram,
Thanks for the reply. As I mentioned, I only see the Package name P1144436.c , P1144436.sqc, P1144436.log files and one P1144436 file with no extension. But i couldnt find a .bnd extension file for P1144436.

Your help is highly appreciated

thanks

To retain the files, I think you can consider using

db2set DB2_SQLROUTINE_KEEP_FILES=Y

I think you need a database restart before this parm comes into effect

You can use

db2set DB2_SQLROUTINE_PREPOPTS=DATETIME ISO (as an example)

to give precompile options for CREATE PROCEDURE

HTH

Sathyaram

Thanks for your reply. I tried it, but it didnt work out for me. Could u please let me know , what stored procedure builder are you using. I am using DB Artisan product and creating stored procedures with that. Even after writing the stored procedure in SQL, it is getting converted into C codes, and gets precompiled and binds.

And i cant find any file with .bnd


I have attached a sample procedure for you to verify:

CREATE PROCEDURE VECAP.SEL_TEST
(OUT O_SQLCODE INTEGER,
OUT O_SQLSTATE CHARACTER(5),
OUT O_MESSAGE CHARACTER(40)
)
SPECIFIC VECAP.SEL_TEST
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE OERRMESS CHAR(40) DEFAULT '';



--********* CONDITION DECLARATIONS *****************
--

--********* CURSOR DECLARATIONS *****************
DECLARE C1 CURSOR WITH RETURN FOR
SELECT * FROM VECAP.TEST;



--********* HANDLER DECLARATIONS *****************
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE, SQLCODE, OERRMESS
INTO O_SQLSTATE, O_SQLCODE, O_MESSAGE
FROM SYSIBM.SYSDUMMY1;

DECLARE EXIT HANDLER FOR NOT FOUND
SELECT SQLSTATE, SQLCODE, OERRMESS
INTO O_SQLSTATE, O_SQLCODE, O_MESSAGE
FROM SYSIBM.SYSDUMMY1;

DECLARE CONTINUE HANDLER FOR SQLWARNING
SELECT SQLSTATE, SQLCODE, OERRMESS
INTO O_SQLSTATE, O_SQLCODE, O_MESSAGE
FROM SYSIBM.SYSDUMMY1;






--********* SQL PROCEDURE STATEMENTS BEGIN **********

-- INITALIZE OUTPUT SQL PARAMETERS
SET O_SQLCODE = 0;
SET O_SQLSTATE = '00000';
SET O_MESSAGE = 'SELECT SUCCESS ';
SET OERRMESS = 'ERROR: SELECT FAILED';
OPEN C1;
END;

Please tell me if i am supposed to code any other parameter for defining the stored proc other than these. DO i need to run FENCED or not? etc....

Thanks
Venkat