Skip to content

Latest commit

 

History

History
203 lines (166 loc) · 7.99 KB

connector-db2.md

File metadata and controls

203 lines (166 loc) · 7.99 KB
title description services documentationcenter author manager ms.reviewer ms.service ms.workload ms.tgt_pltfrm ms.devlang ms.topic ms.date ms.author
Copy data from DB2 using Azure Data Factory | Microsoft Docs
Learn how to copy data from DB2 to supported sink data stores by using a copy activity in an Azure Data Factory pipeline.
data-factory
linda33wj
craigg
douglasl
data-factory
data-services
na
na
conceptual
08/17/2018
jingwang

Copy data from DB2 by using Azure Data Factory

[!div class="op_single_selector" title1="Select the version of Data Factory service you are using:"]

This article outlines how to use the Copy Activity in Azure Data Factory to copy data from a DB2 database. It builds on the copy activity overview article that presents a general overview of copy activity.

Supported capabilities

You can copy data from DB2 database to any supported sink data store. For a list of data stores that are supported as sources/sinks by the copy activity, see the Supported data stores table.

Specifically, this DB2 connector supports the following IBM DB2 platforms and versions with Distributed Relational Database Architecture (DRDA) SQL Access Manager (SQLAM) version 9, 10 and 11:

  • IBM DB2 for z/OS 11.1
  • IBM DB2 for z/OS 10.1
  • IBM DB2 for i 7.2
  • IBM DB2 for i 7.1
  • IBM DB2 for LUW 11
  • IBM DB2 for LUW 10.5
  • IBM DB2 for LUW 10.1

Tip

If you receive an error message that states "The package corresponding to an SQL statement execution request was not found. SQLSTATE=51002 SQLCODE=-805", the reason is a needed package is not created for normal user on such OS. Follow these instructions according to your DB2 server type:

  • DB2 for i (AS400): let power user create collection for the login user before using copy activity. Command: create collection <username>
  • DB2 for z/OS or LUW: use a high privilege account - power user or admin with package authorities and BIND, BINDADD, GRANT EXECUTE TO PUBLIC permissions - to run the copy activity once, then the needed package is automatically created during copy. Afterwards, you can switch back to normal user for your subsequent copy runs.

Prerequisites

To use copy data from a DB2 database that is not publicly accessible, you need to set up a Self-hosted Integration Runtime. To learn about Self-hosted integration runtimes, see Self-hosted Integration Runtime article. The Integration Runtime provides a built-in DB2 driver, therefore you don't need to manually install any driver when copying data from DB2.

Getting started

[!INCLUDE data-factory-v2-connector-get-started]

The following sections provide details about properties that are used to define Data Factory entities specific to DB2 connector.

Linked service properties

The following properties are supported for DB2 linked service:

Property Description Required
type The type property must be set to: Db2 Yes
server Name of the DB2 server. You can specify the port number following the server name delimited by colon e.g. server:port. Yes
database Name of the DB2 database. Yes
authenticationType Type of authentication used to connect to the DB2 database.
Allowed value is: Basic.		 Yes
username Specify user name to connect to the DB2 database. Yes
password Specify password for the user account you specified for the username. Mark this field as a SecureString to store it securely in Data Factory, or reference a secret stored in Azure Key Vault. Yes
connectVia The Integration Runtime to be used to connect to the data store. You can use Self-hosted Integration Runtime or Azure Integration Runtime (if your data store is publicly accessible). If not specified, it uses the default Azure Integration Runtime. No

Example:

{
    "name": "Db2LinkedService",
    "properties": {
        "type": "Db2",
        "typeProperties": {
            "server": "<servername:port>",
            "database": "<dbname>",
            "authenticationType": "Basic",
            "username": "<username>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Dataset properties

For a full list of sections and properties available for defining datasets, see the datasets article. This section provides a list of properties supported by DB2 dataset.

To copy data from DB2, set the type property of the dataset to RelationalTable. The following properties are supported:

Property Description Required
type The type property of the dataset must be set to: RelationalTable Yes
tableName Name of the table in the DB2 database. No (if "query" in activity source is specified)

Example

{
    "name": "DB2Dataset",
    "properties":
    {
        "type": "RelationalTable",
        "linkedServiceName": {
            "referenceName": "<DB2 linked service name>",
            "type": "LinkedServiceReference"
        },
        "typeProperties": {}
    }
}

Copy activity properties

For a full list of sections and properties available for defining activities, see the Pipelines article. This section provides a list of properties supported by DB2 source.

DB2 as source

To copy data from DB2, set the source type in the copy activity to RelationalSource. The following properties are supported in the copy activity source section:

Property Description Required
type The type property of the copy activity source must be set to: RelationalSource Yes
query Use the custom SQL query to read data. For example: "query": "SELECT * FROM \"DB2ADMIN\".\"Customers\"". No (if "tableName" in dataset is specified)

Example:

"activities":[
    {
        "name": "CopyFromDB2",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<DB2 input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "RelationalSource",
                "query": "SELECT * FROM \"DB2ADMIN\".\"Customers\""
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Data type mapping for DB2

When copying data from DB2, the following mappings are used from DB2 data types to Azure Data Factory interim data types. See Schema and data type mappings to learn about how copy activity maps the source schema and data type to the sink.

DB2 Database type Data factory interim data type
BigInt Int64
Binary Byte[]
Blob Byte[]
Char String
Clob String
Date Datetime
DB2DynArray String
DbClob String
Decimal Decimal
DecimalFloat Decimal
Double Double
Float Double
Graphic String
Integer Int32
LongVarBinary Byte[]
LongVarChar String
LongVarGraphic String
Numeric Decimal
Real Single
SmallInt Int16
Time TimeSpan
Timestamp DateTime
VarBinary Byte[]
VarChar String
VarGraphic String
Xml Byte[]

Next steps

For a list of data stores supported as sources and sinks by the copy activity in Azure Data Factory, see supported data stores.