Data Load (DB2 z/OS)

Go Up to Data Load

The DBArtisan Load Data Utility for DB2 z/OS enables you to load records into one or more tables in a tablespace without creating an intermediate exported file. If the tablespace already contains data, you can opt to add the new data to the existing data or to replace it. The data that’s loaded is subject to the same editing or validation routines currently associated with the table and any field procedure associated with particular table columns. As output, you get a loaded tablespace or partition, a file of records that have been discarded, and an error report if you specify the Enforce Constraints option or if unique indexes were part of the load operation.

To use this utility, you need a sufficient set of privileges.

One of the following conditions must be met:

• You must own the table
• Have load privileges for the database
• DBAdmin or DBCTRL authority for the database.
• SYSCTRL or SYSADM authority.

Refer to IBM documentation for details on utilities. For assistance, see Accessing Third Party Documentation.

To Open the Load Data Utility Wizard

1. Connect to a DB2 OS390 database on the Navigator tree.
2. Expand the database and click the Tablespace subnode.
3. In the right window, select a tablespace and click Load Data from the Command drop-down menu.
4. Use the following topics as a guide to completing the pages of the wizard.

Initial Settings

Required Information	Description
Do you want the utility to be restartable?	The utility restart information will be included if a recover operation is required.
Tape Stacking	Select this if you are backing up this operation onto physical media. Click Properties to specify the particulars for the dataset as it’s recorded by opening the Load Utility - Tape Stacking Dialog Box.
Do you want to specify a cursor?	If you want to specify a cursor you need to type the name of the cursor for the input data set in the field below. No is the default. Any cursor name you specify cannot exceed eight characters.
Specify the ln DDN	The input template name. SYSREC is the default.
Would you like to preformat?	If you say yes, you are telling the operation to serialize at the tablespace level. This can inhibit concurrent processing of separate partitions. No is the default.

Resumption/ Replace/Statistics

Required Information	Description
Resume	If you say No, records load into an empty tablespace. This is the default. If you say Yes, records are loaded into a non-empty tablespace. Loading begins at the current end of data in the tablespace. If you specify the sharelevel as change, the before and after triggers for each row that’s loaded are activated. The default sharelevel is none--this specifies that applications cannot concurrently access the tablespace during the load operation. When you select Yes, the remaining options on this panel are unavailable.
Replace	Here you indicate whether the tablespace and its indexes need to be reset to empty before records load. If you select Yes, you are telling the load operation to serialize at the tablespace level. No is the default.
Would you like to specify the statistics option?	Here you indicate whether you want to gather statistics for the tablespace. No is the default. If you select Yes, statistics are gathered on a base tablespace, but not an LOB tablespace. If you gather statistics on encrypted data, the results may not be useful.
Do you want to output message to SYSPRINT?	This is the output dataset for messages and printed output. Default is the default.
Do you want to force aggregation or rollup processing to be done even though some parts do not contain data?	Yes, No, or default. Default is the default. Here you specify whether you want statistics to aggregate or rollup when RUNSTATS is executed even in the event that some parts are empty.

Select Tables (for running statistics)

Here you are presented with a dialog box where you identify the table you want to run statistics on. Click ADD to open the Table/Object Selector dialog. Click Set Properties to open the Select Column for Table dialog. Click Delete to remove a table you selected from the run statistics operation.

Select Tables to Load Data Into

Here you are presented with the dialog box where you identify the tables you want to load the data into. Click ADD to select one or more tables. Click Set Properties to open the Into Table Specification Dialog Box.

Select Indexes

Here you are presented with a dialog box where you identify the indexes you want to run statistics on. Click ADD to open the Index/Object Selector dialog. Click Set Properties to open the Set Correlation option dialog where you set the KeyCard and Frequent Item options. When you ADD Frequent Options, the Add Frequent Options dialog opens which identifies the number of columns and the count.

Update/History Statistics

Required Information	Description
Update Statistics	All: Indicates that all collect statistics are to be updated in the catalog. All is the default. Access Path: Indicates that updates are to be made only to the catalog table columns that provide statistics used for selecting the access path. Space: Indicates that updates are to be made only to the catalog table columns that provide statistics that help assess the status of a particular tablespace or index. None: Indicates no catalog tables will be updated. Note: This is enabled only when the Report option is set to Yes.
History Statistics	This records all catalog table inserts or updates to the catalog history tables. Default: The value specified on All: Indicates all collected statistics are to be updated in the catalog history tables. Access Path: Indicates that updates are to be made only to the catalog history table columns that provide statistics used for selecting the access path. Space: Specifies that only space-related statistics are to be updated in catalog history tables. None: Specifies that no catalog history tables are to updated with the statistics that have been collected.

Dictionary/Log

