Cross database system DDL generation

General Description

Cross database system DDL generation describes the process of generating DDL statements to define, change, or remove database objects on a target DBMS while using the structure of a source DBMS. The central challenge is that the source and target DBMS are not of the same type and may differ regarding supported object types, structural conventions, and semantics.

In this context, DDL (Data Definition Language) knowledge and fundamental concepts of migration are presumed. The focus is not on specific database releases or technical implementation details, but rather on cross-system concepts, object handling, and typical pitfalls.

Common Challenges in Cross DDL Generation

DDL generation for heterogeneous environments entails several recurring issues:

Structural Gaps: Not all object types, properties, or relationships present in the source DBMS are available or meaningful on the target.
Data Type Mismatch: Mapping between similar but not identical data types is necessary; some loss of precision, behavior, or constraint enforcement may occur.
Semantics and Defaults: Defaults or implicit behaviors may differ for elements like tablespaces, sequences, or indexes.
Naming Conventions: Naming rules, case sensitivity, and reserved keywords vary between systems, potentially causing invalid DDL or unwanted renaming.
Implicit Dependencies: Structural dependencies (e.g., on triggers, constraints, or auto-generated objects) may exist that the DDL generator cannot resolve completely, especially when mappings are not 1:1.
Unsupported Features: If the source uses constructs not present in the target system (e.g., advanced partitioning, functional indexes, specific allocation options), generation may fail, require user intervention, or produce a warning indicating manual post-processing is needed.

In practice, these challenges require configuration, awareness, and, in edge cases, user input to address ambiguities or gaps.

Underlying Data Model – Object Mapping

Internally, the DDL generator uses an abstract data model, enabling consistent handling of all relevant database objects. In the context of cross-DDL generation, this mapping process includes the following main object types:

Tables

Tables are mapped as abstract entities with their core attributes (names, columns, constraints). Where the target system demands properties not defined in the source, either a generic default or a user-supplied rule is applied.

Table Partitions

If the source system uses table partitioning, the DDL generator attempts to translate this into the target system’s partitioning scheme. If no direct equivalent exists, the partitioning information may be omitted, or a warning is issued to indicate manual review is necessary. Typically, it is the range partitioning scheme that is supported best across different DBMS products.

Columns

Each column is described with its name, data type, and constraints. The DDL generator attempts to translate source types into the closest matching target type. If no direct mapping exists, a more generic type is selected, or user intervention is required.

Indexes

Indexes, including their uniqueness and column lists, are mapped where feasible. Functional or partial indexes might not be supported everywhere; these mappings may be skipped or generate warnings to ensure task completion transparency.

Tablespaces

Tablespaces exhibit considerable diversity across DBMS products. For some systems, tablespaces are required and demand additional properties (e.g., specifying a database container). In others, this construct may not exist. The DDL generator takes the source tablespace definitions as they are and applies them to the target, ignoring layout recommendations for the target. Common properties are transferred where possible. Else, defaults or user-defined mappings are used.

Aliases

Aliases—such as synonyms or alternative object names—are mapped when the target system provides a corresponding concept. Otherwise, these may be omitted or noted as not transferable.

Sequences

Sequences for generating unique values are supported if the target offers equivalent functionality. Properties like start value, increment, cache size, and cycling are translated as precisely as possible; unsupported properties are ignored or replaced by closest options.

Effects and Solutions using Data Model Defaults

Where the target system requires properties absent in the source, the DDL generator fills in such gaps from a set of DBMS-specific defaults. This may affect allocation sizes, storage clauses, or naming conventions. Where fully automated mapping is not possible, placeholders or rules can be defined by the user to guide the translation process.

When no suitable solution exists for an object or attribute, the generation process is designed to be transparent: warnings or errors with actionable information are produced, enabling manual follow-up.

Therefore, a manual review of the generated DDL is recommended, especially in complex migration scenarios or when critical structures are involved.

Typical Errors and Edge Cases

Data type discrepancies: For example, mapping variable-length character data or numerical precision between DBMS can result in data truncation or conversion errors.
Unsupported objects: Certain system-specific features (e.g., partitioned tables, compressed indexes, user-defined types) may be skipped or result in incomplete DDL.
Inconsistent dependencies: If structural dependencies (such as references via foreign keys, triggers, or functions) cannot be satisfied on the target, referential integrity may be compromised, or deployment may fail.
Naming collisions or illegal identifiers: The use of reserved words, case mismatches, or invalid characters can lead to rejected DDL; automated renaming or quoting may partially mitigate this but does not resolve ambiguity in all cases.
Ambiguous or missing mapping rules: If user-supplied rules do not exist for essential mappings (e.g., functional index expressions, mandatory tablespace/database names), DDL generation will skip these components or request user intervention.
Defaults masking design flaws: Blind application of defaults can hide errors in the source or misrepresent the target’s structural intent—users should review generated DDL in critical scenarios.

Use Cases and Solutions

Several common scenarios illustrate the handling of edge cases:

Functional indexes: Where the source system uses indexes on expressions (such as function-based indexes), the DDL generator tries to translate supported functions if equivalents exist. Otherwise, such indexes are omitted with a warning. The mapping has to be defined in a mapping rule for the field INDEX_KEY_COLUMN_EXPRESSION
Tablespace assignment for DB2 z/OS: Since tablespaces in DB2 z/OS require explicit database names, but many source platforms do not carry this property, users must provide mapping rules for correct DDL generation.
Data type limitations: When no suitable mapping exists for a data type (e.g., geospatial or XML types), these columns or tables may be skipped, and the user is advised to adapt the source or provide conversion guidance.
Unsupported constraints: Constraints, triggers, or foreign keys referencing objects not present or not mappable in the target system are either omitted or trigger a warning for the user to review.

Supported DBMS and Object Types

The DDL generator is designed to support a variety of database platforms and object types. For up-to-date lists of DBMS support and object mapping coverage, refer to:

The supported data types for each system can be found in the respective connection documentation.

DDL Generation for IMS is not supported, as it uses a different data definition approach not based on standard DDL statements.

DDL Generation for Db2 for i is not supported.

flowchart TD A(Source Structure) --> B(Internal Model) B --> C(Target Model Mapping) C --> D(Apply Defaults & Rules) D --> E(Generate Target DDL)

Summary

Cross database system DDL generation requires a careful mapping of structure and semantics, given the many differences between database platforms. While automation and defaulting mechanisms cover many scenarios, error handling and transparency are essential—especially for unsupported features and edge cases. Users are advised to review the generated DDL, particularly in migration and complex integration scenarios, to avoid structural inconsistencies or data loss.

Customizing Cross DDL Generation Examples

Example: Converting function-based indexes to a PostgreSQL database

The following example shows how to map any easy function for PostgreSQL as target system. An easy function in this context means both database systems has implemented the function for the same column types. For PostgreSQL, we ensure, that the columns in the key function expression are quoted with " to avoid problems with case-sensitivity. Our helper function crossDDLQuoteKeyExpression will quote the columns in expressions like UPPER(NAME) to UPPER("NAME").

if (inputValue) {
  outputValue = Packages.de.ubs.xdm.utils.core.Rename.crossDDLQuoteKeyExpression(inputValue);
}

Database name mapping for tablespaces on Db2 z/OS

For a scenario in which DDL should be generated for a Db2 for z/OS target system using a non-z/OS system as source system, it is necessary to define the database name of the included tablespaces. That is because tablespaces need a specified database name only for Db2 z/OS database systems but not for any other database system. This leads to the fact that such database names for tablespaces are not defined in the source system in this specific scenario.

Therefore, the user has to define this information so that XDM is able to produce correct DDL. This is done via a Database specific mapping rule using the object type SYSTABLESPACE and the field type DBNAME on the respective task or task template.