DB2_CLUSTER_HOST_STATE administrative view and DB2_GET_CLUSTER_HOST_STATE table function - get information about hosts

The DB2_CLUSTER_HOST_STATE administrative view and DB2_GET_CLUSTER_HOST_STATE table function retrieve information about the hosts that are part of a Db2® pureScale® instance.

Depending on if you are using the administrative view or the table function, refer to one of the following sections:

DB2_CLUSTER_HOST_STATE administrative view
DB2_GET_CLUSTER_HOST_STATE table function

DB2_CLUSTER_HOST_STATE administrative view

The DB2_CLUSTER_HOST_STATE administrative view returns the hosts that are part of a Db2 pureScale instance. You can obtain a list of unique hosts plus their associated state information for the instance.

The schema is SYSIBMADM.

Refer to the Information returned for DB2_CLUSTER_HOST_STATE administrative view and DB2_GET_CLUSTER_HOST_STATE table for a complete list of information that can be returned.

Authorization

One of the following authorizations is required:

SELECT privilege on the DB2_CLUSTER_HOST_STATE administrative view
CONTROL privilege on the DB2_CLUSTER_HOST_STATE administrative view
DATAACCESS authority
DBADM authority
SQLADM authority
ACCESSCTRL authority
SECADM authority

Example

The following example uses the DB2_CLUSTER_HOST_STATE administrative view to obtain information for a Db2 pureScale instance with four members and two CFs.

SELECT * FROM SYSIBMADM.DB2_CLUSTER_HOST_STATE

The output from this query looks like this:

HOSTNAME STATE                           INSTANCE_STOPPED ALERT
-------- ------------------------------- ---------------- --------
so1      ACTIVE                          NO               NO
so2      ACTIVE                          NO               NO
so3      ACTIVE                          NO               NO 
so4      ACTIVE                          NO               NO
so5      ACTIVE                          NO               NO
so6      ACTIVE                          NO               NO

     6 record(s) selected.

DB2_GET_CLUSTER_HOST_STATE table function

The DB2_GET_CLUSTER_HOST_STATE table function returns the same information as the DB2_CLUSTER_HOST_STATE administrative view, but allows you to specify a host name.

Refer to the Information returned for DB2_CLUSTER_HOST_STATE administrative view and DB2_GET_CLUSTER_HOST_STATE table for a complete list of information that can be returned.

Syntax

The schema is SYSPROC.

Table function parameters

hostname

An optional input argument of type VARCHAR(255) that specifies the hostname in short or long form, for which records will be returned. If the IP address is provided instead, no records will be returned. If this parameter is null or an empty string (''), all records are returned.

Authorization

One of the following authorizations is required:

EXECUTE privilege on the DB2_GET_CLUSTER_HOST_STATE table function
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

None

Example

The following example uses the DB2_GET_CLUSTER_HOST_STATE table function to retrieve information about the host named so3 in a Db2 pureScale instance with four members and two CFs.

SELECT * FROM TABLE(DB2_GET_CLUSTER_HOST_STATE('so3')) as T

The following is an example of the output from this query:

HOSTNAME STATE                           INSTANCE_STOPPED ALERT
-------- ------------------------------- ---------------- --------
so3      ACTIVE                          NO               NO 

     1 record(s) selected.

Information returned

Table 1. Information returned for DB2_CLUSTER_HOST_STATE administrative view and DB2_GET_CLUSTER_HOST_STATE
Column name	Data type	Description
HOSTNAME	VARCHAR(255)	hostname - Host name monitor element
STATE	VARCHAR(32)	The hosts state information: ACTIVE - host is available for use by the database manager INACTIVE - host is not available for use by the database manager NULL - host state is inapplicable (for example, it is in a partitioned database environment)
INSTANCE_STOPPED	VARCHAR(8)	Whether the instance is stopped or not on the hosts: YES - instance is stopped on the host NO - instance is not stopped on the host NULL - inapplicable (for example, if in a partitioned database environment)
ALERT	VARCHAR(8)	Information about alerts on the hosts: YES - there is an alert on the host NO - there is no alert on the host NULL - inapplicable (for example, if in a partitioned database environment)