Looking at How Much Memory DB2 is Using

I used to think that if I could just get enough details into a spreadsheet, I could tell exactly how much memory DB2 would be using at any point in time. I gave up on the spreadsheet idea long ago, though when I was working with 32-bit systems and their limit of ~2GB for the most critical memory areas, I did use a simplified spreadsheet when adjusting to make sure I could keep it under the 2GB.

Thankfully, 64-bit databases mean that I am less likely to have to rob bufferpools to give memory to sorts or vice-versa. Also, I use STMM and automatic settings for many areas on my one-database->one-instance->one-server systems.

But even with these advances it is best to understand DB2′s memory model and have the real-world commands to figure out what is going on.

DB2′s Memory Model

There is a lot of good material on this, so I am not going to go into a full description here. I suggest this developerWorks article as a great read on this: http://www.ibm.com/developerworks/data/library/techarticle/dm-0406qi/

The way I think of it, there are basically three types of memory areas – ones that are allocated at the instance level, ones that are allocated at the database level, and ones that are allocated at the application level.

Instance level memory areas include:

  • AUDIT_BUF_SZ
  • MON_HEAP_SZ
  • FCM areas

Database level memory areas include:

Application level memory areas include:

  • APPLHEAPSZ
  • STMTHEAP
  • STAT_HEAP_SZ
  • Private Sort Heaps (SHEAPTHRES and SORTHEAP)
  • AGENT_STACK_SZ
  • ASLHEAPSZ
  • RQRIOBLK

Why there is no exact way to say “DB2 should be using X memory at any given time”?

It took me a while to understand this. There are two big reasons you cannot just add up parameters as configured in DBM and DB configurations and say exactly how much memory DB2 should be using at any one time.

  1. Some memory areas are allocated only as applications connect or agents are started up, and the number of connected applications can change from second to second
  2. Different memory is allocated at different times. Some areas are allocated in full on instance start or database activation or application connection. Some areas start at a minimum size and are incremented by db2 up to a configured maximum

If you work really hard at it, you can come up with an accurate range of memory allocation that DB2 should fall into at any given time based on your configuration settings, but estimating an exact number accurately is very difficult, especially for a live system with a variable number of end users.

Understanding an Individual Memory Area

Sometimes you need to approach memory from this perspective – understanding how much space the buffer pools are taking, or how much space the Package Cache is using for example.

I really do love the Info Center. I still have a complete three foot long book shelf of db2 8.1 reference books that has been replaced by the info center. Everything that was there is somewhere in the Info Center. Most memory areas correspond to a specific configuration parameter. You can look that configuration parameter up in the information center, you can figure out when it is allocated, whether it is allocated at an exact value, or if it is allocated at a minimum and then allocated up to a maximum. I have blog entries on some areas too.

Take the package cache as an example. The associated parameter is PCKCACHESZ (the one I most frequently misspell). In the Info Center, PCKCACHESZ is detailed here: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.config.doc/doc/r0000266.html

If you look at that page there is a section titled “When allocated” – that is how you can tell when this memory area is allocated.
If you read through the text, you will see details like this:
“The limit specified by the pckcachesz parameter is a soft limit. This limit can be exceeded, if required, if memory is still available in the database shared set. You can use the pkg_cache_size_top monitor element to determine the largest that the package cache has grown, and the pkg_cache_num_overflows monitor element to determine how many times the limit specified by the pckcachesz parameter has been exceeded.”

From reading the Info Center entry, you can learn that the area is allocated in full on database activation, but this particular memory area can exceed the value you set for your parameter.

To contrast, look at the info center entry for LOCKLIST: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.config.doc/doc/r0000267.html

If you read the text carfully, you will see that the lock list is an area that is allocated in full on the first database connection. BUT your lock list will absolutely not get larger than the configured value for LOCKLIST – to the point of degrading performance and finally returning an error if it gets that far.

These are very different behaviors for memory areas that are both allocated at the database level.

How to Tell How Much Memory DB2 is Using

For the most part, I will leave looking at DB2 memory from the OS side to SAs and when needed, googling. I have used top before on linux/UNIX, but it just reports one (generally large) number for all of DB2. That is not terribly helpful, and the main situation I have used it in was when someone was using it and called me and said “What is this db2sysc that is using over 50% of the memory?”, and I had to compare it back to what memory DB2 thought it was using.

top

top looks something like this:

$ top
top - 21:56:00 up 97 days,  8:28,  1 user,  load average: 0.11, 0.04, 0.01
Tasks: 206 total,   1 running, 203 sleeping,   0 stopped,   2 zombie
Cpu(s):  0.0%us,  0.3%sy,  0.0%ni, 99.3%id,  0.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   8060476k total,  7696276k used,   364200k free,     7152k buffers
Swap:  6291448k total,    75748k used,  6215700k free,  6725432k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
16756 db2inst1  20   0 17220 1344  968 R  0.3  0.0   0:00.16 top
24131 db2inst1  20   0 6294m 4.1g 3.7g S  0.3 53.6   2869:02 db2sysc

The nice thing about top is that it does show changes over time, but it can also consume system resources.

db2mtrk

When we want to dig further into memory than the one big number, I have found db2mtrk as a reliable method. It is used like this:

$ db2mtrk -i
Tracking Memory on: 2012/11/15 at 22:33:30

Memory for instance

   other       fcmbp       monh        
   17.0M       832.0K      384.0K      

$ db2mtrk -d
Tracking Memory on: 2012/11/15 at 22:33:38

Memory for database: SAMPLE

   utilh       pckcacheh   other       catcacheh   bph (4)     bph (3)     
   128.0K      44.9M       192.0K      21.9M       106.4M      217.9M      

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

   lockh       dbh         apph (52487)apph (52484)apph (52482)apph (52427)
   567.2M      34.9M       128.0K      128.0K      256.0K      192.0K      

   apph (52426)apph (52424)apph (52423)apph (52422)apph (52421)apph (45849)
   192.0K      128.0K      64.0K       192.0K      192.0K      64.0K       

   apph (45848)apph (45847)apph (45846)apph (45845)apph (45844)appshrh     
   64.0K       576.0K      64.0K       64.0K       64.0K       6.7M        

Adding up all of these numbers will give you a pretty accurate number as far as total memory usage. There are a few other useful option on db2mtrk – “db2mtrk -h” provides both a usage diagram and meanings of the abbreviations used for the various heaps.

db2top

I am a big fan of db2top for watching a database in real time. The memory screen can be useful here, and shows similar memory areas:

[/]23:28:40,refresh=60secs(0.004)                                                                             Memory                                                                            Linux,part=[1/1],DB2INST1:SAMPLEDB
[d=Y,a=N,e=N,p=ALL]                                                                                                                                                                                                      [qp=off]

                                                                             ┌──────────────┬────────────┬────────────┬────────────┬───────────┐
                                          Self Tuning......:         On      │              │         25%│         50%│         75%│	   100%│   Sort Heap........:          0
                                          Sort HWM.........:     117.6M      │Memory hwm%   │--------------------------------------------------│   Private Mem......:     109.8M
                                          Lock List........:     198.0K      │Sort Heap%    │                                                  │   Private Sort.....:          0
                                          Shared Sort......:          0      │Mem Skew%     │                                                  │   Shared Sort HWM..:          0
                                          Private Work HWM.:          0      │Pool Skew%    │                                                  │   PkgCache HWM.....:     156.0M
                                          Catalog Cache HWM:      15.1M      └──────────────┴──────────────────────────────────────────────────┘   Shared Work HWM..:          0

                                                           Memory                 Memory               Percent      Current         High Percent      Maximum # of    
                                                           Type        Level      Pool                   Total         Size    WaterMark     Max         Size Pool(s) 
                                                           ----------- ---------- -------------------- ------- ------------ ------------ ------- ------------ ------- 
                                                           Instance    DB2INST1   Monitor                0.01%       384.0K         1.1M 100.00%       384.0K       1
                                                           Instance    DB2INST1   FCMBP                  0.02%       832.0K       832.0K 100.00%       832.0K       1
                                                           Instance    DB2INST1   Other                  0.51%        17.1M        20.3M  40.44%        42.5M       1
                                                           Database    WC036D01   Applications           0.07%         2.5M         9.6M  13.89%        18.0M      18
                                                           Database    WC036D01   Database               1.04%        34.9M        34.9M  84.83%        41.1M       1
                                                           Database    WC036D01   Lock Mgr              16.88%       567.2M       595.6M 100.24%       565.8M       1
                                                           Database    WC036D01   Utility                0.00%       128.0K        66.6M   0.09%       139.4M       1
                                                           Database    WC036D01   Package Cache          1.11%        37.2M       198.3M 116.41%        32.0M       1
                                                           Database    WC036D01   Catalog Cache          0.65%        21.8M        21.8M 136.72%        16.0M       1
                                                           Database    WC036D01   Other                  0.01%       192.0K       192.0K   0.94%        20.0M       1
                                                           Database    WC036D01   BufferPool            79.24%         2.6G         3.1G 100.00%         2.6G       8
                                                           Database    WC036D01   ApplShrHeap            0.20%         6.6M        28.0M   8.56%        78.1M       1
                                                           Application WC036D01   Applications           0.07%         2.5M         9.6M  13.89%        18.0M      18
                                                           Application WC036D01   Other                  0.19%         6.2M        12.1M   0.00%       138.3G      18


















Quit: q, Help: h                                                                                        Total memory 3.2G                                                                                              db2top 2.0

Notice in the very middle at the bottom, it gives you a total number that matches up well with the other methods described here.

db2pd

db2pd also lets us look at these memory areas in detail:

$ db2pd -mempools

Database Partition 0 -- Active -- Up 43 days 10:01:38 -- Date 2012-11-15-22.37.17.338066

Memory Pools:
Address            MemSet   PoolName   Id    Overhead   LogSz       LogUpBnd    LogHWM      PhySz       PhyUpBnd    PhyHWM      Bnd BlkCnt CfgParm   
0x00000002000012B8 DBMS     fcm        74    0          0           706833      0           0           720896      0           Ovf 0      n/a       
0x0000000200001170 DBMS     fcmsess    77    65376      1401568     1687552     1401568     1572864     1703936     1572864     Ovf 3      n/a       
0x0000000200001028 DBMS     fcmchan    79    65376      259584      507904      259584      393216      524288      393216      Ovf 3      n/a       
0x0000000200000EE0 DBMS     fcmbp      13    65376      656896      925696      656896      851968      983040      851968      Ovf 3      n/a       
0x0000000200000D98 DBMS     fcmctl     73    126624     1513973     3597992     1513973     1703936     3604480     1703936     Ovf 11     n/a       
0x0000000200000C50 DBMS     monh       11    122496     187124      368640      720892      393216      393216      1179648     Ovf 29     MON_HEAP_SZ
0x0000000200000B08 DBMS     resynch    62    42816      153720      2752512     153720      262144      2752512     262144      Ovf 2      n/a       
0x00000002000009C0 DBMS     apmh       70    4512       2498164     8257536     2521972     3080192     8257536     3080192     Ovf 121    n/a       
0x0000000200000878 DBMS     kerh       52    32         1816944     4128768     1824648     2031616     4128768     2031616     Ovf 195    n/a       
0x0000000200000730 DBMS     bsuh       71    0          444750      15335424    2838531     1048576     15335424    4718592     Ovf 64     n/a       
0x00000002000005E8 DBMS     sqlch      50    0          2618755     2686976     2618755     2686976     2686976     2686976     Ovf 208    n/a       
0x00000002000004A0 DBMS     krcbh      69    0          147400      196608      147840      196608      196608      196608      Ovf 15     n/a       
0x0000000200000358 DBMS     eduah      72    44864      4608024     4608064     4608024     4653056     4653056     4653056     Ovf 1      n/a       
0x0000000210000358 FMP      undefh     59    56000      860300      22971520    860300      917504      23003136    917504      Phy 7      n/a       
$ db2pd -db sampledb -mempools

