By Patrick Malone and Paul McWilliams
Db2 for z/OS DBAs who use the LOAD utility in Db2 11 and Db2 12 can reduce the cost and effort of preparing input data sets for the LOAD utility, examining LOAD utility job output, and dealing with discard data sets, by using several new IGNORE options.
DBAs are often aware of records in input data sets that trigger errors and violations during LOAD utility processing, even though the errors and violations are acceptable. To avoid the errors and violations, the DBA can pre-process the input data set to remove records that cause known errors and violations—manually, by using their own automation, or with third-party tooling—with extra time, effort, and cost.
Such errors and violations can cause LOAD processing to end. Otherwise, accepting the errors and violations comes with costs:
- Processing the discard of rejected records
- DASD consumption for the discard data set
- Large job output size, with many acceptable errors obscuring errors that require the DBA's attention
Now in Db2 11 with APAR PI77155 and in Db2 12 with APAR PI77159, DBAs can decide to tolerate and ignore certain categories of errors and violations in LOAD processing by specifying the following IGNORE keywords:
WHEN ignores records that do not satisfy the specified WHEN clause. (Db2 10 introduced this keyword.)
PART ignores records that do not satisfy any partition being loaded.
CONV ignores records that have conversion errors.
VALPROC ignores records that fail a validation procedure.
IDERROR ignores records that have an identity column value that is out of range.
DUPKEY ignores records that cause a duplicate key error.
For example, consider the following table space and table:
CREATE TABLESPACE TS1 IN DB1 SEGSIZE 0 NUMPARTS 2 ;
CREATE TABLE T1
(C01_INT INTEGER NOT NULL WITH DEFAULT
,PRIMARY KEY (C01_INT)
) IN DB1.TS1;
CREATE UNIQUE INDEX IX1 ON T1 (C01_INT)
PARTITION BY RANGE (
PARTITION 1 ENDING AT (10000)
,PARTITION 2 ENDING AT (20000)
When the following LOAD utility control statement runs, record 1 is ignored because it fails to satisfy the WHEN clause. Record 2 is loaded. Record 3 is ignored because it does not fit in the range of PART 1:
//SYSREC DD *
//SYSIN DD *
LOAD DATA RESUME YES INDDN SYSREC DISCARDS 10
INTO TABLE SYSADM.T1 PART 1
WHEN((1:2) = 'AA')
(C01_INT POSITION(3:10) INTEGER EXTERNAL)
The DBA can use the record count messages in the LOAD utility output to determine the number of records that were ignored. In this example, Db2 issues the following messages in the utility output:
DSNU1147I -DB2A 044 12:23:36.01 DSNURWT - (RE)LOAD PHASE STATISTICS - TOTAL NUMBER OF RECORDS LOADED=1 FOR TABLESPACE
DSNU1150I -DB2A 044 12:23:36.01 DSNURWT - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS NOT LOADED=2
DSNU302I 044 12:23:36.18 DSNURILD - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=3
Syntax and options of the LOAD control statement
Base release enhancements after the DB2 12 initial release (see "New LOAD utility IGNORE options")
PI77155: ADD SUPPORT FOR NEW IGNORE OPTIONS FOR THE LOAD UTILITY (Db2 11)
PI77159: ADD SUPPORT FOR NEW IGNORE OPTIONS FOR THE LOAD UTILITY (Db2 12)