Tutorial: DB2 Database Batch Migration

This tutorial takes you through each step in the process of migrating a mainframe-based DB2 table on the mainframe to a local SQL Server using Mainframe Batch Database Tools (MBDT). This process can be done for all the tables in your DB2 database.

Requirements

You must have the Micro Focus Rumba+ Desktop product installed and have it configured to connect to your mainframe.

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

Phase 1: Create a SQL Server schema

You need to create a schema in SQL Server where you can place your migrated DB2 tables.

  • Using SQL Server Management Studio, create a SQL Server schema named DSN8910.

Phase 2: Create unloaded data and the LOAD control statement on the mainframe with DB2

Here, you UNLOAD the sample table, DSN8910.EMP, using Micro Focus Rumba+ Desktop to create and submit the appropriate JCL.

  1. Start Rumba+ Desktop from your desktop by navigating to and selecting Micro Focus Rumba Desktop from the Start menu or by selecting the Micro Focus Rumba Desktop tile from the Start page, depending on your Windows version.
  2. In the Create New Session group, click Mainframe Display.
  3. Click Connection > Connect to start a mainframe session.
  4. In the Application field, type TSO; then press Return.
  5. Type your mainframe user ID; then press Return.
  6. In the Password field, type your mainframe password; then press Return.
  7. In the Option field, type 2; then press Return.
  8. In the Member field of the Edit Entry Panel, type EMPDEMO to create the JCL; then press Return.
  9. Code the JCL required to unload the data from the DSN8910.EMP table using DSNUTILB, and to download the LOAD control statement. For example, here you use the following z/OS mainframe job where the mainframe user is MFIXCH: 
    //MFIXCH02 JOB (DB2JOB),'DB2 JOB',REGION=4M,    
//     CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1) 
//UNLOAD1  EXEC PGM=DSNUTILB,REGION=0M,PARM='DB9R,MFIXCH' 
//STEPLIB  DD DSN=DSN910.SDSNLOAD,DISP=SHR
//SYSREC   DD DSN=MFIXCH.UTILBTS.SYSREC1,                           
//       DISP=(MOD,CATLG),                            
//       UNIT=SYSDA,SPACE=(CYL,(1,1))     
//SYSPUNCH DD DSN=MFIXCH.UTILBTS.PUNCH1,    
//       UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(MOD,CATLG) 
//SYSPRINT DD SYSOUT=* 
//UTPRINT  DD  DUMMY 
//SORTOUT  DD  UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND) 
//SYSDISC  DD  DUMMY
//SYSERR   DD  UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)                  
//SYSMAP   DD  UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)                  
//SYSUT1   DD  UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)                  
//SYSIN    DD *                                                         
UNLOAD DATA FROM TABLE DSN8910.EMP                                      
/*                                                                      
//
  10. To save and submit your JCL, enter SAVE into the Command field, and then enter SUB into the Command field.

Phase 3: Create and configure an enterprise server instance

Now you want to use Enterprise Server to create an enterprise server instance with Mainframe Subsystem (MSS). MSS enterprise servers support JCL applications in Enterprise Developer.

Create an MSS enterprise server instance
Set up XA resources
Now that the enterprise server is created and started, you need to set up the XA resources.
  1. Ensure that you have returned to the Enterprise Server Administration Home page.
  2. Click the Details button that corresponds to the started DSNUTILS enterprise server.
  3. Click Server > Properties > XA Resources; then click Add.
  4. Populate the fields on the XA Resources tab as follows:
    Field Value
    ID HCOD
    Name HCODEMO
    Module esodbcxa.dll
    Open string DSN=HCODEMO
  5. Click Add to complete the XA resources setup.
Create aliases for MBDT
  1. Click Server > Control; then click ES Monitor and Control.
  2. From the Resources menu in the left pane, click JES; then click Alias.
  3. Create the following aliases:
    Program Alias
    HCOTEP2 SQLTP2
    HCOUTILB SQLUTB
  4. Click Update to save the aliases.

Phase 4: Copy the LOAD control statement

In this phase, you copy the LOAD control statement from the mainframe to your Windows machine using Micro Focus Mainframe Access Drag and Drop.

  1. Start Windows Explorer, and browse to the %ProgramFiles(x86)%\Micro Focus\Enterprise Developer\bin directory.
  2. Double-click mfdasmx.exe to launch Micro Focus Mainframe Access Drag and Drop.
  3. In the User Identification field, type your mainframe user ID.
  4. In the Current field, type your mainframe password.
  5. In the Catalog search criteria field, enter the PDS prefix for the PDS that contains your LOAD control statement; then click OK.
  6. Click Configure > Options.
  7. In the EBCDIC/ASCII translation group, check Sequential; then click OK.
  8. Drag and drop the mainframeID.UTILBTS.PUNCH1 file from the right pane to a destination folder in the left pane.
  9. Click OK on each popup notice.
  10. Close Mainframe Access Drag and Drop.

Phase 5: Copy and catalog the unloaded DB2 data

Using Micro Focus Mainframe Access command-line tools, you copy the unloaded DB2 data to your Windows machine, and catalog the data there.

  1. Start an Enterprise Developer command prompt as an administrator. If you need instructions, see To start an Enterprise Developer command prompt as an administrator.
  2. At the command prompt, enter the following to copy the unloaded data in mainframeID.UTILBTS.SYSREC1 to a data file, DSN8910_EMP.DAT, and catalog mainframeID.UTILBTS.SYSREC1 in your DSNUTILS enterprise server instance: 
    MFDAS IMPORT datFilePath\DSN8910_EMP.DAT FROM QSAM 
mainframeID.UTILBTS.SYSREC1 /CATLOC=ESWorkArea\DSNUTILS\catalog.dat
    Where:
    Data Description
    mainframeID The dataset qualifier
    datFilePath The local path to the directory where you want to store the extracted data file
    DSN8910_EMP.DAT The name of the data file containing extracted data
    ESWorkArea Your Enterprise Server WORKAREA folder, which by default is %USERPROFILE%\Documents\Micro Focus User\Enterprise Developer\WORKAREA
    DSNUTILS The name of your enterprise server instance.
    catalog.dat The name of the enterprise server catalog data file.
    After the command has completed, the mainframeID.UTILBTS.SYSREC1 data set has been copied to the DSN8910_EMP.DAT file and cataloged as mainframeID.UTILBTS.SYSREC1 in your enterprise server.

Phase 6: Load the SQL Server database

To enhance the performance of LOAD DATA into SQL Server databases, you divide the creation of schema objects into two steps. In the first DSNTEP2 step, before you load the actual data, you create tables and primary keys only, because when creating primary keys, SQL Server automatically creates a clustered index for each primary key. A clustered index helps the performance of LOAD DATA and minimizes the use of disk space. In another step you create constraints and indexes. After you load the data, you execute another DSNTEP2 step where you create constraints and indexes.

Create a Visual Studio project
  1. In Visual Studio, click File > New > Project.
  2. In the New Project dialog box, expand Installed > COBOL.
  3. Click Enterprise Server, and then click Native.
  4. Select Mainframe Subsystem Application.
  5. In the Name field, type JCL1; then click OK to create a new JCL application project.
  6. From the Solution Explorer, right-click the JCL1 project; then select Add > New Folder.
  7. Name the new folder JCL.
Associate the DSNUTILS enterprise server with the JCL1 project
  1. From Server Explorer, expand Localhost under Micro Focus Servers to see a list of available enterprise servers.
  2. Right-click the DSNUTILS enterprise server; then click Associate with project > JCL1.
  3. If not already started, right-click the DSNUTILS enterprise server; then select Start. If this is the first time you start the server you see a sign-on dialog box. Select Store Credentials to skip this step in the future, and click OK to continue.
Run the MBDT Configuration Utility
  1. From Enterprise Developer, click View > Micro Focus SQL Tools > MBDT Configuration Utility.
  2. In the Database Selection group, select HCOSS
  3. Click Save to exit the MBDT Configuration Utility.
Create DB2 tables in the SQL Server database using SQLTP2
The SQLTP2 utility creates the appropriate DB2 tables in your new SQL Server database. You execute the SQLTP2 alias in JCL using the HCOTEP2 program name. In this phase, you create the JCL in your JCL1 project, and execute it from your JCL1 project.
  1. From the Solution Explorer, right-click the new JCL folder, and select Add > New Item.
  2. Expand COBOL > Native, and select JCL File Template.
  3. In the Name field, type JCLTEP2.jcl; then click Add.
  4. Open the JCLTEP2.jcl file, and replace its contents with the JCLTEP2 job shown below. This assumes you have setup the XA resource HCOD and created alias SQLTP2 for program HCOTEP2, all of which is required for successful execution later: 
    //JCLTEP2  JOB 
//RESETDB  EXEC PGM=IKJEFT01
//SYSTSPRT DD  SYSOUT=*
//SYSPRINT DD  SYSOUT=*
//SYSPUNCH DD  SYSOUT=*                                
//SYSREC00 DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//SYSTSIN  DD  *
 DSN SYSTEM(HCOD)
 RUN  PROGRAM(HCOTEP2)
 END
/*
//SYSIN DD *
CREATE TABLE DSN8910.EMP (
    EMPNO       CHAR(6) NOT NULL,
    FIRSTNME    VARCHAR(12) NOT NULL,
    MIDINIT     CHAR(1) NOT NULL,
    LASTNAME    VARCHAR(15) NOT NULL,
    WORKDEPT    CHAR(3),
    PHONENO     CHAR(4),
    HIREDATE    DATE,
    JOB         CHAR(8),
    EDLEVEL     SMALLINT,
    SEX         CHAR(1),
    BIRTHDATE   DATE,
    SALARY      DECIMAL(9,2),
    BONUS       DECIMAL(9,2),
    COMM        DECIMAL(9,2),
    PRIMARY KEY (EMPNO)
)
IN DATABASE DBNATS;

/*
//
  5. Save and close the JCL file.
  6. In the Solution Explorer, right-click JCLTEP2.jcl; then select Submit JCL from the context menu.
  7. When the job has completed, open SQL Server Management Studio and verify that the DSN8910.EMP table and clustered index definitions are correct.
Load DB2 Data into SQL Server
Now you load the DB2 data into the new SQL Server database using SQLUTB with the LOAD card generated on the mainframe.
  1. From the Solution Explorer, right-click the new JCL folder, and select Add > New Item.
  2. Expand COBOL > Native, and select JCL File Template.
  3. In the Name field, type JCLUTILB.jcl; then click Add.
  4. Open the JCLUTILB.jcl file, and replace its contents with the JCLUTILB job shown below, replacing MFIXCH with your mainframeID. This assumes you have setup the XA resource HCOD and created alias SQLUTB for program HCOUTILB, all of which is required for successful execution later: 
    //JCLUTILB JOB 
//DSNUPROC EXEC PGM=HCOUTILB,PARM='HCOD,SH      '
//*
//SYSPRINT DD   SYSOUT=*
//UTPRINT  DD   SYSOUT=*
//SYSUDUMP DD   SYSOUT=*
//STPRIN01 DD   SYSOUT=*
//IDIOFF   DD   DUMMY
//*
//SYSUT1   DD   DISP=MOD,DSN=SH.DSNUTILB.SYSUT1
//SORTOUT  DD   DISP=MOD,DSN=SH.DSNUTILB.SYSOUT
//SYSMAP   DD   DISP=MOD,DSN=SH.DSNUTILB.SYSMAP
//SYSERR   DD   DISP=MOD,DSN=SH.DSNUTILB.SYSERR
//SYSREC   DD   DISP=SHR,DSN=MFIXCH.UTILBTS.SYSREC1
//SYSIN    DD   *
LOAD DATA INDDN SYSREC LOG NO RESUME NO REPLACE 
EBCDIC  CCSID(00037,00000,00000)
 INTO TABLE
 "DSN8910".
 "EMP"
 WHEN(00001:00002) = X'0012'
 NUMRECS                   42
 ( "EMPNO"
  POSITION(  00003:00008) CHAR(00006)
 , "FIRSTNME"
  POSITION(  00009:00022) VARCHAR
 , "MIDINIT"
  POSITION(  00023:00023) CHAR(00001)
 , "LASTNAME"
  POSITION(  00024:00040) VARCHAR
 , "WORKDEPT"
  POSITION(  00042:00044) CHAR(00003)
                          NULLIF(00041)=X'FF'
 , "PHONENO"
  POSITION(  00046:00049) CHAR(00004)
                          NULLIF(00045)=X'FF'
 , "HIREDATE"
  POSITION(  00051:00060) DATE EXTERNAL
                          NULLIF(00050)=X'FF'
 , "JOB"
  POSITION(  00062:00069) CHAR(00008)
                          NULLIF(00061)=X'FF'
 , "EDLEVEL"
  POSITION(  00071:00072) SMALLINT
                          NULLIF(00070)=X'FF'
 , "SEX"
  POSITION(  00074:00074) CHAR(00001)
                          NULLIF(00073)=X'FF'
 , "BIRTHDATE"
  POSITION(  00076:00085) DATE EXTERNAL
                          NULLIF(00075)=X'FF'
 , "SALARY"
  POSITION(  00087:00091) DECIMAL
                          NULLIF(00086)=X'FF'
 , "BONUS"
  POSITION(  00093:00097) DECIMAL
                          NULLIF(00092)=X'FF'
 , "COMM"
  POSITION(  00099:00103) DECIMAL
                          NULLIF(00098)=X'FF'
 )
 
/*
//
  5. Save and close the JCL file.
  6. In the Solution Explorer, right-click JCLUTILB.jcl; then select Submit JCL from the context menu.
  7. When the job has completed, open SQL Server Management Studio and verify that the data in mainframeID.UTILBTS.SYSREC1 has been successfully loaded into the DSN8910.EMP table.
Execute DDL to create DB2 constraints and indexes
You execute SQLTP2 again to create the DB2 constraints and indexes in the SQL Server database.
  1. From the Solution Explorer, right-click the new JCL folder, and select Add > New Item.
  2. Expand COBOL > Native, and select JCL File Template.
  3. In the Name field, type JCLINDX.jcl; then click Add.

  4. Open the JCLINDX.jcl file, and replace its contents with the JCLINDX job shown below: 

    //JCLINDX JOB 
//RESETDB  EXEC PGM=IKJEFT01
//SYSTSPRT DD  SYSOUT=*
//SYSPRINT DD  SYSOUT=*
//SYSPUNCH DD  SYSOUT=*                                
//SYSREC00 DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//SYSTSIN  DD  *
 DSN SYSTEM(HCOD)
 RUN  PROGRAM(HCOTEP2)
 END
/*
//SYSIN DD *
ALTER TABLE DSN8910.EMP
   ADD CONSTRAINT MIN_SALARY CHECK (SALARY > 15000.00);  
CREATE INDEX DSN8910.XEMP2 ON DSN8910.EMP(WORKDEPT);

/*
//
  5. Save and close the JCL file.
  6. In the Solution Explorer, right-click JCLINDX.jcl; then select Submit JCL from the context menu.
  7. When the job has completed, open SQL Server Management Studio and verify the constraints and indexes under the DSN8910.EMP. In particular, see that the DSN8910 qualifier in the XEMP2 index has been removed in SQL Server.
Parent topic: Database Migration
Related tasks
To start an Enterprise Developer command prompt as an administrator