Loading data into a table space

Authorities and privileges

To load data into a table space:

1. Open the Load the table space notebook.

2. On the Options page, specify information to tell DB2 how you want to load data into the table space.

3. On the Data Sets page, specify information allocate data sets.P>
4. On the Target Tables page, specify information to establish target tables to hold the data.

5. On the Statistics Options page, you can select options for running statistics.

6. On the Table Statistics page, you can define rows to run the statistics on.

7. On the Index Statistics page, you can define indexes to run the statistics on.

8. Click OK to load data into the table space.

Related information

• Creating a table space.

Specifying load options

To specify load options:

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

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

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

4. 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.
5. 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.

   >

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

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

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

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

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

11. Select the SORTKEYS check box, and 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 only one index.

12. If you want to specify a continuation string in the case where your records are very long:
    1. Select the Specify the continuation string check box.

    2. Specify a start and end column number.

    3. 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 ]

Defining the data sets needed for loading

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 options page of the Load table space utility notebook.

To define the load data sets:

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

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

3. 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.
4. 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.

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

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

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

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

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

10. 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 ]

Establishing target tables to hold the data

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:

1. In the Target tables for load section of the window, click the Add push button to open the Add Target Table window. You must define a target table to proceed with the load.

2. Repeat step 1 for any other table to add to the list.

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

4. Click on the Remove push button to delete a table from the list.

5. 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 in the field specification area. This opens the Add Field window.

6. 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 push button to open the Change Field window.

7. Click on the Remove push button to delete a table from the list.

[Return to main task ]

Defining options for runnning statistics

The Statistics options page contains information on gathering statistics stored in the DB2 catalog.

To define options for running statistics:

1. Specify the Report statistics radio button to determine if a set of messages is generated to report the collected statistics. The default, NO, means that output is not sent to SYSPRINT. Yes means that output is sent to SYSPRINT.

2. If you want all the collected statistics inserted into the catalog tables, specify the Access path and space statistics radio button. This puts all collected statistics information for this table space in the catalog.

3. If you want to update only the catalog table columns that provide statistics used for access path selection, specify the Access path statistics only radio button.

4. If you want to update only the catalog table columns that provide statistics to help the database administrator assess the status of a particular table space or index, select the Space statistics only radio button.

5. If you do not want to update the catalog table columns, select the Do not update catalog tables radio button. This is only valid when you have selected YES on the Report statistics radio button.

[Return to main task ]

Defining table statistics options

To define table statistics options:

1. Select the Collect statistics on tables check box to specify a table or tables for which column information is to be gathered. All tables must belong to the selected table space.

2. Specify the All radio button to gather statistics for all columns of all tables in the table space.

3. Specify the Individual radio button to gather statistics for all columns of specified tables in the table space.

4. Click the Add push button to open the Add table options window.

5. Click the Change push button to open the Change table options window.

6. Click the Remove push button to delete a selected table from the page.

[Return to main task ]

Defining Index Statistics Options

Specifies indexes for which information is to be gathered. Column information is gathered for the first column of the index. Non-uniform distribution statistics are collected for all indexes in the list except for those whose distribution are uniform.

To define index statistics options:

1. Select the Collect statistics on indexes check box to specify an index or indexes for which column information is to be gathered. All the indexes must be associated with the selected table space.

2. Specify the All radio button to gather statistics for all indexes defined on tables contained in the table space.

3. Specify the Individual radio button to gather statistics for specified indexes in the table space.

4. Click the Add push button to open the Add index options window.

5. Click the Change push button to open the Change index options window.

6. Click the Remove push button to delete a selected index from the page.

[Return to main task ]