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 database members of single and multi-partitioned instances and the members and CFs of a Db2® pureScale® instance, including state information where applicable.
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
- SELECT privilege on the administrative view
- CONTROL privilege on the administrative view
- DATAACCESS authority
- DBADM authority
- SQLADM authority
- ACCESSCTRL authority
- SECADM authority
Examples
- A Db2 pureScale instance
- A partitioned or non-partitioned database instance using the High Availability Disaster Recovery (HADR) feature.
SELECT * FROM SYSIBMADM.DB2_MEMBER
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 database instance. This is appropriate for a Db2 pureScale instance, only.
SELECT * FROM SYSIBMADM.DB2_CF
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
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 database instance.
- 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
SELECT * FROM TABLE(DB2_GET_INSTANCE_INFO(null,'','','',null)) as T
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.
SELECT * FROM TABLE(DB2_GET_INSTANCE_INFO(null,'','','',null)) as T
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
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, CATCHUP1, 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:
Use the DB2_INSTANCE_ALERTS administrative view to obtain more information about an alert. |
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, CATCHUP1, 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:
Use the DB2_INSTANCE_ALERTS administrative view to obtain more information about an alert. |
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, CATCHUP1, 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:
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. |
- 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.