List Indexes and IX Columns for a given table

If you ever wanted to see the indexes associated to a given table, the SYSIBM.SYSINDEXES catalog table is for you.

And if you want to see the columns that are indexed, this information is stored in yet another catalog table, SYSIBM.SYSKEYS, but this catalog table does not contain a direct relation to the table.

So, if you want to see all related Indexes and IX Columns for a given table, you must do a JOIN. So far so good, except that you end up with one row, for each columns of the indexes.

That is, if you have a table with 2 indexes, that reference respectively 3 and 5 columns of the table, you will get 8 rows.

   Table  Index  ColName  ColSort

   TB1    IX1    COL1     A

   TB1    IX1    COL2     D

   TB1    IX1    COL3     A

   TB1    IX2    COL1     A

   TB1    IX2    COL5     A

   TB1    IX2    COL2     A

   TB1    IX2    COL3     A

   TB1    IX2    COL7     A

I wanted to simplify the results to get only 2 rows (one per index), that will contain the list of columns, something like:

   Table  Index  Columns

   TB1    IX1    COL1,A,COL2,D,COL3,A

   TB1    IX2    COL1,A,COL5,A,COL2,A,COL3,A,COL7,A

Browsing the internet, I learnt that a special GROUP_CONCAT(expr) function can be used with the GROUP BY syntax, but it does not work with DB2 for z/OS.

Similar results could be archived programmatically, or using a UDF, or … using the new XML functions now available in DB2 for z/OS.

Since I never really played with XML functions, that was a good reason to start!

The following SQL, tested under DB2 version 10, provides the expected results. All the magic happens via the function XMLAGG. The rest (XML2CLOB, CAST, TRIM, or other REPLACEs) is just here to make the results “nicer”.

If you want to use it, just replace the table name (in red), and execute!

  SELECT I.CREATOR, I.TBNAME, I.CREATOR, I.NAME, I.UNIQUERULE,  

  TRIM(L ',' FROM REPLACE(REPLACE(CAST(XML2CLOB(                

  XMLAGG(XMLELEMENT(NAME "C", K.COLNAME, ',', K.ORDERING)       

       ORDER BY K.COLSEQ)                                     

  ) AS VARCHAR(2000)),'<C>',','),'</C>','')) AS "COLUMNS"       

  FROM  SYSIBM.SYSINDEXES I                                     

  INNER JOIN SYSIBM.SYSKEYS K                                   

  ON I.CREATOR = K.IXCREATOR AND I.NAME = K.IXNAME              

  WHERE I.TBNAME = 'PIN_ACCOUNT'                                

  GROUP BY I.TBCREATOR, I.TBNAME, I.CREATOR, I.NAME, I.UNIQUERULE

  ORDER BY I.TBCREATOR, I.TBNAME, I.CREATOR, I.NAME, I.UNIQUERULE ;

Sample output

  CREATOR   TBNAME       CREATOR   NAME      UNIQUERULE  COLUMNS

  $APEPCDB  PIN_ACCOUNT  $APEPCDB  XPEP0180  P           ACCTNUM,A,PIN_COMPANY_ID,A

  $APEPCDB  PIN_ACCOUNT  $APEPCDB  XPEP0280  D           BANK_NUMBER,A