Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

DB2

Posted Jul 11, 2003

Saved by the APAR or How to Use IBM's Self Help Web Support - Page 4

By Marin Komadina

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.", 
	ZRC=0XFFFFF609
A fix is available.DB2 Universal Database Version 7 fixpack 8.
APAR Status: Closed as program error. 
Error Description: 
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 
'db2diag.log'
Local Fix: 
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.
Problem Details:  
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 information
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:

$db2set -i
[i] DB2_STRIPED_CONTAINERS=ON
[i] DB2_HASH_JOIN=YES
[i] DB2_BINSORT=YES
[i] DB2COMM=TCPIP
[i] DB2_PARALLEL_IO=*

Adding the new instance level registry variable DB2_DIABLE_FLUSH_LOG:

$db2set DB2_DISABLE_FLUSH_LOG=ON -i ARTIST0
$db2set -i
[i] DB2_STRIPED_CONTAINERS=ON
[i] DB2_HASH_JOIN=YES
[i] DB2_BINSORT=YES
[i] DB2COMM=TCPIP
[i] DB2_PARALLEL_IO=*
[i] DB2_DISABLE_FLUSH_LOG=ON

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.

$ db2stop 
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.

$ db2_kill
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:

$ db2start
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 opened.

$ db2 "list active databases"
                           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.

Conclusion

Clever 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



DB2 Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.