Chapter 8: Importing Data

The Import Data tool is designed for programmers who need sample data for testing. This tool is not intended to be used to load large quantities of data. If you want to load large quantities of data, you should contact IBM or other third-party vendors who provide a number of products that can handle this better than Host Compatibility Option. You should take care in using this tool to ensure that the database does not become unusable. You should back up your data regularly to provide a means of recovering from loss of data.

Import Data Window

The Import Data tool (see Figure 8-1) appears when you click HCO > Import Data on the Tools menu.

Figure 8-1: Import Data Tool

The following elements make up the Import Data window:

Menu bar
Toolbar
Status line
Drop-down list of available databases to connect to
Entry fields for log file name and file to import from
List box of tables to import data to

The Toolbar

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

	Connect to database selected from drop-down list.
	Select File to Import From window is displayed to select file to import from.
	Import data into table selected.
	Display the on-line HCO User's Guide. The User's Guide is a hypertext file that describes how the Import Data tool works.

The Menu Bar

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

The Database Menu

The Database menu is used to connect to a database, import data, or to exit the tool. You can also display this menu by clicking on the right mouse button anywhere over the Import Data window (see Figure 8-1).

Menu Option	Function
Import	Import data into table selected.
Connect	Connect to database selected from drop-down list.
Restrict list	Restrict tables included in list box when connecting to a database. See the section Restricting Tables in List for more details.
Disconnect	Disconnect from the database you are currently connected to.
Exit	Exit Import Data tool.

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 import command completes.
Format - DSNTIAUL	Import data in DSNTIAUL format. The file is converted from EBCDIC to ANSI delimited format and then imported.
Format - IXF	Import data in IBM integrated exchange format (PC version). This format can be used to create new tables as well as load data.
Format - DEL	Import data in ANSI delimited format.
Data - Create table	When importing data in IXF, select this option to create the table as well as load data.
Data - Insert	This is only a valid option when importing data in DEL or DSNTIAUL format.

Log File Entry Field

You can specify the name of the log file that import messages are written to. The default name for the log file is set in the configuration file. See the chapter Setting INI Options for more details. The log file is overwritten if you use the same file name.

Connecting to Database

You must connect to a database before you can import data. The Import Data tool will automatically connect to the database specified in the Mainframe Express project unless you have disabled this feature in the configuration file. You can still connect to other databases by doing the following:

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

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

Restricting Tables in List

The list box in Import 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 from the Database menu

The Restrict Tables in List window then appears (see Figure 8-2).

Figure 8-2: Restrict Tables in List window

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 will be restricted to only those tables that begin with DEMO.

Importing Data

To import data into a table, do the following:

Change any import defaults via Options menu
Connect to database
Select a table that has columns that correspond to data being imported from the list box
Select file to import data from (see Figure 8-3)
Click File or select Import from Database menu (see Figure 8-1)

Figure 8-3: Select File window

If you are importing data in DSNTIAUL format, the file is first converted to ANSI delimited format. It is important that there is enough free disk space available to convert the file or the import will fail. The data is then imported into a table using IBM IMPORT API. If any error occurs, error messages are written to the log file.

Note: It is very important that a log file be specified in case the import fails. Statistics as to how many rows have been imported are needed in case you have to restart the import. Care should also be made to ensure that enough space is available for DB2 logging in case backup/restore becomes necessary.

IBM also provides a fast LOAD capability but the Import Data tool does not use this API. If the time to import data becomes a factor, you may want to consider using this function. See IBM DB2 Command Reference for more details.

Importing DSNTIAUL Files

When Host Compatibility Option imports data in DSNTIAUL format, it converts the file being imported into an ANSI delimited file. To do this, the Import Data tool needs to know the following about the DSNTIAUL file:

What format are date fields in? Default is USA

A temporary file is created in ANSI delimited format. The file uses the following defaults:

