Db2 – Cannot execute stored proc linked to C routine in DB2

db2

DB2 version: 9.7.0.6
OS: RHEL 5

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

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?

Best Answer

Your compiled shared library is named myprog.dll, but you've told it to look for one called myprog in the create procedure call (the .dll suffix is a Windows-ism).

Rename it from myprog.dll to myprog.

Related Question