Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2008
    Posts
    9

    Unanswered: need suggestion for exporting clob data from table

    Hi all,
    we are using DB2 v8.1.0.104,FixPak "11"

    we have table with 3 columns one is clob(500000),row count over 4 million,
    iam trying to export data and import it back to have limited back in table.

    I tried for 200,000 row for testing purpose, it takes around 50 minutes to import back. i remove the index to make it bit easy the column value is almost near to max allowed size( clob 500000).
    Is there any better way to make the import faster ?

    below are the command iam using:
    db2 export to file.del of del modified by lobsinfile messages export.msg 'select * from A_table';

    db2 import from Event.del of del lobs from /home/Data_export/ modified by lobsinfile commitcount 10 messages import.msg 'insert into A_table'

    --
    thanks in advance..

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't think I understand why you are doing this, but LOAD is usually faster than IMPORT.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by n_i
    I don't think I understand why you are doing this, but LOAD is usually faster than IMPORT.
    You're modest, LOAD is always faster than IMPORT.
    Is the flat-file essential? A load from cursor workt great!
    When the databases are not on the same machine, create a nickname (google for federation) for the source-db on you target machine and "load from cursor".

  4. #4
    Join Date
    Aug 2008
    Posts
    9

    having problem after load

    thanks all for your suggestions,
    I have used db2 load to import data to table and the process was successful
    but iam now unable to access the table.

    Details:
    ***Type: com.ibm.db2.jcc.c.SqlException
    ***Error Code: -668
    ***SQL State: 57016

    DB2 SQL error: SQLCODE: -668, SQLSTATE: 57016, SQLERRMC: 1;

    please suggest me..

    thanks all.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at the manual. It contains detailed descriptions for all error messages that DB2 issues: SQL0668N
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Re: sql0668n RC=1. The table is part of R/I and therefore is in check pending state. The set integrity command will take it out of this state.
    Last edited by db2girl; 02-04-09 at 23:03.

  7. #7
    Join Date
    Aug 2008
    Posts
    9

    thanks all

    Hi all,

    thanks for your reply, Load is much faster compaired to import but unfortunately we cant use load because it dont log & we need db2 logs to keep in sync with another server.

    basically we have 3 table with huge data and we are running out of table space and we thought of droping the table to reclaim space provided we get our data back.

    Is there any easier way of copying data to temp table & moving it back to original tables once they are recreated ?

    please suggest .. thanks

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You may want to lower the high-water mark and reorganize the table. That would be much easier than copying data around.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Aug 2008
    Posts
    9

    having problem while export

    Hi all,

    we are trying to export 70million row worth data having clob column.

    iam using below cmd:
    db2 export to test.del of del modified by lobsinfile messages error.msg "SELECT * FROM TEST_TABLE";

    getting error:

    SQL3040N Unable to use the lobpath/lobfile parameters as specified. Reason
    code: "6".

    SQL3040N
    The utility cannot use the file name file-name specified in the option-name parameter. Reason code: reason-code.

    Explanation
    6
    The sum of the starting position and the length of the input data exceeds the size of the file with file name file-name.

    -----------------------------------
    any idea if there is any limitation on clob file size. It only exported 4millions rows of data.

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    try specifying multiple paths with the LOBS option.

Posting Permissions

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