DBD:B2::db prepare failed: [IBM][CLI Driver] CLI0129E No more handles. SQLSTATE=HY014 at ./comm_mim.pl line 35, <FD> line 5807.
Can't prepare statement: [IBM][CLI Driver] CLI0129E No more handles. SQLSTATE=HY014 at ./comm_mim.pl line 35, <FD> line 5807.

Thanks.
-Jianrong

My program is:

#!/usr/bin/perl -w
use DBI;
use DBD:B2::Constants;
use DBD:B2;
my $database='dbiB2:gene';

my $dbh = DBI->connect($database)
or die "Can't connect to $database: $DBI::errstr";

$first = shift(@ARGV) ;
$second= shift(@ARGV) ;
$column= shift(@ARGV) ;

open(FD,"<$first") || die "Couldn't open sourcefile\n";
open(FD1,">$second") || die "Couldn't open output file\n";
while($line= <FD>)
{
chop($line);
@a = split(/\|/,$line);
my $st = "SELECT NAME FROM U.SCT_CON_072005 WHERE SCTID=$a[$column] WITH UR";
my $sth = $dbh->prepare($st) or die "Can't prepare statement: $DBI::errstr";
my $rc = $sth->execute() or die "Can't execute statement: $DBI::errstr";
if (($name) = $sth->fetchrow())
{
print FD1 "$line|$name\n";
} else
{
print "ERROR SCT ID=$a[$column]\n"
}

$rc = $sth->finish;
$st ="";


}
$dbh->disconnect();
close(FD);
close(FD1);

May be you should prepare the statement outside the loop, instead of creating 5000+ statement handles.

I can't prepare the statement outside the loop, because it was changed within loop.

Thanks.

May be you should read DBI documentation then...

Thank you n_i, it works great.
BTW, if the WHERE condition is variable, what can I do.
Anyway, thanks you so much.

-Jianrong

Well, if the query (its WHERE clause) changes you can't use this approach, obviously. If the number of query variants is small you can still prepare all of them ahead of time and execute the one you need in the loop, depending on some condition.

Otherwise you will need to make sure that the statement handle is destroyed at the end of each cycle.