IC5Notice: We have upgraded developerWorks Community to the latest version of IBM Connections. For more information, read our upgrade FAQ.
Topic
  • 1 reply
  • Latest Post - ‏2012-08-14T14:58:45Z by Ivan_CA
SystemAdmin
SystemAdmin
1632 Posts

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

    Re: Data Loading Failed

    ‏2012-08-14T14:58:45Z  
    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