Description:
To transport a database containing SQL objects between CA Datacom environments, use the DataDictionary Transport Utility (DDTRSLM). However, the DDL exported by DDTRSLM cannot be used by itself to create an exact copy of the database entity. This document explains why.
Solution:
To transport a database containing SQL objects between CA Datacom environments, use the DataDictionary Transport Utility (DDTRSLM). Execute the DDTRSLM utility in the development DataDictionary to EXPORT a structure and create a transport file. Use the utility in the production DataDictionary to IMPORT the structure from the transport file. (See the CA Datacom DataDictionary Batch Reference Guide for more information.)
You can also use the DDTRSLM utility to transport other SQL objects such as plans, views, synonyms, procedures, and triggers. The source site must have valid, cataloged SQL objects to be migrated. That means that tables must have an AUTHID and SQL name and plans must be executable. However, DDTRSLM cannot be used to export definitions which could then be imported into other databases. This document explains why.
When DDTRSLM Imports a Datacom object, it does so in two passes, first using DDL then using the DataDictionary Service Facility (DSF). For example, the DDL in the TRANSF file for one of our test tables is:
CREATE TABLE SYSADM.BASTBL ( DBID CHARACTER (00005) FOR SBCS DATA NOT NULL, FILFLD CHARACTER (00003) FOR SBCS DATA ) IN JJP01_AREA DATACOM NAME BAS; CREATE INDEX BASKEY ON SYSADM.BASTBL ( FILFLD) DATACOM NAME SQ007; The original CREATE statement for the key was: CREATE UNIQUE INDEX SYSADM.BASKEY ON SYSADM.BASTBL (FILFLD); But the DDL produced by DDTRSLM on an export will simply read CREATE INDEX SYSADM.BASKEY ON SYSADM.BASTBL (FILFLD);
The original CREATE statement for the key was:
CREATE UNIQUE INDEX SYSADM.BASKEY ON SYSADM.BASTBL (FILFLD);
But the DDL produced by DDTRSLM on an export will simply read
CREATE INDEX SYSADM.BASKEY ON SYSADM.BASTBL (FILFLD);
The UNIQUE keyword will not be present.
On the first pass of the import, an SQL request creates a key occurrence which would be non-unique based on the CREATE statement shown above. However, additional attribute information for the key is stored in an additional output dataset created by the export, known as the transport file and identified by a DDname of TRANSF in the DDTRSLM JCL. The TRANSF data for the table example above is shown below. The second pass of the DDTRSLM import will update the attributes for the key using DSF. This modifies the key definition to make it unique.
%%KEY SQ007 %DDSUPSTART RECORD-NAME SYSADM-BASTBL SQLNAME BASKEY DATACOM-NAME SQ007 INCLUDE-NIL-KEY Y MASTER-KEY N NATIVE-KEY N UNIQUE Y -- this is the information which is processed to make the key unique. FOREIGN N DATACOM-ID 007 DIRECT N KEY-TYPE I %DDSUPEND
After the Import, checking the attributes for the key shows that it is defined as unique in the dictionary, so it will be recreated correctly using our standard procedures with no problem.
The output which DDTRSLM creates on an export is designed to dovetail with the two-step DDTRSLM import process. Therefore the DDL that it exports will not necessarily contain all details about the database.
Because of the complexity outlined above, it is not possible to use the DDTRSLM export process as a single stand-alone step to create duplicates of existing Datacom entity definitions. The output from the DDTRSLM export process cannot be used as input to another database, if the goal is to produce a structure that is identical to the original Datacom database.
Since DDTRSLM was designed to be used within the Datacom environment, and since it's output functions correctly in that context (as outlined above), this is working as designed.