Learn about DB2 Web Query Version 2.1, the latest and greatest querying and reporting technology for the IBM i.

IBM DB2 Web Query for i was first released in 2007 as the strategic query and reporting tool for the IBM i system. It has many benefits, among them modern and graphical query development tools and output formats, the ability to leverage the powerful SQL Query Engine (SQE) in DB2 for i, a metadata layer to hide database complexities, and a single-server-based implementation that runs natively on the IBM i. The product has been enhanced greatly since this initial release, and on June 15, 2012, IBM made generally available Version 2 Release 1 (V2.1) of IBM DB2 Web Query for i. V2.1 was a milestone version and introduced several new changes that significantly enhance the product: everything from simplified ordering to powerful new functionality. The new version is based on a revamped design that lays the foundation for many future enhancements and includes many features that customers have been asking for.

The June 15 date also signaled the end of marketing and ordering for the existing 5733QU2, 5733QU3, and 5733QU4 products and options. However, additional licensed users, Developer Workbench, and one-year software maintenance contracts can continue to be ordered for these products. In addition, the abilities to upgrade your IBM i system to a newer release or to transfer ownership to another machine while retaining QU2, QU3, and QU4 remain available.

Product Ordering and Packaging Simplified

The first thing customers will notice is a much simpler ordering process for DB2 Web Query. In versions 1.1.x, there were three product IDs (5733QU2, 5733QU3, and 5733QU4) and seven different options for the 5733QU2 product. This left many customers in a confused state. Common questions were:

• What exactly should I order?
• Do I need all of the products and options?
• What's included in the base product?
• Do I need option 4 (Runtime User Enablement) if I want to use the Software Development Kit?
• Which products and options do I need if I want to distribute reports via email?

 To address this, the entire product suite has been repackaged in V2.1. In this version, the various products and options have been bundled together. The result is but two product offerings: Express Edition and Standard Edition.

Express Edition

DB2 Web Query Express is the entry-level edition and has a new product ID for ordering: 5733WQE. This version is ideal for those customers that need basic querying and reporting capabilities for a small number of users. Included in Express Edition are the following features:

• Business Intelligence Portal user interface
• Metadata and report authoring tools
• User management security center
• OLAP Analysis
• Spreadsheet client (Excel plug-in)
• Mobile support

Express Edition comes with a predefined number of named user licenses; if you need more, you can order additional licenses. It does not come with the Developer Workbench tool. However, you can order that tool if needed.

Standard Edition

The Standard Edition of DB2 Web Query provides a very robust set of capabilities supporting virtually unlimited number of (runtime) users. The new product ID for ordering is 5733WQS. Standard Edition contains all of the capabilities of the Express Edition plus the following:

• Ability to have virtually unlimited runtime users
• Report scheduling, distribution, and bursting
• Application integration capabilities
• Developer Workbench tool
• Ability to access other data sources using the included SQL Server and generic JDBC adapters

As with Express Edition, you can order additional licenses of named users and Developer Workbench users. In addition, you can order more runtime group profile licenses if more are required in your environment.

While there are two different product IDs for ordering, when you actually receive the product for installation, DB2 Web Query v2.1 is a single product to install on the IBM i: 5733-WQX. It has options to correspond to each ordering level:

• Express Edition — option 1
• Standard Edition — option 2

The prevailing theme is this:

DB2 Web Query: SIMPLE TO UNDERSTAND, SIMPLE TO ORDER!

Core-Based Pricing

Part of the repackaging was the decision to move from a tier-based pricing model to a core-based one. While single-core customers may not see the advantage to this, it may provide a mighty attractive solution for those clients with multi-core systems. For example, if you had a four-core, P30 system, your only option was to purchase Web Query for the P30 level. Thus, you were paying for use of the product across all four cores. Now, with V2.1, you can isolate the Web Query workload to a single core and subsequently pay for only that core. This can result in substantial savings to your company and is especially important when you consider that every Power7 model now contains at least four cores. This new pricing model now allows you to apply the appropriate software licensing to actual usage requirements.

DB2 Web Query enforces this using Workload Capping (also known as Workload Groups) support on the IBM i. All DB2 Web Query jobs run in the QWEBQRY21 subsystem and will be capped (across all partitions) at the core level. So if you purchase workloads for only a single core, Workload Capping ensures that multiple cores are not used to process DB2 Web Query requests. 

New Security Model and Interface

Many clients wanted the ability to allow certain developers to create and edit reports yet not have edit access to the metadata. They wanted the role of the metadata editor to be a different user profile or group of users. DB2 Web Query V2.1 adds a much more granular security model with role-based definitions. Now all DB2 Web Query information (both the reporting repository and all IFS directories) are completely locked down, and access is only allowed through authorization lists and DB2 Web Query application-level security. To access DB2 Web Query, a user profile must be added to what is referred to as a permission set group. The type of access depends on which permission set group they are a member of. Each top-level folder (formerly known as a DB2 Web Query domain) has the following permission set groups:

• Run—Can run reports
• Analyst—Can develop private reports and schedule them to distribute back to Web Query
• Developer—Can develop and schedule private and managed (public) procedures
• DBA—Can only manage metadata
• Sched—Can schedule reports to run in background mode (using Report Broker)
• Folder Administrator—Can move people in/out of Folder
  
  

To simplify administering this new security model, a major new enhancement in 2.1 is the DB2 Web Query Security Center. This new feature provides a useful interface for a DB2 Web Query administrator to manage users and these permission set groups. Figure 1 provides an example of the Security Center interface. You can see that users COBBG, SIMONA, and TBAER are members of Century_Electronics_dev group. This means those three users can create, edit, and delete reports in the Century_Electronics top-level folder.

Figure 1: Security Center shows the members of the developer group.

Now contrast that with the members of the members of the Century_Electronics_dba group as shown in Figure 2.

Figure 2: And these are the members of the DBA group.

The user profiles in this permission set group are RFLAGLER and SEPTIA. These users can administer metadata in this top-level folder. This means they can create, edit, and delete synonyms. But they cannot create or edit reports. Conversely, the users COBBG, SIMONA, and TBAER cannot manage metadata. So separation of duties can be achieved with the new security model!

One important thing to keep in mind: These permission set groups are not IBM i group profiles, authorization lists, or even IBM i objects. They are Web Query-only constructs, and the information is stored in the internal Web Query repository. The only way to access and maintain them is through the DB2 Web Query Security Center. 

One Development Tool

The DB2 Web Query InfoAssist tool was introduced in V1.1.2 and provides a rich and intuitive interface for developing reports, graphs, dashboards, and documents. In V2.1, InfoAssist is the only development tool available. This means that the Report Assistant, Graph Assistant, and Power Painter tools have been retired and are no longer available in V2.1. While this may be sad news for some of you sentimental folks, you can take comfort in knowing that all of the features of the retired tools either have been or will be incorporated into InfoAssist. This has two distinct advantages:

1. One tool for DB2 Web Query developers to learn
2. One tool for the IBM and Information Builders development teams to enhance and maintain

If you haven't tried InfoAssist, you're missing out! The interface is much more graphical and intuitive than the interface in the retired tools. The context-sensitive ribbon, drag-and-drop capability, and interactive design view all make for an enjoyable report-development experience. An example report created in InfoAssist is shown in Figure 3.

Figure 3: InfoAssist in action!

Web-Based Synonym Editor

In versions 1.1.x, you could create metadata (also referred to as synonyms) using a console available in the Web browser. However, if you wanted to edit or enhance the synonym, you were required to purchase a copy of the DB2 Web Query Developer Workbench tool in order to make the necessary modifications. This requirement has been lifted for basic metadata editing capabilities in V2.1. There is now an interface from the BI Portal that allows an administrator (or member of the DBA permission group set) to edit the metadata.

Now synonym updates such as creating Define/Compute fields, defining filters, and setting column formatting can be done without the need for a Developer Workbench license. However, more advanced synonym features such as DBA, Business Views, and Dimension Builder still require the Synonym Editor in Developer Workbench.

An example of the Web-based edit metadata capability is shown in Figure 4.

Figure 4: Web-based synonym editor allows for easy editing or enhancing.

Refresh Web Query Synonym

The ability to refresh a Web Query synonym has been in the product from the beginning and is used to keep the synonym in sync with the format of the underlying data source object. However, this feature was available only in Developer Workbench and the refresh had to be done in one-by-one and interactive fashion. This meant that you had to know about the underlying table change, log into Developer Workbench, and request a refresh over that synonym. But what if you didn't know about the change? And what if there were dozens, if not hundreds, of files whose format changed and synonyms needed to be refreshed? These types of scenarios resulted in a substantial amount of effort.

