Logging during mass DELETE activity

Bharath Nunepalli

Logging during mass DELETE activity

Hi,
I'm using IBM's InfoSphere Optim tool for data archive and purge.

And, testing online DELETE approach for the data purge.

 

Logging during the DELETE activity is one of the significant factors negatively affecting performance.

Is there anything I can do to handle the logging effect during DELETE activity?

Please share best practices with me if you have gone through mass DELETE activity.

 

Bharath Nunepalli,

Senior DB2 DBA.

Michael Hannan

RE: Logging during mass DELETE activity
(in response to Bharath Nunepalli)

Bharath,

Excessive logging from DELETE cannot be avoided unless the DELETE qualifies for Mass Delete which deletes all rows in a table of Segmented or Universal TS, and does not process every row individually (Spacemaps only). Other overheads of DELETE are caused by Referential integrity, Data Capture Changes, Triggers, Index updates, etc.

Other techniques exist using REORG with DISCARD and LOG NO, or Reload the remaining rows still required LOG NO, but these are not really fully "online" approaches.

For online DELETEs, you need to avoid RI connected children (especially for FKs not supported by Indexes), and need to break up DELETEs into reasonable sized Units of Work with Commit. Referential Integrity can come with very high performance cost, for high activity tables.

Some sites use a "Logically Deleted" mechanism where a rows is marked as logically deleted by updating a Delete Flag Column and then do subsequent cleanup, using REORG with DISCARD.

If your design rotates partitions and an entire partition is to be cleaned out, that can be more efficient, however Non-Partitioned indexes still need update.

There is no free ride. Database systems are built for concurrency, rollback possibility, and recovery. This pretty much requires logging changes or taking a new Image Copy after non logged utilities.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Bharath Nunepalli

RE: Logging during mass DELETE activity
(in response to Michael Hannan)

Michael,

Thanks for the response.

Can you please more details or links to the resources about "Logically Deleted" mechanism?

 

Bharath Nunepalli,

Senior DB2 DBA.

Michael Hannan

RE: Logging during mass DELETE activity
(in response to Bharath Nunepalli)

"Logically Deleted" is a design concept and relies on all your queries including a predicate to exclude "logically deleted" rows, since in reality the rows still exist.
AND DEL_FLAG = 'N'.

Alternatively if your queries use Views, then the View can contain the predicate
AND DEL_FLAG = 'N'

When you want to delete a row, "logically", you do

UPDATE table SET DEL_FLAG= 'Y' ....WHERE clause ;

Then subsequent queries will exclude those rows.

Later Reorg with DISCARD can remove those rows physically from the table.

I don't know where this approach is well documented.

Might not be so easy to retrofit this approach, if did not design for it.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jan 09, 2018 - 08:28 AM (Europe/Berlin)

Bharath Nunepalli

RE: Logging during mass DELETE activity
(in response to Michael Hannan)

Thanks for the details.

Bharath Nunepalli,

Senior DB2 DBA.

Peter Hunkeler

AW: Logging during mass DELETE activity
(in response to Michael Hannan)
I'm not a DB2 guy, so please bear with me.

Below concept is not new to me. However, I'm wondering: How much different is an UPDATE compared to a DELETE when it comes to logging, and updating the table space? Both need both, I would understand.


--
Peter Hunkeler


Von: Michael Hannan <[login to unmask email]> An: [login to unmask email] Betreff: [DB2-L] - RE: Logging during mass DELETE activity Datum: 09.01.18, 08:27


"Logically Deleted" is a design concept and relies on all your queries including a predicate to exclude "logically deleted" rows, since in reality the rows still exist.
AND DEL_FLAG = 'N'.
Alternatively if your queries use Views, then the View can contain the predicate AND DEL_FLAG = 'N'
When you want to delete a row, "logically", you do
UPDATE table SET DEL_FLAG= 'Y' ....WHERE clause ;
Then subsequent queries will exclude those rows.
Later Reorg with DISCARD can remove those rows physically from the table.
I don't where this approach is well documented.
Might not be so easy to retrofit this approach, if did not design for it.
Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd
Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription

This email has been sent to: [login to unmask email]
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Steen Rasmussen

AW: Logging during mass DELETE activity
(in response to Peter Hunkeler)
Peter - it depends as usual in Db2 land.

DELETE and INSERT always log the entire row image. But if the DELETE is unqualified, then only the spacemap pages are updated - unless you have Data Capture Changes enabled.
In general, UPDATE logs from first changed byte to last changed byte, but you can have compression dictionary changing what is logged and it also depends on if the update has VARCHAR and whether the updated row fits where it was or it has to be relocated.

Steen

From: Peter Hunkeler [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 1:33 PM
To: [login to unmask email]
Subject: [DB2-L] - AW: RE: Logging during mass DELETE activity

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.
I'm not a DB2 guy, so please bear with me.


Below concept is not new to me. However, I'm wondering: How much different is an UPDATE compared to a DELETE when it comes to logging, and updating the table space? Both need both, I would understand.


--
Peter Hunkeler


Von:

Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>

An:

[login to unmask email]<mailto:[login to unmask email]>

Betreff:

[DB2-L] - RE: Logging during mass DELETE activity

Datum:

09.01.18, 08:27



"Logically Deleted" is a design concept and relies on all your queries including a predicate to exclude "logically deleted" rows, since in reality the rows still exist.
AND DEL_FLAG = 'N'.

Alternatively if your queries use Views, then the View can contain the predicate AND DEL_FLAG = 'N'

When you want to delete a row, "logically", you do

UPDATE table SET DEL_FLAG= 'Y' ....WHERE clause ;

Then subsequent queries will exclude those rows.

Later Reorg with DISCARD can remove those rows physically from the table.

I don't where this approach is well documented.

Might not be so easy to retrofit this approach, if did not design for it.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----

ALIREZA BAGHBAN

RE: Logging during mass DELETE activity
(in response to Bharath Nunepalli)

If you want make the tablespace empty you can LOAD REPLACE the tablespace with a dummy or empty input file.

Be cautious about using LOAD REPLACE the tablespace if you have more than one table in it.

Anguraj Rathinasamy

Logging during mass DELETE activity
(in response to Michael Hannan)
Thanks Micheal for detailed explanation.

Sent from my iPhone

> On Jan 8, 2018, at 10:33 PM, Michael Hannan <[login to unmask email]> wrote:
>
> Michael

Michael Hannan

RE: AW: Logging during mass DELETE activity
(in response to Peter Hunkeler)

In Reply to Peter Hunkeler:

I'm not a DB2 guy, so please bear with me.

Below concept is not new to me. However, I'm wondering: How much different is an UPDATE compared to a DELETE when it comes to logging, and updating the table space? Both need both, I would understand.

UPDATE a flag column could do quite low logging compared to DELETE if rows are not compressed. If compressed, exact savings in Logging is not clear to me, since I don't have a 100% full picture of the Compressed row format (despite my recent post on the topic)  but there should be some saving if flag column is the last column (of non VARCHAR type). It is possible (?) that update flag does not change the row length and only a short amount is logged. I am not 100% sure here, since I have not tested it (the only way to be really sure). Note that log contains updates to indexes for DELETE, but not for UPDATE if Flag is not included in the indexes. If Flag is in the index, then index delete and reinsert might be more than for DELETE.

The "Logically Deleted" concept is not really intended to be a Logging solution, but can have other benefits such as no Index modification overheads where index entries are unchanged, and ability to un-delete rows if someone wanted to get them back (for a period). I merely mention that this technique has been observed in some DB2 customers. I am not trying to advocate it.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd