Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Sep 2006
    Posts
    13

    Unanswered: select all index information

    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

  2. #2
    Join Date
    Jun 2006
    Posts
    471

    index

    sysibm.sysindexes should give all information possible about indexes
    joins with other tables : systables-syscolumns could help for additional info
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Sep 2006
    Posts
    13
    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"
    Last edited by mjschwneger; 01-29-07 at 12:14.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Well, what have you tried so far and what were the results?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Sep 2006
    Posts
    13
    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"
    Last edited by mjschwneger; 01-29-07 at 13:46.

  6. #6
    Join Date
    Jun 2006
    Posts
    471

    index

    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 ?
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Code:
    SELECT i.indschema, i.indname, i.tabschema, i.tabname, c.colname
    FROM   syscat.indexes AS i JOIN syscat.columns AS c
              ON i.tabschema = c.tabschema AND i.tabname = c.tabname
    ORDER BY i.indschema, i.indname, c.colno
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Sep 2006
    Posts
    13
    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

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.

    Code:
    CREATE VIEW indexes AS
       SELECT creator AS indschema,
              name AS indname,
              tbcreator AS tabschema,
              tbname AS tabname
       FROM sysibm.sysindexes );
    
    CREATE VIEW indexcoluse AS
       ( SELECT ixcreator AS indschema, ixname AS indname,
                colname, colseq
         FROM sysibm.syskeys )
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Sep 2006
    Posts
    13
    Quote Originally Posted by stolze
    Code:
    SELECT i.indschema, i.indname, i.tabschema, i.tabname, c.colname
    FROM   syscat.indexes AS i JOIN syscat.columns AS c
              ON i.tabschema = c.tabschema AND i.tabname = c.tabname
    ORDER BY i.indschema, i.indname, c.colno
    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

  11. #11
    Join Date
    Sep 2006
    Posts
    13
    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

  12. #12
    Join Date
    Sep 2006
    Posts
    13

    getting errors

    Quote Originally Posted by stolze
    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".)
    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

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by mjschwneger
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Sep 2006
    Posts
    13
    Quote Originally Posted by Marcus_A
    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

  15. #15
    Join Date
    Sep 2006
    Posts
    13
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •