application_handle reuse? LUW v10.5FP8

Rui Chen

application_handle reuse? LUW v10.5FP8

Hi DB2-experts, 

Have a question on application_handle reuse. When we join from different monitoring table functions/admin views using application_handle, eg. from MON_GET_APPL_LOCKWAIT / MON_LOCKWAIT to MON_GET_CONNECTION, is there any guarantee that application_handle returned by both sides are actually tied to the same TX? Is it possible that the application_handle is reused, and MON_LOCKWAIT and MON_GET_CONNECTION return metrics from different TXs sharing the same application_handle?

A little bit background, we use jdbc connectionPool and pass application metadata, eg. client_userid, which is returned by MON_GET_CONNECTION; we also monitor lockwait regularly. We recently encountered a puzzle where  MON_LOCKWAIT says one TX spent over 100sec on lockwait, but the client_userid points us to a TX that completed super fast. Of course it's possible that we have a bug passing wrong value to client_userid, but just want to make sure DB2 doesn't introduce any extra confusion. 

Thanks for your input. 

Edited By:
Rui Chen[Organization Members] @ Feb 27, 2018 - 02:54 PM (America/Eastern)

Mark Barinstein

RE: application_handle reuse? LUW v10.5FP8
(in response to Rui Chen)

Hi,

There is no such a guaranty especially in case of very quick transactions.
The preferred way to monitor lock waits / timeouts is to create an event monitor for locking and configure your database to catch the desired events and amount of information to it.

Edited By:
Mark Barinstein[Organization Members] @ Feb 28, 2018 - 12:28 PM (Europe/Moscow)

Rui Chen

RE: application_handle reuse? LUW v10.5FP8
(in response to Mark Barinstein)

Awesome, thanks for your input Mark! Lucky for us the lock contention isn't too bad, so we could get away without lock event monitor.  

I also realize i still lack a good understanding of

  1. how monitor tables/views grabs the application_handle/agent_id;
  2. how application_handle/agent_id allocation happens? is the agent id-ed here purely coordinator agent (ref. link)? Do other type of agents share the same numbering pool? (probably irrelevant, just curious....) 
    From a quick test we did, it seems as long as the connection exists, the application_handle is not changed, even across multiple commit/rollback, which seems suggesting if we use connectionPooling and all connection remains healthy (no disconnection), than application_handle shouldn't change at all. So this could explain what we initially saw, since we set client_userid field differently for every TX, but application_handle remain the same. btw, we tested type 4 jdbc connection without pooling.
  3. would connection concentrator make things any different? 
  4. what's different if we have a partitioned DB? We don't use partitioned DB, so just curious.....

From the documentation, application_handle/agent_id consists of 16 bit (make sense, since the number of ports max out at 2^16). 

 

Mark Barinstein

RE: application_handle reuse? LUW v10.5FP8
(in response to Rui Chen)

There was some misunderstanding probably. 

I got the question like “if mon_lockwaits join mon_get_connetion on application_handle may return the information on different transactions (your ‘TX’ term) of the same application“. It may be possible in case of very quick transactions probably. 

But such a query can’t return the information on different applications.

Rui Chen

RE: application_handle reuse? LUW v10.5FP8
(in response to Mark Barinstein)

Thanks Mark, i think you caught my point 100% percent :)