DB2 Version 9.7 for Linux, UNIX, and Windows

IMPORT command

Inserts data from an external file with a supported file format into a table, hierarchy, view or nickname. LOAD is a faster alternative, but the load utility does not support loading data at the hierarchy level.

Quick link to File type modifiers for the import utility.

Authorization

IMPORT using the INSERT option requires one of the following:
- DATAACCESS authority
- CONTROL privilege on each participating table, view, or nickname
- INSERT and SELECT privilege on each participating table or view
IMPORT to an existing table using the INSERT_UPDATE option, requires one of the following:
- DATAACCESS authority
- CONTROL privilege on each participating table, view, or nickname
- INSERT, SELECT, UPDATE and DELETE privilege on each participating table or view
IMPORT to an existing table using the REPLACE or REPLACE_CREATE option, requires one of the following:
- DATAACCESS authority
- CONTROL privilege on the table or view
- INSERT, SELECT, and DELETE privilege on the table or view
IMPORT to a new table using the CREATE or REPLACE_CREATE option, requires one of the following:
- DBADM authority
- CREATETAB authority on the database and USE privilege on the table space, as well as one of:
  - IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the table does not exist
  - CREATEIN privilege on the schema, if the schema name of the table refers to an existing schema
IMPORT to a hierarchy that does not exist using the CREATE, or the REPLACE_CREATE option, requires one of the following:
- DBADM authority
- CREATETAB authority on the database and USE privilege on the table space and one of:
  - IMPLICIT_SCHEMA authority on the database, if the schema name of the table does not exist
  - CREATEIN privilege on the schema, if the schema of the table exists
  - CONTROL privilege on every sub-table in the hierarchy, if the REPLACE_CREATE option on the entire hierarchy is used
IMPORT to an existing hierarchy using the REPLACE option requires one of the following:
- DATAACCESS authority
- CONTROL privilege on every sub-table in the hierarchy
To import data into a table that has protected columns, the session authorization ID must have LBAC credentials that allow write access to all protected columns in the table. Otherwise the import fails and an error (SQLSTATE 42512) is returned.
To import data into a table that has protected rows, the session authorization ID must hold LBAC credentials that meet these criteria:
- It is part of the security policy protecting the table
- It was granted to the session authorization ID for write access
The label on the row to insert, the user's LBAC credentials, the security policy definition, and the LBAC rules determine the label on the row.
For a table with protected rows, if the REPLACE or REPLACE_CREATE option is specified, the session authorization ID must have the authority to drop the table.
To import data into a nickname, the session authorization ID must have the privilege to access and use a specified data source in pass-through mode.

Required connection

Database. If implicit connect is enabled, a connection to the default database is established. Utility access to Linux, UNIX, or Windows database servers from Linux, UNIX, or Windows clients must be a direct connection through the engine and not through a DB2 Connect™ gateway or loop back environment.

Command syntax


>>-IMPORT FROM--filename--OF--filetype-------------------------->

>--+-------------------------+--+------------------------+------>
   |            .-,--------. |  |           .-,--------. |   
   |            V          | |  |           V          | |   
   '-LOBS FROM----lob-path-+-'  '-XML FROM----xml-path-+-'   

>--+-------------------------------+---------------------------->
   |              .--------------. |   
   |              V              | |   
   '-MODIFIED BY----filetype-mod-+-'   

>--+-------------------------------------------------------------------------------------------------------+-->
   |                 .-,------------------------.                                                          |   
   |                 V                          |                                                          |   
   '-METHOD--+-L--(----column-start--column-end-+--)--+------------------------------------------------+-+-'   
             |                                        |                     .-,-------------------.    | |     
             |                                        |                     V                     |    | |     
             |                                        '-NULL INDICATORS--(----null-indicator-list-+--)-' |     
             |       .-,-----------.                                                                     |     
             |       V             |                                                                     |     
             +-N--(----column-name-+--)------------------------------------------------------------------+     
             |       .-,---------------.                                                                 |     
             |       V                 |                                                                 |     
             '-P--(----column-position-+--)--------------------------------------------------------------'     

