Data Manager Red Book

This Data Manager Red Book defines best practices for its effective use in a test server environment.

Data Manager masks and subsets mainframe DB2 data, enabling you to move the transformed data into SQL Option for DB2, which is a common DBMS used in test server environments. Masking and/or reducing your mainframe DB2 data before you move it into Enterprise Test Server provides a robust and efficient environment for testing application using SQL.

Reading this Red Book prepares you to:
  • Mask and reduce mainframe DB2 data with Data Manager
  • Create sequential files that contain the masked and reduced data with Data Manager
  • Move the masked and reduced data into SQL Option for DB2
    • Initially moving the mainframe DB2 schema
    • Subsequently moving the actual data
Note: This Red Book gives specific guidance on how to accomplish several tasks using Data Manager and SQL Option for DB2. However, the procedures described are presented as examples only. We do not intend for you to perform these procedures yourself. For hands-on experience, see the Data Manager and SQL Option for DB2 documentation for step-by-step tutorials you can complete in their entirety.
  • Data Manager Architecture and Workflow
    Provides a high-level overview of the Data Manager underlying architecture and corresponding workflow, specifically for z/OS DB2 data stores.
  • Creating Test Data with Data Manager
    Describes masking and subsetting four mainframe DB2 tables, and the generation of transformed sequential files that can be moved into SQL Option for DB2.
  • Moving Data into SQL Option for DB2
    Describes the process of moving masked and subsetted data from Data Manager into Enterprise Test Server using SQL Option for DB2.
  • Appendix A: Schema JCL
    Example JCL to create a schema of mainframe test data in SQL Option for DB2.
  • Appendix B: Unload JCL
    Example JCL to run DSNUTILB UNLOAD with SQL Option for DB2, generating the necessary LOAD information.
  • Appendix C: Load JCL
    Example JCL to run DSNUTILB LOAD with SQL Option for DB2, loading test data into an XDB database.


Data Manager Architecture and Workflow

Provides a high-level overview of the Data Manager underlying architecture and corresponding workflow, specifically for z/OS DB2 data stores.

This series of topics provides a high-level overview of the Data Manager underlying architecture and corresponding workflow, specifically for z/OS DB2 data stores.

For more detailed information, see the Data Manager documentation in the Enterprise Test Server product.

  • Architecture
  • Workflow
Parent topic: Data Manager Red Book


Architecture

Data Manager is basically a Windows-based user interface that communicates with a z/OS engine.

The user interface is composed of three separate modules:
  • Data Builder
  • Data Masking
  • Data Subset Extraction

You can access these from Start > All Programs > Micro Focus Enterprise Test Server > Data Tools > Data Manager.

The engine runs on z/OS, and is a part of your DB2 environment. The engine is comprised of a knowledge base containing 73 DB2 tables, and a set of batch jobs running on z/OS.

The knowledge base contains both information about environment structure, as well as rules for masking and subsetting. The modules contained in Data Builder, Data Masking, and Data Subset Extraction running on the Windows platform interact with the knowledge base on z/OS via the Borland Database Engine (distributed with Enterprise Test Server) and IBM DB2 Connect, which is required as prerequisite third-party software that must be installed before using Data Manager.

Parent topic: Data Manager Architecture and Workflow


Workflow

Data Manager performs three main workflow activities:
Application Data Model Implementation
Initial implementation of the application model, comprised of the definition of structure and classification of the source data. This is the most time-consuming phase, and it is done only once at the beginning of the project. It is done using the Work with Jobs window in Data Builder, and is composed of the following phases:
  • Data Inventory Phase
  • Data Classification Phase
Application Data Model Update
Update of the application model, if required. Periodically, the information contained within the Data Manager structures should be synchronized with any changes that have been made within the source database. This is achieved by comparing the IBM system catalog on z/OS and the Data Manager internal representation of the catalog on Windows. This is done in the Life Cycle Phase using the Data Builder module.
Application Data Model Extraction
Extraction of data from source environment. This gets data from the source database and produces masked and reduced data in the same output format as if the tables were processed using the IBM DSNUTILB UNLOAD command. Depending on whether you have reduction rules or not, you perform these two phases:
  • Data Masking Phase, done using the Data Masking module
  • Data Subsetting Phase, done using the Data Subset Extraction module
Parent topic: Data Manager Architecture and Workflow


Creating Test Data with Data Manager

We highly recommend that you review the sample sessions and perform the tutorials contained in the Getting Started with z/OS Data Stores section of the Data Manager documentation available within Enterprise Test Server. These help you to quickly gain a concise understanding of how you can use Data Manager to mask and/or reduce mainframe data.

The following is based on the exercises presented in the Getting Started with z/OS Data Stores section. It gives specific guidance on how to mask and subset four mainframe DB2 tables, and then generate transformed sequential files that can be moved into SQL Option for DB2.

For the purposes of this discussion, assume that preliminary tasks of importing JCL job streams and creating DB2 tablespace and tables have already been done, and that the tables exist and are populated in the z/OS environment.
Note: For detailed information on how to perform these tasks, review the subsections Importing JCL Job Streams Into Your Mainframe Environment and Creating the DB2 Tablespace and Tables in the Getting Started with z/OS Data Stores section of your Data Manager documentation.
The table names are based on a small banking application:
Table Name | Description
CUSTOMER | Customer information
ACCOUNT | Account information
CCARD | Credit card information
OPERAT | Credit card operations information
The table definitions are:
CREATE TABLE DELGT.CUSTOMER
(COD_CUS                CHAR(10)            NOT NULL WITH DEFAULT,
 NAME                   CHAR(40)            NOT NULL WITH DEFAULT,
 SURNAME                CHAR(40)            NOT NULL WITH DEFAULT,
 DATE_OF_BIRTH          CHAR(08)            NOT NULL WITH DEFAULT,
 PLACE_OF_BIRTH         CHAR(50)            NOT NULL WITH DEFAULT)

CREATE TABLE DELGT.ACCOUNT
(COD_CUS                CHAR(10)            NOT NULL WITH DEFAULT,
 OFF_NUM                DECIMAL(5)          NOT NULL WITH DEFAULT,
 ACC_NUM                DECIMAL(10)         NOT NULL WITH DEFAULT,
 AMOUNT                 DECIMAL(12)         NOT NULL WITH DEFAULT)

CREATE,TABLE DELGT.CCARD
(OFF_NUM                DECIMAL(5)          NOT NULL WITH DEFAULT,
 ACC_NUM                DECIMAL(10)         NOT NULL WITH DEFAULT,
 CARD_TYPE              CHAR(1)             NOT NULL WITH DEFAULT,
 CARD_NUM               CHAR(16)            NOT NULL WITH DEFAULT,
 AVAILABILITY           DECIMAL(25)         NOT NULL WITH DEFAULT,
 EXP_DATE               CHAR(8)             NOT NULL WITH DEFAULT)

CREATE,TABLE DELGT.OPERAT
(CARD_TYPE              CHAR(1)             NOT NULL WITH DEFAULT,
 CARD_NUM               CHAR(16)            NOT NULL WITH DEFAULT,
 ACCOUNT_OPER           DECIMAL(25)         NOT NULL WITH DEFAULT,
 DATE_OPRT              CHAR(8)             NOT NULL WITH DEFAULT)

The following topics guide you through the procedure:

  • Allocate PDS Libraries and Launch Data Manager
  • Submit a Job
  • Create a Workspace and Groups
  • Data Inventory Phase
  • Data Classification Phase
  • Data Masking Phase
  • Data Subsetting Phase
Parent topic: Data Manager Red Book


Allocate PDS Libraries and Launch Data Manager

Our first task is to allocate PDS libraries to ISPF and launch the Data Manager Main Menu in an ISPF environment. To do this, start an ISPF command prompt; then execute the following command:

TSO EXEC 'URADAR.TS21WRK.CLIST(MFDATA)'

In this example, URADAR.TS21WRK is the name of the PDS library that contains the MFDATA member.

This launches the Data Manager Main Menu:

Parent topic: Creating Test Data with Data Manager


Submit a Job

Before starting any activity, you need to starting the product clist on z/OS. To do this, set up the job submitter for Data Manager on the mainframe, and submit the job.

From the Data Manager Main Menu, enter 1 to Submit Client Scheduled Job:

Submit Client Scheduled Job

Enter appropriate custom parameters for your installation; then submit BURSUBM.

BURSUBM is a job scheduler that checks which jobs have been scheduled via input from the Work with Jobs window in Data Builder. It submits the appropriate corresponding batch job on the mainframe.

For more information, see Appendix B. Starting Data Manager for z/OS and Appendix C. Submission Function in your Data Manager Process for z/OS Guide.

Parent topic: Creating Test Data with Data Manager


Create a Workspace and Groups

After submitting the job, use the Data Builder module to create a Machine and a Company. Machine and Company are entities used internally by Data Manager to classify the workspace that is in the scope of the masking and subsetting project. In Data Manager, a Machine/Company combination corresponds to a workspace.

Use the Data Subset Extraction module to create a Group. A Group is a label marking different methods to establish a set or a classification. A method is simply a set of rules applied to required tables, creating a test environment. In short, a method corresponds typically to a test environment. You can define different methods for the same table, creating different subsetted tables to test with different rules, but all starting from the same workspace.

See the Getting Started with z/OS Data Stores section in your Data Manager documentation for more information on workspaces and groups.

Parent topic: Creating Test Data with Data Manager


Data Inventory Phase

You must have a workspace and a group created before starting the data inventory phase.

To catalog DB2 tables, Data Manager requires information about the structure of each table. The information is provided in a flat file of a specific format as described in the Data Model Guide. You generate the flat file by executing the CUINFIL clist from a Data Manager command prompt.

The CUINFIL clist populates the flat file interface from a list of tables to be processed. In the example that follows, the clist is AA201.DBMANAGER.LISTDB2O, and the list of tables to be processed is AA201.DBMANAGER.LISTDB2.

Now the file interface can be populated, starting from the table list.

You can now populate the Data Manager knowledge base with the required information. From the Work with Jobs window, create a new job and provide a Machine ID and a Company ID (in this example, both are TESTSERVER). Then select and Apply the BURLFIL job.

Work with Jobs

On the Secondary Options dialog box, provide the name of the interface flat file that was specified as the Data Set Name Out when you loaded sequential files. In this case, the name is AA201.DMANAGER.LISTDB2O.

Click OK to submit the BURLFIL batch job of z/OS to the BURSUBM scheduler job. See the Submit a Job topic for information on the BURSUBM scheduler job.

When the batch job is finished, you see the following loaded tables in the Data Builder Work with Data Stores dialog box.

Work with Data Stores

Double-click the ACCOUNT table to see its columns and definitions.

Work with Data Store

Parent topic: Creating Test Data with Data Manager


Data Classification Phase

Now that the table structures containing technical information have been loaded, you can add classes containing application information that drives the data masking and data reduction process.

You assign classes as described in detail in your Data Manager documentation. In short, you can assign classes using:
  • The graphical interface of Data Manager to multi-select and assign classes
  • An external data dictionary
  • Physical or logical referential integrity, assuming that columns referenced in the same relationship share the same class
  • Column contents statistical distribution, assuming that columns having the same statistical distribution of their contents share the same class

The following examples use the data dictionary approach for defining classes for data masking. For data reduction, we use classes for referential integrity, showing both the two interfaces and the steps needed to execute the classification phase in the two cases. This shows you how to load classes from an external dictionary, AA201.DMANAGER.CLALIST, containing masking classes, and how to load classes from a referential integrity interface, AA201.DMANAGER.REFINTEG, containing subsetting classes.

Data Masking Class Assignment Example (using Data Dictionary)
1-MVS

The data masking class basically states that the columns NAME, SURNAME and ADDRESS from the CUSTOMER table are to be masked.

Data Reduction Example (using Referential Integrity)
1-MVS

This example shows acknowledgement of a reduction rule, also known as a method. This method is based on the Referential Integrity rules between the tables CUSTOMER and ACCOUNT, ACCOUNT and CCARD, and CCARD and OPERAT as shown in this simple data model:

The table CUSTOMER and ACCOUNT are linked by a relationship based on the COD_CUS column. ACCOUNT is related to CCARD with multiple keys OFF_NUM and ACC_NUM. And, CCARD and OPERAT are linked with the keys CARD_TYPE and CARD_NUM.

You can generate both interfaces from an Excel spreadsheet by exporting them using the proper separator. You can alternatively generate the referential integrity from the DB2 catalog itself. Or, using a tool provided with Data Manager, you can generate the referential integrity from a DDL statement containing SQL commands that establish the referential integrity. You can generate this type of DDL statement using a data modeling tool such as the Erwin Data Modeler.

From the Work with Jobs window, you can run the load classification from the dictionary by specifying the BURECLR job:

Data Masking BURECLR Job Submission from Work with Jobs
Work with Jobs

Then load the corresponding input file necessary for BURECLR processing:

In the same way, you can submit the load classification from referential integrity by specifying the BURDDUR job:

Data Reduction BURDDUR Job submission from Work with Jobs
Work with Jobs

Then load the corresponding input file:

Data Builder - Work with Data Elements

After completing both processes, Data Manager proposes an "estimated class" for confirmation. Once confirmed, an estimated class becomes an "assigned class" and is then available for subsetting and masking. To create an assigned class, use the Data Builder Work with Data Elements dialog box, which allows column-level operation including the confirmation of class as shown here:

Work with Data Elements

The List of Data Elements shows the data elements contained within the tables that have been processed by the Data Masking and Data Reduction classes. At this point, the classes have not yet been confirmed. To confirm the classes, click Confirm Class. Once confirmed, this populates the Assigned Class column with the relevant class.

Parent topic: Creating Test Data with Data Manager


Data Masking Phase

The first step of the Data Masking phase is to enable masking on your tables for which you have defined masking rules. You can do this on a table-by-table basis, but it is much easier to do it globally.

Start the Data Masking module, and click Tool > Enable Masking; then click Yes to enable global masking.

Next, you specify output file information. In this example, the output of the masking process is a flat file named AA201.OUT.CUSTOMER.

Data Changer properties

Submit the BTEALSR job to generate the JCL necessary to allocate the output data store defined on the Data Changer properties dialog box. All jobs, including masking and subsetting jobs, are generated from the Data Builder interface, from the Work with Jobs window. For example, to submit the BTEALSR job, select BTEALSR from the List of Jobs, and then click Apply:

Work with Jobs

This creates JCL streams in URADAR.UTILITY.JCL, named AL1001 and AL2002, for allocation of output data sets. In this case, the output data sets are in AA201.OUT.CUSTOMER:

Execute the JCL streams outside of Data Manager. Once executed, you can execute masking. For example, from Work with Jobs, use the BDCCHGR job to create your flat file that contains masked data.

Work with Jobs

At the end of this process, the file AA201.OUT.CUSTOMER is populated with masked data coming from production table DELGT.CUSTOMER. This masked data is in the same format as masked data created by the UNLOAD utility executed by DSNUTILB on the mainframe.

Parent topic: Creating Test Data with Data Manager


Data Subsetting Phase

To subset a set of tables, Data Manager uses the concept of a method, which is defined as a set of tables to be reduced in a consistent way. One method corresponds to one test environment.

A method can be created with a Wizard, or using a referential integrity model. The following example shows a referential integrity model. For details of using the Wizard approach, see your Data Manager documentation.

From the Work with Jobs window in the Data Builder module, submit the BTEDDUR job:

Work with Jobs

We require two parameters for this job in addition to the interface parameter, which is the same parameter as that used for class assignment. The two additional parameters are:
  • The name to be assigned to the method
  • The class assigned to the starting point, which is the beginning of the reduction model

Secondary Options 5

At the end of this process, Data Builder creates the method. This method appears on the Work with Method window of the Data Subset Extraction module:

Work with Method

This method requires two additional operations:
  • Specify the initial filter. This is the rule applied to the data contained within the tables. In this example, the filter is set to reduce the data to COD_CUS containing a value of 00000000001.

    Selection class / Filter properties

  • Specify output flat file names:

Next, synchronize Data Manager with the DB2 catalog. This updates any index information, enabling Data Manager queries to run optimally. This process includes:
  • On z/OS, generate an interface from the DB2 catalog using the information shown here:

  • From the Work with Jobs window in Data Builder, submit the BURSYN job, which is a Catalog Synchronization job.

    Work with Jobs

  • Provide the filename in the Secondary Options dialog box:

At this point, you can generate and execute allocation jobs. Specify the method name on the Secondary Options dialog box; then click OK:

Next, execute the BTESBMR job, which generates the reduced test environment.
Note: If masking classes are selected, masking is performed automatically without having to first enabling the masking classes.

Work with Jobs

Specify the method on the Secondary Options dialog box:

At the end of this process, the file AA201.OUT.CUSTOMER is populated with reduced and masked data coming from the production table DELGT.CUSTOMER. This reduced and masked data is in the same format as reduced and masked data created by the UNLOAD utility executed by DSNUTILB on the mainframe.

Parent topic: Creating Test Data with Data Manager


Moving Data into SQL Option for DB2

SQL Option for DB2 is a relational DBMS designed to mimic mainframe DB2 in Windows environments. In addition to providing support for your COBOL environment, SQL Option for DB2 provides support for the batch data migration environment, including the batch utilities DSNUTILB and DSNTEP2. We suggest that you use these utilities when moving test data from Data Manager into Enterprise Test Server.

This is the basic workflow for moving your test data:
  • Use the SQL Option for DB2 Migrate tool to move the schema for your test data into a file that can be used with DSNTEP2, or move your schema directly into your XDB location
  • Use the MFA Drag and Drop Facility to move your test data from the mainframe to your PC, via the user interface or the batch Auto-catalog function
  • Use the SQL Option for DB2 DSNUTILB utility to import your test data into an XDB database

This Red Book assumes that you have already successfully set up XDB Link for connectivity to the mainframe, and have your XDB server up and running. See your SQL Option for DB2 documentation for additional details.

  • Moving a Test Data Schema into SQL Option for DB2
  • Moving Test Data into SQL Option for DB2
Parent topic: Data Manager Red Book


Moving a Test Data Schema into SQL Option for DB2

Use the SQL Option for DB2 Migrate tool to move the schema for your test data into a file that can be used with DSNTEP2, or that can be migrated directly into your XDB location.

Before starting, we suggest that you modify some of your client-side settings via the Client Options tool so that you connect directly to the mainframe location when using the Migrate tool.

To start the Client Options tool, click Start > All Programs > Micro Focus Enterprise Test Server > Data Tools > SQL Option for DB2 > Configuration > Client Options.

On the Connect tab, type the name of your mainframe location into the Locations field, and your mainframe user ID into the AuthID field.

On the Security tab, be sure that Client Security is checked.

To start the Migrate tool, click Start > All Programs > Micro Focus Enterprise Test Server > Data Tools > SQL Option for DB2 > Client > Migrate. The Migrate tool prompts you for your mainframe password.

After supplying the mainframe password, click OK. This starts the Migrate tool:

Before migrating a schema, decide how you want to do the migration. You can create the schema directly in your XDB location, or save the schema as a DDL file you can use as input to the batch DSNTEP2 utility. You also might want to migrate only a portion of the mainframe schema to your Windows environment; for example, privilege statements (DCL). To set these options, click Options > Migrate from the SQL Option for DB2 main window.

This Migrate Options dialog box is set to generate SQL statements and save them in the file C:\downloads\tom.sql3. It is not set up to execute the migration immediately.

To set default options, click Options > Table/Query Defaults:

In this example, the Privileges box is left unchecked. This means that GRANT privilege statements are not generated. Click OK.

From the Catalog Browser, select the tables you want to migrate:

Drag and drop those tables into the Source Location window:

Once the tables are in your Source Location window, complete the To field with a target location. In this example, we are not actually placing the schema into destination location; therefore you can select any location.

Click Commands > Run:

Click OK. This populates the .sql3 file with DDL statements such as these:
CREATE DATABASE DBURSVIL BUFFERPOOL BP2 CCSID EBCDIC;
CREATE TABLESPACE DETESTLG IN DBURSVIL
    BUFFERPOOL BP2
    CCSID EBCDIC;
CREATE TABLE DELGT.ACCOUNT
(
     COD_CUS                        CHAR(10) NOT NULL WITH DEFAULT,
     OFF_NUM                        DECIMAL(5,0) NOT NULL WITH DEFAULT,
     ACC_NUM                        DECIMAL(10,0) NOT NULL WITH DEFAULT,
     AMOUNT                         DECIMAL(12,0) NOT NULL WITH DEFAULT
)  IN  DBURSVIL.DETESTLG AUDIT NONE  DATA CAPTURE NONE ;
CREATE TABLESPACE DETESTLG IN DBURSVIL
    BUFFERPOOL BP2
    CCSID EBCDIC;
CREATE TABLE DELGT.CCARD
(
     OFF_NUM                        DECIMAL(5,0) NOT NULL WITH DEFAULT,
     ACC_NUM                        DECIMAL(10,0) NOT NULL WITH DEFAULT,
     CARD_TYPE                      CHAR(1) NOT NULL WITH DEFAULT,
     CARD_NUM                       CHAR(16) NOT NULL WITH DEFAULT,
     AVAILABILITY                   DECIMAL(25,0) NOT NULL WITH DEFAULT,
     EXP_DATE                       CHAR(8) NOT NULL WITH DEFAULT
)  IN  DBURSVIL.DETESTLG AUDIT NONE  DATA CAPTURE NONE ;

You can use the .sql3 file as input to the SQL Option for DB2 DSNTEP2 utility, and run it in batch mode using DSNUTILB LOAD and UNLOAD steps. We recommend running schema statements in batch mode simply because it provides an easy way to recreate your test data.

Alternatively, you can migrate the schema directly to an XDB target location. To do this, click Commands > Run. Check Execute Migrate, and uncheck Generate SQL. This combination migrates your selected mainframe schema directly into the To location specified.

You can also pull all the tables in a particular mainframe DB2 database rather than selecting them from the Catalog Browser. To do this, click Commands > Migrate Database; then select the database you want to migrate:
Migrate Database

Click Execute.

This option can migrate directly to the target XDB location, or generate the input file to the DSNTEP2 utility, depending on the options you set.

See Appendix A: Schema JCL for an example of a schema JCL stream that uses DSNTEP2 to load schema statements created via the Migrate tool into your XDB database.

Parent topic: Moving Data into SQL Option for DB2


Moving Test Data into SQL Option for DB2

Data Manager creates flat sequential files of masked and/or subsetted data. These files can then be migrated from the mainframe and subsequently used as input to the SQL Option for DB2 DSNUTILB LOAD facility.

To move your Data Manager test data to the PC, use the MFA Drag and Drop facility. You can use the GUI to do this, but for the purposes of this Red Book, we import the mainframe files using Auto-catalog.

From the Enterprise Test Server 32-bit command prompt, you can import and auto-catalog your Data Manager output files using the mfdas import command. For example:
mfdas import c:\data\CUSTOMER.DAT from QSAM AA201.OUT.CUSTOMER
/catloc=c:\xdbes\catalog.dat

Where AA201.out.CUSTOMER is the dataset name on the mainframe and the PC, and c:\data\CUSTOMER.DAT is actual path and filename of the imported file on your PC.

Similar to the suggestion that you place your generated schema into a DSNTEP2 step, we suggest using the mfdas import here for the same reason – to enable you to easily repeat the steps to regenerate your test data as many times as necessary.

It is important that the LOAD control card in your JCL takes into account that files created by Data Manager for DSNUTILB:
  • Contain no header records
  • Have padded varchar data
The easiest way to generate the correct LOAD control card is to execute a DSNUTILB UNLOAD on the table in question. The DSNUTILB UNLOAD creates the LOAD control card that you can use later with SQL Option for DB2 in SYSPUNCH.
Note: When you run this DSNUTILB UNLOAD, you do not need to actually create the extract file for the unloaded table.

You can run the DSNUTILB UNLOAD on the mainframe, or you can run it using SQL Option for DB2 after after defining the schema. We recommend that you use SQL Option for DB2 to minimize your mainframe resources. For example, the UNLOAD statement could be similar to:

UNLOAD DATA FROM TABLE DELGT.CUSTOMER HEADER NONE

Once you have created your LOAD card in SYSPUNCH, you can use it as input to the SQL Option for DB2 DSNUTILB LOAD in a subsequent JCL step.

See Appendix B: Unload JCL and Appendix C: Load JCL for examples of UNLOAD and LOAD JCL.

With the JCL provided in the Appendices, and the MFA Drag and Drop batch Auto-config feature, you are prepared to not only set up test data in SQL Option for DB2, but also to recreate your test data at will.

Parent topic: Moving Data into SQL Option for DB2


Appendix A: Schema JCL

Example JCL to create a schema of mainframe test data in SQL Option for DB2.
//MFIXDBA  JOB (DB2SQL),'DB2 BATCH SQL',REGION=0M,CLASS=A,MSGCLASS=X
//*
//SCHEMA   EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN  DD *
 DSN SYSTEM(DB9R)
 RUN  PROGRAM(DSNTEP2) -
      PLAN(DSNTEP91) -
      LIB('DSN910.RUNLIB.LOAD')
 END
