Offline Extract Schema Solution

The HCOSS Extract Schema tool, when used as an online tool, requires a direct connection to DB2 for z/OS. In some production environments, this direct access can have a negative effect on the speed of regular business transactions on production databases, or can sometimes upset database security policy. To get around this, we provide a solution for schema extraction that can be done off line.

You can use Mainframe Batch Database Tools (MBDT) along with z/OS DB2 DSNUTILB UNLOAD to eliminate the need for direct access to the z/OS DB2 catalog. With this approach, the contents of the DB2 tables are unloaded from the mainframe using methods that either minimize or have no effect on z/OS DB2 processing. Once DB2 catalog tables are unloaded, you can use MBDT to load this information into SQL Server. This enables the Extract Schema tool to access the catalog via SQL Server.

This offline Extract Schema approach expands the existing HCOSS Toolset by altering only the Extract Schema step in the process of HCOSS database migration. All other steps, from Assess Data Migration to Compare Data, remain unchanged.

Like the online Extract Schema tool, the HCOSS Offline Extract Schema tool supports multiple versions of DB2 for z/OS, starting with Version 9.

Unlike the online Extract Schema tool, which is one-step process, the offline tool requires that you perform multiple steps:

1. Unload DB2 catalog tables from mainframe DB2 using z/OS DB2 DSNUTILB UNLOAD
2. Import and catalog the unloaded data sets in Enterprise Server
3. Create the DB2 catalog tables in SQL Server
4. Load the data sets to SQL Server catalog tables using MBDT DSNUTILB LOAD
5. Create foreign key references for the SQL Server catalog tables
6. Run the Extract Schema tool with a SQL Server ADO.NET DSN

The following sections provide details for each step:

Step 1: Unload catalog tables from z/OS DB2

Use the DB2 for z/OS batch utility, DSNUTILB UNLOAD, to unload the DB2 catalog tables for consumption by the MBDT DSNUTILB LOAD tool.

The SYSIBM schema contains 17 DB2 catalog tables that must be unloaded. To expedite this, Micro Focus provides the UNLOAD.parm file that contains all recommended UNLOAD cards. While some of the UNLOAD cards in UNLOAD.parm select an entire table, some of them select only a subset of the columns of a particular table. For example:

UNLOAD EBCDIC CCSID(00037,00037,00000)
DATA FROM TABLE SYSIBM.SYSPARMS
( SCHEMA,
  NAME,
  PARMNAME,
  ROWTYPE,
  ORDINAL,
  TYPENAME,
  LENGTH,
  SCALE,
  SUBTYPE,
  CCSID,
  ENCODING_SCHEME)

The purpose of selecting a subset of columns for some tables is to reduce the amount of data to unload, yet still provide enough information for the HCOSS Extract Schema tool to work successfully.

Important: When unloading, be sure to save the generated LOAD cards. These LOAD cards are used in Step 4.

Note: If you are using z/OS DB2 version 9, you must use the documented UNLOAD card provided for SYSIBM.SYSVIEWS in UNLOAD.parm rather than the one provided for z/OS DB2 version 10 or later.

Step 2: Copy the unloaded DB2 data and catalog it in Enterprise Server

Using Micro Focus Mainframe Access MFDAS IMPORT command-line tool, copy the unloaded DB2 data to your local Windows machine, and then catalog the data in an enterprise server region.

Preparing to copy and unload

Before you can run MFDAS IMPORT, you must create an enterprise server region that is enabled for both Mainframe Subsystem Support and Job Entry Subsystem (JES).

MFDAS IMPORT enables you to both copy and catalog all in one step, but you must run the tool separately for each extracted table. Therefore, you might consider creating a single batch file containing an MFDAS IMPORT command for each table.

Executing MFDAS IMPORT

You must run MFDAS IMPORT from an Enterprise Developer command prompt as an administrator. The method you use to do this varies depending on your operating system. See To start an Enterprise Developer command prompt as an administrator for details.

At your Enterprise Developer command prompt, run MFDAS IMPORT using the following syntax:

MFDAS IMPORT datFilePath\datFileName.dat 
FROM QSAM mainframeDataSet /CATLOC=ESWorkArea\ESRegionName\catalog.dat

Where the variables are:

datFilePath

The path to a local directory on Windows where you want to store the file containing extracted unloaded data set data.

datFileName

The name of a data file to contain the extracted data.

mainframeDataSet

The name of the unloaded data set on the mainframe.

ESWorkArea

Your Enterprise Server WORKAREA folder, which by default is %USERPROFILE%\Documents\Micro Focus User\Enterprise Developer\WORKAREA .

ESRegionName

The name of your MSS- and JES-enabled enterprise server region.

For example, to copy and catalog the data set for SYSVIEWS, the command line might look like this:

MFDAS IMPORT C:\work\DB2CATALOG\SYSVIEWS.DAT FROM QSAM MFIXCH.UTILBTS.ULSYSVWS 
/CATLOC=%USERPROFILE%\Documents\Micro Focus User\Enterprise Developer\WORKAREA 
		EXSCHEMA\catalog.dat

Step 3: Create the DB2 catalog tables in SQL Server

To expedite the creation of DB2 catalog tables in SQL Server, Micro Focus provides the CREATECATALOG.sql script file. This file contains the SQL Server CREATE TABLE statements required to migrate the IBM catalog tables to SQL Server.

We recommended that you execute CREATECATALOG.sql from SQL Server Management Studio. Before execution, be sure that the SYSIBM schema exists in the SQL Server database.

Step 4: Load the data sets into SQL Server catalog tables

The MBDT DSNUTILB LOAD tool loads the imported and cataloged Enterprise Server data sets into SQL Server tables. Use the LOAD cards generated by z/OS DB2 DSNUTILB UNLOAD in Step 2 with some minor changes as follows:

• Replace the RESUME YES with the REPLACE YES option
• Specify the EBCDIC option explicitly to override the MBDT default of ASCII.

The following is a JCL example of MBDT DSNUTILB LOAD into a table in SQL Server:

//LDSYSPRM JOB 
//DSNUPROC EXEC PGM=TSTUTILB,PARM='HCOD,SH      '
//*
//SYSPRINT DD   SYSOUT=*
//UTPRINT  DD   SYSOUT=*
//SYSUDUMP DD   SYSOUT=*
//STPRIN01 DD   SYSOUT=*
//*COLSDISC DD   SYSOUT=* 
//* DISCARDDN CHKSDISC DISCARDS 0
//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.ULSYSPRM
//SYSIN    DD   *
LOAD DATA INDDN SYSREC LOG NO  REPLACE YES
 EBCDIC  CCSID(00037,00037,65534)
 INTO TABLE
 "SYSIBM".
 "SYSPARMS"
 WHEN(00001:00002) = X'0146'
 NUMRECS                 1274
 ( "SCHEMA"
  POSITION(  00003:00132) VARCHAR
 , "NAME"
  POSITION(  00133:00262) VARCHAR
 , "PARMNAME"
  POSITION(  00263:00392) VARCHAR
 , "ROWTYPE"
  POSITION(  00393:00393) CHAR(001)
 , "ORDINAL"
  POSITION(  00394:00395) SMALLINT
 , "TYPENAME"
  POSITION(  00396:00525) VARCHAR
 , "LENGTH"
  POSITION(  00526:00529) INTEGER
 , "SCALE"
  POSITION(  00530:00531) SMALLINT
 , "SUBTYPE"
  POSITION(  00532:00532) CHAR(001)
 , "CCSID"
  POSITION(  00533:00536) INTEGER
 , "ENCODING_SCHEME"
  POSITION(  00537:00537) CHAR(001)
 )
/*
//  
//*

Step 5: Create Foreign Keys for SQL Server catalog tables

To expedite the process of creating foreign keys for SQL Server catalog tables, Micro Focus provides the CREATECATALOGFK.txt file. This file contains SQL Server ALTER TABLE statements that enable foreign keys to be used properly with SQL Server. We recommended that you execute CREATECATALOGFK.txt from SQL Server Management Studio.

Step 6: Run the HCOSS Extract Schema tool against SQL Server

Create an ADO.NET connection to SQL Server using the HCOSS Manage Connections tool. Then, run the HCOSS Extract Schema tool:

From the command line:

Use the MFExtractSchemaInfo command with the DB2 argument set to your ADO.NET connection name. Set all other arguments the same as you would when running against DB2 for z/OS. For example:

MFExtractSchemaInfo DB2=HCODEMO; OUT=SCH81010; SCHEMA=DSN81010; TYPELOG=FILE

For complete information on running the Extract Schema tool from the command line, see MFExtractSchemaInfo command.

From the Extract Schema tool UI:

Follow the instructions in the topic To extract DB2 schema objects with the following exception:

• In step 2, instead of providing a DB2 connection, click SQL Server DSN.

  This turns off the Table Row Count and Both Estimate and Actual options. If the actual table row count is required, use the UI version of the Assess Data Migration tool.

Note: Neither an ADO.NET connection to DB2 for z/OS nor a Microsoft OLE DB2 Provider for DB2 are required.