>--+------------------------------------+----------------------->
   '-XMLPARSE--+-STRIP----+--WHITESPACE-'   
               '-PRESERVE-'                 

>--+--------------------------------------------------------------------------------------+-->
   '-XMLVALIDATE USING--+-XDS--+-----------------------+--| Ignore and Map parameters |-+-'   
                        |      '-DEFAULT--schema-sqlid-'                                |     
                        +-SCHEMA--schema-sqlid------------------------------------------+     
                        '-SCHEMALOCATION HINTS------------------------------------------'     

   .-ALLOW NO ACCESS----.                                   
>--+--------------------+--+----------------------------+------->
   '-ALLOW WRITE ACCESS-'  '-COMMITCOUNT--+-n---------+-'   
                                          '-AUTOMATIC-'     

>--+---------------------+--+-------------+--------------------->
   '-+-RESTARTCOUNT-+--n-'  '-ROWCOUNT--n-'   
     '-SKIPCOUNT----'                         

>--+-----------------+--+-----------+--------------------------->
   '-WARNINGCOUNT--n-'  '-NOTIMEOUT-'   

>--+------------------------+----------------------------------->
   '-MESSAGES--message-file-'   

>--+-+-INSERT---------+--INTO--+-table-name--+-------------------------+-+----------------------+-><
   | +-INSERT_UPDATE--+        |             |    .-,-------------.    | |                      |   
   | +-REPLACE--------+        |             |    V               |    | |                      |   
   | '-REPLACE_CREATE-'        |             '-(----insert-column-+--)-' |                      |   
   |                           '-| hierarchy description |---------------'                      |   
   '-CREATE--INTO--+-table-name--+-------------------------+--------------+--| tblspace-specs |-'   
                   |             |    .-,-------------.    |              |                         
                   |             |    V               |    |              |                         
                   |             '-(----insert-column-+--)-'              |                         
                   '-| hierarchy description |--+-AS ROOT TABLE---------+-'                         
                                                '-UNDER--sub-table-name-'                           

Ignore and Map parameters

|--+--------------------------------+--------------------------->
   |            .-,------------.    |   
   |            V              |    |   
   '-IGNORE--(----schema-sqlid-+--)-'   

>--+----------------------------------------------------+-------|
   |         .-,-----------------------------------.    |   
   |         V                                     |    |   
   '-MAP--(----(--schema-sqlid--,--schema-sqlid--)-+--)-'   

hierarchy description

   .-ALL TABLES---------.           
|--+-| sub-table-list |-+--+----+------------------------------->
                           '-IN-'   

>--HIERARCHY--+-STARTING--sub-table-name-+----------------------|
              '-| traversal-order-list |-'   

sub-table-list

      .-,-------------------------------------------.      
      V                                             |      
|--(----sub-table-name--+-------------------------+-+--)--------|
                        |    .-,-------------.    |        
                        |    V               |    |        
                        '-(----insert-column-+--)-'        

traversal-order-list

      .-,--------------.      
      V                |      
|--(----sub-table-name-+--)-------------------------------------|

tblspace-specs

|--+----------------------------------------------------------------------------------+--|
   '-IN--tablespace-name--+---------------------------+--+--------------------------+-'   
                          '-INDEX IN--tablespace-name-'  '-LONG IN--tablespace-name-'

Command parameters

ALL TABLES

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

ALLOW NO ACCESS

Runs import in the offline mode. An exclusive (X) lock on the target table is acquired before any rows are inserted. This prevents concurrent applications from accessing table data. This is the default import behavior.

ALLOW WRITE ACCESS

