Using the API to set up IBM Optim relationships

Edit online

To define an IBM® Optim relationship by using the API, you must create a JSON pipeline job with a relationship_definition node.

IBM Optim relationships are defined in the relationship_definition node of a pipeline job JSON. The relationship_definition node is on the same level as the other execution nodes in the pipeline, such as the access_definition and insert_definition nodes.

Before you begin

Ensure that you complete following prerequisites:
  • Ensure that both the parent and child tables are defined in the access_definition node.
  • Obtain the table IDs for both tables.

About this task

The JSON payload of the relationships_definition node includes the following parameters:
Table 1. Parameters of relationships_definition JSON
Parameter Description
id A random UUID for the node.
type The type of node. This must be set to execution_node.
op The type of operation. This must be set to relationship_definition.
app_data.relationships An array of relationship objects.
Each object includes the following parameters:
  • id: A random UUID for the object.
  • parent_table_id and child_table_id: These values must match the IDs in the app_data.source_tables parameter of the access_definition node.
  • relationship_conditions: An array of column mappings between parent and child tables.
    Tip: Use consistent naming for columns to simplify mapping and reduce errors.
Note: Note the following limitations of relationship conditions:
  • Matching is limited to column names only.
  • Expressions such as substring, concatenation, or string literals are not supported.

Sample relationship_definition JSON

Refer to the following sample JSON for an example of how to structure the execution_node of your pipeline job JSON.

{
    "id": "d4b2e988-f2d9-4e30-8396-fb20df4fb560",
    "type": "execution_node",
    "op": "relationship_definition",
    "app_data": {
        "relationships": [
            {
                "id": "1423d17e-ca13-47ba-b51a-92fd8ee7430b",
                "parent_table_id": "788c474f-b637-4521-be38-5ca72dbee838",
                "child_table_id": "6912f124-593f-46ef-aaa7-e953afb9c997",
                "relationship_conditions": [
                    {
                        "parent_conditions": {"column_name":"c1"},
                        "child_conditions": {"column_name":"d1"}
                    },
                    {
                        "parent_conditions": {"column_name":"c2"},
                        "child_conditions": {"column_name":"d2"}
                    }
                ]
            }
        ]
    }
}

Defining IBM Optim relationships in a pipeline job

To define relationships IBM Optim in a pipeline job:

  1. Open the pipeline job JSON file.
  2. Add an execution_node with:
    • op set to relationship_definition.
    • app_data.relationships containing one or more relationship objects.
  3. For each relationship object:
    • Assign a unique UUID to the id parameter.
    • Specify the parent_table_id and child_table_id values. The IDs must match the table IDs from the access definition.
    • Define column mappings in relationship_conditions.
  4. Validate the JSON structure.
  5. Submit the job by using the following endpoint:
    POST /v1/flow/submit/{id}