Bookmark and Share
RSS

Recent Posts

Db2 12 Brings Enhanced Data Validation Capabilities to DSN1COPY

October 17, 2017

DSN1COPY is a stand-alone utility to copy Db2 VSAM data sets. Specifically, it allows you to copy:
  • Db2 VSAM data sets to sequential data sets
  • DSN1COPY sequential data sets to Db2 VSAM data sets
  • Db2 image copy data sets to Db2 VSAM data sets
  • Db2 VSAM data sets to other Db2 VSAM data sets
  • DSN1COPY sequential data sets to other sequential data sets
As you can see from this list, DSN1COPY is great for copying data from one set of Db2 tables to another. And it's even better now, thanks to improvements that arrived with Db2 12. More on those in a bit.

DSNICOPY has always been valued because it's much faster than an unload/load. However, for Db2 11 and earlier, it carries some risk. If used incorrectly, subsequent attempts to access the data using SQL could result in abends, data corruption and storage overlays.

In my early years as a DBA, I copied a backup imagecopy of a table when it was in a SIMPLE table space. Then I used DSN1COPY to restore from the imagecopy into a new table space in SEGMENTED format. After doing this, any SQL trying to access the table would abend with an SQL code -904 and reason code 00C91010. You never want to see 00C90101; it means call IBM for help, because that's only way you'll get your production system back.

Other types of errors that can happen with DSN1COPY include:
  • Incorrect DBID, PSID or OBID values are specified when you run DSN1COPY with the OBIDXLAT option.
  • DSN1COPY is used to copy data to a table space of a different type. For example, data is copied from a segmented table space to a partition-by-growth universal table space.
  • DSN1COPY is used to copy data to a table space with a different version number or table definition.
With all this in mind, for years I've been telling customers to stick with the slower but safer unload/load option rather than mess with DSN1COPY. But now for the good news: Db2 12 provides a way to validate and, in some cases, repair mismatches. The REPAIR CATALOG utility enables these mismatches to be fixed:

The column data type or length in the table space differs from the catalog definition for the column. If Db2 supports conversion from the data type and length in the table space to the data type and length in the column, REPAIR CATALOG enables conversion of the data type or length of the data in the table space to match the catalog definition the next time that the data is accessed.

Also with Db2 12, the Db2 REPAIR CATALOG TEST utility can be used to validate and detect these mismatches:
  • The table space is range-partitioned with absolute page numbering, but the catalog indicates that the table space is range-partitioned with relative page numbering.
  • The table space is range-partitioned with relative page numbering, but the catalog indicates that the table space is range-partitioned with absolute page numbering.
  • The table space is range-partitioned with absolute page numbering, but the catalog indicates that the table space is range-partitioned with relative page numbering. REPAIR CATALOG changes the catalog definition to match the table space format.
  • The number of columns in the table space is greater than the number of columns in the catalog definition of the table.
  • The column data type or length in the table space differs from the catalog definition for the column.
Note: None of these data validation enhancements apply to LOB and XML table spaces.

The IBM Knowledge Center has more about the REPAIR utility.

Posted October 17, 2017 | Permalink

comments powered by Disqus