Mapping source and target columns (API)

Edit online

Configure column mappings to define how data from source columns maps to target columns during insert operations.

In IBM® Optim Archive for z/OS®, you can use the API to define flexible column-to-column mappings to be used during data insert operations. Instead of requiring identical column names and structures between source and target tables, you can use column mappings to specify how data from source columns maps to target columns.

Column mapping is limited to direct column-to-column name mappings only. This capability does not support transformation functions, expressions, or computed values. Mappings must reference existing column names from the source table schema.

Before you begin

Before you configure column mappings, ensure that you understand the following constraints:

  • Column mapping can only be achieved by using the IBM Optim Archive for z/OS API.
  • Each target column can have exactly one source mapping. Multiple source columns cannot map to the same target column.
  • Excluded columns cannot be used in mappings.
  • Target columns that are primary keys, foreign keys, or have unique constraints cannot be mapped to, as this would compromise referential integrity.
  • Nullable source columns cannot be mapped to NOT NULL target columns.
  • Source and target columns must have compatible data types. The system enforces exact type match or safe casting within the same type family, matching array types, compatible precision and scale for numeric types, and matching signed or unsigned attributes.

Mapping columns

To define column mappings between your source and target data, edit your JSON flow configuration file:

  1. In the flow configuration JSON, locate the insert_definition node and find the table_map array.
  2. Add a column_map array within the appropriate table mapping object.
  3. Add an object to the column_map array for each column mapping with the following fields:
    • source_column: The name of the source column to map from
    • target_column: The name of the target column to map to

    The following example shows an insert_definition node with a column mapping that maps the first_name source column to the last_name target column:

    {
    "id": "c50f88b6-fc88-460c-aa14-aa4141144de2",
    "type": "execution_node",
    "op": "insert_definition",
    "app_data": {
        "table_map": [
            {
                "id": "1423d17e-ca13-47ba-b51a-92fd8ee7430a",
                "source": {
                    "database": "testdb1",
                    "table_name": "employee_data",
                    "schema": "testschema1",
                    "source_table_id": "788c474f-b637-4521-be38-5ca72dbee838",
                    "connection_profile": "260cf2f2-7d44-408a-a5d4-012810572fa6"
                },
                "target": {
                    "database": "testdb1",
                    "table_name": "employee_data",
                    "schema": "testschema2",
                    "connection_profile": "a84fce94-7285-4214-8d0a-9b94e58993d3"
                },
                "column_map": [
                    {
                        "source_column": "first_name",
                        "target_column": "last_name"
                    }
                ]
            }
        ]
    }
}

When you run the workflow including the column mapping JSON, IBM Optim Archive for z/OS validates the column mappings before execution and applies the mappings during the data insert operation.