Help With SQL

ajh · #1 (**permalink**) 05-05-11, 07:18

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.

n_i · #2 (**permalink**) 05-05-11, 07:39

convert the rows of data into columns in db2

tonkuma · #3 (**permalink**) 05-05-11, 08:14

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.
How to get change of IP in middle of session

ajh · #4 (**permalink**) 05-05-11, 08:49

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

tonkuma · #5 (**permalink**) 05-05-11, 12:29

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.

ajh · #6 (**permalink**) 05-06-11, 05:20

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.

tonkuma · #7 (**permalink**) 05-07-11, 17:07

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.

ajh · #8 (**permalink**) 05-09-11, 05:30

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.

ajh · #9 (**permalink**) 05-09-11, 06:53

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.

tonkuma · #10 (**permalink**) 05-09-11, 07:30

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.

ajh · #11 (**permalink**) 05-09-11, 07:58

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.

tonkuma · #12 (**permalink**) 05-09-11, 09:43

I wrote

Quote:

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.

tonkuma · #13 (**permalink**) 05-09-11, 18:46

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.

tonkuma · #14 (**permalink**) 05-10-11, 10:36

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.

tonkuma · #15 (**permalink**) 05-10-11, 23:13

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech