Using Structure Compare Tasks Before Native Table Copy

This section describes a practical two step approach for cloning a complete or almost complete schema. The approach separates structural alignment from data transfer by combining a structure compare task with a native table copy task.

Separating these concerns is especially useful for applications with many foreign key dependencies, such as Guidewire. Problems may arise if foreign keys exist for tables that are not in the selection or should not be copied, and the tables are to be dropped and recreated. The structure compare task handles foreign keys and other dependent objects up front, while the native table copy task focuses purely on moving data.

Overview Of The Two Step Approach

The recommended setup consists of two tasks executed in sequence:

  1. Structure compare task

    • Compares source and target schemas.

    • Generates DDL to align the target schema to the source (tables, indexes, foreign keys, and other dependent objects).

    • Can be configured to create or rebuilt objects in the target without copying any data.

    • The configuration and comparison of the Structure Compare Task is more flexible and precise than that of a Native Table Copy Task, because the native table copy task only checks whether the data can be copied.

  2. Native table copy task

    • Uses the already aligned target structures to copy only data.

    • Lets XDM select the optimal data transfer mechanism (native unload/load utilities or generic CSV based copy) depending on the involved RDBMS types.

    • Does not need to generate or execute DDL.

This two step approach decouples schema management from data movement. It avoids situations where foreign keys in the target schema interfere with table selection in the table copy task, because foreign key drop and recreate operations are managed centrally in the structure compare task.

Configuring The Structure Compare Task

Defining Source And Target Connections

Configure the structure compare task as follows:

  • Source connection – The application source schema, for example your productive or golden copy Guidewire environment.

  • Target connection – The target schema that should receive the structural clone.

The structure compare task will read metadata from both connections and then compare corresponding objects.

Selecting The Schema

To copy a complete schema, create selection rules as follows:

  • Define selection rules on all tables of the application schema, for example:

  • Pattern like GW_APP.* to include all Guidewire tables.

  • Ensure that the selection also includes all base and referenced tables that own or reference foreign keys into your selected tables.

During execution, the task collects all selected tables and their dependent objects from the source and target environments before comparing structures.

Excluding Unwanted Tables

Create exclude rules for tables from the schema that are not to be copied:

  • Use exclude rules sparingly.

  • If you exclude tables that are still referenced by foreign keys, make sure that:

  • no foreign keys in the source schema point to the excluded tables, or

  • those foreign keys are explicitly handled in your DDL strategy.

Otherwise the structure compare task will detect inconsistent foreign key relationships between source and target, and may create objects that you do not intend.

Defining Mapping Rules For Schemas

If source and target use different schema names, or other properties differ between source and target, configure mapping rules on the structure compare task:

  • Example:

  • Source schema: GW_SRC

  • Target schema: GW_TGT

  • Add mapping rules so that the task maps objects from GW_SRC to GW_TGT.

Mapping rules allow the task to match and compare tables and other objects between environments even if schema, table, or column names differ.

Choosing The Toleration Mode

Use the toleration mode property to define how structural differences are treated.

For a true 1:1 clone of an application schema:

  • Prefer Strict – No structural differences between source and target tables are allowed. The comparison will stop if any difference is detected.

  • Alternatively, use Prevent truncation – Only columns that can hold equally large or larger values in the target are tolerated.

Use Allow truncation only if you have agreed that potential truncation is acceptable and have implemented proper truncation logic with modification rules where needed.

For more background, see Toleration Mode In Comparing And Generating Objects.

Creating Objects in the Target Environment

Configure the structure compare task to generate DDL for structural changes:

  • Enable DDL generation for:

  • Creating missing tables and indexes.

  • Adjusting columns that differ between source and target.

  • Creating or recreating foreign keys and other dependent objects.

  • Decide whether the task should:

  • only generate DDL and reports, or

  • generate and directly execute DDL in the target environment.

A commonly used pattern is:

  1. First run (dry run)

    • Generate DDL and compare reports only.

    • Do not create or drop any objects in the target.

    • Review reports and scripts with DB administration.

  2. Second run (activation)

    • Let the structure compare task execute the approved DDL

After this step the target schema is be structurally aligned with the source, including foreign keys, but still without any application data.

When existing tables differ between source and target, use Drop option Drop all and recreate or Only differing objects to recreate the tables. Only differing objects is recommended, if only a few tables have to be recreated.

Configuring The Native Table Copy Task

Once the target schema matches the source structurally, configure a native table copy task to move the data. In this situation, we assume a one-to-one copy in which the data is not masked. However, reduction and modification rules are possible to reduce or modify data during the copy. We will describe the

Here we describe the configuration of the native table copy tasks that are important for our situation. An overview of what needs to be configured and taken into account in the native table copy tasks can be found in the reference.

Reusing Connections And Schema Selection

Set up the task as follows:

  • Source connection – Same as in the structure compare task.

  • Target connection – Same as in the structure compare task.

Define selection rules for the data copy:

  • Typically, select all tables of the same application schema (for example GW_APP.*).

  • Optionally restrict the selection if some tables should not receive data (for example audit tables or technical logs).

The task will collect all matching tables and their dependent objects on the source, then find corresponding target objects based on mapping rules.

Disabling Structural Changes In Table Copy

Because schema alignment has already been done by the structure compare task, configure the native table copy task so that it does not change structures:

  • Set the Drop option (or equivalent DDL settings) to a mode such as:

  • Drop nothing and create nothing.

  • Ensure that the task only:

  • extracts data from source tables, and

  • loads data into the existing target tables.

With this configuration, the native table copy task does not need to drop or recreate foreign keys. All foreign key handling, including disabling constraints during load if necessary, should be managed on the structure compare side or by the database itself.

Recommended Procedure For Large Application Schemas

For large application schemas (e. g. Guidewire), which typically have many interrelated tables and foreign keys, the following procedure is recommended.

Step 1: Create And Test The Structure Compare Task

  1. Create a new structure compare task template and task for the Guidewire schema.

  2. Configure:

    • Source connection – Productive or golden copy Guidewire schema.

    • Target connection – Target Guidewire schema.

    • Selection rules – Include the complete Guidewire schema.

    • Mapping rules – Map source schema name to target schema name if they differ.

    • Toleration mode – Start with Strict to detect structural issues early.

  3. Run an initial execution as a dry run until Stage 3:

    • Interrupt execution after Stage 3.

    • Generate DDL and comparison reports only.

    • Do not execute DDL automatically yet.

Review the generated reports and DDL:

  • Check whether all required tables and dependent objects of the source are included.

  • Look for unexpected table drops, column changes, or incompatibilities to the unchanged target objects, for example new NOT NULL primary key columns in the target that do not exist in the source.

Step 2: Apply The Generated DDL

Once the DDL has been reviewed and approved:

  • Configure the structure compare task to execute the DDL in the target automatically in the next run.

After successful execution, the target schema does:

  • contain all required tables, indexes, and foreign keys, and

  • be structurally compatible with the source for all tables that will receive data.

Step 3: Run The Native Table Copy Task

Create and configure a native table copy task for the same schema:

  • Use the same source and target connections as in the structure compare task.

  • Reuse the same mapping rules for schema names.

  • Configure selection rules for all tables that should receive data.

  • Set drop and DDL options so that the task does not change structures (Drop nothing and create nothing).

Execute the task and verify that:

  • data is loaded into the existing target tables without structural errors.

Because constraints are already aligned and do not need to be dropped or recreated during the table copy, foreign key problems due to partially selected tables are significantly reduced.

Step 4: Perform End To End Validation

After the first end to end run:

  • Inspect the task reports from both tasks:

  • Structure comparison and DDL reports from the structure compare task.

  • Data extraction, modification, and data load reports from the native table copy task.

  • Validate the application behavior in the target environment:

  • Check that referential integrity is maintained.

  • Confirm that all required Guidewire tables contain data.

If necessary, refine:

  • selection rules (to add or remove tables),

  • mapping rules (for additional schemas),

  • toleration mode configuration, and

  • reduction or modification rules.

Once stable, this two step combination of structure compare and native table copy tasks can be reused as a standard pattern for regularly cloning Guidewire like schemas between environments.