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!  

    Monday, September 19, 2016

    A New DB2 Web Query Redbook Coming!

    No matter where I have lived or where I am at the time, Fall has always been my favorite season of the year. The days are shorter but beautiful as the temperatures have cooled down from the dog days of summer, and the leaves on the trees turn amazing colors of orange, red and brown! That’s happening right now for those of us north of the equator!

    Fall also signals a transition back into work mode. The kids are back in school, vacation pictures put away, and we have a renewed energy that says it’s time to get to work.

    And that’s what the DB2 Web Query team is doing this September! I’m excited to announce that we are working hard on an update to the very popular IBM DB2 Web Query Redbook. You may know that IBM Redbooks are traditionally point in time publications with a lot of “how to” information. But that also means they are not necessarily updated. In the case of the DB2 Web Query Redbook, we’ve performed an update once already – in 2012. We are now at it again! The timing is perfect, as so much has been enhanced in the past few years.

    We’ve gathered our strong team of DB2 Web Query experts together in Rochester, Minnesota for 3 weeks of heads down work to update, create, and organize content; content that represents the latest product features, functions, and benefits. Our esteemed group of DB2 Web Query expert contributors: 

    Simona Pacchiarini in IBM Systems Lab Services flew all the way in from Milan, Italy

    Jim Bainbridge and Rob Bestgen are also part of IBM Systems Lab Services based in Rochester, Minnesota. Rob is also the product development manager for DB2 Web Query.

    Kevin Trisko, from the IBM Support team, works with DB2 Web Query customers day in and day out.

    Kathy Steinbrink is our lead developer for DB2 Web Query and we’re happy to steal her for this project.

    Mike Cain is providing guidance and contribution across the board, and we’d be lost without our project manager, Hernando Bedoya – Mr. Redbook!

    I can’t leave out our team from IBM China. Zhi Yu Zhe, Su Lin Lin, and Wang Yu Yu are working diligently on refreshing the fourteen chapters of tutorials from their home base in Beijing. 

    In the coming weeks I’ll be posting updates and more information about some of the deliverables and new functions such as: 

    • New auditing reports
    • New sample reports for IBM i work management
    • Delivery of Query/400 discovery and analysis tools and reports
    • The IBM PowerSC security compliance and monitoring solution
    • Leveraging DB2 for i 7.3 OLAP and Statistical capabilities

     And much more!

    So stay tuned and reach out to me at mackd AT us.ibm.com or on twitter @mckdrmoly if you have questions or need assistance!

    Friday, June 10, 2016

    Welcome to My DB2 Web Query for i Blog!

    The intent of the blog is to share illuminating information and guidance on aspects of the DB2 Web Query product set and use of DB2 for i for analytics. Who knows, I might even wander of into some musings about the challenges faced when you get the dreaded "you're not serious" comment when suggesting IBM i and DB2 for i for your BI/Analytics application platform!  Yes, that's exactly what I'm suggesting ! I'll also pull in my colleagues and friends that also have a wealth of expertise as guest bloggers from time to time as we develop more content here. 

    DB2 Web Query is the IBM i based Business Intelligence/Analytics product designed to integrate with the IBM i operating system and DB2 for i database. With it, you can start simply by modernizing your Query/400 and RPG reporting environment, start adding more compelling visualizations, or even evolve it into full blown data warehousing – all leveraging your current infrastructure as an IBM i user.

    While I work on building up some posts in the coming weeks – be sure to check out the links here on the right hand side to learn more about this product – that you most likely already own!