DB2 table parttioning and unique constrain

Hi Eperts,
In DB2 if you need to create unique constrain on parttioned table you need to add partitioned key to the index . By doing so we can loose uniqueness of the column ..
Am I missing someting ?

thank you
Julia
juliakirAsked:
Who is Participating?
 
momi_sabagConnect With a Mentor Commented:
that also makes sense, since you don't want to have to check every partition each time you insert / update a record in order to check if it is unique,
if one partition is unavailable, the entire table will become unavailable
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Try creating it like this to maintain uniqueness:

CREATE unique INDEX index_name ON ur_table ( index_key )include ( parition_key);

Depending upon the version, adding INCLUDE clause would do..
0
 
momi_sabagCommented:
try to create a unique index with the not partitioned clause
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
juliakirAuthor Commented:
thanks momi .. will it work for unique as well ?
0
 
juliakirAuthor Commented:
is NOT PARTITIONED clause works in 9.1 ?

rrjegan17 your solution will not work
0
 
momi_sabagCommented:
not partitioned works in 9.1
i'm not sure it will work with unique, i never tried it, but the documentation does not say it should not work, so the best thing will be to try
0
 
juliakirAuthor Commented:
tried .. and eventhough doc for 9.1 says 'Not partitioned' supported . I wasn't able to create not partitioned index in 9.1 (unique and non-unique)

create  index schema.julia_test on schema.julia_test(username) NOT PARTITIONED
in TS_NOTPART;

create  unique index schema.julia_test on schema.julia_test(username) NOT PARTITIONED
in TS_NOTPART;

Execution failed
DB2 Database Error: ERROR [42601] [IBM][DB2/LINUXX8664] SQL0109N  The "PARTITIONED" clause is not allowed.  SQLSTATE=42601
0
 
momi_sabagCommented:
just want to make sure
schema.julia_test is a partitioned table right?
you are using data partitioning, not database partitioning (DPF)
0
 
juliakirAuthor Commented:
yes schema.julia_ partitioning table in DPF

distributed by ("ID")
0
 
momi_sabagCommented:
this type of index is good for data partitioning
not database partitioning

when using database partitioning, the distribution key must be included in ervery unique index
http://bytes.com/topic/db2/answers/839636-primary-key-error-wrong-number-qualifiers
0
 
juliakirAuthor Commented:
yes this is correct .. this is why i posted question ..
so in dpf env not possible to have standalone unique column , unless it is part of distribution key

0
 
juliakirAuthor Commented:
yep .. also since dpf was designed for very big table .. chances are you need unique value across all partitions are very slim

thank you for quick responses
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.