Stackvora.tech

IBM Db2: basic administration and backup

IBM Db2 is a family of data management products, including relational database servers, developed by IBM. This article focuses on fundamental…

IBM Db2 is a family of data management products, including relational database servers, developed by IBM. This article focuses on fundamental administration tasks for Db2 for Linux, UNIX, and Windows (Db2 LUW), covering essential connectivity, instance and database management, and robust backup and recovery strategies. Mastery of these concepts is crucial for maintaining data integrity and ensuring high availability in enterprise environments.

We'll primarily use the Db2 command-line processor (CLP) for these operations, assuming a Db2 11.5 installation. Commands may vary slightly in older versions or on different operating systems.

Establishing Connectivity and Basic Operations

Interacting with a Db2 instance begins with connecting to a database. The db2 command-line processor is the primary tool for this. Once connected, you can execute SQL statements and various Db2 utilities.

Connecting to a Database

Before any database operations, you must connect to the target database. The syntax is straightforward:

db2 connect to <database_alias> user <username> using <password>

For a local connection to the default SAMPLE database, often created during installation, without specifying user credentials (if the current OS user is authorized):

db2 connect to SAMPLE

After a successful connection, the prompt might change, or a confirmation message will appear. To disconnect:

db2 connect reset

Listing Active Applications

To identify current database connections and applications, the LIST APPLICATIONS command is invaluable. This helps diagnose performance issues or identify rogue connections.

db2 list applications show detail

The show detail option provides additional information, such as the application name, agent ID, and the SQL statement currently being executed. Understanding the output:

  • AuthId: The user ID that connected to the database.
  • Application Handle: A unique identifier for the application.
  • Application Name: The name of the application, often derived from the client program.
  • DB Name: The database the application is connected to.
  • Application Status: Indicates what the application is currently doing (e.g., UOW Executing, UOW Waiting, Disconnected).
  • Client IP Address: The IP address of the client application.

To force off a specific application handle:

db2 force application (<application_handle>)

Use this with caution, as it can roll back ongoing transactions and impact users.

Instance and Database Management Fundamentals

A Db2 instance is a logical environment containing one or more databases. Each instance has its own configuration and resources.

Starting and Stopping an Instance

As the instance owner (e.g., db2inst1), you manage the instance status.

To start the instance:

db2start

To stop the instance gracefully:

db2stop

For an immediate stop, which terminates all applications and rolls back transactions:

db2stop force

Listing Databases

To see which databases are cataloged within the current instance:

db2 list db directory

This command shows local and remote databases the instance knows about. For a detailed view of local databases:

db2 list database directory on <instance_home_directory>

For example, if your instance owner is db2inst1 and their home is /home/db2inst1, the command might be db2 list database directory on /home/db2inst1.

Creating and Dropping Databases

Creating a new database is a resource-intensive operation.

db2 create database <database_name> using codeset UTF-8 territory US pagesize 16384

Key parameters:

  • USING CODESET UTF-8: Specifies the character encoding, highly recommended for modern applications.
  • TERRITORY US: Defines the country/region for locale-specific operations.
  • PAGESIZE 16384: Sets the default page size for table spaces, 16KB is common. Larger pages (32KB) can reduce I/O for large rows, but smaller pages (4KB, 8KB) are efficient for OLTP with many small rows.

To drop a database (IRREVERSIBLE!):

db2 drop database <database_name>

Ensure no applications are connected before dropping.

Database Configuration Parameters

Db2 databases have hundreds of configuration parameters that control resource allocation, logging, and other behaviors. These are critical for performance tuning.

db2 get db cfg for <database_alias>

To update a parameter, for example, increasing the log file size (LOGFILSIZ):

db2 update db cfg for SAMPLE using LOGFILSIZ 40960 immediate

The immediate keyword means the change takes effect without requiring a database deactivation/reactivation. Some parameters require a database deactivation or even an instance stop/start.

Common parameters to consider:

  • LOGPRIMARY and LOGSECOND: Number of primary and secondary log files.
  • LOGFILSIZ: Size of each log file (in 4KB pages).
  • DFT_QUERYOPT: Default query optimization level.
  • MAXLOCKS and LOCKLIST: Control locking behavior and memory for lock lists.
  • CATALOGCACHE_SZ: Size of the catalog cache.

Backup and Recovery Strategies

Robust backup and recovery are paramount for any production database. Db2 offers flexible options for online and offline backups.

Offline Backups

An offline backup requires exclusive access to the database, meaning no applications can be connected. This simplifies recovery but incurs downtime.

