Tutorial: DB2 Database Migration

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

You need to establish a connection to a DB2 database on your mainframe so that you can access its SYSIBM tables.
  1. Be sure you have installed the Microsoft OLEDB Provider for DB2.
  2. From the HCO for SQL Server interface, click Manage Connections. This takes you to the Connection List tab.
  3. Click New DB2 Connection. This takes you to the DB2 Connection tab.
  4. In the Data Source Name field, type mainframeDB2 to provide a name for the connection.
  5. 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.
  6. 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.

  7. Click OK.
  8. If the test fails, review your field entries, make corrections accordingly, and try again. When you have a successful connection, click OK.
  9. 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
  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 %LOCALAPPDATA%\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.

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.