Using DPSI indexes in Production

John Alexander1 · Posted: Thu Oct 22, 2009 5:28 am

Hi,
We are on DB2 V8 compatibility mode, and are looking at converting selected partitioned tables to table based partitioning after we implement NFM .
The general consensus is that DPSIs are good for utility performance but not so good for queries.
I would appreciate any feedback/experience good or bad that you have had with DPSIs indexes in your Prod environment.

Steve Davies · New User Joined: 15 Oct 2009 Posts: 32 Location: UK

John,

When we started to do partitioning under V8 we found that we were still getting contention, but we had only partitioned the tables, we were still using NPSI (non-partitioned secondary indexes). The contention we were getting was on the index, so when we changed them to DPSI indexes the contention went away.

We have found them good for performance and good to help eliminate contention. I havn't heard anything about them not being good for queries, we havn't found any performance problems with them.

Hope this helps.

John Alexander1 · Posted: Fri Oct 23, 2009 5:29 am

Many thanks for the reply.

Do you have any large (eg. > 100 million rows) well performing table based partitioned tables with a DPSI in Prod, that have both heavy batch & CICS processing (ie. > 100,000 transactions a day) ?

Steve Davies · New User Joined: 15 Oct 2009 Posts: 32 Location: UK

John,

In short, no. We have partitioned tables well in excess of 100m rows, but these do not have DPSI indexes, they just have the NPSI indexes. We find the performance of these tables are still very acceptable.

Sorry!
Steve.

John Alexander1 · Posted: Fri Oct 23, 2009 8:06 am

Thanks.

I guess we will not know for sure how DPSIs will perform in our shop until we start using them !