Idetifying file/keys created by the SQL optimiser. - IBM AS400

This is a discussion on Idetifying file/keys created by the SQL optimiser. - IBM AS400 ; Hi All. Is there a way to identify what indexes the optimiser creates when running SQL(Imbedded SQL) I am trying to create the indexes for the SQL so it doesn't need to go through the process of creating the "*querynnnn" ...

+ Reply to Thread
Results 1 to 5 of 5

Thread: Idetifying file/keys created by the SQL optimiser.

  1. Idetifying file/keys created by the SQL optimiser.

    Hi All.

    Is there a way to identify what indexes the optimiser creates when
    running SQL(Imbedded SQL)

    I am trying to create the indexes for the SQL so it doesn't need to go
    through the process of creating the "*querynnnn" file in Qtemp.

    What I was hoping to do was interogate (something) that will show we
    what the optimiser is doing and create an idex so it doesn't need to
    do it.


    Thanks....................

  2. Re: Idetifying file/keys created by the SQL optimiser.

    TadPole wrote:
    > Hi All.
    >
    > Is there a way to identify what indexes the optimiser creates when
    > running SQL(Imbedded SQL)
    >
    > I am trying to create the indexes for the SQL so it doesn't need to go
    > through the process of creating the "*querynnnn" file in Qtemp.
    >
    > What I was hoping to do was interogate (something) that will show we
    > what the optimiser is doing and create an idex so it doesn't need to
    > do it.
    >
    >
    > Thanks....................


    Normally just issuing the comand STRDBG alone before calling a program
    or running any SQL will give you that information and a lot more.

    Brad
    www.bvstools.com

  3. Re: Idetifying file/keys created by the SQL optimiser.

    You might also want to check out "Advised Indexes" in Ops
    Navigator. Or, if possible, run the SQL statement thru Visual
    Explain.

  4. Re: Idetifying file/keys created by the SQL optimiser.

    Graybeard wrote:
    > You might also want to check out "Advised Indexes" in Ops
    > Navigator. Or, if possible, run the SQL statement thru Visual
    > Explain.


    With debug stated, it will also let you know the advised indexes... it
    produces a ton of information in the job log after the SQL or Query is run.

  5. Re: Identifying file/keys created by the SQL optimiser.

    As of V5R3, Debug Messages do NOT include all of the advised indexes for SQL
    statements processed by the newer SQL Query Engine (SQE) .

    Debug Messages is no longer considered a strategic tool for DB2 tuning - this
    tool was not enhanced for SQE. Users should start using Visual Explain, Database
    Monitor, and Index Advisor.

    I'd highly recommend that customers attend the DB2 for i SQL Performance
    Workshop to learn how to use these new performance tools and how to tune the
    performance of your reports and queries.

    The schedule can be found online at:
    ibm.com/systemi/db2/db2performance.html


    Bradley V. Stone wrote:
    > Graybeard wrote:
    >> You might also want to check out "Advised Indexes" in Ops
    >> Navigator. Or, if possible, run the SQL statement thru Visual
    >> Explain.

    >
    > With debug stated, it will also let you know the advised indexes... it
    > produces a ton of information in the job log after the SQL or Query is run.


    --
    Kent Milligan
    ISV Enablement - System i
    kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
    >>> ibm.com/iseries/db2

    (opinions stated are not necessarily those of my employer)

+ Reply to Thread