Re: SQL diags to return actual statement executed?

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

At 09:37 AM 4/26/2007, you wrote:

Yes, this is a debug-mode thing that needs to be able to be switched on by a
parameter.

Your first two ideas strike fear in my heart for the potential size of the
resulting output.

The exit point idea sounds interesting and to the point, but I've never done
one. Is there a reference you can point me to, especially if it involves
capturing SQL statements?

Thanks,
Dan

On 4/26/07, rob@xxxxxxxxx <rob@xxxxxxxxx> wrote:
>
> Why do you need to log the SQL statements?
> Debugging the program?
> Troglodytes fear of SQL? Like, who'd log all SQL statements but not all
> READ, CHAIN, WRITE, UPDATE, etc?
>
> I have some ideas in mind, but I'd like to know what you are trying to
> accomplish. Initial shoot from the hip ideas include:
> STRDBG UPDPROD(*YES) // Note: No program name. Powerful stuff will be
> in that joblog.
> Journalling.
> Exit point that traps all SQL statements.
>
>
> Rob Berendt



Dan <dan27649@xxxxxxxxx>
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 04/25/2007 05:15 PM
>
> To
> "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
>
> Subject: SQL diags to return actual statement executed?
>
> (originally posted in RPG400, I decided to post here as well in case
> non-RPG'ers could assist, since my question relates more to SQL
> programming than RPG programming.)
>
> Got an RPG program that does a ton of dynamic SQL INSERTs and UPDATEs,
> which come from our control tables and, therefore, not actually coded in
> the RPG
> source. Because the statements were prepared, we were able to take the
> variable used in the "Execute Immediate :SQLstmt" statement and output it
> to our log file.
>
> Now, we are taking a different tack to significantly improve performance.
> Instead of using all of the dynamic SQLs, our developer has written an
> application to generate the SQLRPGLE source program to do away with the
> dynamic prepares. Performance improvement is dramatic but, along with
> this change, we apparently lost the ability to log the SQL statement that
> was
> actually executed. The SQL statements still use variables, and we need to
> see the statements with the variables converted in the log. We do not
> know how to capture this.
>
> A while ago, I was educated in the "Get Diagnostics" statement, and a
> first poke at the documentation I thought would turn up something that
> would
> return the statement that was actually executed. But I was unable to find
> anything. Am I looking in the wrong spot? FWIW, this is on v5r3.
>
> TIA,
> Dan
--
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.