---
title: "Converting DB2 Schemas (DB2ToSQL) | Microsoft Docs"
ms.prod: sql
ms.custom: ""
ms.date: "01/19/2017"
ms.reviewer: ""
ms.technology: ssma
ms.topic: conceptual
ms.assetid: 7947efc3-ca86-4ec5-87ce-7603059c75a0
author: "Shamikg"
ms.author: "Shamikg"
---
# Converting DB2 Schemas (DB2ToSQL)
After you have connected to DB2, connected to [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], and set project and data mapping options, you can convert DB2 database objects to [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] database objects.
## The Conversion Process
Converting database objects takes the object definitions from DB2, converts them to similar [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] objects, and then loads this information into the SSMA metadata. It does not load the information into the instance of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. You can then view the objects and their properties by using the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Metadata Explorer.
During the conversion, SSMA prints output messages to the Output pane and error messages to the Error List pane. Use the output and error information to determine whether you have to modify your DB2 databases or your conversion process to obtain the desired conversion results.
## Setting Conversion Options
Before converting objects, review the project conversion options in the **Project Settings** dialog box. By using this dialog box, you can set how SSMA converts functions and global variables. For more information, see [Project Settings (Conversion) (DB2ToSQL)](../../ssma/db2/project-settings-conversion-db2tosql.md).
## Conversion Results
The following table shows which DB2 objects are converted, and the resulting [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] objects:
|DB2 Objects|Resulting SQL Server Objects|
|-----------|----------------------------|
|Data Types|**SSMA maps every type except the following listed below:**
CLOB: Some native functions for work with this type are not supported (e.g. CLOB_EMPTY())
BLOB: Some native functions for work with this type are not supported (e.g. BLOB_EMPTY())
DBLOB: Some native functions for work with this type are not supported (e.g. DBLOB_EMPTY())|
|User-Defined Types|**SSMA maps the following User-Defined:**
Distinct Type
Structured Type
SQL PL data types - Note: Weak cursor type are not supported.|
|Special Registers|**SSMA only maps registers listed below:**
CURRENT TIMESTAMP
CURRENT DATE
CURRENT TIME
CURRENT TIMEZONE
CURRENT USER
SESSION_USER and USER
SYSTEM_USER
CURRENT CLIENT_APPLNAME
CURRENT CLIENT_WRKSTNNAME
CURRENT LOCK TIMEOUT
CURRENT SCHEMA
CURRENT SERVER
CURRENT ISOLATION
Other Special Registers are not mapped to SQL server semantic.|
|CREATE TABLE|**SSMA maps CREATE TABLE with the following exceptions:**
Multidimensional clustering (MDC) tables
Range-clustered tables (RCT)
Partitioned tables
Detached table
DATA CAPTURE clause
IMPLICITLY HIDDEN option
VOLATILE option|
|CREATE VIEW|SSMA maps CREATE VIEW with 'WITH LOCAL CHECK OPTION' but other options are not mapped to SQL server semantics|
|CREATE INDEX|**SSMA maps CREATE INDEX with the following exceptions:**
XML index
BUSINESS_TIME WITHOUT OVERLAPS option
PARTITIONED clause
SPECIFICATION ONLY option
EXTEND USING option
MINPCTUSED option
PAGE SPLIT option|
|Triggers|**SSMA maps the following trigger semantics:**
AFTER / FOR EACH ROW Triggers
AFTER /FOR EACH STATEMENT Triggers
BEFORE / FOR EACH ROW and INSTEAD OF / FOR EACH ROW Triggers|
|Sequences|Are mapped.|
|SELECT Statement|**SSMA maps SELECT with the following exceptions:**
Data-change-table-reference clause - Partially mapped, but FINAL tables does not supported
Table-reference clause - Partially mapped, but only-table-reference, outer-table-reference, analyze_table-expression, collection-derived-table, xmltable-expression are not mapped to SQL server semantics
Period-specification clause - Not mapped.
Continue-handler clause - Not mapped.
Typed-correlation clause - Not mapped.
Concurrent-access-resolution clause - Not mapped.|
|VALUES Statement|Is mapped.|
|INSERT Statement|Is mapped.|
|UPDATE Statement|S**SMA maps UPDATE with the following exceptions:**
Table-reference clause - Only-table-reference is not mapped to SQL server semantics
Period clause - Is not mapped.|
|MERGE Statement|**SSMA maps MERGE with the following exceptions:**
Single vs Multiple Occurrences of Each Clause - Is mapped to SQL server semantics for limited occurrences of each clause
SIGNAL Clause - Does not map to SQL Server semantics
Mixed UPDATE and DELETE Clauses - Does not map to SQL Server semantics
Period-clause - Does not map to SQL Server semantics|
|DELETE Statement|**SSMA maps DELETE with the following exceptions:**
Table-reference clause - Only-table-reference is not mapped to SQL server semantics
Period clause - Does not map to SQL Server semantics|
|Isolation Level and Lock Type|Is mapped.|
|Procedures (SQL)|Are mapped.|
|Procedures (external)|Require manual update.|
|Procedures (Sourced)|Do not map to SQL Server semantics.|
|Assignment statement|Is mapped.|
|CALL Statement for a Procedure|Is mapped.|
|CASE Statement|Is mapped.|
|FOR Statement|Is mapped.|
|GOTO statement|Is mapped.|
|IF Statement|Is mapped.|
|ITERATE Statement|Is mapped.|
|LEAVE Statement|Is mapped.|
|LOOP Statement|Is mapped.|
|REPEAT Statement|Is mapped.|
|RESIGNAL Statement|Conditions are not supported. Messages can be optional.|
|RETURN Statement|Is mapped.|
|SIGNAL Statement|Conditions are not supported. Messages can be optional.|
|WHILE Statement|Is mapped.|
|GET DIAGNOSTICS Statement|**SSMA maps GET DIAGNOSTICS with the following exceptions:**
ROW_COUNT - Is mapped.
DB2_RETURN_STATUS - Is mapped.
MESSAGE_TEXT - Is mapped.
DB2_SQL_NESTING_LEVEL - Does not map to SQL Server semantics
DB2_TOKEN_STRING - Does not map to SQL Server semantics|
|Cursors|**SSMA maps CURSORS with the following exceptions:**
ALLOCATE CURSOR statement - Does not map to SQL Server semantics
ASSOCIATE LOCATORS statement - Does not map to SQL Server semantics
DECLARE CURSOR statement - Returnability clause is not mapped to SQL server semantics
FETCH statement - Partial mapping. Variables as target are supported only. SQLDA DESCRIPTOR is not mapped to SQL server semantics|
|Variables|Are mapped.|
|Exceptions, Handlers, and Conditions|**SSMA maps "exception handling" with the following exceptions:**
EXIT Handlers - Are mapped.
UNDO Handlers - Are mapped.
CONTINUE Handlers - Are not mapped.
Conditions - It does not map to SQL server semantics.|
|Dynamic SQL|Not mapped.|
|Aliases|Are mapped.|
|Nicknames|Partial mapping. Manual processing is required for underlying object|
|Synonyms|Are mapped.|
|Standard Functions in DB2|SSMA maps DB2 standard functions when an equivalent function is available in SQL Server:|
|Authorization|Not mapped.|
|Predicates|Are mapped.|
|SELECT INTO statement|Not mapped.|
|VALUES INTO statement|Not mapped.|
|Transaction control|Not mapped.|
## Converting DB2 Database Objects
To convert DB2 database objects, you first select the objects that you want to convert, and then have SSMA perform the conversion. To view output messages during the conversion, on the **View** menu, select **Output**.
**To convert DB2 objects to SQL Server syntax**
1. In DB2 Metadata Explorer, expand the DB2 server, and then expand **Schemas**.
2. Select objects to convert:
- To convert all schemas, select the check box next to **Schemas**.
- To convert or omit a database, select the check box next to the schema name.
- To convert or omit a category of objects, expand a schema, and then select or clear the check box next to the category.
- To convert or omit individual objects, expand the category folder, and then select or clear the check box next to the object.
3. To convert all selected objects, right-click **Schemas** and select **Convert Schema**.
You can also convert individual objects or categories of objects by right-clicking the object or its parent folder, and then selecting **Convert Schema**.
## Viewing Conversion Problems
Some DB2 objects might not be converted. You can determine the conversion success rates by viewing the summary conversion report.
**To view a summary report**
1. In DB2 Metadata Explorer, select **Schemas**.
2. In the right pane, select the **Report** tab.
This report shows the summary assessment report for all database objects that have been assessed or converted. You can also view a summary report for individual objects:
- To view the report for an individual schema, select the schema in DB2 Metadata Explorer.
- To view the report for an individual object, select the object in DB2 Metadata Explorer. Objects that have conversion problems have a red error icon.
For objects that failed conversion, you can view the syntax that resulted in the conversion failure.
**To view individual conversion problems**
1. In DB2 Metadata Explorer, expand **Schemas**.
2. Expand the schema that shows a red error icon.
3. Under the schema, expand a folder that has a red error icon.
4. Select the object that has a red error icon.
5. In the right pane, click the **Report** tab.
6. At the top of the **Report** tab is a drop-down list. If the list shows **Statistics**, change the selection to **Source**.
SSMA will display the source code and several buttons immediately above the code.
7. Click the **Next Problem** button. This is a red error icon with an arrow that points to the right.
SSMA will highlight the first problematic source code it finds in the current object.
For each item that could not be converted, you have to determine what you want to do with that object:
- You can modify the source code for procedures on the **SQL** tab.
- You can modify the object in the DB2 database to remove or revise problematic code. To load the updated code into SSMA, you will have to update the metadata. For more information, see [Connecting to DB2 Database (DB2ToSQL)](../../ssma/db2/connecting-to-db2-database-db2tosql.md).
- You can exclude the object from migration. In [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Metadata Explorer and DB2 Metadata Explorer, clear the check box next to the item before loading the objects into [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] and migrating data from DB2.
## Next Step
The next step in the migration process is to [Load the converted objects into SQL Server](https://msdn.microsoft.com/f4ea1ced-9f9f-4a9d-88ab-81dbab64adc3).
## See Also
[Migrating DB2 Data into SQL Server (DB2ToSQL)](../../ssma/db2/migrating-db2-data-into-sql-server-db2tosql.md)