DB2 AS400

Connectivity Summary

An out-of-the-box connector is available for DB2 AS400 databases to support crawling database objects and profiling sample data.

The drivers used by the connector are given below:

Driver/API: JDBC driver
Version: 10.5
Details: https://ovaledge-jars.s3.amazonaws.com/third_party_jars/jt400-jdk8-10.5.jar
Note: Latest version is 10.6

Connector Capabilities

The following are the connector capabilities mentioned below:

Crawling

Supported objects for Crawling are given below:

Feature Supported Objects Remarks
Crawling Tables  
Table Columns

Supported Data Types: Varchar, longvarchar, char, bigint, integer, decimal, smallint, float, date, time, timestamp.

Views  
Stored Procedures  
Functions  
Triggers  
Relationships  

Please see this article Crawling Data for more details on crawling. 

Profiling

For more details on profiling, please refer to Profiling Data 

Feature Support Remarks
Table Profiling Row count, Columns count, View sample data  
View Profiling Row count, Columns count, View sample data View is treated as a table for profiling purposes
Column Profiling Min, Max, Null count, distinct, top 50 values  
Full Profiling Supported  
Sample Profiling Supported  

Querying

Operation Details
Select Supported
Insert Supported
Update Supported
Delete Supported
Joins within database Supported
Joins outside database Supported
Aggregations Supported
Group By Supported
Order By Supported

By default, the service account provided for the connector will be used for any query operations.

Pre-requisites

To use the connector, the following need to be available:

  1. Connection details as specified in the following section should be available.
  2. A User account with reading privileges.
  3. Add DB2AS400 drivers into the OvalEdge Jar path to communicate with the DB2AS400 database.
Check the Configuration section for further details on how to add the drivers to the jar path.

Connection Details

The following are the connection settings that should be added for connecting to a DB2AS400 database:

  • Database Type: DB2AS400
  • Connection Name: Select a Connection name for the DB2AS400 database. The name that you specify is a reference name to easily identify your DB2AS400 database connection in OvalEdge.
    Example: DB2AS400 Connection 1
  • Hostname/IP Address: Database instance URL (on-premises/cloud-based)
    Example: 3.138.187.1
  • Port Number: 50005
  • Sid/Database: Name of the database to connect.
  • Username: User account login credential
  • Password: Password
  • Driver Name: DBC driver name for DB2AS400. It will be auto-populated.
    Example: com.ibm.as400.access.AS400JDBCDriver
  • Connection String: DB2 connection string. Set the Connection string toggle button to automatic, to get the details automatically from the credentials provided. Alternatively, you can manually enter the string.
    Format: jdbc:db2://{server}:50005/{sid}
    Example: jdbc:db2://3.138.187.2:50005/test
  • License Type: Standard.
  • Plugin Server/Port: NA

Once connectivity is established, additional configurations for Crawling and Profiling can be specified.

33

Profile Settings

  • Order: Priority of the rule
  • Start time and End time: Used when crawling/profiling is to be scheduled
  • No. of threads: No. of threads used to perform profiling
  • Profile Type: Disabled/Auto/Sample/Query
  • Row count Constraint: No. of rows to be fetched
  • Sample profile size: A sample profile row limit
  • Sample data count: A sample amount of data to profile
  • Query Timeout: Time to wait for a response
  • Crawler options: Procedures, Functions, Triggers & Views Source Code/Tables, Views & Columns/Relationship.
  • Profile options: Tables and Columns/Views and Columns
  • Crawler Rules: Include and Exclude Regex for table/column/procedures
  • Profile Rules: Include and Exclude Regex for table/column

Points to note

Port number can vary, default port number is 50005.

FAQs

  1. How much does the driver cost?
    The Microsoft JDBC Driver for DB2AS400  is available at no additional charge.