SQL1643C on Rollforward

Posted by

I ran into this error message recently and thought I’d describe what the issue was.

The Scenario

I was building a new consolidated development database server for a client – moving away from 10 separate database servers to one (big) VM to run their development workloads. This was a Linux server, so I was setting INSTANCE_MEMORY to prevent any one instance from eating all the memory and to make it less likely that I would have a Db2 instance crash due to a Linux Out Of Memory condition killing the biggest memory consumer.

The Problem

I was restoring each database in using a redirected restore the place storage where I wanted it. The backups I was working from were all online backups. On the 8th database I restored, I got the following error on roll forward after the restore.

$ db2 "rollforward db SAMPLE to end of backup and stop overflow log path ('/db2/backups/SAMPLE')"
SQL1643C  The database manager failed to allocate shared memory because the
database manager instance memory limit has been reached.

It took me a while to track down where the issue was. I tried reducing the size of the largest bufferpool (though I didn’t think that would help, since DB2 would start up with the hidden buffer pools if my buffer pools were too large). It should have gone with the hidden buffer pools if any of the memory areas were over-sized. What I did was to set INSTANCE_MEMORY to AUTOMATIC for this one instance to get the roll forward to complete so I could look at the database memory that Db2 was allocating.

After I restarted the instance, I could get the roll forward to complete and then get details on what Db2 was allocating:

$ db2pd -dbptnmem
Database Member 0 -- Active -- Up 0 days 00:06:21 -- Date 2017-12-06-22.21.11.488636

Database Member Memory Controller Statistics

Controller Automatic: Y
Memory Limit:         59181724 KB
Current usage:        6302464 KB
HWM usage:            6302464 KB
Cached memory:        166848 KB

Individual Memory Consumers:

Name             Mem Used (KB) HWM Used (KB) Cached (KB)
========================================================
APPL-SAMPLE             160000        160000      160000
DBMS-b2cnadv1           113024        113024        6848
FMP_RESOURCES            22528         22528           0
PRIVATE                   6528          6528           0
DB-SAMPLE              6000000       6000000           0
LCL-p6116                  128           128           0
LCL-p5530                  128           128           0
LCL-p5582                  128           128           0

$ db2pd -d SAMPLE -mempools

Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:05:42 -- Date 2017-12-06-22.21.26.384984

Memory Pools:
Address            MemSet   PoolName   Id    SecondId    Overhead   LogSz       LogHWM      PhySz       PhyHWM      CfgSize     Bnd BlkCnt CfgParm
0x00007F9828009578 SAMPLE   bph        16    2           0          180959744   180959744   181534720   181534720   181534720   No  3488   n/a
0x00007F98280037A8 SAMPLE   bph        16    1           0          3478197376  3478197376  3480485888  3480485888  3480485888  No  24894  n/a
0x00007F9827FFD9D8 SAMPLE   bph        16    4099        0          784768      784768      1835008     1835008     1835008     No  5      n/a
0x00007F9827FF7C08 SAMPLE   bph        16    4098        0          522624      522624      1572864     1572864     1572864     No  3      n/a
0x00007F9827FF1E38 SAMPLE   bph        16    4097        0          391552      391552      1441792     1441792     1441792     No  2      n/a
0x00007F9827FEC068 SAMPLE   bph        16    4096        0          326016      326016      1376256     1376256     1376256     No  2      n/a
0x00007F9827FE6298 SAMPLE   shsorth    18    0           0          0           0           0           0           66191360    No  0      SHEAPTHRES_SHR
0x00007F9827FE33B0 SAMPLE   lockh      4     0           0          56110208    56110208    56164352    56164352    56229888    No  1      LOCKLIST
0x00007F9827FE04C8 SAMPLE   dbh        2     0           167904     34377208    34377208    35454976    35454976    145096704   No  850    DBHEAP
0x00007F99963833B0 Appl     apph       1     8           0          0           0           0           0           1048576     No  0      APPLHEAPSZ
0x00007F99963804C8 Appl     appshrh    20    0           0          0           0           0           0           81920000    No  0      application shared

What immediately struck me in this output is that the database memory DB-SAMPLE is an awfully round number, and on careful analysis way higher than even the high water marks add up to.

When I checked the setting for DATABASE_MEMORY, I saw:

$ db2 get db cfg for SAMPLE |grep DATABASE_MEMORY
 Size of database shared memory (4KB)  (DATABASE_MEMORY) = 1500000

Indeed, someone had set DATABASE_MEMORY to a static value. I guess this was surprising to me because these databases had not had support from a DBA, and the first 8 similar databases did not have DATABASE_MEMORY set.

The Solution

The solution here was simple – I updated DATABASE_MEMORY to be automatic:

$ db2 update db cfg for WCBSTGQA using DATABASE_MEMORY AUTOMATIC
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

I also changed INSTANCE_MEMORY back to my original limiting value.

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

One comment

  1. Indeed a good approach: Fixing INSTANCE_MEMORY ‘s (making sure they fit in servers memory to prevent Linux OOM ) and not DATABASE_MEMORY ‘s.
    Here we stopped setting DATABASE_MEMORY to whatever static value and set it to AUTOMATIC. Only INSTANCE_MEMORY is static (and well chosen).

    Some years ago on Db2 v9.7.x , I had this scary INCIDENT , 1x instance / 1x database :
    A FATAL db crash recovery, after a ‘simple’ server-power failure (luckily not on a production server), just because the DATABASE_MEMORY was fixed; the crash recovery somehow needed more memory ….
    I could increase the INSTANCE_MEMORY (plenty real memory left on server), and recycle instance, but still the crash db recovery failed , complaining about “not enough database memory” or so … Bummer.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.