Re: Visual Explain with Embedded SQL

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.