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.
