Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    Unanswered: db2 9.7 - Bulk data load consideration

    DB2 9.7 FP 3a /Win 2008 R2

    our DB size is around 550GB, contain around 25 tables and around 100 indexes.
    each table contains average 60 million rows.
    This is a HADR enviornment, the system daily produces around 50 transaction logs each 10MB size.
    Daily we load data into the DB , around 15K rows (include image -each image 2-4 KB size)

    I am going to do a bulk load (which will effect most tables) and expecting around 250,000 rows including images .

    The question is :--
    1. How can I workout that howmany transaction logs will be produced during the load
    2. As this is a HADR enviornment, will this effect log shipping? bandwidth is enouth?
    (current banwidth is 4mbps between primary and standby).
    3. Update Statistics is required immediatley?

    4. Also any other concern?

    Please advise.
    Thanks, valan

  2. #2
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111
    hi,
    anyone can give some ideas please?

    Also, i mentioned the bulkload. but i am not going to use the import or LOAD.
    going to use the standard insert statement.

    Thx.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    1) We cannot give you a specific amount. There are a lot of things that determine how many log files will be generated. I suggest you test it out in a test environment and see.

    2) Of course it will affect log shipping. You are adding work load to the system. Whether the bandwidth is enough depends on the outcome of how many log files are being created.

    3) You are adding less than 0.5% rows each time. Stats may not be needed each time the bulk load is performed. It depends on the kind of data you are loading.

    Andy

  4. #4
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111
    Thanks Andy.
    I can not try this on the test system as the DB is very small and not enough space.

    Is it advisable to suspend the HADR (Deactivate the DB on standby node), once the load has been completed then resume the HADR?

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not think so. But I am no expert on HADR.

    Andy

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't think you need to worry about HADR if you are only inserting 250,000 rows, assuming your log buffer is set correctly. But I am not sure what you mean by bulk load.

    You should be able to test this on some system using a subset of the rows, and extrapolate to a larger number of rows.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by dgunas View Post
    Is it advisable to suspend the HADR (Deactivate the DB on standby node), once the load has been completed then resume the HADR?
    Is the bulk running during downtime or not? If it is not in downtime you must not deactivate your HADR either. You have HADR to guarantee db-uptime so why take extra risks.
    On the other hand: when you do run during downtime, why use INSERT's? LOAD's are much faster....

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    do not forget copy yes when using load and hadr - and logging of index create
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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