Topic
1 reply Latest Post - ‏2012-08-14T14:58:45Z by Ivan_CA
SystemAdmin
SystemAdmin
1632 Posts
ACCEPTED ANSWER

Pinned topic Data Loading Failed

‏2012-08-13T22:02:47Z |
I was trying to load data from a CSV file into a table in the db2 database, data loading fails. This table has a generated always as identity column, so I am unable to figure out the way to import.
Updated on 2012-08-14T14:58:45Z at 2012-08-14T14:58:45Z by Ivan_CA
  • Ivan_CA
    Ivan_CA
    52 Posts
    ACCEPTED ANSWER

    Re: Data Loading Failed

    ‏2012-08-14T14:58:45Z  in response to SystemAdmin
    Hi,

    It depends on how the file looks like and how you want to handle the identify values. One way of importing is to use the column modifier "ignore input values and create new values":

    Example:

    create table tab1(c1 int generated by default as identity,
    c2 varchar(20),
    c3 decimal(7,2),
    c4 char(1));

    alter table tab1 add primary key (c1);

    Let's say my input file looks like the file below, but I already have values 1, 2, 3 and 4 for column c1 in my target table, so I want to ignore these values from the file and generate new ones.

    1,"TEST",+00099.99,"Y"
    2,"TEST",+00099.99,"Y"
    3,"TEST",+00099.99,"Y"
    4,"TEST",+00099.99,"Y"

    When you do the import in Data Studio (Administration Explorer - Tables - tab1 - Load - With Import Utility), after selecting the input file, go to the "Column" tab and select the "Identity column behavior" as "Ignore input file values and create new values". This selection will generate the import statement below, where the 4 rows from the file will be imported with new generated values for column c1.

    CONNECT TO DB1;
    CALL SYSPROC.ADMIN_CMD( 'IMPORT FROM "/tmp/tab1.del" OF DEL MODIFIED BY identityignore MESSAGES ON SERVER INSERT INTO DB2I10F2.TAB1' );
    CONNECT RESET;
    Thanks,
    Ivan