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!
