How to Monitor ---> Database in Db2 Warehouse console.
Welcome back! Folks
if you are here first time do checkout my last blog series -- on How to Monitor ---> Dashboard
Db2 Warehouse console - Create Reports
https://community.ibm.com/community/user/cloud/blogs/noel-dsouza/2024/02/26/db2-warehouse-console-create-reports
Db2 Warehouse console - Monitor[Responsiveness]
https://community.ibm.com/community/user/cloud/blogs/noel-dsouza/2024/02/28/db2-warehouse-console-monitorresponsiveness
Db2 Warehouse console - Monitor [Throughput]
https://community.ibm.com/community/user/cloud/blogs/noel-dsouza/2024/02/27/db2-warehouse-console-monitor-throughput
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
Db2 Warehouse console - Monitor [Contention]
https://community.ibm.com/community/user/cloud/blogs/noel-dsouza/2024/03/11/db2-warehouse-console-monitor-contention
Db2 Warehouse console - Monitor [Time spent]
https://community.ibm.com/community/user/cloud/blogs/noel-dsouza/2024/03/11/db2-warehouse-console-monitor-time-spent
Now let learn How to Monitor ---> Database 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
In this series, we will discuss the Database page .
This page has 3 Tab's on the screen
1)Database Time spent
2)Database Usage
3)Database Partition.
Today we are starting with the tab 1)Database Time spent
1)Database Time spent
The Database Time spent tab has 4 graphs let me put them one by one and try and explain each of these.
1)Time spent
2.a)Other waits 2.b)Other waits [Timestamped]
3)Workloads for 'Other waits'
First , lets see the available timeline that is available for us to pull the graph for, We have the following options .
|
a) Real Time
|
b) Last 1 Hour
|
c)Last 6 Hours
|
d) Last 12 Hours
|
e) Last 24 Hours
|
f)Last 3 days
|
g) Custom.
|
You can also see Pause Data refresh || and Refresh button's
If not using real time drop down option then you can use Refresh button to make sure you have recent data included in the option you selected.
The screen automatically refreshes every 60 sec ;
In the Right side of the screen you will see one more drop down this is for selecting the Workloads. There are various workloads created by the IBM cloud team and are in use. Normally you would use "All user workloads " or " Database " . If you are wondering what are workloads then I suggest you read up on the following articles. As it is a topic in its own self.
If you are interested in read more on this here are the articles on workload manager (WLM).
Db2 workload manager
This has sub topics for you to read
Workload management roadmap
This has demos , Podcast, Article and more.
A good workload management system helps to efficiently meet goals in the environment where work occurs. You can see examples of the need for a good workload management system all around you. The Db2 WLM is a feature that can help identify, manage, monitor, and control the database workload, to better reflect the organization and priorities of your business.
In a Nutshell I would says that it a way to allocate resources (CPU/RAM/disk etc) to the User/request/ SQL.
Now , coming back to our topic. As I mentioned earlier we have 4 graphs in this console screen under "1)Database Time spent" Tab.
Let's, looks at them one by one.
1)Time spent
In this I have selected "Last 24 Hours" and "All user workloads" under select workloads drop down.
This graph shows the following a) SQL execution b) I/O c) Lock waits d)Other Waits e)Other processing for a total of 100% and breakup of how much each of the 5 legends are using the system resource.
Lets see a more granular view of this graph when we select "Database" under select workloads drop down.
See the Difference ?
Time spent is Percent of system time spent processing database events.
"ALL USER WORKLOAD" is an aggregation of all individual user workloads. It comes from MON_GET_WORKLOAD.
"DATABASE" is an overall (DB level) view of all workloads. It comes from MON_GET_DATABASE.
"All WORKLOADS" is aggregation of all DB workloads.
"SYSDEFAULTADMWORLOAD" , "DSM_WORKLOAD", "DB2_MGMT_WL", "SYSDEFAULTUSERWORKLOAD", "CONSOLE_WORKLOAD" each of them is an individual DB workload.
Note: "DSM_WORKLOAD" and "CONSOLE_WORKLOAD" are console-specific workloads others are user workloads.
The 2.a ,2.b and 3 "workloads for ' ' " graph are based on 1)Time spent graph .
as we see the "Time spent" graph has 5 legends if you click on one of the graph say "other waits " it will change the below graph's
2.a)Other waits 2.b)Other waits [Timestamped] and 3)workloads for 'Other waits'
2.a)Other waits
What you see above is a default view of the "Other waits" graph. If you click on says "SQL execution"
the above 2.a and below 2.b graph will show "SQL execution" and "SQL execution [Timestamped]" graph
2.b)Other waits [Timestamped]
Example :
I see a spike on SQL execution at 24/03/10 10:14:54 AM.
I click on the DOT on that spike for the Light blue graph on "Time spent" graph and I see the below graph now shows SQL Execution
And "SQL Execution [24/03/10 10:14:54 AM]" graph for more details
Graph 3) Workloads for ' ' graph now show Workloads for 'SQL execution' since we clicked on SQL Execution in the "Time spent " graph.
So basically graph is the 2nd row and 3rd row are dependent on what you select in the first graph "Time spent"
This helps us to drill down further in the where the time was spent.
That’s it for today.
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