DBD::DB2 $sh->prepare () after FixPack 7 upgrade

[bastb]

Hi,

We've been using the perl DBD::DB2 driver on Perl version 5.8.0
(aix_thread_multi) for a project we're working on without any problems
until now.

We've been using DBD::DB2 version 0.78 on DB2 Version 8.1 (the db2 cli
reports 8.1.0) and we've recently updated DB2 using FixPack 7. The
project we're working on uses the DB2 driver for some webpages and a
couple of daemon scripts. On the DB2 version without the FixPack the
pages were loading fast and after the upgrade, it takes a lot longer
for the pages to appear on the screen.

We've put some logging around the calls to see how much time it takes,
and we noticed the following:

$DCONN is the result of a $DBI->connect_cached () to a DB2 instance
that is running on the local system. The handle was opened using the
options AutoCommit => 1, PrintError => 1, RaiseError => 1. It takes 10
seconds for $DCONN->prepare_cached ("SELECT ...") to return. Using
$DCONN->prepare or $DCONN->prepare_cached makes no difference in time.
The prepare () on DB2 version 8.1 returned immediately, so I didn't use
prepare_cached. If I execute the same statement in the DB2 CLI it takes
less then a second for the SQL statement so I assume it has nothing to
do with the contents of the database.

Our setup is:
- DB2 version 8.1 with FixPack 7 (CLI reports 8.2.0);
- AIX version 5.2 ML 5, 32 bits.
- Perl version 5.8.0 for aix_thread_multi;
- DBI version 1.48.

Thanks for your trouble,

Bas.

[Philip Nelson]

Bas,

Have you activated the database you are connecting to ?

We found that this is virtually essential for web type systems where you
have a small number of intermittent short connections.

Phil

bastb wrote:

Hi,

We've been using the perl DBD::DB2 driver on Perl version 5.8.0
(aix_thread_multi) for a project we're working on without any problems
until now.

We've been using DBD::DB2 version 0.78 on DB2 Version 8.1 (the db2 cli
reports 8.1.0) and we've recently updated DB2 using FixPack 7. The
project we're working on uses the DB2 driver for some webpages and a
couple of daemon scripts. On the DB2 version without the FixPack the
pages were loading fast and after the upgrade, it takes a lot longer
for the pages to appear on the screen.

We've put some logging around the calls to see how much time it takes,
and we noticed the following:

$DCONN is the result of a $DBI->connect_cached () to a DB2 instance
that is running on the local system. The handle was opened using the
options AutoCommit => 1, PrintError => 1, RaiseError => 1. It takes 10
seconds for $DCONN->prepare_cached ("SELECT ...") to return. Using
$DCONN->prepare or $DCONN->prepare_cached makes no difference in time.
The prepare () on DB2 version 8.1 returned immediately, so I didn't use
prepare_cached. If I execute the same statement in the DB2 CLI it takes
less then a second for the SQL statement so I assume it has nothing to
do with the contents of the database.

Our setup is:
- DB2 version 8.1 with FixPack 7 (CLI reports 8.2.0);
- AIX version 5.2 ML 5, 32 bits.
- Perl version 5.8.0 for aix_thread_multi;
- DBI version 1.48.

Thanks for your trouble,

Bas.

[bastb]

Phil,

Thanks for your answer. The database was activated because when the
timeout doesn't occur when I connect using the DB2 CLI.

A collegue used truss to trace the system calls the process produces
and was able to remove the 10 secs. timeout. It seems that after the
upgrade, a db2disp process was activated. This is according to the
documentation. The process sends a kill SIGUSR2 to the db2disp process.
We assume this is done to get a db2 connection.

Our process is not running as the instance user, and is not permitted
to send the SIGUSR2 signal. We reconfigured the database instance to
not start the db2disp process.

I wonder if anyone else had to deal with this issue and if this was
resolved in a later DB2 version.

Thanks,
Bas.