DB2 v11 z/OS utli performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)

Philip Sevetson

DB2 v11 z/OS utli performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)
Has anyone done a performance test of the two ways to delete all data from a table, recently? I've been running LOAD REPLACE / SYSIN DD DUMMY for so long that I've gotten out of the habit.

Of course, I'd assume that LOAD REPLACE without REUSE would be much slower than "DELETE FROM table;" , but we're moving to REUSE for some of our test environments - trading space-reclamation for faster elapsed times. So it occurred to me to wonder; if I'm doing a LOAD DUMMY as part of updating limit keys to receive new data (ALTER limitkeys; LOAD DUMMY; REORG to resolve pending DDL; LOAD new data), what's my fastest way to get through the process?

Does anyone have actual experience comparing things?

Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

steen rasmussen

DB2 v11 z/OS utli performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)
(in response to Philip Sevetson)
Phil - please remember that a MASS DELETE ( unqualified delete from table) can execute for a long time in case you have Data Capture Changes enabled. In this case TRUNCATE is much better - again, other processes might need the deleted rows ???
I don't quite get the PENDING DDL issue. If you empty a partition (DELETE or DUMMY LOAD) - if you are doing ADD partition or ROTATE, this is an immediate alter. If you are altering the LIMITKEYS in Db2 12, this is a PENDING change.
Steen 
On Tuesday, June 25, 2019, 3:01:47 PM EDT, Sevetson, Phil <[login to unmask email]> wrote:

<!--#yiv5755368741 _filtered #yiv5755368741 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv5755368741 {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}#yiv5755368741 #yiv5755368741 p.yiv5755368741MsoNormal, #yiv5755368741 li.yiv5755368741MsoNormal, #yiv5755368741 div.yiv5755368741MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri", "sans-serif";}#yiv5755368741 a:link, #yiv5755368741 span.yiv5755368741MsoHyperlink {color:blue;text-decoration:underline;}#yiv5755368741 a:visited, #yiv5755368741 span.yiv5755368741MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv5755368741 p.yiv5755368741MsoAcetate, #yiv5755368741 li.yiv5755368741MsoAcetate, #yiv5755368741 div.yiv5755368741MsoAcetate {margin:0in;margin-bottom:.0001pt;font-size:8.0pt;font-family:"Tahoma", "sans-serif";}#yiv5755368741 span.yiv5755368741EmailStyle17 {font-family:"Calibri", "sans-serif";color:windowtext;}#yiv5755368741 span.yiv5755368741BalloonTextChar {font-family:"Tahoma", "sans-serif";}#yiv5755368741 .yiv5755368741MsoChpDefault {font-family:"Calibri", "sans-serif";} _filtered #yiv5755368741 {margin:1.0in 1.0in 1.0in 1.0in;}#yiv5755368741 div.yiv5755368741WordSection1 {}-->
Has anyone done a performance test of the two ways to delete all data from a table, recently? I’ve been running LOAD REPLACE / SYSIN DD DUMMY for so long that I’ve gotten out of the habit. 

 

Of course, I’d assume that LOAD REPLACE without REUSE would be much slower than "DELETE FROM table;" , but we’re moving to REUSE for some of our test environments – trading space-reclamation for faster elapsed times.  So it occurred to me to wonder; if I’m doing a LOAD DUMMY as part of updating limit keys to receive new data (ALTER limitkeys; LOAD DUMMY; REORG to resolve pending DDL; LOAD new data), what’s my fastest way to get through the process?

 

Does anyone have actual experience comparing things?

 

Philip Sevetson

Computer Systems Manager

FISA-OPA

5 Manhattan West

New York, NY 10001

[login to unmask email]

212-857-1688 w

917-991-7052 m

212-857-1659 f



 
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachment Links: image001.png (3 k)  
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]
Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
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

Philip Sevetson

DB2 v11 z/OS utli performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)
(in response to steen rasmussen)
Steen,

First - thanks for the fill-in about DELETE versus TRUNCATE – I thought they operated in the same manner. We don’t define our tables (in _my_ area of responsibility; elsewhere in the Agency things are different) with Data Capture Changes or Audit on, so we probably gain the speed benefits even using DELETE.
Second - ALTER TABLE ENDING AT definitely produces a PENDING change in DB2V11. I thought I could escape it by pre-emptying the table, but that appears not to have been the case, so it’s not only DB2V12 which behaves this way.

-phil

From: steen rasmussen [mailto:[login to unmask email]
Sent: Tuesday, June 25, 2019 3:32 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 v11 z/OS utli performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)

Phil - please remember that a MASS DELETE ( unqualified delete from table) can execute for a long time in case you have Data Capture Changes enabled. In this case TRUNCATE is much better - again, other processes might need the deleted rows ???

I don't quite get the PENDING DDL issue. If you empty a partition (DELETE or DUMMY LOAD) - if you are doing ADD partition or ROTATE, this is an immediate alter. If you are altering the LIMITKEYS in Db2 12, this is a PENDING change.

Steen

On Tuesday, June 25, 2019, 3:01:47 PM EDT, Sevetson, Phil <[login to unmask email]> wrote:



Has anyone done a performance test of the two ways to delete all data from a table, recently? I’ve been running LOAD REPLACE / SYSIN DD DUMMY for so long that I’ve gotten out of the habit.



Of course, I’d assume that LOAD REPLACE without REUSE would be much slower than "DELETE FROM table;" , but we’re moving to REUSE for some of our test environments – trading space-reclamation for faster elapsed times. So it occurred to me to wonder; if I’m doing a LOAD DUMMY as part of updating limit keys to receive new data (ALTER limitkeys; LOAD DUMMY; REORG to resolve pending DDL; LOAD new data), what’s my fastest way to get through the process?



Does anyone have actual experience comparing things?



Philip Sevetson

Computer Systems Manager

FISA-OPA

5 Manhattan West

New York, NY 10001

[login to unmask email]

212-857-1688 w

917-991-7052 m

212-857-1659 f

Error! Filename not specified.


**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Peter Vanroose

Re: DB2 v11 z/OS util performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)
(in response to Philip Sevetson)

In my experience, you get the shortest unavailability when issuing the "ALTER TABLE ... PARTITION n ENDING ..." DDL and the REORG of the two affected partitions as the two steps of a single JCL job. (Use DSNTEP4 for step 1, and expect it to return an SQLCODE +610, hence RC=4.)

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://abis.be/

In Reply to Philip Sevetson:

ALTER TABLE ENDING AT definitely produces a PENDING change in DB2V11. I thought I could escape it by pre-emptying the table, but that appears not to have been the case, so it’s not only DB2V12 which behaves this way.

Steven Lamb

Re: DB2 v11 z/OS util performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)
(in response to Peter Vanroose)

What's the actual Alter statement that's executed? We only empty the oldest partition, rather than the entire table, using a dummy Load. We do this first and then perform the Alter Rotate First to Last. We don't get any restricted / advisory states of any sort in DB2 v11; everything is RC0 or SQL code 0.

I've just run through our process manually and SYSPENDINGDDL is empty - not touched for ages.

---------+---------+---------+---------+---------+---------+--------
ALTER TABLE DB2PE.DB2PMFACCT_BUFFER ROTATE PARTITION FIRST TO LAST
ENDING AT('2019-06-28-00.00.00.000000') RESET ;
---------+---------+---------+---------+---------+---------+--------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

A Display DB for ADV or RES states immediately following the Alter shows nothing whatsoever.

Philip Sevetson

DB2 v11 z/OS util performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)
(in response to Steven Lamb)
Steven,

In the case which I’m looking at, I am updating dated limitkeys in test to match the production values, and then LOAD with REPLACE using a (suitably masked) subset of recent production data.
We execute a series of ALTER statements to bring limitkeys up to current prod, altering all partitions to be current;

ALTER TABLE T1 ALTER PARTITION 120 ENDING AT ('2019-05-31');
ALTER TABLE T1 ALTER PARTITION 119 ENDING AT ('2019-04-30');
ALTER TABLE T1 ALTER PARTITION 118 ENDING AT ('2019-03-31');

…and so on. (If we’ve done a ROTATE anywhere in the table history, those partition numbers won’t necessarily look so orderly!)

Current practice is:
ALTER limit keys “upward”
LOAD with REPLACE, using //SYSREC DD DUMMY
REORG SHRLEVEL NONE REUSE (to implement pending changes, from the ALTER)
LOAD with REPLACE, new data

I’m going to try the exercise of emptying the table, then executing these ALTERs, but I did try it once before, and it did _not_ avoid pending changes.

-phil (sevetson)

P.S. we do also execute purge-and-rotate. When we do, we run REORG with SCOPE PENDING, which excludes unchanged partitions.
PLS

From: Steven Lamb [mailto:[login to unmask email]
Sent: Thursday, June 27, 2019 4:00 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 v11 z/OS util performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)


