開始之前
在開始討論之前如果有需要可以復習一下 DB2 的表分區特性，這一特性是在 DB2 v9 引入的，developerWorks 上的教程 "DB2 9表分區-改進大型數據庫的管理" 是一個很好的參考。閱讀此教程需要 IBM ID 和密碼登錄，任何人都可以免費注冊獲得。
表分區特性帶來的一個顯著的性能優勢在於其便捷的數據滾入（roll in）/ 滾出（roll out）功能，通過命令“ALTER TABLE … ATTACH PARTITION …”可以將單個非分區表的數據滾入到目標分區表中作為其新的數據分區，也可以通過命令“ALTER TABLE … DETACH PARTITION …”將分區表的一個數據分區從表中移出，完成這兩種操作都不需要任何數據的移動，因而具有很好的性能優勢。
DB2 Net Search Extender
DB2 NSE（Net Search Extender）提供了一種使用 SQL 查詢來搜索文本文檔的快速、通用和便捷的方法，這些文檔可以是 DB2、其他數據庫和文件系統中的全文本文檔。
NSE 在查詢時並不會按順序搜索所有文本文檔（這將花費大量時間），它會創建文本索引（text index）來搜索文檔，這樣搜索的效率更高。文本索引由從文本文檔中抽取的重要項（term）組成。創建文本索引就是定義和聲明索引屬性的過程，並沒有包含任何的數據，因此在開始使用之前還必須更新索引。更新索引就是將有關術語和文檔的數據添加到文本索引的過程，第一次索引更新時將文本列所有文本信息添加到索引中，也稱作初始更新。
基於完整性處理的增量更新
當使用文本索引進行搜索時，必須考慮表與文本索引之間的同步問題，這是因為必須在文本索引中反映對表進行的任何後續更改（例如，添加、刪除和更新文本文檔）。這些更改將以增量更新方式應用於文本索引。
在 DB2 9.7 之前采用的同步方式是以觸發器為基礎，這些觸發器會自動將新插入、已更改和已刪除（Insert，Update，Delete，簡稱 IUD）的文檔的對應的主鍵存儲在日志表中。每個文本索引都對應一個日志表，當更新文本索引時，這些日志表中的主鍵所對應的源表的文檔將被應用到文本索引中，從而完成文本索引與源表的同步。但是這種基於 IUD 觸發器的增量更新存在一個問題，即 IUD 之外的不被識別的更新將被忽略，例如使用 LOAD 命令裝入數據，以及分區表上 ATTACH 和 DETACH 等操作連接或拆離分區表的數據分區，這都將導致源表與文本索引的不一致。
在 DB2 9.7 中解決了這一問題，增加了一種新的同步方式，即通過完整性處理的增量更新，方法是增加文本保留登台表（text-maintained staging table ）來存儲有關新插入的和已刪除文檔的信息，並使用日志表來存儲有關通過觸發器捕獲的已更改文檔的信息。在這種方式中，文本保留登台表（text- maintained staging table ）實際上相當於一個輔助的日志表，因此這種結構也稱作輔助日志基礎結構（auxiliary log infrastructure ），如圖 1 所示。
圖 1. 使用觸發器和完整性處理的增量更新過程
接下來我們通過實際操作來詳細認識這一特性，同時可以較為全面深入的認識 DB2 NSE。
准備工作
在開始之前，我們先創建一個新的數據庫名字叫做 MYDB，並啟動 NSE 實例服務，如清單 1 所示。
本文中所有操作都是在 LinuxAMD64 平台上的 DB2 9.7 版本進行，9.7 版本之前的版本不支持對分區表進行全文本搜索。
在 DB2 9.7 之前采用的同步方式是以觸發器為基礎，這些觸發器會自動將新插入、已更改和已刪除（Insert，Update，Delete，簡稱 IUD）的文檔的對應的主鍵存儲在日志表中。每個文本索引都對應一個日志表，當更新文本索引時，這些日志表中的主鍵所對應的源表的文檔將被應用到文本索引中，從而完成文本索引與源表的同步。但是這種基於 IUD 觸發器的增量更新存在一個問題，即 IUD 之外的不被識別的更新將被忽略，例如使用 LOAD 命令裝入數據，以及分區表上 ATTACH 和 DETACH 等操作連接或拆離分區表的數據分區，這都將導致源表與文本索引的不一致。
在 DB2 9.7 中解決了這一問題，增加了一種新的同步方式，即通過完整性處理的增量更新，方法是增加文本保留登台表（text-maintained staging table ）來存儲有關新插入的和已刪除文檔的信息，並使用日志表來存儲有關通過觸發器捕獲的已更改文檔的信息。在這種方式中，文本保留登台表（text- maintained staging table ）實際上相當於一個輔助的日志表，因此這種結構也稱作輔助日志基礎結構（auxiliary log infrastructure ），如圖 1 所示。
圖 1. 使用觸發器和完整性處理的增量更新過程
接下來我們通過實際操作來詳細認識這一特性，同時可以較為全面深入的認識 DB2 NSE。
准備工作
在開始之前，我們先創建一個新的數據庫名字叫做 MYDB，並啟動 NSE 實例服務，如清單 1 所示。
本文中所有操作都是在 LinuxAMD64 平台上的 DB2 9.7 版本進行，9.7 版本之前的版本不支持對分區表進行全文本搜索。
清單 1. 創建數據庫
db2start
db2text start
db2 CREATE DB MYDB
db2 CONNECT TO MYDB
接著創建一個分區表並向表中插入若干數據，其中 pk 列為主鍵，comment 列類型為 VARCHAR 用來存放文本文檔，如清單 2 所示。
清單 2. 創建表（base table）並插入若干數據
create table part.multipart
(
pk integer not null primary key,
year integer not null,
value integer not null,
comment varchar(128)) PARTITION BY RANGE (year)
(STARTING (2000) ENDING (2004) EVERY 1)
insert into part.multipart values
(1, 2001, 1, 'blue and grey'),
(2,2001, 1, 'yellow or red'),
(3,2002, 1, 'red checkered with blue'),
(4, 2002, 1, 'white and black'),
(5, 2003, 1, 'grey or green')
PK YEAR VALUE
4
------ ----- ------ ------------
1 2001
1 blue and grey
2 2001
1 yellow or red
3 2002
1 red checkered with blue
4 2002
1 white and black
5 2003
1 grey or green
5 record(s) selected.
我們雖然已經啟動了 NSE 但是並沒有為數據庫 mydb 啟用文本搜索，如果此時查看一下目錄表（catalog table），我們會發現目前這個數據庫還沒有任何與 NSE 相關的目錄表（schema 為 DB2EXT 和 SYSIBMTS）。
對分區表創建文本索引
啟用數據庫
在創建文本索引之前需要啟用數據庫，使用的命令如清單 3 所示。這裡我們使用的是設置 DB2DBDFT 變量然後使用“db2text enable database for text”命令，也可以不設置 DB2DBDFT 變量而使用“db2text enable database for text CONNECT TO MYDB”命令而且以後每條 db2text 命令都需要加上 CONNECT TO 字句，相比之下前一種方式較為便捷。
清單 3. 啟用數據庫
db2text enable database for text
CTE0139 The environment variable "DB2DBDFT" is not set.
export DB2DBDFT=mydb
db2text enable database for text
CTE0001 Operation completed successfully.
啟用數據庫時會自動創建多個目錄表（catalog table），通過查看這些目錄表的內容可以得到當前數據庫上 NSE 相關的配置信息，如清單 4 所示。
清單 4. 查看 NSE 的目錄表（catalog table）
db2 list tables for schema DB2EXT
Table/VIEw Schema Type Creation time
----------- ------------ ----- -------------
DBDEFAULTS DB2EXT V 2009-09-29-...
FORMATS DB2EXT V 2009-09-29-...
INDEXCONFIGURATION DB2EXT V 2009-09-29-...
MODELS DB2EXT V 2009-09-29-...
TCOMMANDLOCKS DB2EXT T 2009-09-29-...
TCONFIGURATION DB2EXT T 2009-09-29-...
TDBDEFAULTS DB2EXT T 2009-09-29-...
TEXTCOLUMNS DB2EXT V 2009-09-29-...
TEXTINDEXES DB2EXT V 2009-09-29-...
TEXTINDEXFORMATS DB2EXT V 2009-09-29-...
TFORMATS DB2EXT T 2009-09-29-...
TMODELS DB2EXT T 2009-09-29-...
TTEXTCOLUMNS DB2EXT T 2009-09-29-...
TTEXTINDEXES DB2EXT T 2009-09-29-...
TTEXTREPLICATION DB2EXT T 2009-09-29-...
15 record(s) selected.
db2 list tables for schema SYSIBMTS
Table/VIEw Schema Type Creation time
------------- ---------- ----- --------------
0 record(s) selected.
創建文本索引
創建文本索引的命令是 CREATE INDEX ... FOR TEXT ...，但是對於分區表創建分區索引時必須使用 administration tables in 子句，否則會遇到錯誤。該子句用來指定為文本索引所創建的管理表的存放表空間。使用的命令和結果如清單 5 所示。
清單 5. 創建文本索引
db2text "create index part.multiind for text on part.multipart(comment)"
CTE0150 Unexpected end of command. Check the command syntax.
db2text "create index part.multiind for text on part.
multipart(comment) administration tables in USERSPACE1"
CTE0001 Operation completed successfully.
需要注意的是，對分區表創建文本索引時默認使用 index configuration(auxlog ON) 子句，而對於非分區表創建文本索引時默認使用 index configuration(auxlog OFF) 子句。AuxLog 用來控制是否對文本索引使用輔助日志基礎結構（文本保留登台表），也就是說，對於分區表默認使用觸發器和利用文本保留登台表的完整性處理的增量更新，而對於非分區表則默認使用觸發器為基礎的增量更新。
我們也可以在對分區表創建文本索引時使用 auxlog OFF 子句，這是允許的但不建議這樣做，這種情況下由於沒有了完整性處理的增量更新基礎架構，無法捕獲觸發器不識別的更改，將可能導致源表與文本索引的不一致。
創建文本索引的同時會創建相應的目錄表，使用清單 4 的命令查詢目錄表，其結果與清單 4 中的結果相比較發現，增加了若干個表和視圖，其命名規則為表名加上索引 ID，如清單 6 所示。
清單 6. 創建文本索引時創建的目錄表
EVENTIX322707 DB2EXT V 2009-09-30-00...
LOGIX322707 DB2EXT V 2009-09-30-00...
TEVENTIX322707 DB2EXT T 2009-09-30-00...
TLOGIX322707 DB2EXT T 2009-09-30-00...
SYSTSAUXLOG_IX322707 SYSIBMTS T 2009-09-30-00...
SYSTSMQT_IX322707 SYSIBMTS S 2009-09-30-00...
TSAUXLOG_IX322707 SYSIBMTS V 2009-09-30-00...
其中：
DB2EXT.TLOGIX322707 是日志表，用來記錄對文檔的更新；
DB2EXT.TEVENTIX322707 是事件表，收集有關在更新文本索引時所有更新和潛在問題的信息；
SYSIBMTS.SYSTSAUXLOG_IX322707 是輔助登台表（auxiliary staging table），用來記錄對文檔的插入和刪除。以上 3 個表還分別有一個對應的視圖。
SYSIBMTS.SYSTSMQT_IX322707，這是一個特殊的 MQT，是完整性處理的增量更新基礎架構的一部分。
此時以上 4 個表都是空的。在隨後的操作中，我們將關注這些目錄表特別是日志表和輔助日志表的作用。
至此，我們創建了一個分區表上的文本索引。
維護分區表上的文本索引
現在我們繼續討論如何維護文本索引，對於分區表上的文本索引有一些特殊的情況，例如在增加數據分區、數據滾入 / 滾出操作之後如何維護文本索引使之能夠捕捉到相應的變化。
更新文本索引，進行文本搜索
文本索引創建之後不能立即進行文本搜索，還需要對文本索引進行更新，其中創建之後的第一次更新也叫做初始更新。更新完成之後我們就可以進行文本搜索。在分區表上使用的命令沒有任何特別之處，如清單 7 所示。
清單 7. 更新文本索引，並進行文本搜索
db2text "update index part.multiind for text"
CTE0001 Operation completed successfully.
db2 "select * from part.multipart where contains(comment, '\"blue\"')=1"
PK YEAR VALUE COMMENT
----------- ----------- ----------- ------------
1 2001 1 blue and grey
3 2002 1 red checkered with blue
2 record(s) selected.
對分區表進行插入、修改和刪除操作
上文中提到，對分區表的修改操作將被記錄在主日志表中，插入和刪除操作將被記錄在輔助日志表中。而對於普通表（非分區表）來說，所有的變化（插入，修改，刪除）都是記錄在同一個日志表中。接下來我們對分區表上的特點進行分析，而對於普通表上的情況請讀者自行實踐和比較。
對表 part.multipart 執行若干插入、刪除和修改操作，如清單 8 所示，其中新插入的數據的 pk 分別為 6，7，8，刪除了 pk=3 的記錄，並修改了 pk=5 的記錄。在更新文本索引之前對該表進行文本搜索，被刪除的記錄（pk=3）已經不再查詢結果中，但是對於更新和新插入的數據，通過文本索引無法搜索到這些數據，也就是說文本索引還沒有捕捉到這些變化。
清單 8. 插入若干新數據
db2 "insert into part.multipart values
(6, 2001, 1, 'new blue and grey'),
(7,2001, 1, 'new yellow or red'),
(8,2002, 1, 'new red checkered with blue') "
db2 "delete from part.multipart where pk=3"
db2 "update part.multipart set comment='update
grey or green' where pk=5"
PK YEAR VALUE COMMENT
----------- ----------- ----------- ------------
1 2001 1 blue and grey
2 2001 1 yellow or red
4 2002 1 white and black
5 2003 1 update grey or green
6 2001 1 new blue and grey
7 2001 1 new yellow or red
8 2002 1 new red checkered with blue
7 record(s) selected.
//在更新文本索引之前，進行文本搜索：
db2 "select * from part.multipart where contains(comment, '\"blue\"')=1"
PK YEAR VALUE COMMENT
----------- ----------- ----------- ------------
1 2001 1 blue and grey
1 record(s) selected.
db2 "select * from part.multipart where contains(comment, '\"new\"')=1"
PK YEAR VALUE COMMENT
----------- ----------- ----------- ------------
0 record(s) selected.
bash-3.1$ db2 "select * from part.multipart where contains(comment, '\"update\"')=1"
PK YEAR VALUE COMMENT
----------- ----------- ----------- ------------
0 record(s) selected.
我們來找一找這些變化都被記錄在何處。在清單 6 中我們已經列出了相關的目錄表，並且之前這些表都是空的，現在我們來看一下其中是否有新的數據，查詢命令和結果如清單 9 所示。
清單 9. 主日志表和輔助日志表中的數據
db2 "select * from DB2EXT.TLOGIX322707"
OperaTION TIME PK01
----------- -------------------------- -----------
1 2009-10-10-02.06.00.984249 5
1 record(s) selected.
db2 "select * from SYSIBMTS.SYSTSAUXLOG_IX322707"
PK GLOBALTRANSID GLOBALTRANSTIME OperaTIONTYPE
------ ----------------- -------------- --------
6 x'000000000001DD0B' x'20091010090600518664000000' 1
7 x'000000000001DD0B' x'20091010090600529969000000' 1
8 x'000000000001DD0B' x'20091010090600529984000000' 1
3 x'00000000000212DE' x'20091010090600629240000000' -1
4 record(s) selected.
在清單 9 中，在主日志表 DB2EXT.TLOGIX322707 中有一條記錄，其列 PK01 的值為 5，這正是之前更新的那一條數據的主鍵值。在輔助日志表 SYSIBMTS.SYSTSAUXLOG_IX322707 中有 4 條記錄，其列 PK 分別為 6，7，8 和 3，分別是之前新插入的三條記錄和刪除的一條記錄對應的主鍵的值。
更新文本索引，然後再次進行文本搜索，同時查詢主日志表和輔助日志表的數據，命令和結果如清單 9 所示。
清單 9. 更新文本索引，再次進行文本搜索
db2text "update index part.multiind for text"
CTE0001 Operation completed successfully.
db2 "select * from part.multipart where contains(comment, '\"blue\"')=1"
PK YEAR VALUE COMMENT
----------- ----------- ----------- ------------
1 2001 1 blue and grey
6 2001 1 new blue and grey
8 2002 1 new red checkered with blue
3 record(s) selected.
db2 "select * from part.multipart where contains(comment, '\"new\"')=1"
PK YEAR VALUE COMMENT
----------- ----------- ----------- ------------
6 2001 1 new blue and grey
7 2001 1 new yellow or red
8 2002 1 new red checkered with blue
3 record(s) selected.
db2 "select * from part.multipart where contains(comment, '\"update\"')=1"
PK YEAR VALUE COMMENT
----------- ----------- ----------- ------------
5 2003 1 update grey or green
1 record(s) selected.
//查詢主日志表和輔助日志表：
db2 "select * from DB2EXT.TLOGIX322707"
OperaTION TIME PK01
----------- ----------- -----------
0 record(s) selected.
db2 "select * from SYSIBMTS.SYSTSAUXLOG_IX322707"
PK GLOBALTRANSID GLOBALTRANSTIME OperaTIONTYPE
----------- ----------- --------- -------------
0 record(s) selected.
在清單 9 中可以看到，更新文本索引後所有的數據變化都能夠被文本索引正確的捕捉到，而且之前主日志表和輔助日志表的數據在更新文本索引之後全部被清空。
至此我們找到了分區表上記錄數據變化的主日志表和輔助日志表，並且驗證了對分區表的修改操作將被記錄在主日志表中，插入和刪除操作將被記錄在輔助日志表中。接下來我們繼續探索分區表上特有的操作如增加分區，數據轉入 / 轉出操作之後文本索引是如何工作的。
增加一個數據分區並用 LOAD 裝入數據
對分區表增加一個分區是一個非常有用的操作，例如新的一年或者新的一個月開始，需要把新的數據放在新的分區中。
首先准備一個 del 數據文件 data.del，其中有若干條 year=2005 的數據，然後使用 ALTER TABLE...ADD PARTITION 命令增加一個分區，最後用 LOAD 工具將新數據裝載進去。數據文件內容以及使用的命令和結果如清單 10 所示。
清單 10. 增加一個數據分區並裝載數據
more data.del
11,2005,1,"Load blue and grey"
12,2005,1,"Load yellow or red"
14,2005,1,"Load white and black"
15,2005,1,"Load update grey or green"
16,2005,1,"Load new blue and grey"
17,2005,1,"Load new yellow or red"
18,2005,1,"Load new red checkered with blue"
db2 "alter table part.multipart add partition starting 2005 ending 2005"
DB20000I The SQL command completed successfully.
db2 "load from data.del of del insert into part.multipart"
Number of rows read = 7
Number of rows skipped = 0
Number of rows loaded = 7
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 7
db2 "select * from part.multipart"
PK YEAR VALUE COMMENT
----------- ----------- ----------- ------------
SQL0668N Operation not allowed for reason code "1" on table "PART.MULTIPART".
SQLSTATE=57016
