Migrate the Database

Provides step-by-step instructions to complete the migration of the DB2 database to your SQL Server database by using an Eclipse 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.

Start the DSNUTILS enterprise server region

On the list of enterprise server regions in the Server Explorer, right-click DSNUTILS, and then select Start from the context menu.
If a sign-on dialog box appears, click OK.

Run the MBDT Configuration Utility

From the Eclipse main menu, click Run > Tools > MBDT Configuration Utility.
In the Database Selection group, select HCOSS
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.

In the Application Explorer view, right-click the JCL1 project, and select New > JCL File.
In the New file name field, type JCLTEP2.jcl, and then click Finish.

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, both of which you did previously in this tutorial:

//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 DSN81110.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;

/*
//

Save and close the JCL file.
In the Application Explorer view, right-click JCLTEP2.jcl; then select Submit to Enterprise Server from the context menu.
When the job has completed, open SQL Server Management Studio and verify that the DSN81110.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.

In the Application Explorer view, right-click the JCL1 project, and select New > JCL File.
In the New file name field, type JCLUTILB.jcl, and then click Finish.

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, both of which you did previously in this tutorial:

//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
 "DSN81110".
 "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'
 )
 
/*
//

Save and close the JCL file.
In the Application Explorer view, right-click JCLUTILB.jcl; then select Submit to Enterprise Server from the context menu.
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 DSN81110.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.

In the Application Explorer view, right-click the JCL1 project, and select New > JCL File.
In the New file name field, type JCLINDX.jcl, and then click Finish.

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 DSN81110.EMP
   ADD CONSTRAINT MIN_SALARY CHECK (SALARY > 15000.00);  
CREATE INDEX DSN81110.XEMP2 ON DSN81110.EMP(WORKDEPT);

/*
//

Save and close the JCL file.
In the Application Explorer view, right-click JCLINDX.jcl; then select Submit to Enterprise Server from the context menu.
When the job has completed, open SQL Server Management Studio and verify the constraints and indexes under the DSN81110.EMP. In particular, see that the DSN81110 qualifier in the XEMP2 index has been removed in SQL Server.

This completes the tutorial.