---
title: "DB2 Utilities Performance: What’s New"
description: "DB2 performance improvements in Utilities you can find in detail here. These are useful for your projects."
url: https://srinimf.com/2022/08/28/db2-utilities-performance-5-improved-areas/
date: 2022-08-28
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2022/08/pexels-photo-4492135.jpeg?fit=1880%2C1058&ssl=1
categories: ["DB2"]
tags: ["DB2 Utilities Performance"]
type: post
lang: en
---

# DB2 Utilities Performance: What’s New

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.

Related[7 DB2 Helpful Questions And Answers](https://srinimf.com/2014/05/18/ibm-db2-zos-questions-and-answers/)

Cross Loader in [DB2 11](https://srinimf.com/2013/11/06/db2-11-advanced-featutres/) 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 tough interview questions](https://i0.wp.com/srinimf.com/wp-content/uploads/2022/10/db2-tough.png?resize=424%2C53&ssl=1)](https://srinimf.com/e-books/#db2-tough-interview-questions)

**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.

Advertisements

## 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](https://www.precisely.com/resource-center/ebooks/real-world-examples-of-ziip-offload-and-cpu-savings-with-precisely-syncsort#)** 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.

Related[DB2 INSERT’s Performance and What is Newly Added](https://srinimf.com/2015/05/19/1-more-addition-to-improve-db2-insert-performance/)

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

- [7 DB2 Helpful Questions And Answers](https://srinimf.com/2014/05/18/ibm-db2-zos-questions-and-answers/)DateMay 18, 2014
- [DB2 Large Object Data Types LOB](https://srinimf.com/2012/06/17/db2-large-object-data-types-lob/)DateJune 17, 2012
- [How to Migrate DB2 BLOB to Oracle](https://srinimf.com/2013/03/02/db2-to-oracle/)DateMarch 2, 2013

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2022%2F08%2F28%2Fdb2-utilities-performance-5-improved-areas%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2022%2F08%2Fpexels-photo-4492135.jpeg%3Ffit%3D1200%252C675%26ssl%3D1&description=DB2%20Utilities%20Performance%3A%20What%27s%20New)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2022/08/28/db2-utilities-performance-5-improved-areas/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2022/08/28/db2-utilities-performance-5-improved-areas/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2022/08/28/db2-utilities-performance-5-improved-areas/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2022/08/28/db2-utilities-performance-5-improved-areas/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%20Utilities%20Performance%3A%20What%27s%20New&body=https%3A%2F%2Fsrinimf.com%2F2022%2F08%2F28%2Fdb2-utilities-performance-5-improved-areas%2F&share=email)
-

### Like this:

Like Loading…