k_murli_krishna
asked on
PROCEDURE EXECUTION ERROR
1) We have DB2 8.1.5 on AIX 5.1. We installed gcc compiler for stored procedures.
2) We set /usr/local/bin as part of PATH and also in file sr_cpath.bat in /home/db2inst1/sqllib/func tion/routi ne which is the install path of the gcc compiler.
3) We set DB2_SQLROUTINE_COMPILER_PA TH=/home/d b2inst1/sq llib/funct ion/routin e/sr_cpath &
DB2_SQLROUTINE_COMPILE_COM MAND=cpp -fpic -D_REENTRANT -I/home/db2inst1/sqllib/in clude SQLROUTINE_FILENAME.c -shared -lpthread -o SQLROUTINE_FILENAME -L/home/db2inst1/sqllib/li b -ldb2
4) The SP's are compiling but while executing them we get:
$ db2 call proc1
SQL0444N Routine "PROC1" (specific name "SQL040923201543446") is implemented
with code in library or path ".../sqlproc/SAMPLE/DB2INS T1/P015434 7", function
"pgsjmp" which cannot be accessed. Reason code: "5". SQLSTATE=42724
We find that pgsjmp function is in file P0154347, P0154347.sqc, P0154347.c and in P0154347.log file we have following info:
-- CONTENTS
-- OF /home/db2inst1/sqllib/func tion/routi ne/sqlproc /SAMPLE/DB 2INST1/tmp /P2271903. sh
SQLROUTINE_FILENAME=P22719 03
export SQLROUTINE_FILENAME
export SQLROUTINE_ENTRY=pgsjmp
-- COMPILATION COMMAND:
cpp -fpic -D_REENTRANT -I/home/db2inst1/sqllib/in clude P2271903.c -shared -lpthread -o P2271903 -L/home/db2inst1/sqllib/li b -ldb2
-- CONTENTS
-- OF /home/db2inst1/sqllib/func tion/routi ne/sqlproc /SAMPLE/DB 2INST1/tmp /P2271903. exp
pgsjmp
-- COMPILATION MESSAGES
-- FOR /home/db2inst1/sqllib/func tion/routi ne/sqlproc /SAMPLE/DB 2INST1/tmp /P2271903. c
cpp: -lpthread: linker input file unused because linking not done
cpp: -ldb2: linker input file unused because linking not done
-- END OF LOG FILE (SQLCODE: 0)
How is SQLCODE:0 when we got error.
If we use gcc instead of cpp as start of DB2_SQLROUTINE_COMPILE_COM MAND, during compile time itself we get errors:
/usr/include/sys/context.h :155: parse error before "sigset64_t"
/usr/include/sys/context.h :158: parse error before '}' token
Please help me compile & execute our SP's.
2) We set /usr/local/bin as part of PATH and also in file sr_cpath.bat in /home/db2inst1/sqllib/func
3) We set DB2_SQLROUTINE_COMPILER_PA
DB2_SQLROUTINE_COMPILE_COM
4) The SP's are compiling but while executing them we get:
$ db2 call proc1
SQL0444N Routine "PROC1" (specific name "SQL040923201543446") is implemented
with code in library or path ".../sqlproc/SAMPLE/DB2INS
"pgsjmp" which cannot be accessed. Reason code: "5". SQLSTATE=42724
We find that pgsjmp function is in file P0154347, P0154347.sqc, P0154347.c and in P0154347.log file we have following info:
-- CONTENTS
-- OF /home/db2inst1/sqllib/func
SQLROUTINE_FILENAME=P22719
export SQLROUTINE_FILENAME
export SQLROUTINE_ENTRY=pgsjmp
-- COMPILATION COMMAND:
cpp -fpic -D_REENTRANT -I/home/db2inst1/sqllib/in
-- CONTENTS
-- OF /home/db2inst1/sqllib/func
pgsjmp
-- COMPILATION MESSAGES
-- FOR /home/db2inst1/sqllib/func
cpp: -lpthread: linker input file unused because linking not done
cpp: -ldb2: linker input file unused because linking not done
-- END OF LOG FILE (SQLCODE: 0)
How is SQLCODE:0 when we got error.
If we use gcc instead of cpp as start of DB2_SQLROUTINE_COMPILE_COM
/usr/include/sys/context.h
/usr/include/sys/context.h
Please help me compile & execute our SP's.
Better yet, if you upgrade to DB2 8.2 (8.1 Fixpak 7) - you no longer need to compile SQL procedures (of course this assumes yours is a SQL and not a C sp)!
yes, bondtrader is correct, this is the standard IBM reply for the really poor implementation of stored prcoedures in v8.1 up to fixpack 6. In fact, the developer team at IBM has very little idea how to fix these types of problems, thats why the mantra is upgrade to Stinger.
I would suggest that your problem lies in the dll versions that the stored procedure dll relies on. Therefore, to fix your problem, open the dll that was created by the create stored procedure command in debug mode and make a note of all the dll's it refers to. Then, on your UNIX box, check for multiple copies of these dll's and make sure all the dll's referenced by the stored proc dll exist on the machine. If not, make sure to install them.
Drop and re create the procedure. Run it again, inform me if it works. You may have to grant execute on procedure to user and or group.
I would suggest that your problem lies in the dll versions that the stored procedure dll relies on. Therefore, to fix your problem, open the dll that was created by the create stored procedure command in debug mode and make a note of all the dll's it refers to. Then, on your UNIX box, check for multiple copies of these dll's and make sure all the dll's referenced by the stored proc dll exist on the machine. If not, make sure to install them.
Drop and re create the procedure. Run it again, inform me if it works. You may have to grant execute on procedure to user and or group.
ASKER
Thanks Bondtrader & ghp7000. Few things I could not understand.
In /home/db2inst1/sqllib/func tion/routi ne/sqlproc /SAMPLE/DB 2INST1, files of type P2244519, P2244519.c, P2244519.log, P2244519.scm & P2244519.sqc are getting created sometimes & at others, files of type P2141500.bnd, P2141500.c, P2141500.log, P2141500.scm & P2141500.sqc are getting created in tmp directory under above path. Why this dual behaviour?
Does the definer of SP have to be granted execute on SP.
Where can I find the dll files in AIX & I assume these get created during compile time.
My sr_cpath has following contents:
PATH=$PATH:/usr/local/bin
PATH=$PATH:/home/db2inst1/ sqllib/fun ction/rout ine/sr_cpa th
export PATH
Is this okay. My other 2 important parameters are:
DB2_SQLROUTINE_COMPILER_PA TH=/home/d b2inst1/sq llib/funct ion/routin e/sr_cpath &
DB2_SQLROUTINE_COMPILE_COM MAND=cpp -fpic -D_REENTRANT -I/home/db2inst1/sqllib/in clude SQLROUTINE_FILENAME.c -shared -lpthread -o SQLROUTINE_FILENAME -L/home/db2inst1/sqllib/li b -ldb2
Are these 2 okay. With gcc instead of cpp, the SP's were not getting compiled => errors.
In /home/db2inst1/sqllib/func
Does the definer of SP have to be granted execute on SP.
Where can I find the dll files in AIX & I assume these get created during compile time.
My sr_cpath has following contents:
PATH=$PATH:/usr/local/bin
PATH=$PATH:/home/db2inst1/
export PATH
Is this okay. My other 2 important parameters are:
DB2_SQLROUTINE_COMPILER_PA
DB2_SQLROUTINE_COMPILE_COM
Are these 2 okay. With gcc instead of cpp, the SP's were not getting compiled => errors.
ASKER
bondtrader & ghp7000, please respond to my comment so that I can close this question.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ghp7000, the below is total list of dll's present when searched from root:
/ar/arpmd/ppm/PICMine/lib/ db2jdbc.dl l
/ar/arpmt/ppm/PICMine/lib/ db2jdbc.dl l
/ar/arpmu/ppm/PICMine/lib/ db2jdbc.dl l
/usr/opt/db2_08_01/samples /icmdemo/s croll.dll
When opened in vi editor, they all show junk info. The ones which get created on compiling stored procedure are not dll's.
We have VisualAge C++ but this eval version has expired. Can we put date back so that it will work. What things may be affected by this. Can you suggest any other compiler for SQL stored procs for DB2 8.1.5 on AIX 5.1.
/ar/arpmd/ppm/PICMine/lib/
/ar/arpmt/ppm/PICMine/lib/
/ar/arpmu/ppm/PICMine/lib/
/usr/opt/db2_08_01/samples
When opened in vi editor, they all show junk info. The ones which get created on compiling stored procedure are not dll's.
We have VisualAge C++ but this eval version has expired. Can we put date back so that it will work. What things may be affected by this. Can you suggest any other compiler for SQL stored procs for DB2 8.1.5 on AIX 5.1.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.