This tutorial takes you through each step in the process of migrating a mainframe-based DB2 database to a Microsoft SQL Server database using HCO for SQL Server database migration tools.
Requirements
Before attempting this tutorial, you must first complete the following tutorials to ensure you have an established SQL Server database named HCO_Test and a connection to that database:
- Tutorial: Create a SQL Server Database
- Tutorial: Create a SQL Server Database Connection
To complete this tutorial, you need access to and connection information for a mainframe-based DB2 database. This includes:
- Computer address and port number
- Initial catalog and Package collection name
- User ID and password that grant you read access to the SYSIBM system database tables
- Standard schema provided with DB2, named
DSNversion-id, where
version-id depends on the version of DB2 you are using.
Phase 1: Start
Enterprise Developer and HCO for SQL Server
If
Enterprise Developer is already running as an administrator and HCOSS for SQL Server was started from
Eclipse, skip this phase.
- Start
Enterprise Developer as an administrator. If you need instructions, see
To start
Enterprise Developer as an administrator.
- From
Eclipse, click
Run > Tools > HCO for SQL Server.
Phase 2: Create a DB2 Database Connection
You need to establish a connection to a DB2 database on your mainframe so that you can access its SYSIBM tables.
- Be sure you have installed the Microsoft OLEDB Provider for DB2.
- From the HCO for SQL Server interface, click
Manage Connections. This takes you to the
Connection List tab.
- Click
New DB2 Connection. This takes you to the
DB2 Connection tab.
- In the
Data Source Name field, type
mainframeDB2 to provide a name for the connection.
- Provide values for all remaining fields on this tab. Valid entries for all fields are required to ensure a valid connection. If you do not know the correct values required, contact your DB2 administrator.
- Click
Test.
A prompt appears showing the name of the schema that contains your mainframe system catalogs. By default, this is
SYSIBM. If the schema name provided is incorrect, type the name of the correct schema.
- Click
OK.
- If the test fails, review your field entries, make corrections accordingly, and try again. When you have a successful connection, click
OK.
- Click
Save to create the connection. This takes you back to the
Connection List tab where you should now see your DB2 connection listed.
Phase 3: Extract a DB2 Schema
In this phase, you download the standard DSN schema from your DB2 database in the form of an XML file that contains its information, and view that file in a text editor. The standard schema name varies depending on your version of DB2:
DB2 Version
|
DSN Schema Name
|
9
|
DSN8910
|
10
|
DSN8010
|
11
|
DSN8110
|
- Extract the DSN schema
-
- From the HCO for SQL Server interface, click
Extract Schema. This takes you to the
Schema Extract tab.
- Click
New.
- In the
Schema Extract Name field, type
schema-name where
schema-name is the DSN schema name for your version of DB2; then click
OK.
- From the
DB2 Connection drop-down list, select the DB2 data source that you specified when creating your connection with the Manage Connections tool.
Depending on your DB2 database connection specifications, you might be prompted for a user ID and password. If so, enter the user ID and password you supplied when creating the DB2 connection.
- Click
Local. This sets the Extract Schema tool to save the generated schema extract file to the directory specified by the
Local HCOSS Directory HCOSS option.
- On the
Select schema(s) to extract list, check the box that corresponds to the schema that corresponds to your version of DB2.
- In the
Table Row Count group, select
Estimated using schema statistics. This option estimates the table row count based on the most recent statistics available, and returns results faster than the other option,
Both Estimated and Actual (COUNT(*)), which additionally performs a COUNT_BIG(*) on the selected table or tables.
- Click
Extract. This initiates the extract schema process, writing the
schema-name.hcodbs XML file, and takes you to the
Results tab. Here you can see the results of the extraction.
- View the
schema-name.hcodbs File
-
- From the HCO for SQL Server interface, click
Options.
- Note the path specification for the
Local HCOSS Directory. The default is
%LOCALAPPDATA%\Micro Focus\Enterprise Developer\hcoss.
- Start a text editor and open the following file:
localHCOSSDirectory\Schemas\schema-name.hcodbs
Where
localHCOSSDirectory is the path specification for the
Local HCOSS Directory option.
- When you have finished looking at the file, close the text editor. Do not save any changes if prompted.
Phase 4: Create a Transfer List
In this phase, you create a list of objects from the DB2 schema you extracted. In a later phase, HCOSS uses this transfer list to identify the objects associated with this extraction and migration.
- From the HCO for SQL Server interface, click
Define Lists.
- On the
Transfer Lists tab, click
New.
- In the
Name field, type
schema-name where
schema-name is the DSN schema name for your version of DB2.
- From the
Schema Extract drop-down list, select
schema-name. This populates the
Available Objects list.
- Click
Add All to add all available schema objects to the
Selected Objects list.
- Click
Save.
Phase 5: Generate DDL and Verify
In this phase of the migration process, you generate DDL from the schema objects in your object transfer list. You use this DDL in the next phase to define the structure of your new SQL Server database, including tables, views, indexes, and foreign key relationships.
- Generate DDL
-
- From the HCO for SQL Server interface, click
Generate DDL. This takes you to the
DDL Tasks tab.
- Click
New to create a new DDL task.
- In the
Name field, type
schema-nametest.
- From the
Transfer List drop-down list, select
schema-name.
- From the
Connection for Execute drop-down list, select
HCODemo.
- To map the extracted schema to a SQL Server schema:
- Click
Schema Mappings. This takes you to the
Schema Mappings tab.
- In the
SQL Server Schema Name field, type
TEST.
- Click the
DDL Tasks tab; then click
Save.
- Click
Execute to execute the task to generate the DDL.
- Verify the SQL Server Database Structure
-
- Open the Server Explorer in
Eclipse and add a connection for your SQL Server server.
- Verify that the appropriate objects were created in the SQL Server database by expanding the entry for your
HCO_Test database and its
Tables sub-entry. You should see the objects created in the TEST schema. If you don't see the objects, click on the database name and then refresh.
- Repeat the Process
-
- Map the extracted schema to a different SQL Server schema by repeating the procedure, using the following information:
DDL task name |
schema-nameprod |
Associated transfer list |
schema-name |
Connection for execute |
HCODemo |
SQL Server schema name |
PROD |
- Save, execute, and verify.
Phase 6: Transfer Data and Verify
You now use the HCOSS Transfer Data tool to populate the schema objects in your SQL Server database with data from your DB2 database.
- Transfer Data
-
- From the HCO for SQL Server interface, click
Transfer Data. This takes you to the
Transfer Data Tasks tab.
- Click
New to create a new transfer data task.
- In the
Name field, type
schema-nametest.
- From the
DDL Task drop-down list, select
schema-nametest.
- Click
OK.
- From the
Source Connection drop-down list, select
mainframeDB2, which is the name of your DB2 connection.
- From the
Destination Connection drop-down list, select
HCODemo, which is the name of your SQL Server connection.
- Click
Save to save the task.
- Click
Transfer to transfer the list. This takes you to the
Results tab where you can view log messages as the transfer takes place.
- Verify the Data Transfer
-
- In the
Eclipse Server Explorer, view the table data for the HCO_Test database's TEST.DEPT table.
- Repeat the Process
-
- Transfer data for a different DDL task by repeating the procedure, using the following information:
Transfer Data task name |
schema-nameprod |
Associated DDL task |
schema-nameprod |
Source connection |
mainframeDB2 |
Destination connection |
HCODemo |
- Save, transfer, and verify.
Phase 7: Compare Data
In this final phase, you compare the data in the DB2 database to the data transferred into the SQL Server database using the HCOSS Compare Data tool.
- Compare Data
-
- From the HCO for SQL Server interface, click
Compare Data.
- Select an appropriate
Source Connection and
Destination Connection.
- Click
New to create a new compare data task.
- In the
Name field, type
schema-nametest.
- From the
DDL Task drop-down list, select
schema-nametest.
- Click
OK.
- Click
Save to save the compare data task.
- Click
Compare to compare the data. This takes you to the
Results tab where you can view messages as the tool compares the data.
- Repeat the Process
-
- Compare data for another DDL task by repeating the procedure, using the following information:
Compare Data task name |
schema-nameprod |
Associated DDL task |
schema-nameprod |
Source connection |
mainframeDB2 |
Destination connection |
HCODemo |
- Save and compare.
This completes the tutorial. Next, please complete one or more Application Migration tutorials. These tutorials use the TEST and PROD schemas you created in this tutorial.