Creating an access definition

Before you begin

Before you define access conditions, ensure that there is a valid connection to the source that you want to use in this flow.

Creating an access definition by using the flow builder

You can define access definitions from within the flow builder tool by adding an Access definition node to a flow builder canvas. Access definition nodes automatically pull a source node into the flow, depending on their configuration.

To add an access definition to a flow:

1. From the navigation menu, click Workflow > Flow builder, then open a flow builder canvas:
   • Load an existing flow from the My flows panel.
     or
   • Import a previously exported flow from a valid JSON flow definition. For more information, see Importing and exporting a flow.
     or
   • Create a new flow by clicking New flow. Provide a name and description, then click Create flow.
2. Add an access definition node to the flow. You can either create a new access definition or add a predefined access definition from your node templates.
   • To create a new access definition, click Create an access definition.
   • To add an access definition based on an existing node template, drag an access definition template from the Node templates panel and drop it onto the canvas. For more information, see Creating and working with template nodes.
3. Provide a name and description for the access definition. Click Next.
4. Select the source type: database or extract.
   Note: After you create the access definition, you cannot later edit the source type that you select here.
5. Click Add table, then browse the existing sources to select a base table for this access definition. Click Add.
6. Add as many base tables and reference tables as needed.

   For database sources, you can add tables from more than one source connection as part of a single access definition.

   For extract sources, you can only add tables from one source per access definition.

7. Optionally, for each base table, click Select related tables to define tables that are related to the base table, either through relationship discovery or IBM Optim relationships.
   1. Click Select related tables.
   2. Define relationship detection rules to set the scope of relationship discovery for this table. Set the types of relationships you want to discover (parent, child, or both), then set the number of relationship levels to include. For example, 1 level will return only the primary relationship, 2 levels will return primary relationships and secondary relationships, and so on. Click Next.
   3. Select the related tables to include from the list of discovered relationships. Relationship discovery is based on primary key-foreign key relationships.

      For each related table you choose, you can optionally exclude columns by clicking Select columns.

      Click Next.

   4. Select the IBM Optim relationships to include for this table, or define additional relationships by clicking New relationship.
      Note: Table relationships are automatically disabled for the following cases:

      • The table is already selected as a base table.
      • The table is already selected as a related table in the previous step.
      • The table has excluded relationship-defining columns.
   5. Click Save.
8. Optionally, for each base table, click Exclude columns to configure any columns that you want to exclude from this flow.
   1. Click Exclude columns.
   2. Deselect any columns that you want to exclude. You cannot exclude columns that contain primary key information.

      For more information, see Managing column exclusion in access definitions.

   3. Click Apply.
9. Click Next. Review the access definition that you have configured, then click Create.
   The new access definition node automatically pulls any required source nodes into the flow.

   Note: Deleting an access definition node will automatically remove all associated source nodes from the flow.
10. Click Save to save the flow.
11. Finish building the flow by adding more nodes as necessary. For more information about creating flows, see Building an IBM Optim Test Data Management flow.

Managing column exclusion in access definitions

Column exclusion provides granular control over which columns are included in data extraction operations. You can selectively include or exclude specific columns from tables in access definitions to control which data is extracted and to improve data privacy compliance.

This capability helps organizations comply with data privacy requirements by excluding sensitive columns and reduces data exposure in non-production environments. You can manage column selections for source tables (base and reference tables) and related tables that are discovered through table dependencies.

When you run a flow that includes an access definition with columns excluded, only the selected columns get included in the data extraction. Excluded columns are omitted to protect sensitive data and reduce data set size.

Best practices for column exclusion:

• Identify and exclude columns containing personally identifiable information (PII), financial data, or other sensitive information.
• Maintain data utility by ensuring that excluded columns do not break referential integrity or remove data that is needed for operations.
• Document which columns are excluded and why to support compliance audits and team collaboration.
• Review column exclusions periodically to ensure that they align with current data privacy requirements and operational needs.

Creating access definitions by using the API

• For API usage, ensure that the following prerequisites are in place:
  • You have a valid access token (<accessToken>). For more information, see Retrieving access tokens for API usage.
  • You have the connection profile name (<connProfileName>), schema name (<schemaName>), and table names.

To create an access definition by using the API:

