索引之rebuild index --DB2

http://blog.chinaunix.net/uid-22334392-id-3365656.html中介绍了当你修改索引的时候需要注意的参数：
在create index的时候，比较中重要的两个参数：
define  用来指定DB2是否定义索引所使用的vsam文件  
defer  说明了DB2在创建索引的时候，仅在catalog中写入信息，并没有真正构建索引，即索引树
当你alter索引的时候，需要注意的是修改索引的方式不同，可能导致的索引的状态也肯能是不同的，

一个是，列已经存在，然后你修改索引，增加列，这是索引的状态被置为REBUILD-pending (RBDP) status.

一个是，表先增加列，然后将该列增加到索引中，这是索引的状态被置为 REORG-pending (AREO*) status

在表处于REBUILD-pending ，你需要进行的操作是rebuild index
在表处于REORG-pending ，你需要进行的操作就是reorg table space ,其中包括了rebuild index
这里我们对rebuild  index 进行介绍，reorg太复杂了，稍后再进行介绍
这里把背景在描述一下：
CREATE INDEX with DEFER YES
– When the index is created with DEFER YES, it should not be
available for use by queries.
– However, customer uses DEFER YES to speed up create index
Run the REBUILD INDEX utility
– As this utility drains the write claim class, no other thread can update
the table until the utility ends.
– However, the rebuild still runs for a long period of time…
? During which queries get unavailable resource on the new, not
yet rebuilt, index.
Restricted availability of applications for a very long time.
你需要知道的是，在你rebuild期间，不论shrlelve的值为啥，均会表空间（table space）导致表空间为
RO status
rebuild的语法如下：

需要注意的参数是：





下面的参数都是比较重要的参数：
DRAIN_WAIT  该值表明了该utility在drain的时候所能等待的时间
Specifies the number of seconds that REBUILD INDEX is to wait when
draining the table space or index. The specified time is the aggregate time for
objects that are to be checked. This value overrides the values that are specified
by the IRLMRWT and UTIMOUT subsystem parameters.
integer can be any integer from 0 to 1800. If you do not specify DRAIN_WAIT
or specify a value of 0, the utility uses the value of the lock timeout subsystem
parameter IRLMRWT.
RETRY integer  说明了drain失败后进行尝试的次数
Specifies the maximum number of retries that REBUILD INDEX is to attempt.
integer can be any integer from 0 to 255. If you do not specify RETRY,
REBUILD INDEX uses the value of the utility multiplier system parameter
UTIMOUT.
Specifying RETRY can increase processing costs and result in multiple or
extended periods during which the specified index, table space, or partition is
in read-only access.
RETRY_DELAY integer  指明了两次drain之间的时间间隔
Specifies the minimum duration, in seconds, between retries. integer can be any
integer from 1 to 1800.
If you do not specify RETRY_DELAY, REBUILD INDEX uses the
DRAIN_WAIT value × RETRY value.

MAXRO 最后一次log iteration时所允许的时间，单位是秒
Specifies the maximum amount of time for the last iteration of log processing.
During that iteration, applications have read-only access.

long  log 规定了在rebuild  index 是，如果出现了 long  log 应该如何进行处理，是 继续等待
（continue） ，还是terminate（中断 rebuild  index）过程，还是drain write class 
delay 规定了在 rebuild index utility 发送消息到console到执行上述操作（continue,term,drain)之间的时间间隔 
发生了long  log，而你不想执行最初设定的动作，你可以实用alter  utility  来进行modify ，参数的意思就不在进行介绍
LONGLOG
Specifies the action that DB2 is to perform, after sending a message to the
console, if the number of records that the next iteration of logging is to process
is not sufficiently lower than the number that the previous iterations processed.
This situation means that the reading of the log by the REBUILD INDEX utility
is not being done at the same time as the writing of the application log.
CONTINUE
Specifies that until the time on the JOB statement expires, DB2 is to
continue performing reorganization, including iterations of log processing,
if the estimated time to perform an iteration exceeds the time that is
specified for MAXRO.
TERM
Specifies that DB2 is to terminate the reorganization after the delay that is
specified by the DELAY parameter.
DRAIN
Specifies that DB2 is to drain the write claim class after the delay that is
specified by the DELAY parameter. This action forces the final iteration of
log processing to occur.
DELAY integer
Specifies the minimun integer between the time that REBUILD send the
LONGLOG message to the console and the time that REBUILD performs the
action the LONGLOG parameter specifies.
The integer specifies the number of seconds.
The default value is 1200.


rebuild 的执行步骤：
Execution phases of REBUILD INDEX
The REBUILD INDEX utility operates in the following phases:
UTILINIT Performs initialization and setup.
UNLOAD Unloads index entries.
SORT Sorts unloaded index entries.
BUILD Builds indexes.
SORTBLD  Sorts and builds a table space for parallel index build processing.
UTILTERM
Performs cleanup.
具体的步骤：
Online REBUILD INDEX - Processing
???? Claim data and drain index(es)
– Turn on full logging for data
– Set index(es) in RBDP and dedrain
???? Unload data isolation UR and extract keys
???? Sort keys and build index as usual
???? Bring index to currency by:
– Reading the data (Insert / Update / Delete) log records
– Extracting index (Insert / Delete) changes
– Sorting index changes by obid / key / part / lrsn
– Apply changes via DSNKCON / DSNKDEL
???? Drain data writers to finish last of log
???? Dedrain data
???? Reset RBDP for index(es)
最后需要说明的是，不同类型的索引在进行rebuild的时候
还是略有区别，下面一一进行说明：
PIindex的rebuild ：

NPIindex的rebuild


all  index 的rebuild ： 


这下子，你是不是对rebuild  index又有了一个比较新的认识，呵呵，
只是还是需要自己梳理的，呵呵 

ref: