Onboarding Data from Db2 for AS/400
The DB2 for AS/400 is a relational database management system that runs on AS/400 system (i series of IBM server). The DB2 is the software used to manage relational databases and AS/400 is an integrated system featuring hardware (AS/400) and an operating system (OS/400). |
Prerequisite
Ensure that you manually add the db2jcc_license_ cisuz.jar file to the following folder:
$IW_HOME/lib/ingestion/connectors /db2_as400/lib/dist-jobs
To obtain this jar file, contact the database admin or IBM. For more details, see IBM Support Documentation.
Creating a Db2 for AS/400 Source
For onboarding data from a Db2 for AS/400 source, see Onboarding an RDBMS Source a rdbms. Ensure that the Source Type selected is Db2 for AS/400.
| Field | Description |
|---|---|
| Source Name | The source name of the target table. |
| Fetch Data Using | The mechanism through which Infoworks fetches data from the database. |
| Connection URL | The connection URL through which Infoworks connects to the database. The URL must be in the following format: jdbc:as400://<hostip>:<host port>/<database_name> |
| Username | The username for the connection to the database. |
| Authentication Type for Password | Select the authentication type from the dropdown. For example, Infoworks Managed or External Secret Store. If you select Infoworks Managed, then provide Authentication Password for Password. If you select External Secret Store, then select the Secret which contains the password. |
| Source Schema | The schema in the database to be crawled. The schema value is case sensitive. |
Once the settings are saved, you can test the connection.
Configuring a Db2 for AS/400 Table
With the source metadata in the catalog, you can now configure the table for CDC and incremental synchronization.
- Click the Configuration link, for the desired table.
- Provide the ingestion configuration details.
| Field | Description |
|---|---|
| Query | The custom query based on which the table has been created. |
| Ingest Type | The type of synchronization for the table. The options include full refresh and incremental. |
| Natural Keys | The combination of keys to uniquely identify the row. This field is mandatory in incremental ingestion tables. It helps in identifying and merging incremental data with the already existing data on target. |
| Incremental Mode | The option to indicate if the incremental data must be appended or merged to the base table. This field is displayed only for incremental ingestion. The options include append and merge. |
| Watermark Column | Select single/multiple watermark columns to identify the incremental records. The selected watermark column(s) should be of the same datatype. |
| Enable Watermark Offset | For Timestamp and Date watermark columns, this option enables an additional offset (decrement) to the starting point for ingested data. Records created or modified within the offset time period are included in the next incremental ingestion job. |
| Ingest subset of data | The option to configure filter conditions to ingest a subset of data. This option is available for all the RDBMS and Generic JDBC sources. For more details, see Filter Query for RDBMS Sources. |
Target Configuration
Configure the following fields:
| Field | Description |
|---|---|
| Target Table Name | The name of the target table. |
| Schema Name | The schema name of the target table. |
| Storage Format | The format in which the tables must be stored. The options include Read Optimized (Parquet), Read Optimized (ORC), Write Optimized (Avro). |
| Partition Column | The column used to partition the data in target. Selecting the Create Derived Column option allows you to derive a column and then use that as the partition column. This option is enabled only if the partition column datatype is date or timestamp. Provide the Derived Column Function and Derived Column Name. Data will be partitioned based on this derived column. |
Optimization Configuration
Configure the following fields:
| Field | Derscription |
|---|---|
| Split By Column | Allows to crawl the data in parallel with the multiple connections to the database. This column denotes the existing column in the database and the column can be any value for which maximum value and minimum value can be computed. |
| Generate History View | This option allows storing the data in the history table. After each CDC, the data will be appended to the history table. |
Advanced Configuration
Following are the steps to set advanced configuration for a table:
Add Configuration
- Click the Data Catalog menu and click Ingest for the required source.
And then click the Advanced Configuration tab.
- Click the Configure Tables tab, click the Advanced Configuration tab and click Add Configuration.
- Enter key, value, and description. You can also select the configuration from the list displayed.
Add Subscribers
You can add subscribers to be notified for the ingestion at the table level.
- Click the Data Catalog menu and click Ingest for the required source.
And then click the Advanced Configuration tab.
- Click the Configure Tables tab, click the Advanced Configuration tab and click Add Subscribers.
- Enter the following fields:
- E-mails: The e-mail ID of the subscriber.
- Notify via e-mail or Slack: The means of notification.
- Notify for: The jobs for which subscribers must be notified.
Sync Data to Target
Using this option, you can configure the Target connections and sync data as described in the section Synchronizing Data to External Target.
The following are the steps to sync data to target.
- From the Data Sources menu, select one of the tables and click View Source/Ingest button.
- Select the source table to be synchronized to Target.
- Click the Sync Data to Target button.
- Enter the mandatory fields as listed in the table below:
| Field | Description |
|---|---|
| Job Name | The name of the ingestion job. |
| Max Parallel Tables | The maximum number of tables that can be crawled at a given instance. |
| Compute Cluster | The template based on which the cluster will spin up for each table.The compute clusters created by admin and are accessible by the user are listed in the drop-down. |
| Overwrite Worker Count | The option to override the maximum and minimum number of worker node values as configured in the compute template. |
| Number of Worker Nodes | The number of worker nodes that will spin up in the cluster. |
| Save as a Table Group | The option to save the list of tables as a table group. |
- Click Sync Data to Target to sync the data to the Target table.
- Click Onboarding an RDBMS Source to navigate back to complete the onboarding process.