Runs import in the online mode. An intent exclusive (IX) lock on the target table is acquired when the first row is inserted. This allows concurrent readers and writers to access table data. Online mode is not compatible with the REPLACE, CREATE, or REPLACE_CREATE import options. Online mode is not supported in conjunction with buffered inserts. The import operation will periodically commit inserted data to prevent lock escalation to a table lock and to avoid running out of active log space. These commits will be performed even if the COMMITCOUNT option was not used. During each commit, import will lose its IX table lock, and will attempt to reacquire it after the commit. This parameter is required when you import to a nickname and COMMITCOUNT must be specified with a valid number (AUTOMATIC is not considered a valid option).

AS ROOT TABLE

Creates one or more sub-tables as a stand-alone table hierarchy.

COMMITCOUNT n | AUTOMATIC

Performs a COMMIT after every n records are imported. When a number n is specified, import performs a COMMIT after every n records are imported. When compound inserts are used, a user-specified commit frequency of n is rounded up to the first integer multiple of the compound count value. When AUTOMATIC is specified, import internally determines when a commit needs to be performed. The utility will commit for either one of two reasons:

to avoid running out of active log space
to avoid lock escalation from row level to table level

If the ALLOW WRITE ACCESS option is specified, and the COMMITCOUNT option is not specified, the import utility will perform commits as if COMMITCOUNT AUTOMATIC had been specified.

The ability of the import operation to avoid running out of active log space is affected by the DB2® registry variable DB2_FORCE_APP_ON_MAX_LOG:

If DB2_FORCE_APP_ON_MAX_LOG is set to FALSE and the COMMITCOUNT AUTOMATIC command option is specified, the import utility will be able to automatically avoid running out of active log space.
If DB2_FORCE_APP_ON_MAX_LOG is set to FALSE and the COMMITCOUNT n command option is specified, the import utility will attempt to resolve the log full condition if it encounters an SQL0964C (Transaction Log Full) while inserting or updating a record. It will perform an unconditional commit and then will reattempt to insert or update the record. If this does not help resolve the issue (which would be the case when the log full is attributed to other activity on the database), then the IMPORT command will fail as expected, however the number of rows committed may not be a multiple of the COMMITCOUNT n value. To avoid processing the rows that were already committed when you retry the import operation, use the RESTARTCOUNT or SKIPCOUNT command parameters.
If DB2_FORCE_APP_ON_MAX_LOG is set to TRUE (which is the default), the import operation will fail if it encounters an SQL0964C while inserting or updating a record. This can occur irrespective of whether you specify COMMITCOUNT AUTOMATIC or COMMITCOUNT n.
The application is forced off the database and the current unit of work is rolled back. To avoid processing the rows that were already committed when you retry the import operation, use the RESTARTCOUNT or SKIPCOUNT command parameters.

CREATE

Note: The CREATE parameter is deprecated and may be removed in a future release. For additional details, see "IMPORT command options CREATE and REPLACE_CREATE are deprecated".

Creates the table definition and row contents in the code page of the database. If the data was exported from a DB2 table, sub-table, or hierarchy, indexes are created. If this option operates on a hierarchy, and data was exported from DB2, a type hierarchy will also be created. This option can only be used with IXF files.

This parameter is not valid when you import to a nickname.

Note: If the data was exported from an MVS™ host database, and it contains LONGVAR fields whose lengths, calculated on the page size, are more than 254, CREATE might fail because the rows are too long. See "Imported table re-creation" for a list of restrictions. In this case, the table should be created manually, and IMPORT with INSERT should be invoked, or, alternatively, the LOAD command should be used.

DEFAULT schema-sqlid

This option can only be used when the USING XDS parameter is specified. The schema specified through the DEFAULT clause identifies a schema to use for validation when the XML Data Specifier (XDS) of an imported XML document does not contain an SCH attribute identifying an XML Schema.

The DEFAULT clause takes precedence over the IGNORE and MAP clauses. If an XDS satisfies the DEFAULT clause, the IGNORE and MAP specifications will be ignored.

FROM filename

Specifies the file that contains the data to be imported. If the path is omitted, the current working directory is used.

HIERARCHY

Specifies that hierarchical data is to be imported.

IGNORE schema-sqlid

This option can only be used when the USING XDS parameter is specified. The IGNORE clause specifies a list of one or more schemas to ignore if they are identified by an SCH attribute. If an SCH attribute exists in the XML Data Specifier for an imported XML document, and the schema identified by the SCH attribute is included in the list of schemas to ignore, then no schema validation will occur for the imported XML document.

If a schema is specified in the IGNORE clause, it cannot also be present in the left side of a schema pair in the MAP clause.

The IGNORE clause applies only to the XDS. A schema that is mapped by the MAP clause will not be subsequently ignored if specified by the IGNORE clause.

IN tablespace-name

Identifies the table space in which the table will be created. The table space must exist, and must be a REGULAR table space. If no other table space is specified, all table parts are stored in this table space. If this clause is not specified, the table is created in a table space created by the authorization ID. If none is found, the table is placed into the default table space USERSPACE1. If USERSPACE1 has been dropped, table creation fails.

INDEX IN tablespace-name

Identifies the table space in which any indexes on the table will be created. This option is allowed only when the primary table space specified in the IN clause is a DMS table space. The specified table space must exist, and must be a REGULAR or LARGE DMS table space.

Note: Specifying which table space will contain an index can only be done when the table is created.

insert-column

Specifies the name of a column in the table or the view into which data is to be inserted.

INSERT

Adds the imported data to the table without changing the existing table data.

INSERT_UPDATE

Adds rows of imported data to the target table, or updates existing rows (of the target table) with matching primary keys.

INTO table-name

Specifies the database table into which the data is to be imported. This table cannot be a system table, a created temporary table, a declared temporary table, or a summary table.

One can use an alias for INSERT, INSERT_UPDATE, or REPLACE, except in the case of an earlier server, when the fully qualified or the unqualified table name should be used. A qualified table name is in the form: schema.tablename. The schema is the user name under which the table was created.

LOBS FROM lob-path

Specifies one or more paths that store LOB files. The names of the LOB data files are stored in the main data file (ASC, DEL, or IXF), in the column that will be loaded into the LOB column. The maximum number of paths that can be specified is 999. This will implicitly activate the LOBSINFILE behavior.

This parameter is not valid when you import to a nickname.

LONG IN tablespace-name

Identifies the table space in which the values of any long columns (LONG VARCHAR, LONG VARGRAPHIC, LOB data types, or distinct types with any of these as source types) will be stored. This option is allowed only if the primary table space specified in the IN clause is a DMS table space. The table space must exist, and must be a LARGE DMS table space.

MAP schema-sqlid

This option can only be used when the USING XDS parameter is specified. Use the MAP clause to specify alternate schemas to use in place of those specified by the SCH attribute of an XML Data Specifier (XDS) for each imported XML document. The MAP clause specifies a list of one or more schema pairs, where each pair represents a mapping of one schema to another. The first schema in the pair represents a schema that is referred to by an SCH attribute in an XDS. The second schema in the pair represents the schema that should be used to perform schema validation.

If a schema is present in the left side of a schema pair in the MAP clause, it cannot also be specified in the IGNORE clause.

Once a schema pair mapping is applied, the result is final. The mapping operation is non-transitive, and therefore the schema chosen will not be subsequently applied to another schema pair mapping.

A schema cannot be mapped more than once, meaning that it cannot appear on the left side of more than one pair.

MESSAGES message-file

Specifies the destination for warning and error messages that occur during an import operation. If the file already exists, the import utility appends the information. If the complete path to the file is not specified, the utility uses the current directory and the default drive as the destination. If message-file is omitted, the messages are written to standard output.

METHOD

L

Specifies the start and end column numbers from which to import data. A column number is a byte offset from the beginning of a row of data. It is numbered starting from 1.

Note: This method can only be used with ASC files, and is the only valid option for that file type.

N

