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?
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)
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
4. LOAD DATA REPLACE LOG NO (with SYSREC DD DUMMY) - unlogged mass
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.
-----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