Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: primary key and import with insert_update

    DB2 v9.5/AIX 6.1
    I always thought that PKs are needed for referencial integrity/constraint only. I am new in this company and I find we are using import with insert_update option which updates existing records in the table with matching PKs. I was told we are using PKs only for this purpose.

    Is there any other way to do same task like import...insert_update without creating/using PKs on the table? Thanks
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Sep 2003
    Posts
    237
    Commands manual says

    INSERT_UPDATE
    Adds rows of imported data to the target table, or updates existing rows (of the target table) with matching primary keys.

    Looks like the import is looking for a primary key (or maybe an unique key) to match imported records to existing records. I don't think it will work without the keys.
    mota

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by dbamota
    Commands manual says

    INSERT_UPDATE
    Adds rows of imported data to the target table, or updates existing rows (of the target table) with matching primary keys.

    Looks like the import is looking for a primary key (or maybe an unique key) to match imported records to existing records. I don't think it will work without the keys.
    So this is the question: do I have to have primary keys for this or it's enough to have unique index on these cols?

    Thank you
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by MarkhamDBA
    do I have to have primary keys for this or it's enough to have unique index on these cols?
    Thank you
    Technically spoken DB2 can do without a Primary Key definition. Personally I do not accept tables without a PK definition.
    Unique key - Wikipedia, the free encyclopedia
    There is a catch with DB2: you can add the keywords "primary key" in the column defintion as part of the CREATE TABLE. DB2 will automaticly create an index for you. When you create you own index later (with unique/cluster/include parameters) you will have 2 almost-the-same indexes on that table causing only overhead and no gain what-so-ever.
    So what they taught me:
    - create table without the PK definition
    - create the unique index (can be more columns)
    - alter table to add the PK contraint
    Now DB2 will check if there is already an index which can be used and will not create its own index. If you own index cannot be used, DB2 will create an index with the same name as you contraint.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The basic point is that an index is not a constraint.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Dec 2008
    Posts
    76
    A unique index is not a declared constraint, although it acts as a defacto constraint.
    Last edited by rdutton; 09-11-09 at 10:36.
    RD

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That's a muddy area. An index (which is a concept for the physical layer of the database design) is just an access method. Personally, I find it really unfortunate to have something like an "unique index" where the index drags a constraint into the picture, which belongs to the conceptional database layer. My approach to avoid any misconceptions is therefore:
    - if you need an access method, define an index
    - if you need a unique constraint, define a primary key or a unique key
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    what ever dr te z said.

    To answer your original question is you DO NOT need UK nor PK to do the insert_update.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Cougar8000

    To answer your original question is you DO NOT need UK nor PK to do the insert_update.
    I'm afraid you are wrong. Check SQL3203N, for example.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    I am not afraid to be wrong thanks for pointing this out. I guess I better refresh my memory on it.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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