What's the actual Alter statement that's executed? We only empty the oldest partition, rather than the entire table, using a dummy Load. We do this first and then perform the Alter Rotate First to Last. We don't get any restricted / advisory states of any sort in DB2 v11; everything is RC0 or SQL code 0.

I've just run through our process manually and SYSPENDINGDDL is empty - not touched for ages.

---------+---------+---------+---------+---------+---------+--------
ALTER TABLE DB2PE.DB2PMFACCT_BUFFER ROTATE PARTITION FIRST TO LAST
ENDING AT('2019-06-28-00.00.00.000000') RESET ;
---------+---------+---------+---------+---------+---------+--------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

A Display DB for ADV or RES states immediately following the Alter shows nothing whatsoever.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Bruce Williamson

RE: DB2 v11 z/OS util performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)
(in response to Philip Sevetson)

Howzit Phil?

The 3 different statements as you've already discovered are quite different and I'll add a 4th just for clarity altho this may indeed be your intent or actual implementation. Simplistically speaking without getting into the nitty gritty the statements and their effect are briefly described below:

  1. DELETE - unqualified, it is a logged mass delete
  2. TRUNCATE - unlogged mass delete
  3. LOAD DATA REPLACE (with SYSREC DD DUMMY) - logged mass delete
  4. LOAD DATA REPLACE LOG NO (with SYSREC DD DUMMY) - unlogged mass delete

Ignoring 1 and 3 as you are concerned with performance, when to use 2. vs. 4. is determined by the following SQL, anything that qualifies use 4 otherwise use 2:

SELECT SUBSTR(TB.NAME,1,32)  AS TBNAME
, SUBSTR(TB.DBNAME,1,8) AS DBNAME
, SUBSTR(TB.TSNAME,1,8) AS TSNAME
FROM SYSIBM.SYSTABLES TB
, SYSIBM.SYSTABLESPACE TS
WHERE TB.CREATOR = 'schema'
AND TB.NAME = 'table name'
AND TB.TYPE = 'T'
AND TS.DBNAME = TB.DBNAME
AND TS.NAME = TB.TSNAME
AND NOT ( TS.SEGSIZE > 0 -- SEGMENTED/UTS
AND ( TB.VALPROC = '' -- NO VALIDPROC
AND TB.DATACAPTURE = '' -- NOT CDC
AND TB.SECURITY_LABEL = '' -- NOT MLS
)
)
WITH UR FOR FETCH ONLY;

Being a SAP shop we use this extensively in the BI area. This is all documented in DB2 manuals and the knowledge centre, if memory serves me with the TRUNCATE statement and when not to use it.

Any questions, give me a shout.

Cheers
Bruce

Michael Hannan

RE: DB2 v11 z/OS util performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)
(in response to Bruce Williamson)

In Reply to Bruce Williamson:

The 3 different statements as you've already discovered are quite different and I'll add a 4th just for clarity altho this may indeed be your intent or actual implementation. Simplistically speaking without getting into the nitty gritty the statements and their effect are briefly described below:

  1. DELETE - unqualified, it is a logged mass delete
  2. TRUNCATE - unlogged mass delete
  3. LOAD DATA REPLACE (with SYSREC DD DUMMY) - logged mass delete
  4. LOAD DATA REPLACE LOG NO (with SYSREC DD DUMMY) - unlogged mass delete

Bruce has oversimplified here. TRUNCATE (New in V10) can be very similar to Mass Delete in some circumstances. Off course TRUNCATE is logged. If IMMEDIATE option of TRUNCATE is used, it is not reversible with ROLLBACK. Without IMMEDIATE, rollback still works, so for recovery purposes Truncate must indeed be logged.

Mass DELETE is logged too but not necessarily every row removed. If no Data Capture changes, then can log simply the update of spacemaps to indicate the table is now empty, 

The major difference between TRUNCATE and DELETE is that TRUNCATE had the option not to fire DELETE triggers. 

Suppose your table does not have DELETE triggers, and no R.I. issues, then Truncate without IMMEDIATE could be very much the same as a Mass DELETE. 

Yes TRUNCATE is more flexible, than Mass DELETE and therefore preferred. So I may agree with the conclusion without agreeing with the reasons. We don't need to go changing all our Mass Deletes though. Some are performing fine.

Mass Delete can be slow if table is parent of Referential Constraint. R.I. checking has to occur (if RESTRICT and worse if CASCADE). TRUNCATE will fail in this circumstance as it is assumed we don't want to truncate when R.I. exists.

