Comparing BCV5 with DSN1COPY Print

The mechanism that BCV5 uses to move data is based on copying the VSAM clusters that DB2 uses to store tablespace and indexspace data. There is DB2 utility that works in a similar way: DSN1COPY. The speed of DSN1COPY and the built-in copy utility of BCV5 are comparable if you copy a single cluster. When you want to copy an entire database or even multiple databases with all associated tablespaces and indexspaces, the copy utility of BCV5 has the advantage of integrated parallel processing with a user-definable number of threads. To emulate this parallelism with DSN1COPY, you need to create an elaborate job chain and execute it under the control of a job scheduler. When the number of data sets becomes larger, you must also take other limitations like the maximum number of steps in a job into consideration when using DSN1COPY.

The main problem with DSN1COPY, however, is the extremely low extend of automation that is built into this utility. Before the utility can be used, you must do a manual comparison of the source and target object structure in order to ensure that all attributes of all involved objects match. Some of these attributes need to be passed to the program in the form of invocation parameters to achieve usable results. The values that you need to look up are scattered over many catalog tables and need to be retrieved using multiple queries. In addition, you sometimes have to define some of the clusters in the target environment by yourself because DSN1COPY requires that all target clusters already be present.

First of all, you need to determine which datasets to copy. Let’s say you want to copy a single tablespace with all its tables and indexes. If the tablespace is partitioned, it consists of more than one dataset. Each partition may have been subject to a fast switch operation, which changes the dataset name of the corresponding VSAM cluster. Also, the indexspaces can have a mangled name that is derived from the actual index name. Indexes, of course, may be partitioned as well. To illustrate the complexity of this seemingly simple task, here’s a query that can be used to give you the name of all datasets that belong to the tablespace DB000001.TS000001 and its indexes. Note that you have to insert the database name and tablespace name in both parts of the UNION when you want to use this query.

SELECT 
  STRIP(P.VCATNAME) !! '.DSNDBC.' !! STRIP(S.DBNAME) !! '.' !! 
  STRIP(S.NAME) !! '.' !! P.IPREFIX !! '0001.' !! 
  SUBSTR('ABCDE', 1 + (MAX(P.PARTITION, 1) / 1000), 1) !! 
  SUBSTR(VARCHAR((MOD(MAX(P.PARTITION, 1), 1000)) + 1000), 2)
FROM 
  SYSIBM.SYSTABLEPART P 
INNER JOIN 
  SYSIBM.SYSTABLESPACE S 
ON 
  P.DBNAME = S.DBNAME AND 
  P.TSNAME = S.NAME 
WHERE 
  S.DBNAME = 'DB000001' AND 
  S.NAME   = 'TS000001' 
UNION ALL 
SELECT 
  STRIP(P.VCATNAME) !! '.DSNDBC.' !! STRIP(X.DBNAME) !! '.' !! 
  STRIP(X.INDEXSPACE) !! '.' !! P.IPREFIX !! '0001.' !! 
  SUBSTR('ABCDE', 1 + (MAX(P.PARTITION, 1) / 1000), 1) !! 
  SUBSTR(VARCHAR((MOD(MAX(P.PARTITION, 1), 1000)) + 1000), 2)
FROM 
  SYSIBM.SYSINDEXPART P 
INNER JOIN 
  SYSIBM.SYSINDEXES X 
ON 
  P.IXNAME = X.NAME AND 
  P.IXCREATOR = X.CREATOR 
INNER JOIN 
  SYSIBM.SYSTABLES T 
ON 
  T.CREATOR = X.TBCREATOR AND 
  T.NAME = X.TBNAME 
INNER JOIN 
  SYSIBM.SYSTABLESPACE S 
ON 
  T.DBNAME = S.DBNAME AND 
  T.TSNAME = S.NAME 
WHERE 
  S.DBNAME = 'DB000001' AND 
  S.NAME   = 'TS000001'

This query is already quite complex, but there are two things missing: It doesn’t take into consideration that a non-partitioned tablespace may have multiple pieces, and that the qualifier I0001 (or J0001) is sometimes I0002 (or J0002) if clone tables are involved.

Knowing which datasets to copy is only half the battle. The following list of options illustrates the plethora of attributes that you need to look up for every single dataset that you want to copy using DSN1COPY:

  • PAGESIZE: You must tell DSN1COPY the correct page size of each tablespace that you copy, which is determined by the bufferpool in which the tablespace is located. This is one of the few options that DSN1COPY can determine by itself, but only if the input dataset contains a header page. For tablespaces that consist of multiple pieces, the detection mechanism will fail for the second and all subsequent pieces. BCV5 will determine all required values automatically for you.
  • FULLCOPY: If you copy from image copy datasets, you must specify this option for each invocation of DSN1COPY might produce unusable target datasets, especially if you copy a segmented tablespace. Of course, if you want to copy from an image copy, you must not specify the tablespace’s VSAM cluster as input for DSN1COPY, but the image copy data sets, whose names must first be extracted from the SYSIBM.SYSCOPY table. BCV5 automatically takes care of the different processing requirements between actual tablespaces and image copies.
  • LOB: For each tablespace, you need to tell DSN1COPY if it is a LOB tablespace or not. If you do copy a LOB tablespace without specifying this option, the DSN1COPY manual states that the results may be unpredictable. BCV5 will handle this automatically for you.
  • DSSIZE/LARGE: You need to know the data set size for all tablespaces that you want to copy. You may or may not be able to retrieve the correct value from the DSSIZE column of the SYSIBM.SYSTABLESPACE table. In some cases, this column contains the value 0 and you have to calculate the correct DSSIZE, which is based on the tablespace type, the number of partitions, and the page size. BCV5 will find out the correct value for you.
  • PIECESIZE: You need to specify the correct piece size for all non-partitioned indexes, which can range from 256 KB to 4 GB. BCV5 will find out the correct value automatically for you.
  • NUMPARTS: For each tablespace, you need to look up the total number of partitions. If you specify a wrong number of partitions, DSN1COPY might mix up which data goes into which VSAM cluster, especially if you copy from an image copy dataset. BCV5 handles this automatically for you.
  • OBIDXLAT: For each table in each tablespace, you need to look up the internal object identifiers (DBID, PSID and all OBIDs) that DB2 uses in both the source and the target environment. Depending on the number of tables per tablespace, the list of OBIDs that you must create can become very long. The slightest error in this list will result in an unusable tablespace in the target. BCV5 automatically handles this translation for you.
  • RESET: If you copy tablespaces between different DB2 subsystems, you must not forget to specify the RESET option. Otherwise, you may get errors when you work with the target table space, either because of the invalid log RBA that is stored inside the pages of the tablespace, or because of the down-level detection mechanism of the target DB2 subsystem. BCV5 will automatically reset these values during the copy.

Each of these options can be looked up in the catalog. For example, in order to determine the DBID, PSID, and list of OBIDs for a single tablespace, use something like this:

SELECT 
  'DBID OF DATABASE ' !! D.NAME, 
  D.DBID 
FROM 
  SYSIBM.SYSDATABASE D 
WHERE 
  D.NAME = 'DB000001' 
UNION ALL 
SELECT 
  'PSID OF TABLESPACE ' !! S.DBNAME !! '.' !! S.NAME,
  S.PSID 
FROM 
  SYSIBM.SYSTABLESPACE S 
WHERE 
  S.DBNAME = 'DB000001' AND 
  S.NAME = 'TS000001' 
UNION ALL 
SELECT 
  'OBID OF TABLE ' !! T.CREATOR !! '.' !! T.NAME, 
  T.OBID 
FROM 
  SYSIBM.SYSTABLES T 
WHERE 
  T.DBNAME = 'DB000001' AND 
  T.TSNAME = 'TS000001'

Execute this query in both the source and target environment. This will give you the information that you need to create the SYSXLAT member. Make sure you line up the OBIDs correctly, because they are returned unsorted by this query. For the indexspaces, you need to execute a similar query to find out DBID, PSOBID, and OBID of the index.

As if all these options weren’t already enough, there are several additional steps that you need to take before and after you use DSN1COPY.

You have to make sure that all attributes of all involved tablespaces, tables and indexes are identical in the source and target environment. Even small changes may render your copied tablespaces unusable. For example, if one of your target tables has a column that may contain NULL values, but that column in the corresponding source table may not contain NULL, you cannot use DSN1COPY to make a copy, even if everything else is identical. You have to drop and recreate the target table before you can use DSN1COPY. If you do the copy with DSN1COPY anyway, you will get error messages from the target DB2 when you try to access data from the target tablespace. Typically, you will get error 00C90101 or 00C90216 if the number of columns is different, or if the lengths or NULL attributes of the columns do not match.

BCV5 can detect this situation. You have the option of using Unload/Load as a fallback mechanism for copying the data. This is completely transparent, because BCV5 will generate all the required JCL and input statements for you. The other option is to drop and recreate target objects that don’t match the structure of the source objects.

Normally, when you drop and recreate an object, you must keep in mind that other objects may be lost as well due to the deletion of the first object. For example, dropping a table will always drop all related indexes, but you will also lose GRANTs and constraints that may have existed on the table, and that includes referential constraints to other tables. What seems like a quick drop and recreate can get out of hands very easily. BCV5 knows what has been dropped, and can recreate the target object as well as all dependent objects automatically for you.

If you have changed your source tables at any point in time using ALTER statements, or if you have dropped tables that were located in a multi-table tablespace, you also need to determine if you need to regorganize the tablespace before copying it. If you fail to do so, the target DB2 may give you error messages when you access the copied tablespace. Typically, when the target DB2 encounters a row with an unexpected OBID, it will give you error 00C9021C when accessing the tablespace that has been copied with DSN1COPY. In contrast, BCV5 will detect if an alteration has taken place that prohibits the direct copy on VSAM cluster level. In some cases, BCV5 is able to make a direct copy where DSN1COPY cannot be used. For example, BCV5 is able to remove all records from a tablespace that belong to a dropped table during the copy, which means you don’t have to run REORG on your source tablespace before copying.

You must also take care of allocation issues. The actual size of an object may differ from the information that is stored in the DB2 catalog. You need to do a LISTCAT on each of the clusters to find out the actual size. Then you must make sure that the target objects have allocation attributes that allow them to grow to the required size. BCV5 handles all the space calculation automatically and can redefine target clusters (without having to drop and recreate DB2 objects) if the corresponding tablespace or indexspace was created with too small allocation parameters.

Finally, for all tables that contain an identity column, you need to look up the highest value that is currently used in the source subsystem. You must explicitly set a higher number as the number that is used next for the target table using an ALTER statement. Otherwise, when you insert a new row into a target table, DB2 might try to use an identity value that is already in use, resulting in an error. BCV5 generates and executes these ALTER statements automatically.

In conclusion, while DSN1COPY can be automated to a certain degree using home-grown scripts and queries, its usage can be a pain if a large number of objects is involved, or if the set of objects to be copied changes. BCV5, on the other hand, offers unmatched flexibility in the selection, renaming and copying of DB2 objects and provides a streamlined, fully automated process that takes the pain out of fast copying.