| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | ms.collection | |
|---|---|---|---|---|---|---|---|---|---|---|
Load converted database objects into SQL Server (Db2ToSQL) |
Learn how to load converted database objects into SQL Server with SSMA for Db2 |
cpichuka |
cpichuka |
randolphwest |
09/24/2024 |
sql |
ssma |
how-to |
|
After you have converted Db2 schemas to [!INCLUDE ssNoVersion], you can load the resulting database objects into [!INCLUDE ssNoVersion]. You can either have SQL Server Migration Assistant (SSMA) create the objects, or you can script the objects and run the scripts yourself. Also, SSMA lets you update target metadata with the actual contents of [!INCLUDE ssNoVersion] database.
If you want to load the converted database objects into [!INCLUDE ssNoVersion] without modification, you can have SSMA directly create or recreate the database objects. That method is quick and easy, but doesn't allow for customization of the [!INCLUDE tsql] code that defines the [!INCLUDE ssNoVersion] objects, other than stored procedures.
If you want to modify the [!INCLUDE tsql] that is used to create objects, or if you want more control over objects creation, use SSMA to create scripts. You can then modify those scripts, create each object individually, and even use [!INCLUDE ssNoVersion] Agent to schedule creating those objects.
To use SSMA to create [!INCLUDE ssNoVersion] database objects, you select the objects in [!INCLUDE ssNoVersion] Metadata Explorer, and then synchronize the objects with [!INCLUDE ssNoVersion], as shown in the following procedure. By default, if the objects already exist in [!INCLUDE ssNoVersion], and if the SSMA metadata is newer than the object in [!INCLUDE ssNoVersion], SSMA alters the object definitions in [!INCLUDE ssNoVersion]. You can change the default behavior by editing Project Settings.
Note
You can select existing [!INCLUDE ssNoVersion] database objects that weren't converted from Db2 databases. However, those objects will not be recreated or altered by SSMA.
-
In [!INCLUDE ssNoVersion] Metadata Explorer, expand the top [!INCLUDE ssNoVersion] node, and then expand Databases.
-
Select the objects to process:
-
To synchronize a complete database, select the check box next to the database name.
-
To synchronize or omit individual objects or categories of objects, select or clear the check box next to the object or folder.
-
-
After you have selected the objects to process in [!INCLUDE ssNoVersion] Metadata Explorer, right-click Databases, and then select Synchronize with Database.
You can also synchronize individual objects or categories of objects by right-clicking the object or its parent folder, and then selecting Synchronize with Database.
After that, SSMA will display the Synchronize with Database dialog, where you can see two groups of items. On the left side, SSMA shows selected database objects represented in a tree. On the right side, you can see a tree representing the same objects in SSMA metadata. You can expand the tree by selecting on the right or left '+' button. The direction of the synchronization is shown in the Action column placed between the two trees.
An action sign can be in three states:
-
A left arrow means the contents of metadata are saved in the database (the default).
-
A right arrow means database contents overwrite the SSMA metadata.
-
A cross sign means no action is taken.
-
Select the action sign to change the state. Actual synchronization is performed when you select OK button of the Synchronize with Database dialog.
To save [!INCLUDE tsql] definitions of the converted database objects, or to alter the object definitions and run scripts yourself, you can save the converted database object definitions to [!INCLUDE tsql] scripts.
-
After you have selected the objects to save to a script, right-click Databases, and then select Save as Script.
You can also script individual objects or categories of objects by right-clicking the object or its parent folder, and then selecting Save as Script.
-
In the Save As dialog box, locate the folder where you want to save the script, enter a file name in the File name box, and then select OK. SSMA appends the .sql file name extension.
After you save the [!INCLUDE ssNoVersion] object definitions as one or more scripts, you can use [!INCLUDE ssManStudioFull] to view and modify the scripts.
-
In [!INCLUDE ssManStudioFull], navigate to File > Open > File.
-
In the Open dialog box, select your script file, and then select OK.
-
Edit the script file by using the query editor.
For more information about the query editor, see "Editor Convenience Commands and Features" in [!INCLUDE ssNoVersion] Books Online.
-
To save the script, select File > Save.
You can run a script, or individual statements, in [!INCLUDE ssManStudioFull].
-
In [!INCLUDE ssManStudioFull], navigate to File > Open > File.
-
In the Open dialog box, select your script file, and then select OK.
-
To run the complete script, press the F5 key.
-
To run a set of statements, select the statements in the query editor window, and then press the F5 key.
For more information about how to use the query editor to run scripts, see " [!INCLUDE ssManStudioFull] [!INCLUDE tsql] Query" in [!INCLUDE ssNoVersion] Books Online.
You can also run scripts from the command line by using the sqlcmd utility, and from the [!INCLUDE ssNoVersion] Agent. For more information about sqlcmd, see "sqlcmd Utility" in [!INCLUDE ssNoVersion] Books Online. For more information about [!INCLUDE ssNoVersion] Agent, see "Automating Administrative Tasks ([!INCLUDE ssNoVersion] Agent)" in [!INCLUDE ssNoVersion] Books Online.
After you load the converted database objects into [!INCLUDE ssNoVersion], you can grant and deny permissions on those objects. It's a good idea to do this step before migrating data to [!INCLUDE ssNoVersion]. For information about how to help secure objects in [!INCLUDE ssNoVersion], see "Security Considerations for Databases and Database Applications" in [!INCLUDE ssNoVersion] Books Online.