Skip to main contentIBM dmctop Wiki

Throughput / Members

Purpose

Members view shows some basic statistics about individual members. Member specific memory and transaction log usage are found here.

Screenshot

image

Metrics Shown

In Grid

Followings are the temporary data set created and used in this section:

MEM_TAB =

SELECT   MEMBER, 
         SUM(MEMORY_POOL_USED)     AS MEM_POOL_USED, 
         SUM(MEMORY_POOL_USED_HWM) AS MEM_POOL_USED_HWM 
FROM     TABLE(MON_GET_MEMORY_POOL(NULL,CURRENT_SERVER,#MEMBER#))
GROUP BY MEMBER

CONN_TAB =

SELECT   MEMBER , 
         SUM(TOTAL_BUFFERS_SENT) AS TOTAL_BUFFERS_SENT , 
         SUM(TOTAL_BUFFERS_RCVD) AS TOTAL_BUFFERS_RCVD 
FROM     TABLE(MON_GET_FCM_CONNECTION_LIST(#MEMBER#)) 
GROUP BY MEMBER

Source:

CURRENT_SERVER

MON_GET_MEMORY_POOL.MEMORY_POOL_USED

MON_GET_MEMORY_POOL.MEMORY_POOL_USED_HWM

MON_GET_MEMORY_POOL.MEMBER

MON_GET_FCM_CONNECTION_LIST.TOTAL_BUFFERS_SENT

MON_GET_FCM_CONNECTION_LIST.TOTAL_BUFFERS_RCVD

MON_GET_FCM_CONNECTION_LIST.MEMBER

Mem skew%

The percentage of amount of committed memory in use by this memory pool skewed from the average.

= SKEW_MEM_POOL_USED / MAX_MEM_POOL_USED

SKEW_MEM_POOL_USED =

 SELECT   (MAX(MEM_POOL_USED) - SUM(MEM_POOL_USED) / (SELECT COUNT(*) FROM   MEM_TAB))
 FROM            CONN_TAB 
 LEFT OUTER JOIN MEM_TAB 
 ON              MEM_TAB.MEMBER=CONN_TAB.MEMBER

MAX_MEM_POOL_USED = 

 SELECT    MAX(MEM_POOL_USED)
 FROM            CONN_TAB 
 LEFT OUTER JOIN MEM_TAB 
 ON              MEM_TAB.MEMBER=CONN_TAB.MEMBER

Pool hwm skew%

The percentage of the highest amount of memory assigned to this pool since it was created skewed from the average.

= SKEW_MEM_POOL_USED_HWM/ MAX_MEM_POOL_USED_HWM

SKEW_MEM_POOL_USED_HWM=

 SELECT   (MAX(MEM_POOL_USED_HWM) - SUM(MEM_POOL_USED_HWM) / (SELECT COUNT(*) FROM   MEM_TAB))
 FROM            CONN_TAB 
 LEFT OUTER JOIN MEM_TAB 
 ON              MEM_TAB.MEMBER=CONN_TAB.MEMBER

MAX_MEM_POOL_USED_HWM= 

 SELECT    MAX(MEM_POOL_USED_HWM)
 FROM            CONN_TAB 
 LEFT OUTER JOIN MEM_TAB 
 ON              MEM_TAB.MEMBER=CONN_TAB.MEMBER

Buffsent skew%

The percentage of the total number of FCM buffers sent from the current database member to a remote database member skewed from the average.

= SKEW_TOTAL_BUFFERS_SENT/ MAX_TOTAL_BUFFERS_SENT

SKEW_TOTAL_BUFFERS_SENT=

 SELECT   (MAX(TOTAL_BUFFERS_SENT) - SUM(TOTAL_BUFFERS_SENT) / (SELECT COUNT(*) FROM  CONN_TAB))
 FROM            CONN_TAB 
 LEFT OUTER JOIN MEM_TAB 
 ON              MEM_TAB.MEMBER=CONN_TAB.MEMBER

MAX_TOTAL_BUFFERS_SENT= 

 SELECT    MAX(TOTAL_BUFFERS_SENT)
 FROM            CONN_TAB 
 LEFT OUTER JOIN MEM_TAB 
 ON              MEM_TAB.MEMBER=CONN_TAB.MEMBER

Bufrec skew%

The percentage of the total number of FCM buffers received from a remote database member skewed from the average.

= SKEW_TOTAL_BUFFERS_RCVD/ MAX_TOTAL_BUFFERS_RCVD

SKEW_TOTAL_BUFFERS_RCVD=

 SELECT   (MAX(TOTAL_BUFFERS_RCVD) - SUM(TOTAL_BUFFERS_RCVD) / (SELECT COUNT(*) FROM  CONN_TAB))
 FROM            CONN_TAB 
 LEFT OUTER JOIN MEM_TAB 
 ON              MEM_TAB.MEMBER=CONN_TAB.MEMBER

MAX_TOTAL_BUFFERS_RCVD= 

 SELECT    MAX(TOTAL_BUFFERS_RCVD)
 FROM            CONN_TAB 
 LEFT OUTER JOIN MEM_TAB 
 ON              MEM_TAB.MEMBER=CONN_TAB.MEMBER

In List

Max mem member

The member of max amount of committed memory in use.

Source: MON_GET_MEMORY_POOL(NULL, CURRENT_SERVER, #MEMBER#).SUM(MEMORY_POOL_USED)

Max memory

The max amount of committed memory in use.

Source: MON_GET_MEMORY_POOL(NULL, CURRENT_SERVER, #MEMBER#).SUM(MEMORY_POOL_USED)

Min mem member

The member of min amount of committed memory in use.

Source: MON_GET_MEMORY_POOL(NULL, CURRENT_SERVER, #MEMBER#).SUM(MEMORY_POOL_USED)

Min memory

The min amount of committed memory in use.

Source: MON_GET_MEMORY_POOL(NULL, CURRENT_SERVER, #MEMBER#).SUM(MEMORY_POOL_USED)

In Table

Followings are the temporary data set created and used in this section:

CONN_TAB =

SELECT MEMBER, 
       SUBSTR(CONNECTION_STATUS, 1, 10) AS CONNECTION_STATUS, 
       SUM(TOTAL_BUFFERS_SENT)          AS TOTAL_BUFFERS_SENT, 
       SUM(TOTAL_BUFFERS_RCVD)          AS TOTAL_BUFFERS_RCVD 
FROM   TABLE(MON_GET_FCM_CONNECTION_LIST(#MEMBER#)) 
GROUP  BY MEMBER, CONNECTION_STATUS

MEM_TAB1 =

SELECT MEMBER, 
       SUM(MEMORY_POOL_USED) * 1024     AS MEM_POOL_USED, 
       SUM(MEMORY_POOL_USED_HWM) * 1024 AS MEM_POOL_USED_HWM 
FROM   TABLE(MON_GET_MEMORY_POOL(NULL, CURRENT_SERVER, #MEMBER#)) 
WHERE  MEMORY_SET_TYPE NOT IN( 'PRIVATE' ) 
GROUP  BY MEMBER

MEM_TAB2 =

SELECT MEMBER, 
       COUNT(*) AS NUM_MEM_POOL 
FROM   TABLE(MON_GET_MEMORY_POOL(NULL, CURRENT_SERVER, #MEMBER#)) 
WHERE  MEMORY_SET_TYPE IN( 'APPLICATION', 'DATABASE' ) 
GROUP  BY MEMBER

TSP_TAB =

SELECT MEMBER, 
       SUM(TBSP_USED_PAGES * TBSP_PAGE_SIZE)  AS SPACE_USED, 
       SUM(TBSP_TOTAL_PAGES * TBSP_PAGE_SIZE) AS TOTAL_SPACE 
FROM   TABLE(MON_GET_TABLESPACE('', #MEMBER#)) 
GROUP  BY MEMBER

LOG_TAB =

SELECT MEMBER, 
       CURRENT_ACTIVE_LOG, 
       FIRST_ACTIVE_LOG, 
       LAST_ACTIVE_LOG, 
       TOTAL_LOG_USED, 
       CAST(CASE ( TOTAL_LOG_USED + TOTAL_LOG_AVAILABLE ) 
             WHEN 0 THEN NULL 
                ELSE DEC(CAST(TOTAL_LOG_USED AS DOUBLE) / 
                     (TOTAL_LOG_USED + TOTAL_LOG_AVAILABLE ) +

Source:

CURRENT_SERVER

MON_GET_FCM_CONNECTION_LIST.MEMBER

MON_GET_FCM_CONNECTION_LIST.CONNECTION_STATUS

MON_GET_FCM_CONNECTION_LIST.TOTAL_BUFFERS_SENT

MON_GET_FCM_CONNECTION_LIST.TOTAL_BUFFERS_RCVD

MON_GET_MEMORY_POOL.MEMBER

MON_GET_MEMORY_POOL.MEMORY_POOL_USED

MON_GET_MEMORY_POOL.MEMORY_POOL_USED_HWM

MON_GET_TABLESPACE.MEMBER

MON_GET_TABLESPACE.TBSP_USED_PAGES

MON_GET_TABLESPACE.TBSP_TOTAL_PAGES

MON_GET_TABLESPACE.TBSP_PAGE_SIZE

MON_GET_TRANSACTION_LOG.MEMBER

MON_GET_TRANSACTION_LOG.CURRENT_ACTIVE_LOG

MON_GET_TRANSACTION_LOG.FIRST_ACTIVE_LOG

MON_GET_TRANSACTION_LOG.LAST_ACTIVE_LOG

MON_GET_TRANSACTION_LOG.TOTAL_LOG_USED

MON_GET_TRANSACTION_LOG.TOTAL_LOG_AVAILABLE

MON_GET_TRANSACTION_LOG.APPLID_HOLDING_OLDEST_XACT

Partition number

The member name of the connection.

SELECT CONN_TAB.MEMBER
FROM   CONN_TAB

Partition status

The connection status

SELECT CONN_TAB.CONNECTION_STATUS
FROM   CONN_TAB

Buffer lwm

The lowest number of free FCM buffers reached during processing. 

SELECT FCM.BUFF_FREE_BOTTOM
FROM   CONN_TAB 
LEFT OUTER JOIN TABLE(MON_GET_FCM (#MEMBER#)) AS FCM ON CONN_TAB.MEMBER = FCM.MEMBER

Source: MON_GET_FCM.BUFF_FREE_BOTTOM

Actual bufsent

The total FCM buffers sent.

SELECT CONN_TAB.TOTAL_BUFFERS_SENT
FROM   CONN_TAB

Actual bufrcvd

The total FCM buffers received

SELECT CONN_TAB.TOTAL_BUFFERS_RCVD
FROM   CONN_TAB

Pool currsize

The total amount of memory pool in use. 

SELECT MEM_TAB1.MEM_POOL_USED
FROM   CONN_TAB
LEFT OUTER JOIN MEM_TAB1 ON CONN_TAB.MEMBER = MEM_TAB1.MEMBER

Pool hwm

The sum of the highest amount of memory assigned to this pool since it was created.

SELECT MEM_TAB1.MEM_POOL_USED_HWM
FROM   CONN_TAB
LEFT OUTER JOIN MEM_TAB1 ON CONN_TAB.MEMBER = MEM_TAB1.MEMBER

Channels free

The number of FCM communication channels that are currently free.

SELECT FCM.CH_FREE
FROM   CONN_TAB 
LEFT OUTER JOIN TABLE(MON_GET_FCM (#MEMBER#)) AS FCM ON CONN_TAB.MEMBER = FCM.MEMBER

Source: MON_GET_FCM.CH_FREE

Space used

The total used space.

SELECT TSP_TAB.SPACE_USED
FROM   CONN_TAB 
LEFT OUTER JOIN TSP_TAB ON CONN_TAB.MEMBER = TSP_TAB.MEMBER

Total space

The total space.

SELECT TSP_TAB.TOTAL_SPACE
FROM   CONN_TAB 
LEFT OUTER JOIN TSP_TAB ON CONN_TAB.MEMBER = TSP_TAB.MEMBER

Log current

The current active Log File number.

SELECT LOG_TAB.CURRENT_ACTIVE_LOG
FROM   CONN_TAB 
LEFT OUTER JOIN LOG_TAB ON CONN_TAB.MEMBER = LOG_TAB.MEMBER

Log first

The file number of the first active log file.

SELECT LOG_TAB.FIRST_ACTIVE_LOG
FROM   CONN_TAB 
LEFT OUTER JOIN LOG_TAB ON CONN_TAB.MEMBER = LOG_TAB.MEMBER

Log last

The file number of the last active log file.

SELECT LOG_TAB.LAST_ACTIVE_LOG
FROM   CONN_TAB 
LEFT OUTER JOIN LOG_TAB ON CONN_TAB.MEMBER = LOG_TAB.MEMBER

Number of pools

The total number of memory pools.

SELECT MEM_TAB2.NUM_MEM_POOL
FROM   CONN_TAB 
LEFT OUTER JOIN MEM_TAB2 ON CONN_TAB.MEMBER = MEM_TAB2.MEMBER

Log space used

The total log space used.

SELECT LOG_TAB.TOTAL_LOG_USED
FROM   CONN_TAB 
LEFT OUTER JOIN LOG_TAB ON CONN_TAB.MEMBER = LOG_TAB.MEMBER

Percent of log used

The percentage of log space used.

SELECT LOG_TAB.LOG_USED_PCT
FROM   CONN_TAB 
LEFT OUTER JOIN LOG_TAB ON CONN_TAB.MEMBER = LOG_TAB.MEMBER

Agent ID holding 1st log

The application handle holding the oldest transaction.

SELECT LOG_TAB.APPLID_HOLDING_OLDEST_XACT 
FROM   CONN_TAB 
LEFT OUTER JOIN LOG_TAB ON CONN_TAB.MEMBER = LOG_TAB.MEMBER
Previous
Throughput: Service classes
Next
Throughput: Skew monitor