SQLCODE=-964 Red X for Statistics within Tivoli Enterprise Portal
Tivoli Enterprise Portal shows red X on Statistics under Summarization and Pruning Agent.
The Red X was shown in Tivoli Enterprise Portal because the Situation named: KSY_Pruning_Failures had triggered and was TRUE. The Red X represents a Situation Event has fired (==TRUE). In this case, if the mouse pointer is placed over the Red X it will show the exact Situation name that had triggered.
Reviewing the SY logs (those logs for the S&P Agent / KSY) to check for any problems, the DB2 errors will be found - the S&P Agent is unable to prune some data due to the DB2 transaction logs
filling up. When the S&P attempts to execute the delete statement in DB2 to "prune / delete" old
data, it will fail due to not enough space in the transaction logs.
Diagnosing the problem
Tivoli Enterprise Portal will show the following error.
After clicking on Statistics, the Error workspace below will show:
The sql code 964 indicates the DB2 transaction log is full:
SQL0964C The transaction log for the database is full.
User Response: Execute a COMMIT or ROLLBACK on receipt of this message (SQLCODE) or retry the operation.
Resolving the problem
The DB2 DBA needs to increase the number and size of the transaction logs.
Check the recommended tuning parameters for Tivoli Data Warehouse on DB2 in the following Redbook:
Tivoli Management Services Warehouse and Reporting
See Chapter 4. IBM Tivoli Data Warehouse tuning
Generally, the following commands has been enough to resolve the issue.
CONNECT TO WAREHOUS;
UPDATE DATABASE CONFIGURATION USING LOGPRIMARY 20 DEFERRED ;
UPDATE DATABASE CONFIGURATION USING LOGSECOND 60 IMMEDIATE ;
UPDATE DATABASE CONFIGURATION USING LOGFILSIZ 8192 DEFERRED ;
UPDATE DATABASE CONFIGURATION USING LOGBUFSZ 512 DEFERRED ;
The parameters above may or may not be sufficient since every environment is different. The more Tivoli Storage Manager agents defined and the greater the amount of activity for the Tivoli Storage Manager server, the bigger the logs may need to be.