getting the first index of a table
Posted: Thu Mar 21, 2013 11:42 pm
Given a tablespace with two tables and each table having one or more indexes, I would like to know which index was created first for each table.
I am getting only the first index of a single table. How to get the first index of the second table that is in the tablespace?
SELECT
MIN( X.CREATEDTS) AS MYTS,
T.NAME AS MYTBNAME,
T.DBNAME AS DBNAME,
T.TSNAME,
M.NAME AS MYTSNAME
FROM SYSIBM.SYSINDEXES X,
SYSIBM.SYSTABLES T,
SYSIBM.SYSTABLESPACE M
WHERE X.TBNAME = T.NAME
AND X.DBNAME = M.DBNAME
AND M.DBNAME = T.DBNAME
AND M.NAME = T.TSNAME
AND M.DBNAME LIKE 'NLKDB5%'
AND T.TYPE = 'T'
GROUP BY T.TSNAME, T.DBNAME, T.NAME, M.NAME
MIN( X.CREATEDTS) AS MYTS,
T.NAME AS MYTBNAME,
T.DBNAME AS DBNAME,
T.TSNAME,
M.NAME AS MYTSNAME
FROM SYSIBM.SYSINDEXES X,
SYSIBM.SYSTABLES T,
SYSIBM.SYSTABLESPACE M
WHERE X.TBNAME = T.NAME
AND X.DBNAME = M.DBNAME
AND M.DBNAME = T.DBNAME
AND M.NAME = T.TSNAME
AND M.DBNAME LIKE 'NLKDB5%'
AND T.TYPE = 'T'
GROUP BY T.TSNAME, T.DBNAME, T.NAME, M.NAME
I am getting only the first index of a single table. How to get the first index of the second table that is in the tablespace?