Walker News

How To Reset DB2 Database Configuration To System Default?

With IBM DB2, each database has its own database configuration file which contains values for various configuration parameters that affect the use of the database.

Prior to DB2 8.2, this file is called SQLDBCON, and it’s changed to SQLDBCONF for DB2 8.2 and above.
You can find the database configuration file at database_directory/db2_instance/NODE0000/database_entry/, where both database_directory and database_entry can be found from db2 list database directory.

Alternatively, execute the Linux command find . -follow -name "SQLDBCON*" at DB2 instance directory.

Although the database configuration parameter values are stored in a physical file, you should never edit or open the file for inspection with any non DB2-provided utilities. If this file is edited accidentally during open for read session, you might have to restore the database from backup for recovery.

While there is not only one DB2 program to deal with SQLDBCONF file, I prefer the DB2 CLP (a.k.a. command line processor). For example (assume the database connection is established):

To view or print DB2 database configuration file
db2 get db2

To display both current and deferred DB2 database configuration parameters value (some parameter value changes are not effective for the current active connection until the client initiate a new database connection):
db2 get db cfg show detail

To update DB2 database configuration for a parameter called LOGFILSIZ (Log file size)
db2 update db cfg using LOGFILSIZ 2000

If for some reason you need to reset the DB2 database parameter changes to system default, disconnect the active database connection and execute reset db cfg (either one of this CLP command):
db2 reset db cfg for database_alias
db2 reset database configuration for database_alias

where database_alias refers to the target database (usually database name and database alias is the same).

Custom Search

2018  •  Privacy Policy