NPSI.

Tejas Jadhav

NPSI.
All -

Please may i request you to share with me a query to DB2 Catalog to find
out 'NPSI' existing on a tablespace other than Partitioned indexes.


Thanks in advance
TJ

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Peter Vanroose

Re: NPSI.
(in response to Tejas Jadhav)
This query will give all NPSIs:

SELECT rtrim(ixcreator) || '.' || ixname
FROM SYSIBM.SYSINDEXPART
WHERE partition = 0

This also gives the indexes on non-partitioned tables, of course.
In order to just see indexes on tables in partitioned tablespaces, add

AND (IXNAME, IXCREATOR) IN (
SELECT NAME, CREATOR
FROM SYSIBM.SYSINDEXES
WHERE (TBNAME, TBCREATOR) IN (
SELECT NAME, CREATOR
FROM SYSIBM.SYSTABLES
WHERE (DBNAME, TSNAME) IN (
SELECT DBNAME, NAME
FROM SYSIBM.SYSTABLESPACE
WHERE PARTITIONS > 0
)
)
)

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Tejas Jadhav

Re: NPSI.
(in response to Peter Vanroose)
Hi Pvanroose -

Many thanks for providing the query.


Cheers !
Tejas J

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L