Displaying SQL when Abending

No Name Available

Displaying SQL when Abending
Dear List Users/SMEs,
 
We have a legacy program that executes about a couple of hundred SQL Statements. Each time an error occurs, the program will abend but does not provide any further information other than the paragraph name.
 
I was wondering if there is any feature in DB2 that would display the SQL statement being executed that is the cause of the abend.
Any feed back/suggestion is greatly appreciated. Thanks you all in advance.

Best Regards
Neeraj Peddu
 
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Cristian Molaro

Re: Displaying SQL when Abending
(in response to No Name Available)
Hi Neeraj,

Found in the Admin Guide V7:
" You can use the subroutine DSNTIAR to convert an SQL return code into a
text message. DSNTIAR takes data from the SQLCA, formats it into a message,
and places the result in a message output area that you provide in your
application program"

A nice example of use is the IBM provideed DSN710.SDSNSAMP(DSNTEP2).
However, this don't provide the query text itself. You shold adapt your code
for printing it with the SQL info.

Hope it helps and regards,
Cristian Molaro
MConsunting.be
Belgium

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Kumar Vaddadi/EIG

Re: Displaying SQL when Abending
(in response to Cristian Molaro)

Hi,

You can also look at Db2 Application Programming & SQL guide (SC26 -
9933-05) Chapter 6 under the topic "Handling the SQL return codes" .

************* Thanks & Regards *******************
Vaddadi Shyam Kumar
IBM Db2 Certified Database Administrator
Data Management Group
The Economical Insurance Group, Waterloo.
Reach me @ 519-570-8500 ext 2242
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Happiness is when what you think, what you say, and what you do are in
harmony. ----- Mahatma Gandhi
******************************************************************************************************************************


|---------+---------------------------->
| | Cristian Molaro |
| | <[login to unmask email]|
| | BE> |
| | Sent by: DB2 Data|
| | Base Discussion |
| | List |
| | <[login to unmask email]|
| | ORG> |
| | |
| | |
| | 01/06/2006 04:16 |
| | AM |
| | Please respond to|
| | DB2 Database |
| | Discussion list |
| | at IDUG |
| | |
|---------+---------------------------->
>------------------------------------------------------------------------------------------------------------------------------|
| |
| To: [login to unmask email] |
| cc: |
| Subject: Re: [DB2-L] Displaying SQL when Abending |
>------------------------------------------------------------------------------------------------------------------------------|




Hi Neeraj,

Found in the Admin Guide V7:
" You can use the subroutine DSNTIAR to convert an SQL return code into a
text message. DSNTIAR takes data from the SQLCA, formats it into a message,
and places the result in a message output area that you provide in your
application program"

A nice example of use is the IBM provideed DSN710.SDSNSAMP(DSNTEP2).
However, this don't provide the query text itself. You shold adapt your
code
for printing it with the SQL info.

Hope it helps and regards,
Cristian Molaro
MConsunting.be
Belgium

---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm








***
Confidentiality Warning: This message and any attachments transmitted with it are confidential and intended solely for the use of the individual(s) or entity to whom they are addressed. If you have received this email in error please delete this message and any attachments. If you are not the intended recipient, you are notified that any review, retransmission, conversion to hard copy, copying, circulation or other use of this message and any attachments is strictly prohibited.

Information confidentielle : Le présent message, ainsi que tout fichier qui y est joint, est envoyé à l'intention exclusive de son ou ses destinataires; il est de nature confidentielle. Si vous n'êtes pas le destinataire prévu, supprimez ce message et tout document joint. Nous avertissons toute personne autre que le destinataire prévu que tout examen, transmission, impression, copie, distribution ou autre utilisation de ce message et de tout fichier qui y est joint est strictement interdit.

Richard Fazio

Re: Displaying SQL when Abending
(in response to Kumar Vaddadi/EIG)
You may want to try adding a diagnostic output coupled with an SQLTRACE.
Got a DB2 Monitor? Most monitors have some form of SQL
capture...otherwise you could turn on some heinous SMF traces and try to
pick out the detail yourself from GTF.

This should dump out connection / return code doc.
//DSNTRACE DD SYSOUT=*

After you get an SQLCODE...read up on DSN1SDMP. I think this will
provide some value.

best of luck,
fa

Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn
z
>>> [login to unmask email] 2006-01-05 4:19:21 PM >>>

Dear List Users/SMEs,

We have a legacy program that executes about a couple of hundred SQL
Statements. Each time an error occurs, the program will abend but does
not provide any further information other than the paragraph name.

I was wondering if there is any feature in DB2 that would display the
SQL statement being executed that is the cause of the abend.

Any feed back/suggestion is greatly appreciated. Thanks you all in
advance.

Best Regards
Neeraj Peddu

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm