Reduce cost and effort with new LOAD utility IGNORE options in Db2 for z/OS

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)          
      )                                         
  COPY YES;                                    

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 *                          
BB00000001                               
AA00000001                               
AA00030000                               
//SYSIN    DD *                          
LOAD DATA RESUME YES INDDN SYSREC DISCARDS 10
IGNORE(WHEN,PART)                       
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
DB1.TS1                                                                    
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

Related information
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)

Views: 887

Add a Comment

You need to be a member of The World of DB2 to add comments!

Join The World of DB2

Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.

User Groups

Blog Posts

Announced today -- Innovations in IBM Db2 AI for z/OS! Learn More!

Posted by Surekha Parekh on February 12, 2019 at 13:30 0 Comments

As the market manager of IBM Db2 for z/OS, I am really excited to share with you all some great news.…

Continue

Videos

  • Add Videos
  • View All

DB2 for z/OS APARS

© 2019   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service