IBM Db2

The IBM Db2 origin reads data from an IBM Db2 database. For information about supported versions, see Supported Systems and Versions in the Data Collector documentation.

The IBM Db2 origin can use multiple threads to process tables in parallel.

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

The origin generates record header attributes that enable you to use the origins of a record in pipeline processing.

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

Prerequisites

Before you configure the IBM Db2 origin, 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.

Multithreaded processing

The IBM Db2 origin uses multiple concurrent threads to process data based on the Number of Threads property. Each table is processed by only one thread a time, which ensures that the order of batches and records from each table is preserved.

As the pipeline runs, each thread connects to the origin system, creates a batch of data, and passes the batch to an available pipeline runner. A pipeline runner is a sourceless pipeline instance - an instance of the pipeline that includes all of the processors, executors, and destinations in the pipeline and handles all pipeline processing after the origin.

Each pipeline runner processes one batch at a time, just like a pipeline that runs on a single thread. When the flow of data slows, the pipeline runners wait idly until they are needed, generating an empty batch at regular intervals. You can configure the Runner Idle Time pipeline property to specify the interval or to opt out of empty batch generation.

Multithreaded pipelines preserve the order of records within each batch, just like a single-threaded pipeline. But since batches are processed by different pipeline runners, the order that batches are written to destinations is not ensured.

For more information about multithreaded pipelines, see Multithreaded pipeline overview.

Record header attributes

The IBM Db2 origin creates record header attributes that include information about the originating file for the record.

You can use the record:attribute or record:attributeOrDefault functions to access the information in the attributes. For more information about working with record header attributes, see Working with header attributes.

The IBM Db2 origin creates the following record header attributes:

schemaName - Name of the schema containing the table the record comes from.
tableName - Name of the table the record comes from.

Field attributes

The IBM Db2 origin generates field attributes for columns converted certain data types in Data Collector. The attributes provide additional information about each field.

The origin generates the following field attributes data types:

Data Collector Data Type	Generated Field Attribute	Description
Date	unit	Original unit in which the date was obtained from IBM Db2.
Datetime	unit	Original unit in which the date was obtained from IBM Db2.
Decimal	precision	Precision of the value obtained from IBM Db2.
Decimal	bit_width	Original bit width in which the value was obtained from IBM Db2.
Decimal	scale	Scale of the value obtained from IBM Db2.
Decimal and String	flight_type	Original flight type.
Double and Float	precision	Original precision obtained from IBM Db2.
Integer, Long, and Short	bit_width	Original bit width in which the value was obtained from IBM Db2.
Integer, Long, and Short	signed	Whether the original value obtained from IBM Db2 was signed.
Time	unit	Original unit in which the date was obtained from IBM Db2.

IBM Db2 data types

The IBM Db2 origin converts IBM Db2 data types into Data Collector data types before passing them to the pipeline.

The following table describes how IBM Db2 data types are converted to Data Collector data types:

IBM Db2 Data Type	Data Collector Data Type
BigInt	Long
Binary	Byte Array
Binary_View	Byte Array
Boolean	Boolean
Char	String
Clob	String
Date	Date
Dbclob	String
DecFloat	String
Decimal	Decimal
Double	Double
Duration	Long
Fixed_Size_Binary	Byte Array
Fixed_Size_List	List
Graphic	String
Integer	Integer
Large_Binary	Byte Array
Large_List	List
Large_List_View	List
List	List
List_View	List
Nchar	String
Numeric	Decimal
Nvarchar	String
Real	Float
SmallInt	Short
Struct	Map
Time	Time
Timestamp	Datetime
Varchar	String
Vargraphic	String
XML	String

Key column data types

The origin supports the following Data Collector data types as key columns:

Boolean
Char
Date
Datetime
Decimal
Double
Float
Integer
Long
Short
String
Time
Zoned Datetime

Troubleshooting IBM Connectivity Service

The origin 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 origin

Configure an IBM Db2 origin to read data from 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.
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 table to read. Using simple or bulk edit mode, click Add to add additional schemas.
Table Name	Name of the table to read. Using simple or bulk edit mode, click Add to add additional tables.
Key Columns	Ordered list of key columns to use as offset columns of the select query. As a best practice, a key column should be an incremental and unique column that does not contain null values. If the column is not unique, meaning multiple rows can have the same value for this column, there is potential for data loss when the pipeline restarts.
Maximum Batch Size	Maximum number of records processed at one time. Honors values up to the Data Collector maximum batch size.
Number of Threads	Number of threads the origin generates and uses for multithreaded processing.

On the Advanced tab, optionally configure advanced properties.

The defaults for these properties should work in most cases:

Advanced Property	Description
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.