HEALTH_GET_ALERT_CFG table function
Returns health alert configuration settings for various object types (database manager, database, table space, table space container) and for various configuration levels (install default, global, and object).
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
-
objecttype
- An input argument of type VARCHAR(3) that indicates the object
type. The value must be one of the following case-insensitive values:
- 'DBM' for database manager
- 'DB' for database
- 'TS' for table space
- 'TSC' for table space container
Note: Leading and trailing spaces will be ignored.
cfg_level
- An input argument of type VARCHAR(1) that indicates the configuration
level. The value must be one of the following case-insensitive values:
- For objecttype 'DBM': 'D' for install default; 'G' or 'O' for instance level.
- For objecttype that is not 'DBM': 'D' for install default; 'G' for global level; 'O' for object level.
dbname
- An input argument of type VARCHAR(128) that indicates the database name. The database name must be provided if objecttype is 'DB', 'TS', or 'TSC', and cfg_level is 'O'. For all other combinations of objecttype and cfg_level, the dbname parameter should be NULL (or an empty string). objectname
- An input argument of type VARCHAR(1024) that indicates the object name, for example, <table space name> or <table space name>.<container name>. The object name must be provided if objecttype is 'TS' or 'TSC', and cfg_level is 'O'. For all other combinations of objecttype and cfg_level, the objectname parameter should be NULL (or an empty string).
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.
Examples
Example 1: Retrieve the
object level alert configuration settings for database SAMPLE.
SELECT * FROM TABLE(SYSPROC.HEALTH_GET_ALERT_CFG('DB','O','SAMPLE',''))
AS ALERT_CFG The following is an example
of output for this query.
OBJECTTYPE CFG_LEVEL DBNAME OBJECTNAME ...
---------- --------- --------...----- -------------...------ ...
DB O SAMPLE ...
DB O SAMPLE ...
DB O SAMPLE ...
DB O SAMPLE ...
DB O SAMPLE ...
DB O SAMPLE ...
DB O SAMPLE ...
DB O SAMPLE ...
DB O SAMPLE ...
DB O SAMPLE ...
DB O SAMPLE ...
DB O SAMPLE ...
DB O SAMPLE ...
DB O SAMPLE ...
... ...
Output for this query (continued).
... ID IS_DEFAULT WARNING_THRESHOLD ...
... -------------------- ---------- -------------------- ...
... 1001 0 0 ...
... 1018 0 0 ...
... 1015 0 0 ...
... 1022 0 0 ...
... 1002 1 95 ...
... 1003 1 30 ...
... 1004 1 60 ...
... 1005 1 75 ...
... 1006 1 75 ...
... 1007 1 5 ...
... 1008 1 75 ...
... 1009 1 5 ...
... 1010 1 50 ...
... 1011 1 80 ...
Output for this query (continued).
... ALARM_THRESHOLD SENSITIVITY EVALUATE ACTION_ENABLED
... -------------------- -------------------- -------- --------------
... 0 0 0 0
... 0 0 1 0
... 0 0 1 0
... 0 0 1 0
... 100 0 0 0
... 50 0 1 0
... 30 0 1 0
... 85 0 1 0
... 85 0 1 0
... 10 0 1 0
... 85 0 1 0
... 10 0 1 0
... 70 0 1 0
... 70 0 0 0
Example 2: Retrieve the warning and alarm
thresholds for the health indicator ID '2002' for table space USERSPACE1
in database SAMPLE.
SELECT WARNING_THRESHOLD, ALARM_THRESHOLD
FROM TABLE(SYSPROC.HEALTH_GET_ALERT_CFG('TS','O','SAMPLE','USERSPACE1'))
AS T WHERE ID = 2002
The following is an example of output for this
query.
WARNING_THRESHOLD ALARM_THRESHOLD
-------------------- --------------------
80 90
SQL22004N Cannot find the requested configuration for the given object.
Returning default configuration for "tablespaces".
1 record(s) selected with 1 warning messages printed.Usage notes
The HEALTH_GET_IND_DEFINITION table function can be used to map health indicator IDs to the health indicator names.
Example: Retrieve the warning and alarm
thresholds for the health indicator Tablespace Utilization (ts.ts_util)
for table space USERSPACE1 in database SAMPLE.
WITH HINAME(ID) AS (SELECT ID FROM TABLE(SYSPROC.HEALTH_GET_IND_DEFINITION('')) AS W
WHERE NAME = 'ts.ts_util')
SELECT WARNING_THRESHOLD, ALARM_THRESHOLD
FROM TABLE(SYSPROC.HEALTH_GET_ALERT_CFG('TS','O','SAMPLE','USERSPACE1')) AS T,
HINAME AS H
WHERE T.ID = H.ID
The following is an example of output for this
query.
WARNING_THRESHOLD ALARM_THRESHOLD
-------------------- --------------------
80 90
SQL22004N Cannot find the requested configuration for the given object.
Returning default configuration for "tablespaces".
1 record(s) selected with 1 warning messages printed.Information returned
| Column name | Data type | Description |
|---|---|---|
| OBJECTTYPE | VARCHAR(3) | Object type. |
| CFG_LEVEL | CHAR(1) | Configuration level. |
| DBNAME | VARCHAR(128) | Database name. |
| OBJECTNAME | VARCHAR(512) | object_name - Object name monitor element |
| ID | BIGINT | id - cluster caching facility identification monitor element |
| IS_DEFAULT | SMALLINT | Whether the settings is the default: 1 if it is the default, 0 if it is not the default or Null if not applicable. |
| WARNING_THRESHOLD | BIGINT | Warning threshold. Null if not applicable. |
| ALARM_THRESHOLD | BIGINT | Alarm threshold. Null if not applicable. |
| SENSITIVITY | BIGINT | Health indicator sensitivity. |
| EVALUATE | SMALLINT | 1 if this health indicator is being evaluated or 0 if it is not being evaluated. |
| ACTION_ENABLED | SMALLINT | 1 if an action is enabled to run upon an alert occurrence or 0 if no action is enabled to run upon an alert occurrence. |