on 09-24-2015 2:34 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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.
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
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.