IDS Forum

INFORMIX DBD/DBI - hangs at the $dbh->disconnect s

Posted By: Rajiv Jhaveri
Date: Friday, 12 January 2007, at 1:13 p.m.

Folks,

We seem to be having issues with PERL INFORMIX DBD/DBI (atleast, that's what the developement team's thinking is), where the perl process seems to hang at the $dbh->disconnect stmt. I am not sure whether it is a DBD/DBI issue or some other issue.

Overview:

We are trying to simulate a load for our testing purposes via a shell script using a while loop. In the while loop the shell script executes the same perl script in the background. 20 background processes are spawned.
In order to reproduce the problem, I have simplified the perl script to use the stores db. The PERL script does the fol:

1. make a connection to the Informix stores DB (it is a tlitcp connection)

2. prepare and execute a simple SQL stmt (select first 100 order_num,order_date,customer_num from orders)

3. Fetch each row from the table and write the output to a log file

4. finish the stmt, and disconnect from the DB.

Problem:

Of the 20 processes, 17 or 18 processes complete immediately. The remaining 2 or 3 of perl processes wait for more than 2 min. before it prints out the stmt. right after the $dbh->disconnect and only then does the UNIX perl process ends.

The same problem was seen with the latest PERL/DBD/DBI as well as with earlier versions of DBD/DBI.

I have included the environment details, truss output, shell and perl scripts for your reference. Let me know if any additional info. is required. All help appreciated.

==================ENVIRONMENT==============================

Environment:

Sun Solaris 10

IDS : 7.31.UD9

ESQL and CDSK: IBM Informix CSDK Version 2.90, IBM Informix-ESQL Version 2.90.UC4

PERL 5.8.5

INFORMIX DBD

"installed into: /PERL/perl5.8.5/lib/site_perl/5.8.5"

"LINKTYPE: dynamic"

"VERSION: 2003.03.0401"

INFORMIX DBI

"installed into: /PERL/perl5.8.5/lib/site_perl/5.8.5"

"LINKTYPE: dynamic"

"VERSION: 1.43"

"EXE_FILES: dbiproxy dbiprof"

============TRUSS OUTPUT==========================
Truss output (taken when the perl process was waiting [for >2 min] instead of ending immediately)>>truss -p 27527pollsys(0xFFBFBC08, 1, 0xFFBFBB38, 0x00000000) (sleeping...)pollsys(0xFFBFBC08, 1, 0xFFBFBB38, 0x00000000) = 0fstat(3, 0xFFBFBA98) = 0lwp_sigmask(SIG_SETMASK, 0xFFBFFEFF, 0x0000FFF7) = 0xFFBFFEFF [0x0000FFFF]ioctl(3, I_NREAD, 0xFFBFBB24) = 0lwp_sigmask(SIG_SETMASK, 0x00000000, 0x00000000) = 0xFFBFFEFF [0x0000FFFF]fstat(3, 0xFFBFBAA8) = 0lwp_sigmask(SIG_SETMASK, 0xFFBFFEFF, 0x0000FFF7) = 0xFFBFFEFF [0x0000FFFF]close(3) = 0lwp_sigmask(SIG_SETMASK, 0x00000000, 0x00000000) = 0xFFBFFEFF [0x0000FFFF]pipe() = 3 [5]pipe() = 6 [7]fork1() = 27741lwp_sigmask(SIG_SETMASK, 0x00000000, 0x00000000) = 0xFFBFFEFF [0x0000FFFF]close(7) = 0close(5) = 0read(6, 0xFFBFC884, 4) = 0close(6) = 0fcntl(3, F_GETFD, 0x00000000) = 0fstat64(3, 0xFFBFC800) = 0fstat64(3, 0xFFBFA4E8) = 0fstat64(3, 0xFFBFA390) = 0
ioctl(3, TCGETA, 0xFFBFA474) Err#22 EINVALread(3, " 0 1 / 1 2 / 0 7 - 1 2 :".., 5120) = 18read(3, 0x0028A38C, 5120) = 0fstat64(3, 0xFFBFC800) = 0read(3, 0x0028A38C, 5120) = 0llseek(3, 0, SEEK_CUR) Err#29 ESPIPEclose(3) = 0sigaction(SIGHUP, 0xFFBFC700, 0xFFBFC878) = 0sigaction(SIGINT, 0xFFBFC700, 0xFFBFC858) = 0sigaction(SIGQUIT, 0xFFBFC700, 0xFFBFC838) = 0waitid(P_PID, 27741, 0xFFBFC680, WEXITED|WTRAPPED) = 0sigaction(SIGHUP, 0xFFBFC720, 0x00000000) = 0sigaction(SIGINT, 0xFFBFC720, 0x00000000) = 0sigaction(SIGQUIT, 0xFFBFC720, 0x00000000) = 0ioctl(1, TCGETA, 0xFFBFC62C) = 0fstat64(1, 0xFFBFC548) = 0write(1, " 5 : H e l l o W o r l".., 39) = 39write(1, " - 1\n", 5) = 5setcontext(0xFFBFC5D0)stat64("/PERL/perl5.8.5/lib/site_perl/5.8.5/sun4-solaris/auto/DBI/DESTROY.al", 0x00117C00) Err#2 ENOENTopen64("/PERL/perl5.8.5/lib/5.8.5/sun4-solaris/auto/DBI/DESTROY.al", O_RDONLY) Err#2 ENOENTopen64("/PERL/perl5.8.5/lib/5.8.5/auto/DBI/DESTROY.al", O_RDONLY) Err#2 ENOENTopen64("/PERL/perl5.8.5/lib/site_perl/5.8.5/sun4-solaris/auto/DBI/DESTROY.al", O_RDONLY) Err#2 ENOENTopen64("/PERL/perl5.8.5/lib/site_perl/5.8.5/auto/DBI/DESTROY.al", O_RDONLY) Err#2 ENOENTopen64("/PERL/perl5.8.5/lib/site_perl/auto/DBI/DESTROY.al", O_RDONLY) Err#2 ENOENTopen64("./auto/DBI/DESTROY.al", O_RDONLY) Err#2 ENOENTsetcontext(0xFFBFC158)_exit(0)

