DB2 10.5 for Linux, UNIX, and Windows

HEALTH_DB_HI table function

The HEALTH_DB_HI table function returns health indicator 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 in Version 9.7. It is not supported in DB2® pureScale® environments. For more information, see Health monitor has been deprecated..

Syntax


>>-HEALTH_DB_HI--(--dbname--,--member--)-----------------------><

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 all databases under the database instance.
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_DB_HI('',-1)) AS T

The following is an example of output from this query.

SNAPSHOT_TIMESTAMP         HI_ID     DB_NAME     HI_VALUE ...
-------------------------- -----...- -------...- -------- ...
2006-02-13-12.30.23.949888      1001 SAMPLE             0 ...
2006-02-13-12.30.23.949888      1002 SAMPLE             0 ...
2006-02-13-12.30.23.949888      1003 SAMPLE             0 ...
2006-02-13-12.30.23.949888      1005 SAMPLE             6 ...
2006-02-13-12.30.23.949888      1006 SAMPLE            53 ...
2006-02-13-12.30.23.949888      1008 SAMPLE             3 ...
2006-02-13-12.30.23.949888      1010 SAMPLE             0 ...
2006-02-13-12.30.23.949888      1014 SAMPLE            74 ...
2006-02-13-12.30.23.949888      1015 SAMPLE             1 ...
2006-02-13-12.30.23.949888      1018 SAMPLE             1 ...
2006-02-13-12.30.23.949888      1022 SAMPLE             1 ...
                                                             
  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              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.30.25.640000              2 Attention             ...
... 2006-02-13-12.30.25.640000              2 Attention             ...
... 2006-02-13-12.29.25.281000              2 Attention             ...

Output from this query (continued).

... HI_FORMULA                                   ...
... ----------------------------------------...- ...
... 0                                            ...
... ((0 / 5000) * 100)                           ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
... (((0 - 0) / ((118 - 0) + 1)) * 100)          ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
... ((1170384 / (1170384 + 19229616)) * 100)     ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
... ((11155116032 / 21138935808) * 100)          ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
... ((5264 / (50 * 4096)) * 100)                 ...
... ((0 / 5) * 100)                              ...
... ((4587520 / 6160384) * 100)                  ...
... -                                            ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
... -                                            ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
...                                              ...
... -                                            ...
...                                              ...
...                                              ...

Output from this query (continued).

... HI_ADDITIONAL_INFO                         
... ----------------------------------------   
... -                                          
... The high watermark for shared sort         
...   memory is "57". "99"% of the time        
...   the sort heap allocation is less         
...   than or equal to "246". The sort         
...   heap (sortheap) database                 
...   configuration parameter is set           
...   to "256". The high watermark for         
...   private sort memory is "0".              
... The sort heap (sortheap) database          
...   configuration parameter is set to        
...   "256". The high watermark for            
...   private sort memory is "57". The         
...   high watermark for shared sort           
...   memory is "0"                            
... The following are the related              
...   database configuration parameter         
...   settings: logprimary is "3",             
...   logsecond is "2", and logfilsiz          
...   is "1000". The application with          
...   the oldest transaction is "712".         
... The following are the related              
...   database configuration parameter         
...   settings: logprimary is "3",             
...   logsecond is "2", and logfilsiz          
...   is "1000", blk_log_dsk_ful is            
...   "NO", logarchmeth1 is "OFF" and          
...   logarchmeth2 is "OFF".                   
... -                                          
... -                                          
... -                                          
... The scope setting in the reorganization    
...   policy is "TABSCHEMA NOT LIKE 'SYS%'".   
...   Automatic reorganization (AUTO_REORG)    
...   for this database is set to "OFF".       
...   The longest estimated reorganization     
...   time is "N/A".                           
... The last successful backup was taken       
...   at "N/A". The log space consumed since   
...   this last backup has been "N/A" 4KB      
...   pages. Automation for database backup    
...   is set to "OFF". The last automated      
...   backup returned with SQLCODE = "N/A".    
...   The longest estimated backup time        
...   is "N/A".                                
... The scope is "N\A". Automatic              
...   statistics collection (AUTO_RUNSTATS)    
...   is set to "OFF".

Information returned

Table 1. Information returned by the HEALTH_DB_HI table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp
HI_ID	BIGINT	A number that uniquely identifies the health indicator in the snapshot data stream.
DB_NAME	VARCHAR(128)	db_name - Database name
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.