I need a query to select table_name, index name, all columns in this index and their order in the index with the low possible security - as loggin as the user. Found some queries on the ibm/db2 manuals but they do not work for me.
Thanks a lot for the help.mj

sysibm.sysindexes should give all information possible about indexes
joins with other tables : systables-syscolumns could help for additional info

I'm sorry but I need more help. I have 2 different setups of db2.
One, where we create different databases, and the second - with db2 schemas.
The statement #1 below works good for me on db2 with schemas setup because I could point to the owner, but I need a separate statement (second below) to check on the same info where different database are created.

The application cannot know what kind of db2 database setup is at the moment of the connection. Is there a way to get the same info with single statement in both cases?
Also, is this statement work the same way on db2 v9?

Thanks a lot, mj

statement 1 - works on db2 with schema setup:
db2 "select indschema, indname, definer, tabschema, tabname, colnames from syscat.indexes where indschema = 'XXX' order by indname asc"

statement 2 - works on db2 where separate databases are created:
db2 "select indschema, indname, definer, tabschema, tabname, colnames from syscat.indexes order by indname asc"

Well, what have you tried so far and what were the results?

This is what I have tried but I need 1 statement for both cases... please, see my previous post.
I would also like to filter all system object out of the result set.
Thanks a lot,mj

statement 1 - works on db2 with schema setup:
db2 "select indschema, indname, definer, tabschema, tabname, colnames from syscat.indexes where indschema = 'XXX' order by indname asc"

statement 2 - works on db2 where separate databases are created:
db2 "select indschema, indname, definer, tabschema, tabname, colnames from syscat.indexes order by indname asc"

what do you mean by db2 schema
each object has a schema
why is it not possible to run the same query on both machine
where not indschema like 'SYS%' would filter
if dbname/sysname would be included in select, you could know where it does come from ?

I don't understand your question. Unless you are working on DB2 for z/OS, you will be connected to a single DB only and the DB2 catalog knows about tables/indexes is this DB only. So what do you mean with "different database" and querying across databases???

Besides, you still haven't given us any indication how your results should look like.

As for your query, it has a major problem: you use SYSCAT.INDEXES.COLNAMES. This is bound to have problems!! because (a) this column is deprecated and may/will go away in the future, and (b) it doesn't work with long and/or delimited column names. So please don't use it and refer to SYSCAT.INDEXCOLUSE instead. (The manual also states exactly that: http://publib.boulder.ibm.com/infoce...n/r0001047.htm)

Note: you usually don't need the DEFINER.

Thanks a lot for the help.
The result I need should look like this:
table_name, index_name, column_name, column_order
address, IX_address_key, address_id, 1
address, IX_address_key, address_type, 2

but I could not get this...
You are right for the DB2 for z/OS - this is my second setup (this is the right way to say it). The first is Db2 UDB 8.1. And I need a single statement for both cases if possible.

Thanks a lot again.
mj

The query I posted gives you pretty much what you want for UDB. You only have to add ", colseq" in the select-list. (Btw, the "colno" in my order-by-clause should have been "colseq".)

DB2 for z/OS uses differently named catalog tables. Thus, you cannot use the same query as-is without any additional work. There are two things you can do:

1. create a view that maps the schema on z/OS to the schema on UDB (or vice versa)
2. use different queries

I would probably go with the first approach because it reduces the complexity of your application in exchange for some additional administration work. You may have to do something similar on DB2 UDB because you can't create tables/views in schemas whose names begin with SYS.

For DB2 UDB, you create those views as simple "SELECT ... FROM syscat....". Now you can directly query your views with the statement I posted before.

This statement returns the all of the table columns not the indexed ones. It might have a wrong join. I get the index name and all columns from the table under colname.
Thanks, mj

The view creation is not appropriate for us. I have to go with using 2 different queries although this makes it more complex for the application.
Could you please help me at least to make the query the way which the application needs it - here is the example of what oputput I need. Is this possible? Instead of getting col1+clo2+clo3 to get the output below:
table_name, index_name, column_name, column_order
address, IX_address_key, address_id, 1
address, IX_address_key, address_type, 2

Thanks for the help.mj

I changed the colno with colseq and now I get the error below:
SQL0203N A reference to column "COLSEQ" is ambiguous. SQLSTATE=42702

Thanks,
mj

Maybe you first need to learn how to write SQL statements. If you access more than one table in a query, and the same column name appears in more than one table, then you need to qualify the column name.

If you follow up on the previous post, there is "c." qualifier before the column name.
Thanks, mj

I got it to work with one statement for the 2 cases - but just different connection information will be provided by the application depending on the setup.
Thanks a lot to this forum for the great help.mj