[v7 for z/OS] Logging Index Entries during Update?

Philip Sevetson

[v7 for z/OS] Logging Index Entries during Update?
We're seeing a difference, a big one, in the number of log writes when we
update n rows in a table with, versus without, indexes.

We updated about millions of rows in a table, first with with indexes
present and populated; we got several log writes per row updated
associated with the transaction (watching with CA Insight for DB2). Doing
the same update with indexes dropped yielded about the same number of log
writes as rows.

All indexes were created omitting the COPY parameter (COPY defaults to
NO).

Insight is definitely reporting much more DB2 logging when indexes are
present than when they are not. Does anyone else have experience with
this, and does anyone know what IBM says officially about logging index
entries?

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
230 Raritan Center Parkway, P5-105
Edison, NJ 08837
Phone: (732) 225-8086
Fax: (732) 225-8109
mailto:[login to unmask email]


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Andy Lankester

Re: [v7 for z/OS] Logging Index Entries during Update?
(in response to Philip Sevetson)
Index updates have to be logged for backout purposes regardless of whether
the index is COPY YES. Others may know whether it is only the before (UNDO)
records that are logged if COPY YES.

Andy

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of [login to unmask email]
Sent: 23 December 2004 20:30
To: [login to unmask email]
Subject: [v7 for z/OS] Logging Index Entries during Update?




We're seeing a difference, a big one, in the number of log writes when we
update n rows in a table with, versus without, indexes.

We updated about millions of rows in a table, first with with indexes
present and populated; we got several log writes per row updated associated
with the transaction (watching with CA Insight for DB2). Doing the same
update with indexes dropped yielded about the same number of log writes as
rows.

All indexes were created omitting the COPY parameter (COPY defaults to NO).

Insight is definitely reporting much more DB2 logging when indexes are
present than when they are not. Does anyone else have experience with this,
and does anyone know what IBM says officially about logging index entries?

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
230 Raritan Center Parkway, P5-105
Edison, NJ 08837
Phone: (732) 225-8086
Fax: (732) 225-8109
mailto:[login to unmask email]

----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.6.4 - Release Date: 22/12/2004



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.6.4 - Release Date: 22/12/2004


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Andy Lankester

Re: [v7 for z/OS] Logging Index Entries during Update?
(in response to Andy Lankester)
Sorry, if it's COPY yes you WILL need the after (REDO) records. The more I
think about it the more I think that you will get both UNDO & REDO records
regardless of COPY YES/NO. The list will arbitrate!

Andy

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of [login to unmask email]
Sent: 23 December 2004 20:30
To: [login to unmask email]
Subject: [v7 for z/OS] Logging Index Entries during Update?



We're seeing a difference, a big one, in the number of log writes when we
update n rows in a table with, versus without, indexes.

We updated about millions of rows in a table, first with with indexes
present and populated; we got several log writes per row updated associated
with the transaction (watching with CA Insight for DB2). Doing the same
update with indexes dropped yielded about the same number of log writes as
rows.

All indexes were created omitting the COPY parameter (COPY defaults to NO).

Insight is definitely reporting much more DB2 logging when indexes are
present than when they are not. Does anyone else have experience with this,
and does anyone know what IBM says officially about logging index entries?

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
230 Raritan Center Parkway, P5-105
Edison, NJ 08837
Phone: (732) 225-8086
Fax: (732) 225-8109
mailto:[login to unmask email]

----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.6.4 - Release Date: 22/12/2004



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.6.4 - Release Date: 22/12/2004


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tom Flesher

Re: [v7 for z/OS] Logging Index Entries during Update?
(in response to Andy Lankester)
DB2 needs both UNDO and REDO log records for index-related logging in order
to handle:

(a) Backout situations

(b) Emergency restart situations

Because DB2 is ARIES-compliant and uses write-ahead logging, there are
so-called "dirty pages" in the database (data and index pages alike). A
dirty page is defined (this is off the top of my head) as a page wherein a
committed update has been made in the buffer pool, but the updated page has
not been physically written to the database just yet. The updates are,
however, reflected in the log.

During emergency restart after a DB2 crash, DB2 must clean up its dirty
pages using the log. It needs after images (redo) for this purpose. System
checkpoints (also found in the log) tell DB2 how far back in the log he
must go - object by object - to find the changes needed to clean up dirty
pages for a particular object. Asynchronous processes in DB2 are
responsible for flushing updates from the buffer pools back to DASD to
avoid a length restart scenario.

There's some interesting material about ARIES on the IBM website at:

http://www.almaden.ibm.com/u/mohan/ARIES_Impact.html

Of course if you're backing up your indexes and intend to do forward
recovery using the log, that's another requirement for REDO log records
related to the indexes. But DB2's own restart and recovery algorithms
require index-related logging and always have, as far as I know.

Best regards & Happy Holidays!
Tom Flesher
E-Net Corporation


>>Sorry, if it's COPY yes you WILL need the after (REDO) records. The more
I think about it the more I think that you will get both UNDO & REDO
records regardless of COPY YES/NO. The list will arbitrate!

Andy<<

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Isaac Yassin

Re: [v7 for z/OS] Logging Index Entries during Update?
(in response to Tom Flesher)
Just to add to what already been written -

Each changed index entry (when you update a column that is part of an index) causes the related index entry to be pseudo-deleted in
one place and inserted in another place - so you get 2 REDO & 2 UNDO entries in the log for that. If you happen to cause an index
page split it can be more.


Isaac Yassin






_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of [login to unmask email]
Sent: Thursday, December 23, 2004 10:30 PM
To: [login to unmask email]
Subject: [v7 for z/OS] Logging Index Entries during Update?



We're seeing a difference, a big one, in the number of log writes when we update n rows in a table with, versus without, indexes.

We updated about millions of rows in a table, first with with indexes present and populated; we got several log writes per row
updated associated with the transaction (watching with CA Insight for DB2). Doing the same update with indexes dropped yielded
about the same number of log writes as rows.

All indexes were created omitting the COPY parameter (COPY defaults to NO).

Insight is definitely reporting much more DB2 logging when indexes are present than when they are not. Does anyone else have
experience with this, and does anyone know what IBM says officially about logging index entries?

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
230 Raritan Center Parkway, P5-105
Edison, NJ 08837
Phone: (732) 225-8086
Fax: (732) 225-8109
mailto:[login to unmask email]

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go
to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The
IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm