Solved

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

Posted on 2013-11-15
5
606 Views
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.
0
Comment
Question by:data_bits
[X]
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
5 Comments
 
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?


Kent
0
 

Author Comment

by:data_bits
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?
   0

-  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.
0
 
LVL 45

Accepted Solution

by:
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!

ALTER TABLE newtable NOT LOGGED INITIALLY  WITH EMPTY TABLE;
COMMIT;
DECLARE cursorname CURSOR FOR SELECT * FROM oldtable;
ALTER TABLE newtable NOT LOGGED INITIALLY;
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,
Kent
0
 
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.
0
 
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.


Kent
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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 (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…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

696 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