Db2 Web Query: Way More Than Just A Query Tool

February 27, 2023 Alex Woodie

You might be forgiven for thinking that Db2 Web Query is just a way to run your SQL queries on IBM i from a browser. After all, “Web” and “query” are right there in the name, aren’t they? But the product’s name belies the true nature of this multi-threat analytics application, which can do a whole lot more for IBM i shops than just run queries.

IBM’s Doug Mack tackled this reality-assumption gap during a recent webinar on Db2 Web Query, which received a bunch of new capabilities with the recent version 2.4.0 upgrade. Mack, a longtime IBMer whose official title is “Db2 for i consultant” in the Technology Services group (formerly Lab Services), is the de facto product manager for Db2 Web Query. (Naming things, as we all know, has never been IBM’s strongest suit.)

Mack recalled a conversation his Technology Services colleague had with a senior IBM communications official at the COMMON NAViGATE event in St. Louis, Missouri last fall. “The person was asking them about Db2 Web Query. They assumed by the name it was just really just a browser-based version of Query/400,” Mack said in the webinar. “Totally underselling its capability.”

Query/400, of course, is the query utility most System i, iSeries, and AS/400 customers used before IBM debuted Db2 Web Query way back in 2007. The product was shipped on the midrange box for many years, and is widely used as result. But in comparing the old greenscreen tool with the much newer Db2 Web Query, one is quickly confronted by a vast gulf in functionality.

For starters, Db2 Web Query isn’t a descendent, direct or otherwise, from Query/400. In fact, Db2 Web Query comes from an entirely different technological tree. It’s wholly based a business intelligence product called WebFOCUS that was written in Java and that was originally developed by Information Builders, which was acquired by TIBCO back in 2020. The OEM relationship between IBM and TIBCO has remained in place following the deal, which should come as a relief to Db2 Web Query users and prospects.

While Query/400 was a query tool, Db2 Web Query is much more than that, according to Mack, who described Db2 Web Query as a full-blown platform for building a data warehouse on IBM i and using it for business intelligence and data analytics purposes.

Db2 Web Query is much more than just a query tool. (Image courtesy Doug Mack and IBM)

There are a number of features that separate a mere query tool from a analytics or a data warehousing tool, according to Mack. For starters, a barebones query tool may provide a way to create SQL queries and submit them, but it doesn’t do much beyond that.

“If you’re using a query tool, you’re basically building a free-form SQL statement or having a GUI generate an SQL statement,” Mack said. “There’s really no mechanism in place to control that. That’s when you get into that runaway query environment.”

Db2 Web Query, on the other hand, provides more of a controlled ad hoc environment, Mack says. It still gives users the freedom to work with the query and change the attributes that will show up in the report, but it does so without giving up control, he says.

Similarly, a query tool doesn’t provide much in the way of monitoring the queries once they’re submitted, whereas a full-blown analytics platform like Db2 Web Query does.

“With the query tool, you really don’t have anything available to you except the underlying database, things like the plan cache, and that’s a lot of work to try to do that,” Mack said. “With Db2 Web Query, we provide probably a dozen or two built-in reports to help you manage your query environment and help you answer questions like ‘Who’s running the longest query?’”

Security is another differentiator between bare-bones query tools and finished BI products. There isn’t much security built into Query/400, which means an IBM i shop should implement either object-level security or use exit point programs to prevent unauthorized access of data.

The abstraction layer in Db2 Web Query is a key advantage over basic query tools.

Db2 Web Query automatically adheres to security capabilities built into the database, such as object-level security and row-and-column level security, Mack said. But the product goes beyond that with additional security capabilities that restrict users to accessing only the objects for which they have been granted access. Plus, Db2 Web Query is a “fairly read-only environment,” he said. “You’ve got to be pretty clever to figure out how to write back to the database in Web Query.”

But the biggest difference between a query tool and a business intelligence product, Mack said, is the abstraction layer. In a query tool like Query/400, a database expert is required to navigate the complexities of the underlying data model. Deep SQL knowledge is also required to be able to create the database joins and other entities required to extract actionable insight from raw data. You’re pretty much on your own.

But thanks to the abstraction layer in Db2 Web Query, that deep level of database and SQL expertise is not required. “With an abstraction layer, it’s taking a different approach,” Mack said. “Just about anybody can become a report author, and they don’t have to know the intricacies of that data model.”

Db2 Web Query uses abstracted data entities called “synonyms” to help shield users from the underlying data complexity. Called metadata in other platforms and tools, the synonyms in Db2 Web Query help the user turn complex and messy data, such as packed date fields in Db2 for i, into data that’s more understandable by less-technical users, such as natural dates. “You’re hiding the complexity of the database,” Mack said.

But that’s not all. In addition to manipulating fields, designing SQL queries, and creating dashboards, reports, and KPIs, Db2 Web Query brings other capabilities that make it much more than just another query tool.

For example, the product contains an ETL (extract, transform, and load) tool called Data Migrator that helps users move data from external source systems, such as Oracle or SQL Server databases, into the Db2 Web Query environment. With the new version 2.4.0 release, IBM has updated Data Migrator to be able to stream data from Db2 for i into a cloud data warehouse, such as AWS Redshift or Snowflake. Users can even use Data Migrator to load Db2 for i data into a real-time message bus such as Apache Kafka.

“If you haven’t heard of Apache Kafka then you’re going to because a lot of people are using that as a way to integrate data from a source into just about any target, including cloud databases and cloud services,” Mack said.

Data Migrator isn’t well known, Mack said. “It’s a hidden gem,” he said. “You know that I said Web Query is not a query tool. It’s business intelligence and it’s infrastructure as well. This is the infrastructure component.”

Mack covered some of the other new features in Db2 Web Query 2.4.0, which we covered back when it was announced, including updates to the mew Designer component, which is slowly closing the functional gap with the legacy InfoAssist tool that customers have traditionally used to design dashboards and reports.

“We’ll continue to see over the next several releases enhancements to the Designer tool,” Mack said. “It’s fairly new. It does not replace the more traditional way of building reports and chart through…Info Assist. This is really more complementary to InfoAssist.”

Another big change with 2.4.0 is the addition of Instant Insights, which brings pre-built machine learning models to bear on data in Db2 Web Query. Instead of hiring a data scientist or upskilling a SQL-loving data analyst with data science skills, Instant Insights allows analysts to get some of the benefits of machine learning, such as automated clustering and classification of data, right on their screen.

Db2 Web Query turns 16 years old this year, and it’s pretty clear that it’s ready to drive. As data and data analytics becomes more critical to competing and winning in the market, having a capable and multi-pronged analytics platform like Db2 Web Query definitely gives IBM i shops an advantage. It’s not the only business intelligence tool for IBM i, and it’s not free, but it should definitely be on the short list of native IBM i products to check out should you find yourself in the market.

For more information about Db2 Web Query and version 2.4.0, including a recording of Mack’s TechChannel webinar, which he said “might be the best” he’s ever done, check out the Db2 Web Query support page at www.ibm.com/support/pages/ibm-db2-web-query-i.

This Issue Sponsored By

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search