Chapter 7: 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 to 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 that you can combine with DB2 Connect, IBM's DRDA gateway, giving you a relatively inexpensive way of moving data between mainframe databases and PCs.

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.
DSNTIAUL is exported using a SELECT statement, so large tables may take a while to process
LOB data types are not supported in DSNTIAUL format
LOB data types are generated as part of file in IXF and DEL formats. See IBM's 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's DB2 Command Reference for more details on how to use the IBM Control Center.

Command Line Interface

You can also run the Export Data tool as a batch function. The command line syntax to run the tool is:

mfhco export db=databasename tbl=table file=filename
    [fmt=format] [log=logfile] [chardel=chardelimiter] [coldel=coldelimiter]
       [date=dateformat] [plus=sign]

There must be at least one space before each parameter. There must be an equal sign (=) between some parameters and their values. If you omit parameters that have a default value, Host Compatibility Option uses the default value. See Examples for specific syntax.

Parameters

export	Parameter to invoke Export Data command line processor. Parameter is positional.
databasename	Name of the databae to connect to.
table	Name of table to be exported. Can include both schema and table name.
filename	Filename to export data to.
format	Format in which to export data. Valid values are IXF, DEL, and DSNTIAUL. You can use DSN for DSNTIAUL.
logfile	File to write processing messages to. If this parameter is not specified, a logfile with a default logfile name is created using the project name.
chardelimiter	Delimiter used to enclose character data when export format DEL specified. Default is double quotes (").
coldelimiter	Delimiter used to indicate the end of column data when export format DEL is specified. Default is comma (,).
dateformat	Format to export date fields in if exporting data in DSNTIAUL or DEL format. Default is format for your country. Valid values for DSNTIAUL are ISO, USA, EUR and JIS. ISO is the only valid option for DEL format.
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".

Example

mfhco export db=db2demo fmt=ixf file=product.ixf tbl=demo.product log=export.log