Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Using DPSI indexes in Production

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
John Alexander1

New User


Joined: 22 Sep 2009
Posts: 7
Location: Sydney Australia

PostPosted: Thu Oct 22, 2009 5:28 am    Post subject: Using DPSI indexes in Production
Reply with quote

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.
Back to top
View user's profile Send private message

Steve Davies

New User


Joined: 15 Oct 2009
Posts: 32
Location: UK

PostPosted: Thu Oct 22, 2009 5:43 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
John Alexander1

New User


Joined: 22 Sep 2009
Posts: 7
Location: Sydney Australia

PostPosted: Fri Oct 23, 2009 5:29 am    Post subject:
Reply with quote

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) ?
Back to top
View user's profile Send private message
Steve Davies

New User


Joined: 15 Oct 2009
Posts: 32
Location: UK

PostPosted: Fri Oct 23, 2009 6:51 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
John Alexander1

New User


Joined: 22 Sep 2009
Posts: 7
Location: Sydney Australia

PostPosted: Fri Oct 23, 2009 8:06 am    Post subject:
Reply with quote

Thanks.

I guess we will not know for sure how DPSIs will perform in our shop until we start using them !
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts HALDB data refresh/copy from producti... vineetanand2007 IMS DB/DC 0 Mon Jan 02, 2017 11:16 am
No new posts Two different PDS production and test... Mani453 CLIST & REXX 6 Fri Nov 20, 2015 2:25 pm
No new posts DB2 XML indexes (V10) ozburt DB2 2 Tue Jan 07, 2014 3:39 pm
No new posts What to study to move from security a... Khalid Rehman All Other Mainframe Topics 3 Mon Sep 23, 2013 7:05 pm
No new posts LISTCAT on production VSAM file mohitsaini JCL & VSAM 2 Fri Sep 20, 2013 12:21 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us