If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > V9.7 How can I compress SYSIBM.SQLxxx indexes?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 04-28-11, 06:00
dr_te_z dr_te_z is offline
Registered User
 
Join Date: Jan 2009
Location: Zoetermeer, Holland
Posts: 555
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?
Reply With Quote
  #2 (permalink)  
Old 04-28-11, 14:31
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,198
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
Reply With Quote
  #3 (permalink)  
Old 04-29-11, 01:30
dr_te_z dr_te_z is offline
Registered User
 
Join Date: Jan 2009
Location: Zoetermeer, Holland
Posts: 555
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On