Re: Visual Explain with Embedded SQL

Unfortunately, run SQL scripts doesn't support host variables...

I've always just replaced the variables with constant values...

However, IIRC the other option is to run the program with the embedded
SQL and use VE after the fact as when you use VE on the statement from
the plan cache and/or SQL monitor and/or the job's "Last SQL
Statement" the system handles the fact that the statement had host
variables.

HTH,
Charles

On Thu, Jun 14, 2012 at 8:35 AM, Michael Ryan <michaelrtr@xxxxxxxxx> wrote:

How can I use Visual Explain to give feedback (processing
recommendations) with an embedded SQL script that uses host variables?
Here's an example:

DECLARE C1 CURSOR FOR
  Select * From MyFile
  Where    MyField1 In(:FldDrxA,:FldDrxB,:FldDrxD)
       And MyField2 Between :HostFld1  and :HostFld2
Order By Case When :DspSort = 1 Then Myfield3 Else NULL End,
        Case When :DspSort = 1 Then MyField4  Else NULL End
For Read Only

I want to 'tune' this statement so I get better performance. I go into
Navigator, Run SQL Scripts, Visual Explain. I get this:

SQL State: 42618
Vendor Code: -312
Message: [SQL0312] Variable FLDDRXA not defined or not usable.

Is there a way I can declare the host variables in Run SQL Scripts so
Visual Explain can work?

If it matters, this statement is actually more complex, and there is
another DECLARE CURSOR in the program.

Thanks!
--
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.