By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,854 Members | 876 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,854 IT Pros & Developers. It's quick & easy.

Reorg on UDB

P: n/a
This question if for UDB on LUW. Suppose I take regular database
backups of my database

Monday: database backup <ts1>
Tuesday: Tablespace reorged and imagecopied <ts2>
Friday: database restored and roll-forwarded to a timestamp <ts3> on
Wednesday.

Now my question is: because the entire database is being
roll-forwarded, I suppose the database roll forward operation will
have to roll forward *through* the data reorganization (of tablespace
that was reorged on Tuesday) in the log. The fact that the tablespace
was imagecopied after
the reorganization doesn't add any value in this case. Is it correct?

Also, I did not find any option equivalent to the LOG NO option that
is
valid on DB2 for OS/390. Seems like the Reorg on UDB is always logged.
Must be pretty 'log consuming' and 'time consuming' for a table with
millions of rows.

TIA
Raquel.
Nov 12 '05 #1
Share this Question
Share on Google+
27 Replies


P: n/a
You are right but there are variations to this situation that might
help. I will presume you are running at V8.

1) You will reorg thru the roll forward in your scenario. However, when
you say image copy, if I understand backup the tblspc that held the
reorg table, then: You can restore the whole db, then restore the
tblspc. backup of Tues., then roll forward and you won't pay the reorg
cost thru roll forward and I believe it is a faster overall process.

2) To my knowledge, up to and including V8 a classic reorg command is
logged as well as metadata for the table at command issue time and
that's it. So logging should not be an issue.

3) Logging will be involved, in V8 and after, if, instead of a classic
reorg (no access to table) you do an INPLACE reorg with ALOOW WRITE. In
this case, as each extent is reorg'ed there is logging happening as this
option alloows you to A)Stop/Start B)Pause/Resume C) Fail with full
recovery available.

HTH, Pierre.

Raquel wrote:
This question if for UDB on LUW. Suppose I take regular database
backups of my database

Monday: database backup <ts1>
Tuesday: Tablespace reorged and imagecopied <ts2>
Friday: database restored and roll-forwarded to a timestamp <ts3> on
Wednesday.

Now my question is: because the entire database is being
roll-forwarded, I suppose the database roll forward operation will
have to roll forward *through* the data reorganization (of tablespace
that was reorged on Tuesday) in the log. The fact that the tablespace
was imagecopied after
the reorganization doesn't add any value in this case. Is it correct?

Also, I did not find any option equivalent to the LOG NO option that
is
valid on DB2 for OS/390. Seems like the Reorg on UDB is always logged.
Must be pretty 'log consuming' and 'time consuming' for a table with
millions of rows.

TIA
Raquel.


--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

Nov 12 '05 #2

P: n/a
Hi Raquel,
It's true that REORG does logging when moving data around, and that's one
good reason to always run a backup afterwards (the same is true for DB2 on
the mainframe, isn't ?). In case logging is an issue for you, IBM has
greatly enhanced it in DB2 V8 (infinite logging for instance). I understand
that you take a backup after the reorg. In that case, can't you restore the
database to that backup image ?
Mauro.

"Raquel" <ra****************@yahoo.com> wrote in message
news:9a**************************@posting.google.c om...
This question if for UDB on LUW. Suppose I take regular database
backups of my database

Monday: database backup <ts1>
Tuesday: Tablespace reorged and imagecopied <ts2>
Friday: database restored and roll-forwarded to a timestamp <ts3> on
Wednesday.

Now my question is: because the entire database is being
roll-forwarded, I suppose the database roll forward operation will
have to roll forward *through* the data reorganization (of tablespace
that was reorged on Tuesday) in the log. The fact that the tablespace
was imagecopied after
the reorganization doesn't add any value in this case. Is it correct?

Also, I did not find any option equivalent to the LOG NO option that
is
valid on DB2 for OS/390. Seems like the Reorg on UDB is always logged.
Must be pretty 'log consuming' and 'time consuming' for a table with
millions of rows.

TIA
Raquel.

Nov 12 '05 #3

P: n/a
"Mauro Cazzari" <ma***********@sas.com> wrote in message
news:bo**********@license1.unx.sas.com...
Hi Raquel,
It's true that REORG does logging when moving data around, and that's one
good reason to always run a backup afterwards (the same is true for DB2 on
the mainframe, isn't ?). In case logging is an issue for you, IBM has
greatly enhanced it in DB2 V8 (infinite logging for instance). I understand that you take a backup after the reorg. In that case, can't you restore the database to that backup image ?
Mauro.

On DB2 for OS/390 a reorg of the tablespace can generally be done without
logging (if specified that way). This puts the tablespace in copy pending
status, unless an image copy (backup) was specified to automatically be done
as part of the reorg. Tablespaces in copy pending status can be read but not
changed. The combination of not logging and doing an image copy instead is
about the same as a reorg with logging, and has the added advantage of a
more recent image copy for recovery purposes. Would be nice if DB2 LUW
worked this way.

There are some exceptions to the logging issue such as on-line reorgs in DB2
for OS/390, but that is a slightly different subject.

But the real question was about the overlap between a database backup and a
tablespace backup (at different times) when doing a roll-forward recovery.
On DB2 OS/390 all backups (image copies) are at the tablespace level, not
database (or sub-system).
Nov 12 '05 #4

P: n/a
"P. Saint-Jacques" <p.*********@videotron.ca> wrote in message news:<3F**************@videotron.ca>...
You are right but there are variations to this situation that might
help. I will presume you are running at V8.

1) You will reorg thru the roll forward in your scenario. However, when
you say image copy, if I understand backup the tblspc that held the
reorg table, then: You can restore the whole db, then restore the
tblspc. backup of Tues., then roll forward and you won't pay the reorg
cost thru roll forward and I believe it is a faster overall process.

2) To my knowledge, up to and including V8 a classic reorg command is
logged as well as metadata for the table at command issue time and
that's it. So logging should not be an issue.

