Edit

Share via

Convert Db2 schemas (Db2ToSQL)

  • Article

After you connect to both Db2 and SQL Server, and set project and data mapping options, you can convert Db2 database objects to SQL Server database objects.

The conversion process

Converting database objects takes the object definitions from Db2, converts them to similar SQL Server objects, and then loads this information into the SQL Server Migration Assistant (SSMA) metadata. It doesn't load the information into the instance of SQL Server. You can then view the objects and their properties by using the SQL Server 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.

Set conversion options

Before converting objects, review the project conversion options in the Project Settings dialog box. Use this dialog box to set how SSMA converts functions and global variables. For more information, see Project Settings (Conversion).

Conversion results

The following table shows which Db2 objects are converted, and the resulting SQL Server objects:

Db2 objects Resulting SQL Server objects
Data types SSMA maps every type except the following types:

CLOB: Some native functions that work with this type aren't supported (for example, CLOB_EMPTY())

BLOB: Some native functions for work with this type aren't supported (for example, BLOB_EMPTY())

DBLOB: Some native functions for work with this type aren't supported (for example, DBLOB_EMPTY())
User-defined types SSMA maps the following user-defined objects:

- Distinct type
- Structured type
- SQL PL data types

Note: Weak cursor types aren't supported.
Special registers SSMA only maps the following registers:

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 aren't mapped to SQL Server semantics.
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 aren't 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 Mapped.
SELECT statement SSMA maps SELECT with the following exceptions:

data-change-table-reference clause - Partially mapped, but FINAL tables aren't supported

table-reference clause - Partially mapped, but only-table-reference, outer-table-reference, analyze-table-expression, collection-derived-table, xmltable-expression aren't 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 Mapped.
INSERT statement Mapped.
UPDATE statement SSMA maps UPDATE with the following exceptions:

Table-reference clause - only-table-reference isn't mapped to SQL Server semantics

Period clause - Isn't mapped.
MERGE statement SSMA maps MERGE with the following exceptions:

Single vs Multiple Occurrences of Each clause - Mapped to SQL Server semantics for limited occurrences of each clause

SIGNAL clause - doesn't map to SQL Server semantics

Mixed UPDATE and DELETE clauses - doesn't map to SQL Server semantics

Period-clause - doesn't map to SQL Server semantics
DELETE statement SSMA maps DELETE with the following exceptions:

Table-reference clause - only-table-reference isn't mapped to SQL Server semantics

Period clause - doesn't map to SQL Server semantics
Isolation level and lock type Mapped.
Procedures (SQL) Mapped.
Procedures (external) Require manual update.
Procedures (sourced) Don't map to SQL Server semantics.
Assignment statement Mapped.
CALL statement for a procedure Mapped.
CASE statement Mapped.
FOR statement Mapped.
GOTO statement Mapped.
IF statement Mapped.
ITERATE statement Mapped.
LEAVE statement Mapped.
LOOP statement Mapped.
REPEAT statement Mapped.
RESIGNAL statement Conditions aren't supported. Messages can be optional.
RETURN statement Mapped.
SIGNAL statement Conditions aren't supported. Messages can be optional.
WHILE statement Mapped.
GET DIAGNOSTICS statement SSMA maps GET DIAGNOSTICS with the following exceptions:

ROW_COUNT - Mapped.

Db2_RETURN_STATUS - Mapped.

MESSAGE_TEXT - Mapped.

Db2_SQL_NESTING_LEVEL - doesn't map to SQL Server semantics

Db2_TOKEN_STRING - doesn't map to SQL Server semantics
Cursors SSMA maps cursors with the following exceptions:

ALLOCATE CURSOR statement - doesn't map to SQL Server semantics

ASSOCIATE LOCATORS statement - doesn't map to SQL Server semantics

DECLARE CURSOR statement - Returnability clause isn't mapped to SQL Server semantics

FETCH statement - Partial mapping. Variables as target are supported only. SQLDA DESCRIPTOR isn't mapped to SQL Server semantics
Variables Mapped.
Exceptions, handlers, and conditions SSMA maps exception handling with the following exceptions:

EXIT handlers - Mapped.

UNDO handlers - Mapped.

CONTINUE handlers - Not mapped.

Conditions - It doesn't map to SQL Server semantics.
Dynamic SQL Not mapped.
Aliases Mapped.
Nicknames Partial mapping. Manual processing is required for underlying object
Synonyms Mapped.
Standard Functions in Db2 SSMA maps Db2 standard functions when an equivalent function is available in SQL Server:
Authorization Not mapped.
Predicates Mapped.
SELECT INTO statement Not mapped.
VALUES INTO statement Not mapped.
Transaction control Not mapped.

Convert 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, navigate to View > 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.

View conversion problems

Some Db2 objects might not be converted. You can determine the conversion success rates by viewing the summary conversion report.

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 were 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.

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, select the Report tab.

  6. At the top of the Report tab, is a dropdown list. If the list shows Statistics, change the selection to Source.

    SSMA displays the source code and several buttons immediately above the code.

  7. Select the Next Problem button (a red error icon with a right-pointing arrow).

    SSMA highlights the first problematic source code it finds in the current object.

For each item that couldn't be converted, you've 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 must update the metadata. For more information, see Connect to Db2 database.

  • You can exclude the object from migration. In SQL Server Metadata Explorer and Db2 Metadata Explorer, clear the check box next to the item before loading the objects into SQL Server and migrating data from Db2.

Related content