Parameter Wednesday: DB CFG UTIL_HEAP_SZ

DB2 Version This Was Written For

9.7

Parameter Name

UTIL_HEAP_SZ

Where This Parameter Lives

DB CFG

How To Check Value

> db2 get db cfg for sample |grep UTIL_HEAP_SZ
 Utilities heap size (4KB)                (UTIL_HEAP_SZ) = 70982

OR

> db2 "select name, substr(value,1,12) value, substr(deferred_value,1,12) deferred_value from sysibmadm.dbcfg where name='util_heap_sz' with ur"

NAME                             VALUE        DEFERRED_VALUE
-------------------------------- ------------ --------------
util_heap_sz                     70982        70982

Description

The utility heap is used by – surprise, surprise – utilities. Load, Backup, Restore, Redistribute, Compression Dictionary Creation, and Online Index reorg operations each use memory from this heap.

Impact

Can drastically impact the performance of utilities.

Default

5000

Range/Values

16-524,288

Recycle Required To Take Effect?

No

Can It Be Set To AUTOMATIC?

No, there is no AUTOMATIC option for this parameter.

How To Change It

db2 update db cfg for sample using UTIL_HEAP_SZ 7000

Rule of Thumb

Start with the default, and increase if you have performance problems with utilities or receive errors.

Tuning Considerations

You can track the allocated/used space using db2mtrk. In the example below, the utility heap is the first one reported in the upper left.

> db2mtrk -d
Tracking Memory on: 2012/08/01 at 03:10:41

Memory for database: SAMPLE

   utilh       pckcacheh   other       catcacheh   bph (4)     bph (3)
   192.0K      36.1M       192.0K      18.2M       1.2G        54.1M

   bph (2)     bph (1)     bph (S32K)  bph (S16K)  bph (S8K)   bph (S4K)
   414.1M      1.4G        832.0K      576.0K      448.0K      384.0K

   lockh       dbh         apph (56234)apph (56209)apph (55221)apph (54716)
   62.8M       35.9M       256.0K      64.0K       64.0K       192.0K

   apph (54205)apph (53697)apph (53195)apph (52173)apph (51151)apph (5977)
   128.0K      128.0K      192.0K      128.0K      192.0K      128.0K

   apph (5599) apph (25678)apph (901)  apph (832)  apph (45)   apph (44)
   128.0K      128.0K      64.0K       128.0K      64.0K       64.0K

   apph (43)   apph (42)   apph (41)   apph (40)   apph (39)   apph (38)
   64.0K       64.0K       64.0K       512.0K      64.0K       64.0K

   apph (37)   appshrh
   64.0K       6.1M

You can also get this information through the SYSIBMADM views:

> db2 "select SNAPSHOT_TIMESTAMP, POOL_ID, POOL_CUR_SIZE, POOL_WATERMARK, POOL_CONFIG_SIZE, DBPARTITIONNUM from sysibmadm.snapdb_memory_pool where DB_NAME = 'WC005D01' and pool_id='UTILITY' with ur
"

SNAPSHOT_TIMESTAMP         POOL_ID        POOL_CUR_SIZE        POOL_WATERMARK       POOL_CONFIG_SIZE     DBPARTITIONNUM
-------------------------- -------------- -------------------- -------------------- -------------------- --------------
2012-08-01-03.24.02.128583 UTILITY                      196608            101253120            290783232              0

Note that while the pool size is specified in 4K pages, the numbers in the the above two commands are in Bytes/KB.

Now here’s part of the interesting part. The space from this heap is allocated as a percentage of the remaining heap in the following ways:

Backup 50%
Restore 100%
Load 25%
Redistribute   50%

Compression Dictionary creation takes only about 10MB to build, and online index reorganization uses this area to track transactions.
 
The important part of that is not the percentages – it’s what the percentages are of. They’re of the REMAINING available heap space – so each additional operation gets less space. This would especially show up if you were running a number of loads at the same time – you would see them get slower and slower with each additional load added.
 
There are some things you can do to get around these allocations. The data buffer option on the load or redistribute utilities would help you ensure consistent space. Similar ends could be achieved by the buffer size on backup and restore. But keep in mind that even if you use these methods to get around the allocations, all that memory still comes from the Utility heap, and if you exhaust it, you won’t have memory available for additional operations.

Related Error Messages

War Stories From The Real World

Link To Info Center

http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/r0000330.html

Related Parameters

You may also like...

5 Responses

  1. harish pathangay says:

    consider restore utility is overwriting an existing db, then i can update db cfg of existing db before starting restore utility there by getting increased performance.

    how will you use this parameter(util heap) if restore is creating new database?
    will restore utility refer db cfg inside the backup image during restore process?
    thanks.

    • Ember Crooks says:

      You can always create a database and set the configuration before restoring into it. Restore will use the cfg of the database it is restoring into, if there is one. If restoring to a new database, it will use what is in the backup image.

      • harish pathangay says:

        Hi,
        Thanks for your inputs and suggestions.

        Question in General with Restore Utility:
        Will Restore Utility use the DB CFG parameters stored inside the backup image file during Restore process or will it apply the DB CFG after restoring complete at the end phase?

        My Observation as a test case:
        Test Case 1:
        create data base db1, update db cfg with num_ioserver set to 6, activate and deactivate db
        backup data base
        drop data base
        restore database db1 – utilizes 255 pre-fetchers during restore process and crashes the instance. (db2pd -edus reveals db2pfchr threads)
        terminate all process,do instance restart.

        create db1 update db cfg with num_ioservers 6
        restore database db1 from backup image – utilizes only 6 pre-fetchers.(db2pd -edus reveals db2pfchr threads)

        my observation from test case:
        when restore process is creating a new database – it is not referring the DB CFG inside the backup image.
        when restore process is overwriting an existing database – it is using the correct num_ioserver configuration, because the db is active with cfg parameters applied.

        if restore utility creates database it is not referring DB CFG upfront,it is applying the DB cfg parameters after doing the restore process at the end phase?

        Win 7,DB2 Express C 10.5,32 bit,1GB RAM,Intel Dual Core CPU 1.6GHz

        Let me know your inputs.

        Thanks,
        Harish Pathangay

        • Ember Crooks says:

          I don’t know when the db cfg is applied. From your tests, it appears to be at the end of the restore.

  1. September 22, 2015

    […] be aware of the usage of the utility heap by other utilities – LOAD, REDISTRIBUTE, etc. See my entry on the utility heap for more […]

Leave a Reply

Your email address will not be published. Required fields are marked *