Converting DB2 Schemas (DB2ToSQL)
After you have connected to DB2, connected to 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 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.
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).
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 listed below:
CLOB: Some native functions for work with this type is not 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:
SQL PL data types - Note: Weak cursor type aren't supported.
|Special Registers||SSMA only maps registers listed below:
SESSION_USER and USER
CURRENT LOCK TIMEOUT
Other Special Registers aren't mapped to SQL server semantic.
|CREATE TABLE||SSMA maps CREATE TABLE with the following exceptions:
Multidimensional clustering (MDC) tables
Range-clustered tables (RCT)
DATA CAPTURE clause
IMPLICITLY HIDDEN 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:
BUSINESS_TIME WITHOUT OVERLAPS option
SPECIFICATION ONLY option
EXTEND USING 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
|SELECT Statement||SSMA maps SELECT with the following exceptions:
Data-change-table-reference clause - Partially mapped, but FINAL tables don'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||Is mapped.|
|INSERT Statement||Is mapped.|
|UPDATE Statement||SSMA 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 - 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 is not mapped to SQL server semantics
Period clause - doesn't 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 aren't supported. Messages can be optional.|
|RETURN Statement||Is mapped.|
|SIGNAL Statement||Conditions aren't 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 - 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 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
|Exceptions, Handlers, and Conditions||SSMA maps "exception handling" with the following exceptions:
EXIT Handlers - Are mapped.
UNDO Handlers - Are mapped.
CONTINUE Handlers - Aren't mapped.
Conditions - It doesn't map to SQL server semantics.
|Dynamic SQL||Not mapped.|
|Nicknames||Partial mapping. Manual processing is required for underlying object|
|Standard Functions in DB2||SSMA maps DB2 standard functions when an equivalent function is available in SQL Server:|
|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
In DB2 Metadata Explorer, expand the DB2 server, and then expand Schemas.
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.
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
In DB2 Metadata Explorer, select Schemas.
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
In DB2 Metadata Explorer, expand Schemas.
Expand the schema that shows a red error icon.
Under the schema, expand a folder that has a red error icon.
Select the object that has a red error icon.
In the right pane, click the Report tab.
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.
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'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 will have to update the metadata. For more information, see Connecting to DB2 Database (DB2ToSQL).
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.
The next step in the migration process is to Load the converted objects into SQL Server.