Tutorial: DB2 Database Migration

Requirements

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 Visual Studio, skip this phase.

1. Start Enterprise Developer as an administrator. If you need instructions, see To start Enterprise Developer as an administrator.
2. From Visual Studio, click View > Micro Focus SQL 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 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:

Extract the DSN schema

1. From the HCO for SQL Server interface, click Extract Schema. This takes you to the Schema Extract tab.
2. Click New.
3. 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.
4. 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.

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. On the Select schema(s) to extract list, check the box that corresponds to the schema that corresponds to your version of DB2.
7. 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.
8. 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

1. Click Tools > Options > Micro Focus > HCO for SQL Server Tools.
2. Note the path specification for the Local HCOSS Directory. The default is %ALLUSERSPROFILE%\Micro Focus\Enterprise Developer\hcoss.
3. 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.

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 schema-name where schema-name is the DSN schema name for your version of DB2.
4. From the Schema Extract drop-down list, select schema-name. 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 schema-nametest.
4. From the Transfer List drop-down list, select schema-name.
5. From the Connection for Execute drop-down list, select HCODemo.
6. 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.
7. Click the DDL Tasks tab; then click Save.
8. 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	schema-nameprod
   Associated transfer list	schema-name
   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 schema-nametest.
4. From the DDL Task drop-down list, select schema-nametest.
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	schema-nameprod
   Associated DDL task	schema-nameprod
   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 schema-nametest.
5. From the DDL Task drop-down list, select schema-nametest.
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	schema-nameprod
   Associated DDL task	schema-nameprod
   Source connection	mainframeDB2
   Destination connection	HCODemo

2. Save and compare.