What performance information does the pdm_vdbinfo command give me, and how can I interpret the output?

Document ID : KB000024865
Last Modified Date : 14/02/2018
Show Technical Document Details

QUESTION:

What is the status of database agents and what they are currently doing?

 

ANSWER:

The pdm_vdbinfo command provides a snapshot report of the database agents (managed by the bpvirtdb_srvr process) which are running and what they are currently doing.

It's normally requested by Support for troubleshooting performance issues.
Below a brief explanation on what the command returns.

The output from pdm_vdbinfo can be split into several sections. This document will go through each of these sections in turn.

The top section of the pdm_vdbinfo output contains general information about the database agents which are running:

========================================
VDBINFO invoked at 06/17/2008 15:41:05
========================================
Min Config Agents    = 32
Max Config Agents    = 45
Max DB Agents        = 45
Tgt num idle         = 2
Num Agents running   = 42
Num Agents starting  = 11
Num Requests pending = 351
Actual num idle      = 0 

Some of the important values in here are:

"Max Config Agents" - This is the highest number of agents which can be running. This is defined in the NX.ENV file, NX_MAX_DBAGENT.
"Num Requests pending" - This is the number of requests which are waiting for the database agents to process them.
"Actual num idle" - This is the number of SELECT agents which are idle. ServiceDesk always wants to see idle agents. In the above example, this is 0, which means that the system is heavily loaded, and all of the SELECT agents are being used. This could indicate that more SELECT agents should be configured by increasing the NX_MAX_DBAGENT parameter in the NX.ENV file.

The next section of the output contains details of what each of the database agents are doing when the pdm_vdbinfo command was executed:

Agent #0 - (UPD) prov#29892_bpvirtdb_srvr @06/17/2008 15:40:53 with 0 in work
   Agent #1 - (UPD) prov#29896_bpvirtdb_srvr @06/17/2008 15:39:47 with 6 in work
 INSERT (db_id=240) INSERT INTO call_req ( active_flag , affected_rc , assi...
 PROC(domsrvr:WEB:2) CNT(272518150)
 <1> <> <793593747> <pcat:807049812> <> <> <> <> <815417450> <User is hav...
 UPDATE (db_id=111215) UPDATE call_req SET active_flag = ?, close_date = ?,...
 PROC(domsrvr:WEB:1) CNT(272501711)
 <active_flag> <0> <close_date> <06/17/2008 15:39:30> <last_mod_dt> <06/1...
 ...
 Agent #24 - (SEL) prov#7086_bpvirtdb_srvr @06/17/2008 15:40:42 with 2 in work
    SELECT_FULL (db_id=2861210) SELECT call_req.open_date, call_req.id FROM ca...
      FIRST (0 of 100 recs) PROC(domsrvr:WEB:4) CNT(272501207)
    SELECT_FULL (db_id=2861120) SELECT chg.open_date, chg.chg_ref_num, chg.id ...
      SUSPENDED (100 of 100 recs) PROC(domsrvr:WEB:4) CNT(272501227)
 Agent #25 - (SEL) prov#25926_bpvirtdb_srvr @06/17/2008 15:38:03 with 1 in work
    SELECT_FULL (db_id=2855903) SELECT call_req.ref_num, cn02.c_last_name, cn0...
      FIRST (0 of 200 recs) PROC(domsrvr:WEB:2) CNT(806656827)
 ...

For each of the agents, this lists the agent number, the type of agent (UPD is an update agent, SEL is a select agent), the process name (which you can find in the slstat output), details of how many statements the agent currently has assigned to it.
Under this is the list of queries which each agent assigned to the agent above.

The next section shows the current queued requests:

QUEUED requests (351)
  SELECT_FULL (db_id=93925) SELECT options.app_name, options.sym, options.id...
   WAITING (0 of 0 recs) PROC(domsrvr:WEB:4) CNT()
 SELECT_SHRT (db_id=247) SELECT chgalg.action_desc , chgalg.analyst , chgal...
   ID(806905163) PROC(domsrvr:WEB:3) CNT(272510369)
 SELECT_FULL (db_id=93925) SELECT options.app_name, options.sym, options.id...
   WAITING (0 of 0 recs) PROC(domsrvr:WEB:2) CNT()
 SELECT_FULL (db_id=2878) SELECT lrel.l_persid, lrel.l_attr, lrel.l_sql, lr...
   WAITING (0 of 0 recs) PROC(domsrvr:WEB:5) CNT(272512156)
 <cr:815421263> <notify_list>
  

The number of queued requests should match the number in the "Num Requests pending" line in the header section.

The next section lists the updates to the database which have been made since the bpvirtdb_nxd process started.

Table Updates:
Event_Delay       ID(00) Inserts(00937) Updates(01376) Deletes(00000) Total(02313)
Workflow_Task     ID(00) Inserts(00183) Updates(01603) Deletes(00006) Total(01792)
Change_Category   ID(00) Inserts(00000) Updates(00006) Deletes(00000) Total(00006)
Act_Log           ID(02) Inserts(29245) Updates(00021) Deletes(00000) Total(29266)
Change_Request    ID(03) Inserts(02673) Updates(17163) Deletes(00000) Total(19836)
Contact           ID(00) Inserts(00090) Updates(01641) Deletes(00000) Total(01731)
Prob_Category     ID(00) Inserts(00000) Updates(00006) Deletes(00000) Total(00006)
Change_Act_Log    ID(04) Inserts(31955) Updates(00313) Deletes(00000) Total(32268)
Audit_Log         ID(05) Inserts(61687) Updates(00000) Deletes(00000) Total(61687)
Call_Req          ID(01) Inserts(05795) Updates(16650) Deletes(00000) Total(22445)
Lrel_Table        ID(00) Inserts(00084) Updates(00000) Deletes(00000) Total(00084)
Animator          ID(00) Inserts(04678) Updates(00000) Deletes(04705) Total(09383)
Property          ID(00) Inserts(00594) Updates(00000) Deletes(00018) Total(00612)
Notify_Log_Header ID(00) Inserts(01028) Updates(00000) Deletes(00000) Total(01028)
Attached_Events   ID(00) Inserts(04235) Updates(03152) Deletes(02671) Total(10058)

The "ID(nn)" columns shows the number of the database agent which performed the update. ID(00) represents the agent which isn't associated with any table, while ID(nn) upwards denotes a database agent which is associated with a particular table (see NX.env - NX_VIRTDB_AGENTnr parameter)

The next section shows the details of the "Select Short" cache.
To avoid having the same query sent to the DBMS, the bpvirtdb_srvr caches internally, meaning that when the request for the query arrives, the data is instantly returned from the cache.

Delayed ID Queue 
Severity         Hash(0005) Queue(0005) MaxQueue(0101) Min|Max|Cur( 0.000    | 0.000    | 21297.191) 
Events           Hash(0101) Queue(0101) MaxQueue(0101) Min|Max|Cur( 15982.384| 29430.765| 26769.977) 
Act_Log          Hash(0101) Queue(0101) MaxQueue(0101) Min|Max|Cur( 17.503   | 7307.828 | 187.802  ) 
Call_Req         Hash(0501) Queue(0500) MaxQueue(0500) Min|Max|Cur( 21.745   | 10300.981| 433.525  ) 
Change_Act_Log   Hash(0111) Queue(0101) MaxQueue(0101) Min|Max|Cur( 2.892    | 5191.435 | 144.302  ) 
Controlled_Table Hash(0013) Queue(0013) MaxQueue(0101) Min|Max|Cur( 0.000    | 0.000    | 52314.179) 

"Queue(nnnn)" represents the current size of the queue, MaxQueue(nnnn) represents the maximum size the queue can be and the "Min|Max|Cur" values represent the times that the queue has taken to wrap.