Control Tables

Overview

As mentioned in other sections, a conversion run is usually divided into two parts:

• Convert all the data in the staging schema, STGADM.
• Copy the converted data from the staging to the production schema, CISADM.

These two steps can be repeated many times, so both activities should be repeatable individually and when executed together. The data conversion could be executed many times before the second step. Additionally, the copy-to-production process should also be repeatable to accommodate issues that might arise with the copy and place the previously copied data if the first step is repeated after the second step.

As a rule, the conversion in the staging schema should be configured by the conversion team such that previously converted data gets deleted from the staging tables before the tables are populated. It should be built into the flow as described under Data Migration Flow.

To ensure the copy-to-production process is repeatable, the tool must be capable of deleting any previously converted data from the production schema, CISADM, before executing the copy again. For a new conversion, the target tables in the production schema are likely empty and can be truncated prior to repeating the copy. In a phased conversion, however, the deletion must be more precise, as the production schema will already contain data that the conversion should not alter.

The precise replacement of converted data can be achieved by tracking the converted data in a separate table and using that to identify the rows to delete. The CX_CONVERTED_ID table is the suggested approach for moderate data volumes. For higher volumes, separate tables may perform better and be easier to manage, although the same general approach could still be used.

CX_CONVERTED_ID table

The CX_CONVERTED_ID table keeps track of the converted data that was copied from the staging to the production schema.

This table is created in the staging schema by running the following:

create table cx_converted_id (
    table_name varchar2(128 byte),
    cx_id varchar2(128 byte),
    constraint cx_converted_id_pk primary key (cx_id))

It contains the table_name of the copied table and the primary ID in the cx_id column. For tables with composite keys, only the leftmost, most significant part of the ID is stored. For instance, the primary key of the CI_PER_NAME table consists of PER_ID and SEQ_NUM, but in this table, only the distinct PER_ID is stored for CI_PER_NAME, regardless of how many names were converted for a person. This ID is sufficient for the tool to determine what to delete when necessary.

The CX_CONVERTED_ID table is used by the SQL_DELETE_FROM_CISADM and SQL_COPY_TO_CISADM executables. The SQL_DELETE_FROM_CISADM removes all data from the production schema that exists in this table and then deletes the rows from this table. Meanwhile, the SQL_COPY_TO_CISADM copies relevant tables from staging to production and populates the CX_CONVERTED_ID table with their table names and IDs.

CX_CONVERTED_TABLE table

The CX_CONVERTED_TABLE is an informational table that specifies the primary key column name for each table listed in the CX_CONVERTED_ID table. The CX_CONVERTED_ID table uses the column CX_ID to represent all key values. This structure helps identify the corresponding column name for the CX_ID value in each of the tables.

To create the CX_CONVERTED_TABLE in the staging schema, run the following.

create table cx_converted_table (
    table_name varchar2(128),
    column_name varchar2(128),
    constraint cx_converted_table_pk primary key (table_name))

The table is populated after all data has been copied to the production schema. The executable POST_PUSH_PROC, provided with the tool, performs this action using the following SQL statement.

insert into cx_converted_table (table_name, column_name)
select id.table_name, tc.column_id
from
    (select distinct table_name from cx_converted_id) id,
    (select table_id,
            column_id,
            row_number() over(partition by table_id order by column_seq asc) row_num
       from ua_table_column
      where primary_key = 1) tc
where id.table_name = tc.table_id
  and tc.row_num = 1
order by table_name