Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2012
    Posts
    41

    Unanswered: DB2 ETL sizing - best practices

    Hello Guys,

    I am looking for some advice here

    I have a couple of tables that I need to extract from one data source to another data source nightly on the same environment.

    Some of the tables will grow and other's wont.

    I am not sure whether I should use the general insert into select statement or whether I should use an export db2 command.

    Is their any documentation on what is the best practice ?

    Example of table sizes attached in pdf

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2012
    Posts
    6
    Is it a one time activity or a daily excercise. Do you want to automate it ?

    you can use load command if you want to write some script for the same and data is very large otherwise insert into select statement is also not bad if you need to transfer few rows..

  3. #3
    Join Date
    Aug 2012
    Posts
    41
    It will be done every night for the forseeable future
    It will also be automated.

    So I would use insert select for tables that have few rows and use the extract load command for large data sets ?

  4. #4
    Join Date
    Mar 2012
    Posts
    6
    yeah coz insert into statement and load give the same performance for few thousand of data. but if data is large then using load command would be a better option.

  5. #5
    Join Date
    Aug 2012
    Posts
    41
    Forgot to mention that the potential large datasets have images associated with them, so I dont think exporting to a csv file would work ?

  6. #6
    Join Date
    Jan 2010
    Posts
    335
    Hi,

    you could use Federation and load from cursor.

    Or you export into a Pipe and zip it from there. Load the other way: Unzip to pipe and load from pipe.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by alexandra123 View Post
    Forgot to mention that the potential large datasets have images associated with them, so I dont think exporting to a csv file would work ?
    It would, if you use the LOBSINFILE modifier.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Aug 2013
    Posts
    1
    Hi alexandra123,

    If the tables/source files which you were loading is big then you can use the not logged initially option at table level so that rows will be directly inserted into the pages instead of normal sql inserts. It will speed up your insert. But make sure you will be not be able to recover incase of any interrupt. For that you need to first turn off the autocommit off(+c).
    1. Prepare your insert statement and put into a file and mention the commit statement after the insert . Note all your data will be pushed to hard disk at the final step. So the commit would take long. This is the fastest way of loading.

    If you want more info, search with alter table not logged initially in publib boulder.

    Thanks,
    SantoshMaturi

  9. #9
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    You know that you can start using the new INGEST feature for free just by installing a db2v10-client?
    Introduction to the DB2 Continuous Data Ingest feature
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

Posting Permissions

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