Using the Self Help Web Support
After reading all of the available documentation and
searching over the Internet for a similar problem, we could not find a solution.
Every attempt to recover the database finished with a corrupted database. Finally,
we decided to go to the IBM support Web site and search in the APAR database
for a similar problem.
An error message from the db2diag.log was copied and
pasted in the Self Help IBM support Search Web page.
The first result from our search was document IY30334,
which had the same message that we had in our database log, in the header, with
the following extract:
IY30334: DB2 CRASHES AFTER 'SQLPGARL' GETS "DIA3806C UNEXPECTED END OF FILE WAS REACHED.",
A fix is available.DB2 Universal Database Version 7 fixpack 8.
APAR Status: Closed as program error.
At the time 'BACKUP ... ONLINE' or 'ARCHIVE ... LOG' DB2 may crash if the truncation is near
the end of a log file. This problem was introduced in v7.2 fixpak 6 - s020313 (by APAR
IY26397) and will be resolved in the next fixpak. This problem can be completely
circumvented, and recovered from if it does occur. This APAR does *not* result in corruption.
Without adjusting the LOGBUFSZ crash recovery will fail again with the same messages in the
CIRCUMVENTION: 'db2set DB2_DISABLE_FLUSH_LOG=ON' and do not use 'ARCHIVE LOG FOR DATABASE ...'
WORKAROUND: if the problem is encountered, set the database configuration parameter LOGBUFSZ
<= 16 then DB2 will not take the problematic code path:
DB2 may potentially crash if the truncation is near the end of the log file.
The db2diag.log may report 0xfffff609,unexpected EOF reached, in sqlpgarl.
In the case of this, set LOGBUFSZ to 16, and db2 will not take the problematic codepath.
After the database has successful recovered, 'db2set DB2_DISABLE_FLUSH_LOG=ON' and then set
the LOGBUFSZ back to the original value.
APAR number IY30334
Following the instruction in the IBM APAR note we had to
set the instance level registry variable DB2_DISABLE_FLUSH_LOG and change the
database parameter LOGBUFSZ to the recommended value. The current instance
registry settings are:
Adding the new instance level registry variable
$db2set DB2_DISABLE_FLUSH_LOG=ON -i ARTIST0
Changing the LOGBUFSZ settings to the recommended value:
$db2 "get db cfg for ARTIST0" | grep 'LOGBUFSZ'"
Log buffer size (4KB) (LOGBUFSZ) = 512
$db2 "update db cfg for ARTIST0 using LOGBUFSZ 16"
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
DB21026I For most configuration parameters, all applications must disconnect
from this database before the changes become effective.
01-01-2003 19:51:13 0 0 SQL1064N DB2STOP processing was successful.
01-01-2003 19:51:14 1 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
ARTIST01: ipclean: Removing DB2 engine and client's IPC resources for db2inst1.
ARTIST01: db2nkill  completed ok
The database is down, and all resources for the database
cleaned. New database start:
01-01-2003 19:53:56 1 0 SQL1063N DB2START processing was successful.
01-01-2003 19:53:57 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
The database was started regularly. A check for the active
databases revealed that the database had finished with automatic recovery and had
$ db2 "list active databases"
Database name = ARTIST0
Applications connected currently = 3
Database path = C:\DB2\NODE0000\SQL00001\
In the database db2diag.log file, we have the log from the
last successful recovery. The database problem is solved and the parameters
have to be changed back.
usage of the Self Help Web support database will help DBAs to avoid making too many
mistakes in a production environment. A DBA must never stop learning how to
effectively use all resources available and never start to believe that the database
vendor code is absolutely error free. Even, when coming from IBM.
See All Articles by Columnist Marin Komadina