cancel
Showing results for 
Search instead for 
Did you mean: 

DIA8300C A memory heap error has occurred.

rupali_karbhari3
Active Contributor
0 Kudos

Hello,

I am new to DB2 and facing an issue where transaction log of SAP system getting full.

SAP system goes into stuck situation with error SQL0964C

db2diag.log

2015-09-24-14.00.01.172344+060 I5462444226E588       LEVEL: Warning
PID     : 15831                TID : 140346168174336 PROC : db2sysc 0
INSTANCE: db2gd2               NODE : 000            DB   : GD2
APPHDL  : 0-1573               APPID: *LOCAL.db2gd2.150924130000
AUTHID  : DB2GD2               HOSTNAME: HS1GD2COMB
EDUID   : 112                  EDUNAME: db2agent (GD2) 0
FUNCTION: DB2 UDB, SQO Memory Management, SqloMemController::getPartitionStats, probe:16
DATA #1 : <preformatted>
Aggregated stats for 6 Local Applications Sets
Current size : 768 KB
HWM : 768 KB
Reserved : 0 KB

2015-09-24-14.00.01.172509+060 E5462444815E767       LEVEL: Warning
PID     : 15831                TID : 140346168174336 PROC : db2sysc 0
INSTANCE: db2gd2               NODE : 000            DB   : GD2
APPHDL  : 0-1573               APPID: *LOCAL.db2gd2.150924130000
AUTHID  : DB2GD2               HOSTNAME: HS1GD2COMB
EDUID   : 112                  EDUNAME: db2agent (GD2) 0
FUNCTION: DB2 UDB, SQO Memory Management, sqloMemLogPoolConditions, probe:30
DATA #1 : <preformatted>
Out of memory failure for Private Heap on node 0.
Requested block size           : 1269923848 bytes.
Physical heap size             : 646381568 bytes.
Configured heap size           : 1099511627776 bytes.
Unreserved memory used by heap : 0 bytes.
Unreserved memory left in set  : 0 bytes.

2015-09-24-14.00.01.172658+060 I5462445583E623       LEVEL: Error
PID     : 15831                TID : 140346168174336 PROC : db2sysc 0
INSTANCE: db2gd2               NODE : 000            DB   : GD2
APPHDL  : 0-1573               APPID: *LOCAL.db2gd2.150924130000
AUTHID  : DB2GD2               HOSTNAME: HS1GD2COMB
EDUID   : 112                  EDUNAME: db2agent (GD2) 0
FUNCTION: DB2 UDB, WLM, sqlrwExpandableBuffer::addElement, probe:10
CALLED  : DB2 UDB, WLM, sqlrwExpandableBuffer::addElement
RETCODE : ZRC=0x8B0F0000=-1961951232=SQLO_NOMEM "No Memory Available"
          DIA8300C A memory heap error has occurred.

2015-09-24-14.00.01.174464+060 I5462446207E630       LEVEL: Severe
PID     : 15831                TID : 140346168174336 PROC : db2sysc 0
INSTANCE: db2gd2               NODE : 000            DB   : GD2
APPHDL  : 0-1573               APPID: *LOCAL.db2gd2.150924130000
AUTHID  : DB2GD2               HOSTNAME: HS1GD2COMB
EDUID   : 112                  EDUNAME: db2agent (GD2) 0
FUNCTION: DB2 UDB, WLM, sqlrwGetWLMTableFunctionMergedResult, probe:60
CALLED  : DB2 UDB, WLM, sqlrwGetWLMTableFunctionMergedResult
RETCODE : ZRC=0x8B0F0000=-1961951232=SQLO_NOMEM "No Memory Available"
          DIA8300C A memory heap error has occurred.

***************

db2sid.nfy file

2015-09-24-07.55.28.110137   Instance:db2gd2   Node:000
PID:6828(db2agent (GD2) 0)   TID:2290083584   Appid:10.115.226.45.19394.150622102548
data protection services  sqlpgResSpace Probe:2860   Database:GD2

ADM1823E  The active log is full and is held by application handle "0-53126".
Terminate this application by COMMIT, ROLLBACK or FORCE APPLICATION.

2015-09-24-07.55.28.116868   Instance:db2gd2   Node:000
PID:6828(db2agent (GD2) 0)   TID:2290083584   Appid:10.115.226.45.19394.150622102548
data protection services  sqlpgResSpace Probe:2860   Database:GD2

ADM1823E  The active log is full and is held by application handle "0-53126".
Terminate this application by COMMIT, ROLLBACK or FORCE APPLICATION.

2015-09-24-07.55.30.320545   Instance:db2gd2   Node:000
PID:6828(db2agent (GD2) 0)   TID:1669326592   Appid:10.115.226.45.35600.150924065529
data protection services  sqlpgResSpace Probe:2860   Database:GD2

ADM1823E  The active log is full and is held by application handle "0-53126".
Terminate this application by COMMIT, ROLLBACK or FORCE APPLICATION.

2015-09-24-07.58.28.341556   Instance:db2gd2   Node:000
PID:15807(db2stop2)   TID:1837209376   Appid:none
base sys utilities  DB2StopMain Probe:911

ADM7514W  Database manager has stopped.

2015-09-24-07.58.34.814843   Instance:db2gd2   Node:000
PID:15821(db2star2)   TID:3622008608   Appid:none
license manager  sqllcRequestAccess Probe:0

Message number 0 is unavailable.

2015-09-24-07.58.36.349073   Instance:db2gd2   Node:000
PID:15821(db2star2)   TID:3622008608   Appid:none
base sys utilities  DB2StartMain Probe:911

ADM7513W  Database manager has started.

2015-09-24-07.59.27.654599   Instance:db2gd2   Node:000
PID:15831(db2agent (GD2) 0)   TID:1660937984   Appid:10.115.226.45.35621.150924065917
database monitor  sqmSqlTarget::validateTable Probe:60   Database:GD2

ADM2014W  The Event Monitor "SAP_LOCK_EVENTS" detected on table
"LOCK_PARTICIPANTS" (ID "2") that the size of the column "LOCK_OBJECT_TYPE_ID"
is smaller than the default size of "2".  Therefore, contents will be truncated
to the user specified size.

2015-09-24-08.00.02.359733   Instance:db2gd2   Node:000
PID:15831(db2agent (GD2) 0)   TID:1660937984   Appid:*LOCAL.db2gd2.150924065948
database monitor  sqmSqlTarget::validateTable Probe:60   Database:GD2

ADM2014W  The Event Monitor "SAP_LOCK_EVENTS" detected on table
"LOCK_PARTICIPANTS" (ID "2") that the size of the column "LOCK_OBJECT_TYPE_ID"
is smaller than the default size of "2".  Therefore, contents will be truncated
to the user specified size.
HS1GD2COMB:db2gd2 63>


Requesting your urgent help upon this.

FYI: DB2 at 10.5.0.5 version.

Thank You,

Rupali

Accepted Solutions (1)

Accepted Solutions (1)

rupali_karbhari3
Active Contributor
0 Kudos

Hi All,

just to update you , issue has been resolved by killing fmp process and system reverted to normal status now.

Hope to not get such issue in future.

Best Regards,

Rupali

Answers (2)

Answers (2)

0 Kudos

Hi Rupali, just gone through the error messages you posted & would rather have you tackle the  ""DIA8300C A memory heap error has occurred" aspect before the ""Log Full" error message.

#Observations: From your  db2sid.nfy file, I can see the core error messages...

(A) ADM1823E  The active log is full and is held by application handle "0-53126".

(B) Terminate this application by COMMIT, ROLLBACK or FORCE APPLICATION

(C) RETCODE : ZRC=0x8B0F0000=-1961951232=SQLO_NOMEM "No Memory Available"                         

                         DIA8300C A memory heap error has occurred. GO r the Memory contention problem first, i.e (C) above. 

Yr:DB SID = GD2


1)  General Questions:  Did you recently carry out a FULL ONLINE Database Backup?  

               - a. What is the current setting for instance memory and database memory?  

               - b. Is STMM ON? If yes, are all associated prerequisite parameter settings well set? - LOCKLIST etc.  

               - c. Can you still connect to the DB using  "db2 connect"?  

               - d. Does the instance memory increase until it crashes?

#Diagnosis:


Check the db2diag.log for such error messages:


MESSAGE : ZRC=0x8B0F0000=-1961951232=SQLO_NOMEM "No Memory Available"          

                    DIA8300C A memory heap error has occurred.


OR              Insufficient INSTANCE_MEMORY to allocate new memory set.          

                   DATA #3 : numChunks, PD_TYPE_NUM_CHUNKS, 4 bytes


The above memory error if found indicates that there is insufficient INSTANCE_MEMORY to allow for new memory set allocation.

Here, there's a need to increase INSTANCE_MEMORY further.



2) You should be looking closely at these parameter settings for your Database GD2:


STMM = YES or NO?

DB_MEM Size = ???

Instance Mem Size =??? '

Database monitor heap size (4KB)          (MON_HEAP_SZ) = ???

Java Virtual Machine heap size (4KB)    (JAVA_HEAP_SZ) = 2048 --- > usually

Audit buffer size (4KB)                  (AUDIT_BUF_SZ) = 0

Size of instance shared memory (4KB)  (INSTANCE_MEMORY) = ????

Agent stack size                      (AGENT_STACK_SZ) = 128  ----> Usually

Sort heap threshold (4KB)                  (SHEAPTHRES) = 0


## 'Action Plan'':


3) Instead of going through the very large db2support.zip file simply use the script below if you have

http access to the DB or request the customer to provide the output from this script....


SCRIPT:

***********************************************************************

db2pd -dbmcfg -file db2pd.dbmcfg.out

db2pd -database PCO -dbcfg -file

db2pd.dbcfg.out db2pd -dbptnmem -file

db2pd.dbptnmem.out

db2pd -database PCO -inst -memset -file

db2pd.mems.out

db2pd -database PCO -inst -mempools -file

db2pd.memp.out

db2pd -database PCO -inst -memblocks all -file

db2pd.lsmemb.out


************************************************************************


49 Better still IS to capture a recurrence of the Issue, you can use this simple script  

(-If DB is running on AIX or Linux) if on Windows do not bother:


Note: Please if  practically possible use the script below especially when issue is re-occuring

--- > Run the following script as db2 user: - Or have the customer contact person do so: ************************************************************************

dbname=GD2 for i in 1 2 3 4 5 6 7 8 9 10

do

db2pd -osinfo -version > osinfo.v.$i

db2pd -db $dbname -inst -memsets > mems.$i

db2pd -db $dbname -inst -mempools > memp.$i

db2pd -db $dbname -inst -memblocks all > memb.$i

db2 get snapshot for applications on $dbname > appsnap.$i

db2 get snapshot for db on $dbname > dbsnap.$i

db2pd -db $dbname -dbptnmem > dbptnmem.$i

sleep 90 done


************************************************************************

---- > (ii) Thereafter, Restart the Database & cross-check the db2diag.log output for errors.


Basically, the core info needed from the script are actually: 


db2pd -db -inst -memsets 

db2pd -db -inst -mempools 

db2pd -db -inst -memblocks all 

db2pd -db -dbptnmem  


4) If their is only one system on your server landscape, we would recommend that the instance memory be set to about 80% of the total available memory on the machine.


5) If you have DB connection and the DB_MEM_THRESH parameter is not = 100, you could choose to do the following:


db2 update db cfg using DB_MEM_THRESH 100 (Ref: SAP Note#1329179)


contact me with the output of above script & we'd take it from there.

Sorry for earlier text malfunctions. 06.10.2015 Message was edited by: Samuel Ifeanyi Ogbogbo

former_member213250
Active Participant
0 Kudos

Hello Rupali

Error in here is straight forward.

===================

===================


Please check available memory resources on your system

You can run " db2pd -dbptnmem

this command should give you current usage and HighwaterMark memory used.
eg of above command output will be like below.

=============================================

Database Member 0 -- Active -- Up 6 days 00:59:55 -- Date 2015-09-24-15.47.43.22                                                                                                                                                                                                                             2329

Database Member Memory Controller Statistics

Controller Automatic: N

Memory Limit:         2800000 KB    (1)

Current usage:        2162496 KB    (2)

HWM usage:            2767616 KB  (3)

Cached memory:        393344 KB

Individual Memory Consumers:

Name             Mem Used (KB) HWM Used (KB) Cached (KB)

========================================================

APPL-DB6                 27904        160000        9024

DBMS-db2db6             118976        166976         640

FMP_RESOURCES            22528         22528           0

PRIVATE                  74496        513600           0

DB-DB6                 1918336       2516928      383680

LCL-p12463                 128           128           0

LCL-p20300                 128           128           0

====================================================

1) Memory limit is  INSTANCE_MEMORY in the database manager configuration, upper limit of database memory since V9.5 INSTANCE_MEMORY is the amount of memory used from db2sysc. But there are other processes outside of db2sysc.

