DB2 Partitioned Indices Order Preservation and Performance

GTECH is in the lottery and gaming business. They use DB2 as a competitive advantage in their offering. They are an IBM SW Reseller. They are always looking at new ways to leverage DB2, DB2 Blu, DB2 Merge/Backup,IBM Replication IIDR, to gain competitive advantage. Customer Satisfaction with DB2 is critical for continued adoption, and promotion. Large GTECH customers are the State Governments and they require efficiencies in operations.

Competitive Data (What are our competitors doing in this area?): Oracle and other DBMS offerings are always
an alternative to DB2.

Current workarounds: The current workaround suggested by L3 Support was to introduce redundant indices, adding partitioned indices to non-partitioned indices. This degraded query performance and was not usable.

Customer impact if not delivered: In short-term, customer will continue to incur operations cost of excessive time to perform detach operation. Customer satisfaction with DB2 will decrease. Ultimately, customer will look at other alternatives.
Background (History and current state): GTECH is an important IBM ASL Program, Reseller of IBM solutions. They are looking to adopt any/all IBM solutions that can be cost effectively embedded into their software/hw offerings to the state lotteries, and customers worldwide. Increasing their strengthening relationship with IBM is critical for future success, and short-term sales of IBM Inf.Management software. There are current opportunities in 2014 to sell GTECH $500K-$2M worth of software. These could be negatively impacted by our response to their request. Lets review request with them carefully and perform adequate due diliigence. Thank you!.

Customer currently has two (2) objectives:
1) Maintain or improve current SQL query performance of queries that contain min(), max(), count() functions AND

2) Improve Detach performance which is currently excessive.
Existing customer implementation uses non-partitioned (global) indices. Customer is trying to reduce the time it takes to detach. Time currently exceeds 30 minutes, to detach.

Customer says process is excessive. They must first delete global (non-partitioned) indices, detach, and later re-create.
As a potential solution to improve detach performance, IBM L3 Support suggested adding partitioned indices to the same partitioned columns (which already have non-partitioned indices). After adding these redundant indices, the customer's SQL query performance significantly degraded.

Customer Contact is "Jarocki, Janusz" - Janusz.Jarocki@gtech.com

Originally entered into DB2 Enhancement Request System as R2PRO00009341

IBM DB2 Owner = Carmen Wong - ckmwong@ca.ibm.com|Carmen Wong

Urgency= High - Needs to be in next release