![](/uploads/1/2/5/4/125410064/110359814.jpeg)
-->
To migrate DB2 databases to SQL Server, you must connect to the DB2 database that you want to migrate. When you connect, SSMA obtains metadata about all DB2 schemas, and then displays it in the DB2 Metadata Explorer pane. SSMA stores information about the database server, but does not store passwords.
Your connection to the database stays active until you close the project. When you reopen the project, you must reconnect if you want an active connection to the database.
Mar 7, 2017 - The first thing to decide when accessing Db2 from the command line is whether that command line will be on the database server or on a client. At XTIVIA, our Db2 DBAs are command line geeks. We prefer the command line to GUIs for administering and working with Db2. The command line is a simple and reliable interface that makes scripting and automation for Db2 easy. Db2 command line environments are simple to set up in just a few steps.
Metadata about the DB2 database is not automatically updated. Instead, if you want to update the metadata in DB2 Metadata Explorer, you must manually update it. For more information, see the 'Refreshing DB2 Metadata' section later in this topic.
Required DB2 Permissions
User authorization defines the list of the commands and objects that are available for a user. This list thereby controls user actions. In DB2, there are predetermined groups of privileges for authorization, both at the instance level and at the level of a DB2 database. This enables SSMA to obtain metadata from schemas owned by the connecting user. To obtain metadata for objects in other schemas and then convert objects in those schemas, the account must have the following permissions:
- Schema Access for schema migration is normally granted to PUBLIC unless the RESTRICT keyword was used in CREATE
- Data access for data migration requires DATAACCESS
Establishing a Connection to DB2
When you connect to a database, SSMA reads the database metadata, and then adds this metadata to the project file. This metadata is used by SSMA when it converts objects to SQL Server syntax, and when it migrates data to SQL Server. You can browse this metadata in the DB2 Metadata Explorer pane and review properties of individual database objects.
Important
Before you try to connect, make sure that the database server is running and can accept connections.
To connect to DB2
- On the File menu, select Connect to DB2.If you previously connected to DB2, the command name will be Reconnect to DB2.
- In the Provider box you will see the OLE DB Provider which is currently the only DB2 client access provider.
- In the Manager box you can select either Db2 for zOs, or DB2 for LUW
- In the Mode box, select either Standard mode, or Connection string mode.Use standard mode to specify the server name and port. Use service name mode to specify the DB2 service name manually. Use connection string mode to provide a full connection string.
- If you select Standard mode, provide the following values:
- In the Server name box, enter or select the name or IP address of the database server.
- If the database server is not configured to accept connections on the default port (1521), enter the port number that is used for DB2 connections in the Server port box.
- In the Server Port box, enter the TCP/IP Port number.
- In the Initial Catalog box, enter the database name
- In the User name box, enter an DB2 account that has the necessary permissions.
- In the Password box, enter the password for the specified user name.
- If you select Connection string mode, provide a connection string in the Connection string box.The following example shows an OLE DB connection string:
Provider=OraOLEDB.DB2;Data Source=MyDB2DB;User Id=myUsername;Password=myPassword;
The following example shows an DB2 Client connection string that uses integrated security:Data Source=MyDB2DB;Integrated Security=yes;
For more information, see Connect To Oracle (OracleToSQL).
Reconnecting to DB2
![Line Line](/uploads/1/2/5/4/125410064/323639524.jpg)
Your connection to the database server stays active until you close the project. When you reopen the project, you must reconnect if you want an active connection to the database. You can work offline until you want to update metadata, load database objects into SQL Server, and migrate data.
Refreshing DB2 Metadata
Metadata about the DB2 database is not automatically refreshed. The metadata in DB2 Metadata Explorer is a snapshot of the metadata when you first connected, or the last time that you manually refreshed metadata. You can manually update metadata for all schemas, a single schema, or individual database objects.
To refresh metadata
- Make sure that you are connected to the database.
- In DB2 Metadata Explorer, select the check box next to each schema or database object that you want to update.
- Right-click Schemas, or the individual schema or database object, and then select Refresh from Database.If you do not have an active connection, SSMA will display the Connect to DB2 dialog box so that you can connect.
- In the Refresh from Database dialog box, specify which objects to refresh.
- To refresh an object, click the Active field adjacent to the object until an arrow appears.
- To prevent an object from being refreshed, click the Active field adjacent to the object until an X appears.
- To refresh or decline a category of objects, click the Active field adjacent to the category folder.
To view the definitions of the color coding, click the Legend button. - Click OK.
Next Step
- The next step in the migration process is to Connecting to SQL Server.
See Also
I have two DB2 servers running Workgroup. For example, Server1 and Server2.
From Server2, at a command prompt I would like to run a SQL command against a known database on Server1.
Can someone explain to me exactly the steps I need to do in order to accomplish this. And do I simply use DB2ADMIN login for crendentials or do I need to setup some other login?
user500741user500741
1 Answer
You need to catalog the Server1 database on Server2.
You haven't mentioned if the servers are Windows or *nix.
If the servers are *nix, you need to log in with a user that has the db2 executables loaded in it's .profile. (Check the .profile of the db2 instance owner if you are not sure).
If the servers are Windows, then you can log in as anybody that can see the IBM DB2 Program Group, and can open the DB2 Command Window or similar.
From the command line on Server2 you need to catalog Server1 as a TCPIP node. The doc is here. It will probably be something like this:
Next you need to catalog the database on the node. The doc is here. It will probably be something like this:
If you need to check the results of the commands, you can use LIST NODE DIRECTORY or LIST DATABASE DIRECTORY to see what db2 has cataloged.
Once you have done all that you should be able to connect to the database on Server1 from Server2 by:
Once the connection has been made you should be able to query the remote database.
This information will stay in the local db2 catalog unless you explicitly remove it. (You can verify that by logging out, logging back in and using LIST NODE DIRECTORY or LIST DB DIRECTORY). If you want to remove the entries, you can use the UNCATALOG DATABASE and UNCATALOG NODE commands.
Michael SharekMichael Sharek
Not the answer you're looking for? Browse other questions tagged db2 or ask your own question.
![](/uploads/1/2/5/4/125410064/110359814.jpeg)