DB2 V9表壓縮(二)

對新表使用資料行壓縮（表壓縮）：

資料行壓縮的目標是節省磁碟儲存空間。它還可以減少磁碟I/O。另外，可以在緩衝池中快取記憶體更多資料，這樣就可以提高緩衝池命中率。但是，關聯的成本以壓縮和解壓縮資料所需的額外CPU週期形式出現。資料行壓縮節省的儲存量和對效能的影響與資料庫中資料的特徵、資料庫的佈局和調整以及應用程式工作負載相關。僅壓縮資料頁上的資料或日誌記錄中的資料。資料行壓縮使用基於靜態字典的壓縮演算法來逐行壓縮資料。在行級別壓縮資料允許將一行中跨多個列值的重複模式替換為較短的符號字串。為了壓縮表資料，表COMPRESS屬性必須設定為YES，且該表必須有壓縮字典。要把表設定成壓縮方式，使用下列兩個命令之一：

CREATE TABLE table_name . . . COMPRESS YES

OR

ALTER TABLE tablename COMPRESS YES

我們接下來建立示例表TEST1，表資料將儲存在表空間TABLESPACE1中，其將啟用表壓縮功能（資料行壓縮）。在DB2CLP視窗中，連上示例資料庫DB2TEST1，發出CREATE　TABLE命令建立帶COMPRESS屬性的示例表TEST1，具體如清單5所示：

- - 清單5 .建立示例表TEST1

C:\> DB2 CREATE TABLE TEST1 ( ID INTEGER , NAME VARCHAR ( 10 ) , DEPARID VARCHAR ( 10 ) , NOTE VARCHAR ( 100 ) ) IN TABLESPACE1 COMPRESS YES

DB20000I  SQL命令成功完成。

命令執行成功，這樣我們就建立了示例表TEST1,其啟用了資料行壓縮方式。要使壓縮生效，我們需要要構建壓縮字典並接著壓縮表（表中需要有資料，不能是空表），然後DB2將掃描表中的資料，找出相同的字串替換為較短的符號字串，並放入到壓縮字典中。表中的所有資料行都將參與構建壓縮字典。該字典將與表資料行一起儲存在表資料物件部分。為了構建壓縮字典，我們可以使用REORG命令，執行離線重組。第一次壓縮一個表（或者你需要重新構建壓縮字典）你可以使用如下命令：

REORG table table_name resetdictionary

這個命令將掃描整個表，建立壓縮字典，然後執行實際表的重組，並在重組的過程中壓縮資料。需要注意此時REORG使用的是RESETDICTIONARY選項。如果表的COMPRESS屬性為YES並且字典存在，則可能會壓縮插入到頁中的資料行。此情況適用於任何插入行操作，包括通過匯入或裝入操作來插入。壓縮是對整個表啟用的，但卻是單獨地壓縮每行。因此，一個表可以同時包含已壓縮的行和未壓縮的行。以後如果你需要執行一個正常的表重組，但是有不希望重新構建壓縮字典，可以執行下面的命令：

REORG table table_name keepdictionary

需要注意此時REORG使用的是KEEPDICTIONARY選項。每個表都擁有自己的壓縮字典。只能壓縮永久資料物件。資料行壓縮不適用於索引、長整型資料物件、LOB 資料物件和XML資料物件。行壓縮與表資料複製支援不相容。

下面我們將為清單5中建立的示例表TEST1建立一個壓縮字典。

在DB2CLP視窗中，發出REORG命令，離線執行表重組命令。當示例表TEST1是空表時，執行REORG命令時會報SQL2220W錯誤，構建壓縮字典失敗，這是因為示例表TEST1中需要有部分資料時建立壓縮字典才會成功，否則壓縮字典將無法構建。具體如清單6所示：

- - 清單6 .對示例表TEST1建立壓縮字典

C:\> db2 REORG TABLE test1  resetdictionary

SQL2220W 沒有為一個或多個資料物件構建壓縮字典。

C:\> db2 ?  sql2220w

SQL2220W 沒有為一個或多個資料物件構建壓縮字典。

解釋:

 未能為一個或多個資料物件構建壓縮字典。這些物件不包含任何記錄，或者不包含大於適合於此頁大小的最小記錄長度的任何記錄。未構建新字典。將繼續完成該操作。如果在執行該操作之前已經有一個字典，則會保留該字典，而行數將取決於壓縮程度。

使用者響應:

 參閱“管理日誌”以確保是那些資料物件導致了警告。

 

為了構建壓縮字典，我們需要先為示例表TEST1插入部分資料。

在DB2CLP視窗中，連上示例資料庫DB2TEST1，在示例表TEST1插入部分資料，部門編號都是‘001’，備註資訊都是‘TEST’，編號和姓名都不相同，具體如清單7所示：

