Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    8

    Unanswered: '|' symbol in a column rejected while loading

    Hi All,
    In the process of migrating the data from sybase to IBM DB2. The following error has been encountered while loading the table in DB2. The table is having the symbol - '|' in a column in few rows. And those particular rows are being rejected.
    Any clue for this error.


    SQL3116W The field value in row "95257" and column "4" is missing, but the target column is not nullable.
    SQL3185W The previous error occurred while processing data from row "95988" of the input file.
    SQL3116W The field value in row "96054" and column "4" is missing, but the target column is not nullable.


    Thanks,

  2. #2
    Join Date
    Oct 2005
    Posts
    109
    please provide the following: ddl of the table, the load statement and sample input which is rejected.
    this is too few information to be able to help.

    Be aware that some use | as delimiter ...
    Juliane

  3. #3
    Join Date
    Jan 2004
    Posts
    49
    Hi,
    Your data load / import script may contains coldel as '|'. Can you do export using some other coldel and chardel (say '<') ? Use samething while doing Import / load / autoload.

    Let us know if you face any problem.

    With Regards

    Bala

  4. #4
    Join Date
    Oct 2005
    Posts
    8

    CHARDEL in Load Utility Command

    Hi Julian and Bala,
    Initially I gave this load utility command:
    LOAD FROM 'D:\Table1.prod' OF DEL MODIFIED BY CHARDEL| COLDEL~ KEEPBLANKS TIMESTAMPFORMAT="MMM DD YYYY HH:MMS:UUUTT" MESSAGES "C:\DBLog1.log" REPLACE INTO SCHEMA.Table1;

    Now, I tried with this by mentioning CHARDEL<
    LOAD FROM 'D:\Table1.prod' OF DEL MODIFIED BY CHARDEL< COLDEL~ KEEPBLANKS TIMESTAMPFORMAT="MMM DD YYYY HH:MMS:UUUTT" MESSAGES "C:\DBLog1.log" REPLACE INTO SCHEMA.Table1;

    But, again the rows were not read.It has thrown this error SQL3115W The field value beginning "<....." is longer than the longest allowable table column. The value was truncated.

    Sample row of input is:
    ^9999~Aug 15 1947 12:00:00:000AM~2~A~FIRST SECOND ~1~1~1~11~YYY001~May 11 1857 1:37:09:220AM^9999~...second row continued...


    Can we load the data by not specifying CHARDEL?

    Thanks,
    Best Regards,
    Saurabh Singhal.

  5. #5
    Join Date
    Oct 2005
    Posts
    109
    If you don't specify cardel and coldel, the default values will be used - I think , for coldel and " as chardel. check the documentation on this:
    http://publib.boulder.ibm.com/infoce...e/r0008305.htm

    I can't see any | pipe symbol or character data in your input. So I can't match the input row to the error message.
    Juliane

  6. #6
    Join Date
    Oct 2005
    Posts
    8

    Chardel

    Hi Juliane,
    Thanks for the reply.
    What is the function of chardel in the Load Utility command?
    Since in my data I am not having '|' in any of the rows and moreover in the data originally I have '~' or '^' as delimiters.
    What if I skip mentioning chardel in Load Utility command?

    Thanks,
    Saurabh.

Posting Permissions

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