Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5

    Unanswered: Index comparison

    DB2 v9.7.5 Linux

    I work mostly with SQL performance and on the mainframe, so a little help here would be appreciated. I have compiled a list of indexes that haven't been used in over 6 months, but some of them are unique. So, I want to do an exists to see if any that are unique have another index out there with the same columns that enforce the same uniqueness. For instance, the unique key for the table may be col1, col2, now lets say I have another index col3, col2, col1, it may have been defined as unique since it contains the unique key. The problem I am having is the catalog is a little different on here than I am used to, on the mainframe we have syskeys table that has a row per index column. Here on LUW, I am finding that all of the columns are in a single column. Any ideas on how I would write the SQL to see if this jumble is in that other jumble?

    Thanks.
    Dave

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Here on LUW, I am finding that all of the columns are in a single column
    I think you may have overlooked SYSCAT.KEYCOLUSE, which has a row for each column of each unique index or PK.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that the issue might be an application of relational division(allowing reminder).
    http://www.dbforums.com/db2/1667338-...g-listagg.html

    Example 1: Tested on DB2 9.7.5 on Windows.
    Code:
    WITH
     index_with_concat_strings AS (
    SELECT ix.TABSCHEMA
         , ix.TABNAME
         , ix.INDSCHEMA
         , ix.INDNAME
         , MAX(ix.COLCOUNT)   AS COLCOUNT
         , MAX(ix.UNIQUERULE) AS UNIQUERULE
         , VARCHAR(
              LISTAGG(
                 cu.COLNAME || ' ' ||
                 CASE cu.COLORDER
                 WHEN 'A' THEN 'ASC'
                 WHEN 'D' THEN 'DESC'
                 ELSE          'Include'
                 END
               , ' , '
              ) WITHIN GROUP( ORDER BY cu.COLSEQ  )
            , 1000 ) AS index_columns
         , VARCHAR(
              '%' ||
              LISTAGG(
                 '(' || cu.COLNAME || ')'
               , '%'
              ) WITHIN GROUP( ORDER BY cu.COLNAME )
              || '%'
            , 1000 ) AS pattern_string
     FROM  SYSCAT.INDEXES     AS ix
     INNER JOIN
           SYSCAT.INDEXCOLUSE AS cu
      ON   cu.INDSCHEMA = ix.INDSCHEMA
       AND cu.INDNAME   = ix.INDNAME
     WHERE ix.UNIQUERULE IN ('U' , 'P')
       AND ix.TABSCHEMA <> 'SYSIBM'
     GROUP BY
           ix.TABSCHEMA
         , ix.TABNAME
         , ix.INDSCHEMA
         , ix.INDNAME
    )
    SELECT VARCHAR(s.TABSCHEMA || '.' || s.TABNAME , 30) AS qualified_table_name
         , s.UNIQUERULE || ': ' ||
           VARCHAR(s.INDSCHEMA || '.' || s.INDNAME , 30) AS source_qualified_indexname
         , r.UNIQUERULE || ': ' ||
           VARCHAR(r.INDSCHEMA || '.' || r.INDNAME , 30) AS included_in_indexname
         , VARCHAR(s.index_columns , 40) AS source_index_columns
         , VARCHAR(r.index_columns , 80) AS including_index_columns
     FROM  index_with_concat_strings AS s
     INNER JOIN
           index_with_concat_strings AS r
      ON   r.TABSCHEMA = s.TABSCHEMA
      AND  r.TABNAME   = s.TABNAME
      AND
      (    r.COLCOUNT       > s.COLCOUNT
       OR  r.COLCOUNT       = s.COLCOUNT
       AND r.pattern_string > s.pattern_string
      )
      AND  r.pattern_string LIKE s.pattern_string
     ORDER BY
           s.TABSCHEMA
         , s.TABNAME
         , s.UNIQUERULE
         , s.INDSCHEMA
         , s.INDNAME
    ;

    Example 1r: Result of Example 1.
    Code:
    QUALIFIED_TABLE_NAME           SOURCE_QUALIFIED_INDEXNAME        INCLUDED_IN_INDEXNAME             SOURCE_INDEX_COLUMNS                     INCLUDING_INDEX_COLUMNS                                                         
    ------------------------------ --------------------------------- --------------------------------- ---------------------------------------- --------------------------------------------------------------------------------
    CHIPPIB .T1                    P: SYSIBM  .SQL111202203348030    U: CHIPPIB .T1_A                  REC_SEQ_NBR ASC                          CURR_IND ASC , COL1 ASC , COL2 ASC , COL3 ASC , COL4 ASC , REC_SEQ_NBR DESC     
    CHIPPIB .TABLE1                P: SYSIBM  .SQL111211151836530    U: CHIPPIB .TABLE1_A              REC_SEQ_NBR ASC                          CURR_IND ASC , BUS_KEY ASC , REC_SEQ_NBR ASC                                    
    CHIPPIB .TABLE1                P: SYSIBM  .SQL111211151836530    U: CHIPPIB .TABLE1_B              REC_SEQ_NBR ASC                          CURR_IND ASC , BUS_KEY ASC , REC_SEQ_NBR DESC                                   
    CHIPPIB .TABLE1                P: SYSIBM  .SQL111211151836530    U: CHIPPIB .TABLE1_C              REC_SEQ_NBR ASC                          BUS_KEY ASC , REC_SEQ_NBR DESC                                                  
    CHIPPIB .TABLE1                P: SYSIBM  .SQL111211151836530    U: CHIPPIB .TABLE1_D              REC_SEQ_NBR ASC                          BUS_KEY ASC , REC_SEQ_NBR DESC , CURR_IND ASC                                   
    CHIPPIB .TABLE1                U: CHIPPIB .TABLE1_C              U: CHIPPIB .TABLE1_A              BUS_KEY ASC , REC_SEQ_NBR DESC           CURR_IND ASC , BUS_KEY ASC , REC_SEQ_NBR ASC                                    
    CHIPPIB .TABLE1                U: CHIPPIB .TABLE1_C              U: CHIPPIB .TABLE1_B              BUS_KEY ASC , REC_SEQ_NBR DESC           CURR_IND ASC , BUS_KEY ASC , REC_SEQ_NBR DESC                                   
    CHIPPIB .TABLE1                U: CHIPPIB .TABLE1_C              U: CHIPPIB .TABLE1_D              BUS_KEY ASC , REC_SEQ_NBR DESC           BUS_KEY ASC , REC_SEQ_NBR DESC , CURR_IND ASC                                   
    DB2ADMIN.ACT                   P: DB2ADMIN.PK_ACT                U: DB2ADMIN.XACT2                 ACTNO ASC                                ACTNO ASC , ACTKWD ASC                                                          
    DB2ADMIN.PRICING_TIER          P: SYSIBM  .SQL120328161945570    U: DB2ADMIN.PRICING_TIER_INCLUDE  LEVEL ASC                                LEVEL ASC , PAYMENT Include                                                     
    DB2ADMIN.PRICING_TIER          U: DB2ADMIN.PRICING_TIER_PAYMENT  U: DB2ADMIN.PRICING_TIER_INCLUDE  PAYMENT ASC                              LEVEL ASC , PAYMENT Include                                                     
    RYUKAI  .TABLE3                U: RYUKAI  .TABLE3_NAME           U: RYUKAI  .TABLE3_ID_NAME        NAME3 ASC                                ID3 ASC , NAME3 ASC                                                             
    RYUKAI  .TABLE3                U: RYUKAI  .TABLE3_NAME           U: RYUKAI  .TABLE3_NAME_ID        NAME3 ASC                                NAME3 ASC , ID3 ASC                                                             
    
      13 record(s) selected.
    Last edited by tonkuma; 09-14-13 at 18:26. Reason: Remove concat_columns column from Example 1.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Yes, I did miss that one Nick.

    Thanks Tonkuma.

Posting Permissions

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