Synchronizing Structures Before Native Copy

Overview

This guide describes a practical two-step approach for cloning a complete or almost complete database schema.

The approach separates structural alignment from data transfer by combining a Structure Compare Task and a Native Table Copy Task.

Separating these concerns is especially useful for applications with many foreign key dependencies, such as Guidewire.

Problems may occur if foreign keys exist for tables that are not part of the selection or should not be copied, especially when tables are dropped and recreated. The Structure Compare Task handles foreign keys and other dependent objects first, while the Native Table Copy Task focuses only on moving data.

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 with the source schema.

    • Handles tables, indexes, foreign keys, and other dependent objects.

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

    • Provides more flexible and precise structure comparison than the Native Table Copy Task.

  2. Native Table Copy Task

    • Uses the already aligned target structures.

    • Copies only data.

    • Lets XDM select the optimal data transfer mechanism, such as native unload/load utilities or generic CSV-based copy.

    • 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.

Prerequisites

Before using this approach, ensure that:

  • Source and target database connections are configured.

  • The executing user has sufficient permissions to read source metadata and data.

  • The executing user has sufficient permissions to create, drop, or modify objects in the target schema, if DDL should be executed.

Steps

Step 1: Configure the Structure Compare Task

The Structure Compare Task is used to align the target schema with the source schema before any data is copied.

Define Source and Target Connections

Configure the Structure Compare Task as follows:

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

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

The Structure Compare Task reads metadata from both connections and compares the corresponding objects.

Select the Schema

To copy a complete schema, create selection rules for all required tables of the application schema.

Example:

  • Include all Guidewire tables, for example with a pattern like GW_APP.*.

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

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

Exclude Unwanted Tables

Create exclude rules for tables from the schema that should not be copied. Use exclude rules carefully.

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 may detect inconsistent foreign key relationships between source and target and may create objects that you do not intend.

Define Mapping Rules for Schemas

If source and target use different schema names, configure mapping rules in 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.

Choose 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 stops if any difference is detected.

  • Prevent truncation: Only columns that can hold equally large or larger values in the target are tolerated.

Use Allow truncation only if potential truncation is acceptable and proper truncation logic with modification rules has been implemented where needed.

For more background, see Toleration Mode in Comparing and Generating Objects.

Create 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 comparison reports only.

    • Do not create or drop objects in the target.

    • Review reports and scripts with database administration.

  2. Second run: Activation

    • Let the Structure Compare Task execute the approved DDL.

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

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

Step 2: Configure the Native Table Copy Task

After the target schema matches the source structurally, configure a Native Table Copy Task to move the data.

In this scenario, we assume a one-to-one copy where the data is not masked. However, reduction and modification rules can still be used to reduce or modify data during the copy.

This section describes the most relevant configuration points for this approach. A full overview of the Native Table Copy Task configuration can be found in the reference.

Reuse Connections and Schema Selection

Configure 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 collects all matching tables and their dependent objects on the source side and then finds corresponding target objects based on mapping rules.

Disable Structural Changes in the 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 or DDL options to a mode such as:

Drop nothing and create nothing

Ensure that the task only:

  • extracts data from source tables, and

  • loads data into 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 by the Structure Compare Task or by the database itself.

Step 3: Recommended Procedure for Large Application Schemas

For large application schemas, such as Guidewire, which typically have many interrelated tables and foreign keys, the following procedure is recommended.

Step 3.1: Create and Test the Structure Compare Task

Create a new Structure Compare Task Template and task for the application schema.

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.

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 with unchanged target objects.

  • Check for issues such as new NOT NULL primary key columns in the target that do not exist in the source.

Step 3.2: Apply the Generated DDL

After 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 should:

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

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

Step 3.3: Run the Native Table Copy Task

Create and configure a Native Table Copy Task for the same schema.

Configure the task as follows:

  • 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, for example 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 caused by partially selected tables are significantly reduced.

Step 3.4: Perform End-to-End Validation

After the first end-to-end run, inspect the task reports from both tasks.

Review:

  • Structure comparison and DDL reports from the Structure Compare Task.

  • Data extraction, modification, and 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 application tables contain data.

If necessary, refine:

  • selection rules

  • mapping rules

  • toleration mode configuration

  • reduction rules

  • 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.

Benefits

This approach provides the following benefits:

  • Structural alignment and data movement are handled separately.

  • Foreign keys and dependent objects are managed before data is copied.

  • The risk of foreign key issues during table copy is reduced.

  • DDL changes can be reviewed before they are executed.

Conclusion

Synchronizing structures before running a Native Table Copy Task is a reliable pattern for cloning large or complex database schemas.

The Structure Compare Task aligns the target schema with the source schema, including tables, indexes, foreign keys, and dependent objects. The Native Table Copy Task then copies the data into the already prepared target structures.

This separation improves control, reduces structural errors during data transfer, and provides a reusable approach for regularly refreshing Guidewire-like or similarly complex application schemas.