Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2004
    Posts
    268

    Unanswered: Identity Column Insert Problem

    RHEL 5.8 - DB2 V9.5 FP8.

    I am importing data from one database into another database via db2look/db2move. I have 1 table that is failing with the load due to the identity column (ID). So, I am trying to export the data from the table with below command,

    db2 "export to USERS.del of del modified by chardel'' coldel; decpt, select * from CRM.USERS"

    then I am importing with below command,

    db2 "import from USERS.del of del modified by chardel'' coldel; decpt, identityignore replace into CRM.USERS"

    and it loads fine except the identity values are not the same. In the original table, it starts with 1 increases with 1 until 834. The table has only 725 rows (Obiously some rows are deleted).

    When I load it into the destination table, it starts with 780 and ends with 1504. Is there a way to get the same identity values from the source table into the destination table on the other database ?

    Is it possible to load the table without the identity column and add identity property later or the identity has to be specified when creating the table ?

    Is there any other way ?

    Thanks.

    Here is how the table is created,

    CREATE TABLE "CRM "."USERS" (
    "ID" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (
    START WITH +1
    INCREMENT BY +1
    MINVALUE +1
    MAXVALUE +9223372036854775807
    NO CYCLE
    CACHE 20
    NO ORDER )
    ,
    ,
    ,
    ,
    ,
    ,

  2. #2
    Join Date
    Sep 2011
    Posts
    16
    Hi

    Yes it is possible to load the table without the identity column and add identity property later.

    You just drop the identity attribute.

    db2 alter table <table_name> alter column <column_name> drop identity

    and import your data and again add identity.

    db2 alter table <table_name> alter column <column_name> set generated always as identity (start with <max(column_identity_name)> +1)


    Ashish

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by mdx34 View Post

    and it loads fine except the identity values are not the same.
    ,
    What else did you expect when you specified the identityignore option?

    You'll need to define the identity on the target table as GENERATED BY DEFAULT and not use identityignore during the import.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Oct 2004
    Posts
    268
    Quote Originally Posted by n_i View Post
    What else did you expect when you specified the identityignore option?

    You'll need to define the identity on the target table as GENERATED BY DEFAULT and not use identityignore during the import.
    I expected to load the data the way it was and not to mess with the order. What would one expect from the word "ignore".

    I used all kinds of import and did not work. Something was always different.

    What worked was what Ashish posted. Thanks for that.................

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    You could have tried LOAD with identityoverride

  6. #6
    Join Date
    Oct 2004
    Posts
    268
    Quote Originally Posted by db2girl View Post
    You could have tried LOAD with identityoverride
    Thank you..........I did but I think it is depreciated in DB2 V9.5.....

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by mdx34 View Post
    Thank you..........I did but I think it is depreciated in DB2 V9.5.....
    I don't think so.

  8. #8
    Join Date
    Oct 2004
    Posts
    268
    Quote Originally Posted by db2girl View Post
    I don't think so.
    Sorry. My bet.......I had a syntax error........Now I am having problem with new inserted identity value being on the top instead of buttom when I do a Select * from the table. I have the last value at 841 and I ran

    db2 "ALTER TABLE CRM.USERS ALTER COLUMN id RESTART WITH 842"

    then I ran a single row insert statement. When I run Select * from CRM.USERS

    I get the 842 value displayed above value 1. I reorg, runstats e.t.c but same result.
    ID column also the primary key. If I run the single statement on the original database, it puts it at the end as expected. What am I missing ?


  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by mdx34 View Post
    being on the top instead of buttom
    What's wrong with this position?


    Add ORDER BY.

  10. #10
    Join Date
    Oct 2004
    Posts
    268
    Quote Originally Posted by db2girl View Post
    What's wrong with this position?


    Add ORDER BY.
    Nothing but is there an explanation ?. This will be a production database and I don't want to mess up anything.

    Thanks.

  11. #11
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    If you do not specify an ORDER BY, you cannot expect the rows returned to be in any particular order . . .

Posting Permissions

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