DB2 Version 10.1 for Linux, UNIX, and Windows

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


>>-MON_GET_SERVERLIST--(--member--)----------------------------><

The schema is SYSPROC.

Table function parameters

member: 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.

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

Example

Example 1: 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

Example 2: 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

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