How to Monitor ---> Dashboard in Db2 Warehouse console.
Welcome back! Folks
if you are here first time do checkout my last blog on Contention
Db2 Warehouse console - Monitor [Contention]
https://community.ibm.com/community/user/cloud/blogs/noel-dsouza/2024/03/11/db2-warehouse-console-monitor-contention
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 5)Time spent
5)Time spent
As you can see below is the Widget that shows Time spent .
You can only see one tab but has graph for
a) SQL execution
b) I/O
c) Lock waits
d)Other Waits
e)Other processing
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 .
It would be confusing if you are looking at the graph with all 5 legends together. To see them individually you can unselect other and keep 1 at a time that you want to check .. For example in the below screen I have unselected others and only kept "SQL execution" as a result the graph looks like this.
This Tells me that mostly my SQL Executions for Last 24 hours were less than 10% . Now let me check more for Last 1 hour .
here you can see that at one point SQL Execution was at 17%. . Of that it shows further breakdown of Section processing at 16.97% and Routine user code at 0.01%.
Next , I will move to Next legend I/O.
see below we only have 9% on one occasion on 22 Feb around 7:45 pm to 8:00pm.
we can also see below there is a breakup of I/O shown.
That is 1) Pool read 8.8%. 2) Pool write 0.19% 3) Direct Read 0.04%.
Here , Pool read and write are memory pools and Direct Read are Physical Read from the disk.
depending on the system and Query and various other factor you could see more Direct reads or less.
Example : on OLTP system you should have goal for having this at the least . However for OLAP this could be higher as OLAP reads large quantity of data.
Having said that Direct read could also mean Poor indexing or inadequate RAM or memory configuration to the db instance or bad SQL plan in general and so on.
You can continue this method to check out graph for other legends like Lock waits , other waits and Other processing.
Each one shows more details and breakdown in that category.
Just for one more example : lets see Other Waits since this was showing as taking most of the time.
Here , you can see Other Waits at one point it reached 91.5 % on 22 Feb 06:45 pm to 7:00 pm . And it shows that of that 91.5 % the system was waiting on Extended Latch for 49.57% and next was Log disk 31.02% .
Hope you enjoyed learning this as much as I did .
That’s it for today.
Here I will end my series on Monitor --> Dashboard Tab .
Thank you all , I hope this was helpful . Please feel free to let me know in the comments.
Be sure to like and subscribe to know more about upcoming blogs or series.
By — Noel Dsouza