Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

    Unanswered: V9.7 How can I compress SYSIBM.SQLxxx indexes?

    I am preparing an upgrade from 9.5 to 9.7. So I made a script to alter all indexes to "compress yes" followed by a reorg table.
    As long as you defined the index yourself everything is okay, but generated indexes (the ones IBM named 'sysibm.SQL<timestamp>') cannot be altered.

    How can I compress those indexes as well?

    p.s. english is not my native language but long time ago at school they tried to teach me that the plural of 'index' is 'indices'. In the RDMS context I never see that word used. Can anyone explain why? Is it a U.K. vs U.S.A. thing?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If the base table is compressed, then all indexes will be compressed by default. Also, you can avoid DB2 creating indexes automatically for you by defining constraints outside of the create table statement.

    Regarding indexes vs indices, the term index in a relational database refers to a very specific tpye of database object (almost like a proper noun), and not the use of a general index.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Marcus_A View Post
    If the base table is compressed, then all indexes will be compressed by default.
    Yes, that will be the case when you define the table in v9.7.
    In my case I will be building a new server (win2008/db2v97) and restore the backup image (win2003/db2v95). Then the new database will be ready for use but the indexes will stil be uncompressed (I can see that when I punch the DDL with db2look). So I am working on "post restore" scripts to activate the 9.7 features.

    But maybe my conclusions were wrong. I thought that all "SYSIBM.SQLxxx" indexes gave errors in the alter statement. It can be that I saw a MDC-index. That one cannot be compressed.

Posting Permissions

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