Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    7

    Thumbs down Unanswered: gcc stored procedure compilation

    Hi,

    Using DB2 7.1.
    Had to get the free gcc compiler to get stored procedures to compile.
    (which was bit of a nightmare to get installed and working!)
    Come on IBM - people do use SQL stored procedures a lot!!!!
    However, there is a constant issue that we can work around but which is very annoying.

    When builiding stored procedures, if you try to compile a procedure with a parameter count different to the last procedure you compiled then you get an error - you have to go and manually change the value of the
    pgsjmp@ parameter which is set in the standard.def file in the sqllib
    directory.
    e.g pgsjmp@4 for no parameters
    pgsjmp@16 for 2 parameters etc.

    Output is in the attached txt file of the errof -(refers to visual studio but we are using gcc).
    Anyone have this problem or know how to get around it?
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2002
    Posts
    134

    Re: gcc stored procedure compilation

    I used the article below as a starting point (i modified def file, probably there is a better solution):
    http://www7b.software.ibm.com/dmdd/l...306haungs.html


    The following procedure worked for me:
    1. Make sure your cygwin installation has both gcc,dllwrap and sed, if not – install
    2. Create db2gcc.bat and set DB2PATH variable inside. db2gcc.bat should have the following content

    setlocal
    @REM Set the DB2PATH variable to your DB2 base path, e.g., c:\ibm\sqllib
    set DB2PATH=c:\sqllib
    gcc -c -g -mno-cygwin -I%DB2PATH%\include %1.c -o %1.o
    sed -e s/\_// %1.def > %1_m.def
    dllwrap -mno-cygwin -target i386-mingw32 -o %1.dll %1.o %DB2PATH%\lib\db2api.lib --def %1_m.def
    rm %1_m.def

    3. Set db2 registry variable

    db2set DB2_SQLROUTINE_COMPILER_PATH=
    db2set DB2_SQLROUTINE_COMPILE_COMMAND=”c:\cygwin\bin\db2g cc.bat SQLROUTINE_FILENAME”

    regards,
    dmitri

  3. #3
    Join Date
    Dec 2002
    Posts
    134

    Re: gcc stored procedure compilation

    Mentioned above procedure is for windows, on AIX 5.2 it's much simpler. I do not know anything about other OS.

    db2set DB2_SQLROUTINE_COMPILE_COMMAND="gcc -g -I/home/test32/sqllib/include SQLROUTINE_FILENAME.c -o SQLROUTINE_FILENAME -L/home/test32/sqllib/lib -ldb2 -shared"

  4. #4
    Join Date
    Mar 2004
    Posts
    4

    Re: gcc stored procedure compilation

    Originally posted by chuzhoi
    Mentioned above procedure is for windows, on AIX 5.2 it's much simpler. I do not know anything about other OS.

    db2set DB2_SQLROUTINE_COMPILE_COMMAND="gcc -g -I/home/test32/sqllib/include SQLROUTINE_FILENAME.c -o SQLROUTINE_FILENAME -L/home/test32/sqllib/lib -ldb2 -shared"
    Hi Chuzhoi,

    When I used the above gcc setup to compile the SQL procedure in AIX 5.1, I got the following error:

    -- COMPILATION COMMAND:

    gcc -I/home/db2/sqllib/include P8224396.c -shared -o P8224396 -L/home/db2/sqllib/lib -ldb2


    -- CONTENTS OF /home/db2/sqllib/function/routine/sqlproc/SGAIX/DB2/tmp/P8224396.exp

    pgsjmp


    -- COMPILATION MESSAGES FOR /home/db2/sqllib/function/routine/sqlproc/SGAIX/DB2/tmp/P8224396.c
    P8224396.sqc: In function `pgsjmp':
    P8224396.sqc:621: storage size of `sql_setdlist' isn't known
    P8224396.sqc:677: storage size of `sql_setdlist' isn't known

    Any idea what is wrong?

  5. #5
    Join Date
    Mar 2004
    Posts
    4

    Re: gcc stored procedure compilation

    Originally posted by chuzhoi
    Mentioned above procedure is for windows, on AIX 5.2 it's much simpler. I do not know anything about other OS.

    db2set DB2_SQLROUTINE_COMPILE_COMMAND="gcc -g -I/home/test32/sqllib/include SQLROUTINE_FILENAME.c -o SQLROUTINE_FILENAME -L/home/test32/sqllib/lib -ldb2 -shared"
    Hi Chuzhoi,

    When I used the above gcc setup to compile the SQL procedure in AIX 5.1, I got the following error:

    -- COMPILATION COMMAND:

    gcc -I/home/db2/sqllib/include P8224396.c -shared -o P8224396 -L/home/db2/sqllib/lib -ldb2


    -- CONTENTS OF /home/db2/sqllib/function/routine/sqlproc/SGAIX/DB2/tmp/P8224396.exp

    pgsjmp


    -- COMPILATION MESSAGES FOR /home/db2/sqllib/function/routine/sqlproc/SGAIX/DB2/tmp/P8224396.c
    P8224396.sqc: In function `pgsjmp':
    P8224396.sqc:621: storage size of `sql_setdlist' isn't known
    P8224396.sqc:677: storage size of `sql_setdlist' isn't known

    Any idea what is wrong?

  6. #6
    Join Date
    Apr 2004
    Posts
    13
    Hi ,

    I think you command should be as follows

    db2set DB2_SQLROUTINE_COMPILE_COMMAND="gcc -g -I/home/<INSTANCE_NAME>/sqllib/include SQLROUTINE_FILENAME.c -G -o SQLROUTINE_FILENAME -L/home/rtv1inst/sqllib/lib -R/home/<INSTANCE_NAME>/sqllib/lib -ldb2

    you have missed the follwoing in your command

    -R/home/<INSTANCE_NAME>/sqllib/lib -ldb2

    thanks
    Lahiru.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    See if this presentation helps

    http://www.gseukdb2.org.uk/downloads/0403hiddengems.zip

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

Posting Permissions

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