1. To create an access definition programmatically, send a POST request to https://VM_HOSTNAME:PORT/optim/v1/flow with a pipeline definition that includes source tables and relationships.

   Headers

   
   Authorization: Bearer <accessToken>
   Content-Type: application/json
         

   To obtain the source and target connection profiles (including encrypted passwords) required for the job JSON, use the following API endpoint:

   Replace {name} with the name of the connection profile. Be either the profile owner or an admin to retrieve this information.

   
   curl -k -X GET \
     -H "Content-Type: application/json" \
     -H "Authorization: Bearer <accessToken>" \
     "https://VM_HOSTNAME:7725/optim/v1/connprofiles/{name}"
   

   Sample request body

   
   {
     "id": "7e5ce0c7-bb3c-4c11-85d6-db34ea08ee8e",
     "version": "3.0",
     "app_data": {
       "optim": {
         "version": "2.0.0"
       }
     },
     "doc_type": "pipeline",
     "pipelines": [
       {
         "id": "e7fe76ec-662e-44e5-b514-bee48382d13c",
         "name": "Workflow-Sample",
         "nodes": [
           {
             "id": "11ec9ef1-2cc2-4dce-8008-a8ddb7a225c5",
             "op": "connection_profile",
             "type": "binding",
             "inputs": [],
             "outputs": [],
             "connection": {
               "ref": "f4dc15d1-25ee-4a20-800c-caa0d439e451",
               "name": "PgSQL"
             },
             "parameters": {
               "connection_type": "source"
             }
           },
           {
             "id": "28f22b9d-36cb-4eda-ad1a-4af5493d55e8",
             "op": "access_definition",
             "type": "execution_node",
             "app_data": {
               "source_tables": [
                 {
                   "id": "e30ed315-a011-4fd1-9712-a93ad792a7f9",
                   "schema": "public",
                   "database": "optim_test",
                   "table_name": "products",
                   "connection_profile": "f4dc15d1-25ee-4a20-800c-caa0d439e451"
                 }
               ]
             }
           },
           {
             "id": "57aa2b91-8284-4156-a097-1cf6be6369f2",
             "op": "connection_profile",
             "type": "binding",
             "inputs": [],
             "outputs": [],
             "connection": {
               "ref": "6a7b49eb-4de8-4390-9431-91bfe08fd0b6",
               "name": "DB2"
             },
             "parameters": {
               "connection_type": "target"
             }
           },
           {
             "id": "570e391b-2202-4648-a351-59a7c7755801",
             "op": "insert_definition",
             "type": "execution_node",
             "app_data": {
               "table_map": [
                 {
                   "id": "15b4b9a0-611a-4ac0-871f-cf9d8988fee1",
                   "source": {
                     "schema": "public",
                     "database": "optim_test",
                     "table_name": "products",
                     "source_table_id": "e30ed315-a011-4fd1-9712-a93ad792a7f9",
                     "connection_profile": "f4dc15d1-25ee-4a20-800c-caa0d439e451"
                   },
                   "target": {
                     "schema": "demo_target",
                     "database": "testdb",
                     "table_name": "products",
                     "connection_profile": "6a7b49eb-4de8-4390-9431-91bfe08fd0b6"
                   }
                 }
               ],
               "insert_parameters": {
                 "relational_integrity": {
                   "conflict_resolution": "merge"
                 }
               }
             }
           }
         ],
         "app_data": {
           "ui_data": {
             "comments": [],
             "worksheet_type": "accessDefinition"
           }
         },
         "runtime_ref": "optim-runtime:2.0.0"
       }
     ],
     "parameters": {},
     "json_schema": "https://api.dataplatform.ibm.com/schemas/common-pipeline/pipeline-flow/pipeline-flow-v3-schema.json",
     "primary_pipeline": "e7fe76ec-662e-44e5-b514-bee48382d13c"
   }
   
   

   Access definition node

   The following JSON structure defines an access definition node within a pipeline:

   
   {
     "app_data": {
       "name": "<name for access definition node>",
       "source_tables": [
         {
           "id": "<UUID for source table>",
           "database": "<databaseName>",
           "table_name": "<TableName>",
           "schema": "default",
           "connection_profile": "<connection_ref>",
           "columns": ["<TABLE-FIELD-NAME-1>", "<TABLE-FIELD-NAME-2>"]
         }
       ]
     },
     "id": "<UUID for access definition node>",
     "op": "access_definition",
     "type": "execution_node"
   }
   

   An access definition is created and saved, specifying the tables and relationships for data extraction. You can retrieve, update, or preview the definition by using the corresponding API endpoints.

2. To preview the access definition and verify relationships, send a GET request to https://VM_HOSTNAME:PORT/optim/v1/flow/preview/{flowId}

   
   curl -X GET https://<host>/optim/v1/flow/preview/{flowId} \
     -H "Authorization: Bearer <accessToken>" 
   

Excluding columns by using the API

By default, all columns are included. To include only specific columns, specify their names in the columns field of the access definition request body. If a column is excluded, no data is read from or written to it. As a result:

• For database targets, the underlying database populates the column with its default value (typically NULL).
• For file-based targets, the column is omitted from the generated files.

Excluded columns remain part of the schema by default, for all targets. To remove them entirely from the target schema, set the exclude_columns_on_create field to "true". This ensures the column is not created in the target schema.

Sample JSON request body:

{
    "id": "access-definition-node",
    "type": "execution_node",
    "op": "access_definition",
    "app_data": {
        "source_tables": [
            {
                "id": "emp-table-id-db2f",
                "database": "${dbName}",
                "table_name": "employee_data",
                "schema": "testschema_db2file_src",
                "connection_profile": "source-db-ref-002",
                "columns": ["emp_id", "first_name", "last_name"],
                "exclude_columns_on_create": "true"
            }
        ]
    }
    ...
}