Hi, I have just taken over as the DBA for a database that has not had any reorgs/runstats run against it for the last three years. As a first step, I have just run reorg/runstats against the system catalog tables & rebound packages.
I next intend to reorg/runstats all the application tables that I have identified as needing this and due to the remote possibility of this causing an adverse affect on performance, it has been decided that I should perform this during an application outage after having first taken a full offline backup in case I should need to backout. (i.e. I don't have the option of running reorgs/runstats while the application is running)
My question is that I have an outage window of 4 hours but reorgs/runstats if run in sequence will take in excess of 10 hours. Is it possible to run reorgs and runstats (against different tables) concurrently ? The tables are not split over nodes - there is just a single node. All the tables reside in one tablespace and all the indexes are in another. Does anyone have any experience of this. I can't find anything in the DB2 for LUW manuals that cover this.
Many thanks, David
4 5957
ya sure, you can run RUNSTATS in parallel.
-- Regards
Sanjay
Hi,
yes, you can run Reorg and Runstats parallel. But another question is resulting from here: Are your tables (or tablespaces) which you want to do reorg on the same or different hard disks?
Running such processes parallel on the same filesystem may slow down everything, depending on the structure of your db, the physical parameters of your storage etc., because in such cases the r/w heads usually will need to do a lot of seeks across your hard disk(s).
Regarding the fact that your db passed years without the aid of an db2 admin, I assume it's not part of a online system with high frequent write actions.You could think about offloading the reorg/runstats to another, similar system (backup/restore of db or tablespaces to similar system, reorg there, backup/restore back) while the main system stays accessable.
Besides, I'm used to nightly online reorgs and runstats actions on my webapp databases, and never experienced any trouble with DB2.
Regards,
Bernd
Hi, thanks for your replies.
In answer, I have established that runstats appear to run fine in parallel.
In terms of disks, our database is on a SAN so should be properly striped across many hard disks. Unfortunately, this database (25 GB in size) IS actually an OLTP database !
As regards REORG's, I have found the following SQL useful
select *
from table (SNAPSHOT_TBREORG ('databasename', -2) ) as snapshot where reorg_end is null with ur
The columns REORG_PHASE and REORG_CURRENT_COUNTER have helped me identify when reorgs submitted in parallel seem to be contending, but I have found precious little information as regards this from IBM or on the WWW.
What I have noticed is that if I try to reorg a table by an index using Tempspace, REORG_PHASE 1 (which I believe is the copy of the table to tempspace) seems to be able to run in parallel. However, REORG_PHASE 2 (whatever that is ?!) seems to allow only one reorg to run at a time if they are both defined to use the same tempspace. If they use seperate tempspace, then they seem to run fine in parallel. When a reorg runs without tempspace defined for use, then the reorgs seem to be running in parallel ok. One thing I'm not sure about is that there are sometimes gaps (at the beginning of phase 2 and 3 and especially during phase 4) when the REORG_CURRENT_COUNTER stays at zero for a long time - not sure if this is contention or not.
Performance is a seperate issue ... these reorgs take 4 hours for the largest tables (35 million rows) and I'm looking to split up tables into seperate tablespaces and to define more than one bufferpool and utilize 8, 16 and 32 k tablespace/bufferpool pages to see how this helps.
Thanks, David
Usually find the following approach works effectively for reorgs: - Turn on Autorunstats
- Reorgchk current statistics
- Any tables that need reorgs (any *'s) kick off an online table reorg
- Use SNAPSHOT_TABREORG to limit the number of concurrent reorgs (say, 5 max)
- For index reorgs, any *'s except the first one
- Check their is enough space in the index tablespace for the shadow copy
- Before running backups, pause or stop online table reorgs
Autoreorg can only do offline table reorgs which usually isn't very practical fore most customers. With online table reorgs, there can be contention issues with backups hence the need to stop the reorgs.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Prem K Mehrotra |
last post by:
I am using db2 version 6 on zos. I am trying to runutility using
utilities menu option in spufi. After specifying information on the screen:
DB2 UTILITIES "DOWN " is not acti
==>
...
|
by: hikums |
last post by:
1. I did a reorgchk, and found that F2 on table and F4 on index
indicates a reorg is necessary.
2. Did a reorg.
3. Again I do a reorgchk, the report is the same as in Step 1
4. I do a...
|
by: andy.standley |
last post by:
Hi,
we are running DB2 V8.2 (8.1.0.80) on redhat linux and I am trying to
set the reorg to be online. I use the control center on the box - db2cc
and then configure automatic maintenance wizard -...
|
by: derekbarrett |
last post by:
Hi all,
Our business is in manufacturing, and we are running SAP on DB2 8.1,
planning on upgrading to 8.2.2 within a couple of months. We are
running operations 24/7, so are looking for ways to...
|
by: andy.standley |
last post by:
Hi,
I posted something a while ago about not being able to setup online
reorgs - the db2 control center only offers online runstats. So I
started adding tasks to reorg each table. This works fine,...
|
by: alex.mcshane |
last post by:
Hi -
Via the UDB / LUW Control Centre I have tried in vain to identify when
a utility was last run.
(e.g. Clicking on a given Table)
Any idea as to how this information can be retrieved?
...
|
by: Yuri |
last post by:
We have an update sql that was running OK for several months completing
in under 2 minutes . Yesterday something changed and we had to kill this
update after it ran more than 6 hours. Update input...
|
by: Okonita via DBMonster.com |
last post by:
Hi all,
I am very surprised to see that after doing a Reorgchk followed by reorg of
selected tables and concluding with a runstats of the reorged tables, all of
the tables continue to be...
|
by: antonyrajeshi |
last post by:
Hi
Environment: DB2 v9.5 Workgroup edition on Windows 2003. the tablespaces are in a SAN storage.
I am processing about 10 million records in one maintable and about 12 million in another in my...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |