Solved

PROCEDURE EXECUTION ERROR

Posted on 2004-09-23
9
632 Views
Last Modified: 2008-01-09
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/function/routine which is the install path of the gcc compiler.

3) We set DB2_SQLROUTINE_COMPILER_PATH=/home/db2inst1/sqllib/function/routine/sr_cpath &
DB2_SQLROUTINE_COMPILE_COMMAND=cpp -fpic -D_REENTRANT -I/home/db2inst1/sqllib/include SQLROUTINE_FILENAME.c -shared -lpthread -o SQLROUTINE_FILENAME -L/home/db2inst1/sqllib/lib -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/DB2INST1/P0154347", 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/function/routine/sqlproc/SAMPLE/DB2INST1/tmp/P2271903.sh

SQLROUTINE_FILENAME=P2271903
export SQLROUTINE_FILENAME
export SQLROUTINE_ENTRY=pgsjmp

-- COMPILATION COMMAND:

cpp -fpic -D_REENTRANT -I/home/db2inst1/sqllib/include P2271903.c -shared -lpthread -o P2271903 -L/home/db2inst1/sqllib/lib -ldb2

-- CONTENTS
-- OF /home/db2inst1/sqllib/function/routine/sqlproc/SAMPLE/DB2INST1/tmp/P2271903.exp

pgsjmp

-- COMPILATION MESSAGES
-- FOR /home/db2inst1/sqllib/function/routine/sqlproc/SAMPLE/DB2INST1/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_COMMAND, 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.
0
Comment
Question by:k_murli_krishna
  • 3
  • 2
  • 2
9 Comments
 
LVL 4

Expert Comment

by:bondtrader
ID: 12160406
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)!
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 12193259
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.
0
 
LVL 17

Author Comment

by:k_murli_krishna
ID: 12239918
Thanks Bondtrader & ghp7000. Few things I could not understand.

In /home/db2inst1/sqllib/function/routine/sqlproc/SAMPLE/DB2INST1, 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/function/routine/sr_cpath
export PATH

Is this okay. My other 2 important parameters are:

DB2_SQLROUTINE_COMPILER_PATH=/home/db2inst1/sqllib/function/routine/sr_cpath &
DB2_SQLROUTINE_COMPILE_COMMAND=cpp -fpic -D_REENTRANT -I/home/db2inst1/sqllib/include SQLROUTINE_FILENAME.c -shared -lpthread -o SQLROUTINE_FILENAME -L/home/db2inst1/sqllib/lib -ldb2

Are these 2 okay. With gcc instead of cpp, the SP's were not getting compiled => errors.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 17

Author Comment

by:k_murli_krishna
ID: 12250594
bondtrader & ghp7000, please respond to my comment so that I can close this question.
0
 
LVL 13

Accepted Solution

by:
ghp7000 earned 63 total points
ID: 12252065
every time you try and compile a stored procedure, the p*.log files are generated. Therefore, each compile attempt has an associated log file, dll and bnd file. You can erase all of these files.
 
if the definer of the sp is not the same user as the schema referenced in the sp, then you will have to grant execute on sp to user or group.

sorry, cant answer your question on the compiler string you are using, but you can check the meaning of each switch on the compiler home page for the product you are using.

also, I really am not very familiar with AIX, so I cant help you there either, but a simple search on the drive should suffice. Remember, the idea is to match the dll's referenced by the stored procedure to see if those dll's exist on your aix machine or to see if you have multiple copies of these dll's on your machine and if you do, if all of the copies have the same file timestamp.
0
 
LVL 17

Author Comment

by:k_murli_krishna
ID: 12288841
ghp7000, the below is total list of dll's present when searched from root:

/ar/arpmd/ppm/PICMine/lib/db2jdbc.dll
/ar/arpmt/ppm/PICMine/lib/db2jdbc.dll
/ar/arpmu/ppm/PICMine/lib/db2jdbc.dll
/usr/opt/db2_08_01/samples/icmdemo/scroll.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.
0
 
LVL 4

Assisted Solution

by:bondtrader
bondtrader earned 62 total points
ID: 12288865
You should be able to use gcc on AIX.  Again though, the nice thing about 8.1.7 (aka 8.2) is that you don't need ANY compiler for SQL Procedures...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now