IBM Db2 SQL Database (for Windows agent)

Version

3.0
Agent restriction

Remote Only
Processing type

Bulk & Row by row
Multi-input step

Not Supported

This connector allows you to remotely execute SQL queries and stored procedures in an IBM Db2 database. It is designed for use with Windows-based remote agents and is not supported on Linux. The connector is ideal for integrating IBM Db2 into automated workflows, enabling you to read, write, and manipulate data directly through SQL commands. Typical use cases include data synchronization, reporting automation, and integration with other business systems.

Prerequisites

The connector requires that the user account used to connect to the IBM Db2 database has permission to create temporary tables.

If the database does not yet have a temporary tablespace configured for user-defined temporary tables, you need to create it and grant access. You can do this using the following SQL statements:

CREATE USER TEMPORARY TABLESPACE tempspace01 MANAGED BY AUTOMATIC STORAGE;
GRANT USE OF TABLESPACE tempspace01 TO PUBLIC;

This ensures that the connected user (or all users in the database) have the necessary space allocated for temporary tables used during connector execution.

Using placeholders

Assume the connector input data in the following format, followed by placeholders, which can be used for direct reference to the input data table:

Row number	ID	Login
`0`	1 `${input[0].ID}`	Alice `${input[0].Login}`
`1`	2 `${input[1].ID}`	Bob `${input[1].Login}`

Returning all input data

select * from #InputData;

Returns the contents of the entire temporary table filled with input data:

ID	Login
1	Alice
2	Bob

Referring to a specific value from the input data

Selected columns must be always wrapped with double quotes and must respect case sensitive names.

select 
    ${input[0].ID} as ID, 
    "Login" as Login
from #InputData

Returns the list as in the previous example, except that the value from the first row and column of the ID input table is always used as ID (${input[0].ID} = 1):

ID	Login
1	Alice
1	Bob

Predefined variables

Variable	Data type	Description
`#InputData`	temporary table	Temporary table filled with input data. This table will be deleted when the connector is finished running.
`@TaskRunID`	text	ID of the currently executing `TaskRun`. Data type should be fixed in future.
`@EndpointTokenID`	integer	ID of the currently used `EndpointToken`.
`@EndpointTokenName`	text	Name of the currently used `EndpointToken`.
`@DataCheckpoint`	text	Datacheckpoint value.

Returning output data

If the output scheme is selected, the result of the last select statement, which is not assigned to any variable, is used as the step output. The structure of the query result must match the structure of the output schema.

Selected columns must be always wrapped with double quotes and must respect case sensitive names.

If there are some complex input schemas returned to output, the result will contain column data serialized as JSON string.

Example

-- NOT the last select in the statement, data will NOT be sent to the output.
select 0 as ID, Login from #InputData;

-- IS the last valid select in the statement, data WILL be sent to the output.
select 1 as ID, Login from #InputData;

-- Is the last select, but its result is assigned to a variable. Therefore, the data will NOT be sent to the output.
declare maxUserID int = (select max(ID) from DXTask);

-- Using variable as parameter
select ID as TaskRunID, @DataCheckpoint as DataCheckpoint from InputData where ID != @TaskRunID
select @TaskRunID as TaskRunID

Logging

Direct logging from SQL connector into task run log is not supported.

Configuration

IBM Db2 Connection String (ver. 1.0.0)

IBM Db2 Connection string

Specifies the full connection string used to connect to your IBM Db2 database. Make sure the string includes all required parameters such as host, port, database name, user credentials, and any additional options specific to your environment.

IBM Db2S Statement (ver. 1.0.0)

IBM Db2 Statement

Defines the SQL statement or stored procedure to be executed on the IBM Db2 database.
You can use any valid SQL syntax supported by your target Db2 instance.

Placeholder	Type	Description
`#InputData`	temporary table	Temporary table filled with input data. This table will be deleted after the connector run is over.
`${input[<row number>].<column name>}`	value	Takes the value from `<row number>`^th row (zero-based index) and column named `<column name>`.
`@TaskRunID`	integer	ID of the currently executing `TaskRun`.
`@EndpointTokenID`	integer	ID of the currently used `EndpointToken`.
`@EndpointTokenName`	text	Name of the currently used `EndpointToken`.
`@DataCheckpoint`	text	Datacheckpoint value.

Data checkpoint column

The data checkpoint column is a column (field), from which the platform takes the last row value after each executed task run and stores it as a Data checkpoint. The data checkpoint value can be used in the SQL statements to control, which data should be processed in the next run. You can refer to the value using the predefined variable @DataCheckpoint. Example of use: processing data in cycles, where every cycle processes only a subset of the entire set due to the total size. If you use e.g. record ID as a data checkpoint column, the platform will store after each cycle the last processed ID from the data subset processed by the task run. If your statement is written in a way that will evaluate the value in data checkpoint against the IDs of the records in the data set, you can ensure this way, that only not processed records will be considered in the next task run.

Input & Output Schema

Input

Data schema is optional

The connector does not expect a specific schema. The required data structure can be achieved by correct configuration. Although the selected connector doesn't require a schema generally, the individual integration task step may need to match the output data structure of the preceding task step and use a data schema selected from the repository or create a new input schema.

Output