MON_GET_SERVERLIST table function - get member priority details

The MON_GET_SERVERLIST table function returns metrics on the server list for the currently connected database as cached on one or more members.

For each active member, the server list contains its connectivity and priority information, which enables a remote client to perform workload balancing (WLB) and automatic client reroute (ACR). The member parameter corresponds to the member where the server list is cached.

Syntax

The schema is SYSPROC.

Table function parameters

An input argument of type INTEGER that specifies a valid member in the same instance as the currently connected database when calling this function. Specify -1 for the current database member, or -2 for all active database members. That is, -1 is equivalent to issuing db2pd -serverlist -db dbname, where dbname is the name of the database currently connected to, only on the current member, whereas -2 is equivalent to issuing the same command for all database members. If the NULL value is specified, -1 is set implicitly.
member_subset_name: An input argument of type VARCHAR(128) that identifies the member subset for which server list metrics should be returned. If the argument is null or the empty string, metrics are returned for all server lists. The default value is NULL.

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

None

Information returned

Table 1. Information returned for MON_GET_SERVERLIST
Column name	Data type	Description
MEMBER	SMALLINT	member - Database member monitor element
CACHED_TIMESTAMP	TIMESTAMP	cached_timestamp - Cached timestamp
HOSTNAME	VARCHAR(255)	hostname - Host name monitor element
PORT_NUMBER	INTEGER	port_number - Port number
SSL_PORT_NUMBER	INTEGER	ssl_port_number - SSL port number
PRIORITY	SMALLINT	priority - Priority value
MEMBER_SUBSET_ID	INTEGER	member_subset_id - Member subset ID
SERVER_LIST_ENTRY_MEMBER	SMALLINT	server_list_entry_member - Member ID for the member in the server list

Usage notes

The MON_GET_SERVERLIST table function returns one row for each member address cached in a server list that matches the input arguments. Each row contains connectivity information and relative priority which enable a remote client to perform workload balancing and automatic client reroute.
The MEMBER column indicates which member cached the server list information; the SUBSET_ID column indicates what member subset the server list information was cached for.

Examples

Assume a connection to database SAMPLE on member 0, where the database has been accessed by a remote client. List all server list metrics as cached on this member for this database:

SELECT MEMBER, CACHED_TIMESTAMP, VARCHAR(HOSTNAME, 30)
   AS HOSTNAME, PORT_NUMBER, SSL_PORT_NUMBER, PRIORITY
   FROM TABLE (MON_GET_SERVERLIST (-1))

Output results for connection to database SAMPLE on member 0.

MEMBER CACHED_TIMESTAMP           HOSTNAME                   ...
------ -------------------------- -------------------------- ...
     0 2011-02-19-17.39.33.000000 coralxib23.torolab.ibm.com ...
     0 2011-02-19-17.39.33.000000 coralxib24.torolab.ibm.com ...

Output for query (continued).

... PORT_NUMBER SSL_PORT_NUMBER  PRIORITY                    
... ----------- ---------------- --------------------------  
...       49712                0                         67  
...       49712                0                         32

Assume a connection to database SAMPLE on member 0 and that the database has been accessed on both members 0 and 1 by a remote client. List all server list metrics as cached on both members for this database:

SELECT MEMBER, CACHED_TIMESTAMP, VARCHAR(HOSTNAME, 30)
   AS HOSTNAME, PORT_NUMBER, SSL_PORT_NUMBER, PRIORITY
   FROM TABLE (MON_GET_SERVERLIST (-2))

Output results for connection to database SAMPLE on member 0, which is currently active on members 0 and 1.

MEMBER CACHED_TIMESTAMP           HOSTNAME                   ...
------ -------------------------- -------------------------- ...
     0 2011-02-19-17.39.33.000000 coralxib23.torolab.ibm.com ...
     0 2011-02-19-17.39.33.000000 coralxib24.torolab.ibm.com ...
     1 2011-02-19-17.39.33.000000 coralxib24.torolab.ibm.com ...
     1 2011-02-19-17.39.33.000000 coralxib23.torolab.ibm.com ...

Output for query (continued).

... PORT_NUMBER SSL_PORT_NUMBER  PRIORITY                    
... ----------- ---------------- --------------------------  
...       49712                0                         67  
...       49712                0                         32  
...       49712                0                         32  
...       49712                0                         67

Assume a connection to the database alias MY_ALIAS exists on member 0 and the database has been accessed on all members. Assume that the connection has been assigned to the member subset MY_SUBSET which includes members 0 and 1 and excludes member 2. List the server list metrics for the member subset MY_SUBSET as cached on member 0.

SELECT MEMBER, SUBSET_ID, CACHED_TIMESTAMP, VARCHAR(HOSTNAME, 30) 
   AS HOSTNAME, PORT_NUMBER, SSL_PORT_NUMBER, PRIORITY
   FROM TABLE (MON_GET_SERVERLIST(0, 'MY_SUBSET')

This query returns the following output:

MEMBER SUBSET_ID CACHED_TIMESTAMP            HOSTNAME                   PORT_NUMBER
------ --------- --------------------------  -------------------------- -----------
0      1         2011-02-19-17.39.33.000000  coralxib14.torolab.ibm.com 33163
0      1         2011-02-19-17.39.33.000000  coralxib15.torolab.ibm.com 33163