DB2 for z/OS : Need some info on DBAT values. Whether my MAXDBAT value needs to be modified

Eswar Prasad Chandrasekaran

DB2 for z/OS : Need some info on DBAT values. Whether my MAXDBAT value needs to be modified

Hi ,.

These are values of DBATs and connections i have come across which are different from regular values especially the QUEDBAT, INADBAT, DSCDBTA, ADBAT. Seen all these are 0 on most of the occasions.

Based on my understanding, if QUEDBAT is non-zero, then at somepoint in time after a restart of DB2, the maxdbat limit was reached and needs to be tweeked. Am i correct ? 

Currently no one has reported any problems but i am curious to know how to determine these values and when can they be changed. I believe it involves CPU, memory angles as well right.

I also did a display thread type(inactive) and can see threads present. Do these pose any threat, are they not getting connected to DB2 or does it mean pooling is happening ?

The CMTSTAT value is inactive 

          

CONDBAT=   1000 MDBAT=  200                          

ADBAT=    3 QUEDBAT=   4942 INADBAT=      0 CONQUED=      0

DSCDBAT=      2 INACONN=    141                            

PKGREL = COMMIT                             

 

Regards

Eswar                                                                           

Edited By:
Eswar Prasad Chandrasekaran[Organization Members] @ Apr 25, 2019 - 09:41 AM (America/Eastern)

Brian Laube

DB2 for z/OS : Need some info on DBAT values. Whether my MAXDBAT value needs to be modified
(in response to Eswar Prasad Chandrasekaran)
Hi Eswar,

In my shop, we saw QUEDBAT go greater than zero when CONDBAT was reached.

We raised CONDBAT to 10000 and the problem went away. I think raising CONDBAT is pretty harmless. These are concurrent remote threads. What is more important is MAXDBAT which is the max DBATS that are concurrently active and really doing something inside the DB2. I would say that if you reach MAXDBAT then your DB2 is pretty busy!

The root reason was one newish application was opening many hundreds of active threads. But the application did not commit in many cases. And when it needed to do something else then it opened another connection/thread. So we had hundreds of concurrent remote threads that DB2 was waiting for the remote client to do something (like commit). The threads were active but not doing anything inside of DB2. After IDTHTOIN seconds (in our case 300 seconds) then DB2 would eventually kill these active threads that were doing nothing. (the application was basically read only so it was not holding many locks)

So the application went through bursts of opening many distinct threads. They were eventually killed off by DB2. But when CONDBAT was reached… we had QUEDBAT increase.


Raising CONDBAT was a quick short term solution because the cost of those remote connected threads was low. They were active but not doing anything
And according to the IBM documentation of today (the knowledge center)… the CONDBAT (for a new DB2) would have default of 10000 (unless you specified another value)
So 10000 seemed like a good idea
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/inst/src/tpc/db2z_ipf_condbat.html


Later we finally figured out why the application was starting all these active threads… and we changed it to commit regularly and the CONDBAT was never reached again.

The interesting takeaway is finding out when CONDBAT is reached or close to being reached.
The system statistics has some fields which can help you track the history of DBATS … I did this once to look for trends..

We now have our monitoring tool (Tivoli Omegamon) watching our DB2 using some standard monitoring situation for when “DBAT connection utilization” reaches 80% (of condbat) to send an information alert
And we have another information alert when “DBAT utilization” reaches 80% (of MAXDBAT)

I suppose one could run -DISPLAY DDF DETAIL in batch and parse the output to calculate your own alert… all the info is there… I considered that manual solution to monitoring until I realized our monitoring tool had a pre-existing alert that just needed to be enabled.

Regards,
Brian


________________________________
Brian Laube
Database Administrator – DB2 for z/OS
Manulife
[ibm-champion-rgb-72px]
IBM Certified Database Administrator - DB2 11 for z/OS
IBM Certified Application Developer - DB2 11 for z/OS


