Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    5

    Angry Unanswered: DB2 Stored Procedure Compilation - pgsjmp

    Hello

    I am pretty new to DB2 Scripting. I am using DB2 UDB 8.1 fixpack 3 on Solaris .
    Written a simple stored procedure like.

    CREATE PROCEDURE loop_until_space(OUT counter INT)
    LANGUAGE SQL
    P1: BEGIN
    DECLARE v_firstnme VARCHAR(12);
    DECLARE v_midinit CHAR(1);
    DECLARE v_lastname VARCHAR(15);
    DECLARE v_counter SMALLINT DEFAULT 0;

    DECLARE c1 CURSOR FOR
    SELECT ACCOUNT_OFFICER.ACCT_OFFICER_NM,
    ACCOUNT_OFFICER.ACCT_OFFICER_NM,
    ACCOUNT_OFFICER.ACCT_OFFICER_REGION
    FROM GCS.ACCOUNT_OFFICER
    AS ACCOUNT_OFFICER;
    --SELECT firstnme, midinit, lastname
    --FROM employee
    --ORDER BY midinit DESC;
    -- DECLARE CONTINUE HANDLER FOR NOT FOUND
    -- SET counter = -1;

    -- initialize OUT parameter
    -- SET counter = 0;
    OPEN c1;
    fetch_loop:
    LOOP
    FETCH c1 INTO
    v_firstnme, v_midinit, v_lastname;
    -- Use a local variable for the iterator variable
    -- because SQL procedures only allow you to assign
    -- values to an OUT parameter
    -- SET v_counter = v_counter + 1;
    IF v_midinit = ' ' THEN
    LEAVE fetch_loop;
    END IF;
    END LOOP fetch_loop;
    CLOSE c1;

    -- Now assign the value of the local
    -- variable to the OUT parameter
    -- SET counter = v_counter;
    END p1
    ************************************************** ********
    When I am trying to build this procedure , it throws an error

    GCS.loop_until_space - Build started.
    Create stored procedure returns -7032.

    -- LOG FILE P9483692.log FOR PROCEDURE GCS .LOOP_UNTIL_SPACE

    -- DB2_SQLROUTINE_PREPOPTS=

    -- PREP/BIND MESSAGES FOR /export/home/db2srv/sqllib/function/routine/sqlproc/GCS_STAG/GCS/tmp/P9483692.sqc

    LINE MESSAGES FOR P9483692.sqc
    ------ --------------------------------------------------------------------
    SQL0060W The "C" precompiler is in progress.
    SQL0091W Precompilation or binding was ended with "0"
    errors and "0" warnings.

    -- DB2_SQLROUTINE_COMPILER_PATH=/export/home/db2srv/sqllib/function/routine/sr_cpath

    -- DB2_SQLROUTINE_COMPILE_COMMAND=cc -I/export/home/db2srv/sqllib/include SQLROUTINE_FILENAME.c -shared -o SQLROUTINE_FILENAME -L/export/home/db2srv/sqllib/lib -ldb2

    -- CONTENTS OF /export/home/db2srv/sqllib/function/routine/sr_cpath

    #!/bin/sh
    PATH=$PATH:/usr/local/bin:/opt/SUNWspro/bin
    export PATH

    -- CONTENTS OF /export/home/db2srv/sqllib/function/routine/sqlproc/GCS_STAG/GCS/tmp/P9483692.sh

    SQLROUTINE_FILENAME=P9483692
    export SQLROUTINE_FILENAME

    -- COMPILATION COMMAND:

    cc -I/export/home/db2srv/sqllib/include P9483692.c -shared -o P9483692 -L/export/home/db2srv/sqllib/lib -ldb2


    -- CONTENTS OF /export/home/db2srv/sqllib/function/routine/sqlproc/GCS_STAG/GCS/tmp/P9483692.exp

    pgsjmp


    -- COMPILATION MESSAGES FOR /export/home/db2srv/sqllib/function/routine/sqlproc/GCS_STAG/GCS/tmp/P9483692.c
    P9483692.sqc: In function `pgsjmp':
    P9483692.sqc:465: storage size of `sql_setdlist' isn't known
    P9483692.sqc:492: storage size of `sql_setdlist' isn't known
    P9483692.sqc:492: storage size of `sql_setdlist' isn't known

    -- END OF LOG FILE (SQLCODE: -7032)

    GCS.loop_until_space - Build failed.
    GCS.loop_until_space - Roll back completed successfully.
    ************************************************** ********
    Point to note is : If I comment out all 'set ' commands like Set counter = V_count +1 , etc and assignment operators Then it compiles .

    Any hing on this? What is this function 'pgsjmp' ? What are the settings required to succcessfully compile a proc.

    Any help on this will be greatly appreciated.

    Regards
    Swati

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    SQL stored procedures are a bit of a mess... they'll be fixed in Stinger, a C compiler will no longer be required (yay!)

    A good reference in the mean time is:
    "Application Development Guide: Building and Running Applications", part of the db2 docs.

    This particular error I haven't seen, but it looks like your compiler can't resolve some type that sql_setdlist depends on? I'm not a C compiler guru by any stretch.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    May 2004
    Posts
    5

    Question

    Hi Jonathan

    Thanks for the reply. But still I am not able to resolve the problem. I am now doing the conversion on a oracle database and then migrate it to DB2.

    Can you suggest a way where I can migrate the data from Oracle to DB2...using ODBC just like taking a dump. I have table created at both instances.


    By the way where can I get the 'Application Development Guide: Building and Running Applications' book /material?

    Regards
    Swati

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What compiler are you using ? If it is one of IBM supported (Application Building Guide has the list) , then get in touch with IBM support ...

    Visit the website, www.ibm.com/db2/migration for migration resources .... There is a redbook on 'Oracle to DB2 Migration' at www.redbooks.ibm.com which you may find very helpful ...

    See the thread 'Useful DB2 Stuff' for link to the manuals ...

    HTH

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

  5. #5
    Join Date
    May 2004
    Posts
    5
    I am using GCC compiler.

  6. #6
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Swatis
    Can you suggest a way where I can migrate the data from Oracle to DB2...using ODBC just like taking a dump. I have table created at both instances.


    By the way where can I get the 'Application Development Guide: Building and Running Applications' book /material?
    Books:
    http://www-306.ibm.com/software/data...manualsv8.html

    See pg 31-32, seems to deal with the set-up for compiling.

    You could do it with ODBC in theory, using federated databases, although the set-up might be a bit much to deal with.

    Use sathyaram_s's links.

    (BTW - I used to use DB2 on Solaris, and it was wonderful! Rock solid... even the folks in the lab praise it as one of the best OS's out there for DB2)
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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