Re: SQL diags to return actual statement executed?

[Dan <dan27649@xxxxxxxxx>]

Thanks Vern,

The redpiece was very helpful.  I hadn't thought of the db monitor before.
Lots of good stuff there.  This will be VERY helpful to us.  I gave it a
try, and the only drawbacks that I see are:

1) the host variables are not plugged into the statement.  (You have to scan
the statement for occurrences of a capital 'H', and use values in the
subsequent field to substitute for the 'H'.)  This is a minor quibble; we
just need to ensure we never use a standalone 'H' in a real query.

2) the SQL statements are truncated after the first 1000 characters; we have
several that exceed that.  It *appears* that they are continued in the next
record, but if this is the case, there is definitely a missing gap.  I'll
dig into this a bit more to see if the redpiece has anything else that can
help me there.

Thanks,
Dan

On 5/5/07, Vernon Hamberg <vhamberg@xxxxxxxxxxx> wrote:
> Dan
>
> If you want to retrieve the statement used, I suggest turning on the
> database monitor when the flag is turned on. There are several record
> types in the resulting file - it can be run against only the job in
> question - it gives you all kinds of things you don't need, too, but
> will get you the statements used. You can even use the resulting file
> as input to Visual Explain in iSeries Navigator, which can extract
> the statements used for you - saves you some trouble. If you want to
> do that work yourself, there are some SELECT statements you should
> use, documented in
>
>
> http://www-912.ibm.com/s_dir/slkbase.nsf/a9f6fda1102483c486256ffd007816dd/280c2652737e1184862567b60069dd8d?OpenDocument
>
> if that link does not all get in to your browser, copy and paste it's
> parts. If you are asked to sign in to get it, let me know - I'll get
> the body of the article.
>
> And there is a redpiece with more info at
> http://www.redbooks.ibm.com/redpapers/pdfs/redp0502.pdf
>
> HTH
> Vern