Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2013
    Location
    India
    Posts
    246

    Unanswered: Row rejected while Load

    Hello all,

    Db2 10.1 WSE on RHEL 6



    While loading About 200000 rows only 9704 get loaded rest 190296 got rejected with message:

    "SQL0406N A numeric value in the UPDATE or INSERT statement is not within the
    range of its target column. SQLSTATE=22003"


    One of the rejected row is as below :

    199991 Paul Jack jdixon255i@simplemachines.org 40 Marcy Lane Massachusetts 72875657 3571653561125220 17.9 27-09-2015

    Table DDL:

    CREATE TABLE DWH.TCS_FOLIO (
    ROLL_NO INTEGER NOT NULL GENERATED BY DEFAULT
    AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE,
    NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),
    NAME VARCHAR(30),
    FATHERS_NAME VARCHAR(30),
    EMAIL VARCHAR(40),
    ADDRESS VARCHAR(100),
    STATE VARCHAR(40),
    PINCODE BIGINT,
    FOLIO_NUMBER VARCHAR(40),
    AMOUNT DECIMAL(4, 2),
    DATE_IEPF DATE
    )

    Load stmt:

    db2 "load client from "E:\sumit_data\sumit\db2 admin files\db2export_import\TCS_folio.txt" of del modified by COLDEL, dateformat="DD-MM-YYYY" identitymissing Messages "E:\sumit_data\sumit\db2 admin files\db2export_import\TCS_folio_msg.txt" insert into DWH.TCS_FOLIO COPY YES TO "/home/db2inst1/db2backup/copy_yes" DISK_PARALLELISM 2"

    I am not able to figure out the reason for rejection i have designed the table well according to data type.

    any clues ?

    thanks in advance
    ssumit

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I presume 199991 is the ROLL_NO column
    identitymissing:If this modifier is specified, the utility assumes that the input data file contains no data for the identity column (not even NULLs)
    if the identity is present in file and not needed : identityignore should be used
    it is always better to specify insert into tablename (col1,col2...
    because the layout of a table can change..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Thanks Guy,

    Sorry...A little correction, Column ROLL_NO is not present in my input file, it is generated at the time of loading only so actual row rejected is:

    Paul Jack jdixon255i@simplemachines.org 40 Marcy Lane Massachusetts 72875657 3571653561125220 17.9 27-09-2015

    I also modified the load stmt as :

    db2 "load client from "E:\sumit_data\sumit\db2 admin files\db2export_import\TCS_folio.txt" of del modified by COLDEL, dateformat="DD-MM-YYYY" identitymissing Messages "E:\sumit_data\sumit\db2 admin files\db2export_import\TCS_folio_msg.txt" insert into DWH.TCS_FOLIO (ROLL_NO,NAME ,FATHERS_NAME,EMAIL,ADDRESS ,STATE ,PINCODE ,FOLIO_NUMBER,AMOUNT,DATE_IEPF) COPY YES TO "/home/db2inst1/db2backup/copy_yes" DISK_PARALLELISM 2"

    still the result is same, only 9704 records are loaded ..

    any suggestion ?
    ssumit

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I would recommend you do use import for this type of errors
    with import you have more indications about row keeping/causing the error
    you can also change commitcount with import...
    when this problem has been identified/corrected you can return to load..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though, you specified "modified by COLDEL, "
    no comma was in your input data
    Paul Jack jdixon255i@simplemachines.org 40 Marcy Lane Massachusetts 72875657 3571653561125220 17.9 27-09-2015
    So, columns in the data might be misinterpreted.

  6. #6
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Thanks Tonkuma and Guy,

    @Tonkuma: Actually that row is copied from ms excel so comma is not seen to you, in fact the txt file contains the comma like

    Tina,Patrick,phunter15@sfgate.com,32 Ryan Center,Virginia,3419,6304648216480410,503.37,27-09-2015

    @Guy: As you said i am switching to import now
    ssumit

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Compared your CREATE TABLE and provided data,
    you might want to insert data 503.37 into the column AMOUNT DECIMAL(4, 2).

    The value 503.37 need at least 5 precision, like DECIMAL(5 , 2).

  8. #8
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Quote Originally Posted by tonkuma View Post
    Compared your CREATE TABLE and provided data,
    you might want to insert data 503.37 into the column AMOUNT DECIMAL(4, 2).

    The value 503.37 need at least 5 precision, like DECIMAL(5 , 2).
    Hi Tonkuma,

    You identified right, after increasing DECIMAL(5 , 2) to DECIMAL(9, 3) it succeeded and all 200000 rows loaded.

    Thanks once again.
    ssumit

Posting Permissions

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