Hi all !

In DB2, I want to create primary key on partition table. How can I do it?

Example:

Create table TEMP1:

CREATE TABLE TEMP1 (
ID INTEGER NOT NULL,
DATEREPORT VARCHAR(8) NOT NULL,
NUMBERREPORT VARCHAR(20),
TITLE VARCHAR,
YEAR INTEGER,
IDCOM INTEGER,
IDTEN INTEGER DEFAULT 0 NOT NULL
)DISTRIBUTE BY HASH (IDTEN, YEAR);

After that, I want to create primary key on ID column but not yet because error:

SQL0270N Function not supported Reason code = "1")'

The command to create PRIMARY KEY:

ALTER TABLE TEMP1 ADD CONSTRAINT PK_TEMP1
PRIMARY KEY(ID);

How can I do?

Thanks !

SQL0270N Function not supported (Reason code = "<reason-code>").

Explanation:

The statement cannot be processed because it violates a restriction as
indicated by the following reason code:

1

The primary key, each unique constraint, and each unique index
must contain all distribution columns of the table (columns may
appear in any order).

Are you trying to use the Table Partitioning feature, or using DPF (Data Partitioning Feature, aka InfoSphere Warehouse)?

When using DPF (DISTRIBUTE BY HASH), you almost always want to use a non-meaningful column to get an even distribution of data across the nodes. Unless you are just playing around with a Try-and-Buy version, it is a little disconcerting that someone would purchase InfoSphere Warehouse (or DPF add-on for an older release of DB2) without knowing this. I would strongly suggest that your company hire a consultant to get you started in the right direction, or else you may end with a gigantic mess.

if help needed - always available..