getting the first index of a table



IBM's flagship relational database management system

getting the first index of a table

Postby Pedro » 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                                       
   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?
Pedro Vera
User avatar
Pedro
 
Posts: 684
Joined: Thu Jul 31, 2008 9:59 pm
Location: Silicon Valley
Has thanked: 0 time
Been thanked: 53 times

L

Postby pmartyn » 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 
       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
pmartyn
 
Posts: 42
Joined: Thu Feb 28, 2013 7:11 pm
Has thanked: 5 times
Been thanked: 3 times

Re: getting the first index of a table

Postby BillyBoyo » 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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: getting the first index of a table

Postby pmartyn » 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?
pmartyn
 
Posts: 42
Joined: Thu Feb 28, 2013 7:11 pm
Has thanked: 5 times
Been thanked: 3 times

Re: getting the first index of a table

Postby enrico-sorichetti » Thu Mar 28, 2013 7:59 pm

just click on the <user> name and follow the flow ;)
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: getting the first index of a table

Postby dick scherrer » Thu Mar 28, 2013 9:41 pm

Hello,

Or click on the PM icon on the right side of a previous post.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: getting the first index of a table

Postby pmartyn » 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
pmartyn
 
Posts: 42
Joined: Thu Feb 28, 2013 7:11 pm
Has thanked: 5 times
Been thanked: 3 times

Re: getting the first index of a table

Postby Pedro » 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.
Pedro Vera
User avatar
Pedro
 
Posts: 684
Joined: Thu Jul 31, 2008 9:59 pm
Location: Silicon Valley
Has thanked: 0 time
Been thanked: 53 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post