DCLGEN

Importing Data

Chapter 11: Exporting Data

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.

11.1 The Export Data Tool

The Export Data tool includes the following features:

Log is displayed in HCO Log tab of IDE
Default is for Export Data tool to connect automatically to the database specified in your Mainframe Express project

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:

Menu bar
Toolbar
Status line
Drop-down list of available databases to connect to
Entry field for log filename
List box of tables from which you can export data

11.2 The Toolbar

The following list shows the buttons in the toolbar and the functions that they invoke:

Button	Function
	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.

11.3 The Menu Bar

The menu bar is an alternate way of invoking Export Data functions. The menu bar is composed of three menus: Database, Options and Help.

11.3.1 The Database Menu

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).

Menu Option	Function
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

11.3.2 The Options Menu

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.

Menu Option	Function
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.

11.4 Log File Entry Field

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

11.5 Connecting to Database

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:

Select the database to which you want to connect from the dropdown list
Click on Connect or select Connect from the Database menu

The list box (see Figure 11-1) displays a list of tables to select from if the connection was successful.

11.6 Restricting Tables in List

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:

Select the database that you want to connect to from the dropdown list
Select Restrict list on the Database menu

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.

11.7 Exporting Data

To export data from a table:

Change any export defaults via Options menu (see Figure 11-4)
Connect to database
Select a table from the list box
Click on File or select Export table on the Database menu (see Figure 11-4)

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.

11.8 Overriding Defaults - DBMAUI

Host Compatibility Option exports data in DBMAUI format with the following default delimiters:

x"02" is used as a column delimiter
A semicolon (";") is used as a row delimiter
A carriage return - line feed (CRLF) is generated at end of each row

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.

11.9 Overriding Defaults - DEL

Host Compatibility Option exports data in delimited format with the following default delimiters:

A comma (",") is used as a column delimiter
Double quotes (") are used to enclose character data

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:

Select the character(s) that you want to use from the drop-down lists
Check the required options
Click Set

Figure 11-7: Set Export - Delimited Window

11.10 Limitations

The Export Data tool has the following limitations:

All columns and rows in a table are exported. There is no ability in this tool to limit the data or rows exported.
Export formats WSF (Lotus worksheet) and IXF (mainframe) are not supported
IXF and DEL files are exported using IBM's EXPORT application programming interface. Some options of the API are not supported. See IBM DB2 Command Reference for list of all available options.
DMAUI and DSNTIAUL are exported using a SELECT statement, so large tables may take a while to process
LOB data types are not supported in DSNTIAUL or DBMAUI format
LOB data types are generated as part of file in IXF and DEL formats. See IBM DB2 Command Reference for other 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.

11.11 Command Line Interface

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.

11.11.1 Required Parameters

Parameter	Description
`/HCOEXPT`	Parameter to invoke Export Data command line processor. Parameter is positional.
`projectname`	Name of Mainframe Express project. Export tool obtains the database-name to connect to from the project.
`TBL=table`	Name of table to be exported. Can include both schema and table name.
`FILE=file name`	Filename to export data to.

11.11.2 Optional Parameters

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.

Parameter	Description
`FMT=format`	Format in which to export data. Valid values are IXF, DEL, DBMAUI and DSNTIAUL. You can use DBM for DBMAUI and DSN for DSNTIAUL.
`LOG=logfile`	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.
`CHARDEL=char`	Delimiter used to enclose character data when export format DEL specified. Default is double quotes (").
`COLDEL=char`	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.
`ROWDEL=char`	Delimiter used to indicate the end of a row when export format DBMAUI specified. Default is semicolon (;).
`DATE=dateformat`	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.
`CRLF=linefeed`	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".
`PLUS=sign`	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".

11.12 Examples

Example 1

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

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

Example 3

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

Example 4

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

DCLGEN

Importing Data