DB2 is delivered with some utilities called DB2 stand alone utilities. These utilities has the ability to be able to run even though DB2 is down. They operate directly on the VSAM datasets that DB2 is based upon. One of these utilities has the name DSN1COPY and may be used to restore a tablespace from a DB2 image copy. Actually you can restore an image copy into any DB2 tablespace and it will also work if the definitions of the tablespace and the contained tables are the same as for the original tablespace that the image copy were produced from.

This tip will only deal with the restore of an image copy into another tablespace than the original one using DSN1COPY. I will assume that the tablespace is not partitioned, that it only contains one table and that the image copy used is a full image copy. I will also assume that the name of the dataset containing the image copy has been found in SYSIBM.SYSCOPY. Before you consider to execute DSN1COPY there are other things you have to fulfil:

• You must know the name of the VSAM dataset containing the tablespace to be replaced.
• You must have ALTER authority to the above VSAM dataset
• You must execute the STOP DB2 command for the above tablespace.

And of course you also need some JCL. It might look like this:

//DSN1COPY EXEC PGM=DSN1COPY,PARM='FULLCOPY,OBIDXLAT,RESET'
//SYSPRINT DD SYSOUT=*
//SYSUT1   DD DISP=SHR,DSN=MY.IMAGE.COPY
//SYSUT2   DD DISP=OLD,DSN=MY.DSNDBC.DBNAME.TSNAME.I0001.A001
//SYSXLAT  DD *
1,27
1,15
1,16
/*

The parameter FULLCOPY indicates that the dataset MY.IMAGE.COPY contains a full image copy. OBIDXLAT indicates that you want to restore into another tablespace than the original one and RESET indicates that LOG informations will be reset. All this is necessary when you restore into another tablespace than the original one. DD name SYSUT2 indicates the name of the VSAM dataset containing the tablespace to be replaced. SYSXLAT contains in the first record two DBIDs separated by a comma. In the second record it is two PSIDs and the last record contains two OBIDs. The first ID (source) in each record must match the ID in the image copy. The second ID (target) in each record must match the ID associated with the tablespace. All the target IDs may be found using the following SQL statement that must be issued against the DB2 subsystem where the tablespace to be replaced is defined:

SELECT TS.DBID, TS.PSID, TB.OBID
FROM SYSIBM.SYSTABLESPACE TS, SYSIBM.SYSTABLES TB
WHERE TS.DBNAME = 'DBNAME'
  AND TS.NAME = 'TSNAME'
  AND TS.DBID = TB.DBID
  AND TS.DBNAME = TB.DBNAME
  AND TS.NAME = TB.TSNAME
  AND TB.TYPE = 'T'

If this SQL statement returns more than one row the tablespace contains more than one table. The predicate TS.DBID = TB.DBID may seem obsolete, but it prevents a tablespace scan of SYSTABLES if your installation has not created an index on DBNAME, TSNAME. In the JCL example the SQL statement returned DBID=27, PSID=15 and OBID=16. You are now ready to execute the DSN1COPY step. This is possible even though you do not know the source IDs yet. DSN1COPY will simply fail if they do not match the source IDs in the image copy dataset and prints the correct source IDs in SYSPRINT. Yoy have to execute DSN1COPY three times in order to find all three source IDs. After each execution you must change 1 to the correct ID in the matching record. When all three records in SYSXLAT are changed DSN1COPY will execute the replacement of the tablespace with the contens of the image copy dataset.

After succesful execution of DSN1COPY you must do two things before the tablespace including tables and indexes are ready for use. First of all you must issue the START DB2 command for the tablespace. Then you must run a recover of the indexes. Otherwise they will be totally useless as they point to rows in the tablespace before it was replaced. If you want to be able to run a normal recover of the tablespace you must also run a normal full image copy.

You might run into a series of odd errors when you start to use the table in the tablespace after the succesful execution of DSN1COPY. This is caused by differences between the definitions in the original table/tablespace compared to the replaced tablespace. You have to check if both tablespaces has the same SEGSIZE and same COMPRESSION. Check also that there is only one table in both tablespaces and that these two tables has the same column definitions in the same order. Maybe the image copy was taken before changes to the column definitions.

If you have read this tip as far as this point you might think that you must be very careful when using DSN1COPY. This is very true. You may run into deep trouble and experience very odd error messages from DB2 which tells you nothing about the real problem. If you are still interested you may read more about DSN1COPY in the Utility Guide and Reference. This link is for the latest update of DB2 version 8.