cmahajan has asked for the wisdom of the Perl Monks concerning the following question:

I am running DBI version 1.616 along with DBD::Sybase version 1.12 to connect to the Sybase IQ 15.4 server through one of my Perl modules. The platform is IBM AIX 5.3.0.0 maintenance level 5300-12 The problem that I am facing is that one of my simple queries randomly hangs for no apparant reason. The query is to select some data from the system view SYS.SYSTABLE. The code runs fine as such but every once in a while the code (execute statement) freezes and appears to be doing noting for a long time and I have to terminate the process forcibly. Below is a fragment of code that I'm running (all variables are properly defined): AutoCommit is set to 1 RaiseError is set to 1 
$dsn = "dbi:Sybase:serverType=IQ:loginTimeout=240:server=$db_server_if
+c";
my $dbh = DBI->connect($dsn,
                         $self->get_db_username(),
                         $self->get_db_password(),
                         \%connect_options );

my $sqlA =<< "END_SQLA";
SELECT table_id
FROM   sys.systable
WHERE table_name like '${uc_table_name}%'
AND length(table_name) = $table_name_len
AND user_name(creator) = '$uc_schema'
END_SQLA

my $sthA = $dbh->prepare($sqlA);
$sthA->execute();

[download]
Can someone help me figure out what might be wrong with the code or if any of the modules versions that I am using are incompatible with Sybase IQ 15.4. Another interesting thing to note is that the same code works fine most of the times, but the problem occurs randomly at random times. I have checked the active connections in my database when the code hangs, there are no deadlocks or blocked processes. I can also see that the connection to DB is being established successfully but I do not see any active executing statements at all for the DB handle.

Replies are listed 'Best First'.
Re: DBD::Sybase query execute hangs randomly for Sybase IQ 15.4
by jfroebe (Parson) on Jun 18, 2013 at 16:37 UTC

    Set the timeout connection option so you aren't waiting forever.

    $dsn = "dbi:Sybase:serverType=IQ:loginTimeout=240:timeout=180:server=$
+db_server_ifc";
my $dbh = DBI->connect($dsn,
                         $self->get_db_username(),
                         $self->get_db_password(),
                         \%connect_options );

my $sqlA =<< "END_SQLA";
SELECT table_id
FROM   sys.systable
WHERE table_name like ?
AND length(table_name) = ?
AND user_name(creator) = ?
END_SQLA

my $sthA = $dbh->prepare($sqlA);
$sthA->execute($uc_table_name . '%', $table_name_len, $uc_schema);

    [download]

    DBD::Sybase works fine with IQ. That being said, connections to IQ work differently than other database systems. If you have 100 allowable connections, you may have 40 connections that can do work at a time. The other 60 will appear to hang from the application perspective. If you're running into this feature, it will also occur using dbisql or isql.

    Jason L. Froebe

    Blog, Tech Blog

[reply]
[d/l]
Re: DBD::Sybase query execute hangs randomly for Sybase IQ 15.4
by mpeppler (Vicar) on Jun 18, 2013 at 14:36 UTC
    I don't think that this would be related to DBD::Sybase directly. Have you tried to reproduce the issue with some other tool (e.g. isql) in a shell script to see if you can narrow down where this happens?

    Michael

    PS - I've not used DBD::Sybase with IQ, so I have no direct experience similar to your setup.

[reply]
Re: DBD::Sybase query execute hangs randomly for Sybase IQ 15.4
by locked_user sundialsvc4 (Abbot) on Jun 18, 2013 at 17:45 UTC

    Ditto.   Since the code “runs fine,” there is really no reason to presume that the root cause of the problem is within this code.   Next time the program freezes, list the active connections and find yours; then, list the units-of-work for that connection.   This, and really only this, will tell you what is actually going on.   It’s going to be resource-contention, or a lock that is bigger than it needs to be, or simply a throttling parameter of some kind, all of which are not “bugs in your code.”

      I've tried running the SQL separately and unfortunately it always runs to completion, giving me the expected result. In my original post I've only posted a small fragment of a much larger code which executes many similar SQL statements within a very short time (like within 1 minute). The code is in fact used in a Datastage parallel job which spawns at least 4 instances of my Perl code in parallel. The code always gets stuck for the same SQL rather than for random (similar) SQLs. I'm also consulting Sybase DBAs to see if there is a limitation on the number of active connections at any time (which may be a setting specific to my database, set by the DBA or may be a Sybase IQ 15.4 default behavior). Since DBD::Sybase works fine with Sybase IQ, I'm inclined to believe that the problem may be related to the database rather than the code itself. Since Sybase 15.4 is a pretty recent iteration of the IQ product, perhaps some default behavior may have changed, I'm really not sure at the moment.
[reply]

        In case your dbas need information on the Config file. In particular, look at:

        1. -iqgovern Number of concurrent queries (default is 2*num_cpu+10)
        2. -gm Number of connections that can be active at one time

        You might be running into blocking locks if the parallel runs of your Perl code are hitting the same pages.

        As far as Perl goes, you may want to enable DBI Tracing to determine if there is something funky going on.

        Jason L. Froebe

        Blog, Tech Blog

[reply]

Back to Seekers of Perl Wisdom