Replacing data with LOAD

You can use the LOAD utility to replace data in a table space that has one or more tables.

Procedure

To replace data with LOAD:

Specify the REPLACE option in the LOAD utility control statement.

This option specifies that all data in the table space is to be replaced. Alternatively, you can load new records into a table space without deleting the existing rows by using the RESUME option.

When you specify LOAD REPLACE, determine what other LOAD options to specify depending on the following implications:

How data sets are processed: Db2 processes data sets depending on the LOAD options that you specify. If you run LOAD REPLACE without the REUSE option, data sets that are not user-managed are deleted before the LOAD utility runs. The LOAD utility defines a new data set with a control interval that matches the page size.
How row format is affected: When you run LOAD REPLACE with the ROWFORMAT RRF option on a table space or partition that is in basic row format, LOAD converts the table space or partition to the reordered row format. If the ROWFORMAT BRF option is specified, existing basic row format table spaces are not converted to reordered row format. If the clause EDITPROC or VALIDPROC is used in a table space or partition, the table space or partition remains in basic format after the LOAD REPLACE. For table spaces that contain some partitions in basic row format and some partitions in reordered row format, LOAD REPLACE converts the partitions that are in basic row format to reordered row format.
Important: ROWFORMAT is deprecated in Db2 12 for z/OS®, meaning that its use is discouraged. Although this keyword remains supported, support is likely to be removed eventually. See Deprecated function in Db2 12 .
How logging is handled: The LOAD REPLACE or PART REPLACE with LOG YES option logs only the reset and not each deleted row. To see what rows are being deleted, use the SQL DELETE statement.

Running LOAD REPLACE has the following effects on restrictive states:

REORG-pending: If an object is in REORG-pending status, you can run LOAD REPLACE on the entire table space, which resets REORG-pending status. You can also run LOAD PART REPLACE or RESUME on any partitions that are not in REORG-pending status. In this situation, no other LOAD operations are allowed.
Advisory REORG-pending: If an object is in advisory REORG-pending status (AREO*), you can run LOAD REPLACE on the entire table space, which resets advisory REORG-pending status (AREO*). The exception is pending limit key changes. LOAD REPLACE does not materialize those changes or reset advisory REORG-pending status (AREOR). In that case, you must run the REORG TABLESPACE utility. Then, you can run LOAD REPLACE. (You can continue to use LOAD REPLACE to materialize immediate alter limit key changes, which are indicated by REORG-pending status (REORP) instead of advisory REORG-pending status (AREOR). Immediate alter limit key changes occur for a partitioned table space with index-controlled partitioning.
REBUILD-pending: If an object is in REBUILD-pending status, you can run LOAD REPLACE on the entire table space, which resets REBUILD-pending status. You can also run LOAD PART REPLACE or RESUME on any partitions. If these partitions are in REBUILD-pending status, a LOAD PART REPLACE or RESUME resets that status.
Advisory REBUILD-pending: If an object is in advisory REBUILD-pending status, you can run LOAD REPLACE on the entire table space, which resets advisory REBUILD-pending status.
REFRESH-pending: If a user-defined table space is in REFRESH-pending (REFP) status, you can replace the data by using LOAD REPLACE.

Examples

Example of replacing one table in a single-table table space

The following control statement specifies that LOAD is to replace one table in a single-table table space.

LOAD DATA
  REPLACE
  INTO TABLE DSN8C10.DEPT
  ( DEPTNO    POSITION (1)     CHAR(3),
    DEPTNAME  POSITION (5)     VARCHAR,
    MGRNO     POSITION (37)    CHAR(6),
    ADMRDEPT  POSITION (44)    CHAR(3),
    LOCATION  POSITION (48)    CHAR(16) )
  ENFORCE NO

Example of replacing one table in a multiple-table table space

LOAD works on an entire table space. Therefore, be careful when using LOAD REPLACE on a table space with multiple tables. To replace all rows in a multiple-table table space, you must work with one table at a time by using the RESUME YES option on all but the first table. For example, if you have two tables in a table space, take the following steps:

Use LOAD REPLACE on the first table as shown in the following control statement. This option removes data from the table space and replaces just the data for the first table.

LOAD DATA CONTINUEIF(72:72)='X'
  REPLACE
  INTO DSN8C10.TOPTVAL
  ( MAJSYS    POSITION (2)    CHAR(1),
    ACTION    POSITION (4)    CHAR(1),
    OBJECT    POSITION (6)    CHAR(2),
    SRCHCRIT  POSITION (9)    CHAR(2),
    SCRTYPE   POSITION (12)   CHAR(1),
    HEADTXT   POSITION (80)   CHAR(50),
    SELTXT    POSITION (159)  CHAR(50),
    INFOTXT   POSITION (238)  CHAR(71),
    HELPTXT   POSITION (317)  CHAR(71),
    PFKTXT    POSITION (396)  CHAR(71),
    DSPINDEX  POSITION (475)  CHAR(2) )

Use LOAD with RESUME YES on the second table as shown in the control statement in the following example. This option adds the records for the second table without deleting the data in the first table.
```
LOAD DATA CONTINUEIF(72:72)='X'
  RESUME YES
  INTO DSN8C10.TDSPTXT
  ( DSPINDEX  POSITION (2)     CHAR(2),
    LINENO    POSITION (6)     CHAR(2),
    DSPLINE   POSITION (80)    CHAR(79) )
```

If you want to replace just one table in a multiple-table table space, delete all rows in the table, and then use LOAD with RESUME YES. For example, assume that you want to replace all the data in DSN8C10.TDSPTXT without changing any data in DSN8C10.TOPTVAL. In this case, take the following steps:

Delete all the rows from DSN8C10.TDSPTXT by using the following SQL DELETE statement:
```
EXEC SQL
   DELETE FROM DSN8C10.TDSPTXT
ENDEXEC
```
Tip: The mass delete works most quickly on a segmented (non-UTS) table space.

Use the LOAD job that is shown in the following figure to replace the rows in that table.

LOAD DATA CONTINUEIF(72:72)='X'
  RESUME YES
  INTO DSN8C10.TDSPTXT
  ( DSPINDEX  POSITION (2)     CHAR(2),
    LINENO    POSITION (6)     CHAR(2),
    DSPLINE   POSITION (80)    CHAR(79) )