1020
DB2 的REORG_學習（1）_REORG INDEXES/TABLE Command
最近寫一個DB2的REORG的腳本，因為以前在mysql裏麵是沒有接觸過這個REORG的，所以係統了解一下，我的學習的話是先從命令入手，看看這個名的結構，然後還有選項，這些選項會揭示一些這個命令能幹什麼事，了解了命令，命令的選項之後再去看相關的理論，再結合理解一下應該就會比較清晰一些了。
所以先來看命令吧。
REORG INDEXES/TABLE Command
重新組織一個索引或一張表。
你可以通過 將索引數據重建到 碎片的，物理連續的頁上麵。在一個數據分區的表上，你可以重組一個特定的分區表上的非分區索引，或者，你可以重組某一個數據分區上的所有分區索引。
如果你聲明了index子句的CLEANUP選項，在不重建索引的情況下將會執行cleanup(*cleanup is performed without rebuilding the indexes*)。這個命令不能被用於聲明臨時表和創建臨時表的索引(*This command cannot be used against indexes on declared temporary tables or created temporary tables (SQLSTATE 42995)*)。
The table option reorganizes a table by reconstructing the rows to eliminate fragmented data, and by compacting information. On a partitioned table, you can reorganize a single partition.
table選項 通過重建行來剔除碎片數據 和壓縮信息來 重組一個表(*The table option reorganizes a table by reconstructing the rows to eliminate fragmented data, and by compacting information*)，在一個分區表上，你可以重組一個單一分區。
Scope範圍
這個命令影響在數據庫分區組中的所有的數據庫分區
Authorization權限
需要下列權限之一：
- SYSADM
- SYSCTRL
- SYSMAINT
- DBADM
- SQLADM
- CONTROL privilege on the table.
必要的連接信息Required connection
Database/數據庫
命令語法
>>-REORG-------------------------------------------------------->
>--+-TABLE--table-name--| Table clause |--------------------------------+-->
+-+-INDEXES ALL FOR TABLE--table-name------------+--| Index clause |-+
| '-INDEX--index-name--+-----------------------+-' |
| '-FOR TABLE--table-name-' |
| .-ALLOW WRITE ACCESS-. |
'-TABLE--table-name--RECLAIM EXTENTS--+--------------------+---------'
+-ALLOW READ ACCESS--+
'-ALLOW NO ACCESS----'
>--+-------------------------------+---------------------------->
'-| Table partitioning clause |-'
>--+-------------------------------+---------------------------><
'-| Database partition clause |-'
Table clause
|--+-------------------+---------------------------------------->
'-INDEX--index-name-'
.-KEEPDICTIONARY--.
>--+-+-------------------+--+-------------------+--+-----------+--+---------------------+--+-----------------+-+--|
| +-ALLOW NO ACCESS---+ '-USE--tbspace-name-' '-INDEXSCAN-' '-| longlob-options |-' '-RESETDICTIONARY-' |
| '-ALLOW READ ACCESS-' |
| .-ALLOW WRITE ACCESS-. .-START--. |
'-INPLACE--+-+--------------------+--+------------------+--+--------+-+-------------------------------------'
| '-ALLOW READ ACCESS--' '-NOTRUNCATE TABLE-' '-RESUME-' |
'-+-STOP--+------------------------------------------------'
'-PAUSE-'
longlob-options
|--LONGLOBDATA--+-----------------------+-----------------------|
'-USE--longtbspace-name-'
Index clause
.-REBUILD---------------.
|--+--------------------+--+-----------------------+------------|
+-ALLOW NO ACCESS----+ '-space-reclaim-options-'
+-ALLOW WRITE ACCESS-+
'-ALLOW READ ACCESS--'
space-reclaim-options
|--+--------------------+--+-----------------+------------------|
| .-ALL---. | '-RECLAIM EXTENTS-'
'-CLEANUP--+-------+-'
'-PAGES-'
Table partitioning clause
|--ON DATA PARTITION--partition-name----------------------------|
Database partition clause
|--ON----------------------------------------------------------->
>--+-+-DBPARTITIONNUM--+--| Partition selection clause |-------------------------------------+--|
| '-DBPARTITIONNUMS-' |
'-ALL DBPARTITIONNUMS--+----------------------------------------------------------------+-'
'-EXCEPT--+-DBPARTITIONNUM--+--| Partition selection clause |--)-'
'-DBPARTITIONNUMS-'
Partition selection clause
.-,--------------------------------------------------.
V |
|--(----db-partition-number1--+--------------------------+-+--)--|
'-TO--db-partition-number2-'
命令參數Command parameters
INDEXES ALL FOR TABLE table-name
指定要重組的索引所屬的表，這個表可以是位於本地數據庫，也可以是位於遠程數據庫。
INDEX index-name
指定一個位於數據分區表上的 要重組的獨立索引。 對於單一索引的重組僅支持在一個分區表上的非分區索引。這個參數不支持塊索引(*block indexes*).
FOR TABLE table-name
指定 非分區索引索引名是創建在 的那個表名稱。鑒於一個數據庫內的索引名稱是唯一的，這個參數是可選的。
重組索引的訪問方式選項
ALLOW NO ACCESS
- 對於重組**全部索引**，這個選項指定 在索引重建過程中 其他用戶不可以訪問表。如果對一個分區表指定了ON DATA PARTITION子句，則隻有指定非分區嚴格限製該訪問模式。
-
對於重組**單一索引**，這個選項指定 在非分區索引重組時， 索引所在的表不能被其他用戶訪問。
ALLOW READ ACCESS
對於重組 全部索引，這個選項指定 在索引重建過程中 其他用戶隻能對表進行隻讀訪問。ALLOW READ ACCESS模式不支持在一個分區表上 重組全部索引，除非指定了CLEANUP或RECLAIM EXTENTS選項，或者指定了ON DATA PARTITION 子句。如果對一個分區表指定了ON DATA PARTITION子句，則隻有指定非分區嚴格限製該訪問模式。
-
對於重組**單一索引**，這個選項指定 在非分區索引重組時， 索引所在的表僅允許用戶進行隻讀訪問。
ALLOW WRITE ACCESS
對於重組 全部索引，這個選項指定 在索引重組時，其他用戶可以對表進行讀寫訪問。ALLOW WRITE ACCESS模式不支持分區表，除非指定了CLEANUP或RECLAIM EXTENTS選項，或者指定了ON DATA PARTITION 子句。如果對一個分區表指定了ON DATA PARTITION子句，則隻有指定非分區嚴格限製該訪問模式。
對於重組**單一索引**，這個選項指定 在非分區索引重組過程中 可以讀寫表。
ALLOW WRITE ACCESS 模式不支持多維集群(* multidimensional clustering (MDC)*)或插入時間集群（ITC）表和擴展索引，除非指定了CLEANUP或RECLAIM EXTRNTS 選項。
下麵的條目適用於數據分區表 當REORG INDEXES ALL命令指定了ON DATA PARTITION子句時 的 數據分區表：
-
隻有指定數據分區嚴格限製訪問模式級別。在一個指定的分區的分區表索引被重組的過程中，表的其他分區允許用戶讀寫。
下麵的 表 列出當指定了ON DATA PARTITION子句時，表的其他分區上支持的和允許的一致性訪問。
表格1：當指定REORG INDEXES ALL 命令的ON DATA PARTITION 子句時 ，支持的 訪問模式和並發(*concurrent*)訪問允許|Access mode | 指定分區上的並發訪問允許|其他分區上的並發訪問允許|
|--|--|--|
| ALLOW NO ACCESS |不允許訪問 | 讀寫訪問|
| ALLOW READ ACCESS | 分區上可讀直到索引被更新| 讀寫訪問|
| ALLOW WRITE ACCESS | 分區上讀寫訪問直到索引被更新| 讀寫| 隻有 指定分區的 分區索引被重組。分區表上的不分區索引不會被重組。
如果在表上有被標記為“失效”或“用於重構（rebuild）”的非分區索引，這些索引會在重組之前rebuild。如果沒有，如果索引對象被標記為“失效”或“用於重構（rebuild）” 隻有指定分區上的分區索引被重組或重構 .-
當指定了 CLEANUP 或 RECLAIM EXTENTS選項時隻有指定分區的分區索引被cleaned 。
The following table lists the supported access modes for index reorganization of partitioned and nonpartitioned tables:
下麵的表格列示了分區表和非分區表的索引重組時支持的訪問模式。
表格2. 分區表和分區表上的索引重組支持的訪問模式
|命令|表類型|表分區子句 |索引子句指定的附加參數|支持的訪問模式|
|---|---|---|---|---|
|REORG INDEXES|非分區表 | 不適用
Not applicable |Any |ALLOW NO ACCESS,
ALLOW READ ACCESS1,
ALLOW WRITE ACCESS
|
|REORG INDEX |分區表|不適用
Not applicable| Any |ALLOW NO ACCESS,
ALLOW READ ACCESS1,
ALLOW WRITE ACCESS|
|REORG INDEXES |分區表|None| REBUILD (不指定的話
這是默認的) |ALLOW NO ACCESS 1|
|REORG INDEXES |分區表|ON DATA PARTITION |REBUILD (不指定的話
這是默認的) |ALLOW NO ACCESS,
ALLOW READ ACCESS1,
ALLOW WRITE ACCESS
|
|REORG INDEXES |分區表|有或沒有ON DATA
PARTITION 子句|指定CLEANUP 或
RECLAIM EXTENTS |ALLOW NO ACCESS,
ALLOW READ ACCESS1,
ALLOW WRITE ACCESS
|
注：標有上標1的是默認的訪問模式.
CLEANUP
當需要CLEANUP時，執行cleanup(清空)而不是REBUILD。索引不被重建，並且釋放的任何頁 僅可以被 定義在這個表上的索引重用。
ALL
指定索引 應該 通過提交 pseudo 刪除的鍵 和pseudo的 空頁 而打掃幹淨。
**CLEANUP ALL**選項將會釋放標記為(pseudo)空 的頁，也會從非空的pages中提交標記(pseudo)刪除的鍵。 如果合並可以 使得 合並後的葉子page上 至少有合並葉子頁的 PCTFREE 的空閑空間的話, 這個選項也會嚐試合並臨近的葉子頁, PCTFREE是 在索引創建時為索引定義的空閑空間百分比。默認的PCTFREE是10%。如果兩個頁可以合並，這兩個頁中的一個將被釋放。在一個索引中pseudo刪除的鍵（包含那些在pseudo空的頁上的鍵 ）的數目 可以通過運行RUNSTATS命令，然後select NUMRIDS DELETED from SYSCAT.INDEXES來獲得。ALL選項可以清空NUMRIDS DELETED 和 NUMEMPTY LEAFS ，在它們已經被確定要提交的情況下。
PAGES
這個選項指定 已提交的 pseudo空的頁 需要 從索引樹中移除。這將不會清空那些沒有被標記為空的頁上pseudo刪除的鍵。既然它隻檢查那些標記為空的葉子頁，那麼大多數情況下就會比使用ALL選項 相對要快。
**CLEANUP PAGES**選項會搜索並且釋放pseudo空的頁。一個已經提交的pseudo空的頁 是這樣一個頁，這個 頁上的所有的鍵都被標記為刪除，所有的這些刪除操作都是等待被提交的(* are known to be committed*). 一個索引中 pseudo 空的頁 可以通過運行RUNSTATS命令和審視 SYSCAT.INDEXES表中的NUM EMPTY LEAFS列來判定/裁定。 PAGES選項將會清掃 NUM EMPTY LEAFS 如果它們被 裁定為要提交的。
使用**ALLOW READ ACCESS** 或 ALLOW WRITE ACCESS 選項來允許 在索引被重組的過程中 其他事務 對表的隻讀或可讀寫訪問權限 。 在被重組的索引的拷貝 被可用之前 的這段時期內 重建索引 時 不允許訪問表(*No access to the table is allowed when rebuilding an index during the period in which the reorganized copies of the indexes are made available.*)。
INDEX index-name REBUILD
REBUILD選項是默認的，表現 在之前的版本中當沒有指定CLEANUP和CONVERT子句時由 索引重組 提供的 相同的功能。索引重組的REBUILD選項 將索引數據重構（rebuild）到一組**物理連續的頁**（* physically contiguous pages*）上。**默認的訪問模式基於表類型而不同**。
INDEX index-name RECLAIM EXTENTS
Specifies the index to reorganize and reclaim extents that are not being used. This action moves index pages around within the index object to create empty extents, and then free these empty extents from exclusive use by the index object and makes the space available for use by other database objects within the table space. Extents are reclaimed from the index object back to the table space. ALLOW READ ACCESS is the default, however all access modes are supported.
指定索引 去重組和回收沒有被使用的擴展數據塊。這個行為 在索引的頁之內移動索引對象來創建空的extents，然後釋放這些空的extents 從被索引對象使用的 專用空間（*exclusive*）中 釋放這些空的extents，使這些空間可以被這個表空間中的其他數據庫對象使用。extents從索引對象中回收到表空間中。默認的模式是ALLOW READ ACCESS，但是事實上可以支持所有的訪問模式。
TABLE table-name RECLAIM EXTENTS
Specifies the table to reorganize and reclaim extents that are not being used. The table-name variable must specify a multidimensional clustering (MDC), insert time clustering (ITC) table or column-organized tables. The name or alias in the form: schema.table-name can be used. The schema is the user name under which the table was created. If you omit the schema name, the default schema is assumed.
For REORG TABLE RECLAIM EXTENTS when the ON DATA PARTITION clause is specified, the access clause only applies to the named partition. Users can read from and write to the rest of the table while the extents on the specified partition are being reclaimed. This situation also applies to the default access levels.
ALLOW NO ACCESS
For REORG TABLE RECLAIM EXTENTS, specifies that no other users can access the table while the extents are being reclaimed.
ALLOW READ ACCESS
For REORG TABLE RECLAIM EXTENTS, specifies that other users can have read-only access to the table while the extents are being reclaimed.
ALLOW WRITE ACCESS
For REORG TABLE RECLAIM EXTENTS, specifies that other users can read from and write to the table while the extents are being reclaimed. This is the default option.
TABLE table-name
指定要重組的表。這個表可以位於本地或遠程數據庫。名稱或別名遵循以下形式：schema.table-name 的可以被使用。模式是表創建在哪個用戶名下。如果你忽略了模式名，將使用默認的模式名。
對typed表，指定的表名稱必須是繼承的根表的名稱。
你不能指定 為 多維集群（MDC）或插入時間集群（ITC）表指定一個索引。 因為 放置 表的重組 的地方不能被用於MDC或ITC（*In place reorganization of tables cannot be used for MDC or ITC tables*）。
當 為一個數據分組的表的表重組 指定ON DATA PARTITION子句時，隻有指定的數據分區被指定：
- 如果在表上沒有定義非分區索引（除了係統自動生成的XML路徑索引），訪問模式僅適用於指定的分區，用戶被允許讀寫表的其他表。
- 如果（除了係統自動生成的XML路徑索引）表上定義 了 非分區索引，默認使用ALLOW NO ACCESS訪問模式，並且僅支持這個訪問模式。在這種情況下，表被放在ALLOW NO ACCESS模式下。如果指定ALLOW READ ACCESS （允許讀訪問），將會返回SQL 1548N。
表格3：非分區表和分區表在表重組時支持的訪問模式
|命令|表類型|表分區子句|支持的訪問模式|
|--|---|---|--|
|REORG TABLE |非分區表| Not applicable | ALLOW NO ACCESS,
ALLOW READ ACCESS1| |REORG TABLE |分區表|Not specified |ALLOW NO ACCESS1| |REORG TABLE
(表上沒有索引或者是隻有分區索引)|分區表| ON DATA PARTITION |ALLOW NO ACCESS,
ALLOW READ ACCESS1| |REORG TABLE
(表上有除了係統生成的XML路徑索引之外的其他非分區索引)|分區表| ON DATA PARTITION|ALLOW NO ACCESS1|
注:
上標1：當訪問子句沒有指定時使用默認模式。
對於一個分區表，表重組 在重新組織表之後 重新建立 表上的 非分區索引和分區索引。如果ON DATA PARTITION 子句 被用來重新組織 數據分區表的一個特定的數據分區， 表重組 隻為特定的分區 重建非分區索引和分區索引
INDEX index-name
這個選項用來指定 當重新組織表的時候 使用的索引。如果你不以如下格式 schema.index-name指定全名，將使用默認的模式。默認模式是這個索引被創建在哪個用戶下。數據庫管理這個在表重組的時候使用這個索引來完全的對表中的數據進行重新排序。
對於一個in place表重組，如果集群索引 被定義在表上且指定了一個索引，那麼必須指定的必須是這個集群索引。如果沒有指定in place選項，可以指定任何索引。如果你不指明一個索引名，記錄將不管順序進行重組。如果這個表定義了一個集群索引，那麼，即使是沒有指明索引，也會使用該聚集索引來聚集表。如果你正在重組MDC表或IDC表，那麼你不能指定索引。
如果一個表重組使用了INDEX，也使用了ON DATA PARTITION子句，那麼隻有指定的分區被使用該索引進行重組。
ALLOW NO ACCESS
指定在表重組的過程中其他用戶不能訪問該表。
ALLOW NO ACCESS是默認的模式，並且是在沒有指定ON DATA PARTITION 子句 的情況下重組一個分區表時 唯一支持 的模式。
如果為一個數據分區表指定了 ON DATA PARTITION子句，那麼隻有這個被指定的數據分區被重組。
- 如果除了係統生成的XML路徑索引之外表上沒有定義非分區索引，隻有指定的分區限製ALLOW NO ACCESS模式。運行用戶讀寫該表的其他數據分區。
- access mode. In this case, the table is placed in ALLOW NO ACCESS mode.如果除了係統生成的XML路徑索引之外，表上定義了非分區索引，那麼ALLOW NO ACCESS是默認的模式並且是唯一支持的訪問模式。
ALLOW READ ACCESS
在重組過程中僅允許對表的讀訪問。
.ALLOW READ ACCESS模式是一個非分區表的默認模式。
如果 為一個數據分區表指定了 ON DATA PARTITION 子句，那麼僅有指定的數據分區被重組：
- 如果除了係統生成的XML路徑索引之外表上沒有定義非分區索引，那麼ALLOW READ ACCESS是默認的模式，並且隻有指定的分區被限製該訪問級別，用戶可以讀寫表的其他分區。
- 如果除了係統生成的XML路徑索引之外表上定義了非分區索引，那麼不支持ALLOW READ ACCESS模式。如果在這種情況下指定了ALLOW READ ACCESS，那麼會返回SQL1548N的錯誤 (SQLSTATE 5U047)。
INPLACE（inplace: 在合適的位置）
在允許用戶訪問的情況下重新組織表。
合適位置表重組（*In place table reorganization* ）僅適用於非分區、非MDC、非ITC的表，且要求表中 沒有擴展索引（**extended indexes**） 沒有定義在XML列上的索引的情況。合適位置表重組隻能在至少有3個頁的表上執行。
合適位置表重組異步的進行，並且可能不立即生效。
- ALLOW READ ACCESS 在表重組過程中僅允許讀訪問。
- ALLOW WRITE ACCESS 在表重組過程中允許寫訪問，這是默認情況。
- NOTRUNCATE TABLE 在合適位置重組之後不truncate表。在truncation時期，這個表加S鎖。
- START 開始 in place REORG處理過程。因為這是默認的，所以這個關鍵字是可選的。
- STOP 在當前時間點停止in place進程。
- PAUSE 為當前時間正在進行時的in place REORG 掛起或終止(* Suspend or pause in place REORG for the time being.*)。
- RESUME 繼續或恢複一個之前中斷的in place 表重組。當一個在線重組被恢複後，並且你希望使用與重組中斷時使用的相同的選項你必須在恢複時再指定一遍選項。
USE tbspace-name
這個選項指定一個係統臨時表空間的名稱，在這個表空間中存放一個被重組的臨時表。如果你不停一個表空間名稱，數據庫管理者在將被重組的表所在的表空間中存儲一份表的工作備份。
對一個8KB，16 KB, 或32 KB的表對象,如果你所指定的係統臨時表空間的頁大小與表數據所在的表空間的頁大小不匹配，DB2數據庫產品會嚐試找一個 LONG/LOB對象的正確大小的 臨時表空間。 這樣的一個表空間必須存在才能重組成功。
對於分區表，臨時表空間被用作 表的數據分區的重新組織 的臨時存儲。整個分區表的重組 在一個時刻隻會 重新組織一個單一數據分區。臨時表空間必須能夠保存表中的最大的數據分區的數據，而不是整個表的數據。當指定 ON DATA PARTITION 子句時，臨時表空間必須能夠保存指定的分區的數據。
如果你不為分區表提供一個表空間的名稱，每個數據分區所在的表空間會被用於數據分區的臨時存儲。數據分區所在的表空間上必須有足夠的可用空間來存儲數據分區的備份。
INDEXSCAN索引掃描
對於一個聚集的REORG，索引掃描 將會被用來重新組織表的記錄。通過through 索引 訪問表 重新組織表的行。默認的方法是掃描表，排序結果來重新組織表，使用臨時表空間是必要的。 即使 through 索引鍵作為排序順序，掃描和排序 比起 先從一個索引中讀記錄標識符 然後遍曆記錄來 仍會比較快。
LONGLOBDATA
長fielf和LOB數據將會被重新組織。
This is not required even if the table contains long or LOB columns. The default is to avoid reorganizing these objects because it is time consuming and does not improve clustering. However, running a reorganization with the LONGLOBDATA option on tables with XML columns will reclaim unused space and thereby reduce the size of the XML storage object.
This parameter is required when converting existing LOB data into inlined LOB data.
USE longtbspace-name
This is an optional parameter, which can be used to specify the name of a temporary table space to be used for rebuilding long data. If no temporary table space is specified for either the table object or for the long objects, the objects will be constructed in the table space they currently reside. If a temporary table space is specified for the table but this parameter is not specified, then the table space used for base reorg data will be used, unless the page sizes differ. In this situation, the DB2 database system will attempt to choose a temporary container of the appropriate page size to create the long objects in.
If USE longtbspace-name is specified, USE tbspace-name must also be specified. If it is not, the longtbspace-name argument is ignored.
KEEPDICTIONARY保持字典
If the COMPRESS attribute for the table is YES and the table has a compression dictionary then no new dictionary is built. All the rows processed during reorganization are subject to compression using the existing dictionary. If the COMPRESS attribute is YES and a compression dictionary doesn't exist for the table, a dictionary will only be created (and the table compressed) in this scenario if the table is of a certain size (approximately 1 to 2 MB) and sufficient data exists within this table. If, instead, you explicitly state REORG RESETDICTIONARY, then a dictionary is built as long as there is at least 1 row in the table. If the COMPRESS attribute for the table is NO and the table has a compression dictionary, then reorg processing will preserve the dictionary and all the rows in the newly reorganized table will be in noncompressed format. It is not possible to compress some data such as LOB data not stored in the base table row.
When the LONGLOBDATA option is not specified, only the table row data is reorganized. The following table describes the behavior of KEEPDICTIONARY syntax in REORG command when the LONGLOBDATA option is not specified.
RESETDICTIONARY
If the COMPRESS attribute for the table is YES then a new row compression dictionary is built. All the rows processed during reorganization are subject to compression using this new dictionary. This dictionary replaces any previous dictionary. If the COMPRESS attribute for the table is NO and the table does have an existing compression dictionary then reorg processing will remove the dictionary and all rows in the newly reorganized table will be in noncompressed format. It is not possible to compress some data such as LOB data not stored in the base table row.
If the LONGLOBDATA option is not specified, only the table row data is reorganized. The following table describes the behavior of RESETDICTIONARY syntax in REORG command when the LONGLOBDATA option is not specified.
ON DATA PARTITION partition-name
這個選項為數據分區表指定要重組的數據分區。
DB2 9.7 修補補丁1和之後的版本，這個子句可以用在REORG INDEXES ALL 命令之後來重組在某一個特定分區上非分區索引；可以用在REORG TABLE 命令之後來重組在一個特定分區上的數據。
如果指定的表上這個分區號碼不存在時，當在這個分區表上 的REORG TABLE或REORG INDEXES ALL 命令之後使用這個子句時，重組失敗並且返回SQL2222N錯誤，錯誤原因碼為1 。 如果分區的分區號碼狀態為附屬的或分離的狀態時（*is in the attached or detached state*），重組失敗並且返回SQL2222N錯誤，錯誤原因碼為3。
如果執行帶有 ON DATA PARTITION 的REORG INDEX 命令，重組失敗並且返回SQL2222N錯誤，錯誤原因碼為2。
如果分區表 在reorg掛起狀態 ，並且表上沒有沒有非分區索引定義，REORG TABLE 命令失敗並且返回SQL1549N (SQLSTATE 5U047) 錯誤 。
ALL DBPARTITIONNUMS
指定操作 對在db2nodes.cfg 文件中聲明的所有的數據庫分區生效。如果沒有指定數據庫分區子句 默認對所有聲明的數據庫分區生效。
EXCEPT
指定操作影響db2nodes.cfg 文件中聲明的所有的數據庫分區，除了在數據庫分區list中聲明的分區。
ON DBPARTITIONNUM | ON DBPARTITIONNUMS
在一個數據分區集合上執行操作。
- db-partition-number1 指定一個再數據庫分區list中的數據庫分區號。
- db-partition-number2 Specifies the second database partition number, so that all database partitions from db-partition-number1 up to and including db-partition-number2 are included in the database partition list. 指定第二個數據庫分區號，從而從*db-partition-number1*開始到*db-partition-number2*包含*db-partition-number2*的所有的數據庫分區 都被包含在數據庫分區列表中。
Examples
example 1
要重組一個表從而回收空間（*reclaim space*），使用臨時表空間mytemp1，輸入以下的命令：
db2 reorg table homer.employee use mytemp1
example 2
To reorganize tables in a partition group consisting of database partitions 1, 2, 3, and 4 of a four-partition system, you can enter either of the following commands:
要重組一個有數據庫分區組，該分區組由1，2,3,4四個分區構成的四分區係統，你可以輸入下麵的 命令
db2 reorg table employee index empid on dbpartitionnum (1,3,4)
db2 reorg table homer.employee index homer.empid on all
dbpartitionnums except dbpartitionnum (2)
example 3
在允許其他事務同時讀表和更新表的前提下， 清理 在EMPLOYEE 表中的所有索引中的 pseudo（標記） 刪除的鍵和pseudo（標記） 空的頁，輸入：
db2 reorg indexes all for table homer.employee allow write access cleanup only
example 4
在允許其他事務同時讀表和更新表的前提下， 清理 在EMPLOYEE 表中的所有索引中的 pseudo（標記） 空的頁，輸入：
db2 reorg indexes all for table homer.employee allow write access cleanup only pages
example 5
使用係統臨時表空間TEMPSPACE1 作為工作域來重組EMPLOYEE表 ，輸入：
db2 reorg table homer.employee use tempspace1
example 6
使用默認模式HOMER 啟動、中斷、恢複 EMPLOYEE表的in place 重組 ，這個HOMER模式在之前的例子中明確的聲明過，輸入下麵的命令：
db2 reorg table employee index empid inplace start
db2 reorg table employee inplace pause
db2 reorg table homer.employee inplace allow read access notruncate table resume
這個命令恢複reorg 包含附加的關鍵詞 指明了隻讀訪問 並且跳過清空步驟，這個表S-lock鎖定。
Usage notes
Restrictions:
- The REORG utility does not support the use of nicknames.
- The REORG TABLE command is not supported for declared temporary tables or created temporary tables.
- The REORG TABLE command cannot be used on views.
- Reorganization of a table is not compatible with range-clustered tables, because the range area of the table always remains clustered.
- REORG TABLE cannot be used on a partitioned table in a DMS table space while an online backup of ANY table space in which the table resides, including LOBs and indexes, is being performed.
- REORG TABLE cannot use an index that is based on an index extension.
- If a table is in reorg pending state, an inplace reorg is not allowed on the table.
- Concurrent table reorganization sharing the same temporary DMS table space is not supported.
- Before running a reorganization operation against a table to which event monitors write, you need to deactivate the event monitors on that table.
- For data partitioned tables:
- The table must have an ACCESS_MODE in SYSCAT.TABLES of Full Access.
- Reorganization skips data partitions that are in a restricted state due to an attach or detach operation. If the ON
- DATA PARTITION clause is specified, that partition must be fully accessible.
- If an error occurs during table reorganization, some indexes or index partitions might be left invalid. The nonpartitioned indexes of the table will be marked invalid if the reorganization has reached or passed the replace phase for the first data partition. The index partitions for any data partition that has already reached or passed the replace phase will be marked invalid. Indexes will be rebuilt on the next access to the table or data partition.
- If an error occurs during index reorganization when the ALLOW NO ACCESS mode is used, some indexes on the table might be left invalid. For nonpartitioned RID indexes on the table, only the index that is being reorganized at the time of the failure will be left invalid. For MDC tables with nonpartitioned block indexes, one or more of the block indexes might be left invalid if an error occurs. For MDC or ITC tables with partitioned indexes, only the index object on the data partition being reorganized will be left invalid. Any indexes marked invalid will be rebuilt on the next access to the table or data partition.
- When a data partitioned table with only partitioned indexes defined on the table is in the reorg pending state, issuing a REORG TABLE command with the ON DATA PARTITION clause brings only the specified data partition out of the reorg pending state. To bring the remaining partitions of the table out of the reorg pending state, either issue REORG TABLE command on the entire table (without the ON DATA PARTITION clause), or issue a REORG TABLE command with the ON DATA PARTITION clause for each of the remaining partitions.
Information about the current progress of table reorganization is written to the history file for database activity. The history file contains a record for each reorganization event. To view this file, execute the LIST HISTORY command for the database that contains the table you are reorganizing.
You can also use table snapshots to monitor the progress of table reorganization. Table reorganization monitoring data is recorded regardless of the Database Monitor Table Switch setting.
If an error occurs, an SQLCA dump is written to the history file. For an inplace table reorganization, the status is recorded as PAUSED.
When an indexed table has been modified many times, the data in the indexes might become fragmented. If the table is clustered with respect to an index, the table and index can get out of cluster order. Both of these factors can adversely affect the performance of scans using the index, and can impact the effectiveness of index page prefetching. REORG INDEX or REORG INDEXES with the REBUILD option can be used to reorganize one or all of the indexes on a table. Index reorganization rebuild will remove any fragmentation and restore physical clustering to the leaf pages. Use the REORGCHK command to help determine if an index needs reorganizing. Be sure to complete all database operations and release all locks before invoking index reorganization. This can be done by issuing a COMMIT after closing all cursors opened WITH HOLD, or by issuing a ROLLBACK.
A classic table reorganization (offline reorganization) rebuilds the indexes during the last phase of the reorganization. When more than one temporary table space exists, it is possible that a temporary table space in addition to the one specified on the REORG TABLE command may be utilized for additional sorts that can accompanying table reorg processing. However, the inplace table reorganization (online reorganization) does not rebuild the indexes. It is recommended that you issue a REORG INDEXES command after the completion of an inplace table reorganization. An inplace table reorganization is asynchronous, therefore care must be taken to ensure that the inplace table reorganization is complete before issuing the REORG INDEXES command. Issuing the REORG INDEXES command before the inplace table reorganization is complete, might cause the reorganization to fail (SQLCODE -2219).
When the REORG rebuilds the indexes on an MDC table, the Full_Block hint bits are not set. Because the Full_Block hint is not set, you might experience degraded insert performance if you insert rows from existing dimension values after the REORG completes and the DB2_TRUST_MDC_BLOCK_FULL_HINT registry variable is turned on. The insert performance automatically improves for each dimension value after an insert of that dimension value completes. For more information, see DB2_TRUST_MDC_BLOCK_FULL_HINT performance variable.
Tables that have been modified so many times that data is fragmented and access performance is noticeably slow are candidates for the REORG TABLE command. You should also invoke this utility after altering the inline length of a structured type column in order to benefit from the altered inline length. Use the REORGCHK command to determine whether a table needs reorganizing. Be sure to complete all database operations and release all locks before invoking REORG TABLE. This can be done by issuing a COMMIT after closing all cursors opened WITH HOLD, or by issuing a ROLLBACK. After reorganizing a table, use RUNSTATS to update the table statistics, and REBIND to rebind the packages that use this table. The reorganize utility will implicitly close all the cursors.
With DB2 V9.7 Fix Pack 1 and later, REORG TABLE commands and REORG INDEXES ALL commands can be issued on a data partitioned table to concurrently reorganize different data partitions or partitioned indexes on a partition. When concurrently reorganizing data partitions or the partitioned indexes on a partition, users can access the unaffected partitions but cannot access the affected partitions. All the following criteria must be met to issue REORG commands that operate concurrently on the same table:
Each REORG command must specify a different partition with the ON DATA PARTITION clause.
Each REORG command must use the ALLOW
NO ACCESS mode restrict access to the data partitions.
The partitioned table must have only partitioned indexes if issuing REORG TABLE commands. No nonpartitioned indexes (except system-generated XML path indexes) can be defined on the table.
For a partitioned table T1 with no nonpartitioned indexes (except system-generated XML path indexes) and with partitions P1, P2, P3, and P4, the following REORG commands can run concurrently:
REORG INDEXES ALL FOR TABLE T1 ALLOW NO ACCESS ON DATA PARTITION P1
REORG TABLE T1 ALLOW NO ACCESS ON DATA PARTITION P2
REORG INDEXES ALL FOR TABLE T1 ALLOW NO ACCESS ON DATA PARTITION P3
Operations such as the following are not supported when using concurrent REORG commands:
Using a REORG command without the ON
DATA PARTITION clause on the table.
Using an ALTER TABLE statement on the table to add, attach, or detach a data partition.
Loading data into the table.
Performing an online backup that includes the table.
If the table contains mixed row format because the table value compression has been activated or deactivated, an offline table reorganization can convert all the existing rows into the target row format.
If the table is distributed across several database partitions, and the table or index reorganization fails on any of the affected database partitions, only the failing database partitions will have the table or index reorganization rolled back.
If the reorganization is not successful, temporary files should not be deleted. The database manager uses these files to recover the database.
If the name of an index is specified, the database manager reorganizes the data according to the order in the index. To maximize performance, specify an index that is often used in SQL queries. If the name of an index is not specified, and if a clustering index exists, the data will be ordered according to the clustering index.
The PCTFREE value of a table determines the amount of free space designated per page. If the value has not been set, the utility will fill up as much space as possible on each page.
To complete a table space rollforward recovery following a table reorganization, both regular and large table spaces must be enabled for rollforward recovery.
If the table contains LOB columns that do not use the COMPACT option, the LOB DATA storage object can be significantly larger following table reorganization. This can be a result of the order in which the rows were reorganized, and the types of table spaces used (SMS or DMS).
Indexes over XML data may be re-created by the REORG INDEXES/TABLE command. For details, see "Recreation of indexes over XML data".
An inplace REORG operation might not be able to fully reclaim space in a table because it cannot move internal records.
最後更新：2017-06-28 17:32:20