calling stored procedure from Perl code

[rajpar@gmail.com]

Environment: Solaris (client + server)
db2 version 7.2 latest fixpak (DB2 v7.1.0.111", "s050516" and
"U803330")
Compiler: gcc

Here is my SP code executed on the client:
CREATE PROCEDURE db2user.x (IN value INT)
LANGUAGE SQL
BEGIN
INSERT INTO db2dba.t1 values (value);
END @

Here are my server db2 registry variables:
[i] DB2_SQLROUTINE_ COMPILER_PATH=/export/home/db2v7/sqllib/function/
routine/sr_cpath
[i] DB2_SQLROUTINE_ COMPILE_COMMAND =gcc -I/export/home/db2v7/sqllib/
include SQLROUTINE_FILE NAME.c -G -o SQLROUTINE_FILE NAME -L/export/home/
db2v7/sqllib/lib -R/export/home/db2v7/sqllib/lib -ldb2

I issue the create procedure stmt on the client, and this does indeed
create a stored procedure on the server in thef following directory: /
export/home/db2v7/sqllib/function/routine/sqlproc/SAMPLE/DB2USER. I
cp the resulting shared library (P0840200) to /export/home/db2v7/
sqllib/function.

I then invoke the SP from the command line: db2 "call x(3)" .. and
this works because a row is indeed inserted into table t1.

Now here's the problem: I want to call the same SP from perl code:
#!/usr/bin/perl -w

use strict;
use DBI;
use DBD::DB2;

$ENV{'DB2INSTAN CE'} = "db2prod";
my $db_connect="db i:DB2:SAMPLE";
my $user="db2user" ;
my $passwd="jD8l14 ";

my $dbh = DBI->connect( $db_connect, "$user", "$passwd" ,
{AutoCommit =0} ) || die "connect failed";

sub Main {
my $rc = $dbh->do("call P0840200!x(36)" ) || die "call SP
failed";
print STDOUT " result: $rc \n\n";

$dbh->rollback;
$dbh->disconnect;
}
Main;


Unfortunately, this does NOT work .. the following is returned when
executing the perl application:
DBD::DB2::db do failed: [IBM][CLI Driver][DB2/SUN] SQL10010N The
specified library, "P0840200", was loaded, but the function "x" could
not be executed.
call SP failed at x.pl line 18.

Any ideas? The output of the /export/home/db2v7/sqllib/function/
routine/sqlproc/SAMPLE/DB2USER/P0840200.log file is as follows:

*** BIND /export/home/db2v7/sqllib/function/routine/sqlproc/
SAMPLE/DB2USER/tmp/P0840200.sqc ***

LINE MESSAGES FOR P0840200.sqc
------
--------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.

*** COMPILE /export/home/db2v7/sqllib/function/routine/sqlproc/
SAMPLE/DB2USER/tmp/P0840200.c ***

DB2_SQLROUTINE_ COMPILER_PATH=/export/home/db2v7/sqllib/function/
routine/sr_cpath

DB2_SQLROUTINE_ COMPILE_COMMAND =gcc -fPIC -I/export/home/db2v7/
sqllib/include SQLROUTINE_FILE NAME.c -G -o SQLROUTINE_FILE NAME -L/
export/home/db2v7/sqllib/lib -R/export/home/db2v7/sqllib/lib -ldb2

#!/bin/sh
PATH=$PATH:/usr/local/bin/gcc:/usr/local/bin
export PATH

# CC=gcc
# export CC

SQLROUTINE_FILE NAME=P0840200
export SQLROUTINE_FILE NAME

gcc -fPIC -I/export/home/db2v7/sqllib/include P0840200.c -G -o
P0840200 -L/export/home/db2v7/sqllib/lib -R/export/home/db2v7/sqllib/
lib -ldb2

*** /export/home/db2v7/sqllib/function/routine/sqlproc/SAMPLE/
DB2USER/tmp/P0840200.exp ***
pgsjmp




----------------------------------------------------------------------------


Note the last line in the .log file .. if I instead change the perl
code to CALL P0840200!pgsjmp instead of P0840200!x, then the following
is returned:
DBD::DB2::db do failed: [IBM][CLI Driver][DB2/SUN] SQL1131N DARI
(Stored Procedure) process has been terminated abnormally.
SQLSTATE=38503
call SP failed at x.pl line 15.

Instead of CALL P0840200!x, if I issue P0840200!x or P0840200!XX or
P0840200!abc, I still get the same SQL10010N error:
DBD::DB2::db do failed: [IBM][CLI Driver][DB2/SUN] SQL10010N The
specified library, "P0840200", was loaded, but the function "xx" could
not be executed.
call SP failed at x.pl line 15.

If the shared library is indeed being loaded, why is the function x
not being recognized? Does this have anything to do with .exp?
Shouldn't "x" be there in place of "pgsjmp"? if so, how can this be
changed given that the SP is being created automatically?

Or does this have anything to do with the DBD::DB2 driver I'm using,
and whether it supports the invocation of SPs? I would think so given
that the shared library is being loaded???

Thanks in advance for your help.

[Ian]

Re: calling stored procedure from Perl code

rajpar@gmail.co m wrote:

Environment: Solaris (client + server)
db2 version 7.2 latest fixpak (DB2 v7.1.0.111", "s050516" and
"U803330")
Compiler: gcc
>
Here is my SP code executed on the client:
CREATE PROCEDURE db2user.x (IN value INT)
LANGUAGE SQL
BEGIN
INSERT INTO db2dba.t1 values (value);
END @
>
Here are my server db2 registry variables:
[i] DB2_SQLROUTINE_ COMPILER_PATH=/export/home/db2v7/sqllib/function/
routine/sr_cpath
[i] DB2_SQLROUTINE_ COMPILE_COMMAND =gcc -I/export/home/db2v7/sqllib/
include SQLROUTINE_FILE NAME.c -G -o SQLROUTINE_FILE NAME -L/export/home/
db2v7/sqllib/lib -R/export/home/db2v7/sqllib/lib -ldb2
>
I issue the create procedure stmt on the client, and this does indeed
create a stored procedure on the server in thef following directory: /
export/home/db2v7/sqllib/function/routine/sqlproc/SAMPLE/DB2USER. I
cp the resulting shared library (P0840200) to /export/home/db2v7/
sqllib/function.

You don't need to copy this file. DB2 knows how to resolve where the
file should be.

I then invoke the SP from the command line: db2 "call x(3)" .. and
this works because a row is indeed inserted into table t1.

Good.

Now here's the problem: I want to call the same SP from perl code:
>
[...]
>
sub Main {
my $rc = $dbh->do("call P0840200!x(36)" ) || die "call SP
failed";

Why aren't you just doing

my $rc = $dbh->do("call x(36)") || die "call SP failed";


You don't need to specify the name of the library or anything else.
When DB2 creates the stored procedure, it keeps track of what stored
procedures reference what libraries.