DB2 Version 9.7 for Linux, UNIX, and Windows

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 in Version 9.7. For more information, see Health monitor has been deprecated.

Syntax


>>-HEALTH_GET_ALERT_CFG--(--objecttype--,--cfg_level--,--------->

>--dbname--,--objectname--)------------------------------------><

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

EXECUTE privilege on the HEALTH_GET_ALERT_CFG table function.

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 example is a sample 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 example is a sample 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 example is a sample 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

Table 1. Information returned by the HEALTH_GET_ALERT_CFG table function
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.
ID	BIGINT	Health indicator ID.
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.