DB2 10.5 for Linux, UNIX, and Windows

Viewing recovery history file entries using the DB_HISTORY administrative view

You can use the DB_HISTORY() administrative view to access the contents of the database history file. This method is an alternative to using the LIST HISTORY CLP command or the C history APIs.

Before you begin

A database connection is required to use this function.

About this task

Deletes and updates to the database history file can be done only through the PRUNE HISTORY or UPDATE HISTORY commands.

Procedure

Use the DB_HISTORY() administrative view within an SQL SELECT statement to access the database history file for the database you are connected to, or on the database partition specified by the DB2NODE environment variable. For example, to see the contents of the history file use:

   SELECT * FROM TABLE(DB_HISTORY()) AS LIST_HISTORY

Example

To hide the syntax of the administrative view, you can create a view as follows:

    CREATE VIEW LIST_HISTORY AS
       SELECT * FROM TABLE(DB_HISTORY()) AS LIST_HISTORY

After creating this view, you can run queries against the view. For example:

   SELECT * FROM LIST_HISTORY

   SELECT dbpartitionnum FROM LIST_HISTORY

   SELECT dbpartitionnum, start_time, seqnum, tabname, sqlstate 
     FROM LIST_HISTORY

For a list of columns and column data types returned by the DB_HISTORY administrative view, see DB_HISTORY administrative view.