How to Monitor ---> Dashboard in Db2 Warehouse console.
Welcome back! Folks
if you are here first time do checkout my last blog on Resource Usage-- >
Db2 Warehouse console - Monitor [Resource Usage]
https://community.ibm.com/community/user/cloud/blogs/noel-dsouza/2024/02/28/db2-warehouse-console-monitor-resource-usage
Now let learn How to Monitor ---> Dashboard in Db2 Warehouse console.
Let's login to cloud IBM com
Login to the https://cloud.ibm.com/
Then locate your Db2 Warehouse instance / service now open the console page from manage page . Click [Open Console] Button.
Now , on the left pane click on the Monitor page. This will give access to multiple tabs i.e Dashboard , Database , Statement and so on…
Today , we will discuss the Dashboard page .
This page has 5 Widgets on the screen
1)Responsiveness
2)Throughput
3)Resource usage
4)Contention
5)Time spent
Now, coming back to the dashboard let's discuss 4)Contention
4)Contention
As you can see below is the Widget that shows Contention .
You may miss this if not looking carefully there are 2 tabs in this Widget. Did you see ? Yes the first is Lock Waits and the second is Concurrent Connections.
Also you can see that the report duration drop down is from Last 1 hour up to Last month. In this case we have selected Last 24 hours .
A lock wait occurs when one transaction (composed of one or more SQL statements) tries to acquire a lock whose mode conflicts with a lock held by another transaction. Excessive lock wait time often translates into poor response time, so it is important to monitor.
Common symptoms of lock waits taking longer than normal are:
- Applications are not completing tasks
- SQL query performance slowdown
- Lock escalations. A small amount is OK, but excessive counts are an issue
The ideal is to monitor continuously : Lock wait, Lock timeout and deadlock locking
In our case we do not have any lock waits so no action needed. However if you see too much here or want to know what to do if you have lock wait problem
here are a few article I would suggest you check to solve this
Diagnosing a lock wait problem
https://www.ibm.com/docs/en/db2/11.1?topic=problems-diagnosing-lock-wait-problem
Resolving lock wait problems
https://www.ibm.com/docs/en/db2/11.1?topic=problem-resolving-lock-wait-problems
Lock waits and timeouts
https://www.ibm.com/docs/en/db2/10.5?topic=management-lock-waits-timeouts
Now moving on to the next tab Concurrent Connections. We can see from below screen that we had a MAX of 148 connection but then dropped to around 108.
We need to just use this to make sure we are not always on the MAX limit of the database. If so we may need to think of increasing this value .
However , just increasing this value is not the solution the system should be able to handle this. The more connection means more load as well.
More load would demand more power or we have will get timeout / performance issue and general slowness etc.
So now , Question: How can I find out the maximum amount of connections allowed on a DB2 database?
Answer: To check at a database level check MAXAPPLS . This parameter specifies the maximum number of concurrent applications that can be connected to a database.
Command
db2 get db cfg for DB_NAME | grep 'MAXAPPLS'
This returns
Max number of active applications (MAXAPPLS) = AUTOMATIC(150)
see below article for more info
In a nutshell
{
maxappls - Maximum number of active applications configuration parameter. This parameter specifies the maximum number of concurrent applications that can be connected (both local and remote) to a database.
}
maxappls - Maximum number of active applications configuration parameter
https://www.ibm.com/docs/en/db2/11.5?topic=parameters-maxappls-maximum-number-active-applications
As I said just increasing the number of connection is not always the solution. For example if you have a vehicle that can take a load of 3 persons and can maintain a speed of 50 KM /HR then if you add 3 more to make it 6 person on this vehicle you will compromise on speed and or fuel efficiency.
Hope this was helpful.
That’s it for today.
In my Next blog I will explain on 4) Contention. Be sure to like and subscribe to know more about this .
Thank you all , I hope this was helpful . Please feel free to let me know if the comments.
By — Noel Dsouza