IBM Db2

The IBM Db2 destination writes data to an IBM Db2 database. For information about supported versions, see Supported Systems and Versions in the Data Collector documentation.

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

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

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

Prerequisites

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

For a self-managed tarball installation of Data Collector, configure Data Collector to find the IBM Connectivity Service engine by running the following command on the Data Collector machine:
```
sudo ln -s /<installation directory>/flightservice/opt/ibm /opt/ibm;
```
For a cloud service provider installation of Data Collector, configure Data Collector to find the IBM Connectivity Service engine. Use SSH to connect to the cloud service provider virtual machine where Data Collector is installed. Then run the following command:
```
sudo ln -s /<installation directory>/flightservice/opt/ibm /opt/ibm;
```
Verify that the Data Collector flight.enable property is set to auto or true.
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 destination 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 destination 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 destination 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 -X GET <HOST PREFIX>/tunneling/rest/<ENGINE ID>/rest/v1/flight/state -H "Content-Type:application/json" -H "X-Requested-By:curl" -H "X-SS-REST-CALL:true" -H "X-SS-App-Component-Id: <CRED_ID>" -H "X-SS-App-Auth-Token: <CRED_TOKEN>." -i
Restart IBM Connectivity Service	curl -X GET <HOST PREFIX>/tunneling/rest/<ENGINE ID>/rest/v1/flight/restart -H "Content-Type:application/json" -H "X-Requested-By:curl" -H "X-SS-REST-CALL:true" -H "X-SS-App-Component-Id: <CRED_ID>" -H "X-SS-App-Auth-Token: <CRED_TOKEN>" -i
Get IBM Connectivity Service logs	curl -X GET <HOST PREFIX>/tunneling/rest/<ENGINE ID>/rest/v1/flight/logs -H "Content-Type:application/json" -H "X-Requested-By:curl" -H "X-SS-REST-CALL:true" -H "X-SS-App-Component-Id: <CRED_ID>" -H "X-SS-App-Auth-Token: <CRED_TOKEN>" -i

Note: You use Control Hub API credentials to make these API calls. For information on creating and using API credentials, see the Control Hub documentation.

Configuring an IBM Db2 destination

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

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 pipeline.
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 pipeline for error handling. Stop Pipeline - Stops the pipeline.

On the DB2 tab, configure the following properties:

Db2 Property	Description
Connection	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 directly enter the details in the pipeline. When you select a connection, Control Hub hides other properties so that you cannot directly enter connection details in the pipeline.
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.

On the Credentials tab, configure the following properties:

Credentials Property Description

User Name Database user name.

Password Database password.

Credentials Property	Description
User Name	Database user name.
Password	Database password.

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 destination 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 destination uses primary keys for the table. Available when Key Columns Location is set to Use the specified Key Columns.

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 destination table. When this property is disabled, extra record fields are sent to error.
Number of Threads	Maximum number of connections that the destination uses to write to the database. The default, 0, ensures that the destination uses the same number of connections as threads used by the pipeline.
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.