Re: DBD::DB2 and db2_query_timeout

[Henri Asseily]

Try using DBIx::HA.
It takes care of the timeout mechanics for you.

---
Henri Asseily
henri.tel

On Aug 12, 2009, at 4:11 AM, 田口 浩 wrote:

Hello,
I found the DB2 way is;

 $sth->{db2_query_timeout} = $timeout;

"%attr" in prepare($stmt, \%attr) seems to be
discarded in "dbdimp.c" in DBD-DB2 package.
But I cann't get the error string or message by this.
$DBI::errstr is null.

I tried the way "local $SIG{'ALRM'} = sub {" .
I set 3 seconds but execute() takes more than 60 seconds,
though I'm noticed the timeout is occurred.

connect ok.
Wed Aug 12 10:00:04 2009 start prepare() ...
Wed Aug 12 10:00:04 2009 end prepare() ...
Wed Aug 12 10:00:04 2009 start execute() ...
Wed Aug 12 10:01:10 2009 db_search error: DB search timed out after 3.

Regards,
HT

-----Original Message-----
From: Ken Youens-Clark [mailto:kycl...@gmail.com]
Sent: Tuesday, August 11, 2009 8:58 PM
To: 田口 浩
Cc: dbi-users@perl.org
Subject: Re: DBD::DB2 and db2_query_timeout

On Aug 10, 2009, at 9:21 PM, 田口 浩 wrote:

Hello,
I like to stop a long running SQL by the code:

my $sth = $dbh->prepare($stmt, { db2_query_timeout => 6 });
my $to = $sth->{db2_query_timeout};
print "end prepare, to=$to.\n";

But my script doesn't stop, and $to is 0.
My code is wrong?

DBI = 1.53 DBD::DB2 = 1.71, DB2 = v8.1

Regards,
Hirosi Taguti

Hirosi,

Here's how I effect a timeout on potentially long-running queries:

        eval {
            local $SIG{'ALRM'} = sub {
                die "DB search timed out after $timeout seconds\n"
            };
            alarm $timeout if $timeout && $^O !~ /Win32/i;
            $markers = $db->selectall_arrayref($select_sql,
{ Columns
=> {} });
            alarm 0 unless $^O =~ /Win32/i;
        };

        if ( my $err = $@ ) {
            print STDERR __PACKAGE__, "::db_search error: ",
                "$err\nSQL=\n$select_sql";
            croak( $err );
            return wantarray ? () : undef;
        }

HTH,

ky