Specifies the names of the columns in the data file to be imported. The case of these column names must match the case of the corresponding names in the system catalogs. Each table column that is not nullable should have a corresponding entry in the METHOD N list. For example, given data fields F1, F2, F3, F4, F5, and F6, and table columns C1 INT, C2 INT NOT NULL, C3 INT NOT NULL, and C4 INT, method N (F2, F1, F4, F3) is a valid request, while method N (F2, F1) is not valid.

Note: This method can only be used with IXF files.

P

Specifies the field numbers (numbered from 1) of the input data fields to be imported. Each table column that is not nullable should have a corresponding entry in the METHOD P list. For example, given data fields F1, F2, F3, F4, F5, and F6, and table columns C1 INT, C2 INT NOT NULL, C3 INT NOT NULL, and C4 INT, method P (2, 1, 4, 3) is a valid request, while method P (2, 1) is not valid. This method can only be used with file types IXF or DEL, and is the only valid method for the DEL file type.

For each of the fields specified by method P, you need to define a corresponding column in the action statement, unless all columns are accounted for or the first x columns are going to be loaded, as shown in the following example:

  db2 load from datafile1.del of del method P(1, 3, 4)
      replace into table1 (c1, c3, c4)

MODIFIED BY filetype-mod

Specifies file type modifier options. See File type modifiers for the import utility.

NOTIMEOUT

Specifies that the import utility will not time out while waiting for locks. This option supersedes the locktimeout database configuration parameter. Other applications are not affected.

NULL INDICATORS null-indicator-list

This option can only be used when the METHOD L parameter is specified. That is, the input file is an ASC file. The null indicator list is a comma-separated list of positive integers specifying the column number of each null indicator field. The column number is the byte offset of the null indicator field from the beginning of a row of data. There must be one entry in the null indicator list for each data field defined in the METHOD L parameter. A column number of zero indicates that the corresponding data field always contains data.

A value of Y in the NULL indicator column specifies that the column data is NULL. Any character other than Y in the NULL indicator column specifies that the column data is not NULL, and that column data specified by the METHOD L option will be imported.

The NULL indicator character can be changed using the MODIFIED BY option, with the nullindchar file type modifier.

OF filetype

Specifies the format of the data in the input file:

ASC (non-delimited ASCII format)
DEL (delimited ASCII format), which is used by a variety of database manager and file manager programs
WSF (work sheet format), which is used by programs such as:
- Lotus® 1-2-3®
- Lotus Symphony®
IXF (Integration Exchange Format, PC version) is a binary format that is used exclusively by DB2.

Important: Support for the WSF file format is deprecated and might be removed in a future release. It is recommended that you start using a supported file format instead of WSF files before support is removed.

The WSF file type is not supported when you import to a nickname.

REPLACE

Deletes all existing data from the table by truncating the data object, and inserts the imported data. The table definition and the index definitions are not changed. This option can only be used if the table exists. If this option is used when moving data between hierarchies, only the data for an entire hierarchy, not individual subtables, can be replaced.

This parameter is not valid when you import to a nickname.

This option does not honor the CREATE TABLE statement's NOT LOGGED INITIALLY (NLI) clause or the ALTER TABLE statement's ACTIVE NOT LOGGED INITIALLY clause.

If an import with the REPLACE option is performed within the same transaction as a CREATE TABLE or ALTER TABLE statement where the NLI clause is invoked, the import will not honor the NLI clause. All inserts will be logged.

Workaround 1: Delete the contents of the table using the DELETE statement, then invoke the import with INSERT statement
Workaround 2: Drop the table and recreate it, then invoke the import with INSERT statement.

This limitation applies to DB2 Universal Database™ Version 7 and DB2 UDB Version 8

REPLACE_CREATE

Note: The REPLACE_CREATE parameter is deprecated and may be removed in a future release. For additional details, see "IMPORT command options CREATE and REPLACE_CREATE are deprecated".