3) Logging will be involved, in V8 and after, if, instead of a classic
reorg (no access to table) you do an INPLACE reorg with ALOOW WRITE. In
this case, as each extent is reorg'ed there is logging happening as this
option alloows you to A)Stop/Start B)Pause/Resume C) Fail with full
recovery available.

HTH, Pierre.

Hello Pierre,

Per your point 1 above, if I restore the whole database
(Monday's status), then restore the tablespace (Tuesday's status),
then rollforward the database and finally rollforward the tablespace,
the Database rollfoward will somehow 'skip' the log reorg log records
of the tablespace. Is that what you mean? How does UDB accomplish this
'skipping' business? When UDB 'starts' rollforward of the database,
does it first figure out what tablespaces are in rollforward pending
status and then while UDB is rollforwarding the database, it just does
not process the log records of those tablespaces?

I don't understand your second point at all. During a Reorg, the
RIDs of the entire data changes and so, UDB 'has' to log each and
every row of the table. This, I fathom, should be pretty 'log
consuming' as I said in my original mail, especially for large tables.
So, why do you say loggin is not an issue. On DB2 for OS/390, we
always use LOG NO for performing Reorg on large tables precisely
because of logging issues. How is it different for UDB. I am surprised
there is no LOG NO option on UDB.

Looking for some insights.

TIA
Raquel.
Nov 12 '05 #5

P: n/a
Pierre is correct, classic (not inplace, no concurrent update) reorg
does not need to log any row data/rids etc.. Logging only occurs at the
start, and end of reorg. This is because the reorg is done to a brand
new copy of the table. All indexes are rebuilt at the end of the
operation, when the new copy of the table is moved over top of the old.

New reorg (inplace) is just like an application moving around row data,
and must log rid/row data for recovery. It also keeps all indexes on the
thable in sync with the rids of the table at any point in time.
I don't understand your second point at all. During a Reorg, the
RIDs of the entire data changes and so, UDB 'has' to log each and
every row of the table. This, I fathom, should be pretty 'log
consuming' as I said in my original mail, especially for large tables.
So, why do you say loggin is not an issue. On DB2 for OS/390, we
always use LOG NO for performing Reorg on large tables precisely
because of logging issues. How is it different for UDB. I am surprised
there is no LOG NO option on UDB.

Raquel wrote: "P. Saint-Jacques" <p.*********@videotron.ca> wrote in message news:<3F**************@videotron.ca>...
You are right but there are variations to this situation that might
help. I will presume you are running at V8.

1) You will reorg thru the roll forward in your scenario. However, when
you say image copy, if I understand backup the tblspc that held the
reorg table, then: You can restore the whole db, then restore the
tblspc. backup of Tues., then roll forward and you won't pay the reorg
cost thru roll forward and I believe it is a faster overall process.

2) To my knowledge, up to and including V8 a classic reorg command is
logged as well as metadata for the table at command issue time and
that's it. So logging should not be an issue.

3) Logging will be involved, in V8 and after, if, instead of a classic
reorg (no access to table) you do an INPLACE reorg with ALOOW WRITE. In
this case, as each extent is reorg'ed there is logging happening as this
option alloows you to A)Stop/Start B)Pause/Resume C) Fail with full
recovery available.

HTH, Pierre.


Hello Pierre,

Per your point 1 above, if I restore the whole database
(Monday's status), then restore the tablespace (Tuesday's status),
then rollforward the database and finally rollforward the tablespace,
the Database rollfoward will somehow 'skip' the log reorg log records
of the tablespace. Is that what you mean? How does UDB accomplish this
'skipping' business? When UDB 'starts' rollforward of the database,
does it first figure out what tablespaces are in rollforward pending
status and then while UDB is rollforwarding the database, it just does
not process the log records of those tablespaces?

I don't understand your second point at all. During a Reorg, the
RIDs of the entire data changes and so, UDB 'has' to log each and
every row of the table. This, I fathom, should be pretty 'log
consuming' as I said in my original mail, especially for large tables.
So, why do you say loggin is not an issue. On DB2 for OS/390, we
always use LOG NO for performing Reorg on large tables precisely
because of logging issues. How is it different for UDB. I am surprised
there is no LOG NO option on UDB.

Looking for some insights.

TIA
Raquel.


Nov 12 '05 #6

P: n/a
"Sean McKeough" <mc******@nospam.ca.ibm.com> wrote in message
news:bp**********@hanover.torolab.ibm.com...
Pierre is correct, classic (not inplace, no concurrent update) reorg
does not need to log any row data/rids etc.. Logging only occurs at the
start, and end of reorg. This is because the reorg is done to a brand
new copy of the table. All indexes are rebuilt at the end of the
operation, when the new copy of the table is moved over top of the old.

New reorg (inplace) is just like an application moving around row data,
and must log rid/row data for recovery. It also keeps all indexes on the
thable in sync with the rids of the table at any point in time.

What does that mean "Logging only occurs at the start, and end of reorg."
Does it log the entire tablespace? Seems like it would be nice if there was
no logging at all and a tablespace backup was created like on DB2 z/OS.

Will DB2 LUW ever go to a model like DB2 for z/OS where the system tables
can be backed up without the data, and then one can backup and recover each
tablespace independently if desired?
Nov 12 '05 #7

P: n/a
I'm familiar with the way image copies work on the mainframe. And they can
work at the index level too, not just at the tablespace level. In addition,
DB2 V8 introduces the possibility to run backups and restores at the system
level.
Mauro.

"Mark A" <ma@switchboard.net> wrote in message
news:e0*****************@news.uswest.net...
"Mauro Cazzari" <ma***********@sas.com> wrote in message
news:bo**********@license1.unx.sas.com...
Hi Raquel,
It's true that REORG does logging when moving data around, and that's one good reason to always run a backup afterwards (the same is true for DB2 on the mainframe, isn't ?). In case logging is an issue for you, IBM has
greatly enhanced it in DB2 V8 (infinite logging for instance). I understand
that you take a backup after the reorg. In that case, can't you restore

the
database to that backup image ?
Mauro.

On DB2 for OS/390 a reorg of the tablespace can generally be done without
logging (if specified that way). This puts the tablespace in copy pending
status, unless an image copy (backup) was specified to automatically be

done as part of the reorg. Tablespaces in copy pending status can be read but not changed. The combination of not logging and doing an image copy instead is
about the same as a reorg with logging, and has the added advantage of a
more recent image copy for recovery purposes. Would be nice if DB2 LUW
worked this way.

There are some exceptions to the logging issue such as on-line reorgs in DB2 for OS/390, but that is a slightly different subject.

But the real question was about the overlap between a database backup and a tablespace backup (at different times) when doing a roll-forward recovery.
On DB2 OS/390 all backups (image copies) are at the tablespace level, not
database (or sub-system).

Nov 12 '05 #8

P: n/a
Hi Raquel,
There is no doubt that logging may play a role while reorganizing objects in
DB2/UDB. However, only the pages that are moved are logged. This means that
the more frequently you run reorgs, the lesser the probability that large
amounts of data will be logged. In addition to that, you may want to
consider using incremental backups to speed up a restore process, minimizing
the elapsed time that DB2 needs to spend re-applying log records during a
rollforward operation.
Mauro.

"Raquel" <ra****************@yahoo.com> wrote in message
news:9a**************************@posting.google.c om...
"P. Saint-Jacques" <p.*********@videotron.ca> wrote in message

news:<3F**************@videotron.ca>...
You are right but there are variations to this situation that might
help. I will presume you are running at V8.

1) You will reorg thru the roll forward in your scenario. However, when
you say image copy, if I understand backup the tblspc that held the
reorg table, then: You can restore the whole db, then restore the
tblspc. backup of Tues., then roll forward and you won't pay the reorg
cost thru roll forward and I believe it is a faster overall process.

2) To my knowledge, up to and including V8 a classic reorg command is
logged as well as metadata for the table at command issue time and
that's it. So logging should not be an issue.

3) Logging will be involved, in V8 and after, if, instead of a classic
reorg (no access to table) you do an INPLACE reorg with ALOOW WRITE. In
this case, as each extent is reorg'ed there is logging happening as this
option alloows you to A)Stop/Start B)Pause/Resume C) Fail with full
recovery available.

HTH, Pierre.

Hello Pierre,

Per your point 1 above, if I restore the whole database
(Monday's status), then restore the tablespace (Tuesday's status),
then rollforward the database and finally rollforward the tablespace,
the Database rollfoward will somehow 'skip' the log reorg log records
of the tablespace. Is that what you mean? How does UDB accomplish this
'skipping' business? When UDB 'starts' rollforward of the database,
does it first figure out what tablespaces are in rollforward pending
status and then while UDB is rollforwarding the database, it just does
not process the log records of those tablespaces?

I don't understand your second point at all. During a Reorg, the
RIDs of the entire data changes and so, UDB 'has' to log each and
every row of the table. This, I fathom, should be pretty 'log
consuming' as I said in my original mail, especially for large tables.
So, why do you say loggin is not an issue. On DB2 for OS/390, we
always use LOG NO for performing Reorg on large tables precisely
because of logging issues. How is it different for UDB. I am surprised
there is no LOG NO option on UDB.

Looking for some insights.

TIA
Raquel.

Nov 12 '05 #9

P: n/a
Mark,

I was partially wrong in my first posting..._less_ data is logged per
row for an offline _clustering_ reorg, but data is logged nontheless.
For non-clustering, we have the 'optimal' logging case.

At a minimum, at reorg startup, some auxilliary data is logged (enough
to know when the 'new' reorg files exist and need to be cleaned up
etc)...a few 100 bytes. At reorg complete, we log another similar record
so we know whether the reorg finished. If lobs/longs were reorged, then
a few other small records are written to remember the lob file page 0
info. For a 'not inplace' clustering reorg, the rids of the rows copied
are logged. 8000 rids are stored at a time, before the log record is cut.

For inplace reorg the entire row must be logged when its moved.

Mark A wrote:
"Sean McKeough" <mc******@nospam.ca.ibm.com> wrote in message
news:bp**********@hanover.torolab.ibm.com...
Pierre is correct, classic (not inplace, no concurrent update) reorg
does not need to log any row data/rids etc.. Logging only occurs at the
start, and end of reorg. This is because the reorg is done to a brand
new copy of the table. All indexes are rebuilt at the end of the
operation, when the new copy of the table is moved over top of the old.

New reorg (inplace) is just like an application moving around row data,
and must log rid/row data for recovery. It also keeps all indexes on the
thable in sync with the rids of the table at any point in time.


What does that mean "Logging only occurs at the start, and end of reorg."
Does it log the entire tablespace? Seems like it would be nice if there was
no logging at all and a tablespace backup was created like on DB2 z/OS.

Will DB2 LUW ever go to a model like DB2 for z/OS where the system tables
can be backed up without the data, and then one can backup and recover each
tablespace independently if desired?


Nov 12 '05 #10

P: n/a
See again folded in text #######

Raquel wrote:
....clipped

Hello Pierre,

Per your point 1 above, if I restore the whole database
(Monday's status), then restore the tablespace (Tuesday's status),
then rollforward the database and finally rollforward the tablespace,
the Database rollfoward will somehow 'skip' the log reorg log records
of the tablespace. Is that what you mean? ####### Not quite. I said restore both images, one after another. At
that point, the db is consistent as of Mon. and the tblsp. is consistent
as of Tue. When you start the roll forward, all transactions from Mon.
to Tue. that address the tblsp. are IGNORED as they are in the Tue.'s
image. When the roll forward hits the timestamp of the tblsp. backup in
the log file, it then picks up all trans. from Tues. backup timestamp to
end of logs and everything ends up consistent.
How does UDB accomplish this 'skipping' business? When UDB 'starts' rollforward of the database,
does it first figure out what tablespaces are in rollforward pending
status and then while UDB is rollforwarding the database, it just does
not process the log records of those tablespaces?

I don't understand your second point at all. During a Reorg, the
RIDs of the entire data changes and so, UDB 'has' to log each and
every row of the table. ######## When you do a classic reorg. DB2 uses a temp tblspc. Acopy of
the table is in the temp.. It is reorg, cleaned, sorted, clustered,
stapled, folded and mutialted. When finished, the indexes are totally
rebuilt and then everything is copied back. In V7, DB2 holds super
exclusive locks on the table and tblspc, so no logging required. In V8,
DB2 will allow read access. This is actually on the old, non-reorg
data. At the end of the reorg, DB2 will then grab a super exclusive
lock, after reads have terminated, to copy back the new stuff.

Therefore, no logging and why there's no NO LOG option. If you were to
crash the system, the DB2 UDB requirements are that you start the reorg
from the ground up on the old non-involved table. Also, remember that
DB2 z/OS reorgs are at the tblspc. level ( as I remember) and DB2 UDB
LUW is at the table level.

Logging will only be involved if you do a V8 reorg and use the INPLACE
parm, which will allow read/write access to the table.
This, I fathom, should be pretty 'log consuming' as I said in my original mail, especially for large tables.
So, why do you say loggin is not an issue. On DB2 for OS/390, we
always use LOG NO for performing Reorg on large tables precisely
because of logging issues. How is it different for UDB. I am surprised
there is no LOG NO option on UDB.

Looking for some insights.

TIA
Raquel.

HTH, Pierre.
PS: This is what we tach in the DB2 IBM classes.
--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

Nov 12 '05 #11

P: n/a
Sean McKeough <mc******@nospam.ca.ibm.com> wrote:
Mark,

I was partially wrong in my first posting..._less_ data is logged per
row for an offline _clustering_ reorg, but data is logged nontheless.
For non-clustering, we have the 'optimal' logging case.


There is a free tool available with which you can have a look at the log
records written by DB2. You cannot decode all the information in all the
log records, and it was originally written for V5, but it is still working.
You can use it to see at least how many log records were written and for
some also some more information.

Have a look here: http://www.minet.uni-jena.de/dbis/db2log/

p.s: With a few changes, I got this working on V8 using the db2ReadLog()
API. You can probably extend it to use the db2ReadLogNoConn() API - I am
playing around with this at the moment, but it will take some more time
until I complete this part.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #12

P: n/a


Sean McKeough wrote:
New reorg (inplace) is just like an application moving around row data,
and must log rid/row data for recovery. It also keeps all indexes on the
thable in sync with the rids of the table at any point in time.


Online index reorg and Online table reorg are separate reorg processes.
If I only do an Online table Reorg, that means the RIDs of part of the table rows will be changed, how to
make the indexes in sync with the rids of the table?
Regards,
FRX

Nov 12 '05 #13

P: n/a


Mark A wrote:

On DB2 for OS/390 a reorg of the tablespace can generally be done without
logging (if specified that way). This puts the tablespace in copy pending
status, unless an image copy (backup) was specified to automatically be done
as part of the reorg. Tablespaces in copy pending status can be read but not
changed. The combination of not logging and doing an image copy instead is
about the same as a reorg with logging, and has the added advantage of a
more recent image copy for recovery purposes. Would be nice if DB2 LUW
worked this way.
So the reorg you mentioned here use the same strategy as Online reorg in DB2 for
OS/390???
Online reorg use Logging to catch up those update during the whole reorg
process.


There are some exceptions to the logging issue such as on-line reorgs in DB2
for OS/390, but that is a slightly different subject.

But the real question was about the overlap between a database backup and a
tablespace backup (at different times) when doing a roll-forward recovery.
On DB2 OS/390 all backups (image copies) are at the tablespace level, not
database (or sub-system).


Nov 12 '05 #14

P: n/a
You're right, the indexes rids must be kept in sync for an inplace
reorg...they're updated as part of row movement. However, this movement
doesn't do the things that an index reorg does (reclaim unused space etc.).

Fan Ruo Xin wrote:

Sean McKeough wrote:

New reorg (inplace) is just like an application moving around row data,
and must log rid/row data for recovery. It also keeps all indexes on the
thable in sync with the rids of the table at any point in time.

Online index reorg and Online table reorg are separate reorg processes.
If I only do an Online table Reorg, that means the RIDs of part of the table rows will be changed, how to
make the indexes in sync with the rids of the table?
Regards,
FRX


Nov 12 '05 #15

P: n/a
Hi Pierre,

I REALLY think there is something missing here. Firstly there is NO
relation between locking and logging (your mail says that in a classic
reorg, a super exclusive lock is acquired and *hence* no logging is
done).

As you have stated, during reorg, DB2 vigorously moves around
(almost)
all the rows in a table to get things right (perfectly clustered, free
space restored etc. etc.). Well, when rows move, their RIDSs change
and
ANY RID change HAS to be logged by DB2 (so, in essence, all the rows
have to be logged). Normally RIDs are a combination of the 'Page
number'
on which the row resides and another number that tells DB2 the
location
of that row within that page. This is how DB2 knows where a row is
located during rollforward or any other recovery that uses log.

I don't see how DB2 can do 'without' logging all the rows in a Reorg.
Consider this simple case for a row that is located on page5
initially:

1. A program updates the row at time t1. DB2 logs that the row in
page5
has been updated.
2. Table is reorged at time t2 and as a consequence the row moves from
page5 to page8. Also, suppose that this fact (that the row was moved
from page5 to page8) was not logged by DB2. So, the log has no idea
that
the row has moved from page5 to page8.
3. At time t3 (after reorg has finished), a program updates this row.
DB2 logs that the row in page8 has been updated.

Now suppose we were to do a rollforward recovery of this tablespace
to time t2. Following will happen with the row:

1. DB2 reads in the log at time t1 that row on page5 had been updated
and so, it goes to page5 and performs the update. At the end of this
step, in the tablespace, row is on page5.
2. DB2 doesn't know anything about the movement of row from page5 to
page8 as a result of reorg (because it was not logged). So, DB2 does
NOT
move the row R1 from page5 to page8. At the end of this step, row is
STILL at page5 in the tablespace.
3. DB2 reads in the log at time t3 that row had been updated on page8.
So, it goes to page8 to update the row...well..it doesn't find R1 on
page8 (because as demonstrated by step 2, in the tablespace, row is on
page5 and not on page8). So, this is an INCONSISTENCY.

Had DB2 performed logging at step2 and logged the movement of row from
page5 to page8, as a part of rollforward, DB2 would have moved row
from
page5 to page8 in step2 of rollforward and then, at step3, would have
found row on page8 and performed the update. No inconsistency.

Hopefully you see my point here and let me know where I am wrong.

TIA
Raquel.
Nov 12 '05 #16

P: n/a
Raquel, I agree and disagree.
I did get confused with Lock and logging.
What I meant to say was that because of superexclusive lock, no access
was permitted, which gave DB2 the ability to work on a copy (remember,
I'M talking about a classic reorg in UDB for LUW) and therefore not have
to log.

The way I learned (and maybe that's wrong but...) the classic reorg with
no access works: A copy of the table is made in tempspace and that is
what is reorg'ed. Since the original table is there with its indexes,
no logging while reorg is required. Fail the process at any point in
time, and DB2 will come back up with the old non-reorged table. If
system fails after, then you have the new table.
At start, the lock is acquired, the table is cleaned, sorted, copied,
the indexes are rebuilt and then and only then is the process deemed to
finished, locks released and so on...
I do agree that if you do an INPLACE reorg, all this works differently
and you do get logging. As to the extent, I need to look at the other
answers and talk to the people I know at the lab to determine theextent
and the performance hit.
Regards, Pierre.

Raquel wrote:
Hi Pierre,

I REALLY think there is something missing here. Firstly there is NO
relation between locking and logging (your mail says that in a classic
reorg, a super exclusive lock is acquired and *hence* no logging is
done).

As you have stated, during reorg, DB2 vigorously moves around
(almost)
all the rows in a table to get things right (perfectly clustered, free
space restored etc. etc.). Well, when rows move, their RIDSs change
and
ANY RID change HAS to be logged by DB2 (so, in essence, all the rows
have to be logged). Normally RIDs are a combination of the 'Page
number'
on which the row resides and another number that tells DB2 the
location
of that row within that page. This is how DB2 knows where a row is
located during rollforward or any other recovery that uses log.

I don't see how DB2 can do 'without' logging all the rows in a Reorg.
Consider this simple case for a row that is located on page5
initially:

1. A program updates the row at time t1. DB2 logs that the row in
page5
has been updated.
2. Table is reorged at time t2 and as a consequence the row moves from
page5 to page8. Also, suppose that this fact (that the row was moved
from page5 to page8) was not logged by DB2. So, the log has no idea
that
the row has moved from page5 to page8.
3. At time t3 (after reorg has finished), a program updates this row.
DB2 logs that the row in page8 has been updated.

Now suppose we were to do a rollforward recovery of this tablespace
to time t2. Following will happen with the row:

1. DB2 reads in the log at time t1 that row on page5 had been updated
and so, it goes to page5 and performs the update. At the end of this
step, in the tablespace, row is on page5.
2. DB2 doesn't know anything about the movement of row from page5 to
page8 as a result of reorg (because it was not logged). So, DB2 does
NOT
move the row R1 from page5 to page8. At the end of this step, row is
STILL at page5 in the tablespace.
3. DB2 reads in the log at time t3 that row had been updated on page8.
So, it goes to page8 to update the row...well..it doesn't find R1 on
page8 (because as demonstrated by step 2, in the tablespace, row is on
page5 and not on page8). So, this is an INCONSISTENCY.

Had DB2 performed logging at step2 and logged the movement of row from
page5 to page8, as a part of rollforward, DB2 would have moved row
from
page5 to page8 in step2 of rollforward and then, at step3, would have
found row on page8 and performed the update. No inconsistency.

Hopefully you see my point here and let me know where I am wrong.

TIA
Raquel.


--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

Nov 12 '05 #17

P: n/a
"P. Saint-Jacques" <p.*********@videotron.ca> wrote in message
news:3F**************@videotron.ca...
Raquel, I agree and disagree.
I did get confused with Lock and logging.
What I meant to say was that because of superexclusive lock, no access
was permitted, which gave DB2 the ability to work on a copy (remember,
I'M talking about a classic reorg in UDB for LUW) and therefore not have
to log.

The way I learned (and maybe that's wrong but...) the classic reorg with
no access works: A copy of the table is made in tempspace and that is
what is reorg'ed. Since the original table is there with its indexes,
no logging while reorg is required. Fail the process at any point in
time, and DB2 will come back up with the old non-reorged table. If
system fails after, then you have the new table.
At start, the lock is acquired, the table is cleaned, sorted, copied,
the indexes are rebuilt and then and only then is the process deemed to
finished, locks released and so on...
I do agree that if you do an INPLACE reorg, all this works differently
and you do get logging. As to the extent, I need to look at the other
answers and talk to the people I know at the lab to determine theextent
and the performance hit.
Regards, Pierre.

Maybe it's my ignorance, but that seems confusing. If a reorg is performed
and the table is not in backup pending status, then it must have been logged
(in some manner) during the reorg to allow others to update it when the
reorg is completed.

On DB2 z/OS, an image copy is performed (as part of the reorg or right after
depending on which release) to allow the tablespace to be updated. Otherwise
it would be non-recoverable. But maybe I am missing something about how DB2
LUW works in this regard.
Nov 12 '05 #18

P: n/a
Mark, the reorg command is logged with metadata concerning the table.
During the reorg "classic" in UDB for LUW, the table is locked
exclusively to the reorger. When the reorg is completed, an entry is
made in the log files and the table becomes available. That logging is
minimal and very brief. There's no requirement to backup, unless your
own procedures say so.
There should be nothing about the table, rids and whatewver logged
during the reorg as all the work is performed on a copy which is done in
the tblspc. specified in the command.
I do agree that if you do not specify a "use <tblspcname>" in the
command the reorg is in place and logging will happen as rows are moved
and rids are maintained.
HTH, Pierre.

Mark A wrote:
"P. Saint-Jacques" <p.*********@videotron.ca> wrote in message
news:3F**************@videotron.ca...
Raquel, I agree and disagree.
....clipped
Maybe it's my ignorance, but that seems confusing. If a reorg is performed
and the table is not in backup pending status, then it must have been logged
(in some manner) during the reorg to allow others to update it when the
reorg is completed.

On DB2 z/OS, an image copy is performed (as part of the reorg or right after
depending on which release) to allow the tablespace to be updated. Otherwise
it would be non-recoverable. But maybe I am missing something about how DB2
LUW works in this regard.


--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

Nov 12 '05 #19

P: n/a
Just one point of clarification,

It is not the 'use <tblspacname>' that makes the reorg in place or
not...it is the use of the 'inplace' keyword. The use parm is only
needed if you don't have room in your container for the copy of the
table. If no tempspace is specified via the 'use' parameter, then the
copy of the table is made in the same container as the table...still no
logging of table data (no changes are made to the table being reorged
until the reorg completes, and the copied table is moved on top of the
original table), except in the clustering case as I described earlier.
As also noted previously, indexes are rebuilt after the copy, so no need
to keep them in sync/logged during the reorg.

P. Saint-Jacques wrote:
Mark, the reorg command is logged with metadata concerning the table.
During the reorg "classic" in UDB for LUW, the table is locked
exclusively to the reorger. When the reorg is completed, an entry is
made in the log files and the table becomes available. That logging is
minimal and very brief. There's no requirement to backup, unless your
own procedures say so.
There should be nothing about the table, rids and whatewver logged
during the reorg as all the work is performed on a copy which is done in
the tblspc. specified in the command.
I do agree that if you do not specify a "use <tblspcname>" in the
command the reorg is in place and logging will happen as rows are moved
and rids are maintained.
HTH, Pierre.

Mark A wrote:
"P. Saint-Jacques" <p.*********@videotron.ca> wrote in message
news:3F**************@videotron.ca...
Raquel, I agree and disagree.


...clipped
Maybe it's my ignorance, but that seems confusing. If a reorg is
performed
and the table is not in backup pending status, then it must have been
logged
(in some manner) during the reorg to allow others to update it when the
reorg is completed.

On DB2 z/OS, an image copy is performed (as part of the reorg or right
after
depending on which release) to allow the tablespace to be updated.
Otherwise
it would be non-recoverable. But maybe I am missing something about
how DB2
LUW works in this regard.


Nov 12 '05 #20

P: n/a
This is not how UWO's online reorg works...UWO's online reorg is
inplace...there is no log catchup etc...think of it as an application
that's driving row updates.
So the reorg you mentioned here use the same strategy as Online reorg in DB2 for OS/390???
Online reorg use Logging to catch up those update during the whole reorg
process.

Fan Ruo Xin wrote:

Mark A wrote:

On DB2 for OS/390 a reorg of the tablespace can generally be done without
logging (if specified that way). This puts the tablespace in copy pending
status, unless an image copy (backup) was specified to automatically be done
as part of the reorg. Tablespaces in copy pending status can be read but not
changed. The combination of not logging and doing an image copy instead is
about the same as a reorg with logging, and has the added advantage of a
more recent image copy for recovery purposes. Would be nice if DB2 LUW
worked this way.


There are some exceptions to the logging issue such as on-line reorgs in DB2
for OS/390, but that is a slightly different subject.

But the real question was about the overlap between a database backup and a
tablespace backup (at different times) when doing a roll-forward recovery.
On DB2 OS/390 all backups (image copies) are at the tablespace level, not
database (or sub-system).



Nov 12 '05 #21

P: n/a
Point taken and I stand corrected and should have been clearer.
Thanks, Pierre.

Sean McKeough wrote:
Just one point of clarification,

It is not the 'use <tblspacname>' that makes the reorg in place or
not...it is the use of the 'inplace' keyword. ....clipped


Nov 12 '05 #22

P: n/a
Hi, Sean,
I thought we talked the same thing. I mean the db2 (offline) reorg for OS/390 and
db2 online reorg for OS/390 used the same policy - shadow copy.
I liked the idea that DB2 UDB for LUW didn't use this approach for the ONLINE
TABLE REORG.
I am thinking it will be more convenient, if -
(1). For the Online Index Reorg, if we can add a small piece step - minus the
number of pages which shadow index tree used from the HWM - as the last step of
the Switch Over phase.
(2). For the big table or index reorg, if we can allow the user to specify a
message log file, other than take the snapshot to monitor the reorg operations.
Regards,
FRX

Sean McKeough wrote:
This is not how UWO's online reorg works...UWO's online reorg is
inplace...there is no log catchup etc...think of it as an application
that's driving row updates.
> So the reorg you mentioned here use the same strategy as Online reorg

in DB2 for
> OS/390???
> Online reorg use Logging to catch up those update during the whole reorg
> process.
>


Fan Ruo Xin wrote:

Mark A wrote:

On DB2 for OS/390 a reorg of the tablespace can generally be done without
logging (if specified that way). This puts the tablespace in copy pending
status, unless an image copy (backup) was specified to automatically be done
as part of the reorg. Tablespaces in copy pending status can be read but not
changed. The combination of not logging and doing an image copy instead is
about the same as a reorg with logging, and has the added advantage of a
more recent image copy for recovery purposes. Would be nice if DB2 LUW
worked this way.



There are some exceptions to the logging issue such as on-line reorgs in DB2
for OS/390, but that is a slightly different subject.

But the real question was about the overlap between a database backup and a
tablespace backup (at different times) when doing a roll-forward recovery.
On DB2 OS/390 all backups (image copies) are at the tablespace level, not
database (or sub-system).



Nov 12 '05 #23

P: n/a
Thank you, Sean.
Regards,
FRX

Sean McKeough wrote:
You're right, the indexes rids must be kept in sync for an inplace
reorg...they're updated as part of row movement. However, this movement
doesn't do the things that an index reorg does (reclaim unused space etc.).

Fan Ruo Xin wrote:

Sean McKeough wrote:

New reorg (inplace) is just like an application moving around row data,
and must log rid/row data for recovery. It also keeps all indexes on the
thable in sync with the rids of the table at any point in time.

Online index reorg and Online table reorg are separate reorg processes.
If I only do an Online table Reorg, that means the RIDs of part of the table rows will be changed, how to
make the indexes in sync with the rids of the table?
Regards,
FRX


Nov 12 '05 #24

P: n/a
Fan,

One big differnce between the zOS online, and our offline reorgs is that
the LUW offline reorg doesn't need to do log catchup, since no changes
can be made to the table during the copy process. (You probably knew
this already) :-) .

I don't understand question #1...can you please clarify?

For 2) The implementers chose snapshot to display incremental progress
because it can be accessed through clp, or the new admin table functions
(SQL) etc...it would not be hard to write an application on top of the
monitor apis (or SQL) that periodically populated a file
somewhere...there's also the history file for tracking this stuff
overall (but obviously not good granularity).

Fan Ruo Xin wrote:
Hi, Sean,
I thought we talked the same thing. I mean the db2 (offline) reorg for OS/390 and
db2 online reorg for OS/390 used the same policy - shadow copy.
I liked the idea that DB2 UDB for LUW didn't use this approach for the ONLINE
TABLE REORG.
I am thinking it will be more convenient, if -
(1). For the Online Index Reorg, if we can add a small piece step - minus the
number of pages which shadow index tree used from the HWM - as the last step of
the Switch Over phase.
(2). For the big table or index reorg, if we can allow the user to specify a
message log file, other than take the snapshot to monitor the reorg operations.
Regards,
FRX

Sean McKeough wrote:

This is not how UWO's online reorg works...UWO's online reorg is
inplace...there is no log catchup etc...think of it as an application
that's driving row updates.
> So the reorg you mentioned here use the same strategy as Online reorg

in DB2 for
> OS/390???
> Online reorg use Logging to catch up those update during the whole reorg
> process.
>


Fan Ruo Xin wrote:

Mark A wrote:

On DB2 for OS/390 a reorg of the tablespace can generally be done without
logging (if specified that way). This puts the tablespace in copy pending
status, unless an image copy (backup) was specified to automatically be done
as part of the reorg. Tablespaces in copy pending status can be read but not
changed. The combination of not logging and doing an image copy instead is
about the same as a reorg with logging, and has the added advantage of a
more recent image copy for recovery purposes. Would be nice if DB2 LUW
worked this way.
There are some exceptions to the logging issue such as on-line reorgs in DB2
for OS/390, but that is a slightly different subject.

But the real question was about the overlap between a database backup and a
tablespace backup (at different times) when doing a roll-forward recovery.
On DB2 OS/390 all backups (image copies) are at the tablespace level, not
database (or sub-system).


Nov 12 '05 #25

P: n/a
Hi, Sean,
I mean when we do Online Index Reorg, the tablespace need to be big enough to hold both
index trees and shadow index trees, so the HWM is almost the double size of the
original index trees. Is that possible if we can lower the HWM to the final index trees
size?
BTW, which part changed to the large space to allow it to store index pages?
Regards,
FRX
Sean McKeough wrote:
Fan,

One big differnce between the zOS online, and our offline reorgs is that
the LUW offline reorg doesn't need to do log catchup, since no changes
can be made to the table during the copy process. (You probably knew
this already) :-) .

I don't understand question #1...can you please clarify?

For 2) The implementers chose snapshot to display incremental progress
because it can be accessed through clp, or the new admin table functions
(SQL) etc...it would not be hard to write an application on top of the
monitor apis (or SQL) that periodically populated a file
somewhere...there's also the history file for tracking this stuff
overall (but obviously not good granularity).

