TEPS transaction log full SQLSTATE=57011

Body

TEPS transaction log full SQLSTATE=57011

 The TEPS cannot behave correctly due to the database transaction log being full.

The issues seen can vary; in some cases the TEPS  does not start, in other cases the report is of agents not showing correctly, or the TEPS starting but not connecting to the TEMS, and users not being able to login.

Reviewing the TEPS logs;  either <teps HOSTNAME>_cq_KfwServices_*-1.log (for Linux/Unix) or <teps_hostname>_cq_*-01.log (for Windows) shows messages like:

 ctpropertysequence.cpp,738,"CTPropertySequence::Dump")   -----> -2102 = "[IBM][CLI Driver][DB2/LINUXX8664] SQL0964C  The transaction log for the database is full.  SQLSTATE=57011, GENERIC SQLSTATE: 57011, ERR: -964
SQLExecDirect rc=-1: SQL_ERROR"
ctsqlconnectionodbc.cpp,148,"CTSQLEvalODBC::Connection::sqlErrorCheckMsg") [IBM][CLI Driver][DB2/LINUXX8664] SQL0964C  The transaction log for the database is full.  SQLSTATE=57011, GENERIC SQLSTATE: 57011, ERR: -964
SQLExecDirect rc=-1: SQL_ERROR
ctpropertysequence.cpp,738,"CTPropertySequence::Dump")   ---> name = EXCEPTION: SQL Exception: accessElement = 0x2b1c140054a0, connection = 0x1b09210
ctpropertysequence.cpp,738,"CTPropertySequence::Dump")   -----> -2101a = -1 L

It is expected that there is a Database Administrator to look after the database, however for the TEPS  database it is sometimes down to the TEPS administrator to look after this database.  Therefore below is a quick guide on how to check and change the attributes needed to fix the above error.

Over time the defualt values set for the transaction log may not be large enough and tuning has to be done.

In this case the values that need to be changed are the size of the LOGFILSIZ and the number of primary and secondary files available.

The files are :

LOGFILSIZ - Size of log files configuration parameter
This parameter defines the size of each primary and secondary log file.

The size of these log files limits the number of log records that can be written to them before they become full and a new log file is required.
This size is measured in 4k pages

LOGPRIMARY - Number of primary log files configuration parameter
This parameter allows you to specify the number of primary log files to be pre-allocated. The primary log files establish a fixed amount of storage allocated to the recovery log files.

LOGSECOND - Number of secondary log files configuration parameter
This parameter specifies the number of secondary log files that are created and
used for recovery log files (only as needed)

To check the sizes that are set and change them follow the steps below. 

It is best to have the TEPS stopped while this is done, as the database needs to be stopped and started for the changes to take effect.

 1. Connect to the TEPS DB

On Unix/Linux
# db2 connect to <TEPS Database> user <user> using <user_pwd>

On Windows
Bring up a DB2 command line window (Start..Programs..IBM DB2 ..select command line )
db2 => connect to <TEPS database>

Since the defualt is TEPS then the command would look like:

db2 => connect to TEPS
 
2. To check the current size of the transaction log, run the command

 On Unix/Linux
# db2 get db cfg | grep LOGFILSIZ  

On Windows sometimes it is not possible to grep the command so just do the command as

db2 => get db cfg

and look for the lines:

Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 8
 Number of secondary log files               (LOGSECOND) = 11
 
 the above is the defualt settings however they are often needed to be larger.

3. Increase the size with the commands

 On Unix/Linux
# db2 update db cfg using LOGFILSIZ <value>
# db2 update db cfg using LOGPRIMARY <value>;
# db2 update db cfg using LOGSECOND <value>;

On Windows:

db2 => update db config using LOGFILESIZ <value>
 db2 => update db cfg using LOGPRIMARY <value>;
 db2 => update db cfg using LOGSECOND <value>;
 
The values  usually set are typically:

db2 update db cfg for <TEPS Database> using logfilsiz 8192
db2 update db cfg for <TEPS Database> using logprimary 20
db2 update db cfg for <TEPS Database> using logsecond 8
 
However if you find the values are already at that level they may have to be increased further,  they have been seen to be at:
 
Log file size (4KB)                (LOGFILSIZ) = 10000
Number of primary log files        (LOGPRIMARY) = 80
Number of secondary log files      (LOGSECOND) = 40
 
4. After increasing the transaction log size restart DB2 for changes to  take effect:

On Linux/Unix

# db2 force applications all
# db2stop
# db2start

On Windows
 db2 =>  force applications all
 db2 =>db2stop
 db2 => db2start

 
5.  Then restart the TEPS

As stated the values above are only a guide, the values to be set depend on the size of the environment.  However you need to be sure you do not exceed the disk space available.

The  disk space the tables need can be calculated with the formula:

 disk_space_required = logfilsiz * (logprimary + logsecond) * page_size

So for the settings of

Log file size (4KB)                (LOGFILSIZ) = 10000
Number of primary log files        (LOGPRIMARY) = 80
Number of secondary log files      (LOGSECOND) = 40

disk_space_required = 10000 * (80 + 40) *4KB  = 4800 MB

Subscribe and follow us for all the latest information directly on your social feeds: