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
