August 26, 2008

Recover or Replicate Using DSN1COPY

DSN1COPY is used to copy VSAM, sequential or image copy data sets. As a stand-alone utility, it can function even when DB2 is down. It's most commonly used to:

  • Recover a table or index space using an image copy that's not registered in SYSCOPY.
  • Copy from one subsystem to another, usually in support of development projects.

Using DSN1COPY to restore and/or copy data can be disastrous. Because DSN1COPY output is a page-for-page copy of the source, the source table space, table and index definitions must be identical to the target table space, table and index. If it isn't, you'll probably receive an SQLCODE of -904 with a reason code of 00C90101. And then you'll likely be making a support call to IBM.

Early in my DBA career I backed up a simple table space used in production. Then I dropped the table and recreated it in a segmented table space. I don’t recall the details, but I ended up performing a fallback and copying that image copy into the table space. The -904 resource unavailable with 00C90101 reason code messages immediately followed. This was at 1 a.m. on a Sunday, and I had a team of developers waiting to conduct testing.

I quickly made a severity 1 call to IBM to get our system back. The support rep told me to never, ever, use DSN1COPY to copy a simple table space to a segmented table space. On the second try, I dropped the segmented table space and recreated it as a simple table space. Now the DSN1COPY restore worked and the developer testing continued without any further problems.

I learned the hard way. Now I make sure I practice recovery scenarios and have procedures in place before I need to implement them in a live production situation.

Replicating from DB2 V8 to DB2 9: Reorder Row Format (subhead)
I know many of you have production jobs in place that are used to copy production data to your test system. You may not be aware that once you go to DB2 V9 on your test systems, the data format may change and not match your production system. If you drop and recreate a table space or run a REORG on the table space, the data may be in a reorder row format.

After running the following query on the DB2 V9 system, the format will be "R" if the data is in a reorder row format. If this is the case, you should use UNLOAD/LOAD instead of DSN1COPY. I actually recommend UNLOAD/LOAD over DSN1COPY in general because I'd rather have jobs run a little slower than have developers waiting for a restore due to changes in the data structure.

             , SUBSTR(TP.TSNAME,1,8) AS TSNAME 
             , TP.PARTITION                     
             , TP.FORMAT                        
WHERE (TP.DBNAME = 'dbname’          
    AND TP.TSNAME = 'tsname')         

New in DB2 V9

With the most recent DB2 release, the biggest DSN1COPY-related change that I can see is in the number of tables in a table space that can be copied. If you use vendor products like PeopleSoft, you may have bumped up against IBM's limits in a segmented table space. (I remember some of the delivered PeopleSoft table spaces containing 1,500-plus tables.) For instance, with DB2 V8 (or by using APAR PK05758 in DB2 V7), the table limit was 1,000. But with DB2 V9, the limit is now 10,000 tables.

For More on DSN1COPY

Remember that DSN1COPY can be useful for replicating or recovering data. However, it's sensitive to any kind of structure change. Read the DB2 9 for z/OS Utility Guide and Reference (SC18-9855) to better understand all of the parameters and restrictions.

The guide also contains some sample queries used to verify source and target definitions.