 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / November 2004pctfree, pctused & data page
Why does DB2 allow us to define the PCTFREE & MINPCTUSED for the index pages, but not data pages when we try to create a regular table? > Why does DB2 allow us to define the PCTFREE & MINPCTUSED for the index > pages, but not data pages when we try to create a regular table? There is a PCTFREE parameter for tables, although I believe you can only modify it with ALTER TABLE, not CREATE TABLE. The default for PCTFREE is -1 (i.e. 0% free space per page). > > Why does DB2 allow us to define the PCTFREE & MINPCTUSED for the index > > pages, but not data pages when we try to create a regular table? > > There is a PCTFREE parameter for tables, although I believe you can only > modify it with ALTER TABLE, not CREATE TABLE. The default for PCTFREE > is -1 (i.e. 0% free space per page). In Version 8.1, there is no alter index. The default percent free is 10 is -1 is specified. Not sure about version 8.2. MINPCTUSED is a type of on-line reorg for indexes. If the number is set high enough, DB2 will combine split pages that have the specified amount of empty space on them (1 - MINPCTUSED). Read the manual for a more complete description of this. There is no alter index in DB2 8.2 for LUW either. However, there has always been alter index for DB2 for OS/390 and z/OS. Thanks, Mark. > There is no alter index in DB2 8.2 for LUW either. However, there has always > been alter index for DB2 for OS/390 and z/OS. Ian, you're right. Now I remembered it. Thanks, FRX > > Why does DB2 allow us to define the PCTFREE & MINPCTUSED for the index > > pages, but not data pages when we try to create a regular table? > > There is a PCTFREE parameter for tables, although I believe you can only > modify it with ALTER TABLE, not CREATE TABLE. The default for PCTFREE > is -1 (i.e. 0% free space per page). Because the parm. can be used at index level but you couyld not use MINPCTUSED at the table level. That function dynamically reclaims the empty page as index pointers are moved from one page to the other. One can't do that with data pages as all index pointers would then have to be updated. Reclaim page4, it then becomes page three and all rids in all indexes beyond page have to be updated!!!!! Now as to PCTFREE it can be defined at create, alter time for tables and only at create time for indexes. HTH, Pierre. > Why does DB2 allow us to define the PCTFREE & MINPCTUSED for the index > pages, but not data pages when we try to create a regular table?  SignaturePierre Saint-Jacques - Reply to: sescons at attglobal dot net IBM DB2 Cerified Solutions Expert - Administration SES Consultants Inc. Thanks, Pierre, I think I was not totally get "dynamically reclaims the empty page" at the beginning. Then I recalled - setup this param suppose to enable "online index defragment". Since we can do Online Index Reorganization which introduced in V8. We are not so interesting in this param so much. This dose make sense. > Because the parm. can be used at index level but you couyld not use > MINPCTUSED at the table level. [quoted text clipped - 11 lines] > > Why does DB2 allow us to define the PCTFREE & MINPCTUSED for the index > > pages, but not data pages when we try to create a regular table? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.