DB2 Version 10.1 for Linux, UNIX, and Windows

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


>>-LIST INSTANCE--+-------------+------------------------------><
                  '-SHOW DETAIL-'

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               NO

Example (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 -                NO

A 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 -                N0

Example (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 -                NO

Example (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    -      -                -