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.