Migrate the Database

Provides step-by-step instructions to complete the migration of the DB2 database to your SQL Server database by using a Visual Studio JCL project and Mainframe Batch Database tools.

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 Create a new project dialog box, select COBOL from the Language drop-down list.
  3. Select Mainframe Subsystem Application.
  4. Click Next.
  5. In the Name field, type JCL1; then click Create 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.

This completes the tutorial.

Parent topic: Tutorial: Mainframe Batch Database Tools (MBDT)
Previous topic: Download and Catalog DB2 Data