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.

Create an Eclipse project

Click File > New > Mainframe COBOL Project.
In the Project name field, type JCL1; then click Finish to create a new JCL application project.

Associate the DSNUTILS enterprise server with the JCL1 project

To start the Server Explorer, click Window > Show View > Other > Micro Focus COBOL > Server Explorer; then click OK.
From Server Explorer, expand Localhost to see a list of available enterprise servers.
Right-click the DSNUTILS enterprise server; then click Associate with project > JCL1.
If not already started, right-click the DSNUTILS enterprise server; then select Start.
On the sign-on dialog box, click OK; then click No to disable password recovery.

Run the MBDT Configuration Utility

From Enterprise Developer, 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.

From the COBOL Explorer, right-click the JCL1 project, and select New > JCL File.
In the New file name field, type JCLTEP2.jcl; 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, 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;

/*
//

Save and close the JCL file.
In the COBOL Explorer, 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 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.

From the COBOL Explorer, right-click the JCL1 project, and select New > JCL File.
In the New file name field, type JCLUTILB.jcl; 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, 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'
 )
 
/*
//

Save and close the JCL file.
In the COBOL Explorer, 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 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.

From the COBOL Explorer, right-click the JCL1 project, and select New > JCL File.
In the New file name field, type JCLINDX.jcl; 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 DSN8910.EMP
   ADD CONSTRAINT MIN_SALARY CHECK (SALARY > 15000.00);  
CREATE INDEX DSN8910.XEMP2 ON DSN8910.EMP(WORKDEPT);

/*
//

Save and close the JCL file.
In the COBOL Explorer, 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 DSN8910.EMP. In particular, see that the DSN8910 qualifier in the XEMP2 index has been removed in SQL Server.

This completes the tutorial.