Skip to content

mapping-db2-schemas-to-sql-server-schemas-db2tosql.md

Latest commit

 

History

History
72 lines (46 loc) · 4.27 KB

mapping-db2-schemas-to-sql-server-schemas-db2tosql.md

File metadata and controls

72 lines (46 loc) · 4.27 KB
title Map Db2 schemas to SQL Server schemas (Db2ToSQL)
description Learn how to customize the mapping between Db2 schemas and SQL Server databases in SSMA for Db2.
author nilabjaball
ms.author niball
ms.reviewer randolphwest
ms.date 09/24/2024
ms.service sql
ms.subservice ssma
ms.topic how-to
ms.collection
sql-migration-content
f1_keywords
ssma.db2.schemamappingpanel.f1

Map Db2 schemas to SQL Server schemas (Db2ToSQL)

In Db2, each database has one or more schemas. By default, SQL Server Migration Assistant (SSMA) migrates all objects in an Db2 schema to a [!INCLUDE ssNoVersion] database named for the schema. However, you can customize the mapping between Db2 schemas and [!INCLUDE ssNoVersion] databases.

Db2 and SQL Server schemas

An Db2 database contains schemas. An instance of [!INCLUDE ssNoVersion] contains multiple databases, each of which can have multiple schemas.

The Db2 concept of a schema maps to the [!INCLUDE ssNoVersion] concept of a database and one of its schemas. For example, Db2 might have a schema named HR. An instance of [!INCLUDE ssNoVersion] might have a database named HR, and within that database are schemas. One schema is the dbo (or database owner) schema. By default, the Db2 schema HR is mapped to the [!INCLUDE ssNoVersion] database and schema HR.dbo. SSMA refers to the [!INCLUDE ssNoVersion] combination of database and schema as a schema.

You can modify the mapping between Db2 and [!INCLUDE ssNoVersion] schemas.

Modify the target database and schema

In SSMA, you can map an Db2 schema to any available [!INCLUDE ssNoVersion] schema.

Modify the database and schema

  1. In Db2 Metadata Explorer, select Schemas.

    The Schema Mapping tab is also available when you select an individual database, the Schemas folder, or individual schemas. The list in the Schema Mapping tab is customized for the selected object.

  2. In the right pane, select the Schema Mapping tab.

    You see a list of all Db2 schemas, followed by a target value. This target is denoted in a two part notation (database.schema) in [!INCLUDE ssNoVersion] where your objects and data are migrated.

  3. Select the row that contains the mapping that you want to change, and then select Modify.

    In the Choose Target Schema dialog box, you might browse for available target database and schema or type the database and schema name in the textbox in a two part notation (database.schema) and then select OK.

  4. The target changes on the Schema Mapping tab.

Modes of mapping

Mapping to SQL Server

You can map source database to any target database. By default, the source database is mapped to the target [!INCLUDE ssNoVersion] database that you connected to using SSMA. If the target database being mapped is non-existing on [!INCLUDE ssNoVersion], then you're prompted with a message:

The Database and/or schema does not exist in target SQL Server metadata. It would be created during synchronization. Do you wish to continue?

Select Yes. Similarly, you can map schema to non-existing schema under target [!INCLUDE ssNoVersion] database, which is created during synchronization.

Revert to the default database and schema

If you customize the mapping between an Db2 schema and a [!INCLUDE ssNoVersion] schema, you can revert the mapping back to the default values.

  1. Under the schema mapping tab, select any row and select Reset to Default to revert to the default database and schema.

Related content