Database Partition 0 -- Database SAMPLEDB -- Active -- Up 4 days 02:22:50 -- Date 2012-11-15-22.39.41.223822

Memory Pools:
Address            MemSet   PoolName   Id    Overhead   LogSz       LogUpBnd    LogHWM      PhySz       PhyUpBnd    PhyHWM      Bnd BlkCnt CfgParm   
0x00007FFF4A0A0408 SAMPLEDB utilh      5     0          3840        146210816   69378188    131072      146210816   69861376    Ovf 16     UTIL_HEAP_SZ
0x00007FFF4A0A0178 SAMPLEDB pckcacheh  7     2772288    32405296    Unlimited   163654502   47120384    Unlimited   208011264   Ovf 4329   PCKCACHESZ
0x00007FFF4A0A0030 SAMPLEDB xmlcacheh  93    50880      145552      20971520    145552      196608      20971520    196608      Ovf 1      n/a       
0x00007FFE9ED11BB0 SAMPLEDB catcacheh  8     90464      16768378    Unlimited   16777210    22937600    Unlimited   22937600    Ovf 4400   CATALOGCACHE_SZ
0x00007FFE9ED117D8 SAMPLEDB bph        16    40800      111484416   Unlimited   111484416   111607808   Unlimited   111607808   Ovf 846    n/a       
0x00007FFE9ED11548 SAMPLEDB bph        16    81600      228203472   Unlimited   228203472   228458496   Unlimited   228458496   Ovf 1725   n/a       
0x00007FFE9ED112B8 SAMPLEDB bph        16    81600      142117728   Unlimited   146656864   142344192   Unlimited   146931712   Ovf 1065   n/a       
0x00007FFE9ED11028 SAMPLEDB bph        16    2121600    2302992752  Unlimited   2875827056  2308177920  Unlimited   2881814528  Ovf 16914  n/a       
0x00007FFE9ED10D98 SAMPLEDB bph        16    0          783104      Unlimited   783104      851968      Unlimited   851968      Ovf 5      n/a       
0x00007FFE9ED10B08 SAMPLEDB bph        16    0          520960      Unlimited   520960      589824      Unlimited   589824      Ovf 3      n/a       
0x00007FFE9ED10878 SAMPLEDB bph        16    0          389888      Unlimited   389888      458752      Unlimited   458752      Ovf 2      n/a       
0x00007FFE9ED105E8 SAMPLEDB bph        16    0          324352      Unlimited   324352      393216      Unlimited   393216      Ovf 2      n/a       
0x00007FFE9ED104A0 SAMPLEDB lockh      4     0          594782848   624623616   624536192   594804736   624623616   624558080   Ovf 1      LOCKLIST  
0x00007FFE9ED10358 SAMPLEDB dbh        2     1883264    32304071    43188224    33257585    36634624    43188224    36634624    Ovf 25517  DBHEAP    
0x00007FFFB63B1920 Appl     apph       1     0          20375       1048576     71576       131072      1048576     131072      Phy 50     APPLHEAPSZ
0x00007FFFB66F3C68 Appl     apph       1     0          176492      1048576     14066624    262144      1048576     14221312    Phy 219    APPLHEAPSZ
0x00007FFFB63B0D98 Appl     apph       1     0          10935       1048576     10935       65536       1048576     65536       Phy 18     APPLHEAPSZ
0x00007FFFB63B0C50 Appl     apph       1     0          10935       1048576     10935       65536       1048576     65536       Phy 18     APPLHEAPSZ
0x00007FFFB63B0B08 Appl     apph       1     0          358615      1048576     402159      589824      1048576     655360      Phy 4364   APPLHEAPSZ
0x00007FFFB63B09C0 Appl     apph       1     0          10935       1048576     10935       65536       1048576     65536       Phy 18     APPLHEAPSZ
0x00007FFFB63B0878 Appl     apph       1     0          10935       1048576     12919       65536       1048576     65536       Phy 18     APPLHEAPSZ
0x00007FFFB63B0730 Appl     apph       1     0          10935       1048576     10935       65536       1048576     65536       Phy 18     APPLHEAPSZ
0x00007FFFB63B0358 Appl     appshrh    20    133568     4077928     81920000    27070923    6881280     81920000    29425664    Phy 1657   application shared

