Hi Gert,

I didn't create the explain tables beforehand, but did that now. With no
effect. I still get the same output.

I am not really familiar with Visual Explain but after reading the
documentation I thought that I could not explain packages with that tool.
Therefore, I used db2expln. The problem is that my SQL stored procedure
contains declared temporary tables, and I want to get explain data of them
as well.

Do you know what the message "Section will be recompiled at next use."
means?


Regards,

Onno Ceelen



"Gert van der Kooij" <gk-ibm-db2@xs4all.nl> wrote in message
news:MPG.1966a27a792e55f498988b@news.xs4all.nl...
> In article <3efbf637$1$49105$e4fe514c@news.xs4all.nl>,
> onnoirs@excite.com says...
> > Hi,
> >
> > I want to get a full EXPLAIN plan of my SQL stored procedure. I found
out
> > that you can set environmental variables by using db2set. So I added the
> > line:
> >
> > DB2_SQLROUTINE_PREPOPTS=EXPLAIN ALL
> >
> > But when I use db2expln like:
> >
> > db2expln -d <db> -u <username> <password> -o c:\explain.txt -c
<creator> -p
> > <package_id> -s 0
> >
> > I see all different sections but without a plan (same as before I
changed
> > the db2set option). For most sections a line is written stating "Section
> > will be recompiled at next use.".
> >
> > What I then did was executing a CALL statement on this SQL stored
procedure
> > with not effect. I also tried to REBIND the package.
> >
> > I also performed a db2stop and db2start so that maybe the
> > DB2_SQLROUTINE_PREPOPTS was initialized, also this had no effect.
> >
> > I wonder whether the sections in my SQL stored procedures get
precompiled
> > everytime or that I have to perform some more actions to get a
well-formed
> > explain plan.
> >
> >
> > Thanks for any help,
> >
> > Onno Ceelen
> >
> >
> >
>
> Did you create the explain tables?
> The DDL is in the SQLLIB/misc directory (db2 -tf EXPLAIN.DDL).
>
> After that you can use the Control Center (db2expln isn't the right
> tool to get data collected in the explain tables) to take a visual
> look at the access plan. You might need to set the EXPLAINSNAP ALL
> option to get an explain snapshot also.
>
> A warning from the Command Reference (I'm not sure if this suits your
> situation):
>
> If the package is to be used for a routine, then the routine
> must be defined as MODIFIES SQL DATA, or incremental
> bind and dynamic statements in the package will cause a run
> time error (SQLSTATE 42985).
>
> Hope this helps.
>
> Kind regards,
>
> Gert van der Kooij
> Ordina, Netherlands
>