Administrator > DB2

Making Queries

What the future holds for DB2 Web Query for System i

The IBM* DB2* Web Query for System i* was introduced in late 2007 as a modernized, Web-based version of the popular Query for i5/OS* (also known as Query/400). In the few short months since making V1R1 available to the market, IBM had shipped more than 5,000 licenses of the new DB2 Web Query product by the end of the year, and the tutorial-based “Getting Started With DB2 Web Query” Redbooks* publication has been downloaded more than 10,000 times.

While Query/400 remains heavily used by end users and IT report developers, calling it a modernized business-intelligence (BI) solution might be a bit of a stretch. Today’s requirements for BI go beyond what Query/400 can provide. In fact, a large majority of the customers I talk to say Query/400 is really being used as a means to get data from DB2 into some other analytical tool. This includes spreadsheets, tools that provide dashboard views of the data, or programs that let analysts slice and dice, drill down and roll up, and look at data across multiple dimensions (this latter notion is often called online analytical processing, or OLAP). Interestingly, market research has identified BI as one of the top-priority solution areas that small and mid-sized business CIOs will pursue over the next three years.

Over the past few years the DB2 for i5/OS development team has invested heavily in new query-processing technology that leverages many IBM-patented technologies. Much effort has also been put into the tools to assist in analyzing and improving query performance. While many of these enhancements, like parallel query processing and the unique (to DB2 for i5/OS) encoded vector indexes, have been around since V4 of i5/OS, the last couple of years have seen the delivery of a totally new query optimization engine in DB2 called SQL Query Engine (SQE).

Why is SQE so important? The answer lies in three key areas. First, SQE can deliver substantially better performance for many queries. Second, it can apply many more methods and strategies representing smarter ways to process queries. Most of these new methods and strategies aren’t available when the older, original query optimizer and engine (referred to as CQE, or “Classic” Query Engine) handle the queries. An example is the autonomic index advice and index creation available in V5R4. And third, new analysis tools for SQE are available to better understand query performance and provide guidance to improve it. An example is the SQL Plan Cache in SystemÊi Navigator in V5R4. Bottom line—it’s faster, smarter and easier to manage.

Unfortunately, tools such as Query/400 that don’t use standard SQL interfaces to DB2 can’t leverage all of the great enhancements in SQE. In early performance analysis with DB2 Web Query done in Rochester, Minn., our testing measured as much as 10 times better response times when comparing queries run with DB2 Web Query’s native adapter interface versus Query/400 (using the same query definition, database and hardware configuration).

So, what’s on the horizon for DB2 Web Query in 2008?

New User Licensing Options

First and foremost is the announcement of a new software-licensing scheme. One major difference from Query/400 is the change from an unlimited-user scenario (Query/400 was, and as of this writing continues to be, offered as an unlimited-user license) to a named-user licensing scheme on DB2 Web Query technology. With V1R1M0 that user had to be a named user defined to the product. Knowing this was a short-term solution, IBM has introduced a new feature called Run Time User Enablement that can, in many cases, significantly reduce the costs and management of user licenses.

Customers who own existing user licenses can purchase the Run Time User Enablement feature available later this month, which allows them to define a user license as either a named user as it is today, or as a group of run-time-only query users. It effectively creates a scenario in which you can define users as either report authors who can create and edit reports or as report consumers who can only execute reports. Two key factors with a run-time user are important to understand. First, with a single-user license, multiple users can execute queries concurrently, creating essentially an unlimited-user licensing scheme. Second, unlike Query/400, DB2 Web Query’s parameterized reporting capabilities and OLAP and Active Reports features mean many more capabilities are available to end users without editing the query definition. In other words, the need for end users to have editing capabilities is significantly reduced with DB2 Web Query compared with Query/400, which required almost all users to have full developer capabilities.

This provides a level of flexibility in how you organize your users. While a single-user license can support multiple, run-time concurrent users, each user license could be used to organize users into groups. For instance, perhaps you would want to leverage the notion of roles across departments, such as worker, supervisor and manager. You could then create reports in domains for all managers, all supervisors and all workers and put them in discreet domains. Then you’d only need to define three group profiles, one for each role.

The Run Time User Enablement feature should be very attractive to customers with a lot of users who’ll never need to alter query definitions. Remember, OLAP and Active Reports features let you pivot rows and columns, filter data, create calculated fields and much more without being a report author in the new licensing scheme.

Next page: >>