IBM i > ADMINISTRATOR > DB2

IBM DB2 Web Query for i: A Secret Weapon for Your Business


A lot of companies have traditionally written reports with Query/400 or RPG using print files. These techniques are great for many situations. Reports enable executives to see year-over-year comparisons, profitability vs. sales. They often list a group of customers or items by sales segment or product family. What these standard reports potentially lack, is the ability to drill down to additional details or graphically display the underlying data. In some cases, business analysts use both period-ending reports and a spreadsheet to depict trends with charts or graphs, but first, they may have to transfer to Excel or even re-key information.

DB2 Web Query for i provides enhancements to these reporting scenarios by enabling generation of reports, charts and graphs directly from IBM i databases. The tooling provides reports with summarization, dashboards of key metrics, trend reports with area, line, and bar charts. Companies may not realize that with recent releases of the IBM i OS, they were entitled to replace the Query/400 program product with DB2 Web Query. It may have been given to them as part of the upgrade to a POWER System.

Web Query uses SQL on the back end to get data from DB2. On the front-end, it employs a drag and drop report designer that requires minimal to no knowledge of SQL language syntax. The report developer simply drags data elements to the canvas and specifies sorts, selection, sequence and totaling. Web Query does the rest, creating SQL to handle the request and return the report to the browser of the user.

Of course there’s more to it, but Web Query is easy to use for many reporting scenarios that IBM i users have everyday. Did I mention you can select target output type at run-time, enabling export to Excel for financial users?

WQ is more flexible than Query/400 ever was. Once a database object is exposed in WQ, the end user can create numerous reports without intervention by IT staff. One issue in Query/400 occurs when tables need to be “joined” to produce desired results. Depending upon the complexity of joins, and the expertise of the report author, you could have erroneous results.

WQ allows a business analyst or DBA to pre-establish joins so they are correct when used by non-technical report consumers. Designers of reports can also create both simple and complex reports that accept run-time parameters to reduce the number of reports required for the organization. A single report can ask the user for parameters, thus enabling different results. WQ can also create queries that allow the user to start at a top-level summary report, chart or graph and drill down into secondary levels such as customers, parts and sales groups, related to the segment selected at the top level. This makes reporting considerably more powerful.

There's nothing wrong with reports! Reports have been good for 50 years of data processing. But whether a report is on green bar paper, 8 1/2 x 11 sheets or spooled out to a PDF document, it potentially lacks several things: 1) ease of portability and mobility 2) interactivity 3) decision support capabilities. DB2 Web Query addresses this with support for all three.

How does Web Query for i have an advantage over these former technologies? Let’s look at three scenarios.

Sales Force

A salesperson who travels frequently, wishes to review customer information while she’s on the plane, flying to meet the customer. Previously, she might download several PDFs or spreadsheets. Web Query incorporates a capability called Active Reports. An Active Report incorporates not only data, but also interactive functions for manipulation of that data. For example, the salesperson might review top-10 products for the customers she’s visiting, examine a chart of sales revenue over time or check delivery performance for product shipments to the customer. She might select certain products or orders for review by product family, gaining knowledge for the meeting from the data. She might even share information during the customer meeting, using a tablet or laptop.

Executive

An executive wants to review several key performance metrics for his business. Web Query dashboard and document capabilities combined with the power of DB2 summarization might be employed. IT professionals could use WQ to create a dashboard of key metrics, available every morning from ERP databases. Resulting reports, charts, graphs or numbers could be placed on a single page, and can be refreshed on demand. Combined with nightly auto-update (or refreshed several times daily) this dashboard could provide both current status and trend information on numerous key metrics.

Plant Data

A manufacturer wants to hang large screen TVs in several factory locations, to keep associates informed about throughput and work order status. A Web Query HTML page that auto-refreshes every 3-minutes could be used to drive the screen and display key metrics or work status.

Combining various DB2 Web Query capabilities for snapshots or trend reporting is powerful. The tool’s capabilities run the gamut from in-house reports on paper, in browser and in email, to connected or disconnected presentations, on a tablet, phone or laptop. IBM calls this “Write Once - Run Anywhere” and DB2 Web Query for i lives up to that claim. Abilities to slice and dice sales numbers while aboard an airplane, might be a game-changer for mobile users. And, we can do this without building a custom application because the Mobile Faves App has these capabilities built in to access information from your IBM i system running DB2 Web Query. All you have to do is make the database known to WQ, and start designing reports.

In the spirit of full disclosure, some capabilities mentioned above are functions of the Standard Edition: a fee-based license upgrade from the Express version that most IBM i sites will originally get. However, the tools’ power may enable you to receive a rapid payback and are worth exploring with Express by building some Proof-of-concept reporting.

To get started with Web Query:

  1. Check to see if you are entitled to the product
  2. Update to Version 2 with latest PTFs
  3. Pick a favorite table and create metadata in Web Query
  4. Write a report, chart or graph

Additional capabilities of DB2 Web Query will be explored in subsequent installments of this article.

Rick Flagler is an information technology consultant, teacher and mentor.



Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.



Advertisement

Advertisement

2017 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Untangling Web Query

How metadata can reduce query and report complexity

Protection From Slow Queries

Introducing DB2 for i Adaptive Query Processing

Intelligent Queries

DB2 for i Learning Statistics Engine works smarter

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters