DB2 LUW 9.1: How to monitor failed SQL statement text? -> Resolution

Peter Suhner

DB2 LUW 9.1: How to monitor failed SQL statement text? -> Resolution
Thanks to everybody for their replies!
I was actually able to resolve the problem without touching the application
itself.

Using the tracing possibilities of the JDBC Universal Driver I simply added
the respective commands to the connect string and got all relevant
information written to a file. Connect string definition is outside the
application (properties files, JNDI, etc.).

Great feature! Just to let you know the details I have figured out, that's
what the connect string looks like for such specific issues:
"jdbc:db2://<dns-name>:<port>/<db-name>:traceLevel=899;traceFile=<target-file-name>"

Trace level 899 results in tracing the following information:
STATEMENT_CALLS
RESULT_SET_CALLS
RESULT_SET_META_DATA
PARAMETER_META_DATA
DIAGNOSTICS

I can see the SQLCODE, the statements and all meta data. Successful
statements are logged as well, so it's quite some amount of data, but fairly
easy to plough through as you do have the SQLCODEs.

Parameter meta data is particularly interesting for this case: It was nice
to see that with an "equal" predicate on a character column, the parameter
has the same format as the target table column (e.g. CHAR(25)), whereas with
a "like" predicate, the parameter will be a 32kB VARCHAR field (implicit
casting).

As a result, the statements in question worked with a "like" predicate while
similar statements using an "equal" predicate failed with SQLCODE -302, when
the parameter was longer than the target column definition.

Room for improvement for our developers detected ;-))

See
"http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.java.doc/doc/rjvdsprp.htm",
for details on this trace feature, including details on existing trace levels.

Regards,
Peter

On Tue, 25 Nov 2008 19:18:05 -0600, Jeffrey Benner, DB2 DBA
<[login to unmask email]> wrote:

>I have encountered exactly the same problem and I believe the event monitor
>will not pick up a SQL statement that fails due to a -302. The event
>monitor, I believe, will only encounter queries that make it to the server
>engine. If the SQL fails to make it past the interpreter it's not going to
>show on the server side at all in System Monitor resources. You have to have
>the developers put intercepts in application code that dumps the attempted
>SQL and the contents of host variables in order to track down your problem.
>Sorry for the bad news.
>
>Jeff Benner
>www.ebenner.com
>
>On Tue, Nov 25, 2008 at 16:24, Peter Suhner <[login to unmask email]>wrote:
>
>> Dear Listers,
>>
>> we do have a Java application accessing DB2 9.1 on Solaris. The application
>> occasionally comes up with SQL statements failing due to SQLCODE -302 (Host
>> variable too large). The application only reports the SQL error, but not
>> the
>> statement. To derive which part of the application generates these
>> unsuitable host variables, we would like to figure out the SQL statements
>> and the host variable values.
>>
>> My firsth thought was: "Easy, we do have event monitors." And my quickly
>> defined table based statement event monitor has recorded lots of
>> information. This includes some SQL statements in the STMT_TEXT column, but
>> by far not all of them. For many statements, I get detailed information,
>> but
>> the STMT_TEXT column is just empty.
>>
>> I'd appreciate any ideas and/or better approaches towards getting the
>> required information out of DB2.
>>
>> Thank you in advance,
>> Peter
>>
>> ______________________________________________________________________
>>
>> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
>> ______________________________________________________________________
>>
>>
>>
>> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
>> list archives, FAQ, and delivery preferences are at
>> http://www.idug.org/lsidug under the Listserv tab. While at the site, you
>> can also access the IDUG Online Learning Center, Tech Library and Code
>> Place, see the latest IDUG conference information and much more. If you
>> have not yet signed up for Basic Membership in IDUG, available at no cost,
>> click on Member Services at http://www.idug.org/lsms
>>
>>
>
>______________________________________________________________________
>
>* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
>______________________________________________________________________
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms
>

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Peter Suhner

Re: DB2 LUW 9.1: How to monitor failed SQL statement text? -> Resolution
(in response to Peter Suhner)
Oooops, the connect string listed is missing a terminating semicolon - this
would result in a DRDA error during connection - tiny, but important detail!

Correct version is
""jdbc:db2://<dns-name>:<port>/<db-name>:traceLevel=899;traceFile=<target-file-name>;"

That's it,
Peter

On Fri, 28 Nov 2008 13:08:20 +0000, Peter Suhner <[login to unmask email]>
wrote:

