DB2 Help

This check box is not available if the Control Center is running as an applet.
If you select this check box, you cannot specify a tape device as the location of the data.

Files, pipes, or devices containing the data

This box lists all the files, pipes, or devices that have the data to be loaded. Use the Add push button to add a name to this box. You can also select a name to be changed, removed, or rearranged.

If you specify several names, they will be processed in sequence. If the last item specified is a tape device, you will be prompted for another tape.

Nondelimited ASCII format (ASC)

Select this radio button to specify that the data in the files to be loaded is in the nondelimited ASCII format (ASC). If you select this radio button, you can click on ASC Options to open the ASC Options window so you can specify options or change default values for loading ASC data into the table columns.

If you select this radio button, you must specify start and end column positions for each data column on the Columns notebook page.

Delimited ASCII format (DEL)

Select this radio button to specify that the data in the files to be loaded is in the delimited ASCII format (DEL). If you select this radio button, you can click on DEL Options to open the DEL Options window so you can specify options or change default values for loading DEL data into the table columns.

Integrated exchange format (IXF)

Select this radio button to specify that the data in the files to be loaded is in the PC integrated exchange format (PC/IXF). If you select this radio button, you can click on IXF Options to open the IXF Options window so you can specify options or change default values for loading IXF data into the table columns.

Add

Click on this push button to open the Add window so you can add the name of a file, pipe, or device to the Files, pipes, or devices containing the data box.

The new entry is added to the list following the entry that is currently selected. If no entry is selected, the new entry is appended to the list.
Note: If you select Load data residing on the local filesystem, you cannot use the ... button to browse the file system. You must type the full file path.

Change

Click on this push button to open the Change window so you can change the name of the selected file, pipe, or device in the Files, pipes, or devices containing the data box.

This push button is available only when a name is selected in the Files, pipes, or devices containing the data box.

Remove

Click on this push button to remove the selected file, pipe, or device names from the Files, pipes, or devices containing the data box. (The actual files, pipes, or devices are not removed from the system.)

This push button is available only when one or more names are selected in the Files, pipes, or devices containing the data box.

Move Up

Click on this push button to move up one position the name of the selected file, pipe, or device in the Files, pipes, or devices containing the data box.

This push button is available only when a name is selected in the Files, pipes, or devices containing the data box.

Move Down

Click on this push button to move down one position the name of the selected file, pipe, or device in the Files, pipes, or devices containing the data box.

This push button is available only when a name is selected in the Files, pipes, or devices containing the data box.

ASC Options

Opens the Load ASC Options window.

DEL Options

Opens the Load DEL Options window.

IXF Options

Opens the Load IXF Options window.

Columns page

Include columns by

Start and end column positions (method L)

Column names (method N)

Column numbers (method P)

Table column

Change

Identity column behavior

Generated column behavior

Cancel

Show Command

Include columns by

Select this check box if you want to specify the method by which columns in DEL or IXF data files are to be loaded into the table columns.

If you selected the Nondelimited ASCII format (ASC) radio button on the File page, this check box is selected and unavailable.

Start and end column positions (method L)

This radio button specifies that, for each table column, data is loaded according to start and end positions in the data file.

This radio button is selected and unavailable if the Nondelimited ASCII format (ASC) radio button on the File page is selected. Method L is the only method available for ASC files.

When this radio button is selected, the following fields are displayed in the Table column box:

Table column: Specifies the name of a column in the table that you are loading data into.
Include: Specifies whether data will be loaded into this table column. Possible values are Y (data will be loaded) or N (data will not be loaded).
Start position: Specifies the byte location in the load file of the beginning of the data column that you want loaded into the table column.
End position: Specifies the byte location in the load file of the end of the data column that you want loaded into the table column.
Null indicator: Specifies the data file column (by byte location) that contains the null indicator flag for the data being loaded into the table column. The default value is zero (0), specifying that the data column is not nullable (which means there will always be data to be loaded into this table column).

You can change the values of the Include, Start position, End position, and Null indicator fields by selecting a table column and clicking on Change. The Change window opens.

Column names (method N)

Select this radio button to specify, for each table column, the name of the data column in the file that is being loaded.

This radio button is available only if the Integrated exchange format (IXF) radio button on the File page and the Include columns by check box are selected. Method N is valid only for files that are in integrated exchange (IXF) format.

If this radio button is selected, the following fields are displayed in the Table column box:

Table column: Specifies the name of a column in the table that you are loading into.
Include: Specifies whether data will be loaded into this table column. Possible values are Y (data will be loaded) or N (data will not be loaded).
Data column: Specifies the name of the data file column that you want loaded into the table column.

You can change the values of the Include and Data column fields by selecting a table column and clicking on Change. The Change window opens.

Column numbers (method P)

This radio button specifies that, for each table column, data is loaded according to the position of the data column in the data file.

This radio button is selected if either the Delimited ASCII format (DEL) or Integrated exchange format (IXF) radio button on the File page is selected. This radio button is available only if the Include columns by check box is selected. Method P is valid only for files that are in delimited ASCII (DEL) or integrated exchange (IXF) file formats.

If this radio button is selected, the following fields are displayed in the Table column box:

Table column: Specifies the name of a column in the table that you are loading into.
Include: Specifies whether data will be loaded into this table column. Possible values are Y (data will be loaded) or N (data will not be loaded).
Column position: Specifies the position (1=first, 2=second, and so on) of the data file column to be loaded into this table column.

You can change the values of the Include and Column position fields by selecting a table column and clicking on Change. The Change window opens.

Table column

This box lists all of the columns in the selected table. The fields that appear in this box vary depending on which Load file type is selected on the File page and which radio button is selected on the Columns page:

Start and end column positions (method L)
Column names (method N)
Column numbers (method P)

To change the values in the fields for a particular table column, select the table column and select Change.

Change

Click on this push button to change field values for a selected table column. The Change window opens.

Identity column behavior

Use this drop-down list to specify how you want the identity column to be loaded. 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 loaded 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 loaded. A value will be generated for each row that is loaded into the table.

IDENTITYIGNORE

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

IDENTITYOVERRIDE

This value applies to GENERATED ALWAYS identity columns only. If the data to be loaded includes the identity column, and the identity values for some or all of the rows are included, these values will be loaded. Any rows that contain either no value or a NULL value for the identity column are not loaded.

Note:

This value is useful if you are migrating data from another database system and the table must be defined as GENERATED ALWAYS, or if you are loading a table from data that you recovered using the ROLLFORWARD option DROPPED TABLE RECOVERY.

Generated column behavior

Use this drop-down list to specify how you want generated columns to be loaded.
Note: You cannot load generated identity columns.

The possible values are:

<default>

If the data to be loaded into a generated column is a NULL value, that value is loaded, even if the column is defined as NOT NULL. 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 loaded. The value for all generated columns is set to NULL.

GENERATEDIGNORE

The data for all generated columns is included in the data to be loaded. The generated column data, however, is to be ignored. The value for all generated columns is set to NULL.

GENERATEDOVERRIDE

If the data to be loaded includes data for generated columns, and the generated values for some or all of the rows are included, these values will be loaded. Any rows that contain either no value or a NULL value for the generated columns are not loaded.

Note:

Counts page

Load mode

Save count

Restart options

Limit the number of rows to be loaded

Warning count

Cancel

Show Command

Load mode

This box contains the mode of the load. To change this mode, click on the down arrow to display a list of valid modes and select one.

The valid modes are:

Insert: Data rows are loaded into the table after any existing table rows. This is the default.
Replace: All existing table data is deleted and the loaded data is inserted. The table definition and index definitions are not changed.
Restart: When a previous load was interrupted, this mode restarts the load.
Terminate: When a previous load was interrupted, this mode terminates that load and moves the table spaces in which the table resides from load pending state to recovery pending state. The table spaces cannot be used until a backup is restored and the table spaces are rolled forward.
Attention: This option is not recommended for general use; it should be selected only if an unrecoverable error occurred.

Save count

This field contains the interval (in number of rows) between consistency points. During the load process, after every N rows are loaded (where N is the number specified in this field) a consistency point will be established. To change the save count, use one of these methods:

Select the up or down arrows to spin through the available choices.
Type in a value.

The default value is 0, meaning no consistency points will be established, unless necessary. If the value you select is not high enough, the synchronization of activities performed at each consistency point will impact performance.

Because a message is issued at each consistency point, you should specify a Save count only if the load is being monitored.

Restart options

Select one radio button to specify where to restart the load process.

Number of records to skip

Specifies that the load operation skips a specified number of records in the data file and then starts the load at the next record. When you select this radio button, you can use one of these methods to specify the number of records that should be skipped:

Select the up or down arrows to spin through the available choices.
Type in a value.

You can use this option with the load mode set to Insert, Replace, or Restart. If you select Restart, you must use the number of rows at the last successful consistency point. The default value is 0, meaning the load will start at the first record.

Restart at the build phase

The load is restarted at the build phase. If you select this option, the load process restarts, no additional rows are loaded, and the indexes are built for the rows that were already loaded. This radio button is available only when the load mode is set to Restart.

Restart at the delete phase

The load is restarted at the delete phase. Rows that cause key violations are deleted from the table.

You can use this option if the message file states that the build phase completed and all temporary files are unmodified. This radio button is available only when the load mode is set to Restart.

Limit the number of rows to be loaded

Select this check box to limit the number of physical file records to be loaded to the value specified in the Row count field.

To change the value in the Row count field, use one of these methods:

Select the up or down arrows to spin through the available choices.
Type in a value.

The default value of zero (0) indicates that the load will continue until either all records are loaded or an unrecoverable error occurs.

Warning count

This field contains the number of warnings after which the load is to be stopped. The default is 0; in this case, the load will continue regardless of the number of warnings encountered during the load operation. To change this number, use one of these methods:

Select the up or down arrows to spin through the available choices.
Type in a value.

Statistics page

Statistics for the table

Statistics for the indexes

Cancel

Show Command

Statistics for the table

Select one of the following radio buttons to specify whether and how you want the table's statistics updated:

Do not update

Statistics for the table are not updated. This is the default.

Update without distribution statistics

Basic-level statistics on the table data are updated in the system catalog tables. An example of a basic-level statistic is the number of pages being used by the table.

Distribution statistics on values in table columns are not collected.

Update with distribution statistics

Basic-level statistics on the table data are updated in the system catalog tables, and statistics of the distribution of data values in the table columns are collected. The query optimizer uses these statistics to more accurately estimate the number of rows in a column that satisfy given equalities or ranges.

An example of a distribution statistic is the frequency with which a particular data value occurs in a column.

Note:

You cannot gather statistics during the load process if you selected Insert or Restart for the Load mode field on the Counts page of the Load notebook.

Statistics for the indexes

Select one of the following radio buttons to specify whether and how you want the table indexes' statistics updated:

Do not update: Statistics for the table's indexes are not updated. This is the default.
Update without extended index statistics: Basic-level statistics are collected on the indexes. An example of a basic-level statistic is the number of index leaf pages.
Update with extended index statistics: Basic-level and detailed statistics are collected for the indexes. The detailed statistics can help the optimizer better estimate the I/O cost of an index scan.
An example of a detailed statistic is the number of I/Os needed to read the data pages into buffer pools of various sizes.

Note:

You cannot gather statistics during the load process if you selected Insert or Restart for the Load mode field on the Counts page of the Load notebook.

Copy Options page

Perform nonrecoverable load operation

Save a copy of the changes made

Tivoli Storage Manager

Vendor shared library

Vendor library name

Save to devices or directories

Specify devices or directories

Add

Change

Remove

Move Up

Move Down

Cancel

Show Command

Perform nonrecoverable load operation

Select this check box to specify this load as a nonrecoverable load.

By default, if forward recovery is enabled for the database in which this table resides and you load data into the table, the table spaces for that table are placed in backup pending state. In this case, the data in the table is not accessible until a table space backup or a full database backup is made.

If you don't want the table spaces to be placed in backup pending state, you can either perform a nonrecoverable load by specifying this check box, or create an image copy of the loaded data by selecting the Save a copy of the changes made check box. Selecting the Perform nonrecoverable load operation check box allows you to perform the load without jeopardizing the recoverability of all the other tables in the database and without the overhead of building a copy of all of the changes made.

Save a copy of the changes made

Select this check box to specify that a copy of the changes made during the load process be saved.

You cannot save a copy of the changes if forward recovery is disabled for the database.

If forward recovery is enabled and you do not specify that a copy be made, the table spaces in which the table resides are placed in backup pending state. The data in the table is not accessible until a table space backup or a full database backup is made.

Tivoli Storage Manager

Select this radio button to specify that the copy of the changes made be stored using the Tivoli Storage Manager.

The Number of sessions spin button contains the number of I/O sessions to be used with Tivoli Storage Manager. To change this number, use one of these methods:

Select the up or down arrows to spin through the available choices.
Type in a value.

The default value is 1, specifying that one I/O session will be used.

The Tivoli Storage Manager radio button is available only when the Save a copy of the changes made check box is selected. The Number of sessions spin button is available only when the Tivoli Storage Manager radio button is selected.

Vendor shared library

Select this radio button to specify that the copy of the changes made be stored using a vendor product.

The Number of sessions spin button contains the number of I/O sessions to be used with the vendor product. To change this number, use one of these methods:

Select the up or down arrows to spin through the available choices.
Type in a value.

The default value is 1, specifying that one I/O session will be used.

After selecting this radio button, specify the name of the library in the Vendor library name field.

The Vendor shared library radio button is available only when the Save a copy of the changes made check box is selected. The Number of sessions spin button is available only when the Vendor shared library radio button is selected.

Vendor library name

Type the name of the shared library (DLL on OS/2) containing the vendor backup and restore I/O functions to be used. You can type the full path. If you do not type the full path, the path where the user exit programs reside will be used.

This field is available only if the Vendor shared library radio button is selected.

Save to devices or directories

Select this radio button to specify that the copy of the changes made be stored on devices or in directories.

The Save to devices or directories radio button is available only when the Save a copy of the changes made check box is selected.

Specify devices or directories

This box lists the devices or directories to which a copy of the changes made will be saved. Use the Add push button to add a name to this box. You can also select names to be changed, removed, or rearranged.

This box is available only when the Save to devices or directories radio button is selected.

Add

Click on this push button to open the Add window so you can add the name of a device or directory to the Specify devices or directories box.

The new entry is added to the list following the entry that is currently selected. If no entry is selected, the new entry is appended to the list.

Change

Click on this push button to open the Change window so you can change the name of the selected device or directory in the Specify devices or directories box.

This push button is available only when a name is selected in the Specify devices or directories box.

Remove

Click on this push button to remove the selected device or directory names from the Specify devices or directories box. (The actual devices and directories are not removed from the system.)

This push button is available only when one or more names are selected in the Specify devices or directories box.

Move Up

Click on this push button to move up one position the name of the selected device or directory in the Specify devices or directories box.

This push button is available only when a name is selected in the Specify devices or directories box.

Move Down

Click on this push button to move down one position the name of the selected device or directory in the Specify devices or directories box.

This push button is available only when a name is selected in the Specify devices or directories box.

Others page

Temporary directories used during index creation

Add

Change

Remove

Move Up

Move Down

Leave the table in quiesced state after load

Preservation of source data order is not required

Skip the header verification code

Data buffer in 4K pages

Sort buffer in 4K pages

Degree of CPU parallelism

Degree of disk parallelism

Percentage of free space to be left on index pages

Message file

Temporary control file

Exception table schema

Name

Cancel

Show Command

Temporary directories used during index creation

This box lists the directories to be used to store the temporary files that are used when indexes are created. Use the Add push button to add a name to this box. You can also select a name to be changed, removed, or rearranged.

If you do not specify directories, the files are created in the sqllib/tmp directory of the DB2INSTANCE owner. If you specify more than one directory, each directory should be on a different file system, and each file system should be on a different disk, to optimize performance.

Make sure that there is enough space on these directories to hold all index keys for the data being loaded.

Note: This list box only appears when the database that contains the table that is being loaded was created using DB2 Version 5 or Version 5.2.

Add

Click on this push button to open the Add window so you can add the name of a directory to the Temporary directories used during index creation box.

The new entry is added to the list following the entry that is currently selected. If no entry is selected, the new entry is appended to the list.