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

Posted in 2007

Rajiv — Fri, 12 Jan 2007 18:13:54 — source: IIUG Forums & Mailing Lists

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\\ ", 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\\ "; $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 . "\\ "; exit 1; } ## print "2:Hello World\\ "; $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 . "\\ "; exit 1; } ## print "3:Hello World\\ "; ## $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\\ "; 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\\ "; } ############################## # 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\\ "; $rc = $dbh->disconnect or warn $dbh->errstr; $dt1 = `date +%m/%d/%y-%H:%M:%S` ; print "5:Hello World - $cmdLineArg - $dt1 - $rc\\ "; ========================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