No more handles error with DBD::DB2 on Linux
Hemant Shah
Folks,
I have DB2 UDB 7.2 EE installed on AIX 5.2 system. I think I am at
latest fixpack, see output of lslpp below. I have also installed DB2
UDB 7.2 EE on RedHat Linux 7.3 I believe that is also at the latest
fix pack, see rpm output below.
The database is on AIX system and on the Linux system it is cataloged
as remote database.
I have perl/TK GUI that uses DBD::DB2 interface to access the database.
If I run the GUI from AIX system (server) I have no problems. If I
run GUI from Linux system (client), then after using it for some time
I get following error:
CLI0129E No more handles.
Last time I got this error I ran following commands on the Server:
db2 list applications
db2 get snapshot for applications on dbname
There was only one connection to the database and that was the GUI,
the application also had only one section.
I have checked my code and I always call finish() method on the
statement handle after I am done. All the access to the database is
done by calling various functions. When the GUI is started it connects
to the database. Each function creates and prepares the statement and then
executes it and then calls finish() before returning.
I am not sure why I am getting this error from the Linix client.
# lslpp -l "db2_07_01*"
Fileset Level State Description
----------------------------------------------------------------------------
Path: /usr/lib/objrepos
db2_07_01.adt.rte 7.1.0.72 COMMITTED Application Development Tools
(ADT)
db2_07_01.adt.samples 7.1.0.72 COMMITTED ADT Sample Programs
db2_07_01.cdb 7.1.0.72 COMMITTED Control Database
db2_07_01.cj 7.1.0.72 COMMITTED Java Common files
db2_07_01.client 7.1.0.72 COMMITTED Client Application Enabler
db2_07_01.cnvucs 7.1.0.72 COMMITTED Code Page Conversion Tables -
Uni Code Support
db2_07_01.conn 7.1.0.72 COMMITTED Connect
db2_07_01.conv.jp 7.1.0.40 COMMITTED Code Page Conversion Tables -
Japanese
db2_07_01.conv.kr 7.1.0.40 COMMITTED Code Page Conversion Tables -
Korean
db2_07_01.conv.sch 7.1.0.40 COMMITTED Code Page Conversion Tables -
Simplified Chinese
db2_07_01.conv.tch 7.1.0.40 COMMITTED Code Page Conversion Tables -
Traditional Chinese
db2_07_01.cs.drda 7.1.0.72 COMMITTED Communication Support - DRDA
Application Server
db2_07_01.cs.ipx 7.1.0.72 COMMITTED Communication Support - IPX
db2_07_01.cs.rte 7.1.0.72 COMMITTED Communication Support - TCP/IP
db2_07_01.cs.sna 7.1.0.72 COMMITTED Communication Support - SNA
db2_07_01.ctsr 7.1.0.72 COMMITTED Control Server
db2_07_01.das 7.1.0.72 COMMITTED Administration Server
db2_07_01.db2.engn 7.1.0.72 COMMITTED Engine
db2_07_01.db2.rte 7.1.0.72 COMMITTED Run-time Environment
db2_07_01.db2.samples 7.1.0.40 COMMITTED Sample Database Source
db2_07_01.db2tie 7.2.0.2 COMMITTED Text Information Extender
db2_07_01.elic 7.1.0.72 COMMITTED Product Signature for UDB
Enterprise Edition
db2_07_01.jdbc 7.1.0.72 COMMITTED Java Support
db2_07_01.ldap 7.1.0.72 COMMITTED DB2 LDAP Support
db2_07_01.spb 7.1.0.72 COMMITTED Stored Procedure Builder
db2_07_01.tspf 7.1.0.72 COMMITTED Transformer Stored Procedure
Files
db2_07_01.wcc 7.1.0.72 COMMITTED Control Center
----------------------------------------------------------------------
# rpm -qa | grep db2
db2-2.4.14-10
db2das71-7.1.0-68
db2cnvc71-7.1.0-68
db2-devel-2.4.14-10
db2jdbc71-7.1.0-68
db2cj71-7.1.0-68
db2adt71-7.1.0-68
db2crte71-7.1.0-68
db2cnvj71-7.1.0-68
db2cliv71-7.1.0-68
db2engn71-7.1.0-68
db2conn71-7.1.0-68
db2smpl71-7.1.0-68
db2cnvt71-7.1.0-68
db2rte71-7.1.0-68
db2wcc71-7.1.0-68
db2adts71-7.1.0-68
db2cdrd71-7.1.0-68
db2cnvk71-7.1.0-68
db2cucs71-7.1.0-68
db2repl71-7.1.0-68
db2elic71-7.1.0-68
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkM...@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Hemant Shah
Folks,
I am not sure if this is a perl of DB2 problem. I had posted this
problem last week but did not get any reply. I am posting it again
with some more info to see it someone can identiry the problem.
I have DB2 UDB 7.2 EE installed on AIX 5.2 system. I think I am at
latest fixpack,
db2_07_01.db2.engn 7.1.0.72 COMMITTED Engine
db2_07_01.db2.rte 7.1.0.72 COMMITTED Run-time Environment
bos.rte 5.2.0.11 COMMITTED Base Operating System Runtime
I am also running perl 5.8.3 on it.
# perl -v
This is perl, v5.8.3 built for aix
I am using DBD::DB2 version 0.76 from DB2.pm and DBI version 1.39 from
DBI.pm.
I have a Perl/TK GUI that users use to access the database, after
using it for some time they get wierd errors.
It displays one of the following errors:
"no more handles error",
"function sequence error", or
"Can't call method" error.
I wrote a test perl program which calls a function to select a row
from a table in a loop. It errors out after looping for 386 iteration.
Here is the fragment of sample program:
---------cut-------------cut-------------cut-------------cut----
sub GetFragmentFromNumber
{
# This function will return a fragment from the fragment table for a given
# fragment number and language.
# Call: $Fragment = GetFragmentFromNumber(1001, 'EN1');
my($FragNum, $Language) = @_;
my($FileName) = "adb.frag.".$FragNum;
my($SqlStmt) = "SELECT SRC_NUM FROM GBLCODE.SOURCENAME
WHERE SRC_NAME = \'$FileName\'
FOR READ ONLY";
eval
{
$SqlStmtHdl = $DbHandle->prepare($SqlStmt);
$SqlStmtHdl->execute();
};
if ($@)
{
print STDERR "GetFragmentFromNumber: $@";
$ErrorCode = 1;
return;
}
$SourceNum = $SqlStmtHdl->fetchrow();
$SqlStmtHdl->finish();
if ($SourceNum)
{
$SqlStmt = "SELECT RTRIM(FRAGMENT) FROM GBLCODE.FRAGMENT
WHERE SRC_NUM = $SourceNum
AND LANGUAGE = \'$Language\'
FOR READ ONLY";
eval
{
$SqlStmtHdl = $DbHandle->prepare($SqlStmt);
$SqlStmtHdl->execute();
};
if ($@)
{
print STDERR "GetFragmentFromNumber: $@";
$ErrorCode = 1;
return;
}
$Fragment = $SqlStmtHdl->fetchrow();
$SqlStmtHdl->finish();
if ($Fragment)
{
return $Fragment;
}
else
{
return;
}
}
else
{
return;
}
}
---------cut-------------cut-------------cut-------------cut----
Here is the loop
---------cut-------------cut-------------cut-------------cut----
SetupDatabaseEnvironment;
for ($i = 0; $i < 1000; $i++)
{
$Fragment = GetFragmentFromNumber(12805,'EN1');
if ($ErrorCode != 0)
{
TerminateDatabaseEnvironment;
exit(1);
}
print "$i: Fragment = $Fragment\n";
}
TerminateDatabaseEnvironment;
exit(0);
---------cut-------------cut-------------cut-------------cut----
Here is the output of one the runs:
384: Fragment = Source
385: Fragment = Source
386: Fragment = Source
DBD::DB2::db prepare failed: [IBM][CLI Driver] CLI0120E Memory allocation failure. SQLSTATE=HY001 at ./tsttie.pl line 174.
GetFragmentFromNumber: Can't call method "execute" on an undefined value at ./tsttie.pl line 175.
Line 175 in the script is the second execute statement where it tries to
get fragment from the table.
I ran following BIND command and re-started the databae, but that did not
help.
db2 "BIND @db2cli.lst BLOCKING ALL CLIPKG 6 grant public"
LINE MESSAGES FOR db2cli.lst
------ --------------------------------------------------------------------
SQL0061W The binder is in progress.
SQL0091N Binding was ended with "0" errors and "0" warnings.
What else do I need to change?
Thanks.
James Willmore
> I am not sure if this is a perl of DB2 problem. I had posted this
> problem last week but did not get any reply. I am posting it again
> with some more info to see it someone can identiry the problem.
[ ... ]
To determine if DBI is at fault or something else is going on, use the
'trace' method of the DBI module. I recommend setting it to 4, since you
may have a suttle bug.
`perldoc DBI` for more info.
HTH
--
Jim
Copyright notice: all code written by the author in this post is
released under the GPL. http://www.gnu.org/licenses/gpl.txt
for more information.
a fortune quote ...
"And what will you do when you grow up to be as big as me?" asked
the father of his little son. "Diet."
Hemant Shah
The problem is in the way DBI, DBD interacts with the database. I added
same select statement into a file 1000 times and ran it as follows:
db2 -stf tstdb2.ddl
I have no problem running it 1000 times. But it still fails after fetching
the data 386 times using DBI interface.
I turned on trace to 9 and here is the tail end of it:
-> prepare for DBD::DB2::db (DBI::db=HASH(0x2032d2e4)~0x2032e26c 'SELECT SRC_NUM FROM GBLCODE.SOURCENAME
WHERE SRC_NAME = 'adb.frag.12805'
FOR READ ONLY')
dbih_setup_handle(DBI::st=HASH(0x27e7fca8)=>DBI::st=HASH(0x27e7fcfc), DBD::DB2::st, 27e7fcb4, Null!)
dbih_make_com(DBI::db=HASH(0x2032e26c), 20051cc8, DBD::DB2::st, 160, 0) thr#0
dbih_setup_attrib(DBI::st=HASH(0x27e7fcfc), Err, DBI::db=HASH(0x2032e26c)) SCALAR(0x2014e0d0) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x27e7fcfc), State, DBI::db=HASH(0x2032e26c)) SCALAR(0x2014e130) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x27e7fcfc), Errstr, DBI::db=HASH(0x2032e26c)) SCALAR(0x2014e100) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x27e7fcfc), TraceLevel, DBI::db=HASH(0x2032e26c)) 9 (already defined)
dbih_setup_attrib(DBI::st=HASH(0x27e7fcfc), FetchHashKeyName, DBI::db=HASH(0x2032e26c)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x27e7fcfc), HandleError, DBI::db=HASH(0x2032e26c)) undef (not defined)
dbd_st_prepare'd sql f66311
SELECT SRC_NUM FROM GBLCODE.SOURCENAME
WHERE SRC_NAME = 'adb.frag.12805'
FOR READ ONLY
STORE DBI::st=HASH(0x27e7fca8) 'NUM_OF_FIELDS' => 1
fbh 0: 'SRC_NUM' , type 4, 11, dsize 10, p0 s537823208
out: ftype 1, indp 0, bufl 12, rlen 12
<- prepare= DBI::st=HASH(0x27e7fca8) at ./tsttie.pl line 121 via ./tsttie.pl line 120
-> execute for DBD::DB2::st (DBI::st=HASH(0x27e7fca8)~0x27e7fcfc)
<- execute= -1 at ./tsttie.pl line 122 via ./tsttie.pl line 120
-> fetchrow for DBD::DB2::st (DBI::st=HASH(0x27e7fca8)~0x27e7fcfc)
dbih_setup_fbav for 1 fields => 0x27e7fce4
dbd_st_fetch 1 fields
0: rc=0 '207483'
<- fetchrow= '207483' row1 at ./tsttie.pl line 132 via ./tsttie.pl line 179
-> finish for DBD::DB2::st (DBI::st=HASH(0x27e7fca8)~0x27e7fcfc)
<- finish= 1 at ./tsttie.pl line 133 via ./tsttie.pl line 179
-> prepare for DBD::DB2::db (DBI::db=HASH(0x2032d2e4)~0x2032e26c 'SELECT RTRIM(FRAGMENT) FROM GBLCODE.FRAGMENT
WHERE SRC_NUM = 207483
AND LANGUAGE = 'EN1'
FOR READ ONLY')
dbih_setup_handle(DBI::st=HASH(0x27e7fd5c)=>DBI::st=HASH(0x27f418ec), DBD::DB2::st, 27e7fd68, Null!)
dbih_make_com(DBI::db=HASH(0x2032e26c), 20051cc8, DBD::DB2::st, 160, 0) thr#0
dbih_setup_attrib(DBI::st=HASH(0x27f418ec), Err, DBI::db=HASH(0x2032e26c)) SCALAR(0x2014e0d0) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x27f418ec), State, DBI::db=HASH(0x2032e26c)) SCALAR(0x2014e130) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x27f418ec), Errstr, DBI::db=HASH(0x2032e26c)) SCALAR(0x2014e100) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x27f418ec), TraceLevel, DBI::db=HASH(0x2032e26c)) 9 (already defined)
dbih_setup_attrib(DBI::st=HASH(0x27f418ec), FetchHashKeyName, DBI::db=HASH(0x2032e26c)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x27f418ec), HandleError, DBI::db=HASH(0x2032e26c)) undef (not defined)
Statement allocation error error -1 recorded: [IBM][CLI Driver] CLI0120E Memory allocation failure. SQLSTATE=HY001
>> DESTROY DISPATCH (DBI::st=HASH(0x27e7fd5c) rc1/1 @1 g0 ima4 pid#96280) at ./tsttie.pl line 144
<> DESTROY ignored for outer handle DBI::st=HASH(0x27e7fd5c) (inner DBI::st=HASH(0x27f418ec))
>> DESTROY DISPATCH (DBI::st=HASH(0x27f418ec) rc1/1 @1 g0 ima4 pid#96280) at ./tsttie.pl line 144
-> DESTROY for DBD::DB2::st (DBI::st=HASH(0x27f418ec)~INNER)
Statement handle DBI::st=HASH(0x27f418ec) DESTROY ignored - never set up
error: -99999 '[IBM][CLI Driver] CLI0120E Memory allocation failure. SQLSTATE=HY001'
<- DESTROY= undef at ./tsttie.pl line 144
DESTROY (dbih_clearcom) (sth 0x27e7fd5c 0x0, com 0x27fc2da8, imp DBD::DB2::st):
FLAGS 0x191: COMSET Warn RaiseError PrintError
PARENT DBI::db=HASH(0x2032e26c)
KIDS 0 (0 Active)
IMP_DATA undef
LongReadLen 32700
NUM_OF_FIELDS 0
NUM_OF_PARAMS 0
dbih_clearcom 0x27e7fd5c (com 0x27fc2da8, type 3) done.
!! ERROR: -99999 '[IBM][CLI Driver] CLI0120E Memory allocation failure. SQLSTATE=HY001'
Matt Emmerton
"Hemant Shah" <sh...@typhoon.xnet.com> wrote in message
news:c2qj04$jod$1...@flood.xnet.com...
In your program, you execute SELECT statements that (potentially) could
return more than one row.
However, you only call fetchrow() once to fetch the first row from the
result set.
You might want to add the "FETCH FIRST 1 ROW ONLY" clause to your SQL as
this will tell DB2 only to retrieve the first row, instead of keeping a
cursor open to handle multiple fetches.
Not only will this be faster, but it might avoid your problem.
--
Matt Emmerton
Hemant Shah
My select will always fetch one row only. The cobination of SRC_NUM and
LANGUAGE is a unique. SRC_NAME is also a unique column.
Actually I was originally using selectrow_array(), but changed it to
prepare->execute->fetch->finish to make sure that I am releasing all the
resources.
> --
> Matt Emmerton
Darin McBride
> While stranded on information super highway James Willmore wrote:
>> On Tue, 09 Mar 2004 17:54:28 +0000, Hemant Shah wrote:
>>
>>> I am not sure if this is a perl of DB2 problem. I had posted this
>>> problem last week but did not get any reply. I am posting it again
>>> with some more info to see it someone can identiry the problem.
>> [ ... ]
>>
>> To determine if DBI is at fault or something else is going on, use the
>> 'trace' method of the DBI module. I recommend setting it to 4, since you
>> may have a suttle bug.
>>
>> `perldoc DBI` for more info.
>>
>> HTH
>>
>> --
>> Jim
>>
>> Copyright notice: all code written by the author in this post is
>> released under the GPL. http://www.gnu.org/licenses/gpl.txt
>> for more information.
>>
>> a fortune quote ...
>> "And what will you do when you grow up to be as big as me?" asked
>> the father of his little son. "Diet."
>>
>
>
> The problem is in the way DBI, DBD interacts with the database. I added
> same select statement into a file 1000 times and ran it as follows:
>
> db2 -stf tstdb2.ddl
Have you tried emailing db2...@ca.ibm.com with your questions?
Hemant Shah
Not to the above e-mail address, but I have sent e-mail to IBM DB2 support
and they asked me to increase few paramaters, but that did not help either.
Hemant Shah
Thanks for the e-mail address. I sent the information to the above e-mail
address and next day Michael sent me newer version of dbdimp.c file. I
re-built the DBD::DB2 module and it fixed the problem.