Link to home
Start Free TrialLog in
Avatar of juliakir
juliakir

asked on

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
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

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..
try to create a unique index with the not partitioned clause
Avatar of juliakir
juliakir

ASKER

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

rrjegan17 your solution will not work
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
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
just want to make sure
schema.julia_test is a partitioned table right?
you are using data partitioning, not database partitioning (DPF)
yes schema.julia_ partitioning table in DPF

distributed by ("ID")
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
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

ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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