Migrating from Test to Production › General Methods › For SQL-Defined Entities

For SQL-Defined Entities

Using DISPLAY and PUNCH

SQL-defined entities (schemas, tables, and so on) can be migrated using the DISPLAY and PUNCH techniques described earlier in this chapter. This approach is useful for creating a second entity that has the same definition as one in test.

Including All Related Entities

To generate syntax for all entities related to an entity whose definition is being displayed or punched, specify the FULL option.

For example, the following statement will generate syntax for all entities in the EMP schema.

display schema emp full as syntax

The next statement will generate syntax for the EMP.DEPT table and its associated indexes, constraints and calc keys.

display table emp.dept full as syntax

Replicating Physical Attributes

By default, when a DISPLAY or PUNCH statement is used to recreate the DDL for an SQL-defined entity such as a schema, the definition will be logically identical to the original. However, certain physical attributes that normally are assigned internally by the DBMS when an entity is created will not be the same if the generated DDL is used to create a new entity. These attributes include such things as a table's numeric identifier (its table ID) and the timestamps that are used to track when an entity's definition is changed.

Ensuring that physical (as well as logical) attributes are identical for all entities associated with an area or segment, facilitates copying of data from one area or segment to another. For example, you could use operating system facilities to copy production files to a quality assurance environment for testing purposes if the physical attributes of both are identical.

Note: To copy files this way, the definitions of the segments in which the files are defined must also be identical except for segment name and page group.

It is possible to define new entities with the same physical attributes as those of another entity by specifying the FULL PHYSICAL clause on the DISPLAY or PUNCH statement. This will cause additional syntax to be generated that will explicitly establish values for the physical attributes of the new entity.

The following entities have physical attributes that need to be considered:

• Schema
• Table
• View
• Table Procedure
• Procedure
• Function
• Index
• Area

Keeping Physical Attributes Identical

Each time an SQL-defined entity definition is directly or indirectly changed, its definition timestamp is changed automatically. For example, if a new index is added to a table, the table's timestamp is updated automatically. To keep two or more entity definitions identical, and after making the same change to the cloned definition, you must use an ALTER statement to set the cloned entity's timestamp to be the same as that of the original entity.

For example, the following procedure ensures that two tables have the same timestamp after adding an index to each of them.

1. Add the index to the first table:

   create index emp.depts on emp.dept (name)
   

2. Determine its new timestamp value:

   display table emp.dept with timestamp
   

3. Add the index to the second table:

   create index emp2.depts on emp2.dept (name)
   

4. Force the second table's timestamp to be the same as the first:

   alter table emp2.dept timestamp 'yyyy-mm-dd-hh.mm.ss.tttttt'
   

Note: For more information about SQL DDL, DISPLAY and PUNCH statements, see the CA IDMS SQL Programming Guide.

Area-Level Timestamps

If an area was defined with the STAMP BY AREA clause, then each time a change is made to the definition of any table stored in the area, the area's timestamp is updated. This is the timestamp that must be manipulated to maintain identical definitions. An area's timestamp is also updated whenever an associated table is created or dropped.