TRUNCATE will be same as Mass DELETE for Data Capture Changes, I believe, which means doing a lot of logging (every row deleted).

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jun 28, 2019 - 07:17 AM (Europe/Berlin)

Philip Sevetson

DB2 v11 z/OS util performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)
(in response to Bruce Williamson)
Bruce,

You’re making my head hurt. That NOT case looks like it can be stated this way:


1. If SEGSIZE > 0 *AND* (there aren’t any VALPROCS, no DATACAPTURE, no MLS), THEN USE LOAD/REPLACE.
(Or, restating; use LOAD/REPLACE for UTS and SEGMENTED which don’t have “complications.”)

2. For all other cases, use TRUNCATE.

Am I reading that correctly?

-phil

From: Bruce Williamson [mailto:[login to unmask email]
Sent: Thursday, June 27, 2019 11:30 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 v11 z/OS util performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)


Howzit Phil?

The 3 different statements as you've already discovered are quite different and I'll add a 4th just for clarity altho this may indeed be your intent or actual implementation. Simplistically speaking without getting into the nitty gritty the statements and their effect are briefly described below:

1. DELETE - unqualified, it is a logged mass delete
2. TRUNCATE - unlogged mass delete
3. LOAD DATA REPLACE (with SYSREC DD DUMMY) - logged mass delete
4. LOAD DATA REPLACE LOG NO (with SYSREC DD DUMMY) - unlogged mass delete

Ignoring 1 and 3 as you are concerned with performance, when to use 2. vs. 4. is determined by the following SQL, anything that qualifies use 4 otherwise use 2:

SELECT SUBSTR(TB.NAME,1,32) AS TBNAME
, SUBSTR(TB.DBNAME,1,8) AS DBNAME
, SUBSTR(TB.TSNAME,1,8) AS TSNAME
FROM SYSIBM.SYSTABLES TB
, SYSIBM.SYSTABLESPACE TS
WHERE TB.CREATOR = 'schema'
AND TB.NAME = 'table name'
AND TB.TYPE = 'T'
AND TS.DBNAME = TB.DBNAME
AND TS.NAME = TB.TSNAME
AND NOT ( TS.SEGSIZE > 0 -- SEGMENTED/UTS
AND ( TB.VALPROC = '' -- NO VALIDPROC
AND TB.DATACAPTURE = '' -- NOT CDC
AND TB.SECURITY_LABEL = '' -- NOT MLS
)
)
WITH UR FOR FETCH ONLY;

Being a SAP shop we use this extensively in the BI area. This is all documented in DB2 manuals and the knowledge centre, if memory serves me with the TRUNCATE statement and when not to use it.

Any questions, give me a shout.

Cheers
Bruce

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

william giannelli

RE: DB2 v11 z/OS utli performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)
(in response to steen rasmussen)

So I am clear, with data capture turned on, delete/truncate would runs faster (generally speaking) than Load replace?

thanks

Bill

Philip Sevetson

DB2 v11 z/OS utli performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)
(in response to william giannelli)
Bill,

All right, then I did understand correctly.

This particular case doesn’t apply to my problem, which is partitioned tablespaces in test, no CDC; we’re ALTERing all index Limit Keys and then repopulating with data from a prod subset. For my situation, your logic is saying “use LOAD with REPLACE and //SYSIN DD DUMMY”. So my code doesn’t need revision.

The discussion has been enlightening and reassuring. Thanks, Bill and all.

-phil (sevetson)

From: william giannelli [mailto:[login to unmask email]
Sent: Tuesday, July 02, 2019 5:54 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 v11 z/OS utli performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)


So I am clear, with data capture turned on, delete/truncate would runs faster (generally speaking) than Load replace?

thanks

Bill

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Bruce Williamson

RE: DB2 v11 z/OS util performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)
(in response to Philip Sevetson)

I have to apologise Phil I can't find the DB2 10 for z/OS performance related documentation that the SQL was based on and not sure it has changed for DB2 11 for z/OS. The bottom line is that TRUNCATEing data from segmented TSs is efficient VALIDPROC, CDC and MLS notwithstanding.

 

In answer to your question, no, in fact the opposite Phil.

