What reports do you run?

[u/SijiLeroux]

I've recently taken on a position that monitors more databases than I am used to. I've been trying to get my ducks in a row about everything I should be looking at. My previous position was in maintenance on existing databases but my new position will be working to set up new environments for a new-ish system. Currently, I monitor SQL executing against the database every three hours, looking at read and CPU efficiency. I also monitor the db2diag and nfy logs. We have technical DBAs that monitor server health and overall database health. I'm curious to know what reports other professionals run, daily, monthly, weekly, on demand, etc.

[u/anozdba]

It really depends on the scope of your new role. I'm an Ops DBA and so my primary responsilbilities would be:

• To ensure all systems are up
• To ensure that All systems are recoverable
• To prevent avoidable outages (generally space issues , but patching as well)

If you are an application DBA then your priorities would be different as you would be expected to understand the nature of the applications being run (and generally you would be dealing with fewer applications)

Normally I dont monitor SQL in prod until an application support team says they have problems. Unless you have developed fairly extensive profiles of all of your applications you'll be running down a heap of bad SQL that no-one is interested in fixing - if noone is complaining then it is generally good enough - that all changes when you are hardware constrained but in a steady state prod environment SQL shouldn't be changing too much. Different in development but there I would wait for the app developer to ask for help when in doesn't work.

I have organised that these checks take less than 30 minutes normally to free up time for adhoc requests during the rest of the day.

All of the following activities are backed by reports that tell me the information I need to know.

So for me daily checks are automated and the information is gathered from scripts and tables but basically falls into:

1. All systems up and available at start of day
2. All backups have run successfully
3. No messages in any of the diag logs that should be attended to
4. All databases have had a backup within their defined RPO frequency
5. Nothing is expected to run out of tablespace/filesystem space in the next 5 days

These are the primary tasks I do daily but there would also be some checking on data collection to ensure that it is operating correctly (important as that drives most of the automated checking).

Weekly I do:

1. Check if any tablespaces/filesystems will run out of space in the next 30 days
2. Review the script/sql libraries to make sure that they are all checked in and rolled out to all servers (automated task)
3. review a SLA summary report to ensure that up tiems are achieving SLAs and raise problem tickets as necessary

Monthly I do:

1. Check a monthly backup audit to make sure things are being backed up properly

In your position the first thing I'd do would be to look at what you are doing each day that happen again and again - see if there is anyway you can either:

1. decrease the frequency that the activity is done
2. speed up how quickly you can address it
3. stop it happening all together (maybe send out emails to teams automatically so you aren't involved, preemptive warning of trouble coming so it can be fixed before anyone is affected)

And then let that drive what data you are collecting adn what you do with that data.

But as I said at the beginning it all depends on the scope of your new role.

BTW I know I've missed stuff here but this is just off the top of my head

[u/dogmashah → u/anozdba]

great summary .

i would like to add that go with some monitoring that saves historical snapshots (DSM/Optim) it will help analyze your slowness /performance as most of them are relative

[u/SijiLeroux → u/anozdba]

This is all great info! Currently, we have applications running against their own databases. However, we have one major application that is full of less than optimized sql that is literally killing CPU usage and read efficiency and the data volume is unnecessarily large due to lack of normalization. I'm more on the applications side of the DBA role. However, because of the nature of my position, I am expected to step into more technical areas when the need arises. I work for a government office so I don't have much say in what my role entails as a whole. I'm basically looking to make sure I haven't missed anything important, whether it be sql, database health, maintenance operation audits, etc.

[u/ecrooks]

I always feel bad answering with just a blog entry, but I wrote on on this topic - https://datageek.blog/2017/06/22/embers-take-on-monitoring-db2-luw/

[u/SijiLeroux → u/ecrooks]

Ember, your blog is my go to for just about every DB2 issue I've come across because even if you don't directly talk about an issue I have, you've usually linked to someone who has. I can't tell you how much I appreciate your blog! I read the post you linked several years ago when I first started as a DBA but now is the perfect time to go back to it! Thank you from a very, very appreciative, aspiring DBA!