Required Information	Description
Keep dictionary	If you specify No, the load operation is prevented from building a new compression dictionary, thereby saving you the costs related to such a build operation. No is the default.
Reuse	When used with Replace (on an earlier panel of the wizard), this option indicates that the load operation should reset and reuse the datasets without deleting or refining them. No is the default.
Log	The Default specifies normal logging during the load operation and is equivalent to Yes. All loaded records will be logged. No specifies no logging of data in the load operation and disables the copy-pending option below.
LOAD is not to set the tablespace in the COPY-pending status	This, the NOCOPYPEND option will not turn off any copy pending options set prior the load operation. NOTE: Specify this option only if the data in this table can be recreated by another load operation if, for some reason, the data is lost. You may not be able to recover the tablespace if you don’t take an image copy after the load operation. No is the default.

Enforce/Specify

Required Information	Description
Enforce	Specifies whether the load operation should enforce check and referential constraints. Constraints indicates constraints will be enforced. If the operation detects a constraint violation, the errant row is deleted and identified by a message. If you elect this option, and there are referential constraints, you must define sort input and output datasets. Default enforces the constraints. No specifies constraints are not enforced. It also puts the tablespace in the Check-pending status if at least one referential or check constraint exists.
Would you like to specify the Work DDN?	If you elected to enforce constraints, you must specify the work DDN. Here you establish DD names for the temporary sort input and output files. The default input is SYSUT1 and output is SORTOUT.
Would you like to specify the SORTKEYS?	This specifies that the index keys are to be sorted in parallel during the sort build phase to improve performance. If you want, you can specify an integer value to provide an estimate of the number of index keys that are to be sorted. The default is 0 if the input is on tape or a cursor.
Format	This selection is for the format of the input data. Unload specifies that the input record format is compatible with DB2 unload format. SQL/DS indicates the input record format is compatible with SQL/DS format.

Input/CCSID

Required Information	Description
Input data file type	Select between EBCDIC, ASCII, or Unicode.
CCSID(s)	These coded character set identifiers for the input file generally correspond to the input data file type. If that’s not the case, the input data will be converted to the table CCSIDs before being loaded.
Do not accept substitution characters in a string	Specifies that the load operation should not accept substitution characters in the string. Use substitutions when a string is being converted from one data type to another. If you do not accept substitutions and the load operation determines that a substitution appears in the string because of a conversion, if discard processing is active, the record will be moved to the discard file. If discard processing is inactive, the load operation will terminate.
Float	S390 specifies that the load operation should expect floating point numbers are in the System/390 hexadecimal floating point format. IEEE specifies that the load operation should expect that floating point number are in IEEE binary floating point format. If you specify IEEE, the binary format floats will be converted to hexadecimal floating points. If there’s a conversion error, the record goes in the discard pile.
Specify the error DDN	Here you specify the DD statement or template for a work dataset that’s used during error processing. SYSERR is the default.

Map DDN/Discard DDN

Required Information	Description
Would you like to specify the Map DDN?	Specifies the DD statement or template for a work data set to be used in error processing. This is used to correlate the identifier of a table row with the input record that’s causing the error. SYSMAP is the default.
Would you like to specify the Discard DDN?	Specifies the DD statement or template for a discard dataset that will hold copies for records that aren’t loaded in the operation (e.g., a record with a conversion error). SYSDISC is the default.
Would you like to specify the maximum number of source records to be written on the discard data set?	Specify an integer with a value between 0 and 2146483647. If the maximum value is reached, the load operation abnormally terminates. When you enter 0, you are specifying that you do not want to set a maximum value and the entire input data set can be discarded.
Would you like to specify the sort device type?	Here you indicate, if available, the device type for temporary datasets that are to be dynamically allocated by DFSORT.

Specify Sort Number/CONTINUE-IF

Required Information	Description
Do you want to specify the sort number?	This is the number of temporary datasets that will be allocated by the sort application program.
Would you like to specify a CONTINUE-IF condition?	Yes indicates that you want to treat each input record as a portion of a larger record. If a record is true, the next record is concatenated with it before it is loaded.
Start/Finish	Specifies the column numbers of the first and last columns.
Hexadecimal	The byte-string value in the continuation field indicates that the next input record is a continuation of the current load record. Records with this byte-string value are concatenated until the value in the continuation field changes.
Condition	After the CONTINUE-IF completes, write a condition as X’byte-string’ or ‘character-string’.

Into Table Specification Dialog Box

At least one table INTO STATEMENT is required for each table that is to be loaded. Each Into Table Specification:

• Identifies the table that is to be loaded.
• Describes fields within the input record.
• Defines the format of the input dataset.

Note: All tables that are specified must belong to the same tablespace.

