Loading data into a table space
Use the Load Table Space notebook to load records into one or more tables of a table space.
Authorities
and privileges
To load data into a table space:
- Open the Load Table Space notebook.
- On the Options page specify load options to tell DB2 how you want to load data into the table space.
- On the Data sets page, specify information to allocate data sets.
- On the Target Tables page, specify information to establish
target tables to hold the data.
- Click OK to load data into the table space.
Related information
These steps are part of the larger task of Loading data into a table space. When you complete the steps for defining the options, return to loading data into a table space.
To specify load options:
- On the Options page, if you want to use a RESUME option, select the check box and specify YES or NO. This determines whether records are to be loaded into an empty or non-empty table space. For nonsegmented table spaces, space occupied by rows that are marked as deleted or by rows of dropped tables is not used.
- If you want to Reset table space and indexes to empty before load , select the check box
(LOAD REPLACE). The newly loaded rows replace
all existing rows of all tables in the table space, not just those of the
table that you are loading.
- If you decide to use the reset option, select the Retain the current compression dictionary check box to keep the compression dictionary, KEEPDICTIONARY. If you select the check box, the load utility does not build a new dictionary. The current compression dictionary
is used for compressing the input data. If you do not keep the dictionary, a new one is built.
- Select the REUSE check box to use non-empty data sets when loading data. You must have already defined these data sets with the VSAM REUSE option to do this. If you have not, or if you do not specify the REUSE check box, then data sets that are used with loading data must be empty.
-
If you want to preformat the remaining pages up to the high allocated RBA in the table space and index spaces assocated with the table, select the check box. The preformatting happens after the data is loaded and the indexes are built.
- Clear the Log records loaded check box if you do not want log records written during the load process. Ensure that the check box is selected to get normal logging during load.
If the check box is cleared, no logging of data occurs during the load
process. Clearing the check box sets a COPY pending restriction against the
table space or partition that the loaded table resides in. No table or
partition in the table space can be updated until the restriction is removed.
- Clear the Enforce check and referential constraints check box
to remove the constraint enforcement. If the enforcement is not on,
the target table space gets put in CHECK pending status if at least one
referential or check constraint is defined for the table.
If the check box is selected, and if load detects a violation,
the utility deletes the errant row and issues a message to identify it.
If you do have referential constraints, then sort
input and output data sets are required.
- Select the Format of the input records check box to identify the format of the
input record. The format should be compatible with DB2
unload format (UNLOAD) or with SQL/DS
unload format. This action uniquely determines the format of the input. If you do not select this check box, the format of the input data is determined by the
rules for field specifications described on the Target Tables notebook
page of this window.
- On the Input data file type radio buttons, select the code page type for the input data. If you specify ASCII, numeric, date, time, and timestamp internal formats
are not affected.
- Select the CCSIDs for the input file check box to specify up to three coded character set identifiers (CCSIDs) for the input data. Type a valid integer value in
one or all of the fields. The CCSID defaults to the one you chose when you installed
DB2 for OS/390. So if any of the fields relating to CCSID are left blank,
the field will use the installation default. You can specify an SBCS (single
byte character set), a Mixed DBCS (graphics and double byte character sets),
or DBCS (double byte character set) data.
-
In the SORTKEYS field, type an integer value for the estimated number of
index keys to be sorted. The default value is 0. The correct number here
can improve performance, unless your tables in this table space have no
indexes or only has one index.
- If you want to specify a continuation string in the case where your records are very long:
- Select the Specify the continuation string check box.
- Specify a start and end column number.
- Specify a radio button to define the format of the string.
Each input record is treated as a portion
of a larger record. Each record with a continuation is concatenated with
the next record before loading takes place. You can concatenate up to a
maximum of 32767 bytes. If you selected the ASCII data file type, code your continuation
field in the hexadecimal form. The continuation field is not part of the
final load record.
[Return to main task ]
These steps are part of the larger task of Loading data into a table space. When you complete the steps for defining the options, return to loading data into a table space.
The data sets page contains the data set information, or data definition statements,
that will be used by the load utility. Some of these data sets are required
depending on the options specified on the Options page of the Load Table Space
utility notebook.
To define the load data sets:
- On the Data Sets page, at the Input data set field, specify the input data set name. This data set contains the data to be loaded. The default name is SYSREC. The data set must be a sequential data
set that is readable by the MVS BSAM access method.
-
Optional: If you selected the Reset check box from the Options page of this notebook,
you can specify 2 copy data sets, primary and backup data sets.
These data sets contain image copy data sets. The default is SYSCOPY for
the primary copy. A full image copy data set is created for the table specified
when load executes. The table space for which an image copy is produced
is not placed in COPY pending status.
-
Optional: If you selected the Reset check box from the Options page of this notebook, you can specify 2 copy data sets at the recovery site, primary
and backup data sets. These data sets contain image copy data sets.
- In the Input sort data set and the Output sort data set fields, specify the temporary work files for sort input and output data sets. The default input sort data set is SYSUT1 and the default sort output is SORTOUT. The fields are required for any of the following reasons:
-
Referential constraints exist and the Enforce check and referential constraints check box is selected from the Options page.
-
The tables have indexes.
-
The SORTKEYS check box on the Options page is selected with no estimate
or an estimate of 0.
-
Type the name of a work data set for error processing in the Error data set fields. This data set stores information about errors that occur in the LOAD processing. This is required if you specified a number of records to be discarded during load processing. The default
is SYSERR.
-
Type the name of the mapping data set in the Mapping data set fields. This is a work data set for mapping the identifier of a table row back to the input record that caused an error. The default is
SYSMAP. The fields are required for any of the following reasons:
-
Referential constraints exist and the Enforce check and referential constraints check box is selected from the Options page.
-
You specify a number of records to be discarded in the Maximum number of records to be written on discard data set field during LOAD processing when loading one or more tables that have unique indexes.
-
Type the name of the discard data set in the Discard data set fields. This is a work data set that is required if you specify a number of records to be discarded in the field below. This data set holds copies of records that did not get loaded. It also holds copies of records loaded, then removed.
It must be a sequential data set that is readable by BSAM access method
services. The default is SYSDISC.
-
Optional: In the Maximum number of records to be written on discard data set field, specify a maximum number of source records to be written on the discard data set. The value can range from 0 to 2147483647. If you
reach the maximum number, LOAD processing abends, the discard data set
is empty, and you cannot see which records were discarded. The default
is 0, which means that there is no maximum. In this case, the entire
input data set can be discarded.
-
Optional: In the SORTDEVT field, specify the device type for temporary data sets to be dynamically
allocated by DFSORT. This can be any device type that is acceptable to
the DYNALLOC parameter of the SORT or OPTION options for DFSORT.
-
If you want to use DFSORT temporary data sets, select the SORTNUM check box and type a number
of data sets to be dynamically allocated by the sort application program.
This is used only if a device type is specified in the previous step.
[Return to main task ]
These steps are part of the larger task of Loading data into a table space. When you complete the steps for defining the options, return to loading data into a table space.
Use the Target Tables notebook page to describe the table or partition
that is to be loaded, to describe the fields within the input record for
the table or partition named, and to define the format of the input data
set. The Target Tables page is divided into two parts. The top part describes
the tables or partitions. The bottom part describes the field specifications
for a selected table name.
To establish target tables:
- In the Target tables for load section of the window, click Add push button to open the Add Target Table window. You must define a target table to proceed with the load.
- Repeat step 1 for any other table to add to the list.
- In the Target tables for load text area, click on a table to select it and then click on the Change push button to open the Change Target Table window. The table of target information includes:
-
Name
- The name of a table to be loaded. The table must be described
in the catalog and must not be a catalog table name.
-
Owner
- The authorization identifier of the table, or of the invoker
of the load utility.
-
Partition
- For partitioned table spaces only, the
partition number for which records are accepted for loading. Any data outside
the range of the specified partition is not loaded. Other information listed includes: PREFORMAT, RESUME, REPLACE, KEEPDICTIONARY, COPYDDN, RECOVERYDDN
-
WHEN clause
-
- Filters the data that is actually loaded into the table.
With nothing specified in the WHEN clause, data from every load record
in the data set is loaded into the table specified.
- Click on the Remove push button to delete a table from the list.
- For any table listed in the top text area, you can add a field specification for that table. Select the table from the top text area and click on the Add push button. This opens the Add Field window.
- In the Field specification for the table selected above text area, click on a field name to select it and then click on the Change Field push button to open the Change Field Table window.
- Click on the Remove push button to delete a table from the list.
[Return to main task ]