IBM Books

API Reference

sqluimpr - Import

Inserts data from an external file with a supported file format into a table, hierarchy, or view. A faster alternative is sqluload - Load; however, the load utility does not support loading data at the hierarchy level.

Authorization

Required Connection

Database. If implicit connect is enabled, a connection to the default database is established.

API Include File

sqlutil.h

C API Syntax 



/* File: sqlutil.h */
/* API: Import */
/* ... */
SQL_API_RC SQL_API_FN
  sqluimpr (
    char * pDataFileName,
    sqlu_media_list * pLobPathList,
    struct sqldcol * pDataDescriptor,
    struct sqlchar * pActionString,
    char * pFileType,
    struct sqlchar * pFileTypeMod,
    char * pMsgFileName,
    short CallerAction,
    struct sqluimpt_in*  pImportInfoIn,
    struct sqluimpt_out*  pImportInfoOut,
    long * pNullIndicators,
    void * pReserved,
    struct sqlca * pSqlca);
/* ... */

Generic API Syntax 



/* File: sqlutil.h */
/* API: Import */
/* ... */
SQL_API_RC SQL_API_FN
  sqlgimpr (
    unsigned short DataFileNameLen,
    unsigned short FileTypeLen,
    unsigned short MsgFileNameLen,
    char * pDataFileName,
    sqlu_media_list * pLobPathList,
    struct sqldcol * pDataDescriptor,
    struct sqlchar * pActionString,
    char * pFileType,
    struct sqlchar * pFileTypeMod,
    char * pMsgFileName,
    short CallerAction,
    struct sqluimpt_in*  pImportInfoIn,
    struct sqluimpt_out*  pImportInfoOut,
    long * NullIndicators,
    void * pReserved,
    struct sqlca * pSqlca);
/* ... */

API Parameters

DataFileNameLen
Input. A 2-byte unsigned integer representing the length in bytes of the data file name.
FileTypeLen
Input. A 2-byte unsigned integer representing the length in bytes of the file type.
MsgFileNameLen
Input. A 2-byte unsigned integer representing the length in bytes of the message file name.
pDataFileName
Input. A string containing the path and the name of the external file from which the data is to be imported.
pLobPathList
Input. An sqlu_media_list using media_type SQLU_LOCAL_MEDIA and the sqlu_media_entry structure listing paths on the client where the LOB files can be found.
pDataDescriptor
Input. Pointer to an sqldcol structure containing information about the columns being selected for import from the external file. The value of the dcolmeth field determines how the remainder of the information provided in this parameter is interpreted by IMPORT. Valid values for this field during an IMPORT (defined in sqlutil) are:
SQL_METH_N
Names
SQL_METH_P
Positions
SQL_METH_L
Locations
SQL_METH_D
Default.

If dcolmeth is SQL_METH_N, selection of columns from the external file is by name.

If dcolmeth is SQL_METH_P, selection of columns from the external file is by position.

If dcolmeth is SQL_METH_L, selection of columns from the external file is by location. The database manager rejects an IMPORT call with a location pair that is invalid because of any one of the following conditions:

A location pair with both locations equal to zero indicates that a nullable column is to be filled with nulls. If pDataDescriptor is NULL, or is set to SQL_METH_D, default selection of columns from the external file is done. In this case, the number of columns and the column specification array are both ignored. The first n columns of data in the external file are taken in their natural order, where n is the number of database columns into which the data is to be imported.

Anything that is not a valid specification of external columns, either by name, position, location, or default, is an error.

For more information, see SQLDCOL.

pActionString
Input. Pointer to a structure containing a 2-byte length field, followed by an array of characters. The array identifies the columns into which data is to be imported.

The character array is of the form: 

   {INSERT | INSERT_UPDATE | REPLACE | CREATE | REPLACE_CREATE}
   INTO {tname[(tcolumn-list)] |
   [{ALL TABLES | (tname[(tcolumn-list)][, tname[(tcolumn-list)]])}]
   [IN] HIERARCHY {STARTING tname | (tname[, tname])}
   [UNDER sub-table-name | AS ROOT TABLE]}

