Professional Web Applications Themes

Explain plan for SQL stored procedure - IBM DB2

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

  1. #1

    Default Explain plan for SQL stored procedure

    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


    Onno Ceelen Guest

  2. #2

    Default Re: Explain plan for SQL stored procedure

    In article <3efbf637$1$49105$e4fe514cnews.xs4all.nl>,
    [email]onnoirsexcite.com[/email] 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

    Gert van der Kooij Guest

  3. #3

    Default Re: Explain plan for SQL stored procedure

    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
    doentation 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-db2xs4all.nl> wrote in message
    news:MPG.1966a27a792e55f498988bnews.xs4all.nl...
    > In article <3efbf637$1$49105$e4fe514cnews.xs4all.nl>,
    > [email]onnoirsexcite.com[/email] 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
    >

    Onno Ceelen Guest

  4. #4

    Default Re: Explain plan for SQL stored procedure

    In article <3efffd76$0$49116$e4fe514cnews.xs4all.nl>,
    [email]onnoirsexcite.com[/email] 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
    > doentation 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 Guest

  5. #5

    Default Re: Explain plan for SQL stored procedure

    Gert van der Kooij wrote:
    > In article <3efffd76$0$49116$e4fe514cnews.xs4all.nl>,
    > [email]onnoirsexcite.com[/email] 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

    Jan M. Nelken Guest

  6. #6

    Default Re: Explain plan for SQL stored procedure

    In article
    <D%%La.20462$2ay.15180news01.bloor.is.net.cable.r ogers.com>,
    [email]Unknown.UserInvalid.Doma[/email]in says...
    > Gert van der Kooij wrote:
    >
    > > In article <3efffd76$0$49116$e4fe514cnews.xs4all.nl>,
    > > [email]onnoirsexcite.com[/email] 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 :)
    Gert van der Kooij Guest

Similar Threads

  1. Explain Plan for queries
    By ovkrishna in forum Informix
    Replies: 8
    Last Post: October 27th, 04:31 PM
  2. Marty,I Have Another Explain Plan Question
    By Richard Winston in forum IBM DB2
    Replies: 4
    Last Post: September 15th, 08:05 PM
  3. Replies: 4
    Last Post: September 15th, 06:11 PM
  4. Way To Generate Readable Explain Plan?
    By Erik Hendrix in forum IBM DB2
    Replies: 1
    Last Post: July 17th, 11:54 PM
  5. need help on a stored procedure
    By Helixpoint in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 8th, 01:26 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139