LIST INSTANCE command
The LIST INSTANCE command lists all database members, cluster caching facility and hosts associated with the current database, and provides details about their states.
Scope
This command can be issued from any databasemember that is in the database instance. The same information will be returned from any database member.
Authorization
None
Required connection
Database
Command syntax
Command parameters
- SHOW DETAIL
- The LIST INSTANCE SHOW DETAIL command is an extension of the LIST INSTANCE command. It will also return information about the partition number, the logical port and the high speed communication name. In a Db2® pureScale® environment, the partition information will default to 0 for each partition.
Example (Db2 pureScale instance)
The following outputs are for a four-member, two-cluster caching facility (CF) Db2 pureScale instance:
LIST INSTANCE
MEMBER_ID TYPE STATE HOME_HOST CURRENT_HOST ALERT
--------- ------- ------- --------- ------------ -----
0 MEMBER STARTED hostA hostA NO
1 MEMBER STARTED hostB hostB NO
2 MEMBER STARTED hostC hostC NO
3 MEMBER STARTED hostD hostD NO
128 CF PRIMARY hostE - NO
129 CF PEER hostF - NO
HOSTNAME STATE INSTANCE_STOPPED ALERT
--------- ------ ---------------- -----
hostA ACTIVE NO NO
hostB ACTIVE NO NO
hostC ACTIVE NO NO
hostD ACTIVE NO NO
hostE ACTIVE NO NO
hostF ACTIVE NO NO
LIST INSTANCE SHOW DETAIL
ID TYPE STATE HOME_HOST CURRENT_HOST ALERT PARTITION_NUMBER LOGICAL_PORT NETNAME
----- ------- ------- --------- ------------ ----- ---------------- ------------ -------
0 MEMBER STARTED hostA hostA NO 0 0 hostA-ib0
1 MEMBER STARTED hostB hostB NO 0 0 hostB-ib0
2 MEMBER STARTED hostC hostC NO 0 0 hostC-ib0
3 MEMBER STARTED hostD hostD NO 0 0 hostD-ib0
128 CF PRIMARY hostE - NO 0 0 hostE-ib0
129 CF PEER hostF - NO 0 0 hostF-ib0
HOSTNAME STATE INSTANCE_STOPPED ALERT
-------- ------ ---------------- -----
hostA ACTIVE NO NO
hostB ACTIVE NO NO
hostC ACTIVE NO NO
hostD ACTIVE NO NO
hostE ACTIVE NO NO
hostF ACTIVE NO NOExample (partitioned database instance with integrated HA)
The following outputs are for a four host partitioned database instance with integrated HA:
LIST INSTANCE
MEMBER_ID TYPE STATE HOME_HOST CURRENT_HOST ALERT
--------- ------- ------- --------- ------------ -----
0 MEMBER STARTED hostA hostA NO
1 MEMBER STARTED hostB hostB NO
2 MEMBER STARTED hostC hostC NO
3 MEMBER STARTED hostD hostD NO
HOSTNAME STATE INSTANCE_STOPPED ALERT
-------- ------ ---------------- -----
hostA ACTIVE - NO
hostB ACTIVE - NO
hostC ACTIVE - NO
hostD ACTIVE - NOA non-partitioned single host database instance will return a single row for each table in the following output from LIST INSTANCE SHOW DETAIL:
LIST INSTANCE SHOW DETAIL
ID TYPE STATE HOME_HOST CURRENT_HOST ALERT PARTITION_NUMBER LOGICAL_PORT NETNAME
----- ------ ------- --------- ------------ ----- ---------------- ------------ -------
0 MEMBER STARTED hostA hostA NO 0 0 hostA-ib0
1 MEMBER STARTED hostB hostB NO 1 0 hostB-ib0
2 MEMBER STARTED hostC hostC NO 2 0 hostC-ib0
3 MEMBER STARTED hostD hostD NO 3 0 hostD-ib0
HOSTNAME STATE INSTANCE_STOPPED ALERT
-------- ------ ---------------- -----
hostA ACTIVE - NO
hostB ACTIVE - NO
hostC ACTIVE - NO
hostD ACTIVE - N0Example (partitioned database instance without integrated HA)
The following outputs are for a 4 host partitioned database instance without integrated HA:
LIST INSTANCE
MEMBER_ID TYPE STATE HOME_HOST CURRENT_HOST ALERT
--------- ------- ------- --------- --------...- -----
0 MEMBER - hostA - -
1 MEMBER - hostB - -
2 MEMBER - hostC - -
3 MEMBER - hostD - -
HOSTNAME STATE INSTANCE_STOPPED ALERT
-------- ------ ---------------- -----
hostA - - -
hostB - - -
hostC - - -
hostD - - -A non-partitioned single host database instance will return a single row for each table in the following output for LIST INSTANCE SHOW DETAIL:
LIST INSTANCE SHOW DETAIL
ID TYPE STATE HOME_HOST CURRENT_HOST ALERT PARTITION_NUMBER LOGICAL_PORT NETNAME
----- ------ ------- --------- ------------ ----- ---------------- ------------ ---------
0 MEMBER - hostA - - 0 0 hostA-ib0
1 MEMBER - hostB - - 1 0 hostB-ib0
2 MEMBER - hostC - - 2 0 hostC-ib0
3 MEMBER - hostD - - 3 0 hostD-ib0
HOSTNAME STATE INSTANCE_STOPPED ALERT
-------- ------ ---------------- -----
hostA - - -
hostB - - -
hostC - - -
hostD - - -Example (non-partitioned database instance with integrated HA)
The following output is from LIST INSTANCE for a single host database instance with integrated HA:
MEMBER_ID TYPE STATE HOME_HOST CURRENT_HOST ALERT
--------- ------ ------- --------- ------------ -----
0 MEMBER STARTED hostA hostA NO
HOSTNAME STATE INSTANCE_STOPPED ALERT
-------- ------ ---------------- -----
hostA ACTIVE - NOExample (non-partitioned database instance without integrated HA)
The following output is from LIST INSTANCE for a single host database instance without integrated HA:
MEMBER_ID TYPE STATE HOME_HOST CURRENT_HOST ALERT
--------- ------- ------- --------- --------...- -----
0 MEMBER - hostA - -
HOSTNAME STATE INSTANCE_STOPPED ALERT
-------- ------ ---------------- -----
hostA - - -