StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow can I debug a lack of IO counts printed in Perl DBI's db_error_handler from a Sybase query?
text
Body
copied!<p>I'm having a fairly strange problem.</p> <p>It's a bit of an X-Y problem, so I will first describe "X".</p> <p><strong>Question X: How can I debug DBI internals using Perl?</strong></p> <p>More specifically, the calls to DBI's db_error_handler. Some of the SQL server callback output is printed (Query plans) 100% of the time, but some (Actual IOs) is ONLY printed when the query returns zero rows (with <code>set statistics io on</code>). </p> <hr> <p><strong>Ultimate problem Y I'm trying to address:</strong></p> <p>For some reason, my Perl script prints query plans <strong>but not actual IOs spent</strong> when executing Sybase queries when the query returns a result set; BUT print both query plans AND actual IOs spent when the SAME query returns zero rows.</p> <p>I have a script that, in essence, does this:</p> <pre><code>sub DoSql { # Sql execution wrapper my ($dbh, $sql) = @_; my $sth = $dbh->prepare($sql); $sth->execute(); } # Set up DB connection, with debug printing turned on my $dbh = CreateDBH(); # Uses DBI and creates Sybase database handle DBI object $dbh->DoSql("set statistics io on"); $dbh->DoSql("set showplan on"); # Print output from DBH callback $dbh->{_db_error_handler} = sub { PrettyPrintSybaseDebug($_[6]); } $dbh->DoSql("SELECT * FROM MyTable WHERE ID=100"); # 1 row returned $dbh->DoSql("SELECT * FROM MyTable WHERE ID=987654321"); # zero rows </code></pre> <p>When executing this script, the first query prints the following Sybase output from db_error_handler:</p> <pre><code>QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. ..... Total estimated I/O cost for statement 1 (at line 1): 80. </code></pre> <p><strong>BUT the second query prints the following Sybase output (see the last 3 lines added):</strong></p> <pre><code>QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. ..... Total estimated I/O cost for statement 1 (at line 1): 80. Table: MyTable scan count 1, logical reads: (regular=3 apf=0 total=3), ... Total actual I/O cost for this command: 6. Total writes for this command: 0 </code></pre> <p>Please note that when I execute these queries in raw <code>isql</code>, BOTH of them, as expected, print both the plan AND the scan counts per table and actual IOs. Both queries run fine and produce expected results sets (1 row and 0 rows respectively)</p> <p>What am I doing wrong and how can I debug this? Unfortunately, <code>perl -d</code> doesn't help me, since I can only debug outside <code>$sth->execute();</code> call and inside the callback.</p> <p>If it matters, my environment is:</p> <ul> <li>Perl 5.8.0</li> <li>Solaris 10</li> <li>Sybase ASE 12.5</li> <li>DBI version is 1.37</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload