Jun 1 ’11

DB2 for z/OS Search and Scroll Techniques

by Daniel L. Luksetich in z/Journal

Most application performance problems involve searching and scrolling. Once you find what you’re looking for, you generally can process it quickly. However, with increased use of automated interfaces, frameworks, and generic coding techniques and tools, this problem seems magnified. While changing programming techniques and customizing code is time-consuming, it’s necessary if you want to save processing time and costs over the life of an application. Besides, you typically apply custom solutions as the exception, not the rule, when building an application.

The techniques presented here can help you solve performance issues when using DB2’s searching and scrolling capabilities.

DB2 Built-In Support for Search and Scroll

DB2 has some built-in support for improving searching and scrolling; some features have been around awhile, but others are relatively new:

Most performance improvements don’t come from these features, but from the proper coding of predicates or by leveraging the flexibility of SQL to obtain the desired performance.

Scrollable Cursors

Scrollable cursors let you move forward and backward through a result set using relative positioning. They can be static scrolling, which places results in a declared temporary table, or dynamic scrolling, which operates against a base table. They can also be sensitive, meaning  changes to data during scrolling are visible, or insensitive, meaning changes to data during scrolling aren’t visible to the cursor. Scrollable cursors let you position anywhere in the result set and move backward and forward in a result set.

Multi-Row Fetch

Multi-row fetch is a fantastic performance enhancement that should be a part of any design that fetches more than a handful of rows per cursor. For random access, multi-row fetch can be a great CPU saver. In some cases it has reduced our CPU utilization by as much as 25 percent for random cursors fetching around 20 rows. For sequential batch cursors, multi-row fetch has saved us as much as 60 percent CPU utilization, with a similar percentage savings in elapsed time processing for large result sets. You can fetch up to 32,768 rows in a multi-row fetch operation. However, IBM recommends about 100 rows for optimum performance vs. thread storage consumption. We usually recommend a maximum of 50 rows, or if you know how many rows your cursor will return, you can specify slightly more than that or 50, whichever is lower. You can get multi-row fetch automatically for read-only remote cursors, but for traditional batch and CICS programs, you must code it yourself.

Backward Index Scan

Backward index scan is possible with DB2 backward scrolling cursors if the ORDER BY clause in the query requests an order in the exact opposite sequence of key columns in the index. This means that one index can be used to avoid sorting for both forward and backward scrolling cursors. While the actual scan process may be no more efficient than having both a forward and backward index, it can create an opportunity to eliminate that extra index that supports the backward scroll. This is important in the overall performance scheme because indexes require time and money to maintain. The fewer indexes there are to maintain, the more efficient overall performance can be achieved.

Soundex Function

The soundex function was introduced with DB2 9 and provides built-in name search assistance. The soundex algorithm assists with the filing and searching of information based on a name. It converts a name into a four-character code. The first character is the first letter of the name. The remaining four characters are all numeric based on the remainder of the name. Vowels are usually ignored, as are repetitive consonants, but there are exceptions.

We can use the soundex algorithm for more complete searches when names are misspelled. In addition, the soundex value can be stored in the database and an index created on that stored column, or an index can be created on the soundex function itself. This will enable index matching access for executions of the soundex function.

Index on Expression

Another important feature built into DB2 9 enables you to create an index key based on an expression. This feature lets you compensate for predicates built on column expressions—normally stage-two predicates that can’t use an index. This is potentially a significant performance improvement for existing predicates and future designs. In the CREATE INDEX statement, an index is actually built on the result of a function, such as the soundex function, for every row in the table. So, the first key value of the index is the soundex result. A query can then use the soundex function as it is coded in the index of the table to search for someone with a specific soundex value. Now the predicate can use an index.

Hash Access

Hash access can be used to optimize data access to tables for commonly issued equals predicates that access a single row in a table. The hash access supports direct access to a row for these types of retrievals without using an index. Hash access can also be used to force uniqueness within a table without an index. Hash access for a table is enabled by specifying the ORGANIZED BY HASH clause of the CREATE TABLE or ALTER TABLE STATEMENT. This clause specifies column names be used as part of the hash key, and the hash key is used to determine the physical location of rows in the table. Hash access can provide a dramatic performance improvement for simple equals predicates on such things as primary keys. Avoiding a traverse of the index tree can reduce both elapsed time and CPU time for these types of searches. Be aware, however, that the hash space can as much as double the storage requirements for a table, and doesn’t necessarily guarantee you can eliminate an index.

Temporal Search

