DB2 Version 9.7 for Linux, UNIX, and Windows

HEALTH_TBS_HI_HIS table function

The HEALTH_TBS_HI_HIS table function returns health indicator history information for table spaces 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 in Version 9.7. For more information, see Health monitor has been deprecated.

Syntax


>>-HEALTH_TBS_HI_HIS--(--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_HIS table function.

Example

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

The following example is a sample output from this query.

SNAPSHOT_TIMESTAMP         TABLESPACE_NAME     HI_ID     ...
-------------------------- ---------------...- -----...- ...
2006-02-13-12.30.37.181478 SYSCATSPACE              2001 ...
2006-02-13-12.30.37.181478 SYSCATSPACE              2001 ...
2006-02-13-12.30.37.181478 SYSCATSPACE              2002 ...
2006-02-13-12.30.37.181478 SYSCATSPACE              2002 ...
2006-02-13-12.30.37.181478 SYSCATSPACE              2003 ...
2006-02-13-12.30.37.181478 SYSCATSPACE              2003 ...
2006-02-13-12.30.37.181478 SYSTOOLSPACE             2001 ...
2006-02-13-12.30.37.181478 SYSTOOLSPACE             2001 ...
2006-02-13-12.30.37.181478 SYSTOOLSPACE             2002 ...
2006-02-13-12.30.37.181478 SYSTOOLSPACE             2002 ...
2006-02-13-12.30.37.181478 SYSTOOLSPACE             2003 ...
2006-02-13-12.30.37.181478 SYSTOOLSPACE             2003 ...
2006-02-13-12.30.37.181478 SYSTOOLSTMPSPACE         2001 ...
2006-02-13-12.30.37.181478 SYSTOOLSTMPSPACE         2001 ...
2006-02-13-12.30.37.181478 TEMPSPACE1               2001 ...
2006-02-13-12.30.37.181478 TEMPSPACE1               2001 ...
2006-02-13-12.30.37.181478 USERSPACE1               2001 ...
2006-02-13-12.30.37.181478 USERSPACE1               2001 ...
2006-02-13-12.30.37.181478 USERSPACE1               2002 ...
2006-02-13-12.30.37.181478 USERSPACE1               2002 ...
2006-02-13-12.30.37.181478 USERSPACE1               2003 ...
2006-02-13-12.30.37.181478 USERSPACE1               2003 ...
                                                            
  22 record(s) selected.

Output from this query (continued).

... HI_TIMESTAMP               HI_VALUE HI_ALERT_STATE HI_ALERT_STATE_DETAIL ...
... -------------------------- -------- -------------- --------------------- ...
... 2006-02-13-12.16.25.911000        0              1 Normal                ...
... 2006-02-13-12.06.26.168000        0              1 Normal                ...
... 2006-02-13-12.16.25.911000       99              4 Alarm                 ...
... 2006-02-13-12.06.26.168000       99              4 Alarm                 ...
... 2006-02-13-12.16.25.911000        0              1 Normal                ...
... 2006-02-13-12.06.26.168000        0              1 Normal                ...
... 2006-02-13-12.16.25.911000        0              1 Normal                ...
... 2006-02-13-12.06.26.168000        0              1 Normal                ...
... 2006-02-13-12.16.25.911000       62              1 Normal                ...
... 2006-02-13-12.06.26.168000       62              1 Normal                ...
... 2006-02-13-12.16.25.911000        0              1 Normal                ...
... 2006-02-13-12.06.26.168000        0              1 Normal                ...
... 2006-02-13-12.16.25.911000        0              1 Normal                ...
... 2006-02-13-12.06.26.168000        0              1 Normal                ...
... 2006-02-13-12.16.25.911000        0              1 Normal                ...
... 2006-02-13-12.06.26.168000        0              1 Normal                ...
... 2006-02-13-12.16.25.911000        0              1 Normal                ...
... 2006-02-13-12.06.26.168000        0              1 Normal                ...
... 2006-02-13-12.16.25.911000      100              4 Alarm                 ...
... 2006-02-13-12.06.26.168000      100              4 Alarm                 ...
... 2006-02-13-12.16.25.911000        0              1 Normal                ...
... 2006-02-13-12.06.26.168000        0              1 Normal                ...

Output from this query (continued).

... HI_FORMULA               HI_ADDITIONAL_INFO                            
... --------------------...- -------------------...---                     
... 0                        -                                             
... 0                        -                                             
... ((9376 / 9468) * 100)    The short term table space growth rate from   
                               "02/13/2006 11:16:25.000911" to             
                               "02/13/2006 12:16:25.000911" is "N/A" bytes 
                               per second and the long term growth rate    
                               from "02/12/2006 12:16:25.000911" to        
                               "02/13/2006 12:16:25.000911" 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".                       
... ((9376 / 9468) * 100)    The short term table space growth rate from   
                               "02/13/2006 11:06:26.000168" to             
                               "02/13/2006 12:06:26.000168" is "N/A" bytes 
                               per second and the long term growth rate    
                               from "02/12/2006 12:06:26.000168" to        
                               "02/13/2006 12:06:26.000168" 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                        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                        -                                             
... 0                        -                                             
... ((156 / 252) * 100)      The short term table space growth rate from   
                               "02/13/2006 11:16:25.000911" to             
                               "02/13/2006 12:16:25.000911" is "N/A"       
                               bytes per second and the long term growth   
                               rate from "02/12/2006 12:16:25.000911" to   
                               "02/13/2006 12:16:25.000911" 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".                       
... ((156 / 252) * 100)      The short term table space growth rate from   
                               "02/13/2006 11:06:26.000168" to             
                               "02/13/2006 12:06:26.000168" is "N/A"       
                               bytes per second and the long term growth   
                               rate from "02/12/2006 12:06:26.000168" to   
                               "02/13/2006 12:06:26.000168" 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                        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                        -                                             
... 0                        -                                             
... 0                        -                                             
... 0                        -                                             
... ((1504 / 1504) * 100)    The short term table space growth rate        
                               from "02/13/2006 11:16:25.000911" to        
                               "02/13/2006 12:16:25.000911" is "N/A"       
                               bytes per second and the long term growth   
                               rate from "02/12/2006 12:16:25.000911"      
                               to "02/13/2006 12:16:25.000911" 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".      
... ((1504 / 1504) * 100)    The short term table space growth rate        
                               from "02/13/2006 11:06:26.000168" to        
                               "02/13/2006 12:06:26.000168" is "N/A"       
                               bytes per second and the long term growth   
                               rate from "02/12/2006 12:06:26.000168"      
                               to "02/13/2006 12:06:26.000168" 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".            
... 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_HIS 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_TIMESTAMP	TIMESTAMP	The date and time that the alert was generated.
HI_VALUE	SMALLINT	The value of the health indicator.
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.