If the table exists, deletes all existing data from the table by truncating the data object, and inserts the imported data without changing the table definition or the index definitions.

If the table does not exist, creates the table and index definitions, as well as the row contents, in the code page of the database. See Imported table re-creation for a list of restrictions.

This option can only be used with IXF files. If this option is used when moving data between hierarchies, only the data for an entire hierarchy, not individual subtables, can be replaced.

This parameter is not valid when you import to a nickname.

RESTARTCOUNT n

Specifies that an import operation is to be started at record n+1. The first n records are skipped. This option is functionally equivalent to SKIPCOUNT. RESTARTCOUNT and SKIPCOUNT are mutually exclusive.

ROWCOUNT n

Specifies the number n of physical records in the file to be imported (inserted or updated). Allows a user to import only n rows from a file, starting from the record determined by the SKIPCOUNT or RESTARTCOUNT options. If the SKIPCOUNT or RESTARTCOUNT options are not specified, the first n rows are imported. If SKIPCOUNT m or RESTARTCOUNT m is specified, rows m+1 to m+n are imported. When compound inserts are used, user specified ROWCOUNT n is rounded up to the first integer multiple of the compound count value.

SKIPCOUNT n

Specifies that an import operation is to be started at record n+1. The first n records are skipped. This option is functionally equivalent to RESTARTCOUNT. SKIPCOUNT and RESTARTCOUNT are mutually exclusive.

STARTING sub-table-name

A keyword for hierarchy only, requesting the default order, starting from sub-table-name. For PC/IXF files, the default order is the order stored in the input file. The default order is the only valid order for the PC/IXF file format.

sub-table-list

For typed tables with the INSERT or the INSERT_UPDATE option, a list of sub-table names is used to indicate the sub-tables into which data is to be imported.

traversal-order-list

For typed tables with the INSERT, INSERT_UPDATE, or the REPLACE option, a list of sub-table names is used to indicate the traversal order of the importing sub-tables in the hierarchy.

UNDER sub-table-name

Specifies a parent table for creating one or more sub-tables.

WARNINGCOUNT n

Stops the import operation after n warnings. Set this parameter if no warnings are expected, but verification that the correct file and table are being used is desired. If the import file or the target table is specified incorrectly, the import utility will generate a warning for each row that it attempts to import, which will cause the import to fail. If n is zero, or this option is not specified, the import operation will continue regardless of the number of warnings issued.

XML FROM xml-path

Specifies one or more paths that contain the XML files.

XMLPARSE

Specifies how XML documents are parsed. If this option is not specified, the parsing behavior for XML documents will be determined by the value of the CURRENT IMPLICIT XMLPARSE OPTION special register.

STRIP WHITESPACE: Specifies to remove whitespace when the XML document is parsed.
PRESERVE WHITESPACE: Specifies not to remove whitespace when the XML document is parsed.

XMLVALIDATE

Specifies that XML documents are validated against a schema, when applicable.

USING XDS: XML documents are validated against the XML schema identified by the XML Data Specifier (XDS) in the main data file. By default, if the XMLVALIDATE option is invoked with the USING XDS clause, the schema used to perform validation will be determined by the SCH attribute of the XDS. If an SCH attribute is not present in the XDS, no schema validation will occur unless a default schema is specified by the DEFAULT clause.
The DEFAULT, IGNORE, and MAP clauses can be used to modify the schema determination behavior. These three optional clauses apply directly to the specifications of the XDS, and not to each other. For example, if a schema is selected because it is specified by the DEFAULT clause, it will not be ignored if also specified by the IGNORE clause. Similarly, if a schema is selected because it is specified as the first part of a pair in the MAP clause, it will not be re-mapped if also specified in the second part of another MAP clause pair.
USING SCHEMA schema-sqlid: XML documents are validated against the XML schema with the specified SQL identifier. In this case, the SCH attribute of the XML Data Specifier (XDS) will be ignored for all XML columns.
USING SCHEMALOCATION HINTS: XML documents are validated against the schemas identified by XML schema location hints in the source XML documents. If a schemaLocation attribute is not found in the XML document, no validation will occur. When the USING SCHEMALOCATION HINTS clause is specified, the SCH attribute of the XML Data Specifier (XDS) will be ignored for all XML columns.