2) Current usage, it can happen that INSTANCE_MEMORY becomes bigger than the configured limit.

3) HWM Usage is max used memory value.

Based on these figures you need to consider the options to increase INSTANCE_MEMORY or other database memory allocations.

Further more you can refer to IBM documentation based on your current DB version.

Hope it helps

Thanking you
Regards
Venkat.

rupali_karbhari3
Active Contributor
0 Kudos

Thank you Venkat for your quick response.

here is the output of this-

HS1GD2COMB:db2gd2 55> db2pd -dbptnmem

Database Member 0 -- Active -- Up 5 days 00:40:53 -- Date 2015-09-29-08.39.27.050436

Database Member Memory Controller Statistics

Controller Automatic: N
Memory Limit:         9236480 KB
Current usage:        6722048 KB
HWM usage:            9214848 KB
Cached memory:        662464 KB

Individual Memory Consumers:

Name             Mem Used (KB) HWM Used (KB) Cached (KB)
========================================================
APPL-GD2                 39424        160000       15424
DBMS-db2gd2             104640        109696          64
FMP_RESOURCES            22528         22528           0
PRIVATE                  85632       2011072           0
DB-GD2                 6469440       8361344      646976
LCL-p15853                 128           128           0
LCL-p15853                 128           128           0
LCL-p15853                 128           128           0

also i have checked INSTANCE_MEMORY parameter which is set to

Global instance memory (4KB)          (INSTANCE_MEMORY) = 2309120

Member instance memory (4KB)                            = GLOBAL

Can you please help in incresing this parameter along with value.

do we need to set it to automatic?

Please help.

Thank you,

Rupali

former_member182505
Contributor
0 Kudos

Hello Rupali

if transaction log of SAP system getting full then defiantly  it will stuck,

untill unless application hander wil not get terminated or killed,

i dont think its related to memory ,

do you know the procedure to handle the log full?

or do you have any alert system to avoid this log full,?

Check below note may help you

  495297 - DB6: Monitoring transaction log

2141933 - DB6: How to initially trouble shoot transaction log full issue

DB6: Use of infinite logging or secondary log files

Thanks

Sadiq

rupali_karbhari3
Active Contributor
0 Kudos


Hi Saqib,

Thank you for your response and as correctly gussed , TLog full situation has occured again.

As per SAP note # 2141933, here is the data analysis -

****************

Log space available to the database (Bytes)= 40068006
Log space used by the database (Bytes)     = 3965824794
Appl id holding the oldest transaction     = 20069

Application handle                         = 20069

application handle                         = 20069
Application status                         = UOW Executing
Status change time                         = 09/27/2015 21:15:00.177056
Application code page                      = 1208
Application country/region code            = 1
DUOW correlation token                     = *LOCAL.db2gd2.150927201500
Application name                           = DB2ATS
Application ID                             = *LOCAL.db2gd2.150927201500
Sequence number                            = 00009
TP Monitor client user ID                  = DB2GD2
TP Monitor client workstation name         = HS1GD2COMB
TP Monitor client application name         = DB2ATS-Task: SAPTOOLS.DBH_LATCHWAIT_C
OLLECT
TP Monitor client accounting string        =
Is this a System Application               = YES

Connection request start timestamp         = 09/27/2015 21:15:00.152467
Connect request completion timestamp       = 09/27/2015 21:15:00.152779
Application idle time                      =
CONNECT Authorization ID                   = DB2GD2
Client login ID                            = db2gd2
Configuration NNAME of client              = HS1GD2COMB
Client database manager product ID         = SQL10055
Process ID of client application           = 15853
Platform of client application             = LINUXAMD64
Communication protocol of client           = Local Client

Snapshot timestamp                         = 09/30/2015 08:07:35.360099
Coordinator agent process or thread ID     = 213
UOW log space used (Bytes)                 = 41677
Previous UOW completion timestamp          = 09/27/2015 21:15:00.173785
UOW start timestamp                        = 09/27/2015 21:15:00.173812
UOW stop timestamp                         =
UOW completion status                      =

Most recent operation                      = Execute
Most recent operation start timestamp      = 09/27/2015 21:15:00.177057
Most recent operation stop timestamp       =

Dynamic SQL statement text:
INSERT INTO SAPTOOLS.DBH_STG_LATCHWAIT(        COLLECT_MODE,        SNAPSHOT_TIMES
TAMP,        TIME_PERIOD,        LATCH_NAME,        MEMBER,        TOTAL_EXTENDED_
LATCH_WAITS,        TOTAL_EXTENDED_LATCH_WAIT_TIME,        REAL_HOST_NAME) SELECT
C.COLLECT_MODE AS COLLECT_MODE,        C.SNAPSHOT_TIMESTAMP AS SNAPSHOT_TIMESTAMP,
        SYSFUN.TIMESTAMPDIFF(2, CHAR(C.SNAPSHOT_TIMESTAMP - P.SNAPSHOT_TIMESTAMP))
AS TIME_PERIOD,        C.LATCH_NAME AS LATCH_NAME,        C.MEMBER AS MEMBER,
    CASE WHEN (C.TOTAL_EXTENDED_LATCH_WAITS < P.TOTAL_EXTENDED_LATCH_WAITS) THEN (
C.TOTAL_EXTENDED_LATCH_WAITS) ELSE (C.TOTAL_EXTENDED_LATCH_WAITS-P.TOTAL_EXTENDED_
LATCH_WAITS) END AS TOTAL_EXTENDED_LATCH_WAITS,        CASE WHEN (C.TOTAL_EXTENDED
_LATCH_WAIT_TIME < P.TOTAL_EXTENDED_LATCH_WAIT_TIME) THEN (C.TOTAL_EXTENDED_LATCH_
WAIT_TIME) ELSE (C.TOTAL_EXTENDED_LATCH_WAIT_TIME-P.TOTAL_EXTENDED_LATCH_WAIT_TIME
) END AS TOTAL_EXTENDED_LATCH_WAIT_TIME,        C.REAL_HOST_NAME AS REAL_HOST_NAME
FROM   ( SAPTOOLS.DBH_SNAP_LATCHWAIT AS C INNER JOIN SAPTOOLS.DBH_SNAP_LATCHWAIT
AS P           ON C.LATCH_NAME = P.LATCH_NAME AND             C.MEMBER = P.MEMBER
) INNER JOIN SYSIBMADM.SNAPDBM AS I          ON C.MEMBER         = I.MEMBER WHERE
  I.DB2START_TIME <= P.SNAPSHOT_TIMESTAMP AND        C.SNAPSHOT_TIMESTAMP = ? AND
       C.COLLECT_MODE = ? AND        P.SNAPSHOT_TIMESTAMP = ? AND        ( P.COLLE
CT_MODE = ? OR P.COLLECT_MODE IS NULL )

****************************

Can you please help to dig this issue further?

Thank You,

Rupali

hugo_amo
Employee
Employee
0 Kudos

Hi Rupali,

This SAPTOOLS.DBH_LATCHWAIT_COLLECT is a collector.

The clean method to prevent the collector jobs ( SAPTOOLS.DBH_LATCHWAIT_COLLECT is one of them) is to disable them via the Data Collector Framework in the responsible Solution Manager. The job will be removed from the database then. So if you start with removing the collector for latch waits this would be a good workaround. In general the collectors are collecting historical data for later analysis in the solution manager. There is no other impact.

I would recommend you to apply the latest fixpack for your db2 version, you may get rid of this problem.

Regards,

Hugo.

former_member182505
Contributor
0 Kudos

Hi Rupali

application handler 20069 causing the log full,

you can force it but you should know which programme or job is causing the issue,o

Go to St04->loggin->Application with Oldest Transaction

Then go to performance->applications and find the Oldest Transaction and PID related to this.

Thanks

Sadiq