Fan Ruo Xin wrote:
Hi, Sean,
I thought we talked the same thing. I mean the db2 (offline) reorg for OS/390 and
db2 online reorg for OS/390 used the same policy - shadow copy.
I liked the idea that DB2 UDB for LUW didn't use this approach for the ONLINE
TABLE REORG.
I am thinking it will be more convenient, if -
(1). For the Online Index Reorg, if we can add a small piece step - minus the
number of pages which shadow index tree used from the HWM - as the last step of
the Switch Over phase.
(2). For the big table or index reorg, if we can allow the user to specify a
message log file, other than take the snapshot to monitor the reorg operations.
Regards,
FRX

Sean McKeough wrote:

This is not how UWO's online reorg works...UWO's online reorg is
inplace...there is no log catchup etc...think of it as an application
that's driving row updates.

> So the reorg you mentioned here use the same strategy as Online reorg
in DB2 for
> OS/390???
> Online reorg use Logging to catch up those update during the whole reorg
> process.
>

Fan Ruo Xin wrote:
Mark A wrote:

>On DB2 for OS/390 a reorg of the tablespace can generally be done without
>logging (if specified that way). This puts the tablespace in copy pending
>status, unless an image copy (backup) was specified to automatically be done
>as part of the reorg. Tablespaces in copy pending status can be read but not
>changed. The combination of not logging and doing an image copy instead is
>about the same as a reorg with logging, and has the added advantage of a
>more recent image copy for recovery purposes. Would be nice if DB2 LUW
>worked this way.
>There are some exceptions to the logging issue such as on-line reorgs in DB2
>for OS/390, but that is a slightly different subject.
>
>But the real question was about the overlap between a database backup and a
>tablespace backup (at different times) when doing a roll-forward recovery.
>On DB2 OS/390 all backups (image copies) are at the tablespace level, not
>database (or sub-system).


Nov 12 '05 #26

P: n/a
Hi Fan,

Ok, got it...unfortunately, for the online (with shadow copy) approach
that was chosen, we really do need the 2x space. I discussed this with
the developers who know about this work, if you're really concerned
about the high water mark you have only a few options:
1) run the reorg with no-access (the only inplace algorithm available)
2) run a second online reorg of the index...this reorg will likely move
the shadow copy back to where the original index was.

Fan Ruo Xin wrote:
Hi, Sean,
I mean when we do Online Index Reorg, the tablespace need to be big enough to hold both
index trees and shadow index trees, so the HWM is almost the double size of the
original index trees. Is that possible if we can lower the HWM to the final index trees
size?
BTW, which part changed to the large space to allow it to store index pages?
Regards,
FRX
Sean McKeough wrote:

Fan,

One big differnce between the zOS online, and our offline reorgs is that
the LUW offline reorg doesn't need to do log catchup, since no changes
can be made to the table during the copy process. (You probably knew
this already) :-) .

I don't understand question #1...can you please clarify?

For 2) The implementers chose snapshot to display incremental progress
because it can be accessed through clp, or the new admin table functions
(SQL) etc...it would not be hard to write an application on top of the
monitor apis (or SQL) that periodically populated a file
somewhere...there's also the history file for tracking this stuff
overall (but obviously not good granularity).

