Get all the index information for a given schema —

问题:

I am writing a JDBC program to fetch some database meta data information and as part of that I want to query all the indexes that are there in a given schema. I had a look at some JDBC API and from DatabaseMetaData interface, can use methods like getTables to get all the tables for a given schema. I am trying to find something similar (or write using a combination of some API) to get information like all the indexes,views etc on a schema. Is there a way to get it? For ex, for index there is a method - getIndexInfo but for each of the table in a schema, I need to call this method. My database is db2.

回答1:

I would use the DB2 Catalog Views to get the information.

As an example, if you want all of the indexes for a table, you'd use a query like this (I'm assuming you're using DB2 on Linux/Unix/Windows here):

SELECT *
FROM SYSCAT.INDEXES
WHERE tabname   = @tablename 
  AND tabschema = @schema
ORDER BY indname

回答2:

I did the following after trying some approaches 1. Wrote a wrapper around JDBC calls to simplify my work. 2. Queried the syscat schema like

select tabname from syscat.TABLES where tabschema = ?

1. Wrote some java utilities to compare the 2 sets of results returned by the 2 schema and also did some manual comparison.

If I find a better solution, will post it. Thanks a lot for all the help.

回答3:

Getting data from Syscat schema is not correct. in ZOS environment Syscat may be or may not be present because while installation you have options not to install the Syscat schema. so better use Sysibm schema.