Starting in 1.1.2 Group PTF level 5, a refresh feature was added to the CRTWQSYN CL command. This new features means you can now keep your DB2 Web Query synonyms in sync with files in a "semi-automated" fashion. By adding an entry to the job scheduler that invokes this command on a daily basis, your synonyms can be kept up to date with little or no effort on your part. If you need to know what was changed, simply reference the generated spooled file that contains a log of the changes.

In the following example, existing synonyms (in folder CENTURY_ELECTRONICS with prefix MYPREFIX_) are refreshed for all files that start with the characters "ORD" in the library QWQCENT.

CRTWQSYN FILE(ORD*) SCHEMA(QWQCENT) PREFIX(MYPREFIX_) APPFLR(CENTURY_ELECTRONICS) OPTION(*REFRESH)

Business Intelligence Portal

The Business Intelligence Portal (BIP) is the new interface that Web Query end users are sure to enjoy. It unleashes a feature that many customers have been asking for: the ability for end users (report runners/consumers) to organize and customize their reporting environment. The following new features help end users accomplish this:

• Favorites—A folder that contains only the user's favorite reports. Any accessible report, chart, document, or dashboard can be added to this folder from the right-click menu as shown in Figure 5.
  

             Figure 5: Favorites contains the user's favorite reports.

• Pages—Ability for report runners (consumers) to create their own private, customized pages by dragging and dropping predefined reports and charts onto the page and resizing the panels. The result is one or more pages with a custom dashboard that the end user creates. Prior to V2.1, only report developers had the ability to create dashboards. Now all end users have this power at their fingertips. Figure 6 shows an example of this new feature.

           Figure 6: Create your own dashboards!

Improved Mobility Support with "Mobile Favorites"

For those users on the go who want to access their reports via their iPad, iPhone, Android, or other mobile device, the new Mobility support is certain to please. Mobile Favorites has an innovative, device-agnostic, browser-based architecture that enables organizations to share reports across devices.

The DB2 Web Query mobile strategy is Develop Once, Run Anywhere! This is important in today's business climate, where many employees are using their own personal devices to access their company's business information. Because of this, the notion of fully standardizing the interface to this information to a single device borders on the impractical. The ability to develop mobile content and interfaces once and have that capability supported on many different devices can result in signifcant savings for many companies. Figure 7 demonstrates DB2 Web Query's ability to provide business-critical information using a variety of personal devices.

Figure 7: Go mobile with DB2 Web Query!

DB2 Web Query mobility support provides a variety of ways for you to access this content from your mobile device:

• Email (attachments)—Open/launch attachments based on output format (HTML, active reports, PDF, and spreadsheets) or open reports in Mobile Favorites application (Apple iPad or iPhone only)
• Mobile Favorites URL—Provides interactive reporting capability. Open the following URL from your device:
  http://your_host_name:12331/webquery/mobile
• Mobile Faves App (Apple iPad or iPhone only)—Open from email. Provides Mobile Favorites interaction for live reporting. Eliminates need to enter the Mobile Favorites URL and user credentials.

More to Come…

The features discussed in this article are the major ones added to V2.1, but rest assured there are many more. For a more complete list and description of all the goodies and benefits of the new version, see the wiki in our developerWorks Web site.

If you have questions on using the product, make sure you check out our DB2 Web Query forum under the link above. To make it easier to access these sites, direct links have been added to the new DB2 Web Query login page as in Figure 8.

Figure 8: The new login page has helpful links.

For those of you who haven't heard of, seen, or previously ordered DB2 Web Query and are wondering how to get from Query/400 to this great technology, keep this in mind: you can get the DB2 Web Query Express Edition at no charge if you have the Query/400 product (5722QU1) and have an IBM i software maintenance contract. You can get details by clicking on the "Frequently Asked Questions" link on our DB2 Web Query home page.

The DB2 Web Query Redbook is currently being updated with V2.1 content and will be available for download later this summer. Check the DB2 Web Query developerWorks wiki or the IBM Redbook Web site periodically for updates on this.

And finally, if you need help with a query or database modernization strategy, IBM has skilled resources that can help. If you recently purchased a new system, you may even be eligible for a services voucher that would entitle you to one or more days of free onsite services. This is a tremendous offering that a surprisingly small percentage of our customers actually take advantage of (or even know about for that matter). You can get more details here.