Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2013-11-15
Medium Priority
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
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
  • 3
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 2000 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 46

Accepted Solution

Kent Olsen earned 2000 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 46

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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

610 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