DB2 version 11 has capabilities of improved performance in utilities. Below, you will find those details.
1. Cross Loader
The Cross Loader utility copies data from one table to another. When copying large objects (LOBs), DB2 10 used LOB locator variables, which must be acquired and freed for each LOB of each row. LOB locator variables are necessary when the SUBSTR function is used to read portions of a LOB in random sequence, but DB2 9 introduced FETCH CONTINUE to improve the performance when a LOB is read sequentially.
Cross Loader in DB2 11 switched from LOB locator variables to FETCH CONTINUE.
2. Load parallelism
The DB2 LOAD utility has long supported parallelism when the input data is organized in multiple data sets. Using multiple data sets enables DB2 to overcome any I/O bottlenecks associated with reading the input data sets.
DB2 11 supports parallelism even when all of the data is in a single input data set. Dividing the input records into different data sets sorted by partition is still the preferred method, but if the data is originally stored as a single data set, it is no longer necessary to segregate the data by partition. Input I/O bottlenecks are not overcome, but there are many cases where input I/O is no longer a bottleneck.
3. REORG Switch Phase [Time]
Completing the SWITCH phase of online REORG with SHRLEVEL REFERENCE or CHANGE has often been cited as being of more importance than either the CPU consumption or the elapsed time of the job because sometimes the user must face the choice of either allowing the REORG job to fail or potentially impact applications to allow the SWITCH phase to complete.
DB2 11 provides relief for this problem with three changes:
- Provide drain relief for REORG on subset of data partitions by changing the current serial draining algorithm, to setting drain in control against all target parts before waiting for existing claimers to commit
- Optimize and remove unnecessary physical opens and closes
- Optimize other getpages and subprocesses including SYSLGRNX usage, catalog and directory updates, and so on.
4. Suppress NULL Key
Some applications, especially general-purpose vendor packages, create indexes where every key contains a null value. A null key is defined to be a key where every column of the key contains a null value.
DB2 11 NFM can improve insert performance of NULL entries by the option of excluding NULL rows from indexes. The CREATE INDEX DDL statement is changed to state EXCLUDE NULL KEYS, and the RUNSTATS utility collect statistics only on non-NULL value.
5. Statistics Collection
DB2 11 RUNSTATS improves zIIP offload capability of distribution statistics collection function with up to 81% CPU being zIIP-eligible. Inline statistics in DB2 11 also improve zIIP offload capability with up to 30% additional CPU offload to zIIP.
In addition to deferring the shadow index build (described above), the SORTNPSI utility keyword or the REORG_PART_SORT_NPSI subsystem parameter affect whether inline statistics will be collected for NPSIs during part level REORG.
Related
Srinimf 
You must be logged in to post a comment.