Micro Focus Enterprise Developer 2.3 for Visual Studio 2015 > Welcome > Getting Started > Mainframe Tutorials > Tutorials: HCO for SQL Server (HCOSS) > Tutorial: DB2 Database Batch Migration

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.

Note:

Requirements

You must have the Micro Focus Rumba 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, we UNLOAD the sample table, DSN8910.EMP, using Micro Focus Rumba to create and submit the appropriate JCL.

To start Rumba, click Start > All Programs > Micro Focus Rumba > Micro Focus Rumba Desktop.
In the Create New Session group, click Mainframe Display.
Click Connection > Connect to start a mainframe session.
In the Application field, type TSO; then press Return.
Type your mainframe user ID; then press Return.
In the Password field, type your mainframe password; then press Return.
In the Option field, type 2; then press Return.
In the Member field of the Edit Entry Panel, type EMPDEMO to create the JCL; then press Return.

Code the JCL required to unload the data from the DSN8910.EMP table using DSNUTILB, and to download the LOAD control statement. For example:

//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 *                                                         
NLOAD DATA FROM TABLE DSN8910.EMP                                      
/*                                                                      
//

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

Follow all of the instructions presented in the Configure an Enterprise Server for JCL Support section of the JCL tutorial in this documentation; however, substitute DSNUTILS wherever JCLDEMO is specified.

Set up XA resources

Now that the enterprise server is created and started, we need to set up the XA resources.

Ensure that you have returned to the Enterprise Server Administration Home page.
Click the Details button that corresponds to the started DSNUTILS enterprise server.
Click Server > Properties > XA Resources; then click Add.

Populate the fields on the XA Resources tab as follows:

ID	`HCOD`
Name	`HCODEMO`
Module	`esodbcxa.dll`
Open string	`DSN=HCODEMO`

Click Add to complete the XA resources setup.

Create aliases for MBDT

Click Server > Control; then click ES Monitor and Control.
From the Resources menu in the left pane, click JES; then click Alias.

Create the following aliases:

Program	Alias
HCOTEP2	SQLTP232
HCOUTILB	SQLUTB32

Click Update to save the aliases.

Phase 4: Copy the LOAD control statement

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

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

Click Start > All Programs > Micro Focus Enterprise Developer > Tools; then right-click Enterprise Developer Command Prompt (32 bit) and select Run as Administrator.

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 AUTO 
mainframeID.UTILBTS.SYSREC1 /CATLOC=ESWorkArea\DSNUTILS\catalog.dat

Where:

`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\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 mainframeIDUTILBTS.SYSREC1 data set has been copied to the DSN8910_EMP.DAT file and cataloged as mainframeIDUTILBTS.SYSREC1 in your enterprise server.

Phase 6: Load the SQL Server database

To enhance the performance of LOAD DATA into SQL Server databases, we divide the creation of schema objects into two steps. In one step, we 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 we create constraints and indexes.

Create a Visual Studio project

Start Enterprise Developer.
Click File > New > Project.
Expand COBOL > Native > Mainframe Subsystem Application.
In the Name field, type JCL1; then click OK to create a new JCL application project.
From the Solution Explorer, right-click the JCL1 project; then select Add > New Folder.
Name the new folder JCL.

Associate the DSNUTILS enterprise server with the JCL1 project

From Server Explorer, expand Localhost under Micro Focus Servers 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. 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

From Enterprise Developer, click View > Micro Focus SQL Tools > MBDT Configuration Utility.
In the Database Selection group, select HCOSS
Close the MBDT Configuration Utility.

Create DB2 tables in the SQL Server database using SQLTP232

The SQLTP232 utility creates the appropriate DB2 tables in your new SQL Server database. You execute the SQLTP232 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 Solution Explorer, right-click the new JCL folder, and select Add > New Item.
Expand COBOL > Native, and select JCL File Template.
In the Name field, type JCLTEP2.jcl; then click Add.

Open the JCLTEP2.jcl file, and add the job specifications and the DDL necessary to create the table DSN8910.EMP. The completed JCL is:

//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 Solution Explorer, right-click JCLTEP2.jcl; then select Submit JCL 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 the MBDT SQLUTB32 utility with the LOAD card generated on the mainframe.

From the Solution Explorer, right-click the new JCL folder, and select Add > New Item.
Expand COBOL > Native, and select JCL File Template.
In the Name field, type JCLUTILB.jcl; then click Add.

To load mainframeID.UTILBTS.PUNCH1 into the DSN8910.EMP table, add the LOAD control statement and change or add to the job specifications in the JCL file as follows:

Specify the program HCOUTILB and DSN name HCOD
Add REPLACE to the LOAD DATA option
Map SYSREC to the cataloged data set (mainframeID.UTILBTS.PUNCH1).

The completed JCL is as follows, with the exception that you provide your mainframe ID in place of MFIXCH:

//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 Solution Explorer, right-click JCLUTILB.jcl; then select Submit JCL from the context menu.
When the job has completed, open SQL Server Management Studio and verify that the data in the DSN8910.EMP table are correct.

Execute DDL to create DB2 constraints and indexes

We execute the MBDT SQLTP232 utility again to create the DB2 constraints and indexes in the SQL Server database.

From the Solution Explorer, right-click the new JCL folder, and select Add > New Item.
Expand COBOL > Native, and select JCL File Template.
In the Name field, type JCLINDX.jcl; then click Add.

To the JCL file, add the job specifications and the DDL necessary to create a check constraint and an index. The completed JCL is:

//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 Solution Explorer, right-click JCLINDX.jcl; then select Submit JCL 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.