Calling DB2 stored procedure

Folks,

I am trying to call a DB2 stored procedure through a TeamSite CGI script that is supposed to return multiple rows. However, on running the script nothing is returned. If the same call is made through the DB2 Connect Client the stored procedure is executing and returning the rows back. I have attached the code with this post.

Any thoughts on where I might be going wrong will be greatly welcome.

Samarth

#!j:\iw-perl\bin\iwperl

use DBI;
use CGI qw( :standard );
use CGI::Carp qw( warningsToBrowser fatalsToBrowser );

my $subsystem = "DD0G";
#my $collectionID = "AVGI00";
my $username = "WECIAINT";
my $password = "CIA321IN";
#my $applicationName = "CIA";
#my $applicationID = "CREWNET";
#my $timestamp = "2004-08-09-11.20.32.948427";
#my $esevere = "N";
#my $enum = "0";
#my $egroup = "0";
#my $esqlcd = "0";
#my $epgm = " ";
#my $etext = " ";
#my $edbavail = "Y";
#my $esqlca = " ";

print( "Content-type: text/html\n\n" );
print( "<html>\n" );
print( "<head>\n" );
print( "\t<title>DBI Connection Test</title>\n" );
print( "</head>\n" );
print( "<body>\n" );

DBI->trace(15, "myscript.trc");

