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:

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:

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.