Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

In DB2 v9.7, if you reload data into tables every night, is a reorg needed for performance?

Posted on 2013-11-15
Last Modified: 2013-12-03
On db2 v9.7 on Linux, we have a nightly process that loads application tables from another database.

is it necessary to also do reorgs of the tables? The LOAD REPLACE command options we use include rebuilding the indexes. In this case, will reorgs of all the application tables make any difference to performance? I can see where reorg of the SYSIBM would be needed, but curious about the this situation.
Question by:data_bits
  • 3
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 500 total points
ID: 39653231
Hi data_bits,

The short answer is, "it depends".  The long answer is, "it depends on the nature of the data, how it's organized, and how you use it".

A REORG can't hurt.  It might be that a weekly or monthly reorg is sufficient so that you don't have to run one daily.

If you can answer a few questions, we can probably give you a better answer.  :)

-  How much data is there in the affected tables?  (Rows and approximate size.)
-  How many rows are replaced daily?
-  How many rows are inserted daily?
-  How many rows are deleted daily?
-  When the data is read for reporting, what are the fields primarily used for joining and filtering?


Author Comment

ID: 39656647
Hi Kent,

Responses below.

-   How much data is there in the affected tables?  (Rows and approximate size.)

    The tables vary in how many records they contain. Some have 5,000 while the larger ones have 35,000,000 in them. I guess the largest tables would be 900MB to 1.1GB in physical size.

-  How many rows are replaced daily?
 All rows are replaced each day using LOAD/REPLACE. Indexes are all rebuilt as part of the load process. Then we do a backup. Full set of runstats commands are executed right after that.

-  How many rows are inserted daily?
    On average 20 at most into some history tables. Sometimes for month end there may be 100-200.

-  How many rows are deleted daily?

-  When the data is read for reporting, what are the fields primarily used for joining and filtering?
  The primary key columns are used for most the joins. It is an INTEGER data type.
LVL 45

Accepted Solution

Kent Olsen earned 500 total points
ID: 39657704
Do you need to do a LOAD REPLACE?  If you're copying a table into this database that may have gained a few rows but will not have had any data deleted, you'll get better performance out of the load if you initialize the table before the load.  And use LOAD FROM CURSOR!

DECLARE cursorname CURSOR FOR SELECT * FROM oldtable;
LOAD FROM cursorname INSERT INTO newtable ....;

For best overall query performance, sort the data (in the cursor declaration) to group/order items by the most critical items.  (If you're examining invoices, you might order by invoice or customer number.  If you're examining student grades, sort by student id, etc.)

Good Luck,
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 39680751
Depend on the type of hardware you use. on iSeries a RGZPFM is enough, other servers i would do a drop/rebuild  of the table that is really fast.
LVL 45

Expert Comment

by:Kent Olsen
ID: 39680798
Hi Murphey,

I didn't realize that you were an iSeries guy.  Good to know!

On the pSeries, the sequence that I posted above should be about as fast as it gets.  The ALTER ... WITH EMPTY TABLE drops the data almost instantly without actually dropping the table.  Dropping the table has potential pitfalls as that can impact referential integrity, triggers, invalidate stored procedures, etc.

And LOAD ... FROM CURSOR is one of the things that IBM got right!  I had a coworker tell me about it one day and thought that he was nuts.  It turned out that at the time I was a full release behind in my knowledge and just didn't know about it.  But it's hugely faster than any other type of LOAD that I've found.


Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question