RE: Returning Passed SQL Statements from Debug

The best tool for seeing what the SQL statement really was is the database 
monitor. STRDBMON gets it going, ENDDBMON does what you'd expect. The data goes 
into a funky file that is a mess to work with, but here is a link to some 
queries that might be helpful:

http://www-03.ibm.com/servers/eserver/iseries/db2/dbmonqrys.htm

And there is a Redpaper with more of the same at

http://www.redbooks.ibm.com/redpapers/pdfs/redp0502.pdf

There is a record in the outfile, with a record ID of 1000, that contains the 
SQL statement, but with parameter markers. There are records with ID 3010 that 
have the actual values used. Ops Nav has Visual Explain, which can help put it 
all together for you. And you can run database monitors from Navigator.

A quote from the Redpaper - 
The text of the query along with the host variable values from the 3010 record 
can be used to reconstruct the query for debug purposes.
The 3010 record lists the values, so you see them in one place - easier than 
EVAL, maybe.

HTH
Vern
-------------- Original message -------------- 
From: "Brian Piotrowski" <bpiotrowski@xxxxxxxxxxxxxxx> 

> Exactly! Here's the statement I have: 
> 
> C/Exec SQL 
> C+ update ssw85 set kdlotstr=(select min(dskdlf) from sst01 where 
> C+ dsotyp=:ordertyp and dsodno=:ordernum) 
> C+ , kdlotend=(select max(dskdlf) from 
> C+ sst01 where dsotyp=:ordertyp and dsodno=:ordernum and 
> C+ dsspcd=:supp and dssplc=:supploc), asnnum=(select distinct 
> C+ asno from sst02 where 
> C+ asodtp=:ordertyp and asodno=:ordernum 
> C+ and asplcd=:plantcde and asspcd=:supp 
> C+ and assplc=:supploc) where ordertyp=:ordertyp 
> C+ and ordernum=:ordernum and 
> C+ suppcode=:suppcode 
> C/End-Exec 
> 
> I want to run an EVAL on this statement after it executes to see exactly 
> what was passed instead of EVAL DSKDLF, EVAL ORDERTYP, EVAL ORDERNUM, 
> etc... 
> 
> Brian. 
> 
> -----Original Message----- 
> From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
> Sent: Tuesday, March 21, 2006 10:00 AM 
> To: Midrange Systems Technical Discussion 
> Subject: RE: Returning Passed SQL Statements from Debug 
> 
> I think I understand now. So, it's not a prepared statement or an 
> executed immediate statement; it's more like a 
> execsql select thisfield, thatfield from myfile where 
> otherfield=:hostvar1 
> and yaddafield between :this ...; 
> and you want to see what that looks like, right? 
> 
> Rob Berendt 
> -- 
> Group Dekko Services, LLC 
> Dept 01.073 
> PO Box 2000 
> Dock 108 
> 6928N 400E 
> Kendallville, IN 46755 
> http://www.dekko.com 
> 
> 
> 
> 
> 
> "Brian Piotrowski" 
> Sent by: midrange-l-bounces@xxxxxxxxxxxx 
> 03/21/2006 09:44 AM 
> Please respond to 
> Midrange Systems Technical Discussion 
> 
> 
> To 
> "Midrange Systems Technical Discussion" 
> cc 
> 
> Subject 
> RE: Returning Passed SQL Statements from Debug 
> 
> 
> 
> 
> 
> 
> Hi Mike, 
> 
> Yeah, I can see it in the source, but the statement with which I'm 
> working has about ten different variables. I was hoping to see 'em all 
> in one shot instead of doing an EVAL on each of them. 
> 
> Brian. 
> 
> -----Original Message----- 
> From: Michael_Schutte@xxxxxxxxxxxx [mailto:Michael_Schutte@xxxxxxxxxxxx] 
> 
> Sent: Tuesday, March 21, 2006 9:28 AM 
> To: Midrange Systems Technical Discussion 
> Subject: Re: Returning Passed SQL Statements from Debug 
> 
> Can you not view it in the source of the program? 
> 
> If you are having trouble with it... but it in debug mode and just the 
> value of the dynamic variables. 
> 
> Michael Schutte 
> Work 614-492-7419 
> email michael_schutte@xxxxxxxxxxxx 
> 
> 
> 
> 
> "Brian 
> 
> Piotrowski" 
> 
> > To 
> oeparts.com> "Midrange Systems Technical 
> 
> Sent by: Discussion" 
> 
> midrange-l-bounce 
> 
> s@xxxxxxxxxxxx 
> cc 
> 
> 
> 
> Subject 
> 03/21/2006 09:11 Returning Passed SQL Statements 
> 
> AM from Debug 
> 
> 
> 
> 
> 
> Please respond to 
> 
> Midrange Systems 
> 
> Technical 
> 
> Discussion 
> 
> > 
> nge.com> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> Hi All, 
> 
> 
> 
> Is there an EVAL function that will return the SQL statement passed into 
> the compiler if I do not declare it as a variable? Normally I build an 
> SQL statement using SQLSTMT = "xxxx". When I do this, all I need to do 
> is an EVAL SQLSTMT in debug to show me what was passed. However, there 
> are quite a few times when I just pass the entire statement through to 
> compiler with the dynamic variables. Is there an EVAL statement that I 
> can use to see it? I did a quick search, but all I could find were the 
> general evals on SQLCOD, SQLCA, etc. 
> 
> 
> 
> Thanks, 
> 
> 
> 
> Brian. 
> 
> 
> 
> -=-=-=-=-=-=-=-=-=-=-=-=-=- 
> 
> Brian Piotrowski 
> 
> Specialist - I.T. 
> 
> Simcoe Parts Service, Inc. 
> 
> PH: 705-435-7814 
> 
> FX: 705-435-6746 
> 
> -=-=-=-=-=-=-=-=-=-=-=-=-=- 
> 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
> 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
> 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
> 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
>