Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: Capture explain for all existing Stored Procedures (corresponding packages

    v9.7 FP7

    I need to explain all existing stored procedures (without recreating or running them). I want to query the explain tables directly so don't really need to see the output from db2expln/db2exfmt. In DB2 z/OS, you can capture this info during REBIND, but this is not available in DB2 LUW. Is db2expln the only option to collect explain info for all existing SP's without recreating/running them?

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    You can get the same behavior if you BIND your routine with an EXPLAIN YES option.
    REBIND will populate the explain tables for the static statements of the routine in this case.
    Regards,
    Mark.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    But to bind SP's package with EXPLAIN YES option, I would have to set DB2_SQLROUTINE_PREPOPTS and recreate SP. I prefer not to recreate/execute SP. It looks like there is no way to get explain for existing SP's without recreating/executing them other than using db2expln (but that doesn't populate the explain tables)

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Not sure why you state the above. It has been quite a few years, but I know we used to just rebind our procedure packages years ago in v7. Haven't been working in such a situation in quite awhile, but I find it hard to believe, you can't rebind the packages today.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I think you should be looking at rebinding the packages, not the procedures themselves. then you can join to your plan table to get the external procedure name.

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    If you are at DB2 9.7 or newer you can try the EXPLAIN_FROM_CATALOG routine to place access plans to the explain tables.
    Regards,
    Mark.

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    dav1mo, I'm using DB2 LUW, not z/OS. Yes, on z/OS, I can rebind the packages with 'explain yes' and then get the info from the plan_table table.

    Mark, Thank you, EXPLAIN_FROM_CATALOG populated the explain tables (just tried for one SP / section). This home-grown application has lots of SP's, so using this method can take a long time. But it's good to know there is something I can use. Thanks again.

  8. #8
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by db2girl View Post
    This home-grown application has lots of SP's, so using this method can take a long time.
    But we can easily automate the process

    Code:
    begin
      declare explain_schema, explain_requester, source_name, source_schema, source_version varchar(128);
      declare explain_time timestamp;
    
      declare continue handler for SQLSTATE '55075' begin end;
    
      for c1 as 
      select s.pkgschema, s.pkgname, s.version, s.sectno
      from syscat.routinedep d
      join syscat.packages p on p.pkgschema=d.bschema and p.pkgname=d.bname
      join syscat.statements s 
      on s.pkgschema=p.pkgschema and s.pkgname=p.pkgname and s.version=p.pkgversion
      where d.btype='K' and d.routineschema not like 'SYS%'
      <some additional filter here>
      do
        call EXPLAIN_FROM_CATALOG(
          c1.pkgschema, c1.pkgname, c1.version, c1.sectno
        , explain_schema, explain_requester, explain_time
        , source_name, source_schema, source_version);
      end for;
    end/
    Regards,
    Mark.

  9. #9
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    If any of the sprocs use REOPT ALWAYS, or if they use explicit dynamic SQL, or if they use session-tables - what value comes from looking at the plans in this manner?

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I used EXPLAIN_FROM_CATALOG to explain several sections of another package. For some of them, I got:

    SQL20503N The explain facility is not supported for the specified section.
    Reason code = "7". SQLSTATE=55075

    SQL20503N The explain facility is not supported for the specified section.
    Reason code = "5". SQLSTATE=55075


    These sections reference DGTT and are marked as "Incremental Bind" (see this from the db2expln output I collected earlier).


    RC = 7:

    7. The section does not exist because the statement is an incremental
    bind statement. Incremental bind statements do not have a section stored
    in the catalogs. These statements are bound during the execution of an
    application process.



    Why does it say "Incremental bind statements do not have a section stored in the catalogs"? I do see it in syscat.statements. Anyone knows?

    For "Incremental Bind" sections, I don't think there is a way to get the explain data until SP is run. Right?

    Is there some way to identify "incremental bind" sections by querying the catalog, etc...?

  11. #11
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    sections that include explicit or implicit dynamic sql cannot be explained in this manner. sql against session tables will need incremental binds (i.e. compiled at run time).
    So I do not know the value of your quest: unless you omit sprocs that are reopt-always, or omit sections that refer to session tables, or omit sections that use dynamic sql.

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by db2mor View Post
    sql against session tables will need incremental binds (i.e. compiled at run time).
    The only way to populate the explain tables is to drop/recreate SP [ with DB2_SQLROUTINE_PREPOPTS/SET_ROUTINE_OPTS ('EXPLAIN ALL') set ] and then run the SP. Right?

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I realize that this is completely "off the wall", but a DBForums member sells a tool that does everything you've requested and more. At $149 for the tool, you've already spent far more time than I could justify especially since then you'd own the tool and be able to use it at will on other problems.

    I'd suggest that you check out at least the free trial of SqlSpec. I think that you'll be amazed at what it can do!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Pat Phelan View Post
    a tool that does everything you've requested and more.
    A documentation tool. I don't see how this tool will help me populate the data into the explain tables or for anything else I do on a day-to-day basis.

    I prefer to spend time learning more about SP's than what this tool can do for me.

  15. #15
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by db2girl View Post
    For "Incremental Bind" sections, I don't think there is a way to get the explain data until SP is run. Right?
    Yes.

    Quote Originally Posted by db2girl View Post
    Why does it say "Incremental bind statements do not have a section stored in the catalogs"? I do see it in syscat.statements. Anyone knows?

    Is there some way to identify "incremental bind" sections by querying the catalog, etc...?
    SYSIBM.SYSSECTION.VALID will have 'R' for all these incremental bind statements or when the package was bound with the REOPT [ONCE | ALWAYS] options.
    You can join this table with SYSCAT.STATEMENTS by UNIQUE_ID.
    Regards,
    Mark.

Posting Permissions

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