/*
//SYSIN    DD *

SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1;

DROP DATABASE DBURSVIL;
COMMIT WORK;

CREATE DATABASE DBURSVIL BUFFERPOOL BP2 CCSID EBCDIC;
COMMIT WORK;

CREATE TABLESPACE DETESTLG IN DBURSVIL
    BUFFERPOOL BP2
    CCSID EBCDIC;

CREATE TABLE DELGT.ACCOUNT
(
     COD_CUS                        CHAR(10) NOT NULL WITH DEFAULT,
     OFF_NUM                        DECIMAL(5,0) NOT NULL WITH DEFAULT,
     ACC_NUM                        DECIMAL(10,0) NOT NULL WITH DEFAULT,
     AMOUNT                         DECIMAL(12,0) NOT NULL WITH DEFAULT
)  IN  DBURSVIL.DETESTLG AUDIT NONE  DATA CAPTURE NONE ;

CREATE TABLESPACE DETESTLG IN DBURSVIL
    BUFFERPOOL BP2
    CCSID EBCDIC;

CREATE TABLE DELGT.CCARD
(
     OFF_NUM                        DECIMAL(5,0) NOT NULL WITH DEFAULT,
     ACC_NUM                        DECIMAL(10,0) NOT NULL WITH DEFAULT,
     CARD_TYPE                      CHAR(1) NOT NULL WITH DEFAULT,
     CARD_NUM                       CHAR(16) NOT NULL WITH DEFAULT,
     AVAILABILITY                   DECIMAL(25,0) NOT NULL WITH DEFAULT,
     EXP_DATE                       CHAR(8) NOT NULL WITH DEFAULT
)  IN  DBURSVIL.DETESTLG AUDIT NONE  DATA CAPTURE NONE ;

CREATE TABLESPACE DETESTLG IN DBURSVIL
    BUFFERPOOL BP2
    CCSID EBCDIC;

CREATE TABLE DELGT.CUSTOMER
(
     COD_CUS                        CHAR(10) NOT NULL WITH DEFAULT,
     NAME                           CHAR(40) NOT NULL WITH DEFAULT,
     SURNAME                        CHAR(40) NOT NULL WITH DEFAULT,
     DATE_OF_BIRTH                  CHAR(8) NOT NULL WITH DEFAULT,
     PLACE_OF_BIRTH                 CHAR(50) NOT NULL WITH DEFAULT,
     ADDRESS                        CHAR(50) NOT NULL WITH DEFAULT
)  IN  DBURSVIL.DETESTLG AUDIT NONE  DATA CAPTURE NONE ;

CREATE TABLESPACE DETESTLG IN DBURSVIL
    BUFFERPOOL BP2
    CCSID EBCDIC;

CREATE TABLE DELGT.OPERAT
(
     CARD_TYPE                      CHAR(1) NOT NULL WITH DEFAULT,
     CARD_NUM                       CHAR(16) NOT NULL WITH DEFAULT,
     ACCOUNT_OPER                   DECIMAL(25,0) NOT NULL WITH DEFAULT,
     DATE_OPRT                      CHAR(8) NOT NULL WITH DEFAULT
)  IN  DBURSVIL.DETESTLG AUDIT NONE  DATA CAPTURE NONE ;

CREATE INDEX DELGT.ACCOUNTX1
    ON DELGT.ACCOUNT(
        COD_CUS
 )
 FREEPAGE 0
 PCTFREE 10
 BUFFERPOOL BP0
 CLOSE YES ;

CREATE INDEX DELGT.CCARDX1
    ON DELGT.CCARD(
        OFF_NUM,ACC_NUM
 )
 FREEPAGE 0
 PCTFREE 10
 BUFFERPOOL BP0
 CLOSE YES ;

CREATE UNIQUE INDEX DELGT.CUSTOMERX1
    ON DELGT.CUSTOMER(
        COD_CUS
 )
 FREEPAGE 0
 PCTFREE 10
 BUFFERPOOL BP0
 CLOSE YES ;

CREATE INDEX DELGT.OPERATX1
    ON DELGT.OPERAT(
        CARD_TYPE,CARD_NUM
 )
 FREEPAGE 0
 PCTFREE 10
 BUFFERPOOL BP0
 CLOSE YES ;

/*
//
Parent topic: Data Manager Red Book


Appendix B: Unload JCL

Example JCL to run DSNUTILB UNLOAD with SQL Option for DB2, generating the necessary LOAD information.
/MFIXDBA JOB (DSNUTILB),'DSNUTILB',REGION=0M,CLASS=A,MSGCLASS=X
//*
//*
//*
//DELETE  EXEC PGM=IDCAMS
//SYSPRINT DD  SYSOUT=*
//SYSIN    DD  *
   DELETE  (MFIXDB.ACCOUNT.SYSPUNCH)
   IF MAXCC = 8 THEN SET MAXCC = 0
//*
//*
//*
//UNLOAD   EXEC PGM=DSNUTILB,PARM='DB9R,',REGION=0K
//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)
//SYSPUNCH DD  DSN=MFIXDB.ACCOUNT.SYSPUNCH,
//             UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(MOD,CATLG)
//SYSREC   DD DUMMY
//SYSIN    DD  *
UNLOAD DATA
    FROM TABLE DELGT.CUSTOMER
    HEADER NONE
    PUNCHDDN SYSPUNCH
    UNLDDN   SYSREC
/*
//
Parent topic: Data Manager Red Book


Appendix C: Load JCL

Example JCL to run DSNUTILB LOAD with SQL Option for DB2, loading test data into an XDB database.
//MFIXDBA JOB (DSNUTILB),'DSNUTILB',REGION=0M,CLASS=A,MSGCLASS=X
//*
//*
//*
//LOAD     EXEC PGM=DSNUTILB,PARM='DB9R,',REGION=0K
//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)
//SYSPUNCH DD  DSN=MFIXDB.ACCOUNT.SYSPUNCH,
//             UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(MOD,CATLG)
//SYSREC   DD DSN=AA201.OUT.CUSTOMER,DISP=OLD
//SYSIN    DD DSN=MFIXDB.ACCOUNT.SYSPUNCH,DISP=OLD
//
Parent topic: Data Manager Red Book