IBM Mainframe Forum

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.

Select all

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

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?

Posted: **Thu Mar 28, 2013 10:19 am**

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.

Select all

SELECT
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,
pm

Code'd

Posted: **Thu Mar 28, 2013 12:58 pm**

Please take the time to Code your posts where spacing needs to be preserved, and use the Preview button to see what it will look like, and adjust if necessary.

Posted: **Thu Mar 28, 2013 7:52 pm**

Thanks Billy! I was unaware of how the 'preview' worked. I have an additional question. Is there any way to send you a private message?

Posted: **Thu Mar 28, 2013 7:59 pm**

just click on the <user> name and follow the flow

Posted: **Thu Mar 28, 2013 9:41 pm**

Hello,

Or click on the PM icon on the right side of a previous post.

Posted: **Thu Mar 28, 2013 10:02 pm**

Thank you Gentlemen, both were successful.
I hope I did not overstep my bounds but I created a post because I could not stand to see the number 666 on my screen.
My apologies if this caused any offense to anyone but I could not look at it any longer. Especially during Holy Week.

Thank you for your patience and have a Happy Easter,
PM

Posted: **Tue Apr 02, 2013 4:03 am**

PM, thanks for your response.

I am assuming that you are a DBA because ...

I am a developer working in a test environment.

IBM Mainframe Forum

getting the first index of a table

getting the first index of a table

L

Re: getting the first index of a table

Re: getting the first index of a table

Re: getting the first index of a table

Re: getting the first index of a table

Re: getting the first index of a table

Re: getting the first index of a table