| View previous topic :: View next topic |
| Author |
Message |
vmbigot
Beginner
Joined: 17 Jun 2004
Posts: 36
Topics: 14
Location: westminster, california
|
 Posted: Wed Mar 07, 2007 12:08 pm Post subject: tablespace compression weirdness |
 |
|
We have a tablespace that we needed to turn compression on. This tablespace was allocated as follows:
PRIQTY 140000
SECQTY 4000
This allocation created a tablespace size of 1,227 cylinders for the primary and 6 cylinders for the secondary. The tablespace used only one extent. Without changing the allocation but turning compression on, when we reloaded the tablespace the cylinder allocation changed to 195 for the primary and stayed at 6 cylinders for the secondary. The number of extents changed from 1 extent to 47 extents! I don't believe that this is normal.
This tablespace (and others) were created in version 7 and compressed in version 8. Is there a difference in how DB2 allocated tablespaces between the two versions?
_________________
Inside every older man is a young boy asking this question. What the heck happened?!? |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Wed Mar 07, 2007 12:34 pm Post subject: |
|
|
vmbigot,
| Quote: |
This tablespace (and others) were created in version 7 and compressed in version 8. Is there a difference in how DB2 allocated tablespaces between the two versions?
|
I don't think it is abnormal. I am guessing that there not enough free candidate volumes to allocate the entire primary quantity when you re-created the tablespace. When you don't get the full primary allocation, then you automatically will use more secondary allocations.
Also before compressing data, did you run the DSN1COMP stand-alone utility to estimate how well it will compress ?
After data is compressed, use compression reports and catalog statistics to determine how effectively it was compressed.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Wed Mar 07, 2007 1:15 pm Post subject: |
|
|
kolusu, The tablespaces in question was altered to 'COMPRESS YES' then a "UNLOAD' 'RELOAD' was run to compress the data. The compression ran GREAT! The problem is the allocations changed from prior the compression to after the compression. The listcat before and after are drasticially different.
| Code: |
DSNU231I -DB2T DSNURBDC - DICTIONARY WITH 4096 ENTRIES HAS BEEN SUCCESSFULLY
FDBHRS.FTSHRTRH
DSNU234I -DB2T DSNURWT - COMPRESSION REPORT FOR TABLE SPACE FDBHRS.FTSHRTRH
777488 KB WITHOUT COMPRESSION
306762 KB WITH COMPRESSION
60 PERCENT OF THE BYTES SAVED FROM COMPRESSED DATA ROWS
99 PERCENT OF THE LOADED ROWS WERE COMPRESSED
177 BYTES FOR AVERAGE UNCOMPRESSED ROW LENGTH
72 BYTES FOR AVERAGE COMPRESSED ROW LENGTH
208116 PAGES REQUIRED WITHOUT COMPRESSION
84641 PAGES REQUIRED WITH COMPRESSION
59 PERCENT OF THE DB2 DATA PAGES SAVED USING COMPRESSED DATA |
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
videlord
Beginner
Joined: 09 Dec 2004
Posts: 147
Topics: 19
|
| Posted: Thu Mar 08, 2007 5:52 pm Post subject: |
|
|
If your PRIQTY is 140000, then first allocation is 195 cyls.
1cyl = 15 trks, 1 trk = about 48K (in DB2)
So after LOAD, the data sets will redefine with the PRIQTY
I think the original PRIQTY is not 140000.
By the way, using REORG to turn on compression is better then LOAD. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
