DB2 Version 10.1 for Linux, UNIX, and Windows

DB2_MEMBER and DB2_CF administrative views and DB2_GET_INSTANCE_INFO table function

The DB2_MEMBER and DB2_CF administrative views and DB2_GET_INSTANCE_INFO table function return information about the members and CFs of a DB2® pureScale® instance, including state information, where applicable.

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

DB2_MEMBER and DB2_CF administrative views
DB2_GET_INSTANCE_INFO table function

DB2_MEMBER and DB2_CF administrative views

The DB2_MEMBER administrative view returns such information as the machine name on which a member is currently running, its state, whether any alerts are present, and the name of the high speed interconnect for internal database communications.

The DB2_CF administrative view returns similar information for cluster caching facilities (also known as CFs).

The schema is SYSIBMADM.

Refer to the tables in Information returned for a complete list of information that can be returned.

Authorization

One of the following authorizations is required:

SELECT privilege on the administrative view
CONTROL privilege on the administrative view
DATAACCESS authority
DBADM authority
SQLADM authority

Examples

The following example uses the DB2_MEMBER administrative view to display the status of all members in the DB2 instance. This is appropriate for the following kinds of instance:

A DB2 pureScale instance
A partitioned or non-partitioned database instance using the DB2 High Availability Disaster Recovery (HADR) feature.

Execute the following query to display the status of all members in the DB2 instance:

SELECT * FROM SYSIBMADM.DB2_MEMBER

The following is an example of output from this query.

ID     HOME_HOST CURRENT_HOST STATE      ALERT
------ --------- ------------ ---------- -----
     0 so1       so1          STARTED    NO
     2 so2       so2          STARTED    NO
     4 so3       so3          STARTED    NO

     3 record(s) selected.

The following example uses the DB2_CF administrative view to display the status of all CFs in the DB2 instance. This is appropriate for a DB2 pureScale instance, only.

Execute the following query to display the status of all cluster caching facilities in the DB2 instance:

SELECT * FROM SYSIBMADM.DB2_CF

The following is an example of output from this query.

ID     CURRENT_HOST STATE      ALERT
------ ------------ ---------- -----
   128 so5          PRIMARY    NO
   129 so6          PEER       NO
   
     2 record(s) selected.

DB2_GET_INSTANCE_INFO table function

The DB2_GET_INSTANCE_INFO table function returns the same information as the DB2_MEMBER and DB2_CF administrative views, but enables you to filter the information returned by passing input parameters, such as the current host. The current host is the host where the member is currently running.

Syntax


>>-DB2_GET_INSTANCE_INFO--(--id--,--home_host--,--current_host--,--type--,--db_partition_num--)-><

The schema is SYSPROC.

Table function parameters

id

An optional input argument of type INTEGER that specifies a valid member or cluster caching facility identifier. Entries are returned for all database members or cluster caching facilities that match the input. If this parameter is null or -2 all records are returned. If this parameter is -1, information for the currently connected member is returned.

home_host

An optional input argument of type VARCHAR(255) that specifies the home host for which records are to be returned. The home host is the original host name associated with a particular member when the member was created. Use the short format of the host name and not an IP address for the home_host argument. If this parameter is null or an empty string, all records are returned.

current_host

An optional input argument of type VARCHAR(255) that specifies the current host for which records are to be returned. The current host is the host where the member is currently running, which might not be the same as the home host, if, for example, the member had to be started on another host as part of a restart light operation. Use the short format of the host name and not an IP address for the current_host argument.

type

An optional input argument of type VARCHAR(32) that specifies whether to retrieve information for members or cluster caching facilities. The possible values are:

CF – Returns all records for the cluster caching facilities in the current DB2 pureScale instance
MEMBER – Returns all records for the members in the current DB2 instance.

If this parameter is null or an empty string, all records returned.

db_partition_num

An optional input argument of type INTEGER that specifies a valid database partition number. Information is returned for all database partition numbers that match the input. If this parameter is null or -2, all records are returned. If this parameter is -1, information for the currently connected member is returned. For a DB2 pureScale instance, the only valid inputs are 0 or null; all other input values will not return data.

Authorization

EXECUTE privilege on the DB2_GET_INSTANCE_INFO table function.

Default PUBLIC privilege

None

Examples

The following example uses the DB2_GET_INSTANCE_INFO table function to retrieve information from a DB2 pureScale instance with four members and two cluster caching facilities. In this example, member number 2 is being restarted on the host called so1:

SELECT * FROM TABLE(DB2_GET_INSTANCE_INFO(null,'','','',null)) as T

The following is an example of output from this query.

ID  HOME_HOST CURRENT_HOST TYPE   STATE      ALERT DB_P..._NUM LOGICAL_PORT NETNAME
--- --------- ------------ ------ ---------- ----- ----------- ------------ -------
  0 so1       so1          MEMBER STARTED    NO              0            0 so1-ib0
  1 so2       so1          MEMBER RESTARTING NO              0            0 so2-ib0
  2 so3       so3          MEMBER STARTED    NO              0            0 so3-ib0
  3 so4       so4          MEMBER STARTED    NO              0            0 so4-ib0
128 so5       so5          CF     PRIMARY    NO              -            0 so5-ib0
129 so6       so6          CF     PEER       NO              -            0 so6-ib0

     6 record(s) selected.

The following example uses the DB2_GET_INSTANCE_INFO table function to retrieve information from a partitioned database instance with four members using the DB2 High Availability Disaster Recovery (HADR) feature:

SELECT * FROM TABLE(DB2_GET_INSTANCE_INFO(null,'','','',null)) as T

The following is an example of output from this query.

ID HOME_HOST CURRENT_HOST TYPE   STATE   ALERT DB_PARTITION_NUM LOGICAL_PORT NETNAME
-- --------- ------------ ------ ------- ----- ---------------- ------------ -----
 0 so1       so1          MEMBER STARTED NO                   0            0 so1-ib0
 2 so2       so2          MEMBER STARTED NO                   2            0 so2-ib0
 4 so3       so3          MEMBER STARTED NO                   4            0 so3-ib0
 7 so4       so4          MEMBER STARTED NO                   7            0 so4-ib0
     4 record(s) selected.

Information returned

Table 1. Information returned for DB2_MEMBER administrative view
Column name	Data type	Description
ID	SMALLINT	id - cluster caching facility identification monitor element
HOME_HOST	VARCHAR(255)	The machine which was associated with the member when it was first added to the instance.
CURRENT_HOST	VARCHAR(255)	The machine name on which the member is currently running.
STATE	VARCHAR(32)	The state of the member or cluster caching facility. The potential states for a member are STARTED, STOPPED, RESTARTING, WAITING_FOR_FAILBACK, ERROR, and UNKNOWN. The potential states for a cluster caching facility include STOPPED, RESTARTING, BECOMING_PRIMARY, PRIMARY, CATCHUP¹, PEER, ERROR, and UNKNOWN. The NULL state for a member or a cluster caching facility indicates that state is inapplicable. For example, if in a partitioned database environment. See Values for member and cluster caching facility states and alerts for more information.
ALERT	VARCHAR(8)	Information about alerts on the instance: YES - there is an alert NO - there are no alerts The NULL state for a member or a cluster caching facility indicates that state is inapplicable. For example, if in a partitioned database environment. Use the DB2_INSTANCE_ALERTS administrative view to obtain more information about an alert.

Table 2. Information returned for DB2_CF administrative view
Column name	Data type	Description
ID	SMALLINT	id - cluster caching facility identification monitor element
CURRENT_HOST	VARCHAR(255)	The machine name on which the member is currently running.
STATE	VARCHAR(32)	The state of the member or cluster caching facility. The potential states for a member are STARTED, STOPPED, RESTARTING, WAITING_FOR_FAILBACK, ERROR, and UNKNOWN. The potential states for a cluster caching facility include STOPPED, RESTARTING, BECOMING_PRIMARY, PRIMARY, CATCHUP¹, PEER, ERROR, and UNKNOWN. The NULL state for a member or a cluster caching facility indicates that state is inapplicable. For example, if in a partitioned database environment. See Values for member and cluster caching facility states and alerts for more information.
ALERT	VARCHAR(8)	Information about alerts on the instance: YES - there is an alert NO - there are no alerts The NULL state for a member or a cluster caching facility indicates that state is inapplicable. For example, if in a partitioned database environment. Use the DB2_INSTANCE_ALERTS administrative view to obtain more information about an alert.

Table 3. Information returned for DB2_GET_INSTANCE_INFO table function
Column name	Data type	Description
ID	SMALLINT	id - cluster caching facility identification monitor element
HOME_HOST	VARCHAR(255)	The machine which was associated with the member when it was first added to the instance.
CURRENT_HOST	VARCHAR(255)	The machine name on which the member is currently running.
TYPE	VARCHAR(32)	Either 'MEMBER' or 'CF'.
STATE	VARCHAR(32)	The state of the member or cluster caching facility. The potential states for a member are STARTED, STOPPED, RESTARTING, WAITING_FOR_FAILBACK, ERROR, and UNKNOWN. The potential states for a cluster caching facility include STOPPED, RESTARTING, BECOMING_PRIMARY, PRIMARY, CATCHUP¹, PEER, ERROR, and UNKNOWN. The NULL state for a member or a cluster caching facility indicates that state is inapplicable. For example, if in a partitioned database environment. See Values for member and cluster caching facility states and alerts for more information.
ALERT	VARCHAR(8)	Information about alerts on the instance: YES - there is an alert NO - there are no alerts The NULL state for a member or a cluster caching facility indicates that state is inapplicable. For example, if in a partitioned database environment. Use the DB2_INSTANCE_ALERTS administrative view to obtain more information about an alert.
DB_PARTITION_NUM	SMALLINT	The database partition number for this member.
LOGICAL_PORT	SMALLINT	The logical port number of the member or cluster caching facility.
NETNAME	VARCHAR(255)	The name of the high speed interconnect for internal database communications.

Note:

CATCHUP includes a percentage value as part of the returned state. This percentage value represents the amount to which the secondary cluster caching facility has caught up to the current state of the primary caching facility.