Load -- Fields and controls

File page
Columns page
Counts page
Statistics page
Copy page
Others page



File page

" " Files, pipes, or devices containing the data
" " Nondelimited ASCII format (ASC)
" " Delimited ASCII format (DEL)
" " Integrated exchange format (IXF)
" " Add
" " Change
" " Remove
" " Up
" " Down
" " OK
" " Show SQL
" " Cancel

" " 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.

The files, pipes, or devices must reside on the node where the database that contains the table resides. 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.

" " 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.

" " 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.

" " 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.



Columns page

" " Include columns by
" " Start and end column positions (method L)
" " Column names (method N)
" " Column numbers (method P)
" " Table column
" " Change
" " OK
" " Show SQL
" " Cancel

" " 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:

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.



Counts page

" " Load mode
" " Save count
" " Restart options
" " Limit the number of rows to be loaded
" " Warning count
" " OK
" " Show SQL
" " Cancel

" " 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:

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:

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:

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:



Statistics page

" " Statistics for the table
" " Statistics for the indexes
" " OK
" " Show SQL
" " Cancel

" " 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
" " ADSM
" " Vendor shared library
" " Vendor library name
" " Save to devices or directories
" " Specify devices or directories
" " Add
" " Change
" " Remove
" " Up
" " Down
" " OK
" " Show SQL
" " Cancel

" " 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.

" " ADSM

Select this radio button to specify that the copy of the changes made be stored using ADSTAR Distributed Storage Manager (ADSM).

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

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

The ADSM 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 ADSM 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:

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.

" " 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.

" " 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
" " Up
" " Down
" " Auto Select
" " Rebuild all indexes
" " Extend indexes with new data
" " Defer indexes update
" " 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
" " Remote file
" " Exception table schema
" " Name
" " OK
" " Show SQL
" " Cancel

" " 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.

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

" " Change

Click on this push button to open the Change window so you can change the name of the selected directory in the Temporary directories used during index creation box.

This push button is available only when a name is selected in the Temporary directories used during index creation box.

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

" " Remove

Click on this push button to remove the selected temporary directory names from the Temporary directories used during index creation box.

This push button is available only when one or more names are selected in the Temporary directories used during index creation box.

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

" " Up

Click on this push button to move up one position the name of the selected directory in the Temporary directories used during index creation box.

This push button is available only when a name is selected in the Temporary directories used during index creation box.

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

" " Down

Click on this push button to move down one position the name of the selected directory in the Temporary directories used during index creation box.

This push button is available only when a name is selected in the Temporary directories used during index creation box.

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

" " Auto select

Click this radio button to have DB2 decide whether to use rebuild or incremental mode when building the table index. DB2 Universal Database will choose, the least expensive method for building the index.

Note: This push button only appears when the database that contains the table that is being loaded was created using DB2 Version 6.

" " Rebuild all indexes

Click this radio button to rebuild all of the table indexes, and construct the indexes with their associated definitions for free space.

To rebuild all of the table indexes, DB2 Universal Database must have sufficient resources to sort all of the index keypart for both old and appended table data.

Note: This push button only appears when the database that contains the table that is being loaded was created using DB2 Version 6.

" " Extend indexes with new data

Click this radio button to extend existing indexes with new data. Sort space sufficient for appending index keys for the inserted records is required. It is only possible to extend indexes with new data in cases where the index object is valid and accessible at the time when load starts. If you c hoose Extend indexes with new data and the indexes cannot be extended with new data due to the state of the index, then a warning will be generated, the load will continue, and all indexes will be rebuilt.

Extending indexes with new data will consume index freespace.

Note: This push button only appears when the database that contains the table that is being loaded was created using DB2 Version 6.

" " Defer indexes update

Click this radio button to prevent the indexes form being updated when the table is loaded. The indexes will be marked as needing to be refreshed and the first non-load access will force them to be rebuilt.

The total time for index construction when indexes are built at the first non-laod access is greater than choosing to rebuild all indexes while loading the table. Therefore, when performing multiple loads with deferred indexing, it is advisable (from a performance viewpoint) to let the last load in the sequence perform index rebuild, rather than allow indexes to be rebuilt at first non-load access. Deferred indexing is only supported for tables with non-unique indexes.

Note: This push button only appears when the database that contains the table that is being loaded was created using DB2 Version 6.

" " Leave the table in quiesced state after load

Select this check box to specify that the quiesce state of the table after the load be left in exclusive mode. In this mode, you have exclusive access to the table and its table spaces; other users cannot read or update the table data.

" " Preservation of source data order is not required

Select this check box to indicate that preservation of source data order is not required while loading the table.

" " Skip the header verification code

Select this check box to have the header verification code skipped when the table is loaded.

" " Data buffer in 4K pages

This field contains the number of pages of memory for data buffers used during the load. To change the number, use one of these two methods:

The default value of 0 specifies that the minimum number of buffers to be used will be calculated according to a particular algorithm. Each buffer is at least 16 pages in size (each page is 4 KB of memory).

If you specify a value that is less than the algorithmic minimum, then the minimum required number of buffers is used and no warning is returned.

" " Sort buffer in 4K pages

This field contains the number of pages of memory for sorting the index keys during the load. (Each page is 4 KB of memory.) To change this number, use one of these two methods:

The default value of 0 specifies that a minimum amount of storage is used. This minimum amount used depends on the number of indexes, the number of keys in each index, the size of the keys, and the number of temporary directories specified in the Temporary directories used during index creation box.

If you specify a value greater than zero but less than the required minimum, the minimum value for that load is used.

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

" " Degree of CPU parallelism

This field contains the degree of CPU parallelism to be used by the load utility for such operations as formatting data. A degree of parallelism of 3 means that three formatters are spawned. To change this number, use one of these methods:

The default value of 0 specifies that the degree of CPU parallelism will be based on the number of system CPUs currently online.

This option is most useful when you are running on a system with multiple CPUs. It allows for optimal parallel loading while preserving the order of records in the source data. This is especially useful when you are loading presorted data.

" " Degree of disk parallelism

This field contains the degree of disk parallelism to be used by the load utility for such operations as database I/O. A degree of parallelism of 3 means that three database writers are spawned. To change this number, use one of these methods:

The default value of 0 specifies that the degree of disk parallelism will be based on the number of system disks currently online.

" " Percentage of free space to be left on index pages

Use this field to specify the percentage of each index page to leave as free space when loading the index. The value selected in this field overrides the value used at the time the index was created.

If a percentage of free space is not specified here, the value will default to the percentage used at the time the index was created. This value affects index leaf pages only.

The first entry in a page is added without restriction. When additional entries are placed in an index page, the value specified in this field determines the percentage of free space left on each page.

Select the check box to activate the percentage spin button. Using the spin button, select a percent from 0 to 99. The default value is 10 percent. If you select a value greater than 10, only 10 percent free space will be left in non-leaf pages.

" " Message file

This field contains the name of the file where warning and error messages that occur during the load will be written. This file will be written in the current directory. To change the name of the file, type a name in the field. You can type the full path. If you do not type the full path, the current directory is used.

" " Remote file

Type the base name to be used for creating temporary files during the load. We recommend that you enter a fully qualified name according to the server node.

The remote file is a base file name that DB2 will append with different extensions to create temporary files used to store messages and consistency points, and to delete phase information. The remote file resides on the server machine and is accessed by the DB2 instance exclusively. Any file name qualification must reflect the directory structure of the server, not the client, and the DB2 instance owner must have read and write permission on this file. You must ensure that more than one load is not issued using the same fully qualified remote file name.

Examples

In the following situations, where you either type a partially qualified name or no name at all, the indicated file name is used:

" " Exception table schema

Use this box to specify the schema of the exception table. The schema must exist. This box displays the current authorization ID. To change the name, use one of these methods:

" " Name

Use this box to specify the name of the exception table. The table must exist. Use one of these methods: