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: This is a technology preview feature only. It is being made available to allow you to test and provide feedback on this new capability; however, this feature is not intended for production use and it is not supported as such.
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, you 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, 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
/*
//
- 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.
- 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
|
SQLTP2
|
HCOUTILB
|
SQLUTB
|
- 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.
- 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 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
an Eclipse project
-
- Start
Enterprise Developer.
- 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.