Monday, July 1, 2019

There’s Never Been a Better Time to Install Db2 Web Query Version 2.2.1!

Whether you’re entering your summer fun months or getting prepared for winter, it’s a really good time to start thinking about getting your Db2 Web Query environment up to the latest level!

Just like the famous scene in the movie “A Few Good Men,” you need answers, but more than that, YOU NEED THE TRUTH! So here’s the truth. 

Nearly a year ago IBM announced end of support would be coming for the older release of Db2 Web Query, Version 2.1.x.  That end of support time is coming soon. IBM did NOT end support for Db2 Web Query, nor is Db2 Web Query going away. This is BUSINESS AS USUAL that when a newer version of software comes out, eventually support for older versions goes away. For example, I bet you understood the message about older OS levels going out of support!

If you missed the announcement note that support for 2.1 versions ends in September, 2019.

The LATEST version of Db2 Web Query as of this writing is Version 2.2.1. Don’t get confused….its 2.1 that is going away. If I recall correctly from my math days, 2.2 is greater than 2.1. And in this case, much greater!  And it has never been EASIER with our latest EZ-Install package for initial installation AND upgrading from previous releases, like 2.1.

The updated EZ-Install simplifies the installation or upgrade process but is much MORE than just laying down the licensed program products. With this latest version the IBM Db2 Web Query team has added additional sample reports and provides a TEST DRIVE and INTRO TO INFOASSIST set of tutorials for you to learn the product right out of the gate. As with previous EZ-Install packages, it has a pre-req checker, a Query/400 Discovery tool, the sample reports, and a date dimension table! EZ-Install loads up ALL products under a 70-day trial (which you can even extend for another 40 days if needed). 

System Administrators might like some of the new and updated reports over IBM i Services.

Adding multiple system information for the PTF Currency report and System Status reports makes it easier to view metrics across all your IBM i LPARs/Server.

New or updated reports/charts are available for looking at system storage related metrics. Below is an example of an included temporary storage visualization.  

One of my favorite new reports is a spoolfile display report with a drill down to convert the spool file to PDF (and can easily be enhanced to convert to other formats, like Excel or HTML).  

Because I know you can handle the truth, here is a bit more truth: Db2 Web Query Version 2.2.1 is the ONLY version (today) of Db2 Web Query that supports the latest IBM i OS release - 7.4. The upgrade process is VERY  EASY with the EZ-Install package, so there is no sense waiting around till its too late! 

If you have any questions or need to request the latest EZ-Install package, please reach out to us at and we’ll be happy to help (please include the serial number of your system if requesting EZ-Install).

Monday, February 19, 2018

To SQL or not to SQL - with Db2 Web Query

Sometimes I find myself talking out of both sides of my mouth when discussing Db2 Web Query capabilities. The term is related to contradicting yourself – maybe saying one thing to one person, and another, opposite thing to another. 

While certainly not trying to deceive anyone, the association with Db2 Web Query is about whether you need to be an SQL programmer to use it. The short answer is, ABSOLUTELY NOT. However, out the other side of my mouth, I have to say, it certainly can be an advantage if you do know SQL!

Db2 Web Query provides a graphical interface to building reports, dashboards, and BI applications. With the metadata interface simplifying the database structures for report authors, the graphical interface is easily used by those other than programmers and database experts. The “Business Analyst” is a classic power user of Db2 Web Query, building reports from scratch and never having to code at an SQL or RPG level to accomplish their goals – because under the covers Db2 Web Query generates the necessary code to access the data and provide the report logic, formatting, etc. 

While we strongly discourage you from editing any of that code, you can see what is generated by right clicking on a report or chart and choosing (if you have authority) to open with text editor. The code probably won’t make any sense to you, and you definitely DO NOT want to edit anything there as it might mess up the execution of the report. The code is stored in IBM i in something we call the repository. 

Many people have asked the Db2 Web Query team if you can write your own, or leverage existing SQL code in a report. So out the other side of my mouth I say “Absolutely!”  Reports, charts, and dashboards can contain data from many different data sources. Db2 file/tables are the most obvious data source, but did you know you can also leverage existing Query/400 definitions, SQL Views, SQL (or any HLL) stored procedures, Db2 functions (including user defined functions), and uploaded SQL scripts? You can also upload Excel spreadsheets and with Standard Edition, access non Db2 databases such as MySQL, Postgres, and just about any relational database you can get a JDBC driver for. 

A good example of leveraging the power of using SQL as a data source for reports is one I’ve been showing off lately to simplify some pretty complex reporting requirements. The examples use new IBM i 7.3 OLAP extensions to the SQL language. These new capabilities in the SQL language make it simpler to code, and will perform extremely well.

Suppose you are in Human Resources, and you want to regularly check salaries of your employees to ensure that are in line with corporate policies. For instance, maybe all employees need to be within an average for their department, or within a department you don’t want one employee to be an anomaly compared to others – either way higher or way lower. You might want to answer these kinds of questions: 

  •        What is the employee’s salary compared to the average WITHIN their department?
  •        What is an employee’s ratio of salary within their department and overall company?
  •        For each employee, can you compare their salary to the two closest behind this person’s salary, and the two closest ahead of them in salary? 

While a couple of these might be easily answered by using the graphical interface to generate the necessary code, that third bullet is really going to make you think about how you might do that. It might require the age old approach of using a work file. Db2 Web Query supports the work file concept using something called HOLD files, all handled within a single report. Within a report, you can run your query, generate a HOLD (or temporary) file, and then have the report complete by querying the HOLD file. So while do-able, it is a lot of work to build this kind of report, and the report itself will take a while to run if its having to do multiple passes of the data. 

An alternative to simplify the creation of, and speed up the execution of the report is to leverage SQL capabilities in 7.3. You can read more about these enhancements in Mike Cain’s blog on database enhancements in7.3. For my example, I used new “Partition By” syntax to create logical windows looking into the data and then performing aggregation functions or OLAP functions such as LAG and LEAD against the data in each window. 

The first step (after learning a bit about how these new 7.3 functions work) is to build the SQL to get an average of salary across each employee within each department, and secondarily, get a view of each employee’s salary compared to the two closest salaries (still within their department) both above and below theirs.

Using my favorite SQL Scripting tool, Access Client Solutions (ACS), which you can download for free here, I created these two scripts below that create VIEWs with my select statement (I could have chosen to just create the scripted select statement instead of putting it into a VIEW object, and then uploaded that into Db2 Web Query). 

To use these now in a report you need to create the metadata (called a synonym) over your view or uploaded script. The steps are pretty close to the same whether it’s a view or an uploaded script (you’d choose External SQL Scripts rather than “Tables, Views, and Other Objects").  

Once your synonym is built and tested (use the SAMPLE DATA option within the synonym editor) to run the SQL statement and validate you get the data you were expecting you’re ready to build your report, chart, or visualization:

Note that I added a filter so the “salary analyst” can select different (and multiple) departments or look at all employees. The prompt is automatically built and available when you run the report. 

Now let's create a drill down with the first SQL View I created. Here is an average salary report with an auto drill down on any of the department values to link to a chart for just that department’s employees: 

Each of these reports probably took at most about 15 minutes to develop using the new 7.3 SQL functions and Db2 Web Query. And when I run the reports the response time is sub-second. You’d be hard pressed to gain that kind of productivity and response time using the workfile approach! Also, if your SQL View ever changes, all you need to do is refresh your synonym.

There are many other examples of using SQL within Db2 Web Query if you are comfortable with it. For instance, a couple of weeks ago I was with a client that had already written some user defined functions and was interested in leveraging those to “un-tangle” data in their reports. Why re-invent the wheel?

In a recent prototyping exercise to demonstrate how you can integrate Db2 Web Query with Watson, our team leveraged an SQL Stored Procedure to build a weather augmented data mart using Db2 Web Query as the foundation for data gathering and visualization of the consolidated data.

Some customers using the old Showcase query builder tended to write SQL statements, and find they can leverage those same statements within Db2 Web Query to provide the same data access but enhance the output with the many ways to display the result set, i.e., leveraging already existing SQL!

SQL is fun and easy to learn – but you don’t have to and can let the graphical interface generate the appropriate code. No more talking out of both sides of my mouth (on this topic anyway)! 

Tuesday, October 3, 2017

New Db2 Web Query Version 2.2.1 Announced!

The evolution of Db2 Web Query for i continues with a new version announced on October 3rd, 2017. With Version 2.2.1, both the Business Intelligence product set and the DataMigrator ETL (Extract, Transformation, and Load) component are enhanced - adding several new features to address user requests as well as bring new industry features for reporting and analytics to IBM i customers.

There have been several times in the history of this product where IBM has delivered minor changes, and other times when you could say the product capabilities took a major leap. An example of a big leap was when Db2 Web Query went from the Report/Graph Assist report authoring tools to the much more intuitive, powerful InfoAssist component.  Another major leap was adding DataMigrator ETL with its integrated ability to un-tangle data into data marts or data warehouses.

Version 2.2.1 marks another major leap! This new version, available in mid-December, extends the community of users to the data analyst (or data scientist). New powerful “data discovery” analytics and visualizations make it even easier for analysts to navigate through data to understand what is driving the business. 

End users will be more empowered by new features such as auto link, auto drill down, and dynamic parameter navigation. Version 2.2.1 makes it easier for the end user to iterate through data and across multiple reports without having to go back to the report author for changes in the original report(s). The ability to interactively select data on the report to create a filter will be very useful to analysts to focus on related data of interest – within the same report.  

Were you aware of the geographical mapping visualizations in previous versions of Web Query? In 2.2.1, this gets even more interesting with additional enrichment to bring in information to your maps quickly and easily. New “layers” on the maps support such things as adding demographic data. 

Learning how to build new Data Visualizations will be easier with embedded video links to walk you through the steps. Here is a complete set of Version 2.2.1 Video Demonstrations - be sure to look at the ones titled "Visualizations." 

Upgrading to 2.2.1 is a no charge software update (assuming you are current on software maintenance). The Db2 Web Query EZ-Install package for 2.2.1 is now available to simplify that upgrade, or you can use it to install for the first time and run the complete product set under a 70 day trial.

To request the package, send an email to and include your name, company name, and serial number of the system where you plan to install the software. When you're done installing we'll point you to a website for "next steps" that includes a test-drive, tutorials, and documentation on the Query/400 Discovery tool and Sample System Administration reports! 

Wednesday, May 24, 2017

DB2 Web Query: BI on a Budget Webinar

I have just returned from two conferences these past 2 weeks – the COMMON User’s Group annual U.S.  meeting in Orlando, FL., and an IBM sponsored event in Paris specifically for IBM i users. In Paris, I spoke about database revitalization to a room of over 500 IBM i folks, and at both events covered a number of DB2 and DB2 Web Query topics. In the last couple of weeks I have probably presented to over 1000 people! 

These events, along with others, such as the RPG/DB2 Summit, are a really great way to hear about what IBM is doing to deliver new function across a broad set of IBM i technologies. However, events require travel costs and time, which not everyone has a wealth of!

With this in mind, and assuming maybe you didn’t get to an event, I wanted to share with you a SHORT recorded webinar we (Rob Bestgen and I) conducted this week providing an overview of DB2 Web Query for i and details on the new (and very popular it turns out) EZ-Install Package.  The hour long webinar will be available for some time and you should check it out! 

To register for, and listen to the webinar, click here  -  and ENJOY!  

Thursday, January 12, 2017

DB2 Web Query: EZ-Install for a Fast Path to Value

DB2 Web Query EZ-Install – Now Available

One of the things I hear often at speaking engagements or by working directly with customers around the world is that they know of DB2 Web Query, would like to evaluate or get some hands on experience, but aren’t always sure how to get started and gain quick value from it. In 2016 we started thinking about ways to simplify the getting started process, and that has led to the emergence of something we’re calling the DB2 Web Query EZ-Install package!

With its availability TODAY the DB2 for i Lab Services team is providing a simplified process of obtaining and installing the complete suite of DB2 Web Query trial software. The products are at the latest level (including Group PTFs already applied) and include both Express Edition, Standard Edition, and the DataMigrator ETL tool. The trial code period for this is 70 days, although you could choose to apply license keys at any time and have this become permanent (just keep in mind your license keys will dictate which components you have purchased which may be less than the entire suite you’ve got in this package). Also note that while this package is made available to you, it should not circumvent the normal ordering and acquisition process. 

One big benefit of the EZ-Install package is...IT'S EASY. A many step process including acquiring the code, installing it and setting it up is made much simpler by providing a one-stop shopping install package that also sets up the environment for you so when complete, you are ready to go to work. 

For those of you that have not previously installed DB2 Web Query, this is a great place to start. For someone with DB2 Web Query already installed, think of this as a quick way to get the latest version installed for testing or analysis purposes. Note that for existing customers with Version 1 installed, this will install Version 2.2 right next to that (if done in the same LPAR/Server). If you install the EZ-Install package in the same LPAR where you have Version 2.1 installed you should be aware that this will overlay that code with 2.2 (but EZ-Install will prompt you to make sure you really want to do that). 

The EZ-Install package is also more than just the products and set up. We've included some other goodies, including: 

  • Everything you need to start right into learning how to build reports with InfoAssist

This allows you to jump right into learning how to build reports with the InfoAssist authoring tool. Our current set of tutorials includes over 14 chapters that will make you very proficient in report authoring in no time. As part of the package we install the sample database and the meta data built over that so you're ready to dig in. 

Remember that middle school math class where you were given a problem to solve, but the complete answer was laid out for you in the Appendix of the book? If you skipped to the answer, it would circumvent your learning experience. So I encourage you to take on the challenge of completing the tutorials although we are admittedly giving you the appendix too! In other words, the package will contain the complete set of finished tutorial reports so if you need to compare what you’ve done against what the DB2 Web Query team has done, you have that ability.  Or, use those reports to demo the product to your management.

  • DB2 Web Query  Usage Reports

As a first time user, this might be of less interest but over time will be very beneficial to understand who is running what reports, which reports are taking the longest, and more. See my previous blog entry for some examples of these reports. The EZ-Install package will contain these monitoring and auditing reports and auto-install them.

  • Sample Reports for the IBM i Systems Administrator

Want to get away from green screen? The EZ-Install package includes several sample reports applicable to the metrics a Systems Admin would like to monitor and report on. Visualizations are provided over such system aspects as temp storage usage, utilization metrics such as disk I/O, CPU consumption, and spool files.   

For clients already running Version 2.2 of DB2 Web Query, we are making these sample reports available outside of the EZ-Install package. See below for acquisition details. 

  • Query/400 Report Definition Discovery Tool and Sample Reports

I just recently worked with a client that had to move to a new version of their ERP system. They were quite concerned that existing Query/400 reports would be affected by this change but had no idea how to scope that possible impact. I had them run our Discovery Tool to glean information about their current queries – how many do they have (turned out to be over 10,000), which files were used the most by these queries, which queries haven’t been used in months or years, which fields of those files are most used in selection criteria or result sets, etc. This is information gathering to assist in understanding potential impact as well as input into a modernization strategy

This Query/400 Discovery tool is now available as part of EZ-Install. It is not automatically installed as you want to read the usage guide to understand what it is doing and how to use it, but its there with some sample reports over the files that are created containing the output of the tool. 


Send an email to requesting the DB2 Web Query EZ-Install Package. Be sure to include your: 
  • Name
  • Company Name
  • Serial Number (of where you intend to install DB2 Web Query)

NOTE: To acquire the standalone system administration samples outside of the EZ-Install package (this assumes you’ve already got Version 2.2 installed), again, send an email to requesting the System Administration sample reports package and include the same information as noted above).

Everything you need will be included in the email response back to you, including a link to the package, installation guide, and information on where to go from there: HINT:

Please send any feedback you have for us at We'll try to support any issues as best we can. 

You can also reach me at mackd AT or on twitter @mckdrmoly if you have questions or need assistance!

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!  

    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 or on twitter @mckdrmoly if you have questions or need assistance!