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
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
try to create a unique index with the not partitioned clause
ASKER
thanks momi .. will it work for unique as well ?
ASKER
is NOT PARTITIONED clause works in 9.1 ?
rrjegan17 your solution will not work
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
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
ASKER
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
create index schema.julia_test on schema.julia_test(username
in TS_NOTPART;
create unique index schema.julia_test on schema.julia_test(username
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)
schema.julia_test is a partitioned table right?
you are using data partitioning, not database partitioning (DPF)
ASKER
yes schema.julia_ partitioning table in DPF
distributed by ("ID")
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
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
ASKER
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
so in dpf env not possible to have standalone unique column , unless it is part of distribution key
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
thank you for quick responses
CREATE unique INDEX index_name ON ur_table ( index_key )include ( parition_key);
Depending upon the version, adding INCLUDE clause would do..