Fan Ruo Xin wrote:
Hi, Sean,
I thought we talked the same thing. I mean the db2 (offline) reorg for OS/390 and
db2 online reorg for OS/390 used the same policy - shadow copy.
I liked the idea that DB2 UDB for LUW didn't use this approach for the ONLINE
TABLE REORG.
I am thinking it will be more convenient, if -
(1). For the Online Index Reorg, if we can add a small piece step - minus the
number of pages which shadow index tree used from the HWM - as the last step of
the Switch Over phase.
(2). For the big table or index reorg, if we can allow the user to specify a
message log file, other than take the snapshot to monitor the reorg operations.
Regards,
FRX

Sean McKeough wrote:

This is not how UWO's online reorg works...UWO's online reorg is
inplace...there is no log catchup etc...think of it as an application
that's driving row updates.
>So the reorg you mentioned here use the same strategy as Online reorg

in DB2 for

>OS/390???
>Online reorg use Logging to catch up those update during the whole reorg
>process.
>

Fan Ruo Xin wrote:

>Mark A wrote:
>
>
>
>
>>On DB2 for OS/390 a reorg of the tablespace can generally be done without
>>logging (if specified that way). This puts the tablespace in copy pending
>>status, unless an image copy (backup) was specified to automatically be done
>>as part of the reorg. Tablespaces in copy pending status can be read but not
>>changed. The combination of not logging and doing an image copy instead is
>>about the same as a reorg with logging, and has the added advantage of a
>>more recent image copy for recovery purposes. Would be nice if DB2 LUW
>>worked this way.
>
>
>>There are some exceptions to the logging issue such as on-line reorgs in DB2
>>for OS/390, but that is a slightly different subject.
>>
>>But the real question was about the overlap between a database backup and a
>>tablespace backup (at different times) when doing a roll-forward recovery.
>>On DB2 OS/390 all backups (image copies) are at the tablespace level, not
>>database (or sub-system).
>
>


Nov 12 '05 #27

P: n/a
Thank you, Sean. That's very helpful.
Regards,
FRX

Sean McKeough wrote:
Hi Fan,

Ok, got it...unfortunately, for the online (with shadow copy) approach
that was chosen, we really do need the 2x space. I discussed this with
the developers who know about this work, if you're really concerned
about the high water mark you have only a few options:
1) run the reorg with no-access (the only inplace algorithm available)
2) run a second online reorg of the index...this reorg will likely move
the shadow copy back to where the original index was.

Fan Ruo Xin wrote:
Hi, Sean,
I mean when we do Online Index Reorg, the tablespace need to be big enough to hold both
index trees and shadow index trees, so the HWM is almost the double size of the
original index trees. Is that possible if we can lower the HWM to the final index trees
size?
BTW, which part changed to the large space to allow it to store index pages?
Regards,
FRX
Sean McKeough wrote:

Fan,

One big differnce between the zOS online, and our offline reorgs is that
the LUW offline reorg doesn't need to do log catchup, since no changes
can be made to the table during the copy process. (You probably knew
this already) :-) .

I don't understand question #1...can you please clarify?

For 2) The implementers chose snapshot to display incremental progress
because it can be accessed through clp, or the new admin table functions
(SQL) etc...it would not be hard to write an application on top of the
monitor apis (or SQL) that periodically populated a file
somewhere...there's also the history file for tracking this stuff
overall (but obviously not good granularity).

Fan Ruo Xin wrote:

Hi, Sean,
I thought we talked the same thing. I mean the db2 (offline) reorg for OS/390 and
db2 online reorg for OS/390 used the same policy - shadow copy.
I liked the idea that DB2 UDB for LUW didn't use this approach for the ONLINE
TABLE REORG.
I am thinking it will be more convenient, if -
(1). For the Online Index Reorg, if we can add a small piece step - minus the
number of pages which shadow index tree used from the HWM - as the last step of
the Switch Over phase.
(2). For the big table or index reorg, if we can allow the user to specify a
message log file, other than take the snapshot to monitor the reorg operations.
Regards,
FRX

Sean McKeough wrote:

>This is not how UWO's online reorg works...UWO's online reorg is
>inplace...there is no log catchup etc...think of it as an application
>that's driving row updates.
>
>
>>So the reorg you mentioned here use the same strategy as Online reorg
>
>in DB2 for
>
>>OS/390???
>>Online reorg use Logging to catch up those update during the whole reorg
>>process.
>>
>
>Fan Ruo Xin wrote:
>
>
>
>>Mark A wrote:
>>
>>
>>
>>
>>>On DB2 for OS/390 a reorg of the tablespace can generally be done without
>>>logging (if specified that way). This puts the tablespace in copy pending
>>>status, unless an image copy (backup) was specified to automatically be done
>>>as part of the reorg. Tablespaces in copy pending status can be read but not
>>>changed. The combination of not logging and doing an image copy instead is
>>>about the same as a reorg with logging, and has the added advantage of a
>>>more recent image copy for recovery purposes. Would be nice if DB2 LUW
>>>worked this way.
>>
>>
>>>There are some exceptions to the logging issue such as on-line reorgs in DB2
>>>for OS/390, but that is a slightly different subject.
>>>
>>>But the real question was about the overlap between a database backup and a
>>>tablespace backup (at different times) when doing a roll-forward recovery.
>>>On DB2 OS/390 all backups (image copies) are at the tablespace level, not
>>>database (or sub-system).
>>
>>


Nov 12 '05 #28

This discussion thread is closed

Replies have been disabled for this discussion.