Tutorial: DB2 Database Migration

Requirements

Phase 1: Start Studio Enterprise Edition and HCO for SQL Server

If Studio Enterprise Edition and HCOSS for SQL Server tools are already running, skip this phase.

1. Start Studio Enterprise Edition as an administrator from Start > All Programs > Micro Focus Studio Enterprise Edition x.x > Microsoft Visual Studio 2008.
2. From the Visual Studio IDE, click Tools > Micro Focus COBOL Tools > HCO for SQL Server Tools.

Phase 2: Create a DB2 Database Connection

Phase 3: Extract a DB2 Schema

In this phase, you download the DSN8910 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 DSN8910 schema is a standard schema provided with DB2 for z/OS v9.

Extract the DSN8910 schema

1. From the HCO for SQL Server interface, click Extract Schema. This takes you to the Schema Extract tab.
2. From the Source Connection drop-down list, select the DB2 data source that you specfied 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.

   After completing the connection, HCOSS displays a list of available schema objects.

3. In the Extract column, check the box that corresponds to the DSN8910 schema.
4. In the Schema Extract Name field, type dsn8910.
5. 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.
6. Click Extract. This initiates the extract schema process, writing the dsn8910.hcodbs XML file, and takes you to the Results tab. Here you can see the results of the extraction.

View the dsn8910.hcodbs File

1. From the Visual Studio IDE, click Tools > Options > Micro Focus COBOL Tools > HCO for SQL Server.
2. Note the path specification for the Local HCOSS Directory.
3. Start a text editor and open the following file:

   %APPDATA%\Micro Focus\NetExpress\6.0\HCOSS\Schemas\dsn8910.hcodbs

   Where localDirectory is the path specification for the Local HCOSS Directory option.

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

1. From the HCO for SQL Server interface, click Define Lists.
2. On the Transfer Lists tab, click New.
3. In the Name field, type dsn8910.
4. From the Schema Extract drop-down list, select dsn8910. This populates the Available Objects list.
5. Click Add All to add all available schema objects to the Selected Objects list.
6. 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

1. From the HCO for SQL Server interface, click Generate DDL. This takes you to the DDL Tasks tab.
2. Click New to create a new DDL task.
3. In the Name field, type dsn8910test.
4. From the Transfer List drop-down list, select dsn8910.
5. Click OK.
6. From the Connection for Execute drop-down list, select HCODemo.
7. To map the extracted schema to a SQL Server schema:
   1. Click Schema Mappings. This takes you to the Schema Mappings tab.
   2. In the SQL Server Schema Name field, type TEST.
8. Click the DDL Tasks tab; then click Save.
9. Click Execute to execute the task to generate the DDL.

Verify the SQL Server Database Structure

1. Open the Server Explorer in Visual Studio and add a connection for your SQL Server server.
2. 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

1. Map the extracted schema to a different SQL Server schema by repeating the procedure, using the following information:

   DDL task name	dsn8910prod
   Associated transfer list	dsn8910
   Connection for execute	HCODemo
   SQL Server schema name	PROD

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

1. From the HCO for SQL Server interface, click Transfer Data. This takes you to the Transfer Data Tasks tab.
2. Click New to create a new transfer data task.
3. In the Name field, type dsn8910test.
4. From the DDL Task drop-down list, select dsn8910test.
5. Click OK.
6. From the Source Connection drop-down list, select mainframeDB2, which is the name of your DB2 connection.
7. From the Destination Connection drop-down list, select HCODemo, which is the name of your SQL Server connection.
8. Click Save to save the task.
9. 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 Visual Studio Server Explorer, view the table data for the HCO_Test database's TEST.DEPT table.

Repeat the Process

1. Transfer data for a different DDL task by repeating the procedure, using the following information:

   Transfer Data task name	dsn8910prod
   Associated DDL task	dsn8910prod
   Source connection	mainframeDB2
   Destination connection	HCODemo

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

1. From the HCO for SQL Server interface, click Compare Data.
2. Select an appropriate Source Connection and Destination Connection.
3. Click New to create a new compare data task.
4. In the Name field, type dsn8910test.
5. From the DDL Task drop-down list, select dsn8910test.
6. Click OK.
7. Click Save to save the compare data task.
8. 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

1. Compare data for another DDL task by repeating the procedure, using the following information:

   Compare Data task name	dsn8910prod
   Associated DDL task	dsn8910prod
   Source connection	mainframeDB2
   Destination connection	HCODemo

2. Save and compare.