Technical Blog Post
Abstract
Allocated Shared Sort Heap size is reported in MON_GET_DATABASE, NOT in database snapshot
Body
There are several ways you can check the current size of "Shared Sort Heap". Let me list some of them below.
1. database snapshot
2. db2mtrk
3. sysibmadm.snapdb
4. MON_GET_DATABASE table function
When you check the data for each, however, you will find out that #1, #2 and #3, #4 have the different value. Let me show actual data from a test machine.
--------------------------------
1. database snapshot
$ db2 get snapshot for database on tstv105 | gnugrep -A3 "Shared Sort Heap"
Memory Pool Type = Shared Sort Heap
Current size (bytes) = 2359296
High water mark (bytes) = 2621440
Configured size (bytes) = 376963072
2. db2mtrk
$ db2mtrk -i -d -v | grep "Shared Sort Heap"
Shared Sort Heap is of size 2359296 bytes
3. sysibmadm.snapdb
$ db2 "select snapshot_timestamp, SORT_SHRHEAP_ALLOCATED from sysibmadm.snapdb"
SNAPSHOT_TIMESTAMP SORT_SHRHEAP_ALLOCATED
-------------------------- ----------------------
2017-08-07-23.46.55.309671 4
1 record(s) selected.
4. MON_GET_DATABASE table function
$ db2 "select SORT_SHRHEAP_ALLOCATED from table(MON_GET_DATABASE(-2))"
SORT_SHRHEAP_ALLOCATED
----------------------
4
1 record(s) selected.
--------------------------------
As you can see, database snapshot and db2mtrk show 2359296 bytes while sysibmadm.snapdb and MON_GET_DATABASE report 4 pages(16KB).
After discussion with area expert team, we found that the difference between the outputs from db2mtrk and sysibmadm.snapdb is because of many low-level details regarding how they gather the data. And we confirmed that value from MON_GET_DATABASE(sysibmadm.snapdb) should be the one to use in the future.
As written in the following knowledge center link, The SNAPDB administrative view and SNAP_GET_DB table function are deprecated and have been replaced by the MON_GET_TABLE table function. Thus, please use the table function to see your shared sort heap size.
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
UID
ibm13286227