HEALTH_TBS_INFO table function

Returns table space information from a health snapshot of a database.

Important: This table function has been deprecated and might be removed in a future release because the health monitor has been deprecated. It is not supported in Db2® pureScale® environments. For more information, see Health monitor has been deprecated..

Syntax

The schema is SYSPROC.

Table function parameters

dbname

An input argument of type VARCHAR(255) that specifies a valid database name in the same instance as the currently connected database when calling this function. Specify a database name that has a directory entry type of either "Indirect" or "Home", as returned by the LIST DATABASE DIRECTORY command. Specify the null value to take the snapshot from the currently connected database.

member

An input argument of type INTEGER that specifies a valid database member number. Specify -1 for the current database member, or -2 for an aggregate of all active database members. An active database member is a member where the database is available for connection and use by applications.

If the null value is specified, -1 is set implicitly.

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.

Example

SELECT * FROM TABLE(HEALTH_TBS_INFO('',-1)) AS T

The following is an example of output from this query.

SNAPSHOT_TIMESTAMP         TABLESPACE_NAME      ...
-------------------------- ----------------...- ...
2006-02-13-12.30.35.027383 SYSCATSPACE          ...
2006-02-13-12.30.35.027383 SYSTOOLSPACE         ...
2006-02-13-12.30.35.027383 SYSTOOLSTMPSPACE     ...
2006-02-13-12.30.35.027383 TEMPSPACE1           ...
2006-02-13-12.30.35.027383 USERSPACE1           ...
                                                   
  5 record(s) selected.

Output from this query (continued).

... ROLLED_UP_ALERT_STATE ROLLED_UP_ALERT_STATE_DETAIL
... --------------------- ----------------------------
...                     4 Alarm                       
...                     1 Normal                      
...                     1 Normal                      
...                     1 Normal                      
...                     4 Alarm

Information returned

Table 1. Information returned by the HEALTH_TBS_INFO table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp
TABLESPACE_NAME	VARCHAR(128)	tablespace_name - Table space name
ROLLED_UP_ALERT_STATE	BIGINT	The most severe alert state captured by this snapshot.
ROLLED_UP_ALERT_STATE_DETAIL	VARCHAR(20)	The text description of the ROLLED_UP_ALERT_STATE column.