IMPORT and EXPORT commands for nicknames

Use the IMPORT command to import data into a nickname from a file and the EXPORT command to export data from a nickname to a file.

IMPORT command
The IMPORT command is supported with the Db2® family, Informix®, Microsoft SQL Server, Oracle, Sybase, and Teradata data sources.

The IMPORT command for a Db2 for VM remote table does not work correctly for binary data (FOR BIT DATA) columns.

The following restrictions apply when you use the IMPORT command to import data into a nickname:
  • The remote object on which the nickname is defined must be a table. You cannot import into a nickname that is defined on a view or synonym.
  • The supported file types are IXF, ASC, and DEL.
  • The ALLOW WRITE ACCESS clause must be specified. This clause invokes the online import mode. The ALLOW WRITE ACCESS clause allows concurrent applications to have read and write access to the import target table.
  • You cannot use the COMMITCOUNT AUTOMATIC mode with nicknames.
  • You must specify a value for n for the COMMITCOUNT option. The value of n cannot be zero.
  • Only the INSERT and INSERT_UPDATE operations are supported with nicknames.
  • The column types that are not supported with nicknames are LOBs and generated columns. To import LOB data into a remote table, the corresponding nickname column must be a VARCHAR data type.
  • The following file-type modifiers are not supported with nicknames:
    • dldelfiletype
    • generatedignore
    • generatedmissing
    • identityignore
    • identitymissing
    • indexixf
    • indexschema
    • lobsinfile
    • nodefaults
    • no_type_idfiletype
    • usedefaults
  • Hierarchy (typed table) is not supported with nicknames.
If you submit an IMPORT command that does not adhere to these restrictions, you will receive an SQL error code -27999, for example: 
SQL27999N The requested IMPORT operation into a remote target 
(nickname) cannot be performed.
The following example uses the INSERT option to import data from an IXF file type:
IMPORT FROM import_file_1.ixf OF IXF 
   ALLOW WRITE ACCESS 
   COMMITCOUNT 20 
   INSERT INTO NICKNAME_1;
The following example uses the INSERT_UPDATE option to import data from a DEL file type:
IMPORT FROM import_file_1.del OF DEL 
   ALLOW WRITE ACCESS 
   COMMITCOUNT 50 
   INSERT_UPDATE INTO NICKNAME_1;
The following example uses the INSERT option to import data from an ASC file type. The example includes the STRIPTBLANKS file modifier to truncate any trailing blank spaces in the data. The METHOD L parameter specifies the start and end of the column numbers.
IMPORT FROM import_file_1.asc OF ASC MODIFIED BY STRIPTBLANKS 
   METHOD L(1 6, 8 32,34 44, 46 48) 
   ALLOW WRITE ACCESS 
   COMMITCOUNT 20 
   INSERT INTO NICKNAME_1;
EXPORT command
You can use the EXPORT command to export data from a nickname to a file. The EXPORT command is supported with the Db2 family, Informix, JDBC, Microsoft SQL Server, ODBC, Oracle, Sybase, and Teradata data sources.
The following restrictions apply when you use the EXPORT command to export data from a nickname:
  • The description of the target table that is necessary to perform the Import CREATE operation will not be saved in the IXF file format. Use the db2look utility to collect the information that you need to re-create the table.
  • The supported file types are IXF and DEL.