Required Information	Description
Page 1:
Skip fields in the input data set that do not correspond to columns in the target table	Specifies whether or not the load operation should skip fields in the input dataset that do not correspond to tables in the target table.
Partition	An integer value that applies to the number of the partition where records are to be loaded. This is an option that is enabled only for partitioned tablespaces.
Would you like to preformat?	Specifies that any remaining pages should be preformatted up to the high-allocated RBA in the partition and its corresponding partitioning index space. Preformatting happens after the data is loaded and the indexes are built.
Load records into a non-empty partition	No loads records into an empty partition. No is the default. Yes loads records into a non-empty partition. If the partition is empty, you get a warning message, but the partition is loaded anyway.
Replace only the contents of the partition specified above, rather than the entire tablespace.	Note: You cannot use Load Replace with the partition integer replace option. You can either replace an entire tablespace using the load replace option or the single partition option here.
Reset and reuse DB2-managed data sets	When used with the replace option above, this specifies that the load operation should reset and reuse the DB2-managed datasets without deleting and redefining them.
Would you like to keep the dictionary?	This is an option only if a dictionary exists and the partition being loaded has the COMPRESS YES attribute. If the partition has the COMPRESS YES attribute but no dictionary exists, a dictionary is built and you get an error message.
Would you like to specify a cursor?	If desired, specify a cursor for the input dataset.
Select statement for cursor
In DNN	Specifies the data definition statement or template for the input dataset for the partition. The default is SYSREC.
Discard DNN	Specifies the data definition statement or template for the partition. This dataset also holds copies of records that were loaded and removed. It must be a sequential data set.
Page 2:
Do you want to set criteria for which records are loaded?	If you do not want to specify load criteria, all records in the input dataset are loaded, except for data that is beyond the range of the specified partition.
Do you want to use field selection criteria?	Yes or No.
SQL/DS	Table Owner: Select a table owner from the drop-down list. Table: This is the name of the table that the SQL/DS table is loaded into.
Field selection Criteria	The criteria describes a field and a character constant. A field must contain a character or graphic string. (When compared to a string constant, no datatype conversions are performed.) If the field and the constant are not the same length, the shorter of the two is padded with blanks before a comparison is made.
Fields	Click Add to open the Field Specification Dialog Box.

Field Specification Dialog Box

Required Information	Description
Field Name	The name of a field defined by a field specification. This can be any name you choose.
Would you like to specify a position?	Indicates where a field appears in the assembled load record. Start/End are the locations of the first and last field records. The first record is column 1.
Datatype	Pick the appropriate datatype from the drop-down list.
Length/Scale	Length is the length in bytes of the input field. Scale is the number of digits to the right of the decimal point and must be greater than or equal to 0. 0 is the default.
Would you like to add a condition that causes the db2 column to be loaded with NULL or it’s default value?	NULLIF/CONDITION: Write a condition that causes the DB2 column to be loaded with NULL. DEFAULTIF/CONDITION: Write a condition that causes the DB2 column to be loaded with its default value. No

Set Dataset Info Dialog Box

Required Information	Description
Dataset Name	The name of the input dataset from which the table data should be loaded.
Template Name
SMS: Management class	The management class name may not exceed 8 characters. This enables the dataset to be catalogued. MGMTCLAS name.
Storage class	The storage class name must be valid and cannot exceed 8 characters. STORCLAS name.
Data class	The data class name cannot exceed 8 characters. DATACLAS name.
Unit: Device Type	Specifies the device type or group name for the data set.
Count
Volume: Private	A communications connection that’s specific to DB2.
Sequence Number
Count
Serial no(s)	The serial number of the volume on which a new temporary or permanent data set resides.
Space: Unit
Primary Allocation
Secondary Allocation
Release
Label: Sequence Number
Retention period
Expiration date

Load Utility - Tape Stacking Dialog Box

Tape stacking allows you to make four copies of the same data set simultaneously. DB2 OS390’s Copy to Copy utility allows you to make multiple image copies as well.

Required Information	Description
Local Site Primary Copy	Name the data set where you want the output image copy to go at the local primary site. This relates to the copyddn1 parameter in the stored procedure.
Local Site Backup Copy	Name the data set where you want the output image copy to go at the local backup site. This relates to the copyddn2 parameter in the stored procedure.
Recovery Site Primary Copy	Name the data set where you want the output image copy to go at the recovery primary site. This relates to the recoveryddn1 parameter in the stored procedure.
Recovery Site Backup Copy	Identify the image set where you want the image copy to go at the recovery backup site. This relates to the recoveryddn2 parameter in the stored procedure.
In DDN	Identify the input data set that contains the table to be loaded.
Discard DDN	Identify the work data set that will hold copies of records not included in the load operation.