>Thanks to everybody for their replies!
>I was actually able to resolve the problem without touching the application
>itself.
>
>Using the tracing possibilities of the JDBC Universal Driver I simply added
>the respective commands to the connect string and got all relevant
>information written to a file. Connect string definition is outside the
>application (properties files, JNDI, etc.).
>
>Great feature! Just to let you know the details I have figured out, that's
>what the connect string looks like for such specific issues:
>"jdbc:db2://<dns-name>:<port>/<db-name>:traceLevel=899;traceFile=<target-file-name>"
>
>Trace level 899 results in tracing the following information:
>STATEMENT_CALLS
>RESULT_SET_CALLS
>RESULT_SET_META_DATA
>PARAMETER_META_DATA
>DIAGNOSTICS
>
>I can see the SQLCODE, the statements and all meta data. Successful
>statements are logged as well, so it's quite some amount of data, but fairly
>easy to plough through as you do have the SQLCODEs.
>
>Parameter meta data is particularly interesting for this case: It was nice
>to see that with an "equal" predicate on a character column, the parameter
>has the same format as the target table column (e.g. CHAR(25)), whereas with
>a "like" predicate, the parameter will be a 32kB VARCHAR field (implicit
>casting).
>
>As a result, the statements in question worked with a "like" predicate while
>similar statements using an "equal" predicate failed with SQLCODE -302, when
>the parameter was longer than the target column definition.
>
>Room for improvement for our developers detected ;-))
>
>See
>"http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.java.doc/doc/rjvdsprp.htm",
>for details on this trace feature, including details on existing trace levels.
>
>Regards,
>Peter
>
>On Tue, 25 Nov 2008 19:18:05 -0600, Jeffrey Benner, DB2 DBA
><[login to unmask email]> wrote:
>
>>I have encountered exactly the same problem and I believe the event monitor
>>will not pick up a SQL statement that fails due to a -302. The event
>>monitor, I believe, will only encounter queries that make it to the server
>>engine. If the SQL fails to make it past the interpreter it's not going to
>>show on the server side at all in System Monitor resources. You have to have
>>the developers put intercepts in application code that dumps the attempted
>>SQL and the contents of host variables in order to track down your problem.
>>Sorry for the bad news.
>>
>>Jeff Benner
>>www.ebenner.com
>>
>>On Tue, Nov 25, 2008 at 16:24, Peter Suhner <[login to unmask email]>wrote:
>>
>>> Dear Listers,
>>>
>>> we do have a Java application accessing DB2 9.1 on Solaris. The application
>>> occasionally comes up with SQL statements failing due to SQLCODE -302 (Host
>>> variable too large). The application only reports the SQL error, but not
>>> the
>>> statement. To derive which part of the application generates these
>>> unsuitable host variables, we would like to figure out the SQL statements
>>> and the host variable values.
>>>
>>> My firsth thought was: "Easy, we do have event monitors." And my quickly
>>> defined table based statement event monitor has recorded lots of
>>> information. This includes some SQL statements in the STMT_TEXT column, but
>>> by far not all of them. For many statements, I get detailed information,
>>> but
>>> the STMT_TEXT column is just empty.
>>>
>>> I'd appreciate any ideas and/or better approaches towards getting the
>>> required information out of DB2.
>>>
>>> Thank you in advance,
>>> Peter
>>>
>>> ______________________________________________________________________
>>>
>>> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
>>> ______________________________________________________________________
>>>
>>>
>>>
>>> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
>>> list archives, FAQ, and delivery preferences are at
>>> http://www.idug.org/lsidug under the Listserv tab. While at the site, you
>>> can also access the IDUG Online Learning Center, Tech Library and Code
>>> Place, see the latest IDUG conference information and much more. If you
>>> have not yet signed up for Basic Membership in IDUG, available at no cost,
>>> click on Member Services at http://www.idug.org/lsms
>>>
>>>
>>
>>______________________________________________________________________
>>
>>* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
>>______________________________________________________________________
>>
>>
>>
>>The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
>list archives, FAQ, and delivery preferences are at
>http://www.idug.org/lsidug under the Listserv tab. While at the site, you
>can also access the IDUG Online Learning Center, Tech Library and Code
>Place, see the latest IDUG conference information and much more. If you
>have not yet signed up for Basic Membership in IDUG, available at no cost,
>click on Member Services at http://www.idug.org/lsms
>>
>
>______________________________________________________________________
>
>* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
>______________________________________________________________________
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms