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
610 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

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, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 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