Db2

 View Only

 Db2 11.5.8 Windows - online bkp fails with SQL2009C

Samuel Pizarro's profile image
Samuel Pizarro posted Sat February 15, 2025 09:15 AM

live system,  DB2 standard edition on windows.  

Backup online is failing with SQL2009C ... 

I have a fixed and huge util_heap_sz (150000 4KB pages) configure that should be enough for this..   No other utilities are running when bkp is initiated..  but it still fails. 

tested fully auto, without specifying buffers and parallelism,  and setting a smaller parallelism and buffers, but it still fails.  

fully auto:  it sets parallelism to 15,  it tries to allocate 30 buffers of 1601 pages..  

and on diag this is show...  

Out of memory failure for Backup/Restore/Util Heap (UTIL_HEAP_SZ) on node 0.
Requested block size           : 6557696 bytes.
Physical heap size             : 261685248 bytes.
Configured heap size           : 614400000 bytes.
Unreserved memory used by heap : 0 bytes.
Unreserved memory left in set  : 1631911936 bytes.

which makes no sense ... 

--- 
If I reduce parallelism on purpose,  to 3 , it tries to allocate 6 buffers of 4097 pages (which is still nothing compared to the util_heap_sz) 

but the,  it still fails with same SQLcode and this on diag: 

2025-02-15-10.03.44.197000-180 E16180642F654        LEVEL: Error
PID     : 4792                 TID : 2996           PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000           DB   : NOXGR
APPHDL  : 0-14373              APPID: *LOCAL.DB2.250215130340
AUTHID  : DB2ADMIN             HOSTNAME: nox01-november
EDUID   : 2996                 EDUNAME: db2agent (NOXGR) 0
FUNCTION: DB2 UDB, database utilities, sqluxAllocateTransferBuffers, probe:1293
MESSAGE : SQL2009C  A backup or restore failed due to inadequate memory
          resources.
DATA #1 : <preformatted>
Failure allocating buffer 1 of 6 IO buffers,
of size 4097pages
.

2025-02-15-10.03.44.213000-180 E16181298F645        LEVEL: Error
PID     : 4792                 TID : 2996           PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000           DB   : NOXGR
APPHDL  : 0-14373              APPID: *LOCAL.DB2.250215130340
AUTHID  : DB2ADMIN             HOSTNAME: nox01-november
EDUID   : 2996                 EDUNAME: db2agent (NOXGR) 0
FUNCTION: DB2 UDB, database utilities, sqluxAllocateTransferBuffers, probe:1297
MESSAGE : ZRC=0x8B0F0005=-1961951227=SQLO_NOMEM_UTILH
          "No memory available in 'Backup/Restore/Util Heap'"
          DIA8327C No memory available in the utility heap.

these are my db-cfg relevant settings: 

 Self tuning memory                    (SELF_TUNING_MEM) = ON
 Size of database shared memory (4KB)  (DATABASE_MEMORY) = 3700000
 Database memory threshold               (DB_MEM_THRESH) = 100
 Max storage for lock list (4KB)              (LOCKLIST) = AUTOMATIC(66048)
 Percent. of lock lists per application       (MAXLOCKS) = AUTOMATIC(97)
 Package cache size (4KB)                   (PCKCACHESZ) = AUTOMATIC(524288)
 Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(102568)
 Sort list heap (4KB)                         (SORTHEAP) = AUTOMATIC(20513)

 Database heap (4KB)                            (DBHEAP) = AUTOMATIC(3567)
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 300
 Log buffer size (4KB)                        (LOGBUFSZ) = 2150
 Utilities heap size (4KB)                (UTIL_HEAP_SZ) = 150000
 SQL statement heap (4KB)                     (STMTHEAP) = AUTOMATIC(8192)
 Default application heap (4KB)             (APPLHEAPSZ) = AUTOMATIC(256)
 Application Memory Size (4KB)             (APPL_MEMORY) = AUTOMATIC(40000)
 Statistics heap size (4KB)               (STAT_HEAP_SZ) = AUTOMATIC(10000)

Observation: in the past,  utli_heap_sz was set to automatic as well,  but we started facing those memory allocation errors too often..  which was preventing us to perform online backups.   So, we decide to set it to a big fixed size as sometimes auto-runstats is also triggered , while bkp is running..  and this set resolved the problem by that time..  (1 year ago)  and we were good since then.  But now, issue is back..   

Any assistance would be much appreciated..  Unfortunately I can't open an IBM official case for this.  Customer was not able to continue paying the yearly support.  

Mark Barinstein's profile image
Mark Barinstein posted Mon February 17, 2025 06:24 AM

Here is the description of possible reason of the problem.
Backup or restore Fais with SQL2009C inadeuqate memory

So, it seems, that it's memory fragmentation, but you already have DB_MEM_THRES = 100...
I'd suggest to set DATABASE_MEMORY to AUTOMATIC...

Samuel Pizarro's profile image
Samuel Pizarro posted Mon February 17, 2025 07:32 AM

Hi @Mark Barinstein 

Thanks for your feedback..    

So,  OUCH!  this is really, really bad.  I thought the util_heap_sz when set to a fixed size was pre-allocated and pinned memory, so it can be used when needed..  

Utility heap is a very important memory heap. I never thought it would be allocated dynamically like this.  This is so undesirable.  

You have a big pool (db-memory)  and several inner heaps compeeting each other from that..  several set to automatic,  it's logical that they will become fragmented over the time.  and now,  one particular heap requires contiguous segments..  and it can't work,  even haven available memory (set ) to it..   I've fixed util_heap to a big size,  exactly to avoid this situation.  I want be able to take backups.  reorgs,  runstats,  etc .. without problems.. 

Yes,  I have a big up time, but not that big (240 days only) ..  and the intention is to keep the database up as much as possible.   

Now, being forced to deactivate the db just to be able to take an ONLINE backup is very bad !!

Isn't there any special register that I can set that will instruct db2 engine to pre-allocate all utlity_heap at db activation time and never free it up ? 

About setting database_memory back to automatic..  it was like that in the past..   but we also failed the same problem, and with db_mem set to auto,  it worst,  as the time passes,  the overall memory utilization increases reaching up to a point of using all system memory..    (STMM is enabled.  for BPS, sorts,  etc.. ) 

Then the side effect is worst,  as the OS becomes unresponsive due lack of free memory..   I won't enable db_memory back to auto,,  it was worst with that.. 


IBM should review this approach of allocating util_heap on demmand..  or at least give us an option to control that by using a  register variable. 

Mark Barinstein's profile image
Mark Barinstein posted Mon February 17, 2025 09:59 AM

You may try the following with your fixed DATABASE_MEMORY to fight with memory fragmentation.

db2set DB2_LARGE_PAGE_MEM=DB

I'm not sure, but the instance restart may be required even with the "DB" value.

There is a contradictive info in the doc about DB2_ALLOCATION_SIZE.
It's market as deprecated, but from the other hand it's still suggested to set it to a very high value like 256MB for Windows (I wouldn't do it).

Samuel Pizarro's profile image
Samuel Pizarro posted Mon February 17, 2025 03:48 PM

Thanks @Mark Barinstein for your assistance and feedbacks. 

Will evaluate those carefully. 

Appreciate your help very much. 

Regards

Sam