Forget the NOT for the moment and just evaluate the bit inside the brackets i.e. SEGMENTED and VALIDPROC and CDC and MLS are all TRUE; so for the NOT to qualify, one or more of the previous 4 conditions must be FALSE. The NOT is effectively ORing the 4 conditions. I hope this makes a little more sense, I know I sometimes get an ice-cream headache working this out so I always ignore the NOT until the end.

So restated in your words, use LOAD for simple TS or classic partitioned TS and any TS with complications.

I use this to generate LOAD cards:

SELECT '  LOAD REPLACE LOG NO NOCOPYPEND INTO TABLE "'
|| STRIP(TB.CREATOR) || '"."'
|| STRIP(TB.NAME) || '" PART '
|| STRIP(CHAR(TP.PARTITION))
...

HTH

Cheers
Bruce

P.S. For reference TRUNCATE

In Reply to Philip Sevetson:

Bruce,

You’re making my head hurt. That NOT case looks like it can be stated this way:


1. If SEGSIZE > 0 *AND* (there aren’t any VALPROCS, no DATACAPTURE, no MLS), THEN USE LOAD/REPLACE.
(Or, restating; use LOAD/REPLACE for UTS and SEGMENTED which don’t have “complications.”)

2. For all other cases, use TRUNCATE.

Am I reading that correctly?

-phil

From: Bruce Williamson [mailto:[login to unmask email]
Sent: Thursday, June 27, 2019 11:30 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 v11 z/OS util performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)


Howzit Phil?

The 3 different statements as you've already discovered are quite different and I'll add a 4th just for clarity altho this may indeed be your intent or actual implementation. Simplistically speaking without getting into the nitty gritty the statements and their effect are briefly described below:

1. DELETE - unqualified, it is a logged mass delete
2. TRUNCATE - unlogged mass delete
3. LOAD DATA REPLACE (with SYSREC DD DUMMY) - logged mass delete
4. LOAD DATA REPLACE LOG NO (with SYSREC DD DUMMY) - unlogged mass delete

Ignoring 1 and 3 as you are concerned with performance, when to use 2. vs. 4. is determined by the following SQL, anything that qualifies use 4 otherwise use 2:

SELECT SUBSTR(TB.NAME,1,32) AS TBNAME
, SUBSTR(TB.DBNAME,1,8) AS DBNAME
, SUBSTR(TB.TSNAME,1,8) AS TSNAME
FROM SYSIBM.SYSTABLES TB
, SYSIBM.SYSTABLESPACE TS
WHERE TB.CREATOR = 'schema'
AND TB.NAME = 'table name'
AND TB.TYPE = 'T'
AND TS.DBNAME = TB.DBNAME
AND TS.NAME = TB.TSNAME
AND NOT ( TS.SEGSIZE > 0 -- SEGMENTED/UTS
AND ( TB.VALPROC = '' -- NO VALIDPROC
AND TB.DATACAPTURE = '' -- NOT CDC
AND TB.SECURITY_LABEL = '' -- NOT MLS
)
)
WITH UR FOR FETCH ONLY;

Being a SAP shop we use this extensively in the BI area. This is all documented in DB2 manuals and the knowledge centre, if memory serves me with the TRUNCATE statement and when not to use it.

Any questions, give me a shout.

Cheers
Bruce

Michael Hannan

RE: DB2 v11 z/OS utli performance: DELETE/TRUNCATE versus LOAD DATA REPLACE (with SYSREC DD DUMMY)
(in response to william giannelli)

In Reply to william giannelli:

So I am clear, with data capture turned on, delete/truncate would runs faster (generally speaking) than Load replace?

thanks

Bill

Bill,

LOAD REPLACE can be with LOG NO or LOG YES. However, either way I don't think it is going to log every record effectively removed like a DELETE or TRUNCATE would for DATA CAPTURE CHANGES. It is also not going to fire DELETE Triggers, similar to Truncate capability. 

So generally it could be a lot faster than DELETE/TRUNCATE in DCC situation. It could also be faster than DELETE if R.I. checking would be required, but have an ENFORCE phase, or have a CHECK DATA required.

Choice made may be influenced by whether it is a one off operation or a regular process. If no R.I. problems and no Capture Changes, and no other curly problems disallowing, I would probably choose the simple TRUNCATE (to remove all rows from a table - not a partition), since that is what it is designed for.

If have Capture Changes, then need to decide if every row deleted has to be logged  or not.

If need to empty a partition only, LOAD will be needed for speed. That is not defined as mass delete.

For me it is less wise to build a formula to the answer, if that implies we don't really understand what we are doing in the underlying process, and understand recoverability requirements.

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd