I am assuming that you are a DBA because you should not be running ANY queries against the Catalog Tables.
In addition even if you are a DBA you should be very careful running queries against those tables.
Remember, even a IS or S lock can postpone a X lock. That can be a very 'bad' thing and after reviewing your
SQL, table scans were going to be required creating a potential lock problem.
Your SQL did not match your question. I believe your question was
'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 have modified your SQL to;
remove the need for table scans and promoted the usage of indexes
removed duplicate elements in the result set
reordered the table list for an easier 'read'
added required element to the result set
This SQL is meant to get you started in the right direction and is organized so you can move
it to QMF in the event you want to create a report.
T.DBNAME AS DBNAME, <<== name of the database from systables
T.TSNAME, <<== name of the tablespace from systables
T.NAME AS MYTBNAME, <<== name of the table from systables
X.NAME AS MYIXNAME, <<== name of the index from sysindexes
X.CREATEDTS AS MYTS <<== when the index was created
FROM SYSIBM.SYSTABLESPACE M, **
SYSIBM.SYSTABLES T, ** ** reordered in parent/child order
SYSIBM.SYSINDEXES X **
WHERE X.TBNAME = T.NAME
AND X.DBNAME = M.DBNAME
AND M.DBNAME = T.DBNAME
AND M.NAME = T.TSNAME
AND M.TSNAME LIKE 'WHATEVERTHENAMEIS%' <<== this predicate forces the use of the index
AND T.TYPE = 'T'
ORDER BY T.DBNAME, T.TSNAME, T.NAME, X.NAME
Best of luck,