Once you have created SQL tables, you need data to test your applications. You may also need to distribute data to other PCs or send data back to the mainframe. IBM and other vendors provide products to do this. However, if you have not installed one of these products, moving data around can be a problem.
Host Compatibility Option supplies you with an Export Data tool which you can combine with DB2 Connect, IBM's DRDA gateway, giving you a relatively inexpensive way of moving data between mainframe databases and PCs.
The Export Data tool includes the following features:
IBM provides export capabilities within Control Center but does not support DBMAUI or DSNTIAUL formats.
The Export Data tool (see Figure 11-1) appears when you select HCO > Export Data on the IDE Tools menu.
Figure 11-1: Export Data Tool
The following elements make up the Export Data window:
The following list shows the buttons in the toolbar and the functions that they invoke:
|Connect to the database selected from drop-down list|
|Export data to file from the table selected from the list box. In the Save Export To window specify the filename where you want to save data (see Figure 11-5).|
|Display the online HCO User's Guide which is a hypertext file that describes how the Export Data tool works.|
The menu bar is an alternate way of invoking Export Data functions. The menu bar is composed of three menus: Database, Options and Help.
You use the Database menu to connect to a database, export tables or to exit the tool. You can also display this menu by right-clicking with the mouse anywhere in the Export Data window (see Figure 11-1).
|Export table||Export data from the table selected from the list box|
|Connect||Connect to the database selected from the drop-down list|
|Restrict list||Restrict tables included in the list box when connecting to a database. See the section Restricting Tables in List for more details.|
|Disconnect||Disconnect from the database to which you are currently connected|
|Exit||Exit Export Data tool|
You can override options set in the configuration file by selecting the Options menu. These options are pre-defined. See the chapter Setting INI Options for more details. The settings depend on your current environment and what you have set as defaults.
|Display log||Display the log file after export command completes|
|Format - DSNTIAUL||Export data in DSNTIAUL format. This is a format that some mainframe products support and is written out in EBCDIC.|
|Format - DBMAUI||Export data in DBMAUI format. This is a format that some mainframe products support similar to ANSI delimited.|
|Format - IXF||Export data in IBM integrated exchange format (PC version). This format stores table and index definitions besides the data which makes it ideal for transferring data between PCs and other PCs or the mainframe.|
|Format - DEL||Export data in ANSI delimited format. This is a common format for exchanging data with other PC-based products.|
You can specify the name of the log file where export messages are written.You specify the default-name for the log file in the configuration file. See the chapter Setting INI Options for more details. If you use the same filename you overwrite the log file. If you have Display Log checked, the log is displayed in the HCO Log tab of the IDE (see Figure 11-2). You also need to have Output selected on the View menu of the IDE for this to be displayed.
Figure 11-2: HCO Log
The Export Data tool automatically connects to the database specified in the Mainframe Express project when you first invoke the tool unless you have disabled this feature by changing the configuration file.
To connect to a different database from which to export data:
The list box (see Figure 11-1) displays a list of tables to select from if the connection was successful.
The list box in Export Data tool has a limit of 200 tables to include when you connect to a database. If the database contains a large number of tables or if you only want to include a specific set of tables, you can restrict the list. To do this:
The Restrict List window appears (see Figure 11-3 ).
Figure 11-3: Restrict Tables in List
You can restrict the list using all or part of a schema name, all or part of a table name or both. When you have made your entries, click Restrict. In the example above, the list is restricted to only those tables that begin with DEMO.
To export data from a table:
Figure 11-4: Select Table Window
The Save Export To window appears (see Figure 11-5). This window looks different on different operating systems but the functionality remains the same.
Figure 11-5: Save Export To Window
A default filename is built based on the table selected and the export format. You can change the filename or accept the default. When ready, click Save. The data is then exported.
Host Compatibility Option exports data in DBMAUI format with the following default delimiters:
You may need to override these defaults if the data that you are exporting contains one of these characters. To override these defaults, select Format > DBMAUI on the Options menu. The Set Export - DBMAUI window appears (see Figure 11-6).
Figure 11-6: Set Export - DBMAUI Window
To change options, select the character(s) that you want to use from the dropdown lists and click Set.
Host Compatibility Option exports data in delimited format with the following default delimiters:
You can also specify that dates are exported in ISO format and that numeric fields are not exported with plus signs.
You may need to override these defaults if the data that you are exporting contains one of these characters. To override these defaults, select Format > DEL on the Options menu. The Set Export - Delimited window appears (see Figure 11-7).
To change options:
Figure 11-7: Set Export - Delimited Window
The Export Data tool has the following limitations:
If you need to restrict rows and/or columns exported, you can use the IBM command line processor, DB2 or the IBM Control Center to export data. See IBM DB2 Command Reference for more details on how to use the IBM Control Center.
You can also run the Export Data tool as a batch function. The command line syntax to run the tool is:
MFECL /HCOEXPT projectname TBL=table FILE=file FMT=format LOG=logfile CHARDEL=char COLDEL=char ROWDEL=char DATE=dateformat CRLF=linefeed PLUS=sign
There must be at least one space before each parameter. There must be an "=" between some parameters and their values. See examples for specific syntax.
||Parameter to invoke Export Data command line processor. Parameter is positional.|
||Name of Mainframe Express project. Export tool obtains the database-name to connect to from the project.|
||Name of table to be exported. Can include both schema and table name.|
||Filename to export data to.|
You can omit optional parameters. You should set the value that you normally want to use in the configuration file and then use the following entries to override defaults only when needed. See the chapter Setting INI Options for more details.
||Format in which to export data. Valid values are IXF, DEL, DBMAUI and DSNTIAUL. You can use DBM for DBMAUI and DSN for DSNTIAUL.|
||Filename to write processing messages to. If this parameter is not specified, a default log filename is created from a combination of the drive\folder specified in the Mainframe Express project and the Host Compatibility Option configuration file.|
||Delimiter used to enclose character data when export format DEL specified. Default is double quotes (").|
||Delimiter used to indicate the end of column data when export format DEL or DBMAUI specified. Default is comma (,) for DEL and x"02" for DBMAUI.|
||Delimiter used to indicate the end of a row when export format DBMAUI specified. Default is semicolon (;).|
||Format to export date fields in if exporting data in DBMAUI, DSNTIAUL or DEL format. Default is format for your country. Valid values are ISO, USA, EUR and JIS. ISO is the only valid option for DEL format.|
||Generate a carriage return and line feed at end of each row when generating data in DBMAUI format. Valid values are "Y" for yes to generate CRLF or "N" to generate line feed only at the end of each row. Default is "Y".|
||Generate positive numeric data with plus signs when exporting data in DEL format. Valid values are "Y" for yes to generate plus signs or "N" to not generate plus signs. Default is "Y".|
Example 1 exports table DEMO.ITEMS from DB2DEMO database specified in Mainframe Express project hcodemo located in d:\mfuser\projects to the file c:\export\items.dbm. Log file, delimiters and DBMAUI format are defaults.
MFECL /HCOEXPT D:\MFUSER\PROJECTS\HCODEMO.MVP FILE=C:\EXPORT\ITEMS.DBM TBL=DEMO.ITEMS
Example 2 exports table DEMO.ITEMS from DB2DEMO database specified in Mainframe Express project hcodemo located in d:\mfuser\projects to file c:\export\items.ixf using format IXF. Log file uses defaults set in the configuration file.
MFECL /HCOEXPT D:\MFUSER\PROJECTS\HCODEMO.MVP FMT=IXF FILE=C:\EXPORT\ITEMS.IXF TBL=DEMO.ITEMS
In Example 3, the table DEMO.ITEMS is exported from DB2DEMO database specified in Mainframe Express project hcodemo located in d:\mfuser\projects to file c:\export\items.del using format DEL and log file export.log in the current folder. Column delimiter and decimal sign are defaults. The character delimiter is a single quote (') and the date format is ISO.
MFECL /HCOEXPT D:\MFUSER\PROJECTS\HCODEMO.MVP FILE=C:\EXPORT\ITEMS.DEL TBL=DEMO.ITEMS LOG=EXPORT.LOG DATE=ISO CHARDEL=' FMT=DEL
In Example 4, the table DEMO.ITEMS is exported from DB2DEMO database specified in Mainframe Express project hcodemo located in d:\mfuser\projects to file c:\export\items.dsn using format DSNTIAUL and log file export.log in the current folder. The date format is ISO.
MFECL /HCOEXPT D:\MFUSER\PROJECTS\HCODEMO.MVP FILE=C:\EXPORT\ITEMS.DSN TBL=DEMO.ITEMS LOG=EXPORT.LOG DATE=ISO FMT=DSN
Copyright © 1999 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.