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. 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_HI_HIS('',-1)) AS TThe following is an example of 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". 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". 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". 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". 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".
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".
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
| 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. |