Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    7

    Unanswered: Import failing please help

    My import file.

    39302~#1~#1~#F~#~#LBS~#10.00~#20.00
    66826~#DOCK1~#1~#F~#TL~#LBS-Ch~#10.00~#12.50
    66829~#DOCK1~#1~#F~#TL~#LBS-Ch~#10.00~#12.50
    66832~#DOCK1~#1~#F~#TL~#LBS-Ch~#10.00~#12.50
    66838~#DOCK1~#1~#F~#TL~#LBS-Ch~#10.00~#12.50
    66843~#DOCK1~#1~#F~#TL~#LBS-Ch~#10.00~#12.50
    66929~#DOCK1~#1~#F~#TL~#lbs~#10.00~#12.50
    66915~#1~#1~#F~#~#~#2.00~#2.00
    66917~#1~#1~#F~#~#~#3.00~#3.00
    65048~#2~#1~#F~#~#cube~#45.00~#17.00
    65991~#1~#1~#R~#~#LBS-Ch~#1.00~#10.00

    import command

    CONNECT TO SAMPLE;
    IMPORT FROM C:\PTP_DB2\PTP_DOCK_RATE.OUT OF DEL MODIFIED BY COLDEL~# DECPT. METHOD P (1, 2, 3, 4, 5, 6, 7, 8) MESSAGES C:\PTP_DB2\DOCK_RATE.log INSERT INTO DB2ADMIN.DOCK_RATE (FACILITY_ID, DOCK_ID, DOCK_RATE_ID, DOCK_RATE_TYPE, MOT, SIZE_UOM, LOAD_RATE, UNLOAD_RATE);
    CONNECT RESET;

    Error getting ---

    SQL3120W The field value in row "1" and column "3" cannot be converted to an
    INTEGER value, but the target column is not nullable. The row was not loaded.

    Getting above error for all rows.

    Table structure

    CREATE TABLE "DB2ADMIN"."DOCK_RATE" (
    "FACILITY_ID" INTEGER NOT NULL ,
    "DOCK_ID" VARCHAR(8) NOT NULL ,
    "DOCK_RATE_ID" INTEGER NOT NULL ,
    "DOCK_RATE_TYPE" VARCHAR(1) ,
    "MOT" VARCHAR(8) ,
    "SIZE_UOM" VARCHAR(8) ,
    "LOAD_RATE" DECIMAL(13,2) ,
    "UNLOAD_RATE" DECIMAL(13,2) )
    IN "USERSPACE1" ;


    Early reply is appreciated.

    Thanks
    PTP

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Import failing please help

    I think, the coldel can be only one character

    Cheers

    Sathyaram

    Originally posted by ptp
    My import file.

    39302~#1~#1~#F~#~#LBS~#10.00~#20.00
    66826~#DOCK1~#1~#F~#TL~#LBS-Ch~#10.00~#12.50
    66829~#DOCK1~#1~#F~#TL~#LBS-Ch~#10.00~#12.50
    66832~#DOCK1~#1~#F~#TL~#LBS-Ch~#10.00~#12.50
    66838~#DOCK1~#1~#F~#TL~#LBS-Ch~#10.00~#12.50
    66843~#DOCK1~#1~#F~#TL~#LBS-Ch~#10.00~#12.50
    66929~#DOCK1~#1~#F~#TL~#lbs~#10.00~#12.50
    66915~#1~#1~#F~#~#~#2.00~#2.00
    66917~#1~#1~#F~#~#~#3.00~#3.00
    65048~#2~#1~#F~#~#cube~#45.00~#17.00
    65991~#1~#1~#R~#~#LBS-Ch~#1.00~#10.00

    import command

    CONNECT TO SAMPLE;
    IMPORT FROM C:\PTP_DB2\PTP_DOCK_RATE.OUT OF DEL MODIFIED BY COLDEL~# DECPT. METHOD P (1, 2, 3, 4, 5, 6, 7, 8) MESSAGES C:\PTP_DB2\DOCK_RATE.log INSERT INTO DB2ADMIN.DOCK_RATE (FACILITY_ID, DOCK_ID, DOCK_RATE_ID, DOCK_RATE_TYPE, MOT, SIZE_UOM, LOAD_RATE, UNLOAD_RATE);
    CONNECT RESET;

    Error getting ---

    SQL3120W The field value in row "1" and column "3" cannot be converted to an
    INTEGER value, but the target column is not nullable. The row was not loaded.

    Getting above error for all rows.

    Table structure

    CREATE TABLE "DB2ADMIN"."DOCK_RATE" (
    "FACILITY_ID" INTEGER NOT NULL ,
    "DOCK_ID" VARCHAR(8) NOT NULL ,
    "DOCK_RATE_ID" INTEGER NOT NULL ,
    "DOCK_RATE_TYPE" VARCHAR(1) ,
    "MOT" VARCHAR(8) ,
    "SIZE_UOM" VARCHAR(8) ,
    "LOAD_RATE" DECIMAL(13,2) ,
    "UNLOAD_RATE" DECIMAL(13,2) )
    IN "USERSPACE1" ;


    Early reply is appreciated.

    Thanks
    PTP

  3. #3
    Join Date
    May 2003
    Posts
    7

    Re: Import failing please help

    Thanks for reply.

    I am putting this coldel 2 characters because there are no chances that these 2 characters appear in my import Data so import will not fail.

    If this is limitation then my import will fail if COLDEL character can be only one character and that COLDEL character appears in my import data.

    is there any way i should come out of this limitation ?

    In Sql server and oracle i can specify more than 1 character as column seperator.

    Thanks a lot in advance

    PTP





    is there any way i can put 2 characters for COLDEL .

    Originally posted by sathyaram_s
    I think, the coldel can be only one character

    Cheers

    Sathyaram

  4. #4
    Join Date
    Apr 2003
    Posts
    191

    Re: Import failing please help

    Hi PTP,

    no can do. COLDEL is a character in DB2.

    Of course there are some options, but not necessarily with DB2. Can you work on the data earlier?

    Your options:
    a) IXF-Format if your data source is (another) DB2.
    b) Fixed column length format doesn't need COLDELs.
    c) The hard way is designating a COLDEL character, filtering and fixing the data before the COLDEL is applied. This would be a programmatic solution.

    Johann

    Originally posted by ptp
    Thanks for reply.

    I am putting this coldel 2 characters because there are no chances that these 2 characters appear in my import Data so import will not fail.

    If this is limitation then my import will fail if COLDEL character can be only one character and that COLDEL character appears in my import data.

    is there any way i should come out of this limitation ?

    In Sql server and oracle i can specify more than 1 character as column seperator.

    Thanks a lot in advance

    PTP





    is there any way i can put 2 characters for COLDEL .

Posting Permissions

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