As part of the DB2 Support team at Triton I’ve been working with DBI tools for the last 6 months when we began using them to help manage and support our DB2 Remote DBA clients. Things have been going well.
So what does a Remote DBA service use these tools for? Well, in order to proactively manage our customers’ DB2 systems we use tools, as an in-house DBA would, to continually monitor, analyse and report on findings. This is good for our customers because it means that they don’t have to purchase their own monitoring tools as these come as part of the DB2 support service. Over the years Triton has tried a couple of different monitoring tools and we’ve decided to work with DBI as we’ve found them to be the best in terms of the range of products available to the DBA, the functionality that each product provides and the level of support provided by the DBI team.
A typical day for Remote DBA
Well there’s no such thing really. Our customers’ support requirements range from 24/7 to office hours, to out of ours only so the team is always on call one way or another! Each customer is different and will have quiet periods where things tick along nicely and busier periods they have Triton on speed dial! Not that it makes any difference to their contract because Triton customers have no limit on the number of calls they can make.
The phone vibrates, only two vibrations so it must be an email coming through. I look down to see that the email concerns a client of ours and is referencing an alert that has been breached for one of the production databases. It suggests that the database score has dropped below the threshold value of ‘5000’, reaching levels that would be of concern as the performance of the production database will be poor. This alert system is down to Brother Hawk, a product, which is configured on initial install with 140+ alerts at the DBA’s disposal and allows the DBA to create his/her own alerts.
I open up Brother Panther to look into why the database score has dropped so much, Brother Panther being the main performance tool. It shows every database that has been configured and provides numerous performance metrics that measure the workloads running against each and every database. It also allows the DBA to check on bufferpool, tablespace, table and statement performance and providing the functionality to run explains and the design advisor. I right click on the database in question and scroll down to analyse database score. The report shows me that the current workload running against the database has caused the score to drop so much, specifically the lack of indexes or sub-optimal index created for the database. This requires further analysis.
I navigate out of this window and right-click and open up a statement performance window, ah ha! A sea of red, the index read efficiency column is highlighting some poor values for a series of SQL. Anything above the threshold of 100 is highlighted in red and surely requires analysing. I look to run explain against the first SQL I encounter, table scans, as expected. I click the design advisor button and away goes Brother Panther, a series of recommended indexes are retunred along with runstats that may be required. By clicking on the ‘pdf’ button I am able to create a report for the explain and recommended indexes and send it off to the customer. So that’s what I do and then moving back to the main page for all SQL I place a flag next to the analysed SQL and make an entry, something along the lines of ‘SQL analysed, recommendations made and 30% improvement’. This will be beneficial to anyone running Brother Panther later and not wasting time analysing the same SQL as it has been flagged. This is now a recurring process for all SQL believed to be performing badly.
I need a clearer picture here, I need to know what percentage of this workload is CPU intensive. I load up Brother Thoroughbred, this provides a pie-chart of the current workload and reports on I/O, Sorts, CPU etc. by clicking into the pie-chart, I am able to see the SQL that is causing the excessive CPU usage. A statement performance screen appears with all SQL currently causing the increased CPU usage. I can continue with analysing this SQL in the same way I did previously.
The phone vibrates once more, another threshold breached. This time I am not happy with the alert as it should not be alerting me. The threshold has been set too low for this configuration parameter. I open up the administration console and navigate to Brother Hawk alerts, scroll down to the alert in question and edit it so that the threshold is increased and will only alert me when there is a real issue.
A typical day working as a remote DBA, and made a lot easier working with the DBI performance tools.