ONLINE REORG

[andy.standley]

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 - and can get runstats
to be online but reorg only offers offline at the activities settings.
I have tried to understand why - I thought that online was always
offered ? I think that all my indexes are type 2 - I ran some reorg
indexes all on the tables to convert them if they were not. For each
table I can set up a task to do a reorg online, but not through the
maintenance wizard.

What am I missing ?

ta for any clues

andy

[Mark A]

<an***********@ecngroup.co.nz> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

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 - and can get runstats
to be online but reorg only offers offline at the activities settings.
I have tried to understand why - I thought that online was always
offered ? I think that all my indexes are type 2 - I ran some reorg
indexes all on the tables to convert them if they were not. For each
table I can set up a task to do a reorg online, but not through the
maintenance wizard.

What am I missing ?

ta for any clues

andy

You need to enable LOGRETAIN=ON instead of the default of circular logging.
Make sure you understand what the implications of that are, and that you
have some way of dealing with the log files.

Also, if you use LOGRETAIN=ON, this may affect you backup/recovery strategy,
since you MUST have the logs available when doing an on-line backup.
Probably the best way to ensure that the logs are available with an on-line
backup is to include them in the backup image.

[andy.standley]

thanks for the reply
not sure how this affects a reorg - why doed logretain change the way I
can do a reorg of a table?

[andy.standley]

thanks for the reply
not sure how this affects a reorg - why does logretain change the way I
can do a reorg of a table?

andy

[Larry]

Have a read of the REORG command in the Command Referece at

ftp://ftp.software.ibm.com/ps/produc...S/db2n0e81.pdf

Note the explanation for the parameters "ALLOW READ ACCESS" and "ALLOW
WRITE ACCESS". It would appear that ALLOW READ ACCESS is the default.
Does the db2cc present these options?

Larry Edelstein

an***********@ecngroup.co.nz wrote:

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 - and can get runstats
to be online but reorg only offers offline at the activities settings.
I have tried to understand why - I thought that online was always
offered ? I think that all my indexes are type 2 - I ran some reorg
indexes all on the tables to convert them if they were not. For each
table I can set up a task to do a reorg online, but not through the
maintenance wizard.

What am I missing ?

ta for any clues

andy

[andy.standley]

Larry,
The db2 gui seems to not do any configuration except when to do it and
to what tables. It allows backup and runstats to be online but reorg
only online. I am making tasks to do all the tables that I want and
that allows configuration like the cli command

andy

[Larry]

Andy,

When I open my CC and then right-click on a table, I select "Reorganize"
and I see a popup that gives me the choice between online and offline.
It also has a tab that allows me to schedule the REORG. Granted I am on
windows, but it should be the same.

8.1.0.80 would appear to be fp8. Where are you using the db2cc from? Are
you sure that your db2cc is at V8? If it is, I'm not sure I have an answer.

Larry Edelstein

an***********@ecngroup.co.nz wrote:

Larry,
The db2 gui seems to not do any configuration except when to do it and
to what tables. It allows backup and runstats to be online but reorg
only online. I am making tasks to do all the tables that I want and
that allows configuration like the cli command

andy

[andy.standley]

Larry,
Sorry - that it was I have done too. But in the tools -> wziards you
can setup maintenance. This offers reorg but only offline. I have used
this to setup runstats but reorg only says offline not online. I have
run the control center on windows and see the same as on linux. Yes it
is fp8.


andy

[Pierre Saint-Jacques]

I think the following is set up for online reogs to be automated.
A) I don't think the db has to be in logretain=recovery mode as the online
reorg is automatically revocered from a crash via the active logs which are
always available wether in circular logging or roll forward enbled mode.
B) For autoamtion online, one has to set the parm AUTO_REORG ON in the db
cfg or use the the db2 cc and define an online maintenca time window (page 3
Timing in the wizard).

Once that is set it is DB2 that will decide wether or not the table needs to
be roerg'ed. If yes, then it will check the time. If time is in the online
window defined then the online reorg will happen. If time is out the window
then no online reorg happens.
That's why you don't choose the online option for reorg; it is because DB2
decides ( Ig guess it runs a reorgchk under the covers) wether the table
needs to be and if in the window it will reorg it online.
That's the way it was explained to me and I may need to be corrected, but it
still makes sense to me!!!
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Larry" <la***@nospam.net> a écrit dans le message de news:
bJ******************@fe10.lga...

Andy,

When I open my CC and then right-click on a table, I select "Reorganize"
and I see a popup that gives me the choice between online and offline. It
also has a tab that allows me to schedule the REORG. Granted I am on
windows, but it should be the same.

8.1.0.80 would appear to be fp8. Where are you using the db2cc from? Are
you sure that your db2cc is at V8? If it is, I'm not sure I have an
answer.

Larry Edelstein

an***********@ecngroup.co.nz wrote:

Larry,
The db2 gui seems to not do any configuration except when to do it and
to what tables. It allows backup and runstats to be online but reorg
only online. I am making tasks to do all the tables that I want and
that allows configuration like the cli command

andy

[Mark A]

"Pierre Saint-Jacques" <se*****@invalid.net> wrote in message
news:bX********************@weber.videotron.net...

I think the following is set up for online reogs to be automated.
A) I don't think the db has to be in logretain=recovery mode as the online
reorg is automatically revocered from a crash via the active logs which
are always available wether in circular logging or roll forward enbled
mode.

You are correct. I was in error.

[andy.standley]

Pierre,
Thanks. I set up online and offline windows - that is fine. I go to the
activities of the maintenance wizard and it gives me options for
backup, reorg, runstats. But for reorg it says offline and the other
two online. I have now checked and changed the AUTO_REORG param to ON
but I can't restart the database and it says it needs that to make it
take effect. Maybe that will work ...

andy

[Ivan Popivanov]

Hi,

The automatic REORG introduced in v8.2 supports only offline REORGs, it
does not support online REORGs that's why the GUI offers you an offline
window only.

Ivan
an***********@ecngroup.co.nz wrote:

Pierre,
Thanks. I set up online and offline windows - that is fine. I go to the
activities of the maintenance wizard and it gives me options for
backup, reorg, runstats. But for reorg it says offline and the other
two online. I have now checked and changed the AUTO_REORG param to ON
but I can't restart the database and it says it needs that to make it
take effect. Maybe that will work ...

andy

[UDBDBA]

Andy:

Automatic maintenance feature is *currently* offline because that's the
way it can be scheduled given a specific window. Online reorg runs in
trickle mode and is not meant for speed. So, the window to complete
online reorg really depends on applications running against the
database.

If you are willing to take the command line option, let me know. It is
pretty simple and can be scripted and run from a scheduler/cron.

1. Runstats daily
2. Check reorgchk for table and index using sysproc functions and
export it to a table.
3. Pick the table & index that qualify fo reorg.
4. Start the online reorg for those tables only
5. Check and make sure that the reorg table completed and then start
index reorg.
6. Do a runstats, rebind all packages usin rebind command or just run
db2rbind if it permits.
8. Once again, run reorgchk for tables and indexes using sysproc and
dump it into the table.
9. Generate reports from this data.
10. Optionally. you can also export the sysproc.snapshot_tbreorg data
into a table and generate reports out of it. Even better, you can now
use the table function for list history command to export history reorg
data into a table and do the analysis.

You can also setup mechanism to page and let you know if reorg is
running for longer duration and if it's blocking a user application.
Online reorg being just like any other application connection will
block user application if it needs but only for a short period of time
during lock conversion where it needs to drain the old scanners. One
good feature is online reorg does not respect LOCK_TIMEOUT db cfg. So,
it will continue to wait on lock, but that might cause logs filesystem
to fill up if it ends up being the application holding the oldest log.

So, online reorg is complex underneath compared to offline mode and
cannot be scheduled to run automatically unless all the factors can be
pre-determined and rules can be setup before deploying it.

I am sure IBM already have their hands on developing a wizard to get it
running in online mode. Just a matter of which fixpak / version :)

HTH
Vijay

[andy.standley]

Thanks for that - from reading the docs it just seemed like reorg was
ok online.
some quotes
"DB2 V8.2 includes automated policy-based monitoring that can alert you
to the need for, or perform, automatic maintenance operations, such as
backup (which, by the way, is self-tuning in DB2 V8.2 and, for online
backups, will include the log files), table statistics collection
(which uses sampling and is 100% transparent to users and
applications), and online table reorganizations."

"The automatic reorganization feature can be enabled or disabled by
using the auto_reorg, auto_tbl_maint, and auto_maint database
configuration parameters.
If you are unsure about when and how to reorganize your tables and
indexes, you can incorporate automatic reorganization as part of your
overall database maintenance plan.
Tables considered for automatic reorganization are configurable by you
using the Automatic Maintenance wizard from the Control Center or
Health Center. "

maybe my auto_reorg, auto_tbl_maint, and auto_maint (which are all set
to ON) have not come into play yet as they were turned on and the
database requires stopping or all apps disconnecting....

All I have done is add tasks for each table such as

CONNECT TO XXXXX;
REORG TABLE ZZZZZZZZZZZ_YYYYYY INDEX
ZZZZZZZZZZZ_YYYYYYY INPLACE ALLOW WRITE ACCESS START ;
CONNECT RESET;
andy

[UDBDBA]

Andy:

Does it mean that we can now setup automatic online reorg?
I looked up online doc and as of V8.2 / FP7, the reorg was still
offline. Maybe i am missing a link here, can you provide a link to your
source doc?

Thanks much!

Vijay

[andy.standley]

Vijay
I have not made it work online yet - would be good to say I had!
But from what I read it seems like it is possible. As I said - maybe I
have set the cfg params but have to wait for a restart before they are
active and then it will be possible...

andy

[UDBDBA]

Andy:

Automatic maintenance related db cfg changes can be done online with
propagation class immediate. Can you send me the link to doc where it
specifies Automatic *online* reorg? The docs i have only talk about
automatic *offline* reorgs, online backups and online runstats.

I am also trying to setup a testcase.

Thanks much!

Vijay