DB2¶

DB2 data sources and sinks are in the database category of I/O connectors.

Tool documentation¶

IBM DB2 documentation

Connector type values¶

The "type": value to use in the source or sink JSON files.

Connector type	Value
Data source	DKDataSource_DB2SQL
Data sink	DKDataSink_DB2SQL

Connection properties¶

The properties to use when connecting to a DB2 instance from Automation.

Field	Scope	Type	Required?	Description
`hostname`	source/sink	string	yes	DB2 SQL host name.
`port`	source/sink	string/number	yes	DB2 SQL port.
`database`	source/sink	string	yes	DB2 SQL database name.
`username`	source/sink	string	yes	DB2 SQL username.
`password`	source/sink	string	yes	DB2 SQL password.
`ssl_file`	source/sink	string	no	A variable or vault entry to the SSL certification file contents. A value in this field will add the required SSL flags to the DB2 connection string, as `SECURITY=SSL;SSLServerCertificate=<key.arm>;`

Connections¶

See Connection Properties for details on connection configurations

Defined in kitchen-level variables¶

db2config in kitchen overrides

{
    "db2config": {
        "username": "#{vault://db2/username}",
        "password": "#{vault://db2/password}",
        "hostname": "#{vault://db2/hostname}",
        "database": "#{vault://db2/database}",
        "port": "#{vault://db2/port}",
        "ssl_file": "#{vault://db2/ssl_file}"
    }
}

The Connection tab in a Node Editor¶

(Screenshot of the Connector tab)

Expanded connection syntax¶

For a data source¶

db2_datasource.json

{
    "type": "DKDataSource_DB2",
    "name": "db2_datasource",
    "config": {
        "username": "{{db2config.username}}",
        "password": "{{db2config.password}}",
        "hostname": "{{db2config.hostname}}",
        "database": "{{db2config.database}}",
        "port": "{{db2config.port}}"
    },
    "keys": {},
    "tests": {}
}

For a data sink¶

db2_datasink.json

{
    "type": "DKDataSink_DB2",
    "name": "db2_datasink",
    "config": {
        "username": "{{db2config.username}}",
        "password": "{{db2config.password}}",
        "hostname": "{{db2config.hostname}}",
        "database": "{{db2config.database}}",
        "port": "{{db2config.port}}"
    },
    "keys": {},
    "tests": {}
}

Condensed connection syntax¶

Note

Note: Do not use quotes for your condensed connection configuration variables.

For a data source¶

db2_datasource.json

{
    "type": "DKDataSource_DB2",
    "name": "db2_datasource",
    "config-ref": "db2config",
    "keys": {},
    "tests": {}
}

For a data sink¶

db2_datasink.json

{
    "type": "DKDataSource_DB2",
    "name": "db2_datasink",
    "config-ref": "db2config",
    "keys": {},
    "tests": {}
}

Local connections¶

DB2 SQL database contents may be viewed locally by configuring connections with IDEs like PyCharm or database query tools like DBeaver or MySQL Workbench.

Tip

Connecting PyCharm or DataGrip to DB2: See the Database connection topic in PyCharm help for detailed information.

Other configuration properties¶

See the following topics for common properties and runtime variables:

File encoding requirements¶

Files used with data sources and data sinks must be encoded in UTF-8 in order to avoid non-Unicode characters causing problems with sinking data to database tables and errors when running related tests

For CSV and other delimited files, use Save as in the program and select the proper encoding, or consider using a text editor with encoding options.

Data source example¶

db2_datasource.json

{
    "type": "DKDataSource_DB2SQL",
    "name": "db2_datasource",
    "config": {{db2config}},
    "keys": {},
    "tests": {}
}

Data sink example¶

db2_row_count.sql

{
    "type": "DKDataSink_DB2SQL",
    "name": "db2_datasink",
    "config": {{db2config}},
    "keys": {},
    "tests": {}
}