HEALTH_DB_HI_HIS table function
The HEALTH_DB_HI_HIS table function returns health indicator history 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 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_HIS('',-1)) AS TThe following is an example of output from this query.
SNAPSHOT_TIMESTAMP HI_ID DB_NAME HI_VALUE ...
-------------------------- -----...- -------...- -------- ...
2006-02-13-12.30.26.325627 1001 SAMPLE 0 ...
... ...
2006-02-13-12.30.26.325627 1002 SAMPLE 0 ...
... ...
2006-02-13-12.30.26.325627 1003 SAMPLE 0 ...
... ...
2006-02-13-12.30.26.325627 1005 SAMPLE 3 ...
... ...
2006-02-13-12.30.26.325627 1008 SAMPLE 2 ...
... ...
2006-02-13-12.30.26.325627 1010 SAMPLE 0 ...
... ...
2006-02-13-12.30.26.325627 1014 SAMPLE 73 ...
... ...
2006-02-13-12.30.26.325627 1015 SAMPLE 1 ...
... ...
2006-02-13-12.30.26.325627 1018 SAMPLE 1 ...
... ...
2006-02-13-12.30.26.325627 1022 SAMPLE 1 ...
...
Output from this query (continued).
... HI_TIMESTAMP HI_ALERT_STATE HI_ALERT_STATE_DETAIL ...
... -------------------------- -------------------- --------------------- ...
... 2006-02-13-12.21.25.649000 1 Normal ...
... ... ...
... 2006-02-13-12.21.25.649000 1 Normal ...
... ... ...
... 2006-02-13-12.20.25.182000 1 Normal ...
... ... ...
... 2006-02-13-12.16.25.911000 1 Normal ...
... ... ...
... 2006-02-13-12.16.25.911000 1 Normal ...
... ... ...
... 2006-02-13-12.16.25.911000 1 Normal ...
... ... ...
... 2006-02-13-12.21.25.649000 1 Normal ...
... ... ...
... 2006-02-13-12.29.55.461000 2 Attention ...
... ... ...
... 2006-02-13-12.29.25.281000 2 Attention ...
... ... ...
... 2006-02-13-12.27.55.743000 2 Attention ...
... ...
Output from this query (continued).
... HI_FORMULA ...
... ----------------------------------------- ...
... 0 ...
... ... ...
... ((0 / 5000) * 100) ...
... ...
... ...
... ...
... ...
... ...
... ...
... ...
... ... ...
... (((0 - 0) / ((68 - 0) + 1)) * 100) ...
... ...
... ...
... ...
... ...
... ...
... ... ...
... ((698410 / (698410 + 19701590)) * 100) ...
... ...
... ...
... ...
... ...
... ...
... ... ...
... ((3920 / (50 * 4096)) * 100) ...
... ... ...
... ((0 / 4) * 100) ...
... ... ...
... ((4521984 / 6160384) * 100) ...
... ... ...
... - ...
... ...
... ...
... ...
... ...
... ...
... ...
... ...
... ... ...
... - ...
... ...
... ...
... ...
... ...
... ...
... ...
... ...
... ... ...
... - ...
... ...
... ...
... ... ...
Output from this query (continued).
... HI_ADDITIONAL_INFO
... ---------------------------------------
... -
...
... The high watermark for shared sort
... memory is "15". "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 "15". 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 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
| 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. |