Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    19

    Unanswered: Binding DB2 Stored procedures

    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

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Binding DB2 Stored procedures

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

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Dec 2003
    Posts
    19

    Re: Binding DB2 Stored procedures

    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
    Originally posted by sathyaram_s
    If you navigate sqllib/function/routine and its sub-folder you will find the bind file

    Cheers
    Sathyaram

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Binding DB2 Stored procedures

    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Dec 2003
    Posts
    19

    Re: Binding DB2 Stored procedures

    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

    Originally posted by sathyaram_s
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •