Import -- Fields and controls

File page
Large Objects page
Columns page



File page

" " Import file
" " Nondelimited ASCII format (ASC)
" " Delimited ASCII format (DEL)
" " Work sheet format (WSF)
" " Integrated exchange format (IXF)
" " Options
" " Import mode
" " Commit records
" " Restart
" " Compound
" " Insert an implied decimal point on decimal data (IMPLIEDDECIMAL)
" " Convert input data into a single sub-table (NO_TYPE_ID)
" " Do not load default values for columns that are not nullable (NODEFAULTS)
" " Use default values on defaultable columns when missing input data (USEDEFAULTS)
" " Message file
" " OK
" " Show SQL
" " Cancel

" " Import file

Type the name of the input file. If the path is omitted, the current working directory is assumed.

" " Nondelimited ASCII format (ASC)

Select this radio button to specify that the data in the input file is in the nondelimited ASCII format (ASC).

With this file type, the only column method available on the Columns page for importing columns into the table is Location (method L).

The valid import modes for this file type are:

" " Delimited ASCII format (DEL)

Select this radio button to specify that the data in the input file is in the delimited ASCII format (DEL).

With this file type, the column methods available on the Columns page for importing columns into the table are:

The valid import modes for this file type are:

" " Work sheet format (WSF)

Select this radio button to specify that the data in the input file is in work sheet format (WSF), used by products like Lotus 1-2-3 and Lotus Symphony.

With this file type, the column methods available on the Columns page for importing columns into the table are:

The valid import modes for this file type are:

" " Integrated exchange format (IXF)

Select this radio button to specify that the data in the input file is in the PC integrated exchange format (PC/IXF).

With this file type, the column methods available on the Columns page for importing columns into the table are:

The valid import modes are:

" " Options

Click on this push button to specify modifiers for the import file type that you selected. The options window opens for your file type:

" " Import mode

Use this field to indicate the type of import mode that will be used. The available modes vary depending on the file type you selected. Use one of these methods:

The valid modes are:

CREATE
Creates the table definition and row contents. If the data was exported from a database manager table, indexes are also created. This option can be used only with IXF files.

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.

REPLACE
Deletes all existing data in the table and inserts the imported data. The table definition and the index definitions are not changed. This option can be used only if the table exists.

REPLACE_CREATE
If the table exists, deletes all existing data in the table and inserts the imported data without changing the table definition or the index definitions.

If the table does not exist, creates the table definition and row contents. If the data was exported from the database manager, indexes are also created.

This option can be used only with IXF files.

" " Commit records

This box contains the number of records to insert into the target table before the changes are committed. Specifying a number for commit reduces the number of records that are lost if a failure occurs during the import.

To change the value in this box, use one of these methods:

" " Restart

This box contains the number of records in the file to skip before the import begins. If an error occurs during an import, you can specify this information to restart the import operation immediately following the last row that was successfully imported and committed.

To change the value in this box, use one of these methods:

" " Compound

This box contains compound SQL, which improves import performance by grouping SQL statements into a block; this might reduce network overhead and improve response time.

To change the value in this box, use one of these methods:

" " Insert an implied decimal point on decimal data (IMPLIEDDECIMAL)

Select this check box to specify that the location of an implied decimal point is determined by the column definition, and is not at the end of the value.

Examples

The value 12345 is imported into a DECIMAL (0,2) column as 123.45 and not 12345.00.

" " Convert input data into a single sub-table (NO_TYPE_ID)

Select this check box to have input data converted into a single sub-table.

" " Do not load default values for columns that are not nullable (NODEFAULTS)

When this check box is selected, default values will not be loaded for columns that are not nullable.

" " Use default values on defaultable columns when missing input data (USEDEFAULTS)

When this check box is selected, default values will be loaded for defaultable columns when input data is missing.

" " Message file

Type the name of the file that will contain warning and error messages that occur during import. If you do not type the full path, the current directory and default drive are used. If the file already exists, the information is appended. If you do not specify a message file, an error is returned.



Large Objects page

" " Retrieve large objects (LOBs) in separate files (LOBSINFILE)
" " LOB paths
" " Add
" " Change
" " Remove
" " Move Up
" " Move Down
" " OK
" " Show SQL
" " Cancel

" " Retrieve large objects (LOBs) in separate files (LOBSINFILE)

Select this check box to specify that you want to retrieve LOBs in separate files. When you select this check box, the options on the Large Objects page are enabled.

" " LOB paths

This list box contains a list of paths where the large objects (LOBs) from the input file are stored.

To add a path to the list box, click on the Add push button. The Add window opens.

To change a path, select it from the list box and click on the Change push button. The Change window opens.

To remove a path from the list box, select it and click on the Remove push button. The path is deleted from the list.

" " Add

Click on this push button to add new paths into the LOB paths list box. The Add window opens.

" " Change

Click on this push button to change the object selected in the list box. The Change window opens.



Columns page

The available controls vary depending on which file type and import mode you selected on the File page.

" " Add
" " Change
" " Column names
" " Column position
" " Data column
" " Move Down
" " End position
" " Include
" " Include columns by
" " Index table space
" " Long data table space
" " Null indicator
" " Remove
" " Start position
" " Table column
" " Table name
" " Table schema
" " Table space
" " Move Up
" " Identity column behavior
" " Generated column behavior
" " OK
" " Show SQL
" " Cancel

" " Add

Click on this push button to add column names into the Column names box. The Add window opens.

" " Change

Click on this push button to change the object selected in the list box. The Change window opens.

" " Column names

Use this box to specify the names of the columns created in the new table. The order in which the columns appear in the list is the order in which data is imported from the columns in the input file. The column mapping is a one-to-one mapping. The first column in the list receives data from the first row in the input file, and so on.

To add a column name to the Column names box, click on the Add push button. The Add window opens.

To change a column name, select it from the Column names box and click on the Change push button. The Change window opens.

To remove a column name from the Column name box, select it and click on the Remove push button. The column name is deleted from the list.

" " Column position

The Column position column in the Table column box indicates the position of a column in the input file that will be imported into the target column.

This information is available only when using Position (method P).

To change the column position, select the column in the Table column box and click on the Change push button.

Examples

In the Table column box, you have the following column:
Table column Include Column position
Name Y 2

The input file contains the following columns:
CustNum CustName PurchDate

The data from the CustName column in the input file is imported into the Name column in the target table.

" " Data column

The Data column in the Table column box specifies name of the data column in the input file that will be imported into the target column.

To change the Data column name, select the column in the Table column box and click on the Change push button.

This information is available only when using Names (method N).

" " End position

The End position column in the Table column box specifies the end of the column of data in the input file that will be imported into the target column.

To change the End position column location value, select the column in the Table column list box and click on the Change push button.

This information is available only when using Location (method L).

Examples

The following example represents a row from an input file where each letter and underscore represents one data column: 

D O G _ _ _ Y _

In this example the:

" " Include

A Y in the Include column indicates that data from the input file will be imported into the column. An N in this field indicates that data will not be imported into the column.

To change the include state for a column, select the column in the Table column box and click on the Change push button.

" " Include columns by

Click on one of the following radio buttons to specify the column method by which all input file columns will be imported into the table. All input file columns are selected from the input file and imported in the table.

The available methods vary depending on the file type and import mode you selected on the File page:

Default (method D)
Indicates that when data is being imported, the first column of the file is imported into the first column of the table you selected, and so on, in the natural order of the input file. If the input file contains more columns than the target table, the additional columns at the end of the input file are dropped.

Position (method P)
Indicates that each data column will be specified by the number of its position (for example, 1 for first column) in the input file.

Location (method L)
Indicates that each data column will be specified by its starting and ending column locations in the input file.

Names (method N)
Indicates that each data column will be specified by its name in the input file.

" " Index table space

Optional: Use this box to specify a table space in which to create any indexes on the table. Indexes can be stored in a different table space than the table itself. You might want to specify an index table space to improve performance for very large tables.

To select a table space in which to create indexes:

This field is available only if you specified a table space other than the default in the Table space box. If you do not specify an index table space, the indexes will be created in the same table space as the table.

" " Long data table space

Optional: Use this box to specify the table space in which to store the values of any long columns. Long data can be LONG VARCHAR, LONG VARGRAPHIC, LOB data types, or distinct types with any of these as source types.

To select a table space for values of long columns:

This field is available only if you specified a table space other than the default in the Table space box. If you do not specify a long data table space, the long data will be created in the same table space as the table.

" " Null indicator

The Null indicator column in the Table column box specifies which column in the input file contains the Null indicator.

To change the Null indicator column location value, select the column in the Table column box and click on the Change push button.

This information is available only when using Location (method L)

" " Start position

The Start position column in the Table column box specifies the beginning column of the data in the input file that will be imported into the target column.

To change the start position column location value, select the column in the Table column box and click on the Change push button.

This information is available only when using Location (method L).

Examples

The following example represents a row from an input file where each letter and underscore represents one data column: 

D O G _ _ _ Y _

In this example the:

" " Table column

The Table column column in the Table column box displays the names of the columns that exist in the target table. The column names cannot be changed.

" " Table name

Type a name for the table that you are creating. This name:

Attention: This name must be unique within the table's schema. No other object in the schema can have the same name.

Validation of table names is not performed at this time. Validation is performed by the Database Manager when the import process begins.

" " Table schema

Use this box to specify the schema of the table that you're creating. The initial value in this box is the user ID under which you connected to the database. To specify a schema with a name that's different than this ID, select an existing schema from the dropdown list or type the name of the schema that you want. The schema can be an existing one or, if you have IMPLICIT_SCHEMA authority, it can be a new one. A new schema name:

" " Table space

Optional: Use the table space box to specify the table space in which to create the table.

" " Identity column behavior

Use this drop-down list to specify how you want the identity column data to be imported. The possible values are:

<default>
If the identity column is defined as GENERATED ALWAYS, an identity value is generated for each row in the input data that either does not have a value for the identity column, or for which a NULL value is specified. If a non-NULL value exists for the identity column, that row is rejected.

If the identity column is defined as GENERATED BY DEFAULT, non-NULL values are imported for that column. If a row either does not have a value for the identity column, or the value in the input data is NULL, a value is generated for that row.

IDENTITYMISSING
Data for the table's identity column is not included in the data to be imported. A value will be generated for each row that is imported into the table.

IDENTITYIGNORE
Data for the table's identity column is included in the data to be imported. The identity column data, however, will be ignored, and a value generated for each row that is imported into the table.

" " Generated column behavior

Use this drop-down list to specify how you want data to be imported into generated columns. The possible values are:

<default>
If the data to be imported into a generated column is either a NULL value, or does not exist, a value based on the column definition is generated for that row. If a non-NULL value exists in the input data, that row is rejected.

GENERATEDMISSING
No data for generated columns is included in the data to be imported. A value based on the column definition is generated for each row.

GENERATEDIGNORE
The data for all generated columns is included in the data to be imported. The generated column data, however, is to be ignored. The value based on the column definition is generated for each row.