Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2012
    Posts
    4

    Unanswered: DPSI vs Unique NPSI

    Hi, 'we' are currently in the processig of designing a new partitioned tablespace. Data Partioned Secondary Indexes (DPSI) are favoured for utility use etc. However, in the version of DB2 in use, they can not currently be unique. The particular secondary index to be defined can be defined as a unique NPSI.

    I am just wondering if I would be better of defining a unique NPSI rather than a non-unique DPSI (time is of the essence and time not available to play around with different versions to see how well they perform). However, I do recall in a previous life, creating a DPSI (to replace a unique NPSI) and this definitely did perform much worse when tested.

    I was just wondering if any one else has any experience of defining either/both of these index types.

  2. #2
    Join Date
    Apr 2012
    Posts
    4
    I guess the previous thread 'partition index' answers the question! Thanks Marcus_A, don't know how I missed that discussion!!
    Last edited by Promptly; 04-07-12 at 15:57.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I think Marcus_A's answer is for DB2 LUW, not z/OS.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2girl View Post
    I think Marcus_A's answer is for DB2 LUW, not z/OS.
    That is correct. Whether it applies to z/OS I don't know.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Apr 2012
    Posts
    4

    DPSI and NSPI

    Thank you DB2_Girl and Marcus_A.

    I'm only familiar with DB2 Z/OS, but when reading the 'partition index' post, it seemed that 'global indexes' were akin to NPSI?

    I would appreciate any input on NPSI vs DPSI performance.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Promptly View Post
    Thank you DB2_Girl and Marcus_A.

    I'm only familiar with DB2 Z/OS, but when reading the 'partition index' post, it seemed that 'global indexes' were akin to NPSI?

    I would appreciate any input on NPSI vs DPSI performance.
    I believe that the considerations are similar in DB2 z/OS as I described in the thread about DB2 LUW partitioned indexes, although there are some differences.

    See if this helps, and if not, then crack open the PDF versions of the DB2 manuals and start browsing:
    http://documents.bmc.com/products/do...827/141827.pdf
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Apr 2012
    Posts
    4
    Marcus_A, thanks a million for that link! A nice succinct description of DB2 indexes!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •