Life After IBM Db2 Web Query for i: Finding an Alternative
Following the end of support for the business intelligence platform, IBM i consultant Rick Flagler answers the question, "What do we do now?"
When IBM Db2 Web Query for i (WQ) first became available, customers were EXCITED! To some, it was a replacement for Query/400. For others, it was a reasonably priced business intelligence (BI) tool. And for others, it was simply a convenient way to provide Excel downloads to client PCs. Time passed …
When IBM made the sudden announcement to withdraw sales and support for WQ after several releases, customers who had adopted the tool expressed surprise and dismay. The loss of an IBM i BI tool that had been around for over a decade created a quandary for IBM customers who used WQ to provide their business with key performance indicators (KPIs) via reports, charts and graphs.
I wondered how long it would take for someone to call and ask me, “What do we do now?” It did not take long, but first, let’s back up and look at the history.
The Background
IBM and Information Builders Inc. (ibi) jointly offered WQ, which was based on the product called ibi WebFocus. WebFocus had been around for a long time, starting with mainframe hardware, and could be purchased directly from ibi for the AS/400 and IBM i. IBM’s WQ offering contained a subset of the WebFocus features, so therefore had a lower and presumably more attractive price point as a Query/400 replacement. When TIBCO acquired ibi in 2021, it seemed that the WebFocus and WQ products would be supported and enhanced moving forward. Later, an entity called Cloud Software Group was created by a merger of TIBCO and Citrix by Vista Equity Partners. Suddenly, it appeared that IBM and ibi disagreed in some way, resulting in IBM’s sudden withdrawal of the product and support offerings.
Compared to the Query/400 product, WQ was a huge improvement, containing many features for reports, spreadsheets, charts and dashboards in HTML, PDF or Excel. It touted ease of use, used SQL for data access of Db2 or other database platforms and provided a metadata layer, a report designer and a GUI presentation layer. The metadata layer could be used to replace obscure field names with business-friendly names, change data formats and pre-join tables.
WQ could easily convert legacy dates, stored in numeric fields, often in a variety of formats to Db2 DATE fields. DATE columns enable numerous calculations to add/subtract months, days and years, determine days between dates and provide day or month names; all things that used to require hundreds of lines of RPG code.
The WQ Designer provided an interface for running and authoring reports that could be easily mastered by either programmers or users. IBM i customers, used to having their database and software on a central system, were happy that WQ provided a modern browser-based reporting capability. This was all running on IBM i, did not require external servers and was using the internal web server.
The Withdrawal Questions
For those impacted by the WQ withdrawal, the clock began ticking to find a replacement software solution with similar characteristics. For several customers I knew, this withdrawal raised concerns that a future Program Fix (PTF) would cause the WQ software to cease functioning, knowing such an event would interrupt or disable critical management reporting.
These concerns led to a review of the known software providers in the BI and query space. I started by creating a “shopping list” of web query features that would be desired in any replacement product:
- Browser-based interface for users and developers
- An IDE-like development environment for report creation
- Allow drill from one report to another (hyperlink to single report)
- Drill to multiple reports by clicking a hyperlink to pop up a choice menu of other reports
- Ability to auto-convert web query report objects (FEX, ACX, MAS) to new object/report
- Duplicate objects to make similar reports more quickly
- Metadata layer between Db2 and BI user
- Traffic lighting of rows/columns in a report by conditional values.
- (i.e., column value is > zero = green; negative = red; otherwise, leave as is)
- Ability to “pivot” data to mimic WQ’s ACROSS summarization of data.
- (i.e., year goes across top, salesman, customer, part# goes down left side)
- Prompting for date(s) with a calendar widget
- Parameter prompting for runtime values such as
- Salesman, Territory, Product—passing such input value(s) to reports
- Runtime selection/filtering using parameters, calculated values, etc., to impact results
- Calculations using database columns or SQL functions existing in Db2 for i
- Ability to utilize report dates in a range: 01/01/2023 -> 05/31/2023
- Generate reports to compare a current period to the same period in the prior year
- Ability to provide totals via SUM, MIN, MAX or AVG at detail, subtotal or grand total level
- Built in email system or integration with native SNDSMTPEMM command
- Ability to email to a distribution list or to individuals
- Ability to “burst” result data into multiple report (or Excel, PDF) files to be emailed to distribution lists
- (i.e., report break on SalesRep, send each SalesRep only their data)
- Product features bundled in a single offering vs. multiple parts
- Built-in scheduler or integration with IBM i Job Scheduler
- Product runs on IBM i server—no Windows, Linux or other hardware appliances required
- Db2 database access is native instead of on some other server platform
- Menu or portal as the front page, to present available reports to users plus control security
- Provide DEMO or proof-of-concept for key features:
- Reports
- Parameter passing and drill-down
- emailing and scheduling of XLSX workbook to distribution list
Armed with the above list, I began to review various offerings in the IBM i marketplace.
The Choices
Many of the software products and vendors addressing the BI and data analytics space will be familiar to longtime IBM customers. Vendors such as Cognos, Microsoft Power BI, NGS, Qlik, MRC m-Power, Fortra Help Systems Sequel and, of course, TIBCO/ibi WebFocus.
More BI vendors exist, but I narrowed my list to include software that runs totally on the IBM Power systems and IBM i. I did not consider software requiring additional Windows, Linux or cloud platforms. For companies who already have other servers in-house or in the cloud, it is important to understand that many companies have products that can run on various server platforms. You might expand your choices by that widened scope. Some, if not all, vendors have the software using Java-based or open source concepts to run anywhere.
I also didn’t consider AI capabilities. But by the time I finished evaluating software, a number of vendors had begun to mention future AI plans and the intent to include large language models (LLMs) within their reporting tools. The AI objectives will be to analyze database information to provide insights that humans did not see:
- Best performing products
- Worst performing products
- Supply chain weaknesses
- What a customer will buy next
- Likelihood of a sales, cost or profitability trend continuing
These ideas were outside of my scope, but surely will be of interest to those planning a BI project.
The Proof
In addition to the “shopping list” I mentioned above, I asked vendors to review report designs and provide proof-of-concept reports which would mimic what I’d provided. During my review of demos and proof-of-concept offerings, I often wondered whether any one vendor, other than TIBCO/ibi, could directly replace WQ. Having evolved over a number of decades, WebFocus and WQ had rich features and function, including some proprietary techniques.
I’ve found it often depends upon the audience that is going to be served. The expectations of the user community might drive the software selection. Some groups are numbers-driven; CFOs and finance professionals come to mind. Other groups are visually driven; sales and marketing comes to mind. So, dealing with Company/Department A, where users are accustomed to reports full of numbers, can be very different from Company/Department B, who like charts, graphs or dashboards. A third type of user is a business analyst who wants to play WHAT-IF to make reports on the fly. Future AI capabilities might appeal here.
WQ provided tools to create a wide array of reports, charts, graphs and visual analysis.
The Solution (or One Solution)
I narrowed the field to a couple brands and ultimately recommended MRC’s m-Power because m-Power checked most of the boxes in my shopping list. One appealing thing about m-Power is the use of templates for types of reports.
There are several templates, such as REGULAR, CROSSTAB and OLAP. Each of these report templates contain some standard characteristics, such as selection of table(s), columns(s), sorting, filtering and calculations. Depending upon the user requirements, one template or another might make it easier to generate the report.
• When a user simply wants a summary of data with some totals, the REGULAR report template easily enables you to get this result.
• For a simple list, either the REGULAR template or a WEB 2.0 template enables this.
• For a more advanced financial report, the CROSSTAB template may be used to provide This/Last period comparison reporting and summary break totals.
• For the analyst wanting to play WHAT-IF, the OLAP template enables many of the above capabilities. Plus, the data set can be sliced and diced by selecting various elements: dates, dimensions and sort columns via drag/drop. When applied, the changes cause the report on re-cast on demand.
• In cases where a data set will be used to generate many reports, some effort can be spared by establishing a data model. A data model could be built to join several tables, perform calculations or filter records. Then, various reports could use that model.
MRC’s m-Power was able to provide all the reports needed to meet customer demands to replace WQ, excluding perhaps the visual analysis. There are charting and graphing options for those wishing to build dashboards or suggest trends within reports using line, bar, scatter and pie charts.
The Supporting Cast
The MRC technical support group was able to quickly develop report prototypes to satisfy requested examples. While they were doing that, I was able to work with demo software on a website they’ve made available. See CrazyBikes.com or mrc-Productivity.com for more information about this.
Once m-Power was installed, as I worked on designing reports, whenever I asked MRC for assistance, they were eager to help and responded quickly. My support tickets were all answered rapidly as well. During a three-day product training class, when MRC determined that a feature we needed could be important to other customers, they added such features to the product or customized a template to make our lives easier.
Marketed as a low-code tool, perhaps it is not considered a BI tool, but the demos proved the product’s various capabilities and proved it could closely mimic reports provided by WQ. The software runs on Apache Tomcat on IBM i (or other platforms) and uses Java and JavaScript to build reports based on actions the developer takes in the designer. There is a way to get to underlying code, but it is used infrequently. At Run-time, the user can enter parameters and select one of several output types, then run the report. The user interface for all functions is friendly and easy to master.
The m-Power menu system lets you present lists of reports to users and group them by topic, and has security to control who can see what. Security can be driven by database or by user roles.
m-Power’s metadata layer isn’t as complex as what came with WQ or WebFocus, which is probably a good thing. It enables designers to give fields user-friendly names, manage connections to IBM i libraries and Db2 objects and resolve naming issues. There’s a separation of development and production, so developers can thoroughly test reports before pushing them to production. There is a dictionary concept to manage report objects and separate one development area from another. Of course, as with any BI tooling, knowledge of the underlying Db2 objects and data preparation/harvesting of ERP data will be necessary in order to maximize the report results. m-Power makes it easy to import the definition information for a table or view you’ve created for reporting.
I recommend MRC m-Power to those considering reporting or BI for their IBM i installation, and particularly to those seeking a replacement for WQ. In this article, I have only scratched the surface of what the m-Power platform can do. In addition to reporting, you might consider modernization, data entry, workflow, report scheduling or data exploration capabilities, many of which are included in the entry-level product, with some requiring the full version.
Good travels on YOUR data journeys!