sql index question - IBM AS400

This is a discussion on sql index question - IBM AS400 ; If I code this in a sql source member statement against a file is there a way to determine similiarly like dspdbr what sql statements have indexes referencing which files, if so how. When you create an index it does ...

+ Reply to Thread
Results 1 to 5 of 5

Thread: sql index question

  1. sql index question

    If I code this in a sql source member statement against a file is
    there a way to determine similiarly like dspdbr what sql statements
    have indexes referencing which files, if so how.

    When you create an index it does not become an object like a LF,
    correct.

    If you can create a sql index object how would you do this other than
    ILERPG??


  2. Re: sql index question

    iseriesflorida wrote:
    > If I code this in a sql source member statement against a file is
    > there a way to determine similiarly like dspdbr what sql statements
    > have indexes referencing which files, if so how.
    >
    > When you create an index it does not become an object like a LF,
    > correct.
    >
    > If you can create a sql index object how would you do this other than
    > ILERPG??
    >


    An SQL INDEX is a type of LF. These descriptions may help:
    http://publib.boulder.ibm.com/infoce...erminology.htm
    http://publib.boulder.ibm.com/infoce...fyxdbterms.htm

    Use the CREATE INDEX SQL statement to create an index. This can be
    embedded in a program (eg exec sql create index ...), run in iNav Run
    SQL Scripts, via CL RUNSQLSTM, via ODBC/JDBC client, via dynamic SQL
    (PREPARE/EXECUTE), etc.
    http://publib.boulder.ibm.com/infoce...zmstxcindx.htm

    Re index usage, there are tools like Visual Explain and Index Advisor.
    Here is some info:
    http://publib.boulder.ibm.com/infoce...q/queryopt.htm

    --
    Karl Hanson

  3. Re: sql index question

    il 14/10/2008 14.20, Scrive iseriesflorida 43542568:
    > If I code this in a sql source member statement against a file is
    > there a way to determine similiarly like dspdbr what sql statements
    > have indexes referencing which files, if so how.
    >
    > When you create an index it does not become an object like a LF,
    > correct.
    >

    It does. You won't find a corresponding LF if you create an UNIQUE or
    PRIMARY key beacuse the index become property of the physical file.
    > If you can create a sql index object how would you do this other than
    > ILERPG??
    >

    Maybe I did not understand the question.
    You can create an index with CREATE INDEX SQL statement in any language
    supported by SQL toolkit, in a source member using RUNSQLSTM, generally
    speaking with any SQL interfece that access as400, e.g. JDBC throug a
    db2 JDBC driver, or CLI too.

    --
    Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

  4. Re: sql index question

    iseriesflorida wrote:
    > If I code this in a sql source member statement against a file is
    > there a way to determine similiarly like dspdbr what sql statements
    > have indexes referencing which files, if so how.


    DSPDBR will tell you what LF objects (and SQL indexes) exist over a
    file, it won't tell you what DDS source member (or SQL statement) was
    used to create the LF (or SQL index).

    > When you create an index it does not become an object like a LF,
    > correct.


    Wrong. An SQL index appears as an LF object.

    > If you can create a sql index object how would you do this other than
    > ILERPG??


    I would do it in a separate source member (like DDS source), and use
    RUNSQLSTM to create the index.

  5. Re: sql index question

    iseriesflorida wrote:
    > If I code this in a sql source member statement against a file is
    > there a way to determine similiarly like dspdbr what sql statements
    > have indexes referencing which files, if so how.
    >
    > When you create an index it does not become an object like a LF,
    > correct.


    Yes, it does. It also shows up with DSPDBR.


    >
    > If you can create a sql index object how would you do this other than
    > ILERPG??


    You can do it in STRSQL, and you can do it in the SQL script processor
    of iSeries Navigator (CWBUNDBS).

+ Reply to Thread