- - 清單7 .對示例表TEST1插入部分資料

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 1 , ' AA ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 2 , ' BB ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 3 , ' CC ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 4 , ' DD ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 5 , ' EE ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 6 , ' FF ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 7 , ' GG ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 8 , ' HH ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 9 , ' II ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

 

命令成功完成，這樣我們為示例表TEST1插入了9條記錄。部門編號都是‘001’，備註資訊都是‘TEST’，編號和姓名都不相同。

下面我們對示例表TEST1建立一個壓縮字典，由於是第一次構建壓縮字典，所以需要執行帶RESETDICTIONARY選項的REORG命令。

在DB2CLP視窗中，對示例表TEST1發出REORG命令，執行離線重組，為示例表TEST1構建壓縮字典，具體如清單8所示：

- - 清單8 .對示例表TEST1建立壓縮字典

 

C:\> db2 REORG TABLE test1  resetdictionary

DB20000I  REORG命令成功完成。

 

命令成功完成，這個命令將掃描整個表，建立壓縮字典，然後執行實際表的重組，並在重組的過程中壓縮資料。由於部門編號和備註資訊各行的資料都相同，所以將DB2通過分析與獲取資料中出現的重複模式，生成壓縮字典。

接下來我們繼續對示例表TEST1插入資料，由於示例表TEST1的COMPRESS屬性為YES並且字典存在，所以可能會壓縮插入到頁中的資料行。此情況適用於任何插入行操作，包括通過匯入或裝入操作來插入。

在DB2CLP視窗中，對示例表TEST1通過INSERT　INTO命令插入剩餘10條資料，具體如清單9所示：

- - 清單9 .對示例表TEST1插入資料

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 10 , ' JJ ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 11 , ' KK ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 12 , ' LL ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 13 , ' MM ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 14 , ' NN ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 15 , ' OO ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 16 , ' PP ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 17 , ' QQ ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 18 , ' RR ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 19 , ' SS ' , '  001 '  , '  TEST ')

DB20000I  SQL命令成功完成。

 

命令成功完成，新插入的10條記錄在資料庫中也將以壓縮的方式儲存。

如果想估計對示例表TEST1使用表壓縮（資料行壓縮）比不使用表壓縮節省了多少空間，可以通過INSPECT ROWCOMPESTIMATE語句進行評估分析。記住，這個命令只是估計壓縮的效果，而不是檢視最終實際的壓縮效果，如果想檢視實際的壓縮效果，需要檢視系統表SYSIBM．SYSTABLES。帶壓縮估計選項（ROWCOMPESTIMATE）的INSPECT命令，將生成一份報告，描述節省了多少頁。語法如下：

DB2 INSPECT ROWCOMPESTIMATE TABLE NAME table_name RESULTS KEEP file_name

 由於INSPECT命令生成的檔案是二進位制的，無法直接檢視，所以我們需要使用DB2INSPF命令將此檔案格式轉成可讀模式才能檢視，具體語法如下：

DB2INSPF file_name  output_file_name

       下面我們看看我們使用表壓縮方式節省了多少空間，在DB2CLP視窗中發出DB2　INSPECT命令，具體如清單10所示：

- - 清單10 .檢視示例表TEST1，看一下表壓縮方式節省了多少空間

C:\> DB2 " INSPECT ROWCOMPESTIMATE TABLE NAME test1 RESULTS KEEP  test1.resp "

DB20000I  INSPECT命令成功完成。

 

       命令成功完成，這樣我們就在C:\Program Files\IBM\SQLLIB\DB2下生成了一個檔案test1.resp，由於這個檔案是二進位制的，我們需要使用DB2INSPF將此檔案格式轉成可讀模式才能檢視，繼續在DB2CLP視窗中執行DB2INSPF命令，具體如清單11所示：

- - 清單11 .檢視示例表TEST1，看一下表壓縮方式節省了多少空間

C:\> cd C:\Program Files\IBM\SQLLIB\DB2

C:\Program Files\IBM\SQLLIB\DB2> DB2INSPF test1.resp  output_test1.resp

 

       此時我們開啟output_test1.resp檔案，可以看到通過壓縮而節省的頁數所佔的百分比是56，通過壓縮而節省的位元組數所佔的百分比是56，由於行大小太小而不適合壓縮的行數所佔的百分比是0，壓縮字典大小是8192個位元組，擴充字典大小是3336 個位元組，具體如清單12所示：

- - 清單12 .檢視示例表TEST1，看一下表壓縮方式節省了多少空間

DATABASE: DB2TEST1                                                           

VERSION : SQL09010                                                            

2007-10-25-21.14.03.500000                                                   

操作：ROWCOMPESTIMATE TABLE

模式名：RHETTE 

表名：TEST1

表空間標識：3  物件標識：4

結果檔名：test1.resp

 

    表階段開始（有符號的標識：4，無符號的：4；表空間標識：3）：RHETTE.TEST1

    

      資料階段開始。物件：4 表空間：3

      行壓縮估計結果：

      通過壓縮而節省的頁數所佔的百分比：56

      通過壓縮而節省的位元組數所佔的百分比：56

      由於行大小太小而不適合壓縮的行數所佔的百分比：0

      壓縮字典大小：8192 個位元組。

      擴充字典大小：3336 個位元組。

      資料階段結束。

    表階段結束。

處理已完成。2007-10-25-21.14.03.562000

 

       我們可以使用INSPECT ROWCOMPESTIMATE語句對壓縮的效果進行評估分析，如果想檢視實際的壓縮效果，需要檢視系統表SYSIBM．SYSTABLES，這系統表中，有幾個新增的列是和壓縮相關的：

Ø         COMPRESSION：此參數列示對於表來說，是否使用了壓縮，其有4個值，含義分別是：

n         N：沒有設定任何壓縮

n         V：僅僅設定了空間值壓縮

n         R：僅僅設定了資料行壓縮

n         B：即設定了空間值壓縮，也設定了資料行壓縮

Ø         AVGROWSIZE：新增列，用來表示表的平均物理行長，包括所有壓縮和沒有壓縮的資料行，此引數用來決定每頁能存放的最大行數（對常規表空間來說，單頁最大能放255行，對於大型表空間來說，單頁最大行數會超過255行。）。當值為-1的時候，表示統計資訊沒有收集。

Ø         PCTPAGESSAVED：新增列，表示使用壓縮節省空間頁的百分比。當值為-1的時候，表示統計資訊沒有收集。

Ø         PCTROWSCOMPRESSED：新增列，表示表中壓縮的行數佔總行數的百分比。這個引數用來決定解壓縮時CPU的開支。當值為-1的時候，表示統計資訊沒有收集。

Ø         AVGROWCOMPRESSIONRATIO：新增列，表示所有壓縮的行的平均壓縮比例（是壓縮前的總頁數和壓縮後的總頁數的比例）。當值為-1的時候，表示統計資訊沒有收集。

Ø         AVGCOMPRSSEDROWSIZE：新增列，表示所有壓縮行在物理磁碟上的平均物理行長，當值為-1的時候，表示統計資訊沒有收集。

另外，需要注意的是，如果想檢視SYSIBM．SYSTABLES表中某個表的壓縮情況，需要要查詢都是表執行RUNSTATS，只有這樣，上述幾個列的值才不會是-1。

下面，我們在DB2CLP視窗中，發出RUNSTATS命令對示例表TEST1執行統計資訊，具體如清單13所示：

- - 清單13 .對示例表TEST1執行統計資訊

C:\> DB2 RUNSTATS ON TABLE RHETTE.TEST1

DB20000I  RUNSTATS命令成功完成。

 

    接下來我們就可以通過系統表SYSIBM．SYSTABLES檢視示例表TEST1的壓縮情況了。

我們繼續在DB2CLP視窗中，發出SELECT命令檢視示例表TEST1的壓縮情況，具體如清單14所示：

- - 清單14 .檢視示例表TEST1壓縮情況

C:\> DB2 SELECT NAME , COMPRESSION , AVGROWSIZE , PCTPAGESSAVED , PCTROWSCOMPRESSED ,AVGROWCOMPRESSIONRATIO , AVGCOMPRESSEDROWSIZE FROM SYSIBM.SYSTABLES WHERE NAME = 'TEST1'

NAME  COMPRESSION  AVGROWSIZE  PCTPAGESSAVED  PCTROWSCOMPRESSED    AVGROWCOMPRESSIONRATIO   AVGCOMPRESSEDROWSIZE       

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -       

TEST1   R           19            56            100                2.29223                      19         

1 條記錄已選擇。      

 

    可以看到示例表TEST1啟用了資料行壓縮，壓縮後的所有行的平均行長是19，壓縮後節省空間的百分比是56％和用INSPECT命令估計的一樣，壓縮的行數佔總行數的百分比是100％，壓縮前和壓縮後頁數的百分比是2．29223，壓縮行的平均行長是19。

    如果想檢視壓縮字典的大小，可以使用ADMIN_GET_TAB_INFO表函式檢視其DICTIONARY_SIZE列的值，比如我們想檢視示例表TEST1的壓縮字典大小，可以在DB2CLP視窗中這麼檢視，具體如清單15所示：

 - - 清單15 .檢視示例表TEST1壓縮字典大小