See examples of the XMLVALIDATE option below.

Examples

Example 1

The following example shows how to import information from myfile.ixf to the STAFF table:

   db2 import from myfile.ixf of ixf messages msg.txt insert into staff

SQL3150N  The H record in the PC/IXF file has product "DB2    01.00", date
"19970220", and time "140848".
 
SQL3153N  The T record in the PC/IXF file has name "myfile", 
qualifier "        ", and source "            ".
 
SQL3109N  The utility is beginning to load data from file "myfile".
 
SQL3110N  The utility has completed processing.  "58" rows were read 
from the input file.
 
SQL3221W  ...Begin COMMIT WORK. Input Record Count = "58".
 
SQL3222W  ...COMMIT of any database changes was successful.
 
SQL3149N  "58" rows were processed from the input file.  "58" rows were
successfully inserted into the table.  "0" rows were rejected.

Example 2 (Importing into a table with an identity column)

TABLE1 has 4 columns:

C1 VARCHAR(30)
C2 INT GENERATED BY DEFAULT AS IDENTITY
C3 DECIMAL(7,2)
C4 CHAR(1)

TABLE2 is the same as TABLE1, except that C2 is a GENERATED ALWAYS identity column.

Data records in DATAFILE1 (DEL format):

   "Liszt"
   "Hummel",,187.43, H
   "Grieg",100, 66.34, G
   "Satie",101, 818.23, I

Data records in DATAFILE2 (DEL format):

   "Liszt", 74.49, A
   "Hummel", 0.01, H
   "Grieg", 66.34, G
   "Satie", 818.23, I

The following command generates identity values for rows 1 and 2, since no identity values are supplied in DATAFILE1 for those rows. Rows 3 and 4, however, are assigned the user-supplied identity values of 100 and 101, respectively.

   db2 import from datafile1.del of del replace into table1

To import DATAFILE1 into TABLE1 so that identity values are generated for all rows, issue one of the following commands:

   db2 import from datafile1.del of del method P(1, 3, 4)
      replace into table1 (c1, c3, c4)
   db2 import from datafile1.del of del modified by identityignore
      replace into table1

To import DATAFILE2 into TABLE1 so that identity values are generated for each row, issue one of the following commands:

   db2 import from datafile2.del of del replace into table1 (c1, c3, c4)
   db2 import from datafile2.del of del modified by identitymissing
      replace into table1

If DATAFILE1 is imported into TABLE2 without using any of the identity-related file type modifiers, rows 1 and 2 will be inserted, but rows 3 and 4 will be rejected, because they supply their own non-NULL values, and the identity column is GENERATED ALWAYS.

Examples of using the XMLVALIDATE clause

Example 1 (XMLVALIDATE USING XDS)

For the following XMLVALIDATE clause:

XMLVALIDATE USING XDS
  IGNORE (S1.SCHEMA_A)
  MAP ((S1.SCHEMA_A, S2.SCHEMA_B))

The import would fail due to invalid syntax, since the IGNORE of S1.SCHEMA_A would conflict with the MAP of S1.SCHEMA_A to S2.SCHEMA_B.

Example 2 (XMLVALIDATE USING XDS)

For the following XMLVALIDATE clause:

XMLVALIDATE USING XDS
  DEFAULT S8.SCHEMA_H
  IGNORE (S9.SCHEMA_I, S10.SCHEMA_J)
  MAP ((S1.SCHEMA_A, S2.SCHEMA_B), (S3.SCHEMA_C, S5.SCHEMA_E),
    (S6.SCHEMA_F, S3.SCHEMA_C), (S4.SCHEMA_D, S7.SCHEMA_G))