my $dbh = DBI -> connect( "DBI Smiley Surprised

DBC:$subsystem", "$username", "$password" );
if( !$dbh ) {
print( "Unable to connect to database!!" . DBI -> errstr );
exit;
}
else {
print( "Connected to DB2!!\n" );
}

my $sth = $dbh -> prepare( q{ CALL DD0GWIN1.EPOSP529('AVGI04','CIA','WECIAINT','CREWNET','2005-03-17-12.26.41.469562','N',0,0,0,' ',' ','Y',' ','SAMARTH') } );

$sth -> execute();
if( $@ ) {
print( "Execute stored procedure failed" );
$dbh -> disconnect;
exit;
}

print( "Executed stored procedure" );
do {

while (my @row = $sth->fetchrow_array()) {

print( "<p>" . "$row[0]" . " " . "$row[1]" . " " . "$row[2]" . " " . "$row[3]" . " " . "$row[4]" . " " . "$row[5]" . " " . "$row[6]" . " " . "$row[7]" . " " . "$row[8]" . " " . "$row[9]" . " " . "$row[10]" . " " . "$row[11]" . " " . "$row[12]" . " " . "$row[13]" . " " . "$row[14]" . " " . "$row[15]" . " " . "$row[16]" . " " . "$row[17]" . " " . "$row[18]" . " " . "$row[19]" . " " . "$row[20]" . " " . "$row[21]" . " " . "$row[22]" . " " . "$row[23]" . " " . "$row[24]" . " " . "$row[25]" . " " . "$row[26]" . " " . "$row[27]" . " " . "$row[28]");
}
}while( $sth->{db2_more_results} );

$dbh -> commit;
$sth -> finish();
$dbh -> disconnect();

print( "\n</body>" );
print( "</html>\n" );

Find more posts tagged with

Comments

akshathp

have you verified if your db connection is setting up or not?
did you try using DBD: Smiley Very Happy

B2 for dsn connection string? Something like this:

use DBD: Smiley Very Happy

B2;
my $dbh = DBI -> connect( "DBI Smiley Very Happy

B2:$subsystem", "$username", "$password" );

Akshat Pramod Sharma

jbonifaci

I don't have any experience with DB2 store procedures, but posting the output of the script might be helpful. What is being output? Where is it exiting? Things like that would help troubleshoot.

Migrateduser

I am able to setup a connection to DB2 and in fact if I replace the stored procedure call with a sql statement I get results back also. However, I still need to get a stored procedure to run to atleast see as to what is the output being returned from the stored procedure. Running the exact same statement from DB2 Connect client runs the stored procedure.

I am attaching the log file with this post.

DBI 1.38-nothread dispatch trace level set to 15
Note: perl is running without the recommended perl -w option
-> DBI->connect(DBI Smiley Surprised

DBC

D0G, WECIAINT, ****)
-> DBI->install_driver(ODBC) for MSWin32 perl=5.008002 pid=1372 ruid=0 euid=0
install_driver: DBD: Smiley Surprised

DBC version 1.06 loaded from

\iw-home\iw-perl\vendor\lib/DBD/ODBC.pm
New DBI::dr (for DBD: Smiley Surprised

DBC::dr, parent=, id=)
dbih_setup_handle(DBI::dr=HASH(0x18d4400)=>DBI::dr=HASH(0x1a12908), DBD: Smiley Surprised

DBC::dr, 0, Null!)
dbih_make_com(Null!, 0, DBD: Smiley Surprised

DBC::dr, 92, 0) thr#15d40c8
dbih_setup_attrib(DBI::dr=HASH(0x1a12908), Err, Null!) SCALAR(0x1a01164) (already defined)
dbih_setup_attrib(DBI::dr=HASH(0x1a12908), State, Null!) SCALAR(0x1a011ac) (already defined)
dbih_setup_attrib(DBI::dr=HASH(0x1a12908), Errstr, Null!) SCALAR(0x1a01188) (already defined)
dbih_setup_attrib(DBI::dr=HASH(0x1a12908), TraceLevel, Null!) 0 (already defined)
dbih_setup_attrib(DBI::dr=HASH(0x1a12908), FetchHashKeyName, Null!) 'NAME' (already defined)
<- install_driver= DBI::dr=HASH(0x18d4400)
>> connect DISPATCH (DBI::dr=HASH(0x18d4400) rc2/3 @5 g0 ima1 pid#1372) at

/iw-home/iw-perl/site/lib/DBI.pm line 585
-> connect for DBD: Smiley Surprised

DBC::dr (DBI::dr=HASH(0x18d4400)~0x1a12908 'DD0G' 'WECIAINT' **** HASH(0x1a1a9ac))
New DBI::db (for DBD: Smiley Surprised

DBC::db, parent=DBI::dr=HASH(0x1a12908), id=)
dbih_setup_handle(DBI::db=HASH(0x1a128cc)=>DBI::db=HASH(0x1a1aa00), DBD: Smiley Surprised

DBC::db, 18d1c30, Null!)
dbih_make_com(DBI::dr=HASH(0x1a12908), 19c7bd0, DBD: Smiley Surprised

DBC::db, 220, 0) thr#15d40c8
dbih_setup_attrib(DBI::db=HASH(0x1a1aa00), Err, DBI::dr=HASH(0x1a12908)) SCALAR(0x18d72bc) (already defined)
dbih_setup_attrib(DBI::db=HASH(0x1a1aa00), State, DBI::dr=HASH(0x1a12908)) SCALAR(0x18d731c) (already defined)
dbih_setup_attrib(DBI::db=HASH(0x1a1aa00), Errstr, DBI::dr=HASH(0x1a12908)) SCALAR(0x18d72ec) (already defined)
dbih_setup_attrib(DBI::db=HASH(0x1a1aa00), TraceLevel, DBI::dr=HASH(0x1a12908)) 0 (already defined)
dbih_setup_attrib(DBI::db=HASH(0x1a1aa00), FetchHashKeyName, DBI::dr=HASH(0x1a12908)) 'NAME' (already defined)
dbih_setup_attrib(DBI::db=HASH(0x1a1aa00), HandleError, DBI::dr=HASH(0x1a12908)) undef (not defined)
<- connect= DBI::db=HASH(0x1a128cc) at

/iw-home/iw-perl/site/lib/DBI.pm line 585
>> STORE DISPATCH (DBI::db=HASH(0x1a1aa00) rc2/1 @3 g0 ima418 pid#1372) at

/iw-home/iw-perl/site/lib/DBI.pm line 628
-> STORE for DBD: Smiley Surprised

DBC::db (DBI::db=HASH(0x1a1aa00)~INNER 'PrintError' 1)
STORE DBI::db=HASH(0x1a1aa00) 'PrintError' => 1
<- STORE= 1 at

/iw-home/iw-perl/site/lib/DBI.pm line 628
>> STORE DISPATCH (DBI::db=HASH(0x1a1aa00) rc2/1 @3 g0 ima418 pid#1372) at

/iw-home/iw-perl/site/lib/DBI.pm line 628
-> STORE for DBD: Smiley Surprised

DBC::db (DBI::db=HASH(0x1a1aa00)~INNER 'AutoCommit' 1)
<- STORE= 1 at

/iw-home/iw-perl/site/lib/DBI.pm line 628
>> STORE DISPATCH (DBI::db=HASH(0x1a1aa00) rc2/1 @3 g0 ima418 pid#1372) at

/iw-home/iw-perl/site/lib/DBI.pm line 631
-> STORE for DBD: Smiley Surprised

DBC::db (DBI::db=HASH(0x1a1aa00)~INNER 'Username' 'WECIAINT')
STORE DBI::db=HASH(0x1a1aa00) 'Username' => 'WECIAINT'
<- STORE= 1 at

/iw-home/iw-perl/site/lib/DBI.pm line 631
<- connect= DBI::db=HASH(0x1a128cc)
>> STORE DISPATCH (DBI::db=HASH(0x1a1aa00) rc2/1 @3 g0 ima418 pid#1372) at

/iw-home/iw-perl/site/lib/DBI.pm line 645 via

\iw-home\httpd\iw-bin\mail2_form.ipl line 35
-> STORE for DBD: Smiley Surprised

DBC::db (DBI::db=HASH(0x1a1aa00)~INNER 'dbi_connect_closure' CODE(0x1a18674))
STORE DBI::db=HASH(0x1a1aa00) 'dbi_connect_closure' => CODE(0x1a18674)
<- STORE= 1 at

/iw-home/iw-perl/site/lib/DBI.pm line 645 via

\iw-home\httpd\iw-bin\mail2_form.ipl line 35
>> prepare DISPATCH (DBI::db=HASH(0x1a128cc) rc1/1 @2 g0 ima201 pid#1372) at

\iw-home\httpd\iw-bin\mail2_form.ipl line 48
-> prepare for DBD: Smiley Surprised

DBC::db (DBI::db=HASH(0x1a128cc)~0x1a1aa00 ' CALL DD0GWIN1.EPOSP529('AVGI04','CIA','WECIAINT','CREWNET','2005-03-17-12.26.41.469562','N',0,0,0,' ',' ','Y',' ','SAMARTH') ')
New DBI::st (for DBD: Smiley Surprised

DBC::st, parent=DBI::db=HASH(0x1a1aa00), id=)
dbih_setup_handle(DBI::st=HASH(0x1a1aa84)=>DBI::st=HASH(0x1a1a9a0), DBD: Smiley Surprised

DBC::st, 1a1aa90, Null!)
dbih_make_com(DBI::db=HASH(0x1a1aa00), 1a1b6c8, DBD: Smiley Surprised

DBC::st, 208, 0) thr#15d40c8
dbih_setup_attrib(DBI::st=HASH(0x1a1a9a0), Err, DBI::db=HASH(0x1a1aa00)) SCALAR(0x18d72bc) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x1a1a9a0), State, DBI::db=HASH(0x1a1aa00)) SCALAR(0x18d731c) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x1a1a9a0), Errstr, DBI::db=HASH(0x1a1aa00)) SCALAR(0x18d72ec) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x1a1a9a0), TraceLevel, DBI::db=HASH(0x1a1aa00)) 0 (already defined)
dbih_setup_attrib(DBI::st=HASH(0x1a1a9a0), FetchHashKeyName, DBI::db=HASH(0x1a1aa00)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x1a1a9a0), HandleError, DBI::db=HASH(0x1a1aa00)) undef (not defined)
<- prepare= DBI::st=HASH(0x1a1aa84) at

\iw-home\httpd\iw-bin\mail2_form.ipl line 48
>> execute DISPATCH (DBI::st=HASH(0x1a1aa84) rc1/1 @1 g0 ima41 pid#1372) at

\iw-home\httpd\iw-bin\mail2_form.ipl line 71
-> execute for DBD: Smiley Surprised

DBC::st (DBI::st=HASH(0x1a1aa84)~0x1a1a9a0)
!! ERROR: -1 '[IBM][CLI Driver][DB2] SQL0440N No authorized routine named "EPOSP529" of type "" having compatible arguments was found. SQLSTATE=42884

(SQL-42884)(DBD: st_execute/SQLExecute err=-1)'
<- execute= undef at

\iw-home\httpd\iw-bin\mail2_form.ipl line 71
>> fetchrow_array DISPATCH (DBI::st=HASH(0x1a1aa84) rc1/1 @1 g1 ima0 pid#1372) at

\iw-home\httpd\iw-bin\mail2_form.ipl line 86
-> fetchrow_array for DBD: Smiley Surprised

DBC::st (DBI::st=HASH(0x1a1aa84)~0x1a1a9a0)
!! ERROR: -1 '(DBD: no select statement currently executing err=-1)'
<- fetchrow_array= ( ) [0 items] row-1 at

\iw-home\httpd\iw-bin\mail2_form.ipl line 86
>> FETCH DISPATCH (DBI::st=HASH(0x1a1a9a0) rc2/1 @2 g0 ima404 pid#1372) at

\iw-home\httpd\iw-bin\mail2_form.ipl line 84
-> FETCH for DBD: Smiley Surprised

DBC::st (DBI::st=HASH(0x1a1a9a0)~INNER 'db2_more_results')
.. FETCH DBI::st=HASH(0x1a1a9a0) 'db2_more_results' = undef
error: -1 '(DBD: no select statement currently executing err=-1)'
<- FETCH= undef at

\iw-home\httpd\iw-bin\mail2_form.ipl line 84
>> commit DISPATCH (DBI::db=HASH(0x1a128cc) rc1/1 @1 g0 imac81 pid#1372) at

\iw-home\httpd\iw-bin\mail2_form.ipl line 119
-> commit for DBD: Smiley Surprised

DBC::db (DBI::db=HASH(0x1a128cc)~0x1a1aa00)
<- commit= 1 at

\iw-home\httpd\iw-bin\mail2_form.ipl line 119
>> finish DISPATCH (DBI::st=HASH(0x1a1aa84) rc1/1 @1 g0 ima1 pid#1372) at

\iw-home\httpd\iw-bin\mail2_form.ipl line 120
-> finish for DBD: Smiley Surprised

DBC::st (DBI::st=HASH(0x1a1aa84)~0x1a1a9a0)
<- finish= 1 at

\iw-home\httpd\iw-bin\mail2_form.ipl line 120
>> disconnect DISPATCH (DBI::db=HASH(0x1a128cc) rc1/1 @1 g0 imac01 pid#1372) at

\iw-home\httpd\iw-bin\mail2_form.ipl line 121
-> disconnect for DBD: Smiley Surprised

DBC::db (DBI::db=HASH(0x1a128cc)~0x1a1aa00)
<- disconnect= 1 at

\iw-home\httpd\iw-bin\mail2_form.ipl line 121
>> DESTROY DISPATCH (DBI::st=HASH(0x1a1aa84) rc1/1 @1 g0 ima4 pid#1372)
<> DESTROY ignored for outer handle DBI::st=HASH(0x1a1aa84) (inner DBI::st=HASH(0x1a1a9a0))
>> DESTROY DISPATCH (DBI::st=HASH(0x1a1a9a0) rc1/1 @1 g0 ima4 pid#1372)
-> DESTROY for DBD: Smiley Surprised

DBC::st (DBI::st=HASH(0x1a1a9a0)~INNER)
<- DESTROY= undef
DESTROY (dbih_clearcom) (sth 0x1a1aa84 0x0, com 0x1a1ce40, imp DBD: Smiley Surprised

DBC::st):
FLAGS 0x111: COMSET Warn PrintError
PARENT DBI::db=HASH(0x1a1aa00)
KIDS 0 (0 Active)
IMP_DATA undef
NUM_OF_FIELDS 0
NUM_OF_PARAMS 0
dbih_clearcom 0x1a1aa84 (com 0x1a1ce40, type 3) done.

>> DESTROY DISPATCH (DBI::db=HASH(0x1a128cc) rc1/1 @1 g0 ima4 pid#1372)
<> DESTROY ignored for outer handle DBI::db=HASH(0x1a128cc) (inner DBI::db=HASH(0x1a1aa00))
>> DESTROY DISPATCH (DBI::db=HASH(0x1a1aa00) rc1/1 @1 g0 ima4 pid#1372)
-> DESTROY for DBD: Smiley Surprised

DBC::db (DBI::db=HASH(0x1a1aa00)~INNER)
<- DESTROY= undef
DESTROY (dbih_clearcom) (dbh 0x1a128cc 0x0, com 0x1a1b6c8, imp DBD: Smiley Surprised

DBC::db):
FLAGS 0x311: COMSET Warn PrintError AutoCommit
PARENT DBI::dr=HASH(0x1a12908)
KIDS 0 (0 Active)
IMP_DATA undef
dbih_clearcom 0x1a128cc (com 0x1a1b6c8, type 2) done.

-- DBI::END
>> disconnect_all DISPATCH (DBI::dr=HASH(0x18d4400) rc1/3 @1 g0 ima801 pid#1372) at

/iw-home/iw-perl/site/lib/DBI.pm line 653 via

\iw-home\httpd\iw-bin\mail2_form.ipl line 0
-> disconnect_all for DBD: Smiley Surprised

DBC::dr (DBI::dr=HASH(0x18d4400)~0x1a12908)
<- disconnect_all= '' at

/iw-home/iw-perl/site/lib/DBI.pm line 653 via

\iw-home\httpd\iw-bin\mail2_form.ipl line 0
! >> DESTROY DISPATCH (DBI::dr=HASH(0x1a12908) rc1/1 @1 g0 ima4 pid#1372) during global destruction
! -> DESTROY for DBD: Smiley Surprised

DBC::dr (DBI::dr=HASH(0x1a12908)~INNER)
! <- DESTROY= (not implemented) during global destruction
DESTROY (dbih_clearcom) (drh 0x18d4400 0x0, com 0x19c7bd0, imp DBD: Smiley Surprised

DBC::dr):
FLAGS 0x215: COMSET Active Warn AutoCommit
PARENT undef
KIDS 0 (0 Active)
IMP_DATA undef
dbih_clearcom 0x18d4400 (com 0x19c7bd0, type 1) done.

! >> DESTROY DISPATCH (DBI::dr=HASH(0x18d4400) rc1/1 @1 g0 ima4 pid#1372) during global destruction
! <> DESTROY for DBI::dr=HASH(0x18d4400) ignored (inner handle gone)

akshathp

1. Have you verified if number of parameters you are including in the call are same as you have in SP? (post the SP too in this thread)
2. Have you verified if any other SP call works for you other then this which is giving errors?
If none of the stored procedures are working in this way, then it could be related to DB2 bind for database and user.
Also, check into this error that you are getting in the logs.

No authorized routine named "EPOSP529" of type "" having compatible arguments was found.

Why type is "" (blank)?

Akshat Pramod Sharma

Migrateduser

What I am not able to understand as to why is this error occurring especially when running the exact CALL statement through the DB2 Connect client returns the results from the stored procedure.

akshathp

DB2 CLI auto binds the db info. Hence that would always work if connection is not the issue.
Maybe it is not able to get it from the page you calling from as webapp. Did you confirm if any other stored procedure call works?

Akshat Pramod Sharma