INSERT
The imported data is to be added to the data in the table, and the previously existing table data should not be changed.

INSERT_UPDATE
The imported rows are added for data with primary keys that are not in the table, and are updated for data with matching primary keys. This option is only valid when the target table has a primary key, and the specified (or implied) list of target columns being imported includes all columns for the primary key. This option cannot be applied to views.

REPLACE
The previously existing table data is deleted before the imported data is inserted into the table. The table definition and index definitions are not disturbed. (Indexes are deleted and replaced if indexixf is in FileTypeMod, and FileType is SQL_IXF.) If the table is not already defined, an error is returned.

Attention: If an error occurs after the existing data is deleted, that data is lost.

CREATE
If the specified table name is not already defined, the table definition and the row contents are created using the PC/IXF information in the specified PC/IXF file. If the file was previously exported by the database manager, indexes are also created. If the specified table name is already defined, an error is returned. This option is valid for the PC/IXF file format only.

REPLACE_CREATE
If the specified table name is already defined, the table row contents are replaced using the PC/IXF row information in the PC/IXF file. If the table name is not already defined, the table definition and row contents are created using the PC/IXF information in the PC/IXF file. If the PC/IXF file was exported by the database manager, indexes are also created. This option is valid for the PC/IXF file format only.

Attention: If an error occurs after the existing data is deleted, that data is lost.

tname
The name of the table, typed table, view, or object view into which the data is to be inserted. An alias for REPLACE, INSERT_UPDATE, or INSERT can be used, except in the case of a down-level server, when a qualified or unqualified name should be used. If it is a view, it cannot be a read-only view.

tcolumn-list
A list of column names within the table or view into which the data is to be inserted. Commas must separate the list elements. If column names are not present, column names as defined in the CREATE TABLE or the ALTER TABLE statement are used. If no column list is specified for typed tables, data will be inserted into all columns within each sub-table.
Note:An additional DB2 File Manager specification, using the same syntax as in the IMPORT command (see the Command Reference), can be specified after tcolumn-list.

sub-table-name
Specifies a parent table when creating one or more sub-tables under the CREATE option.

ALL TABLES
An implicit keyword for hierarchy only. When importing a hierarchy, the default is to import all tables specified in the traversal-order-list.

HIERARCHY
Specifies that hierarchical data is to be imported.

STARTING
Keyword for hierarchy only. Specifies that the default order, starting from a given sub-table name, is to be used.

UNDER
Keyword for hierarchy and CREATE only. Specifies that the new hierarchy, sub-hierarchy, or sub-table is to be created under a given sub-table.

AS ROOT TABLE
Keyword for hierarchy and CREATE only. Specifies that the new hierarchy, sub-hierarchy, or sub-table is to be created as a standalone hierarchy.

The tname and the tcolumn-list correspond to the tablename and the colname list of SQL INSERT statements, and have the same restrictions.

The columns in tcolumn-list and the external columns (either specified or implied) are matched according to their position in the list or the structure (data from the first column specified in the sqldcol structure is inserted into the table or view field corresponding to the first element of the tcolumn-list).

If unequal numbers of columns are specified, the number of columns actually processed is the lesser of the two numbers. This could result in an error (because there are no values to place in some non-nullable table fields) or an informational message (because some external file columns are ignored).

pFileType
Input. A string that indicates the format of the data within the external file. Supported external file formats (defined in sqlutil) are:
SQL_DEL
Delimited ASCII, for exchange with dBase, BASIC, and the IBM Personal Decision Series programs, and many other database managers and file managers.
SQL_ASC
Non-delimited ASCII.
SQL_WSF
Worksheet formats for exchange with Lotus Symphony and 1-2-3 programs.
SQL_IXF
PC version of the Integrated Exchange Format, the preferred method for exporting data from a table so that it can be imported later into the same table or into another database manager table.
pFileTypeMod
Input. A pointer to a structure containing a 2-byte long field, followed by an array of characters that specify one or more processing options. If this pointer is NULL, or the structure pointed to has zero characters, this action is interpreted as selection of a default specification.

