Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Posts
    14

    Question Unanswered: Load warning: SQL3114W... Help

    I have this unload file from an Informix database table, which is "|" delimited.

    When I try to LOAD it into an identical DB2 table, for some of the rows, I get the following error.

    ***
    SQL3114W Some data following ""O"" in row "189595" and column "11" was not loaded.
    ***

    The data in this column starts with a " (double quote).

    Also, if the data have a " (double quote) anythere ELSE (except the very first character), it does not give this error.

    Is there a way to resolve this error.

    Thanks in advance.
    M2001

  2. #2
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,

    Have you checked your load statement. What string delimiter are you using in the load command and what is the string delimiter in the load file?
    There must be a difference in this.

    Check and let us know.

    Cheers,
    Prashant

  3. #3
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: Load warning: SQL3114W... Help

    Can you write us the exact statement youīre submitting??

    Just out of curiosity: | is the column delimiter?

    It seems that you didnīt provide the char delimiter.
    If so, just add the chardel" (if " is your delimiter) right after the coldel parameter.

    Fernando

    Originally posted by M2001
    I have this unload file from an Informix database table, which is "|" delimited.

    When I try to LOAD it into an identical DB2 table, for some of the rows, I get the following error.

    ***
    SQL3114W Some data following ""O"" in row "189595" and column "11" was not loaded.
    ***

    The data in this column starts with a " (double quote).

    Also, if the data have a " (double quote) anythere ELSE (except the very first character), it does not give this error.

    Is there a way to resolve this error.

    Thanks in advance.

  4. #4
    Join Date
    Jan 2003
    Posts
    14

    Re: Load warning: SQL3114W... Help

    Thanks for the reply.

    Here's the LOAD statement...

    load from g:\sqldata\tblname.unl of del
    modified by coldel|
    dumpfile=f:\tblname.rej
    savecount 10000
    warningcount 26000000
    messages f:\tblname.msg
    insert into db2admin.tblname


    Here's a sample record that gives this problem...

    00064114|00001|665|0006|400107|000006|NA|x|x|x|"C" BASE||IC|DATA MER|19980723|


    What happens is.. when this record gets loaded, gives the warning...

    -->SQL3114W Some data following ""C"" in row "189595" and column "11" was not loaded.<--

    In the 11th column on "C gets loaded.


    Any ideas.
    M2001

  5. #5
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: Load warning: SQL3114W... Help

    Heys,

    When you donīt specify the char delimiter the DB2 uses the default delimiter which is the " symbol.
    The problem is that after the "C" you have other information, and as the delimiter does not cover it the DB2 canīt recognize the rest.
    If it is possible, try putting on the file instead of "C" BASE - "C BASE" or removing the word BASE.

    But IMO the best option would be to unload the data from the source table using any char delimiter other than ", like ?, for example. And then, load the data into the target table specifying the chosen delimiter for the char columns, with the chardel? (for the given example).

    See if it is possible..

    HTH,

    Fernando

    Originally posted by M2001
    Thanks for the reply.

    Here's the LOAD statement...

    load from g:\sqldata\tblname.unl of del
    modified by coldel|
    dumpfile=f:\tblname.rej
    savecount 10000
    warningcount 26000000
    messages f:\tblname.msg
    insert into db2admin.tblname


    Here's a sample record that gives this problem...

    00064114|00001|665|0006|400107|000006|NA|x|x|x|"C" BASE||IC|DATA MER|19980723|


    What happens is.. when this record gets loaded, gives the warning...

    -->SQL3114W Some data following ""C"" in row "189595" and column "11" was not loaded.<--

    In the 11th column on "C gets loaded.


    Any ideas.

  6. #6
    Join Date
    Jan 2003
    Posts
    14

    Re: Load warning: SQL3114W... Help

    I'm confused.

    If you see the LOAD statement, it says "coldel|". Also the data file is | delimited.

    Am I missing something in your comments
    M2001

  7. #7
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: Load warning: SQL3114W... Help

    Hey,

    The coldel| part from what I got is correct. coldel stands for column delimiter.

    The problem is when DB2 tries to recognize the char/varchar columns inside your datafile. You specify the delimiter for characters by using the "chardel" parameter or by using the default delimiter, that is " (double quote).
    As you didnīt specify the character delimiter, DB2 is using the default (").

    Now back to your datafile:
    00064114|00001|665|0006|400107|000006|NA|x|x|x|"C" BASE||IC|DATA MER|19980723|

    When the loader gets to the 11th column, it finds a " (double quote) that by default, initiates a character string. After the C, thereīs another " closing the string. As the string was closed, DB2 wonīt recognize the string "BASE" (because it is out of the delimited character) as a part of the same column, so it is not loaded into the table.

    Try removing the double quotes from the columns or placing the string 'BASE' inside the quotes.

    Hope this is clearer.

    Fernando

    Originally posted by M2001
    I'm confused.

    If you see the LOAD statement, it says "coldel|". Also the data file is | delimited.

    Am I missing something in your comments

  8. #8
    Join Date
    Jan 2003
    Posts
    14

    Re: Load warning: SQL3114W... Help

    Thanks a lot.

    Sorry didn't quite understand your earlier comment.

    New to DB2 :-(
    M2001

  9. #9
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: Load warning: SQL3114W... Help

    No problem =)

    Did that work?
    Fernando

    Originally posted by M2001
    Thanks a lot.

    Sorry didn't quite understand your earlier comment.

    New to DB2 :-(

Posting Permissions

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