Integrate data from IBM Db2 to Databricks using Matillion

Creating a User in IBM Db2

IBM Db2 (LUW) primarily relies on the operating system or a central directory (such as LDAP) for user authentication. In most typical Db2 installations, you do not create users directly inside Db2 using SQL statements. Instead, user accounts are created at the OS or LDAP level, and then those users are granted database privileges.

Below are the steps for creating a new user on the operating system and then granting that user appropriate privileges within a Db2 database.

1. Create an Operating System User

On the server hosting your Db2 database, create a new operating system user (e.g.,

db2user1

bash
sudo useradd db2user1
sudo passwd db2user1

On Windows, you can use the Computer Management console or:

powershell
net user db2user1 <password> /add

2. Grant Database Privileges to the User

After creating the user at the OS level, connect to the Db2 database as a user with suitable privileges and grant the necessary permissions.

Example: Granting CONNECT privilege

``` -- Connect to the database as an administrator CONNECT TO sample USER db2admin USING your_password;

-- Grant CONNECT privilege to the new user GRANT CONNECT ON DATABASE TO USER db2user1;

-- Optional: Grant additional object privileges (example: on a schema) GRANT SELECT, INSERT, UPDATE ON TABLE myschema.mytable TO USER db2user1; ```

Notes

• Db2 will authenticate

  db2user1

  using the underlying OS or LDAP.
• Usernames are case-sensitive and must match the OS/LDAP account credentials.
• The new user can now connect to the database and exercise granted permissions.

3. (Optional) Using Roles for Group Permissions

You can also create a ROLE in Db2 and assign privileges to that role. Then, grant the role to one or multiple users for easier management:

CREATE ROLE app_users;
GRANT SELECT, INSERT, UPDATE ON TABLE myschema.mytable TO ROLE app_users;
GRANT ROLE app_users TO USER db2user1;

Note: If your environment uses LDAP authentication, consult your system administrator for user creation and management. Also, in IBM Db2 for z/OS, user authentication and authorization principles may differ significantly.

Checking the JDBC driver

The JDBC driver for IBM Db2 is already installed in Matillion Data Productivity Cloud—no additional download or installation steps are required. To confirm the presence of the driver, you can follow these steps:

1. Create a new Orchestration pipeline in Matillion Data Productivity Cloud.
2. In your pipeline, add a "Database Query" component from the available components.
3. In the properties for the "Database Query" component, locate the Database Type dropdown list.
4. Look for IBM Db2 in the list of available database types. If it appears, the JDBC driver is present and ready to use.

If you would like to check the exact version of the JDBC driver currently installed, refer to the instructions provided at:
https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/#database-driver-versions

Checking network connectivity

To enable your Matillion Data Productivity Cloud environment to connect to an IBM Db2 database, you must ensure that your database allows incoming network connections from the appropriate sources, depending on the configuration and deployment model you are using:

• Full SaaS agent configuration:
  You must permit incoming connections to your IBM Db2 database from the specific public IP addresses used by Matillion infrastructure. The complete and up-to-date list of IP addresses is available at:
  https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/

• Hybrid SaaS deployment:
  In a Hybrid SaaS deployment, you should allow incoming connections from your organization’s own Virtual Private Cloud (VPC). This allows the Hybrid SaaS agent, running within your network, to connect securely to the IBM Db2 database. For tools to check connectivity and network reachability from your VPC, refer to:
  https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/

Additionally, if your IBM Db2 database is referenced using a DNS hostname instead of a direct IP address, ensure that the Matillion Full SaaS or Hybrid SaaS agent—depending on your deployment—can correctly resolve the hostname to the appropriate database server address. Network connectivity issues may occur if DNS resolution is not possible from the relevant environment.

Querying Data from an IBM Db2 Database

This document outlines how to query data from an IBM Db2 database using SQL, considerations for datatype conversion between Db2 and Databricks, and strategies for performing initial and incremental loads using the same Database Query component.

Example SQL SELECT Statements for IBM Db2

Below are common examples of SELECT queries for IBM Db2:

``` -- Select all columns from a table SELECT * FROM EMPLOYEE;

-- Select specific columns SELECT EMPNO, FIRSTNME, LASTNAME, SALARY FROM EMPLOYEE;

-- Using WHERE clause to filter rows SELECT EMPNO, FIRSTNME, LASTNAME FROM EMPLOYEE WHERE DEPTNO = 'D11';

-- Ordering query results SELECT EMPNO, FIRSTNME, LASTNAME FROM EMPLOYEE ORDER BY SALARY DESC;

-- Aggregation with GROUP BY SELECT DEPTNO, AVG(SALARY) AS AVG_SALARY FROM EMPLOYEE GROUP BY DEPTNO; ```

Datatype Conversion: IBM Db2 and Databricks

When transferring data from IBM Db2 to Databricks, implicit or explicit datatype conversion may be required to match Db2 types with the supported Databricks types. Common considerations:

Example SQL for explicit casting in Db2:

SELECT EMPNO, CAST(SALARY AS DECIMAL(15,2)) AS SALARY
FROM EMPLOYEE;

Adjust types as necessary before data transfer to prevent compatibility issues.

Initial vs. Incremental Load Pattern

The recommended data loading pattern consists of an initial full data load followed by periodic incremental loads. Use the same Database Query component for both strategies.

Initial Load

• Approach: Load the entire dataset with no

  WHERE

  clause.
• Query Example:

SELECT *
    FROM EMPLOYEE;

Incremental Loads

• Approach: Load only new or updated records based on a deterministic filter (e.g., by using a timestamp or an incrementing ID).
• Query Example (using a timestamp column):

SELECT *
    FROM EMPLOYEE
    WHERE LAST_UPDATED > ?;

Replace

?

• Further reading and best patterns:
  Incremental Load / Data Replication Strategy (Matillion Exchange)

Note: Always validate filters and data types to ensure reliable and efficient data replication between IBM Db2 and Databricks.

Data Integration Architecture

Loading data in advance of integration is a key strategy used to divide and conquer the challenges inherent in modern data workflows, as it allows the data integration process to be separated into two distinct steps: loading and transformation. This separation is a core advantage of the Extract, Load, Transform (ELT) architecture. In the ELT model, raw data from various sources is first loaded into the Databricks environment where it is then transformed and integrated as needed. Data integration often requires complex data transformation tasks, and the most effective approach to manage these tasks is by leveraging data transformation pipelines. These pipelines allow orchestration, monitoring, and reusability of transformation logic, making the integration process repeatable and reliable. Another significant benefit of the ELT approach is that all transformations and integration activities happen directly inside the Databricks database. This ensures that data processing is fast, on-demand, and scalable, without the need to invest in and maintain separate, dedicated data processing infrastructure.