Not all options can be used with all of the supported file types.

For more information, see the Command Reference.

pMsgFileName
Input. A string containing the destination for error, warning, and informational messages. Can be the path and the name of an operating system file or a standard device. If a file already exists, it is appended to. If it does not exist, a file is created.
CallerAction
Input. The action requested by the caller. Valid values (defined in sqlutil) are:
SQLU_INITIAL
Initial call. CallerAction must be set to this value on the first call to the API.

If the initial call or any subsequent call returns and requires the caller to perform some action prior to completing the requested import, the caller action must be set to one of the following:

SQLU_CONTINUE
Continue processing. The action requested by the utility has completed, so the system can continue processing the initial request.
SQLU_TERMINATE
Terminate processing. The action requested was not performed, so the system terminates the initial request.
pImportInfoIn
Input. For information about this structure, see SQLUIMPT-IN.
pImportInfoOut
Output. For information about this structure, see SQLUIMPT-OUT.
NullIndicators
Input. For ASC files only. An array of integers that indicate whether or not the column data is nullable. The number of elements in this array must match the number of columns in the input file; there is a one-to-one ordered correspondence between the elements of this array and the columns being imported from the data file. That is, the number of elements must equal the dcolnum field of the pDataDescriptor parameter. Each element of the array contains a number identifying a column in the data file that is to be used as a null indicator field, or a zero indicating that the table column is not nullable. If the element is not zero, the identified column in the data file must contain a Y or an N. A Y indicates that the table column data is null, and N indicates that the table column data is not null.
pReserved
Reserved for future use.
pSqlca
Output. A pointer to the sqlca structure. For more information about this structure, see SQLCA.

REXX API Syntax 



IMPORT FROM datafile OF filetype
[MODIFIED BY :filetmod]
[METHOD {L|N|P} USING :dcoldata]
[COMMITCOUNT :commitcnt] [RESTARTCOUNT :restartcnt]
MESSAGES msgfile
{INSERT|REPLACE|CREATE|INSERT_UPDATE|REPLACE_CREATE}
INTO tname [(:columns)]
[OUTPUT INTO :output]
 
CONTINUE IMPORT
 
STOP IMPORT

REXX API Parameters

datafile
Name of the file from which the data is to be imported.
filetype
The format of the data within the external file. The file formats supported are:

DEL
Delimited ASCII

ASC
Non-delimited ASCII

WSF
Worksheet formats

IXF
PC version of Integrated Exchange Format.
filetmod
A host variable containing additional information unique to the chosen file type. If no MODIFIED BY clause is specified, the default filetmod is used.
L|N|P
A character that indicates the method to be used to select columns within the external file. Valid values are:

L
Location

N
Name

P
Position.
dcoldata
A compound REXX host variable containing information about the columns selected for import from the external file. The content of the structure depends upon the method selected. In the following description, XXX is the name of the host variable:
tname
Name of the target table or view. Data cannot be imported to a read-only view.
columns
A REXX host variable containing the names of columns within the table or view into which the data is to be inserted. In the following, XXX is the name of the host variable:

XXX.0
Number of columns

XXX.1
First column name

XXX.2
Second column name

XXX.3
and so on.
msgfile
File or device name where error and warning messages are sent. Path can be used for files.
commitcnt
A host variable specifying that a COMMIT is to be performed after every commitcnt imported records.
restartcnt
A host variable specifying that an import is to be started at record (restartcnt+1). The first restartcnt records are to be skipped.
output
A compound REXX host variable into which information from the import will be passed. In the following, XXX is the name of the host variable:

XXX.1
Number of records read from the file during import

XXX.2
Number of records skipped before inserting or updating begins

XXX.3
Number of rows inserted into the target table

XXX.4
Number of rows of the target table updated with information from the imported records

