Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

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
0
juliakir
Asked:
juliakir
  • 6
  • 5
1 Solution
 
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
 
juliakirAuthor Commented:
thanks momi .. will it work for unique as well ?
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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
 
momi_sabagCommented:
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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now