DB2 Version 9.7 for Linux, UNIX, and Windows

HEALTH_TBS_HI table function

Returns health indicator information for table spaces from a health snapshot of table spaces in a database.

Important: This table function has been deprecated and might be removed in a future release because the health monitor has been deprecated in Version 9.7. For more information, see Health monitor has been deprecated.

Syntax


>>-HEALTH_TBS_HI--(--dbname--,--dbpartitionnum--)--------------><

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.
dbpartitionnum: An input argument of type INTEGER that specifies a valid database partition number. Specify -1 for the current database partition, or -2 for an aggregate of all active database partitions. An active database partition is a partition where the database is available for connection and use by applications.
If the null value is specified, -1 is set implicitly.

Authorization

EXECUTE privilege on the HEALTH_TBS_HI table function.

Example

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

The following example is a sample output from this query.

SNAPSHOT_TIMESTAMP         TABLESPACE_NAME     HI_ID     HI_VALUE ...
-------------------------- ---------------...- -----...- -------- ...
2006-02-13-12.30.35.229196 SYSCATSPACE              2001        0 ...
2006-02-13-12.30.35.229196 SYSCATSPACE              2002       99 ...
2006-02-13-12.30.35.229196 SYSCATSPACE              2003        0 ...
2006-02-13-12.30.35.229196 SYSTOOLSPACE             2001        0 ...
2006-02-13-12.30.35.229196 SYSTOOLSPACE             2002       62 ...
2006-02-13-12.30.35.229196 SYSTOOLSPACE             2003        0 ...
2006-02-13-12.30.35.229196 SYSTOOLSTMPSPACE         2001        0 ...
2006-02-13-12.30.35.229196 TEMPSPACE1               2001        0 ...
2006-02-13-12.30.35.229196 USERSPACE1               2001        0 ...
2006-02-13-12.30.35.229196 USERSPACE1               2002      100 ...
2006-02-13-12.30.35.229196 USERSPACE1               2003        0 ...
                                                                     
  11 record(s) selected.

Output from this query (continued).

... HI_TIMESTAMP               HI_ALERT_STATE  HI_ALERT_STATE_DETAIL ...
... -------------------------- --------------- --------------------- ...
... 2006-02-13-12.26.26.158000               1 Normal                ...
... 2006-02-13-12.26.26.158000               4 Alarm                 ...
... 2006-02-13-12.26.26.158000               1 Normal                ...
... 2006-02-13-12.26.26.158000               1 Normal                ...
... 2006-02-13-12.26.26.158000               1 Normal                ...
... 2006-02-13-12.26.26.158000               1 Normal                ...
... 2006-02-13-12.26.26.158000               1 Normal                ...
... 2006-02-13-12.26.26.158000               1 Normal                ...
... 2006-02-13-12.26.26.158000               1 Normal                ...
... 2006-02-13-12.26.26.158000               4 Alarm                 ...
... 2006-02-13-12.26.26.158000               1 Normal                ...

Output from this query (continued).

... HI_FORMULA                HI_ADDITIONAL_INFO                              
... ---------------------...- ------------------------------------------------
... 0                         -                                               
... ((9376 / 9468) * 100)     The short term table space growth rate          
                                from "02/13/2006 11:26:26.000158" to          
                                "02/13/2006 12:26:26.000158" is "N/A"         
                                bytes per second and the long term growth     
                                rate from "02/12/2006 12:26:26.000158"        
                                to "02/13/2006 12:26:26.000158" is "N/A"      
                                bytes per second. Time to fullness is         
                                projected to be "N/A" and "N/A"               
                                respectively. The table space is defined      
                                with automatic storage set to "YES" and       
                                automatic resize enabled set to "YES".        
... 0                         The table space is defined with automatic       
                                storage set to "YES" and automatic resize     
                                enabled set to "YES". The following are       
                                the automatic resize settings: increase       
                                size (bytes) "-1", increase size (percent)    
                                "N/A", maximum size (bytes) "-1". The         
                                current table space size (bytes) is           
                                "38797312".                                   
... 0                         -                                               
... ((156 / 252) * 100)       The short term table space growth rate          
                                from "02/13/2006 11:26:26.000158" to          
                                "02/13/2006 12:26:26.000158" is "N/A"         
                                bytes per second and the long term growth     
                                rate from "02/12/2006 12:26:26.000158"        
                                to "02/13/2006 12:26:26.000158" is "N/A"      
                                bytes per second. Time to fullness is         
                                projected to be "N/A" and "N/A"               
                                respectively. The table space is defined      
                                with automatic storage set to "YES" and       
                                automatic resize enabled set to "YES".        
... 0                         The table space is defined with automatic       
                                storage set to "YES" and automatic resize     
                                enabled set to "YES". The following are       
                                the automatic resize settings: increase       
                                size (bytes) "-1", increase size (percent)    
                                "N/A", maximum size (bytes) "-1". The         
                                current table space size (bytes) is           
                                "1048576".                                    
... 0                         -                                               
... 0                         -                                               
... 0                         -                                               
... ((1504 / 1504) * 100)     The short term table space growth rate from     
                                "02/13/2006 11:26:26.000158" to               
                                "02/13/2006 12:26:26.000158" is "N/A"         
                                bytes per second and the long term growth     
                                rate from "02/12/2006 12:26:26.000158" to     
                                "02/13/2006 12:26:26.000158" is "N/A" bytes   
                                per second. Time to fullness is projected     
                                to be "N/A" and "N/A" respectively. The       
                                table space is defined with automatic storage 
                                set to "YES" and automatic resize enabled     
                                set to "YES".                                 
... 0                         The table space is defined with automatic       
                                storage set to "YES" and automatic resize     
                                enabled set to "YES". The following are       
                                the automatic resize settings: increase       
                                size (bytes) "-1", increase size (percent)    
                                "N/A", maximum size (bytes) "-1". The         
                                current table space size (bytes) is           
                                "6291456".

Information returned

Table 1. Information returned by the HEALTH_TBS_HI 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
HI_ID	BIGINT	A number that uniquely identifies the health indicator in the snapshot data stream.
HI_VALUE	SMALLINT	The value of the health indicator.
HI_TIMESTAMP	TIMESTAMP	The date and time that the alert was generated.
HI_ALERT_STATE	BIGINT	The severity of the alert.
HI_ALERT_STATE_DETAIL	VARCHAR(20)	The text description of the HI_ALERT_STATE column.
HI_FORMULA	VARCHAR(2048)	The formula used to calculate the health indicator.
HI_ADDITIONAL_INFO	VARCHAR(4096)	Additional information about the health indicator.