CLP Commands

ARCHIVE LOG

This utility closes and truncates the active log file for a recoverable database. If user exit is enabled, an archive request is issued.

Usage:

• The user must be one of the following: sysadm, sysctrl, sysmaint, or dbadm.

• No connection to the database is required.

• Command parameters:

  DATABASE database-alias: Specifies the alias of the database whose active log is to be archived

  USER username: Identifies the user name under which a connection will be attempted

  USING password: Specifies the password to authenticate the user name

  ON ALL DBPARTITIONNUMS: Specifies that the command should be issued on all database partitions in the db2nodes.cfg file. This is the default if a database partition number clause is not specified

  EXCEPT: Specifies that the command should be issued on all database partitions in the db2nodes.cfg file, except those specified in the database partition number list

  ON DBPARTITIONNUM/ON DBPARTITIONNUMS: Specifies that the logs should be archived for the specified database on a set of database partitions

  db-partition-number: Specifies a database partition number in the database partition number list

  TO db-partition-number: Used when specifying a range of database partitions for which the logs should be archived. All database partitions from the first database partition number specified, up to and including the second database partition number specified, are included in the database partition number list.

Notes:

• This command can be used to collect a complete set of log files up to a known point. The log files can then be used to update a standby database.

• This command can be executed only when the invoking application or shell does not have a database connection to the specified database. This prevents a user from executing the command with uncommitted transactions. As such, the ARCHIVE LOG command will not forcibly commit the user's incomplete transactions. If the invoking application or shell already has a database connection to the specified database, the command will terminate and return an error. If another application has transactions in progress with the specified database when this command is executed, there will be a slight performance degradation because the command flushes the log buffer to disk. Any other transactions attempting to write log records to the buffer will have to wait until the flush is complete.

• If used in a partitioned database environment, a subset of database partitions may be specified by using a database partition number clause. If the database partition number clause is not specified, the default behavior for this command is to close and archive the active log on all database partitions.

• Using this command will use up a portion of the active log space, due to the truncation of the active log file. The active log space will resume its previous size when the truncated log becomes inactive. Frequent use of this command may drastically reduce the amount of the active log space available for transactions.

For compatibility with versions earlier than Version 8:

• The keyword NODE can be substituted for DBPARTITIONNUM.

Sample output from db2 archive log:

$ db2 archive log for database SAMPLE

DB20000I  The ARCHIVE LOG command completed successfully.

LIST HISTORY

This command lists entries in the history file. The history file contains a record of recovery and administrative events. Recovery events include full database and table space level backup, incremental backup, restore, and rollforward operations. Additional logged events include create, alter, drop, or rename table space, reorganize table, drop table, and load.

Usage:

• Anyone can access the utility.

• An explicit instance attachment is not required. If the database is listed as remote, an instance attachment to the remote server is established for the duration of the command.

• Command parameters:

  HISTORY: Lists all events that are currently logged in the history file

  BACKUP: Lists backup and restore operations

  ROLLFORWARD: Lists rollforward operations

  DROPPED TABLE: Lists dropped table records

  LOAD: Lists load operations

  CREATE TABLESPACE: Lists table space create and drop operations

  RENAME TABLESPACE: Lists table space renaming operations

  REORG: Lists reorganization operations

  ALTER TABLESPACE: Lists alter table space operations

  ALL: Lists all entries of the specified type in the history file

  SINCE timestamp: A complete timestamp (format yyyymmddhhmmss) or an initial prefix (minimum yyyy) can be specified. All entries with timestamps equal to or greater than the timestamp provided are listed.

  CONTAINING schema.object_name: This qualified name uniquely identifies a table.

  CONTAINING object_name: This unqualified name uniquely identifies a table space.

  FOR DATABASE database-alias: Used to identify the database whose recovery history file is to be listed

Notes:

The report generated by this command contains the operation symbols listed in Table A.1 and operation types listed in Table A.2.

PRUNE HISTORY/LOGFILE

This command is used to delete entries from the recovery history file or to delete log files from the active log file path. Deleting entries from the recovery history file may be necessary if the file becomes excessively large and the retention period is high. Deleting log files from the active log file path may be necessary if logs are being archived manually (rather than through a user exit program).

Usage:

• The user must be one of the following: sysadm, sysctrl, sysmaint, or dbadm.

• Connection to the database is required.

• Command parameters:

  HISTORY timestamp: Identifies a range of entries in the recovery history file that will be deleted. A complete timestamp (in the form yyyymmddhhmmss) or an initial prefix (minimum yyyy) can be specified. All entries with timestamps equal to or less than the timestamp provided are deleted from the recovery history file.

  WITH FORCE OPTION: Specifies that the entries will be pruned according to the timestamp specified, even if some entries from the most recent restore set are deleted from the file. A restore set is the most recent full database backup, including any restores of that backup image. If this parameter is not specified, all entries from the backup image forward will be maintained in the history.

  LOGFILE PRIOR TO log-file-name: Specifies a string for a log file name, for example, S0000100.LOG. All log files prior to (but not including) the specified log file will be deleted. The LOGRETAIN database configuration parameter must be set to RECOVERY or CAPTURE.

UPDATE HISTORY FILE

This command updates the location, device type, or comment in a history file entry.

Usage:

• The user must be one of the following: sysadm, sysctrl, sysmaint, or dbadm.

• Connection to the database is required.

• Command parameters:

  FOR object-part: Specifies the identifier for the backup or copy image. It is a timestamp with an optional sequence number from 001 to 999.

  LOCATION new-location: Specifies the new physical location of a backup image. The interpretation of this parameter depends on the device type.

  DEVICE TYPE new-device-type: Specifies a new device type for storing the backup image. Valid device types are:

  • D? Disk

  • K? Diskette

  • T? Tape

  • A? TSM

  • U? User exit

  • P? Pipe

  • N? Null device

  • X? XBSA

  • Q? SQL statement

  • O? Other

  COMMENT new-comment: Specifies a new comment to describe the entry

Note: The history file is used by database administrators for record keeping. It is used internally by DB2 for the automatic recovery of incremental backups.