Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 644
  • Last Modified:

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

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
data_bits
Asked:
data_bits
  • 3
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
data_bitsAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
MurpheyApplication ConsultantCommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now