Results 1 to 15 of 15

Thread: Help With SQL

  1. #1
    Join Date
    Apr 2011
    Posts
    31

    Red face Unanswered: Help With SQL

    Example 1

    Schemna name: XYZ
    Index name : I1
    Table name : A
    columns name : col1, col2

    Schema name : PQR
    Index name : I8
    Table name : A
    columns name : col1, col2

    Query Should Return :

    Index Name Table Columns
    I1 A col1 , col2
    I8 A col1 , col2

    This means that 2 indexes in 2 different schemas are based on the same columns. I need to identify such indexes so I can then have the same names for all such indexes.
    Because the index is the same in both schemas for the same table but have different names. The idea is to standardize the indexes.

    example 2

    Schemna name: LMN
    Index name : I2
    Table name : A
    columns name : col1, col2

    Schema name : PQR
    Index name : I9
    Table name : A
    columns name: col1, col2, col3

    Query should return:

    Index Name Table Columns
    I2 A col1 , col2
    I9 A col1 , col2, col3

    This means that in different schema's the indexes are actually the same but are based on different number of columns. I need to identfy such indexes and then standardize them in both schemas.

    Please help with a query to do so. I am using the syskeys and sysindexes tables.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can concatenate values of a column by using XMLCAST( XMLGROUP(...) ...) or LISTAGG.

    There are examples in 4th and 5th article of me in this thread.
    http://www.dbforums.com/db2/1666067-...e-session.html

  4. #4
    Join Date
    Apr 2011
    Posts
    31

    Red face Sql help

    Please guide with a query as the sysindexes and syskeys tables are universal.
    Will appreciate your help.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What do you mean by "universal"?
    I'm using DB2 for Windows.
    I'm not so familiar to DB2 for z/OS nor DB2 for i.


    DB2 9.7 for LUW:
    SYSCAT.INDEXES
    Each row represents an index.
    SYSCAT.INDEXCOLUSE
    Each row represents a column that participates in an index.

    DB2 10 for z/OS:
    SYSIBM.SYSINDEXES table
    The SYSIBM.SYSINDEXES table contains one row for every index.
    SYSIBM.SYSKEYS table
    The SYSIBM.SYSKEYS table contains one row for each column of an index key.

    DB2 for i 7.1:
    The IBM i catalog includes the views and tables in the QSYS2 schema...
    SYSINDEXES
    The SYSINDEXES view contains one row for every index in the SQL schema
    created using the SQL CREATE INDEX statement, including indexes on the SQL
    catalog.
    SYSKEYS
    The SYSKEYS view contains one row for every column of an index in the SQL
    schema, including the keys for the indexes on the SQL catalog.

  6. #6
    Join Date
    Apr 2011
    Posts
    31

    Query help

    i am using this query :

    select distinct sk.column_name, sk.index_name
    , si.table_schema
    from qsys2.syskeys sk , qsys2.syskeys sk1
    , qsys2.sysindexes si
    where sk.column_name = sk1.column_name
    and sk.index_name != sk1.index_name
    and si.index_schema = sk.index_schema
    and si.table_schema in('ZDBXINV004','ZDBXDEMO4')
    and si.table_name = 'TAUDITTRAILDETAIL'
    order by 3;

    Output I am getting is not what i want.

    Sample Data taken from sysindexes and syskeys.

    TABLE_NAME || COLUMN_NAME || INDEX_NAME || SCHEMA

    T1 || C1 || IDX1 || A
    T1 || C1 || IDX2 || B


    Output desired is :

    TABLE_NAME || COLUMN_NAME

    T1 || C1


    All other being the same only the index name is different. We conclude by looking at this that in schema B IDX2 is actually IDX1 of schema A.
    By identifying such indexes based on similar columns i will replace the name of IDX2 to IDX1. There are many such indexes which have different names.

    Kindly guide please.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You used qsys2 qualifier, then you must be using DB2 for i.

    I'm using DB2 Express-C for Windows.
    So, I'll give you an example on DB2 for Windows.
    You can modify it and implement the idea on DB2 for i.


    As far as I understood, you need to take two steps.

    First, make a row with list of index columns for a index.
    Basically, there are two ways, recursive query(suggested by n_i) and string aggregate function(XMLGROUP or LISTAGG).
    You can find some examples of both technique on this forums.

    Second, compare list of index columns for a same named tables on different schema.

    Here is an example for second step on DB2 for Windows.
    1) I tried to use the functionalities common to iSeries as possible as I could.
    2) Select and compare three schema.
    3) Try to find and report index anomary:
    3-0) IX_A is normal. So, excluded from the result.
    3-1) Report indexes which are not exists on all of three schema.
    3-2) Report indexes which column list(including ASC/DESC and sequence of columns) are same,
    but have different index names, wheather the indexes exist on all of three schema or not.

    This is a tentative result.
    I doubt whather it is neccesary such complex logic and code.
    But, I have tired to review and improve the query.

    Sanple data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /*************************************************
    ********** Begin of sample data.        **********
    *************************************************/
     index_col_list
    ( index_schema
    , index_name
    , table_schema
    , table_name
    , index_columns
    ) AS (
              SELECT 'XYZ' , 'IX_A'  , 'XYZ' , 'TAB_A' , 'COL1:A, COL2:A' FROM sysibm.sysdummy1
    UNION ALL SELECT 'UVW' , 'IX_A'  , 'UVW' , 'TAB_A' , 'COL1:A, COL2:A' FROM sysibm.sysdummy1
    UNION ALL SELECT 'PQR' , 'IX_A'  , 'PQR' , 'TAB_A' , 'COL1:A, COL2:A' FROM sysibm.sysdummy1
    
    UNION ALL SELECT 'XYZ' , 'IX_B'  , 'XYZ' , 'TAB_A' , 'COL2:A, COL4:D' FROM sysibm.sysdummy1
    UNION ALL SELECT 'PQR' , 'IX_B'  , 'PQR' , 'TAB_A' , 'COL2:A, COL4:D' FROM sysibm.sysdummy1
    
    UNION ALL SELECT 'UVW' , 'IX_C'  , 'UVW' , 'TAB_A' , 'COL1:A, COL2:A, COL3:A' FROM sysibm.sysdummy1
    
    UNION ALL SELECT 'XYZ' , 'IX_D'  , 'XYZ' , 'TAB_B' , 'COL1:A, COL2:A' FROM sysibm.sysdummy1
    UNION ALL SELECT 'UVW' , 'IX_D2' , 'UVW' , 'TAB_B' , 'COL1:A, COL2:A' FROM sysibm.sysdummy1
    UNION ALL SELECT 'PQR' , 'IX_D'  , 'PQR' , 'TAB_B' , 'COL1:A, COL2:A' FROM sysibm.sysdummy1
    
    UNION ALL SELECT 'UVW' , 'IX_E1' , 'UVW' , 'TAB_C' , 'COL2:A, COL4:A' FROM sysibm.sysdummy1
    UNION ALL SELECT 'PQR' , 'IX_E'  , 'PQR' , 'TAB_C' , 'COL2:A, COL4:A' FROM sysibm.sysdummy1
    )
    /*************************************************
    **********   End of sample data.        **********
    *************************************************/
    SELECT * FROM index_col_list
     ORDER BY LEFT(INDEX_NAME, 4) , index_schema;
    ------------------------------------------------------------------------------
    
    INDEX_SCHEMA INDEX_NAME TABLE_SCHEMA TABLE_NAME INDEX_COLUMNS         
    ------------ ---------- ------------ ---------- ----------------------
    PQR          IX_A       PQR          TAB_A      COL1:A, COL2:A        
    UVW          IX_A       UVW          TAB_A      COL1:A, COL2:A        
    XYZ          IX_A       XYZ          TAB_A      COL1:A, COL2:A        
    PQR          IX_B       PQR          TAB_A      COL2:A, COL4:D        
    XYZ          IX_B       XYZ          TAB_A      COL2:A, COL4:D        
    UVW          IX_C       UVW          TAB_A      COL1:A, COL2:A, COL3:A
    PQR          IX_D       PQR          TAB_B      COL1:A, COL2:A        
    UVW          IX_D2      UVW          TAB_B      COL1:A, COL2:A        
    XYZ          IX_D       XYZ          TAB_B      COL1:A, COL2:A        
    PQR          IX_E       PQR          TAB_C      COL2:A, COL4:A        
    UVW          IX_E1      UVW          TAB_C      COL2:A, COL4:A        
    
      11 record(s) selected.
    Result:

    Code:
    TABLE_NAME INDEX_COLUMNS          <schema-name>.<index-name>                        
    ---------- ---------------------- --------------------------------------------------
    TAB_A      COL1:A, COL2:A, COL3:A  ---  , UVW.IX_C,  ---                            
    TAB_A      COL2:A, COL4:D         PQR.IX_B,  ---  , XYZ.IX_B                        
    TAB_B      COL1:A, COL2:A         PQR.IX_D, UVW.IX_D2, XYZ.IX_D                     
    TAB_C      COL2:A, COL4:A         PQR.IX_E, UVW.IX_E1,  ---                         
    
      4 record(s) selected.

    Query example 1-1:

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /*************************************************
    ********** Begin of sample data.        **********
    *************************************************/
     index_col_list
    ( index_schema
    
    ...
    ...
    
    )
    /*************************************************
    **********   End of sample data.        **********
    *************************************************/
    SELECT table_name
         , index_columns
         , SUBSTR(
              XMLCAST(
                 XMLGROUP(
                       ', '
                    || CASE s_table_schema
                       WHEN p_table_schema THEN
                            p_table_schema || '.' || index_name
                       ELSE ' ---  '
                       END  AS s
                    ORDER BY p_table_schema
                 )
                 AS VARCHAR(50)
              )
            , 3
           ) AS "<schema-name>.<index-name>"
     FROM  (SELECT index_name
                 , table_name
                 , p.table_schema AS p_table_schema
                 , s.table_schema AS s_table_schema
                 , index_columns
                 , p.rn           AS p_rn
                 , ROW_NUMBER()
                     OVER(PARTITION BY table_name
                                     , index_columns
                              ORDER BY s.table_schema
                                     , p.table_schema
                         ) AS rn
             FROM  (SELECT table_schema
                         , ROW_NUMBER() OVER(ORDER BY table_schema ASC) AS rn
                     FROM  index_col_list
                     GROUP BY
                           table_schema
                   ) AS p
             LEFT OUTER JOIN
                   (SELECT t.*
                         , ROW_NUMBER()
                             OVER(PARTITION BY table_name
                                             , index_columns
                                      ORDER BY table_schema ASC
                                 ) AS rn_asc
                         , ROW_NUMBER()
                             OVER(PARTITION BY table_name
                                             , index_columns
                                      ORDER BY table_schema DESC
                                 ) AS rn_desc
                         , DENSE_RANK()
                             OVER(PARTITION BY table_name
                                             , index_columns
                                      ORDER BY index_name ASC
                                 ) AS dk_asc
                         , DENSE_RANK()
                             OVER(PARTITION BY table_name
                                             , index_columns
                                      ORDER BY index_name DESC
                                 ) AS dk_desc
                     FROM  index_col_list t
                   ) AS s
               ON  
                   s.dk_asc + s.dk_desc > 2
               AND s.table_schema =  p.table_schema
               OR  s.rn_asc + s.rn_desc < 4
               AND
                 (     s.table_schema >= p.table_schema
                   OR  s.rn_desc = 1
                   AND s.table_schema <= p.table_schema
                 )
           ) AS r
     WHERE p_table_schema >= s_table_schema
       OR  p_table_schema <  s_table_schema
       AND rn < p_rn * 2
     GROUP BY
           table_name
         , index_columns
    ;
    ------------------------------------------------------------------------------
    
    TABLE_NAME INDEX_COLUMNS          <schema-name>.<index-name>                        
    ---------- ---------------------- --------------------------------------------------
    TAB_A      COL1:A, COL2:A, COL3:A  ---  , UVW.IX_C,  ---                            
    TAB_A      COL2:A, COL4:D         PQR.IX_B,  ---  , XYZ.IX_B                        
    TAB_B      COL1:A, COL2:A         PQR.IX_D, UVW.IX_D2, XYZ.IX_D                     
    TAB_C      COL2:A, COL4:A         PQR.IX_E, UVW.IX_E1,  ---                         
    
      4 record(s) selected.
    Last edited by tonkuma; 05-10-11 at 00:24.

  8. #8
    Join Date
    Apr 2011
    Posts
    31

    Red face Sql guidance

    Hello Tonkuma,

    Thank you so much for your reply.

    I am altering your query and executing. It gives me an error :

    I am using iseries on a DB2 5.4

    SQL State: 42601
    Vendor Code: -199
    Message: [SQL0199] Keyword AS not expected. Valid tokens: ) ,. Cause . . . . . : The keyword AS was not expected here. A syntax error was detected at keyword AS. The partial list of valid tokens is ) ,. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

  9. #9
    Join Date
    Apr 2011
    Posts
    31

    Red face Sql guidance

    Hello Tonkuma,

    Thanks very much for your reply.

    1) Is there any workaround like creating temporary tables / views to achieve
    this instead of this complex query for which u have rightly mentioned

    " I doubt whather it is neccesary such complex logic and code.
    But, I have tired to review and improve the query. "

    If u think so please advice how to do please. 2 tables are involved here SYSINDEXES and SYSKEYS

    2) I am altering your query and executing. It gives me an error :

    I am using iseries on a DB2 5.4

    SQL State: 42601
    Vendor Code: -199
    Message: [SQL0199] Keyword AS not expected. Valid tokens: ) ,. Cause . . . . . : The keyword AS was not expected here. A syntax error was detected at keyword AS. The partial list of valid tokens is ) ,. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

    Thanks again.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I used OLAP specifications(ROW_NUMBER and DENSE_RANK) and XMLGROUP.
    They are not supported on DB2 5.4 for iSeries.
    That must be a reason of error.

    OLAP specifications: suppoted from DB2 6.1 for i.
    XMLGROUP: supported from DB2 7.1 for i.

    An idea of workaround is to use like a following joins...

    FROM (SELECT table_schema ... FROM index_col_list ...) AS p
    INNER JOIN ((SELECT index_columns ... FROM index_col_list ...) AS q
    LEFT OUTER JOIN index_col_list

    and recursive common table expression.
    Last edited by tonkuma; 05-09-11 at 08:34. Reason: Add recursive common table expression to an idea of workaround.

  11. #11
    Join Date
    Apr 2011
    Posts
    31

    Red face Sql guidance

    Tonkuma,

    Thanks for your generous and quick reply again.

    The second option of joins that you have shared with me looks fine.

    I am trying. However please share some more guidelines on this approach so that i can try.

    Please help.

    IF you think there are any other approaches or creating more than one tem[ table and

    getting output kindly guide.

    Thanks in anticipation.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I wrote
    As far as I understood, you need to take two steps.

    First, make a row with list of index columns for a index.
    Basically, there are two ways, recursive query(suggested by n_i) and string aggregate function(XMLGROUP or LISTAGG).
    You can find some examples of both technique on this forums.

    Second, compare list of index columns for a same named tables on different schema.
    I'm not interesting in the first step.
    Because, I thought that you can make index_col_list in the following example from SYSINDEXES and SYSKEYS
    by a simple application of recursive query(or common table expression).

    Here is another example of second step on DB2 for Windows that I tried to use common functionalities to DB2 5.4 for iSeries(as far as I understood).

    Example 1-2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /*************************************************
    ********** Begin of sample data.        **********
    *************************************************/
     index_col_list
    ( index_schema
    , index_name
    , table_schema
    , table_name
    , index_columns
    ) AS (
              SELECT 'XYZ' , 'IX_A'  , 'XYZ' , 'TAB_A' , 'COL1:A, COL2:A' FROM sysibm.sysdummy1
    UNION ALL SELECT 'UVW' , 'IX_A'  , 'UVW' , 'TAB_A' , 'COL1:A, COL2:A' FROM sysibm.sysdummy1
    UNION ALL SELECT 'PQR' , 'IX_A'  , 'PQR' , 'TAB_A' , 'COL1:A, COL2:A' FROM sysibm.sysdummy1
    
    UNION ALL SELECT 'XYZ' , 'IX_B'  , 'XYZ' , 'TAB_A' , 'COL2:A, COL4:D' FROM sysibm.sysdummy1
    UNION ALL SELECT 'PQR' , 'IX_B'  , 'PQR' , 'TAB_A' , 'COL2:A, COL4:D' FROM sysibm.sysdummy1
    
    UNION ALL SELECT 'UVW' , 'IX_C'  , 'UVW' , 'TAB_A' , 'COL1:A, COL2:A, COL3:A' FROM sysibm.sysdummy1
    
    UNION ALL SELECT 'XYZ' , 'IX_D'  , 'XYZ' , 'TAB_B' , 'COL1:A, COL2:A' FROM sysibm.sysdummy1
    UNION ALL SELECT 'UVW' , 'IX_D2' , 'UVW' , 'TAB_B' , 'COL1:A, COL2:A' FROM sysibm.sysdummy1
    UNION ALL SELECT 'PQR' , 'IX_D'  , 'PQR' , 'TAB_B' , 'COL1:A, COL2:A' FROM sysibm.sysdummy1
    
    UNION ALL SELECT 'UVW' , 'IX_E1' , 'UVW' , 'TAB_C' , 'COL2:A, COL4:A' FROM sysibm.sysdummy1
    UNION ALL SELECT 'PQR' , 'IX_E'  , 'PQR' , 'TAB_C' , 'COL2:A, COL4:A' FROM sysibm.sysdummy1
    )
    /*************************************************
    **********   End of sample data.        **********
    *************************************************/
    , matrix_schema_vs_index AS (
    SELECT p.table_schema  AS schema
           /* I assumed that index_schema is same as table_schema. */
         , p.rn
         , q.table_name
         , q.index_columns
         , r.index_name
     FROM  (SELECT DISTINCT
                   table_schema
                 , (SELECT COUNT(DISTINCT table_schema)
                     FROM  index_col_list b
                     WHERE b.table_schema <= a.table_schema
                   ) AS rn
             FROM  index_col_list a
           )              AS p
     CROSS JOIN
           (SELECT DISTINCT
                   table_name
                 , index_columns
             FROM  index_col_list
           )              AS q
     LEFT OUTER JOIN
           index_col_list AS r
       ON  r.table_schema  = p.table_schema
       AND r.table_name    = q.table_name
       AND r.index_columns = q.index_columns
    )
    , recursive_cte
    ( table_name
    , index_columns
    , index_list
    , rn
    ) AS (
    SELECT table_name
         , index_columns
         , CAST('' AS VARCHAR(50) )
         , 1
     FROM  matrix_schema_vs_index
     GROUP BY
           table_name , index_columns
     HAVING
           COUNT(index_name)
           < (SELECT MAX(rn)
               FROM  matrix_schema_vs_index
             )
       OR  MIN(index_name) < MAX(index_name)
    UNION ALL
    SELECT pre.table_name
         , pre.index_columns
         , pre.index_list
           || ', ' || COALESCE(new.schema || '.' || new.index_name , ' ---  ')
         , pre.rn + 1
     FROM  recursive_cte          pre
         , matrix_schema_vs_index new
     WHERE new.table_name    = pre.table_name
       AND new.index_columns = pre.index_columns
       AND new.rn            = pre.rn
       AND pre.rn < 100000
    )
    SELECT table_name
         , index_columns
         , SUBSTR(index_list , 3) AS "<schema-name>.<index-name>"
     FROM  recursive_cte
     WHERE rn
           = (SELECT MAX(rn) + 1
               FROM  matrix_schema_vs_index
             )
     ORDER BY
           table_name
         , index_columns
    ;
    ------------------------------------------------------------------------------
    
    TABLE_NAME INDEX_COLUMNS          <schema-name>.<index-name>                        
    ---------- ---------------------- --------------------------------------------------
    TAB_A      COL1:A, COL2:A, COL3:A  ---  , UVW.IX_C,  ---                            
    TAB_A      COL2:A, COL4:D         PQR.IX_B,  ---  , XYZ.IX_B                        
    TAB_B      COL1:A, COL2:A         PQR.IX_D, UVW.IX_D2, XYZ.IX_D                     
    TAB_C      COL2:A, COL4:A         PQR.IX_E, UVW.IX_E1,  ---                         
    
      4 record(s) selected.
    Last edited by tonkuma; 05-10-11 at 00:23. Reason: Replace GROUP BY by DISTINCT in subquery p in matrix_schema_vs_index. Add blanks to '--- '.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another two examples without using recursive common table expression.
    You might prefer output format of second example.

    Example 2-1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /*************************************************
    ********** Begin of sample data.        **********
    *************************************************/
     index_col_list
    ...
    ...
    )
    /*************************************************
    **********   End of sample data.        **********
    *************************************************/
    , index_list_added_rn AS (
    SELECT table_schema  AS schema
         , (SELECT COUNT(DISTINCT table_schema)
             FROM  index_col_list b
             WHERE b.table_schema <= a.table_schema
           ) AS rn
         , table_name
         , index_columns
         , index_name
     FROM  index_col_list a
    )
    SELECT CASE rn
           WHEN min_rn THEN
                h.table_name
           ELSE ''
           END AS table_name
         , CASE rn
           WHEN min_rn THEN
                h.index_columns
           ELSE ''
           END AS index_columns
         , schema
         , CASE 
           WHEN different = '*'
            OR  rn = min_rn     THEN
                index_name
           ELSE ''
           END  AS index_name
         , CASE rn
           WHEN min_rn THEN
                CASE absent
                WHEN '*' THEN
                     'absent in some schema; '
                ELSE ''
                END
                ||
                CASE different
                WHEN '*' THEN
                     'different index names;'
                ELSE ''
                END
           ELSE ''
           END  AS anomaries
     FROM  (SELECT table_name
                 , index_columns
                 , CASE
                   WHEN COUNT(index_name)
                        < (SELECT MAX(rn)
                            FROM  matrix_schema_vs_index
                          )
                   THEN '*'
                   ELSE ' '
                   END  AS absent
                 , CASE
                   WHEN MIN(index_name) < MAX(index_name)
                   THEN '*'
                   ELSE ' '
                   END  AS different
                 , MIN(CASE
                       WHEN index_name IS NOT NULL
                       THEN rn
                       END
                      ) AS min_rn
             FROM  matrix_schema_vs_index
             GROUP BY
                   table_name , index_columns
             HAVING
                   COUNT(index_name)
                   < (SELECT MAX(rn)
                       FROM  matrix_schema_vs_index
                     )
               OR  MIN(index_name) < MAX(index_name)
           ) AS h
     INNER JOIN
           matrix_schema_vs_index AS d
       ON  d.table_name    = h.table_name
       AND d.index_columns = h.index_columns
       AND d.index_name IS NOT NULL
     ORDER BY
           h.table_name
         , h.index_columns
         , rn
    ;
    ------------------------------------------------------------------------------
    
    TABLE_NAME INDEX_COLUMNS          SCHEMA INDEX_NAME ANOMARIES
    ---------- ---------------------- ------ ---------- ---------------------------------------------
    TAB_A      COL1:A, COL2:A, COL3:A UVW    IX_C       absent in some schema;
    TAB_A      COL2:A, COL4:D         PQR    IX_B       absent in some schema;
                                      XYZ
    TAB_B      COL1:A, COL2:A         PQR    IX_D       different index names;
                                      UVW    IX_D2
                                      XYZ    IX_D
    TAB_C      COL2:A, COL4:A         PQR    IX_E       absent in some schema; different index names;
                                      UVW    IX_E1
    
      8 record(s) selected.

    Example 2-2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /*************************************************
    ********** Begin of sample data.        **********
    *************************************************/
     index_col_list
    ...
    ...
    )
    /*************************************************
    **********   End of sample data.        **********
    *************************************************/
    , index_list_added_rn AS (
    SELECT table_schema  AS schema
         , (SELECT COUNT(DISTINCT table_schema)
             FROM  index_col_list b
             WHERE b.table_schema <= a.table_schema
           ) AS rn
         , table_name
         , index_columns
         , index_name
     FROM  index_col_list a
    )
    SELECT CASE group
           WHEN 0 THEN
                h.table_name
           ELSE d.schema
           END AS table_or_schema
         , CASE 
           WHEN group = 0
            AND different = ' '
            OR  group = 1
            AND different = '*' THEN
                d.index_name
           ELSE ''
           END AS index_name
         , CASE group
           WHEN 0 THEN
                h.index_columns
           ELSE ''
           END AS index_columns
         , CASE group
           WHEN 0 THEN
                CASE absent
                WHEN '*' THEN
                     'absent in some schema; '
                ELSE ''
                END
                ||
                CASE different
                WHEN '*' THEN
                     'different index names;'
                ELSE ''
                END
           ELSE ''
           END  AS anomaries
     FROM  (SELECT table_name
                 , index_columns
                 , CASE
                   WHEN COUNT(index_name)
                        < (SELECT MAX(rn)
                            FROM  index_list_added_rn
                          )
                   THEN '*'
                   ELSE ' '
                   END  AS absent
                 , CASE
                   WHEN MIN(index_name) < MAX(index_name)
                   THEN '*'
                   ELSE ' '
                   END  AS different
                 , MIN(CASE
                       WHEN index_name IS NOT NULL
                       THEN rn
                       END
                      ) AS min_rn
             FROM  index_list_added_rn
             GROUP BY
                   table_name , index_columns
             HAVING
                   COUNT(index_name)
                   < (SELECT MAX(rn)
                       FROM  index_list_added_rn
                     )
               OR  MIN(index_name) < MAX(index_name)
           ) AS h
     CROSS JOIN
           (          SELECT 0 FROM sysibm.sysdummy1
            UNION ALL SELECT 1 FROM sysibm.sysdummy1
           ) AS g(group)
     INNER JOIN
           index_list_added_rn AS d
       ON  d.table_name    = h.table_name
       AND d.index_columns = h.index_columns
       AND d.index_name IS NOT NULL
       AND
       (   group = 0 AND rn = min_rn
        OR group = 1
       )
     ORDER BY
           h.table_name
         , h.index_columns
         , group
         , rn
    ;
    ------------------------------------------------------------------------------
    
    TABLE_OR_SCHEMA INDEX_NAME INDEX_COLUMNS          ANOMARIES 
    --------------- ---------- ---------------------- ---------------------------------------------
    TAB_A           IX_C       COL1:A, COL2:A, COL3:A absent in some schema;
    UVW
    TAB_A           IX_B       COL2:A, COL4:D         absent in some schema;
    PQR
    XYZ
    TAB_B                      COL1:A, COL2:A         different index names;
    PQR             IX_D
    UVW             IX_D2
    XYZ             IX_D
    TAB_C                      COL2:A, COL4:A         absent in some schema; different index names;
    PQR             IX_E
    UVW             IX_E1
    
      12 record(s) selected.
    Last edited by tonkuma; 05-10-11 at 06:10. Reason: Remove all joins and subqueries from matrix_schema_vs_index, and renamed it to index_list_added_rn.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to use LEFT OUTER JOIN in second subselect of recursive common table expression.
    But, I coudn't do so.

    Hore is an alternative way.

    Example 1-3:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /*************************************************
    ********** Begin of sample data.        **********
    *************************************************/
     index_col_list
    ...
    ...
    )
    /*************************************************
    **********   End of sample data.        **********
    *************************************************/
    , index_list_added_rn AS (
    SELECT table_schema  AS schema
         , (SELECT COUNT(DISTINCT table_schema)
             FROM  index_col_list b
             WHERE b.table_schema <= a.table_schema
           ) AS rn
         , (SELECT MIN(table_schema)
             FROM  index_col_list b
             WHERE b.table_name    = a.table_name
               AND b.index_columns = a.index_columns
           ) AS min_schema
         , table_name
         , index_columns
         , index_name
     FROM  index_col_list a
    )
    , recursive_cte
    ( table_name
    , index_columns
    , index_list
    , rn
    ) AS (
    SELECT table_name
         , index_columns
         , CAST('' AS VARCHAR(50) )
         , 1
     FROM  index_col_list
     GROUP BY
           table_name
         , index_columns
     HAVING
           COUNT(*)
           < (SELECT COUNT(DISTINCT table_schema)
               FROM  index_col_list
             )
       OR  MIN(index_name) < MAX(index_name)
    UNION ALL
    SELECT pre.table_name
         , pre.index_columns
         , pre.index_list
           || ', '
           || CASE new.rn
              WHEN pre.rn THEN
                   new.schema || '.' || new.index_name
              ELSE ' ---  '
              END
         , pre.rn + 1
     FROM  recursive_cte       pre
         , index_list_added_rn new
     WHERE new.table_name    = pre.table_name
       AND new.index_columns = pre.index_columns
       AND pre.rn <  100000
       AND pre.rn <= (SELECT MAX(rn)
                       FROM  index_list_added_rn
                     )
       AND
         (     new.rn        = pre.rn
           OR  NOT EXISTS
               (SELECT 0
                 FROM  index_list_added_rn nex
                 WHERE nex.table_name    = pre.table_name
                   AND nex.index_columns = pre.index_columns
                   AND nex.rn            = pre.rn
               )
           AND new.schema = new.min_schema
         )
    )
    SELECT table_name
         , index_columns
         , SUBSTR(index_list , 3) AS "<schema-name>.<index-name>"
     FROM  recursive_cte
     WHERE rn = (SELECT MAX(rn) + 1
                  FROM  index_list_added_rn
                )
    ;
    ------------------------------------------------------------------------------
    
    TABLE_NAME INDEX_COLUMNS          <schema-name>.<index-name>                        
    ---------- ---------------------- --------------------------------------------------
    TAB_A      COL1:A, COL2:A, COL3:A  ---  , UVW.IX_C,  ---                            
    TAB_A      COL2:A, COL4:D         PQR.IX_B,  ---  , XYZ.IX_B                        
    TAB_B      COL1:A, COL2:A         PQR.IX_D, UVW.IX_D2, XYZ.IX_D                     
    TAB_C      COL2:A, COL4:A         PQR.IX_E, UVW.IX_E1,  ---                         
    
      4 record(s) selected.

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Using same idea of example 3-1.

    Although, conditions to join "index_list_added_rn new" in second subquery of CTE was changed,
    same row as example 3-1 would be joined.

    Example 1-3':
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /*************************************************
    ********** Begin of sample data.        **********
    *************************************************/
     index_col_list
    ...
    ...
    )
    /*************************************************
    **********   End of sample data.        **********
    *************************************************/
    , index_list_added_rn AS (
    SELECT table_schema  AS schema
         , (SELECT COUNT(DISTINCT table_schema)
             FROM  index_col_list b
             WHERE b.table_schema <= a.table_schema
           ) AS rn
         , (SELECT MIN(table_schema)
             FROM  index_col_list b
             WHERE b.table_name    = a.table_name
               AND b.index_columns = a.index_columns
           ) AS min_schema
         , table_name
         , index_columns
         , index_name
     FROM  index_col_list a
    )
    , recursive_cte
    ( table_name
    , index_columns
    , index_list
    , rn
    ) AS (
    SELECT table_name
         , index_columns
         , CAST('' AS VARCHAR(50) )
         , 1
     FROM  index_col_list
     GROUP BY
           table_name
         , index_columns
     HAVING
           COUNT(*)
           < (SELECT COUNT(DISTINCT table_schema)
               FROM  index_col_list
             )
       OR  MIN(index_name) < MAX(index_name)
    UNION ALL
    SELECT pre.table_name
         , pre.index_columns
         , pre.index_list
           || ', '
           || CASE new.rn
              WHEN pre.rn THEN
                   new.schema || '.' || new.index_name
              ELSE ' ---  '
              END
         , pre.rn + 1
     FROM  recursive_cte       pre
         , index_list_added_rn new
     WHERE pre.rn < 100000
       AND EXISTS
           (SELECT 0
             FROM  index_list_added_rn AS max
             WHERE max.rn >= pre.rn
           )
       AND new.table_name    = pre.table_name
       AND new.index_columns = pre.index_columns
       AND new.rn
           = (SELECT MAX(rn)
               FROM  index_list_added_rn max
               WHERE max.table_name    = pre.table_name
                 AND max.index_columns = pre.index_columns
                 AND
                 (   max.rn     = pre.rn
                  OR max.schema = max.min_schema
                 )
             )
    )
    SELECT table_name
         , index_columns
         , SUBSTR(index_list , 3) AS "<schema-name>.<index-name>"
     FROM  recursive_cte
     WHERE rn = (SELECT MAX(rn) + 1
                  FROM  index_list_added_rn
                )
    ;
    ------------------------------------------------------------------------------
    
    TABLE_NAME INDEX_COLUMNS          <schema-name>.<index-name>                        
    ---------- ---------------------- --------------------------------------------------
    TAB_A      COL1:A, COL2:A, COL3:A  ---  , UVW.IX_C,  ---                            
    TAB_A      COL2:A, COL4:D         PQR.IX_B,  ---  , XYZ.IX_B                        
    TAB_B      COL1:A, COL2:A         PQR.IX_D, UVW.IX_D2, XYZ.IX_D                     
    TAB_C      COL2:A, COL4:A         PQR.IX_E, UVW.IX_E1,  ---                         
    
      4 record(s) selected.

Posting Permissions

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