Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    1

    Unanswered: Db2 Export count is off by some records

    Dear DB2 experts,
    Have an issue that we need your advice/help on. Basically, we do an export from db2 by extracting data from 3 tables. One of the tables has a clob field with multiple lines of data that we need to extract. So, in our export (please see sample below) we put the clob field as the last field and run the export db2 says it exported 15000 rows.

    Now we use the DEL file and run a java program to parse it and load the data into a different database which is non DB2. Now during the parsing, we loop thru the file and populate the data into a separate column on this non db2 database. When the java code completes, the count is off by either 5 or 10 records depending on the number of records that we exported. We have gone thru the java code line by line to verify if we were missing anything and they all seems to look ok. We have also put a count on the number of lines we read and it exactly matches to the number of lines in the DEL file. So, we are not sure if the number that gets displayed after the db2 export is accurate. We have noticed that this happens only on data which spans across multiple lines.

    So any ideas/suggestions that you can provide to help us look into?

    Here’s a sample of the data in the DB2 DEL file

    112345,0031716,1,25,-1,0,"Email","746335","#Wed Nov 10 20:57:44 EST 2008
    REALACCOUNTNUMBER=XXXX6789
    ROUTING_NUM=XXXX6283
    ACCOUNT_NUMBER= XXXX6789
    ACCOUNTNUMBER= XXXX6789-1
    MAX=
    MIN=5.00
    ROUTINGNUMBER= XXXX6283
    "
    112346,0031718,1,25,-1,0,"Email","746335","#Wed Nov 10 20:57:45 EST 2008
    subject=Test message
    to=xyz@usemail.com
    from=onlineemail.com
    "

    Thanks
    Santhosh

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    DB2 will export up to 32KB of LOB data, the rest will be truncated (by default). So, if your LOB value exceeds 32KB, you need to use LOB modifiers. Please see here for more info:
    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows


    I'm not sure if this is related to your problem or not... probably not since the number of records should still match, just some data may be missing.

    What I'd suggest to do is to create a DB2 table to match your exported file and then try to load it to see if the number of exported and loaded records match. This may help you figure out if the problem is within DB2 or somewhere else.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    My guess would be that either the Java application or the target DBMS doesn't correctly parse the data file due to the additional line breaks in the CLOB values. Due to the truncation mentioned by db2girl, I'd recommend to use the LOBSINFILE option on the EXPORT command.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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