Topic
No replies
SystemAdmin
SystemAdmin
1731 Posts
ACCEPTED ANSWER

Pinned topic Cannot execute a stored procedure linking to External C program

‏2013-01-10T21:25:54Z |
DB2 version: 9.7.0.6 OS: RHEL 5

Here are the steps I took to create and deploy a C routine:

1> Created myprog.sqC file
2> Executed db2 prep myprog.sql bindfile
3> Executed db2 bind myprog.bnd
4> Compiled the C code by executing

gcc -I /.../sqllib/include -c -fPIC myprog.C
5> Created a shared library i.e. myprog.dll by executing

gcc -shared -o myprog.dll myprog.o -L /.../sqllib/lib64 -ldb2
6> Copied over the dll file created from Step 5 to /.../sqllib/function/. Set the permission 755 on the myprog.dll file

7> Created stored proc as follows:

create procedure myschema.test_func (IN INT_VAL1 INTEGER, OUT INT_VAL2 INTEGER) SPECIFIC test_func1 DYNAMIC RESULT SETS 0 NO SQL NOT DETERMINISTIC LANGUAGE C FENCED THREADSAFE PARAMETER STYLE SQL EXTERNAL NAME 'myprog!myfunc'

8> However, when I run the db2 command line : call myschema.test_func(10, ?), I get the error:

SQL0444N Routine "myschema.test_func" (specific name "test_func") is
implemented with code in library or path ".../sqllib/function/myprog",
function "myfunc" which cannot be accessed. Reason code: "4".
SQLSTATE=42724
I have been looking around for quite some time and frankly, I have given up.

Can anyone please help me out here to debug/understand the reason behind error in Step 8?