db2 backup database SAMPLE to /backups compress include logs

Explanation of parameters:

  • to /backups: Specifies the target directory for the backup image. Ensure sufficient space.
  • compress: Compresses the backup image, saving disk space and often reducing backup time.
  • include logs: (Db2 11.5+) Includes archived log files required for crash recovery within the backup image itself, simplifying recovery. For older versions or specific scenarios, logs might need separate management.

A full offline backup takes the longest but is the most self-contained.

Online Backups

Online backups allow applications to continue working while the backup is performed, minimizing downtime. This requires the database to be configured for archival logging.

First, ensure archival logging is enabled. Connect to the database and check LOGARCHMETH1:

db2 get db cfg for SAMPLE | grep LOGARCHMETH1

If it's OFF, you need to enable it. For example, to archive logs to a disk directory:

db2 update db cfg for SAMPLE using LOGARCHMETH1 DISK:/db2/archive_logs

This change requires a database deactivation and reactivation (db2 deactivate db SAMPLE then db2 activate db SAMPLE or db2 connect reset then db2 connect to SAMPLE) to take effect.

Once archival logging is active, perform an online backup:

db2 backup database SAMPLE online to /backups compress include logs

The online keyword indicates an online backup. This command will capture the database state at a point in time, and the archived logs generated during and after the backup are essential for point-in-time recovery.

Restoring a Database

Restoring a database from a backup image:

db2 restore database SAMPLE from /backups taken at <timestamp> into SAMPLE replace existing
  • from /backups: The source directory or device where the backup image resides.
  • taken at <timestamp>: Specify the timestamp of the desired backup image (e.g., 20230315103000). You can find this timestamp in the backup image filename or by listing backup history.
  • into SAMPLE: The target database name. Can be different from the source database name.
  • replace existing: Overwrites an existing database with the restored one. Use with extreme caution!

Point-in-Time Recovery (PITR)

PITR allows restoring a database to a specific moment in time using a full backup and subsequent archived transaction logs. This is critical for recovering from logical data corruption or accidental deletions.

Assuming you have a full online backup and all subsequent archived logs up to the desired recovery point:

db2 restore database SAMPLE from /backups taken at <full_backup_timestamp> into SAMPLE replace existing history file

After the restore completes, roll forward the logs:

db2 rollforward db SAMPLE to point in time 'YYYY-MM-DD-HH.MM.SS.NNNNNN' and complete

The timestamp for point in time should be the desired recovery point. After successful rollforward, the database will be in a pending state. To make it available:

db2 rollforward db SAMPLE complete

Or, if you just ran the to point in time command:

db2 terminate
db2 activate database SAMPLE

Monitoring and Troubleshooting

Checking Database Health

The db2pd utility is a powerful tool for monitoring database activity and diagnosing issues without connecting to the database.

db2pd -db SAMPLE -logs

This shows current log usage. Other useful db2pd commands:

  • db2pd -db SAMPLE -transactions: Lists active transactions.
  • db2pd -db SAMPLE -util: Shows active utilities (e.g., backup, restore).
  • db2pd -db SAMPLE -applications: Similar to LIST APPLICATIONS but provides more raw detail.

Diagnostic Log (db2diag.log)

The db2diag.log is the primary diagnostic log file for a Db2 instance. It contains errors, warnings, and informational messages from the Db2 engine. Its location is usually within the instance home directory under sqllib/db2dump/.

tail -f /home/db2inst1/sqllib/db2dump/db2diag.log

The db2diag command-line utility can parse and filter this log:

db2diag -l severe -H 2h

This filters for severe messages in the last 2 hours.

Common Pitfalls

  • Insufficient disk space: Backups can consume significant space. Always ensure the target directory has ample free space, often 1-2x the database size.
  • Not enabling archival logging for online backups: Attempting an online backup without LOGARCHMETH1 configured will result in an error (SQL2428N).
  • Forgetting immediate for config changes: Many database configuration (DB CFG) changes require a database deactivation/reactivation if immediate isn't specified or supported for that parameter, leading to unexpected behavior.
  • No regular backup validation: A backup is only good if it can be restored. Periodically restore backups to a test environment to verify their integrity and your recovery procedures.
  • Incorrect timestamp for PITR: Using an imprecise or incorrect timestamp for to point in time during rollforward can lead to data loss or an inconsistent state. Always verify the desired timestamp.
  • Running sensitive commands as non-instance owner: While some commands can be run by other users with appropriate privileges, many administrative tasks (e.g., db2start, db2stop, db2 backup) require the instance owner.

Back to the knowledge base · Ask the AI assistant