C:\>db2 describe "select * from table(sysproc.admin_get_tab_info('rhette','test1')) as t"

SQLDA 資訊

 sqldaid : SQLDA     sqldabc: 1204  sqln: 27  sqld: 27

 列資訊

 sqltype               sqllen  sqlname.data                    sqlname.length

 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

 449   VARCHAR            128  TABSCHEMA                                    9

 449   VARCHAR            128  TABNAME                                      7

 453   CHARACTER            1  TABTYPE                                      7

 501   SMALLINT             2  DBPARTITIONNUM                              14

 497   INTEGER              4  DATA_PARTITION_ID                           17

 453   CHARACTER            1  AVAILABLE                                    9

 493   BIGINT               8  DATA_OBJECT_L_SIZE                          18

 493   BIGINT               8  DATA_OBJECT_P_SIZE                          18

 493   BIGINT               8  INDEX_OBJECT_L_SIZE                         19

 493   BIGINT               8  INDEX_OBJECT_P_SIZE                         19

 493   BIGINT               8  LONG_OBJECT_L_SIZE                          18

 493   BIGINT               8  LONG_OBJECT_P_SIZE                          18

 493   BIGINT               8  LOB_OBJECT_L_SIZE                           17

 493   BIGINT               8  LOB_OBJECT_P_SIZE                           17

 493   BIGINT               8  XML_OBJECT_L_SIZE                           17

 493   BIGINT               8  XML_OBJECT_P_SIZE                           17

 501   SMALLINT             2  INDEX_TYPE                                  10

 453   CHARACTER            1  REORG_PENDING                               13

 449   VARCHAR             10  INPLACE_REORG_STATUS                        20

 449   VARCHAR             12  LOAD_STATUS                                 11

 453   CHARACTER            1  READ_ACCESS_ONLY                            16

 453   CHARACTER            1  NO_LOAD_RESTART                             15

 501   SMALLINT             2  NUM_REORG_REC_ALTERS                        20

 453   CHARACTER            1  INDEXES_REQUIRE_REBUILD                     23

 453   CHARACTER            1  LARGE_RIDS                                  10

 453   CHARACTER            1  LARGE_SLOTS                                 11

 493   BIGINT            8  DICTIONARY_SIZE                      15

 

如果覺得壓縮的效果不能滿意，可以對錶進行解壓縮，也就是把表的COMPRESS 屬性設定為 NO，然後執行傳統離線表重組，這樣就把壓縮表變成了不壓縮的表了。

       比如，我們現在想對示例表TEST1進行解壓縮，可以繼續在當前的DB2CLP視窗中，先發出ALTER　TABLE命令，把示例表TEST1的COMPRESS屬性設定為NO，再執行REORG命令執行離線重組，具體如清單16所示：

- - 清單16 .對示例表TEST1進行解壓縮

C:\Program Files\IBM\SQLLIB\DB2>cd \

C:\> db2 alter table test1 compress no

DB20000I  SQL命令成功完成。

C:\> db2 reorg table test1 resetdictionary

DB20000I  REORG命令成功完成。

 

       命令成功完成，此時示例表TEST1將不再使用表壓縮功能，並且壓縮字典將被刪除。此時我們再次在DB2CLP視窗中檢視示例表TEST1的實際壓縮情況，可以發現，沒有使用壓縮，有關壓縮的各個列都和壓縮的時候值不一樣了，具體如清單17所示：

- - 清單17 .檢視示例表TEST1壓縮情況

C:\> DB2 SELECT NAME , COMPRESSION , AVGROWSIZE , PCTPAGESSAVED , PCTROWSCOMPRESSED ,AVGROWCOMPRESSIONRATIO , AVGCOMPRESSEDROWSIZE FROM SYSIBM.SYSTABLES WHERE NAME = 'TEST1'

NAME  COMPRESSION  AVGROWSIZE  PCTPAGESSAVED  PCTROWSCOMPRESSED    AVGROWCOMPRESSIONRATIO   AVGCOMPRESSEDROWSIZE        

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -       

TEST1     N         45             0            0                    0                            0             

1 條記錄已選擇。      

 

       總結一下，如果對一個新表啟用表壓縮：

l         建立一個表，將表的COMPRESS屬性設定為 YES

l         對錶裝入一部分資料

l         執行帶有RESETDICTIONARY選項的離線重組，建立一個新的壓縮字典

l         裝入剩餘的資料到這個表中（裝入時將考慮壓縮字典並在裝入時壓縮資料）

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9524210/viewspace-496180/，如需轉載，請註明出處，否則將追究法律責任。