XXX.5
Number of records that could not be imported

XXX.6
Number of records imported successfully and committed to the database, including rows inserted, rows updated, rows skipped, and rows rejected.

Sample Programs

C
\sqllib\samples\c\impexp.sqc

COBOL
\sqllib\samples\cobol\impexp.sqb

FORTRAN
\sqllib\samples\fortran\impexp.sqf

REXX
\sqllib\samples\rexx\impexp.cmd

Usage Notes

IMPORT accepts input data with minor incompatibility problems (for example, character data can be imported using padding or truncation, and numeric data can be imported with a different numeric data type), but data with major incompatibility problems is not accepted.

IMPORT (in PC/IXF format) can be used to recover a previously exported table. The table returns to the state it was in when exported. This is distinct from the backup utility.

An INSERT, INSERT_UPDATE, REPLACE, or REPLACE_CREATE keyword in the parameter list controls whether the existing data in the table or view is deleted before the rows of imported data are added:

INSERT
Inserts new rows, has no effect on existing rows.
INSERT_UPDATE
Inserts new rows, and updates existing rows that have matching keys.
REPLACE
Deletes all rows and repopulates the table.
REPLACE_CREATE
If the table exists, deletes all rows and repopulates the table. If the table does not exist, creates and populates the table.

The caller action repeat call facility provides support for multiple PC/IXF files created on platforms that support diskettes.

Be sure to complete all table operations and release all locks before calling this API. This can be done by issuing a COMMIT after closing all cursors opened WITH HOLD, or by issuing a ROLLBACK.

When importing part of a file after a system failure, record the number of records imported every time a COMMIT is done. Whenever a COMMIT is performed, two messages are written to the message file: one indicates the number of records to be committed, and the other is written after a successful COMMIT. When restarting the import after a failure, specify the number of records to skip, as determined from the last successful COMMIT.

Importing IXF files to a remote database is much faster if the IXF file is on a hard drive rather than on diskettes. Non-default values for pDataDescriptor, or specifying an explicit list of table columns in the pActionString, makes importing to a remote database slower.

Importing to a remote database requires enough disk space on the server for a copy of the input data file, the output message file, and potential growth in the size of the database.

If IMPORT is run against a remote database, and the output message file is very long (more than 60KB), the message file returned to the user on the client may be missing messages from the middle of the import. The first 30KB of message information and the last 30KB of message information are always retained.

After the old rows are deleted during a REPLACE or REPLACE_CREATE, the utility performs an automatic COMMIT. Consequently, if the system fails, or the application interrupts the database manager after the records are deleted, part or all of the old data is lost. Ensure that the old data is no longer needed before using these options.

When the log becomes full during a CREATE, REPLACE, or REPLACE_CREATE, the utility performs an automatic COMMIT on inserted records. If the system fails, or the application interrupts the database manager after an automatic COMMIT, a table with partially filled data remains in the database. Use the REPLACE or the REPLACE_CREATE option to execute the whole import again, or use INSERT with the restartcount parameter set to the number of rows successfully imported.

By default, automatic commits are not done for the INSERT or the INSERT_UPDATE option. However, they are done if the commitcnt parameter is not zero. A full log results in a rollback.

IMPORT adds rows to the target table using the SQL INSERT statement. The utility issues one INSERT statement for each row of data in the input file. If an INSERT statement fails, one of two actions result:

Data cannot be imported to a system table or a summary table.

Views cannot be created with the IMPORT API.

One cannot REPLACE or REPLACE_CREATE an object table if it has any dependents other than itself, or an object view if its base table has any dependents (including itself).

To replace such a table or a view, do the following:

  1. Drop all foreign keys in which the table is a parent.
  2. Execute IMPORT.
  3. Alter the table to recreate the foreign keys.

If an error occurs while recreating foreign keys, modify the data so that it will maintain referential integrity.

Referential constraints and key definitions are not preserved when creating tables using the PC/IXF file format.

On the Windows NT operating system:

See Also

sqluexpr - Export

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]