In DB2 10, system-period temporal tables automate the storage of current and history data in separate tables. With this built-in temporal design, you build a current table and a history table, then tell DB2 that the two tables are related in that manner. DB2 then moves data from the current table to the history table whenever there’s a change to data in the current table.

DB2 also automatically supports querying data based on a timestamp. You can choose how to read the temporally related tables; you can read the current table for the current data and the history table for all historical queries. If you want to read data “as of” a given time, or time range, you can query the current table and specify a system time predicate in the query. DB2 will return the data from both the current and history tables relative to the time or period you requested. It does this by rewriting the query as a UNION ALL of a query against the current table and a query against the history table. While this approach may not be a high-performance solution, it relieves the application and DBA from a lot of extra work.

Improved In-List Matching

One of the optimization techniques available in DB2 10 is improved matching index access for multiple in-list predicates in a single query. In previous versions, if multiple in-lists were coded in a query, only one of them would be eligible for index matching. Now DB2 can build work files using the in-lists and then use those work files in a join to the target table.

Improved Pagination Optimization

DB2 10 also introduces range-list index scan. Search queries, especially those that use scrolling techniques to control pagination, can contain multiple OR predicates. In previous versions of DB2, multiple index access was used to avoid a non-matching index scan or table space scan. In DB2 10, a range-list index scan can be used; it will consume fewer RID list resources than multi-index access. DB2 can use a range-list index scan when a query meets these requirements:

Search Query Variations

SQL advancements provide many choices for doing things such as complex searches. If we must search for multiple conditions or values, we can choose from several options.

The sample queries in Figure 1 search for two different names using three different techniques. The first query uses a compound predicate that might get multi-index access at best under DB2 9, or perhaps range-list index access under DB2 10. The second query uses a UNION ALL to perform the search for each search condition, which has a better chance of two-column index matching access, but executes two query blocks. The third query builds a search table using a common table expression and then uses relational division (a join) to perform the complex search. Each of these queries has merit; it’s best to code them all, EXPLAIN, and benchmark test each of them.


Searching on Multiple Ranges

Figure 2 shows a typical generic search where a user sees a screen with multiple search criteria they can enter. Often, this criterion is in the form of upper and lower values for a range search.

If there was an index on the three columns referenced in the WHERE clause of this query, then in the case of range predicates, DB2 can only match on the first indexed column. At best, it can do index screening on the other columns. The lower number of matching columns can result in more of the index being accessed, especially for large tables with many rows of data.

Often, the domain of the columns being searched is finite and possibly quite small. This creates the opportunity to use code tables that contain the entire domain of search values and then perform the more expensive range searches on these small tables. This accommodates an index match on more columns of the target table. This technique can be effective and efficient, especially for large tables and several range predicates.

The first query in Figure 3 leverages a non-correlated subquery and some code tables to generate a set of values to power the search. The code tables are accessed first in this query and then three-column matching index access is attained for the table being searched. So, we apply the range predicates to small code tables rather than the big table. The less efficient index (or table) access is against a small table instead of a big table. There’s a Cartesian join between the code tables to provide all value combinations to the outer portion of the query.

In the second query in Figure 3, the same query range predicates are applied to our code tables in separate, non-correlated subqueries. This avoids the potential for a large work file due to the join in the previous example, but introduces an additional query block. The access path again has three-column matching index access for our search in the outer portion of the statement, provided DB2 hasn’t rewritten the query.

In the third query in Figure 3, a common table expression is used with a Cartesian join using our range predicates to build a set of values for the search. The table produced is then joined to the table being searched. This can avoid the large work file creation and a sort of the search values. Again, the code tables are accessed first in the access path and we get three-column matching index access.


Early Out

Multi-table generic searches can be expensive because the optimizer has to pick only one path it thinks is the best access path. So, if the input is variable and optional, then the query may perform poorly if the values entered for predicates aren’t against the first table accessed in the join sequence. Coding generic searches is easy and they provide flexible search screens. However, the performance of these queries is generic at best. Figure 4 shows a generic search on an employee number, department name, or project number.

There are many solutions to the generic search problem. Some include building the query on the fly and providing dynamic literal values to the input. Such a solution could take advantage of DB2 distribution statistics. DB2 run-time optimization is also a possibility and will give DB2 the opportunity to choose the access path at run-time, but the incremental bind during execution can add more CPU time than it saves.

There’s a simple solution to the generic search if you know a little bit about how your users enter their search queries. Often, users know the common search criteria they enter. By simply coding two queries and a little programming logic, you can dramatically improve overall performance of the generic search. A study of user activity showed that users usually received an employee number for input, and were rarely given a different search value. With this knowledge, the application can be built so a search on only the employee table happens first (see the early-out query in Figure 4).

If the employee is then found, the search is over. If the employee isn’t found, then the normal generic search executes. The generic query is then executed only if the early-out query produces no result. If two queries and application logic aren’t desired, then you could potentially put your early-out logic directly in a single query. The combined query in Figure 5 behaves just like the two queries and application logic in the early-out design combined. It does this by taking advantage of a DB2 during join predicate. DB2 join predicates are evaluated during the join operation, and the join is only performed if the join condition is true. If the join predicate is false, the join doesn’t happen. Here, if the employee number is found in the first join for  just the employee table, then the second table expression doesn’t run (i.e., if A.EMPNO has a value, then the join doesn’t happen).

Figure 6 also has early-out logic built into the query via a COALESCE function. The COALESCE function returns the first non-null value in a list of two or more values. Those values can actually be expressions, and those expressions can be SQL statements. The drawback is that the COALESCE function can only return a scalar result. However, this technique can be effective for complex search operations with variable input, or for search input against several tables such as active vs. inactive tables.


Positioning and Restart

Often, when cursors are used for scrolling or restart, the queries are coded with compound predicates that facilitate the scrolling based on multiple columns. Unfortunately, these types of compound predicates typically lack Boolean term predicates, and DB2 needs Boolean term predicates to efficiently use indexes for queries. If your query lacks Boolean term predicates, then the best index access you’ll get (as of DB2 9) is multi-index access or non-matching index access. This can cause a lot of anxiety as you wait for what appears to be a simple scrolling cursor to scan through an entire index before returning the few rows you require to fill a screen. So, it’s important to code Boolean term predicates. You can do this by maintaining separate search, scroll, and restart queries. You can also add redundant Boolean term predicates to a generic scrolling cursor to improve index access.

A typical scrolling cursor (see query 1 in Figure 7) reads data in a batch program for a range on a combination of leading key column values. Due to a lack of Boolean term predicates, it can do non-matching index access at best. Query 2 in Figure 7 is the same query as the typical scrolling cursor, but the predicates were changed so they’re Boolean term. The result is a single column match on the first key column.


Query 3 in Figure 7 is the same query, but combines the search positioning/restart with the technique of building keys from code tables. Here, a code table that contains all values for SEG and SUBSEG is accessed in a non-correlated subquery, where the range of values desired is applied. The result of the non-correlated sub-query is then evaluated in a row expression to get two matching index column access. This works only if the code table you build is relatively small. The code table in this example contained 200 rows of data while the main table had about 500 million rows.

DB2 10 may offer relief for these situations automatically with the introduction of range-list access.

Scrolling Multi-Table Queries

A major issue with search and scroll is when it involves complex queries that access several tables that can return large quantities of data. You want to minimize the amount of work the database is doing, and often DB2 can do this for you. DB2 will do its best to avoid sorting or materializing the result so you only return the data needed to fill a screen. However, if DB2 must materialize the result, then the query could process a lot of data only to have the application throw most of it away when it gets control. When this happens, you should try to write a query that eliminates the materialization, but this isn’t always possible. If materialization is unavoidable, you may be better off materializing the smallest amount of data in one query, then reverting to the database for the rest of the data later.

Query 1 in Figure 8 is a simple example for what’s actually a problem with extremely large, complex queries used to fill screens with data. If the ORDER BY results in a sort, then the query can be quite expensive to operate for search and scroll. If we can’t avoid materialization, perhaps we can minimize it. The applications will read only the data required to avoid or reduce the materialization (with the appropriate supporting index), such as query 2 in Figure 8. Subsequent queries can collect related data using IN-List or programmatic join. An application can also store the required sort information in an array, and only get screen data that was needed as the user paged through the screens. Remember, in these situations, we’re expecting the screen or user to read only a limited amount of the data that qualifies.



The goal of tuning search and scroll processes should be to balance the amount of work involved in coding these processes with the performance of the queries and program process. It’s always good to reduce the number of indexes in the database. Don’t just try throwing an index at a performance issue. Instead, see if there’s an alternative way to code the SQL statement that may yield a performance improvement. Many DB2 features and SQL coding techniques offer solutions. The SQL techniques presented here show alternative paths to the data. Consider testing these, including EXPLAIN and benchmark tests, to determine the best opportunity for your specific performance situation.