===================PERL SCRIPT (x.pl)==========================
#!/PERL/bin/perl
use DBI;
my($fileName);
my($cmdLineArg)=$ARGV[0];
$fileName="log.out." . $$;
## print "1:Hello World\n";
$string1_connect="dbi:Informix:"."stores"."\@"."icdev_2007_1";
$dbh = DBI->connect($string1_connect,'','',{});
if ( ! defined($dbh)) {
$dt = `date +%m/%d/%y-%H:%M:%S` ;
chomp($dt);
$err_msg="ERROR: " . $dt . " :x.pl:Cannot connect to the database";
print $err_msg . "\n";
exit 1;
}
## print "2:Hello World\n";
$sql_stmt= "select first 100 order_num,order_date,customer_num from orders";
$sth= $dbh->prepare( $sql_stmt);
if (! defined($sth)) {
$dt = `date +%m/%d/%y-%H:%M:%S` ;
chomp($dt);
$err_msg="ERROR: " . $dt . " :x.pl:Cannot prepare statement";
print $err_msg . "\n";
exit 1;
}
## print "3:Hello World\n";
## $dbh->do("set isolation to dirty read");
$dbh->do("set lock mode to wait 30");
##############################
# Execute the statement at the database level
##############################
$sth->execute ;
if ( ! (open( logFH, ">$fileName" ) ) ) {
print "Couldn't open $logfile file\n";
exit (1);
}
while (($a,$b,$c) = $sth->fetchrow()) {
$a =~ s/\s+$//g;
$b =~ s/\s+$//g;
$c =~ s/\s+$//g;
print logFH "$a:$b:$c\n";
}
##############################
# Release the statement handle resources
##############################
$sth->finish ;
close (logFH );
##############################
# Disconnect from the database
##############################
$dt0 = `date +%m/%d/%y-%H:%M:%S` ;
## print "4:Hello World - $cmdLineArg - $dt0\n";
$rc = $dbh->disconnect or warn $dbh->errstr;
$dt1 = `date +%m/%d/%y-%H:%M:%S` ;
print "5:Hello World - $cmdLineArg - $dt1 - $rc\n";

========================SHELL DRIVER SCRIPT========================

#!/bin/ksh

for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
do
./x.pl $i &
done

=======================================================================
--
Rajiv Jhaveri
rajiv_jhaveri_06@hotmail.com
_________________________________________________________________
Try amazing new 3D maps
http://maps.live.com/?wip=51

IDS Forum is maintained by Administrator with WebBBS 5.12.