Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    10

    Red face Unanswered: DB2: Create primary key on partition table?

    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 !

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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).

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if help needed - always available..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •