Tuesday, October 18, 2016

DB2 Web Query Monitoring and Audit Reports

If you’re still using Query/400 heavily in your shop, you probably have come across times where it would have been really nice to know information about those reports, such as:
  • How many queries are actually on my system, and how has that number changed over time?
  • Which queries will be impacted by changes to the underlying tables/files?
  • How old are some of these queries – and is anyone using them anymore?
  • Who is running all these queries? And should they be?
  • Which ones are running the longest time?
  • How many queries are redundant in nature, mostly doing the same exact query?
  • What is the impact to my system of all these Query/400 reports?

Answers to these questions continue to be difficult to obtain. Regularly monitoring and taking action to consolidate or clean up queries is simply not done unless it becomes problematic. One client our IBM DB2 Lab Services team worked with recently was shocked to find out that they had, unbeknownst to them, close to 40,000 Query/400 definitions on their system! Who knew?

It doesn’t have to be that way. One of the more subtle benefits of modernizing Query/400 into DB2 Web Query is the ability to get more control over the reporting environment. There are too many aspects to this to cover in one blog entry, but in the future I’ll explore more of these - such as moving the work to DB2 for i, or creating a controlled ad-hoc reporting strategy. But for now….

Want to know what reports will be affected by a change in an underlying table? DB2 Web Query includes an “impact analysis” function that builds a list of reports where an individual field is used. Or find the reports where an entire master table or “synonym” (a meta data representation of a table) is referenced. A “refresh synonym” function can automatically pick up and apply changes that have been made to a table without having to re-do all your reports (or run to see if they break)! You can read more about these features in Volume 8 of the DB2 Web Query Product Manual

New with Version 2.2 of DB2 Web Query are also a set of reports to monitor and/or audit report usage. If you are not on Version 2.2 yet, I highly recommend you get there. All of those things you wish you had in Query/400 are built into reports and dashboards shipped with Version 2.2 (Note: you need to have Group PTF Level 1 or higher applied, and there is a step to restore the reports from the change management package they are shipped in).

These reports and dashboards answer the following kinds of questions: 

  • How many reports are in each folder? Which reports are being run most often?

  • Which reports are taking the longest time to run? When was the last time it ran? 
  • Who are the most active report runners and what reports are they running? 

  • When was a report changed and who was making those modifications?

    These reports should give you plenty of insight into what’s going on with your Web Query environment, and they are ready to run by just importing the change management package. You need to be a DB2 Web Query Administrator to install them. Sign on to web query and scroll down to the bottom of the resource tree and you’ll see the “Change Management” functions. Expand and select Import and you should see a package called “IBM DB2 Web Query Information.” Right click and take the defaults to import the reports and synonyms. 

    Make sure you refresh your browser to see the new Top Level folder by the same name – and give them a try. 
    For more info on the audit reports, see the New Features guide. Many of the reports have a “look-back” window of 30 days. If you’re interested in keeping more history data and tracking over a broader time period, you could create a mini data mart or history tables with a little tweaking of these reports - and then automate the collection process. If you’re interested in getting some guidance or extending the use of these reports, feel free to reach out to the team at QU2@us.ibm.com!