There are interesting looking columns here for things like high water marks and upper bounds. See those “Unlimited” – well, they are not fully unlimited. They are limited by the amount of physical and virtual memory on the server, and possibly by things that provide upper bounds on a composite basis like DATABASE_MEMORY or INSTANCE_MEMORY.

For a slightly different way of looking at it, you can also look at it from a memory sets point of view with db2pd:

$ db2pd -db sampledb -memsets

Database Partition 0 -- Database SAMPLEDB -- Active -- Up 4 days 02:30:10 -- Date 2012-11-15-22.47.01.149499

Memory Sets:
Name         Address            Id          Size(Kb)   Key         DBP    Type   Unrsv(Kb)  Used(Kb)   HWM(Kb)    Cmt(Kb)    Uncmt(Kb) 
SAMPLEDB     0x00007FFE9ED10000 639729668   4428736    0x0         0      1      865920     3413120    4005824    3845824    160000    
  Seg0       0x00007FFE7ED10000 811827205   0          0x0         0      1      0          0          0          0          0         
  Seg1       0x00007FFE6DF5D000 811859975   0          0x0         0      1      0          0          0          0          0         
AppCtl       0x00007FFFB63B0000 639696899   16512      0x0         0      12     0          8640       165248     16192      320       
  Seg0       0x00007FFE8ED10000 770605065   0          0x0         0      12     0          0          0          0          0         
App52482     n/a                821788678   128        0x0         0      4      0          128        0          128        0         
$ db2pd -memsets

Database Partition 0 -- Active -- Up 43 days 10:11:39 -- Date 2012-11-15-22.47.18.517271

Memory Sets:
Name         Address            Id          Size(Kb)   Key         DBP    Type   Unrsv(Kb)  Used(Kb)   HWM(Kb)    Cmt(Kb)    Uncmt(Kb) 
DBMS         0x0000000200000000 1497006081  48192      0x7F9F7661  0      0      6208       18624      22144      22144      26048     
FMP          0x0000000210000000 1497038850  22592      0x0         0      0      2          0          960        22592      0         
Trace        0x0000000000000000 1496973312  39251      0x7F9F7674  0      -1     0          39251      0          39251      0         

Man, I love MON_GET…

The MON_GET table functions are by far one of my favorite recent features. I hear they come from Informix inspiration, and they are inspiring, containing data we have never had SQL access to before and getting it in a lightweight way. MON_GET_MEMORY_SETS and MON_GET_MEMORY_POOLS should correspond to the db2pd way of looking at things.

$ db2 "select substr(HOST_NAME,1,25) as HOST_NAME, substr(DB_NAME,1,10) as DBNAME, MEMORY_SET_TYPE, MEMORY_SET_ID, MEMORY_SET_SIZE, MEMORY_SET_COMMITTED, MEMORY_SET_USED, MEMORY_SET_USED_HWM from table(MON_GET_MEMORY_SET(null,null,-2)) as t with ur"

HOST_NAME                 DBNAME     MEMORY_SET_TYPE                  MEMORY_SET_ID        MEMORY_SET_SIZE      MEMORY_SET_COMMITTED MEMORY_SET_USED      MEMORY_SET_USED_HWM 
------------------------- ---------- -------------------------------- -------------------- -------------------- -------------------- -------------------- --------------------
xxxxxxxxxxxxxxxxxxxxx.xxx -          DBMS                                                0                49348                22675                19136                22675
xxxxxxxxxxxxxxxxxxxxx.xxx -          FMP                                                 2                23134                23134                  983                  983
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                                             9               115212               115212                61276               173015
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   DATABASE                                            1              4535025              3938123              3493789              4101963
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                                        12               170065                16580                 9371               169213

  5 record(s) selected.
[db2inst1@435753-svd36db01r ~]$ db2 "select substr(HOST_NAME,1,25) as HOST_NAME, substr(DB_NAME,1,10) as DBNAME, MEMORY_SET_TYPE, MEMORY_POOL_TYPE, MEMORY_POOL_ID, APPLICATION_HANDLE, EDU_ID, MEMORY_POOL_USED, MEMORY_POOL_USED_HWM from table(MON_GET_MEMORY_POOL(null,null,-2)) as t with ur"

HOST_NAME                 DBNAME     MEMORY_SET_TYPE                  MEMORY_POOL_TYPE                 MEMORY_POOL_ID       APPLICATION_HANDLE   EDU_ID               MEMORY_POOL_USED     MEMORY_POOL_USED_HWM
------------------------- ---------- -------------------------------- -------------------------------- -------------------- -------------------- -------------------- -------------------- --------------------
xxxxxxxxxxxxxxxxxxxxx.xxx -          DBMS                             FCM_LOCAL                                          74                    -                    -                    0                    0
xxxxxxxxxxxxxxxxxxxxx.xxx -          DBMS                             FCM_SESSION                                        77                    -                    -              1572864              1572864
xxxxxxxxxxxxxxxxxxxxx.xxx -          DBMS                             FCM_CHANNEL                                        79                    -                    -               393216               393216
xxxxxxxxxxxxxxxxxxxxx.xxx -          DBMS                             FCMBP                                              13                    -                    -               851968               851968
xxxxxxxxxxxxxxxxxxxxx.xxx -          DBMS                             FCM_CONTROL                                        73                    -                    -              1703936              1703936
xxxxxxxxxxxxxxxxxxxxx.xxx -          DBMS                             MONITOR                                            11                    -                    -               393216              1179648
xxxxxxxxxxxxxxxxxxxxx.xxx -          DBMS                             RESYNC                                             62                    -                    -               262144               262144
xxxxxxxxxxxxxxxxxxxxx.xxx -          DBMS                             APM                                                70                    -                    -              3080192              3080192
xxxxxxxxxxxxxxxxxxxxx.xxx -          DBMS                             KERNEL                                             52                    -                    -              2031616              2031616
xxxxxxxxxxxxxxxxxxxxx.xxx -          DBMS                             BSU                                                71                    -                    -              1376256              4718592
xxxxxxxxxxxxxxxxxxxxx.xxx -          DBMS                             SQL_COMPILER                                       50                    -                    -              2686976              2686976
xxxxxxxxxxxxxxxxxxxxx.xxx -          DBMS                             KERNEL_CONTROL                                     69                    -                    -               196608               196608
xxxxxxxxxxxxxxxxxxxxx.xxx -          DBMS                             EDU                                                72                    -                    -              4653056              4653056
xxxxxxxxxxxxxxxxxxxxx.xxx -          FMP                              MISC                                               59                    -                    -               917504               917504
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1429                    0                    0
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1480               196608               196608
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                                                                             90                    -                 1379                    0                    0
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                                                                             90                    -                 1379                    0                    0
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1379               393216              1572864
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1451               327680               327680
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1524               524288               524288
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1403               327680               393216
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1444               327680               720896
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1423               393216              1507328
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1521               262144               524288
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1502               524288               851968
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1504               655360               851968
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1503               393216               589824
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1536               458752               851968
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1418               524288               851968
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1412               393216               851968
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1519               458752              1572864
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1535               458752               655360
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1408               720896              3211264
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1449               327680               524288
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1526               262144               655360
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1410               458752               851968
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1525               393216              1507328
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1401               524288              1507328
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1446               458752               917504
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1532               589824               851968
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1409               196608               196608
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1400               393216               524288
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1437               589824               589824
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1283               393216              1507328
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1529               589824               589824
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1405               917504              3407872
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1447               655360              1572864
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1433               655360              1572864
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1428               720896              1179648
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1520               786432              1179648
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1443               589824               720896
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1430               655360               917504
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1422               655360               917504
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1445               458752              1507328
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1498               655360               655360
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1450               655360               655360
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1534               393216              1572864
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1424               196608               262144
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1538               786432              1572864
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1416               589824               589824
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1442               655360               720896
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1518               589824               720896
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1537               458752               720896
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1539               196608               196608
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1533               327680               720896
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1523               589824               589824
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1505               131072               131072
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1506               196608              4521984
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1528               655360               655360
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1527               589824               720896
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1522               589824               720896
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1417               655360               720896
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1420               524288               589824
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1508                65536              2228224
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1517               262144               262144
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1516               262144               262144
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1515               262144               262144
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1514               262144               262144
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1513               262144               262144
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1512               327680               458752
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1511               262144               262144
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1510                65536                65536
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1507               393216               393216
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1435               327680               327680
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1421               196608               196608
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1501               196608               196608
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1399               196608               524288
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1439               196608               196608
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1499               196608               196608
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1397               786432              3276800
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1414               655360               720896
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1432               589824               655360
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1415               589824               851968
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1427               458752               655360
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1169               589824               655360
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1452               655360               655360
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1500               524288               589824
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                 1426               720896              1048576
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                   16                    0                    0
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                   14                65536                65536
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                   13                65536                65536
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PRIVATE                                            88                    -                    0                65536                65536
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          USER_DATA                                          95                    -                    0              8650752             11337728
xxxxxxxxxxxxxxxxxxxxx.xxx -          PRIVATE                          PERSISTENT_PRIVATE                                 86                    -                    0             15400960             36503552
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   DATABASE                         UTILITY                                             5                    -                    -               131072             69861376
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   DATABASE                         PACKAGE_CACHE                                       7                    -                    -             39059456            208011264
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   DATABASE                         XMLCACHE                                           93                    -                    -               196608               196608
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   DATABASE                         CAT_CACHE                                           8                    -                    -             22937600             22937600
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   DATABASE                         BP                                                 16                    -                    -            111607808            111607808
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   DATABASE                         BP                                                 16                    -                    -            228458496            228458496
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   DATABASE                         BP                                                 16                    -                    -            142344192            146931712
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   DATABASE                         BP                                                 16                    -                    -           2308177920           2881814528
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   DATABASE                         BP                                                 16                    -                    -               851968               851968
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   DATABASE                         BP                                                 16                    -                    -               589824               589824
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   DATABASE                         BP                                                 16                    -                    -               458752               458752
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   DATABASE                         BP                                                 16                    -                    -               393216               393216
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   DATABASE                         LOCK_MGR                                            4                    -                    -            594804736            624558080
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   DATABASE                         DATABASE                                            2                    -                    -             36634624             36634624
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                52543                    -                65536                65536
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                52542                    -                65536                65536
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                52540                    -               262144              7274496
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                52537                    -               196608               262144
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                52532                    -               196608               196608
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                52531                    -               196608               327680
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                52530                    -               131072               131072
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                52527                    -               131072               131072
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                52526                    -                65536               131072
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                52525                    -               196608               196608
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                52487                    -               131072               131072
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                45849                    -                65536                65536
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                45848                    -                65536                65536
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                45847                    -               589824               655360
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                45846                    -                65536                65536
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                45845                    -                65536                65536
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPLICATION                                         1                45844                    -                65536                65536
xxxxxxxxxxxxxxxxxxxxx.xxx SAMPLEDB   APPLICATION                      APPL_SHARED                                        20                    -                    -              7340032             29425664

  137 record(s) selected.

Now that is pretty long, but think of the potential for easy addition and averaging and such.

I think you are going to get the best possible answer to “how much memory is DB2 using” from DB2′s perspective by using:

$ db2 "select SUM(MEMORY_POOL_USED) as TOT_MEMORY_USED from table(MON_GET_MEMORY_POOL(null,null,-2)) as t with ur"
TOT_MEMORY_USED     
--------------------
          3579117568

  1 record(s) selected.

4 comments… add one

  • Thanks a lot, it was really very helpful, as I was faing stat_hp_sz issue

    Reply
  • Ember ur my best tutor, one more superb tutorial..

    Reply
  • DB2 10.1 application heap size memory leak. The table functioned mentioned on this website helped us track the rogue connections and force them off. Thank a lot !

    Reply
  • Thanks Ember. That helped me to understand the Issues with my applheapsz that is being used by the application.

    You are the perfect tutor.

    Reply

Leave a Comment