Troubleshooting
Problem
In a partitioned database environment, the following error is returned, while trying to execute a query, SQL0903N COMMIT statement failed, transaction rolled back. Reason code: "2". SQLSTATE=40504
Symptom
Following error messages are dumped in the db2diag.log,
2013-02-27-21.05.37.877552+330 I50125304A835 LEVEL: Error
PID : 6291550 TID : 14404 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : TEST
APPHDL : 0-15842 APPID: xx.xx.xx.xx.65064.13022715041
AUTHID : DB2INST1
EDUID : 14404 EDUNAME: db2agent (TEST) 0
FUNCTION: DB2 UDB, relation data serv, sqlrrcom_dps, probe:150
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1473 sqlerrml: 13
sqlerrmc: 1,2,3,4,5,6,7
sqlerrp : SQLRR065
sqlerrd : (1) 0x801A006D (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0xFFFFFC4A (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
SQL1473N implies that a transaction cannot be committed because the time difference between the system time on the local node and the virtual timestamps of node(s) < node-list> is greater than the max_time_diff database manager configuration parameter. The transaction is hence, rolled back.
2013-02-27-21.05.37.877552+330 I50125304A835 LEVEL: Error
PID : 6291550 TID : 14404 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : TEST
APPHDL : 0-15842 APPID: xx.xx.xx.xx.65064.13022715041
AUTHID : DB2INST1
EDUID : 14404 EDUNAME: db2agent (TEST) 0
FUNCTION: DB2 UDB, relation data serv, sqlrrcom_dps, probe:150
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1473 sqlerrml: 13
sqlerrmc: 1,2,3,4,5,6,7
sqlerrp : SQLRR065
sqlerrd : (1) 0x801A006D (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0xFFFFFC4A (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
SQL1473N implies that a transaction cannot be committed because the time difference between the system time on the local node and the virtual timestamps of node(s) < node-list> is greater than the max_time_diff database manager configuration parameter. The transaction is hence, rolled back.
Cause
This could happen, if there is an abrupt change in the system time of one of the physical nodes. It could result in a mismatch, of the Virtual Timestamps used for synchronization of all the nodes.
Resolving The Problem
The following steps could be used to get rid of this issue:
- db2 terminate
- db2stop
- db2start
However, sometimes the solution above, may not work as the VTS in LFH file has a future timestamp.
This requires resetting the VTS in the LFH. DB2 Advanced Support should be contacted to take this further, at this point.
After resetting a full offline backup is mandatory.
- db2 update dbm cfg using MAX_TIME_DIFF <value>
(where, value should be equal to the maximum difference in the system time of 2 nodes. The maximum value for this parameter is 1440.)
- db2 terminate
- db2stop
- db2start
However, sometimes the solution above, may not work as the VTS in LFH file has a future timestamp.
This requires resetting the VTS in the LFH. DB2 Advanced Support should be contacted to take this further, at this point.
After resetting a full offline backup is mandatory.
Related Information
[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Database Objects\/Config - Database","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"}],"Version":"9.7;9.5;9.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
08 December 2022
UID
swg21633084