Upcoming vacation: <April 26, May 3, June 21>


E [login to unmask email]<mailto:[login to unmask email]>
T 519 593-8817 or x238817
M 519 575 2786
Service Requests for the CDN DBA team must be registered as TASKS in the BTS Service Request Intake tool: Notes://MLILAPP04/85257AAD005BAA60/<notes://MLILAPP04/85257AAD005BAA60/>

Db2 v12 for z/OS – documentation and Knowledge Centre: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/home/src/tpc/db2z_12_prodhome.html

From: Eswar Prasad Chandrasekaran <[login to unmask email]>
Sent: Thursday, April 25, 2019 9:34 AM
To: [login to unmask email]
Subject: [DB2-L] - DB2 for z/OS : Need some info on DBAT values. Whether my MAXDBAT value needs to be modified


Hi ,.

These are values of DBATs and connections in the test region of my shop. Based on my understand, if QUEDBAT is non-zero, then at somepoint in time after a restart of DB2, the maxdbat limit was reached and needs to be tweeked. Am i correct ?

Currently no one has reported any problems but i am curious to know how to determine these values and when can they be changed. I believe it involves CPU, memory angles as well right.

I also did a display thread type(inactive) and can see threads present. Do these pose any threat, are they not getting connected to DB2 or does it mean pooling is happening ?

The CMTSTAT value is inactive



DSNL090I DT=I CONDBAT= 1000 MDBAT= 200

DSNL092I ADBAT= 3 QUEDBAT= 4942 INADBAT= 0 CONQUED= 0

DSNL093I DSCDBAT= 2 INACONN= 141

DSNL105I CURRENT DDF OPTIONS ARE:

DSNL106I PKGREL = COMMIT



Regards

Eswar

-----End Original Message-----

STATEMENT OF CONFIDENTIALITY The information contained in this email message and any attachments may be confidential and legally privileged and is intended for the use of the addressee(s) only. If you are not an intended recipient, please: (1) notify me immediately by replying to this message; (2) do not use, disseminate, distribute or reproduce any part of the message or any attachment; and (3) destroy all copies of this message and any attachments.
Attachments

  • image001.png (<1k)

Eswar Prasad Chandrasekaran

RE: DB2 for z/OS : Need some info on DBAT values. Whether my MAXDBAT value needs to be modified
(in response to Brian Laube)

Thank you Brian for your detailed explanation. 

I will check if the CONDBAT can be increased an if that satisfies the situation.

But we did receive the message untill some days back at random intervals

DSNL030I  + DSNLILNR DDF PROCESSING FAILURE FOR  417 

REASON=00D31034                                    

Doesn't this mean that the MAXDBAT has reached.  It no longer comes, but is there something that is needed to be done from our end ?

We dont have omegamon, only CA insight or DB2. is there an option which gives us a similar feature ?

Regards

Eswar

Javier Estrada Benavides

RE: DB2 for z/OS : Need some info on DBAT values. Whether my MAXDBAT value needs to be modified
(in response to Eswar Prasad Chandrasekaran)

Hey

   About CONDBAT, I would also check what's the status of your current connections and the values for the connection pools inside app servers. I've seen that most people tend to keep a high value for their minimum number of remote JDBC/ODBC connections, therefore you can easily get hundreds of threads doing nothing at all which is never friendly, especially when you get very high activity and the threads listings are way too big rightwhen you want to narrow what you want to see.

     And about Insight (Sysview for Db2), it has many predefined exceptions ready to be used for your environment and you can also set up your own, we can talk individually about this  

 

Have a great day :)

Javier Estrada Benavides, Czech Republic / Mexico

IBM Champion for Analytics

IBM Certified System Administrator - Db2 12 for z/OS

IBM Db2 12 DBA for z/OS - 2018 (the ugly brown badge from IBM Open Badge Program)

IBM Certified System Administrator - DB2 11 for z/OS

IBM Certified Database Administrator - DB2 11 DBA for z/OS