Bookmark and Share

Recent Posts

How DSN1COPY Improves Data Validation

August 16, 2016

DSN1COPY is a high-performance copy utility that should have a place in every DBA's toolbox. 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.
Valuable as it is, DSN1COPY does come with this risk: no validations are performed to ensure the data set you're copying is a valid format for the target. However, DB2 Version 11 introduced new capabilities to prevent abends, data corruption and storage overlays. Once the DSN1COPY utility runs, DB2 11 automatically validates the target data set. And after being populated by DSN1COPY, the first time a data set is physically opened by an operation other than a utility, DB2 checks for certain data and catalog inconsistencies.

In earlier DB2 versions, if DSN1COPY is used incorrectly, subsequent attempts to access the data can result in abends, data corruption and storage overlays. Here are some possible scenarios:
  • 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 (PBG) universal table space (UTS).
  • DSN1COPY is used to copy data to a table space with a different version number or table schema.
These data integrity errors might not be detected until a system failure occurs after the data is stored in the target table space. As a result, the data is unavailable. As noted though, in Version 11, DB2 automatically detects these inconsistencies. Specifically, DB2 checks for any data and catalog inconsistencies for these items and reports them with a -904 SQL code:
  • DBID, PSID and OBID.
  • Table space type.
  • Table schema. (DB2 checks this item if the table space contains only one table and an OBDREC is stored in the system page.)
Several exception situations exist. To limit the performance impact, DB2 does not check for data and catalog inconsistencies in these situations:
  • The data set is physically opened by a utility, including the REPAIR utility.
  • DB2 is restarting.
  • The header page is not formatted yet.
  • The REPAIR utility is operating on the header page. (The REPAIR utility closes the page set when it is finished. Therefore, validation can be done the next time that the data set is physically opened.)
  • The LOGAPPLY phase of the RECOVER utility is processing.
Any reported inconsistencies can be corrected by using the REPAIR utility with the new CATALOG option. REPAIR can also be used to proactively check for any inconsistencies should you run DSN1COPY rather than wait for the data set to be physically opened. In this case, use the utility with the CATALOG TEST option.

(Note: None of these data validation enhancements apply to LOB and XML table spaces.
So if you use DSN1COPY to copy the table space and index space and then issue a SELECT statement you may end up with a return code -904 and need to determine what caused the problem.)

IBM has a recommended procedure for copying data using DSN1COPY.

DBAs love using DSN1COPY because it quickly copies files from one system to another. You just need to be careful about how to use it. Test out the process and make sure you understand the impact database changes have on your test system and production systems.

Posted August 16, 2016| Permalink

comments powered by Disqus