Troubleshooting Distributed Sorting

There are situations that can cause data integrity problems when using IBM Cognos products to read and merge data after using distributed sorting.

The situations can occur only when all the following conditions are met:

• A single logical SQL query is sufficiently complex to be decomposed into two or more physical database queries, which are subsequently merged together after distributed sorting. An example of a complex logical query is a query that has groupings with subtotals containing extended aggregates, such as moving averages or rolling subtotals.
• One or more physical queries is sorted by the database, and one or more physical queries is sorted by another database, or by the operating system on the computer running the IBM Cognos product. Alternatively, two or more physical queries are sorted by the same database, then, the data results are merged based on the sorting order of the operating system on the computer running the IBM Cognos product. This process is called distributed sorting and merging; it is not unique to IBM Cognos products.
• The sorting orders are different.

You will not have the problem if the data is sorted in the same order by all your databases and by your operating system. Even if the data is not sorted in the same order, you may not be currently experiencing the problem.

To help you determine if you currently, or may in the future, experience these problems, IBM Cognos is providing you with the following:

• a Distributed Sorting Validation Kit
• new product features to eliminate the potential problems in IBM Cognos products

Distributed Sorting Validation Kit

You can download the Distributed Sorting Validation Kit from the Cognos Software Services Web site at http://support.cognos.com/distributedsort The kit includes the following:

• scripts

You can run scripts to check the sorting order of your databases and operating systems. These scripts load data into your databases and use the collating sequences of your databases to sort the data. They also use the collating sequences of your operating systems to sort the same data.

• utility

A utility to check the sorting order of the operating system on which your IBM Cognos product is installed.

• the Troubleshooting Distributed Sorting book

This book tells you how to run the scripts and utility and how to determine the results of the validation process. It also tells you how to modify the way your IBM Cognos product sorts data.

New Product Features

To eliminate the risk of experiencing the problems, we have enhanced our products in the following ways:

• Enable or disable distributed sorting.

On a database connection, you can enable or disable distributed sorting. You can disable distributed sorting if the results of the operating system and database sorting validation process aren’t the same, or if your databases don’t sort in the same order. If neither of these situations exists, you can enable distributed sorting.

Note: When an IBM Cognos product that is multibyte-language-enabled accesses a database it automatically adjusts its default method of distributed sorting for specific databases. This behavior prevents the most common causes of the potential problem.

• Specify a special collating sequence.

On a database connection you can specify special options when distributed sorting is not compatible by default. IBM Cognos provides special collating sequences, such as those for EBCDIC and Microsoft Access language variations, to substitute for the collating sequence of your operating system. You can use these special collating sequences to continue to use distributed sorting.

For more information about these new features, see the Troubleshooting Distributed Sorting book, available with the Distributed Sorting Validation Kit.

nbna