Processing method of DB2 SQL error (sqlcode: – 964, sqlstate: 57011)

Description of fault phenomenon:

When executing SQL statement, the following error message appears

Instruction SQL: insert into t_ stat_ file_ Temp sqlstate: 57011, vendor error code: – 964, DB2 SQL error: sqlcode: – 964, sqlstate: 57011, sqlerrmc: null

Fault reason description:

There is not enough storage space in the database heap to process the statement. This error message is the result of running out of space in the transaction log

Troubleshooting:

Method 1: expand the storage space of transaction log

Adjust the number of primary log files and secondary log files by similar statements. For example, allocates eight main log files and allocates up to 100 auxiliary log files< u> Note & lt/ u>: auxiliary log files will be allocated as needed and deleted when is not needed

db2 update db cfg for $DBNAME using logprimary 8
db2 update db cfg for $DBNAME using logsecond 100

Method 2: handle abnormal transaction

If the transaction log storage space is insufficient due to transaction exception, simply expanding the storage space is often difficult to completely solve the problem, so this method needs to provide thorough troubleshooting steps. Note: the following operations are performed after DB2 connect to $dbname connects to the database

2.1 locating abnormal database nodes

Through the following statement, view the log space usage of each node and locate the transaction log space usage (log)_ UTILIZATION_ Percent) is too high

db2 " select DB_NAME, LOG_UTILIZATION_PERCENT, TOTAL_LOG_USED_KB,TOTAL_LOG_AVAILABLE_KB,TOTAL_LOG_USED_TOP_KB, DBPARTITIONNUM 
from SYSIBMADM.LOG_UTILIZATION order by DBPARTITIONNUM "

The execution results are similar as follows.

2.2 check abnormal activity connection

On the node where the transaction log space utilization is too high, execute the following command to locate whether the transaction log usage (UOW) exists_ LOG_ SPACE_ Used) is too high

db2 "select APPLICATION_HANDLE,UOW_LOG_SPACE_USED,UOW_START_TIME 
from TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) 
order by UOW_LOG_SPACE_USED"

If there is an abnormal active connection, it can be terminated by a command similar to the following

db2 "force application (h1 [,h2,..hn])"

H1 [, H2,… HN] stands for application handle identifier

2.3 checking in double transaction

On the node with high utilization rate of transaction log space, execute the following command to locate whether there is an in double transaction in interactive mode

db2 list indoubt transactions with prompting

If there are uncertain transactions, operations such as COMMIT or ROLLBACK can be performed through interactive commands, as described in the WITH PROMPTING mode.

Command parameters WITH PROMPTING Indicates that indoubt transactions are to be processed. If this parameter is specified, an interactive dialog mode is initiated, permitting the user to commit, roll back, or forget indoubt transactions. If this parameter is not specified, indoubt transactions are written to the standard output device, and the interactive dialog mode is not initiated.
Interactive dialog mode permits the user to:

List all indoubt transactions (enter l)

List indoubt transaction number x (enter l, followed by a valid transaction number)

Quit (enter q)

Commit transaction number x (enter c, followed by a valid transaction number)

Roll back transaction number x (enter r, followed by a valid transaction number)

Forget transaction number x (enter f, followed by a valid transaction number).

A blank space must separate the command letter from its argument.

 

Similar Posts: