Chapter 13: 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.

13.1 Import Data Window

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

Figure 13-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

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

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

13.3.1 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 13-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.

13.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 import command completes.
Format - DSNTIAUL	Import data in DSNTIAUL format. The file is converted from EBCDIC to ANSI delimited format and then imported.
Format - DBMAUI	Import data in DBMAUI format. The file is converted to 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. DSNTIAUL, or DBMAUI format.

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

13.5 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 13-1) will display a list of tables to select from if the connection was successful.

13.6 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 13-2).

Figure 13-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.

13.7 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 13-3)
Click File or select Import from Database menu (see Figure 13-1)

Figure 13-3: Select File window

If you are importing data in DBMAUI or 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.

13.8 Importing DBMAUI Files

When Host Compatibility Option imports data in DBMAUI 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 DBMAUI file:

What column delimiter is used? Default is x"02"
What row delimiter is used? Default is a semicolon (";")
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, DBMAUI from the Options menu. The Set Import - DBMAUI window appears (see Figure 13-4).

Figure 13-4: Set Import DBMAUI window

To change options, select character(s) that you want to use from dropdown lists and click Set.

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.

13.9 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 13-5). To change options, select your choices from the drop-down lists and then click Set.

Figure 13-5: Set Import DSNTIAUL window

13.10 Creating New Table

Figure 13-6: 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 13-6).
Enter the schema 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 13-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 13-7: Display Import Log window

13.11 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.
DMAUI and DSNTIAUL files are converted to ANSI delimited format and then use the DEL IMPORT API.
LOB data types are not supported in DSNTIAUL or DBMAUI format.
LOB data types generated as part of file in IXF and DEL formats. See IBM DB2 Command Reference for other limitations.

13.12 Command Line Interface

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

MFECL /HCOIMPT projectname TBL=table FILE=file FMT=format 
    LOG=logfile CHARDEL=char COLDEL=char ROWDEL=char
        DATE=dateformat CRLF=linefeed PLUS=sign

At least one space must occur before each parameter. There must be an "=" between some parameters and their values. See examples for specific syntax.

13.12.1 Required Parameters

Parameter	Description
`/HCOIMPT`	Parameter to invoke Import Data command line processor. Parameter is positional.
`projectname`	Name of Mainframe Express project that contains database name that you want data imported to.
`TBL=table`	Name of table to be imported. Can include both schema and table name.
`FILE=file name`	Filename to import data to.

13.12.2 Optional Parameters

You can omit optional 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.

Parameter	Description
`FMT=format`	Format to import data. Valid values are IXF, DEL, DBMAUI, and DSNTIAUL. You can use DBM for DBMAUI and DSN for DSNTIAUL.
`LOG=logfile`	File name to write processing messages to. If parameter is not specified, default log file name is built using 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 import format DEL specified. Default is double quotes (").
`COLDEL=char`	Delimiter used to indicate the end of column data when import 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 import format DBMAUI specified. Default is semicolon (;).
`DATE=dateformat`	Format to import date fields in if importing data in DBMAUI, DSNTIAUL, or DEL format. Default is the format for your country. Valid values are ISO, USA, EUR, and JIS. ISO is 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 only generate line feed at end of each row. Default is "Y".
`PLUS=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".

13.13 Examples

Example 1

In Example 1, 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, delimiters, and DBMAUI format are defaults.

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

Example 2

In Example 2, 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 Example 3, 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 Example 4, 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