Altering the assignment of a validation routine

You can use the ALTER TABLE statement to make certain changes to a validation exit routine that is associated with a table, if one exists.

About this task

Begin general-use programming interface information. If you have a validation exit routine associated with a table, you can use the ALTER TABLE statement to make the following changes:

Disassociate the validation routine from the table using the VALIDPROC NULL clause. The routine is no longer given control when Db2 accesses the table. For example:
```
ALTER TABLE DSN8910.EMP
  VALIDPROC NULL;
```
Assign a new validation routine to the table using the VALIDPROC clause. (Only one validation routine can be connected to a table at a time; so if a validation routine already exists, Db2 disconnects the old one and connects the new routine.) Rows that existed before the connection of a new validation routine are not validated. In this example, the previous validation routine is disconnected and a new routine is connected with the program name EMPLNEWE:
```
ALTER TABLE DSN8910.EMP
  VALIDPROC EMPLNEWE;
```

To ensure that the rows of a table conform to a new validation routine, you must run the validation routine against the old rows. One way to accomplish this is to use the REORG and LOAD utilities.

Procedure

To ensure that the rows of a table conform to a new validation routine by using the REORG and LOAD utilities:

Use REORG to reorganize the table space that contains the table with the new validation routine.
Specify UNLOAD ONLY, as in this example:
```
REORG TABLESPACE DSN8D91A.DSN8S91E
  UNLOAD ONLY
```
This step creates a data set that is used as input to the LOAD utility.
Run LOAD with the REPLACE option, and specify a discard data set to hold any invalid records.
For example,
```
LOAD INTO TABLE DSN8910.EMP
   REPLACE
   FORMAT UNLOAD
   DISCARDDN SYSDISC
```
The EMPLNEWE validation routine validates all rows after the LOAD step has completed. Db2 copies any invalid rows into the SYSDISC data set.