IBM Db2

The IBM Db2 target writes data to an IBM Db2 database. For information about supported versions, see Supported systems and versions.

When you configure the IBM Db2 target, you specify the database name, host name, and port number. In addition, you can enable the target to use SSL. You can also use a connection to configure the target.

You specify tables to load the data into and the write mode the target uses to write data from record fields to table columns.

Before you use the IBM Db2 target, you must complete some prerequisite tasks.

Prerequisites

About this task

Before you configure the IBM Db2 target, complete the following prerequisite tasks.

Procedure

  1. Verify that the Db2Data Collector flight.enable property is set to auto or true.
  2. To connect to IBM Db2 z/OS, install and activate an appropriate license certificate:
    1. Install an IBM Db2 Connect Unlimited Edition license certificate. For more information, see the IBM Db2 documentation.
    2. Activate the license certificate. For more information, see the IBM Db2 documentation.

Specifying tables

You can use the IBM Db2 target to write to one or more tables.

Specify the tables to use based on how many tables you want to write to:
Single table
To write data to a single table, simply enter the table name and schema name for the table.
Multiple tables in a single schema
To write data to multiple tables in a single schema, specify a field or header in the record that defines the table names as follows.
To specify a field that defines the table names:
${record:value('/<field_name>')}
To specify an attribute that defines the table names:
${record:attribute('<attribute_name>')}
Multiple tables in multiple schemas
To write data to multiple tables in multiple schemas, specify fields or headers in the record that define the table and schema names as follows.
To specify a field that defines the table or schema names:
${record:value('/<field_name>')}
To specify an attribute that defines the table or schema names:
${record:attribute('<attribute_name>')}

Use the Table Name and Schema Name properties on the Table tab to specify the tables to write to.

Key columns

When using merge or update write mode, you specify which set of key columns to use to compare records with rows in the database.

Use the table primary keys
The stage uses the table primary keys to configure the operations. Use this option when primary key columns are defined for the table.
Use columns specified in the headers
The stage uses key columns stored in the indicated record header attribute for the first record in a batch. Key columns must be listed as a JSON-formatted list or a comma-separated list. If the record header attribute is empty, the stage uses the table's primary keys.
The default record header attribute name is jdbc.primaryKeySpecification.
Use specified columns for each table
Specify a set of key columns for each table. If the key columns property for a table is left empty, the stage uses the table's primary keys.

IBM Db2 data types

The IBM Db2 target converts Data Collector data types into IBM Db2 data types before loading data into the database.

The following table describes how Data Collector data types are converted to standard IBM Db2 data types:
Data Collector Data Type IBM Db2 Data Type
Boolean SmallInt
Byte SmallInt
Byte_Array Binary, Binary_View, Fixed_Size_Binary, or Large_Binary depending on the original data type stored in metadata
Char Vargraphic
Date Date
Datetime Timestamp
Decimal Decimal
Double Double
Float Real
Integer Integer
List List, Large_List, Fixed_Size_List, List_View, or Large_List_View depending on the original data type stored in metadata
Long BigInt
Map Map or Struct depending on the original data type stored in metadata
Short SmallInt
String Vargraphic
Time Time
Zoned_Datetime Timestamp

Troubleshooting IBM Connectivity Service

The target connects to the database through IBM Connectivity Service. IBM Connectivity Service and all stages that use the service are bundled into the IBM Connectivity Service stage library, streamsets-datacollector-ibm-connectivity-service-lib. By default, Data Collector runs IBM Connectivity Service when the IBM Connectivity Service stage library is installed.

If IBM Connectivity Service stops responding, Data Collector attempts to restart the service. If Data Collector is unable to restart IBM Connectivity Service, you can use the following API calls to view the IBM Connectivity Service state and restart IBM Connectivity Service:
Call CURL request
Get IBM Connectivity Service state

curl --location 'https://<engine url>/rest/v1/flight/state' --header 'Authorization: Bearer <IBM Cloud API key>

Restart IBM Connectivity Service

curl --location 'https://<engine url>//rest/v1/flight/restart' --header 'Authorization: Bearer <IBM Cloud API key>

Get IBM Connectivity Service logs

curl --location 'https://<engine url>//rest/v1/flight/state' --header 'Authorization: Bearer <IBM Cloud API key>

Note: You use an IBM Cloud API key to make these API calls. For information on managing IBM Cloud API keys, see the IBM Cloud documentation.

Configuring an IBM Db2 target

About this task

Configure an IBM Db2 target to write data to an IBM Db2 database.

Procedure

  1. In the Properties panel, on the General tab, configure the following properties:
    General Property Description
    Name Stage name.
    Description Optional description.
    Required Fields Fields that must include data for the record to be passed into the stage.
    Tip: You might include fields that the stage uses.

    Records that do not include all required fields are processed based on the error handling configured for the flow.
    Preconditions Conditions that must evaluate to TRUE to allow a record to enter the stage for processing. Click Add to create additional preconditions.

    Records that do not meet all preconditions are processed based on the error handling configured for the stage.
    On Record Error Error record handling for the stage:
    • Discard - Discards the record.
    • Send to Error - Sends the record to the flow for error handling.
    • Stop Flow - Stops the flow.
  2. On the DB2 tab, configure the following properties:
    Db2 Property Description
    Connection

    7.4 and later

    		 Connection that defines the information that is required to connect to an external system.

    To connect to an external system, you can select a connection that contains the details, or you can specify the details in local properties. When you select a connection, the flow canvas hides properties that are defined in the connection.

    Host Host name or IP address of the database.
    Port Database port number.
    Database Type Type of Db2 database to connect to.
    Database Database name.
    Use SSL Enables SSL/TLS.
    SSL Certificate SSL certificate of the host. Enter a credential function that returns the certificate or enter the contents of the certificate.
  3. On the Credentials tab, configure the following properties:
    Credentials Property Description
    User Name Database user name.
    Password Database password.
  4. On the Tables tab, configure the following properties:
    Tables Property Description
    Schema Name of the schema that contains the tables to write to. You can use an expression that evaluates to the field or header in the record containing the schema name.
    Table Name Names of the table to write to. You can us an expression that evaluates to the field or header in the record containing the table name.
    Auto Create Table If the specified table does not exist in the database, the target creates the table in the specified schema.
    Write Mode Mode for writing records to the target table:
    • Insert - Uses the primary keys to insert data into the table.
    • Merge - Records are matched against rows in the database using the specified key columns. If a record matches a record in the database, the data in the existing record is updated. If the record does not match a record in the database, the record is inserted into the table.
    • Update - Updates the table using the specified key columns.
    Key Columns Location Location of the key columns to use when using the merge or update write mode.
    Key Columns Record Header Attribute Record header attribute containing key column information. The key columns must be provided as a Json list or a comma-separated string.

    Available when Key Columns Location is set to Key Columns Indicated In The Record Headers.
    Key Columns List of key columns for each table. When working with a single table, the schema and table names can be left empty. If key columns are not specified for a table, the target uses primary keys for the table.

    Available when Key Columns Location is set to Use the specified Key Columns.
  5. On the Advanced tab, optionally configure advanced properties.

    The defaults for these properties should work in most cases:

    Advanced Property Description
    Ignore Extra Fields Ignores extra record fields not present in the target table. When this property is disabled, extra record fields are sent to error.
    Number of Threads Maximum number of connections that the target uses to write to the database. The default, 0, ensures that the target uses the same number of connections as threads used by the flow.
    Query Timeout Maximum number of seconds to wait for a query to finish.

    Default is 600.
    Connection Validation RPC Timeout Maximum number of seconds to wait for an IBM Connectivity Service connection or authentication.

    Default is 3 minutes.
    Generic RPC Timeout Maximum number of seconds to wait for all other IBM Connectivity Service actions.

    Default is 15 minutes.