Column delimiter is a comma (",")
Character delimiter is double quotes (")

You may need to override these defaults if the data that you are importing contains one of these characters. To override these defaults, just select Format, DEL from the Options menu. The Set Import DSNTIAUL window appears (see Figure 8-5). To change options, select your choices from the drop-down lists and then click Set.

Figure 8-4: Set Import DSNTIAUL window

The Import Data tool uses column information from the table selected to determine which columns are character and which are numeric. If columns don't correspond, errors could occur in translating fields.

Creating New Table

Figure 8-5: Create Table window

To create a new table from an import file, you must do the following:

Connect to database
Select a file that is in IXF format
Click on the Options, Data, Create table menus to set the option. The Create Table window appears (see Figure 8-6).
Enter theschema and table name that you want to create and click Set.
Run the import command

The table is created, data imported, and the log is displayed if you have Display log checked after processing completes (see Figure 8-7) in the HCO Log tab of the IDE. You also need to have Output selected on the View menu of the IDE for this to be displayed.

If you need to restrict rows and/or columns imported, you can use the IBM command line processor, DB2, to import data. See IBM DB2 Command Reference for more details on how to use this product.

Figure 8-6: Display Import Log window

Limitations

The Import Data tool has the following limitations:

Data for all columns in a table must be in import file. There is no ability in this tool to limit the column data being inserted.
Import formats WSF (Lotus worksheet) and IXF (mainframe) are not supported.
IXF and DEL files are imported using IBM's IMPORT application programming interface. Some options of the API are not supported. See IBM DB2 Command Reference for list of all available options.
DSNTIAUL files are converted to ANSI delimited format and then use the DEL IMPORT API.
LOB data types are not supported in DSNTIAUL format.
LOB data types generated as part of file in IXF and DEL formats. See IBM's DB2 Command Reference for other limitations.

Command Line Interface

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

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

At least one space must occur before each parameter. There must be an "=" 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

Host Compatibility Option uses the default value set in configuration file. See the chapter Setting INI Options for more details. You should set the normal value you want to use there and then use following entries to override default only when needed.

import	Invokes the Import Data command-line processor. This parameter is positional.
databasename	Name of the database to import to.
tablename	Name of table to be imported. You can specify both the schema and the table name, separated with a period.
filename	Filename to import data to.
format	Format to import 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 a combination of the drive/folder specified in the Mainframe Expressproject name and the Host Compatibility Option configuration file.
chardelimiter	Delimiter used to enclose character data when import format is DEL specified. Default is double quotes (").
coldelimiter	Delimiter used to indicate the end of column data when import format DEL is specified. Default is comma (,).
dateformat	Format to import date fields in if importing data in DSNTIAUL or DEL format. Default is the format for your country. Valid values for DSNTIAUL are ISO, USA, EUR, and JIS. ISO is only valid option for DEL format.
sign	Generate positive numeric data with plus signs when importing data in DEL format. Valid values are "Y" for yes to generate plus signs or "N" to not generate plus signs. Default is "Y".

Examples

Example 1

In this example, table DEMO.ITEMS in database DB2DEMO specified in Mainframe Express project hcodemo located in d:\mfuser\projects has data imported from file c:\import\items.dbm. Log file and delimiters are defaults.

MFECL /HCOIMPT D:\MFUSER\PROJECTS\HCODEMO.MVP 
    FILE=C:\IMPORT\ITEMS.DBM TBL=DEMO.ITEMS

Example 2

In this example, data is imported into table DEMO.ITEMS in database DB2DEMO specified in Mainframe Express project hcodemo located in d:\mfuser\projects from file c:\import\items.ixf using format IXF. Log file uses defaults set in configuration file.

MFECL /HCOIMPT D:\MFUSER\PROJECTS\HCODEMO.MVP FMT=IXF 
    FILE=C:\IMPORT\ITEMS.IXF TBL=DEMO.ITEMS

Example 3

In this example, the table DEMO.ITEMS in database DB2DEMO specified in Mainframe Express project hcodemo located in d:\mfuser\projects is imported from file c:\import\items.del using format DEL and log file import.log in current folder. Column delimiter and decimal sign are defaults. Character delimiter is single quote (') and date format is ISO.

MFECL /HCOIMPT D:\MFUSER\PROJECTS\HCODEMO.MVP 
    FILE=C:\IMPORT\ITEMS.DEL TBL=DEMO.ITEMS LOG=IMPORT.LOG 
    DATE=ISO CHARDEL=' FMT=DEL

Example 4

In this example, data is imported into table DEMO.ITEMS from database DB2DEMO specified in Mainframe Express project hcodemo located in d:\mfuser\projects from file c:\import\items.dsn using format DSNTIAUL and log file import.log in current folder. Date format is ISO.

MFECL /HCOIMPT D:\MFUSER\PROJECTS\HCODEMO.MVP 
    FILE=C:\IMPORT\ITEMS.DSN TBL=DEMO.ITEMS LOG=IMPORT.LOG 
    DATE=ISO FMT=DSN