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 ;
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