Step-by-Step guidance to ingest Db2 data into Fabric Native SQL Database using Microsoft Fabric Data pipelines
Introduction
Mainframes are purpose-built systems with specialized hardware and software designed for high-throughput, reliability, and scalability. They excel at handling large, transaction-heavy workloads. On the other hand, Azure Cloud offers flexibility, scalability, resource sharing and operate on commodity hardware and virtualized infrastructure. Azure provides best of Compute, Storage, Networking, Security services at reasonable price.
Migrating from a Db2 z/OS database to Fabric Native SQL Database provides numerous benefits for organizations aiming to modernize their data infrastructure and improve operational efficiency. The Fabric Native SQL Database offers the following advantages:
- User-Friendly: It is easy to configure and manage.
- High Performance: Handles large data and queries efficiently.
- Secured: It provides robust security features which ensure data protection.
- Seamless Integration: It integrates seamlessly with other Microsoft Fabric services.
- Autonomous Features: Automatic scaling and performance tuning.
- Dev Productivity: Integrated with Development frameworks and Analytics which enhances Development productivity.
If you need an easy-to-manage operational database platform that can easily integrate with cross-platform queries against real-time intelligence data, parquet files, and master data managed in a warehouse then Azure Fabric Native Database should be your preferred option.
-
If you want to know more about in what scenario to use Azure SQL DB and when to use Azure Fabric Native Database you can refer the documentation at link.
- How to create SQL Database in Fabric is mentioned at location: link
In this technical blog, we have explained how to copy Db2 database table data from Mainframe to Fabric Native SQL Database using Microsoft Fabric Data pipelines.
Though this blog focusses on Db2 z/OS as the source, the same principles and guidance will apply for other databases like Db2 LUW, Db2 iSeries (AS400), Oracle, etc.
Migrate Mainframe Db2 data to Fabric Native SQL Database
High level diagram which shows different components involved and data flow from Db2 to Fabric Native SQL Database using Fabric Data Pipelines is shown below:
This technical blog is divided into 4 sections:
A) Installation of on-premises data gateway
B) Creation of Fabric Data Pipeline
C) Use of Fabric Data Pipeline Copy data activity to perform data copy from Db2 to Fabric Native SQL Database and
D) Execution of Copy data process
A. Install an on-premises data gateway
An on-premises data gateway (OPDG) is software that you install on a Windows machine in on-premises network or in Azure VM. OPDG facilitates access to data in on-premises environment for copying to Azure cloud. You can install a gateway either in personal mode, which applies to Power BI only, or in standard mode. Difference between personal and standard mode OPDG installation is mentioned at link. For this data copy scenario, we recommend installing the OPDG in standard mode.
Complete steps to download and install on-premises data gateway is documented at location link.
After gateway software is downloaded and installed, configure gateway by providing your Microsoft Entra ID.
Sign in and Register the newly installed gateway software.
Configure the gateway restore key.
After successful registration status will be shown as Ready for Microsoft Fabric.
B. Create Fabric Data Pipeline
Login to fabric.microsoft.com. Select Fabric experience from the bottom left of the screen, then click on Your fabric workspace and then click on New item as shown below:
Click on Data pipeline to create new pipeline.
Provide new pipeline name.
C. Use Copy data activity to copy data from Db2 z/OS to Fabric Native SQL DB
Click on Pipeline activity and then select Copy data activity.
Provide meaningful name to Copy activity.
On Source tab click on Connection drop down and then on More option to create new connection to Source Db2 database.
Search for Db2 as Source and select IBM Db2 database option listed below.
Provide parameters for connecting to source Db2 database server as mentioned below.
A) Server: Provide Db2 server DNS name or IP address.
B) Database: Db2 database name.
C) Connection name: Any name to identify source connection.
D) Data gateway: In case you want to use Data gateway to connect to source Db2 provide data gateway name here. E) Username: Provide mainframe user id which has access to Db2 database.
F) Password: Password for mainframe user id.
Click on Test connection to check if provided parameters and network connectivity to Db2 is working fine. For successful connection you will get message: Connection successful.
Click on Preview data to view sample data from Db2 table.
Click on Destination tab and under Connection drop down option and click on More to create new connection to target Fabric Native SQL Database.
Click on Azure SQL database. Please note connector to both Fabric Native SQL Database and Azure SQL database is same as shown below. You will not see separate connector option for Fabric Native SQL Database.
Provide Fabric Native SQL Database server name, database name and gateway through which connection to Fabric Native SQL DB needs to be made. Also, for Authentication method use Organization account and Sign in. Click on Connect for making connection to target Fabric Native SQL Database.
Provide the table name where data should be copied. The Schema of source Db2 table and target Fabric Native SQL Database table should be same. Microsoft SQL Server Migration Assistant for Db2 (SSMA) software can help to convert Db2 Schema to Fabric Native SQL Database Schema.
D. Run the pipeline
Click on Run option for executing the pipeline to perform data copy from Db2 to Fabric Native SQL Database.
As shown below data copy pipeline execution completed successfully.
Click on Activity Name to see additional details of execution. As shown below 201 records copied from Db2 to Fabric Native SQL Database.
You can even connect to Fabric Native SQL Database from SSMA and check the number of rows inserted.
Summary
The increasing adoption of cloud technology necessitates a seamless way to migrate data to the cloud. Microsoft Fabric is an end-to-end analytics and data platform tailored for enterprises seeking a unified solution. It encompasses a broad range of services including data movement, processing, ingestion, transformation, real-time event routing, and report building. Microsoft Fabric offers a comprehensive suite of capabilities, such as Data Engineering, Data Factory, Data Science, Real-Time Analytics, Data Warehouse, and Databases.
In this blog, we have demonstrated how straightforward it is to copy data from Db2 on Mainframe to Fabric Native SQL Database using Fabric Data Pipelines. This powerful tool simplifies the migration process, ensuring a smooth and efficient transition to the cloud while leveraging the robust features of Microsoft Fabric.
Feedback and suggestions
If you have feedback or suggestions for improving this data migration asset, please send an email to Database Platform Engineering Team.
Updated Jan 02, 2025
Version 1.0skhandelwal
Microsoft
Joined July 28, 2020
Modernization Best Practices and Reusable Assets Blog
Follow this blog board to get notified when there's new activity