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

Peter Suhner

DB2 LUW 9.1: How to monitor failed SQL statement text?
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

DB2 DBA Jeffrey Benner

Re: DB2 LUW 9.1: How to monitor failed SQL statement text?
(in response to Peter Suhner)
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

harby ariza

Re: DB2 LUW 9.1: How to monitor failed SQL statement text?
(in response to DB2 DBA Jeffrey Benner)

_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________




You can also speak with the webpshere administrators and them can setup traces at the webpshere level to capture the SQL causing you grief. Actually you can try setting the db2 diaglevel up to 4 for the db2 instance and then see whether it capture the query. I believe in v9 it does and is very handy. Try and let us know you go with it. I hope it helps.



________________________________
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Jeffrey Benner, DB2 DBA
Sent: Wednesday, 26 November 2008 12:18 PM
To: [login to unmask email]
Subject: Re: DB2 LUW 9.1: How to monitor failed SQL statement text?

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 < http://www.ebenner.com >

On Tue, Nov 25, 2008 at 16:24, Peter Suhner <[login to unmask email]<mailto:[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 - North America * May 11-15, 2009 * Denver, CO, USA < 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 IDUG.ORG <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<http://www.idug.org/lsconf>, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services< http://www.idug.org/lsms >


_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review,
retransmission, dissemination or other use of, or taking of any action in reliance
upon this information, by persons or entities other than the intended recipient is
prohibited.

If you have received this in error, please contact the sender and delete this e-mail
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute
the information contained in this e-mail and any attached files, with the permission
of the sender.

This message has been scanned for viruses with Symantec Scan Engine and cleared by
MailMarshal.
_______________________________________________________________________________________

______________________________________________________________________

* 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