LOAD utility REPLACE option

Musab Ahmed · New User Joined: 10 Dec 2012 Posts: 37 Location: India

HI All,

There is a production job which uses LOAD utility to load a table. It is as follows.

LOAD DATA
REPLACE COPYDDN (COPY01)
STATISTICS TABLE (ALL) INDEX (ALL) UPDATE ALL
LOG NO
SORTKEYS 900000 SORTDEVT SYSDA
DISCARDS 0
INTO TABLE DB2FS001.TFSA092_UPC_HIER
(

I know that LOAD REPLACE statement would first delete any existing row and replaces the data with the new data.

I just want to know when the existing rows are deleted, will the space occupied by the rows also get freed up or should a reorg is to be performed to free the space?

gylbharat · Posted: Wed Feb 27, 2013 11:48 pm

read manuals

sushanth bobby · Posted: Thu Feb 28, 2013 10:19 am

Hi Musab,

Welcome to IBMMAINFRAMES!

When you do a LOAD REPLACE, db2 will redefine the dataset itself, so space occupied by the existing rows will be gone along with the dataset. For DB2 to do that below conditions should be satisified.
1) Tablespace should have been created in DB2 managed storage groups.
2) REUSE option should not be used.

Regards,
Sushanth

Musab Ahmed · New User Joined: 10 Dec 2012 Posts: 37 Location: India

Hi Sushanth,

Sorry for my ignorance, but what do you actually mean by

"space occupied by the existing rows will be gone along with the dataset"

Does this mean the space will be re-utilized for the new LOAD statement?

sushanth bobby · Posted: Thu Feb 28, 2013 11:51 am

Hi Musab,

redefine = delete the existing dataset -> create new dataset

So, i said