DB2 Web Query for IBM i Simplifies Reporting
If you’re one of the IBM i shops that’s planning the upgrade from IBM i 5.4 to 6.1 or 7.1, you have a great opportunity to install IBM DB2 Web Query, if you haven’t already done so. Over the past couple releases, both DB2 and DB2 Web Query for i have received a lot of attention from the IBM development team. Web Query is built for IBM i and designed to replace the Query/400 product. It has the capability to run similar and more powerful queries and output them in a variety of ways.
The old adage that “a picture is worth a thousand words” may apply here, as Web Query results can be viewed as textual reports or as charts of various types. Web Query is optimized to work specifically with our DB2 database, a database that has been tuned to support SQL for both transactional and business intelligence (BI) query loads. DB2 Web Query takes advantages of such things as Encoded Vector Indexes and the SQE for fast data lookups, joins and data retrieval.
With Web Query, you don’t have to move data off the IBM i platform to another computing environment. Query/400, while still available, may no longer meet the needs of the modern organization seeking to use Web browsers or mobile devices to view business data, charts and metrics. Web Query enables shops to move to a modern BI interface, more easily distribute reports, and even create dashboards and other decision-making tools for key managers and executives.
In this article, I’ll review a simple BI application example, with the DB2 Web Query objects to report on sales data, and take a look at the tools DB2 Web Query provides for the task. I’ll show the resulting report and make a chart using the same data. For RPG programmers who might attack this with in a program, you’ll want to contrast the level of effort required in RPG with the approach in the Web Query environment.
The collaboration between IBM and Information Builders Inc. that created DB2 Web Query for i brings several tools for managing the query environment—the Web Query home page or portal, the Web-based InfoAssist content development tool and a PC-based client tool called Developer Workbench. The home page or Web portal (Figure 1) is where content is both created and later used. The user can launch queries or edit content from this portal (Figure 2). In version 2.1, the portal evolves further to allow users to create personal favorites, mobile favorites and personal portal pages. In addition, DB2 Web Query document editing facilitates the development of dashboards (Figure 3).
The principal tool is the InfoAssist module, which is used to author a report, document or chart. Before you can design a report, you must create metadata, which then becomes the data source for report creators. Metadata is a descriptive layer between the DB2 database object and the user presentation. Metadata serves several key functions:
- Tells the query tools where data is located
- Tells how data is formulated or enables an administrator to modify data format, terminology, computations and drill-down hierarchies
- Defines how related tables are joined and to create OLAP-capable drill-down hierarchies
The advantages of metadata could as simple as renaming field “ARSSLS” to a user-friendly “Annual Revenue Amount” or changing a non-date decimal 8,0 field ARSDT to a DB2 DATE type field named “Invoice Date”. Metadata probably warrants an article on its own, however, so this article will focus on report design and data access using the InfoAssist tool. Also in the Web environment, security parameters for data sources are set and DB2 Web Query objects (query executables) can be run to view data.
The Developer Workbench, which is a PC-based development environment for DB2 Web Query is used for advanced content creation with HTML documents and is more of a programming tool than InfoAssist. While the Web-based environment offers some metadata-editing capability, the Developer Workbench further enables those tasks a DBA might want to do, such as predetermining table linkages when joined or creating drill-down hierarchies. In many companies, one set of IT users will act as DBAs to formulate the metadata so that others can do reporting. In some shops, developers will be doing both metadata creation and BI reporting while letting knowledgeable end user business analysts write their own reports.
A report designer will typically have advance knowledge of the data sources, called Synonyms, which result from generating metadata over existing DB2 tables or physical files. DB2 Web Query and DB2 know the difference between a table created with DDS and one created with SQL DDL. To get optimum performance from SQL on IBM i, it’s helpful to have created the underlying DB objects with one of the SQL interfaces. This is because the tables, views and indexes will be optimized to use the improved query engine. However, if you have a system that utilizes physical files from DDS rather than SQL created tables, fear not. DB2 Web Query will still access these files with the same interface. And regardless of how your tables are defined, you can use various SQL techniques to extract and/or summarize data from your existing database into tables, views, stored procedures or materialized query tables (MQTs)—all of which can be exposed to DB2 Web Query via metadata. Web Query is very flexible in this way.