SEO

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

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

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
>

In article <3efffd76$0$49116$e4fe514c@news.xs4all.nl>,
onnoirs@excite.com says...
> Hi Gert,
>
> I didn't create the explain tables beforehand, but did that now. With no
> effect. I still get the same output.

That's because db2expln doesn't use the data within the explain
tables, it only uses the package information to display the access
path. The DB2_SQLROUTINE_PREPOPTS options only make sense when you
can/need to use Visual Explain.

>
> 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?
>

Sorry, I don't know. I can guess what it means but I don't know why
it's there. Maybe a deferred bind has been used?

Did you try to use the Control Center to examine the explain tables?
If you right-click on the Database name and select the option 'Show
Explained Statements History' you can check if any explain data has
been collected already.

Hope this helps.

Kind regards, Gert

Ps. If you want some direct help you can phone me tomorrow during my
break, email me to get my number.

Gert van der Kooij wrote:

> In article <3efffd76$0$49116$e4fe514c@news.xs4all.nl>,
> onnoirs@excite.com says...
>
>>Hi Gert,
>>
>>I didn't create the explain tables beforehand, but did that now. With no
>>effect. I still get the same output.
>
>
> That's because db2expln doesn't use the data within the explain
> tables, it only uses the package information to display the access
> path. The DB2_SQLROUTINE_PREPOPTS options only make sense when you
> can/need to use Visual Explain.
>

Perhaps db2exfmt tool would be useful here?

Jan M. Nelken

In article
<D%%La.20462$2ay.15180@news01.bloor.is.net.cable.r ogers.com>,
Unknown.User@Invalid.Domain says...
> Gert van der Kooij wrote:
>
> > In article <3efffd76$0$49116$e4fe514c@news.xs4all.nl>,
> > onnoirs@excite.com says...
> >
> >>Hi Gert,
> >>
> >>I didn't create the explain tables beforehand, but did that now. With no
> >>effect. I still get the same output.
> >
> >
> > That's because db2expln doesn't use the data within the explain
> > tables, it only uses the package information to display the access
> > path. The DB2_SQLROUTINE_PREPOPTS options only make sense when you
> > can/need to use Visual Explain.
> >
>
> Perhaps db2exfmt tool would be useful here?
>
> Jan M. Nelken
>
>

Thanks, it slipped my mind