IBM DB2 on iSeries/AS400
The IBM DB2 Connection is available for databases on iSeries/AS400, which is discussed on this IBM Knowledge Center page.
Prerequisites
- DB2 running on iSeries/AS400 mainframe (min version tested v7.4)
- Network connectivity
- Hostname or IP address
- Port Number (SSL or non-SSL)
- Database name
- Database access credentials
Connection Properties
The following table describes the fields available when creating a new IBM DB2 Connection. Create a connection using the information below and these step-by-step instructions.
Field | Required | Description |
---|---|---|
Access Type | Required | The type of connection for DB2 on AS400 is Read-Only. |
Host | Required | IP or the hostname of the DB2 server. |
Port | Optional | Host server port number. The default port number for DB2 on iSeries/AS400 is 8471 (non-SSL) or port 9471 (SSL). Reference: IBM Toolbox for Java. |
Database Name | Required | The database name you are connecting to. |
Custom Connection Properties | Optional | Properties specified when connecting to DB2. See Properties for DB2 on AS400 using JDBC below for Ascend specific formatting. |
Requires Credentials | Optional | Choose from existing credentials or create new credential for connecting to DB2 Database on AS400 if 'Requires Credentials' checkbox is selected. |
Custom Connection Properties
Ascend utilizes JDBC to connect to IBM DB2 on iSeries/AS400 which allows for many property specifications. The names of keys and values can be found in the IBM Toolbox for Java JDBC properties documentation.
Ascend specific formatting
To specify properties in Ascend, do not use quotations. Present each property as newline-delimited key=value pairs (see Figure 3 below for an example).
Credential Properties
The following table describes the fields available when creating a new IBM DB2 credential.
Field | Required | Description |
---|---|---|
Credential Name | Required | The name to identify this credential with. This credential will be available as a selection for future use. |
Credential Type | Required | This field will automatically populate with DB2 on AS400. |
User | Required | The DB2 username to connect with. |
Password | Required | The DB2 password to connect with. |
Read Connector Properties
The following table describes the fields available when creating a new IBM DB2 Read Connector.
Field | Required | Description |
---|---|---|
Name | Required | Provide a name for your connector. We recommend using lowercase with underscores in place of spaces. |
Description | Optional | Describes the connector. We recommend providing a description if you are ingesting information from the same source twice, for different reasons. |
Table Name | Required | Name of the table being ingested. This can either be supplied manually or selected in Step 2. |
Schema Name | Optional | If using a specific schema configuration, provide a name. |
Write Connector Properties
The following table describes the fields available when creating a new IBM DB2 Write Connector. Create a new Write Connector using the information below and these step-by-step instructions.
Field Name | Required | Description |
---|---|---|
Name | Required | Provide a name for your connector. We recommend using lowercase with underscores in place of spaces. |
Description | Optional | Describes the connector. We recommend providing a description if you are ingesting information from the same source multiple times for different reasons. |
Upstream | Required | The name of the previous connector the Write Connector will pull data from. |
Table Name | Required | The name of the table that will be written to the DB2 database. |
Write Strategy | Required | The strategy to copy data into the DB2 database. The Full Load strategy will replace the entire table's contents each time that the upstream transform has changed. If the upstream data set contains multiple Ascend partitions and not all partitions are expected to change on each update, then the Incremental By Partition strategy allows for Ascend to only update/insert/delete the partitions of the Amazon Redshift table that have changed in Ascend. |
Max Number of Parallel Ascend Partitions | Optional | Ascend can write partitions in parallel. Ex.: If a transform has 2000 partitions and you set the max parallel to 100, Ascend will write partitions 100 at a time. |
A SQL Statement for Ascend to Execute Before Writing | Optional | Executes a pre-processing script before writing to final table. |
A SQL Statement for Ascend to Execute After Writing | Optional | Executes a pre-processing script after writing to final table. |
Schema Name | Optional | The name of the schema to match. |
On Schema Mismatch | Optional | Options are as follows: - Skip schema check - Stop and display error - Recreate table - Alter table |
Column Type Overrides | Optional | Specify data types overrides (e.g: "name CHAR(64), comments VARCHAR(1024)") where the column name is followed by the data type and each pair is separated by a comma. |
Updated 7 months ago