Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2006
    Posts
    2

    Unanswered: Index selection db2 sql queary

    Hi All,

    Can any one provide me with some suggestion how to force db2 to select an index
    i have a following sql for which there is a full table scan even though we have index matching the where clause

    SELECT T9222.CNNO,
    T9222.TRANNO,
    T1015.CTRIBADT
    FROM DB2LPP.TLP9222 T9222,DB2LPP.TLP9015 T1015
    WHERE T9222.TRTYP = 'AL'
    AND T9222.TRSTATCD IN ('SM','SI','WB','FL')
    AND T9222.TRANNO = T1015.TRANNO

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Please provide DB2 version, OS version, and DDL for the tables and indexes in question. Also insure that you have don runstats on table and indexes.

    Andy

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    provide index definition and access plan.
    Grofaty

  4. #4
    Join Date
    Dec 2006
    Posts
    2
    we hav an index for this table,with follwing columns

    TRTYP
    TRSTATCD
    TRANNO
    CLNTID
    but this sql is not picking up this index,insted it gos for a table scqane:

    access path:
    Line 01 Data accessed from the table
    SQL operation: SELECT statement
    In......Table: DB2TEST.TLP9222
    Access Method: Table scan using sequential prefetch.
    Lock: Intent share

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    A possible reason could be that, according to the statistics, the combined TRTYP = 'AL' & TRSTATCD IN ('SM','SI','WB','FL') occur too often to justify index use.
    Or maybe the index is non-clustering and the RID pool is too small.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    have you tried creating an index this way:
    create index db2test.test on DB2TEST.TLP9222 (TRANNO, trtyp, trstatcd)

    Is there any index on table DB2LPP.TLP9015?

    Try also running distributed statistic: runstats on table xxx with distribution and detailed indexes all.

    Please also provide all access plan data.

    Hope this helps,
    Grofaty

Posting Permissions

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