Syntax and options of the LOAD control statement

The LOAD utility control statement, with its multiple options, defines the function that the utility job performs.

You can create a control statement with the ISPF/PDF edit function. After creating it, save it in a sequential or partitioned data set. When you create the JCL for running the job, use the SYSIN DD statement to specify the name of the data set that contains the utility control statement.

Syntax diagram

Read syntax diagramSkip visual syntax diagram LOAD DATAINDDNSYSRECINDDNddnameINDDN(, ddname)INCURSORcursor-namePREFORMATCOPYDICTIONARY1integerPRESORTEDNOPRESORTEDYESPARALLEL( num-subtasks)PRESORTROWFORMATRRFROWFORMATBRFRBALRSN_CONVERSIONEXTENDEDRBALRSN_CONVERSIONNONEresume-specflashcopy-specKEEPDICTIONARYREUSELOGYESLOGNONOCOPYPEND workddn-spec SORTKEYSSORTKEYSNOSORTKEYSinteger format-spec FLOAT(S390)FLOAT(IEEE)EBCDICASCIIUNICODECCSID(, integer)NOSUBSENFORCECONSTRAINTS1ENFORCENONOCHECKPENDERRDDNSYSERRERRDDNddnameMAPDDNSYSMAPMAPDDNddnameDISCARDDNSYSDISCDISCARDDNddnameDISCARDS0DISCARDSintegerBACKOUTNOBACKOUTYESSORTDEVTdevice-typeSORTNUMintegerCONTINUEIF( start: end)=X' byte-string'' character-string'ignore-specdecfloat-specoverride-specdrain-specINDEXDEFERNONEINDEXDEFERNPIALLNONUNIQUEIMPLICIT_TZ' timezone-string'UPDMAXASSIGNEDVALYESUPDMAXASSIGNEDVALNODEFINEAUXNODEFINEAUXYESFORCENONEFORCEREADERSFORCEALLINTO-TABLE-spec
Notes:
  • 1 If you specify SHRLEVEL REFERENCE, ENFORCE NO is used.

resume-spec:

Read syntax diagramSkip visual syntax diagramRESUMENO1SHRLEVELNONESHRLEVELREFERENCEREPLACEcopy-specstatistics-specRESUMEYESSHRLEVELNONEcopy-specSHRLEVELCHANGE
Notes:
  • 1 The RESUME or REPLACE value in the INTO TABLE clause overrides the default value for LOAD RESUME.

workddn-spec:

Read syntax diagramSkip visual syntax diagramWORKDDN(SYSUT1,SORTOUT)WORKDDN( ddname1, ddname2)( ddname1,SORTOUT)(SYSUT1, ddname2)

copy-spec:

Read syntax diagramSkip visual syntax diagramCOPYDDN(SYSCOPY)( ddname1, ddname2)(, ddname2)RECOVERYDDN( ddname3, ddname4)

flashcopy-spec:

Read syntax diagramSkip visual syntax diagramFLASHCOPYNOFLASHCOPYYESCONSISTENTFCCOPYDDN(template-name)

statistics-spec:

Read syntax diagramSkip visual syntax diagram STATISTICS stat-table-spec stat-index-spec REPORTNOREPORTYESUPDATEALLUPDATEACCESSPATHSPACENONESTATCLGMEMSRTintegerINVALIDATECACHE NOINVALIDATECACHE YESHISTORYALLACCESSPATHSPACENONEFORCEROLLUPYESNO

stat-table-spec

Read syntax diagramSkip visual syntax diagramTABLE(ALL)SAMPLEintegerUSE PROFILETABLE(table-name)table-stats-spec

Start of change table-stats-spec: End of change

Read syntax diagramSkip visual syntax diagramSAMPLEintegerCOLUMNALLCOLUMN(,column-name),COLGROUP(,column-name)colgroup-stats-specUSE PROFILE

Start of change colgroup-stats-spec: End of change

Read syntax diagramSkip visual syntax diagram FREQVALCOUNTinteger1MOSTBOTHLEASTCOUNT10 MOSTCOUNTintegerMOSTBOTHLEAST HISTOGRAMNUMQUANTILES100NUMQUANTILESinteger
Notes:
  • 1 When the COUNT keyword is not specified, the utility automatically determines the count value and collects the most frequently occurring values.

stat-index-spec

Read syntax diagramSkip visual syntax diagramINDEX(ALL)correlation-stats-specINDEX(,index-namecorrelation-stats-spec)

Start of change correlation-stats-spec: End of change

Read syntax diagramSkip visual syntax diagramKEYCARD1FREQVALNUMCOLS1COUNT10FREQVALNUMCOLSintegerCOUNTinteger2MOSTBOTHLEAST HISTOGRAM NUMCOLS1NUMQUANTILES100NUMCOLSintegerNUMQUANTILES100NUMQUANTILESinteger
Notes:
  • 1 The KEYCARD option is deprecated. The KEYCARD functionality is now built into the default execution of the inline statistics for indexes and cannot be disabled.
  • 2 When the COUNT keyword is not specified, the utility automatically determines the count value and collects the most frequently occurring values.

format-spec:

Read syntax diagramSkip visual syntax diagramFORMATUNLOADSQL/DSINTERNALDELIMITEDCOLDEL','COLDELcoldelCHARDEL'"'CHARDELchardelDECPT'.'DECPTdecptSPANNEDYESNO

ignore-spec:

Read syntax diagramSkip visual syntax diagramIGNORE(WHENPARTCONVVALPROCIDERRORDUPKEY)

decfloat-spec:

Read syntax diagramSkip visual syntax diagram DECFLOAT_ROUNDMODE ROUND_CEILINGROUND_DOWNROUND_FLOORROUND_HALF_DOWNROUND_HALF_EVENROUND_HALF_UPROUND_UP

override-spec:

Read syntax diagramSkip visual syntax diagram OVERRIDE ( ,SYSTEMPERIODIDENTITYTRANSIDNONDETERMINISTICROWCHANGE )1
Notes:
  • 1 Alternate syntax: The following syntax is still accepted: PERIODOVERRIDE instead of OVERRIDE(SYSTEMPERIOD), TRANSIDOVERRIDE instead of OVERRIDE(TRANSID), IDENTITYOVERRIDE instead of OVERRIDE(IDENTITY). However, the keywords PERIODOVERRIDE, TRANSIDOVERRIDE, and IDENTITYOVERRIDE are deprecated.

Start of changedrain-spec:End of change

Read syntax diagramSkip visual syntax diagram DRAIN_WAITinteger1 RETRYinteger2 RETRY_DELAYinteger3 SWITCHTIMENONESWITCHTIMEtimestamplabeled-duration-expression
Notes:
  • 1 The default for DRAIN_WAIT is the value of the IRLMRWT subsystem parameter.
  • 2 The default for RETRY is the value of the UTIMOUT subsystem parameter
  • 3 The default for RETRY_DELAY is the smaller of the following two values: min (DRAIN_WAIT value x RETRY value, DRAIN_WAIT value x 10)

labeled-duration-expression

Read syntax diagramSkip visual syntax diagramCURRENT_DATECURRENT_TIMESTAMPWITH TIME ZONE +  - constantYEARYEARSMONTHMONTHSDAYDAYSHOURHOURSMINUTEMINUTESSECONDSECONDSMICROSECONDMICROSECONDS

INTO-TABLE-spec:

For the syntax diagram and the option descriptions of the into-table specification, see INTO-TABLE-spec.

Option descriptions

DATA
Specifies that data is to be loaded. This keyword is optional and is used for clarity only.
INDDN ddname
Specifies the input data set or data sets.

ddname is the name of a DD statement from the job step or the name of a template in the utility input stream. The default value is SYSREC.

Start of changeIf multiple ddname values are specified, those data sets are dynamically concatenated as input to LOAD. You can specify a maximum of 1000 ddname values. You cannot specify the same DD statement from the job step more than once.End of change

If you are using DRDA fast load, ddname must be SYSCLIEN.

The record format for an input data set must be fixed-length or variable-length. The data set must be a sequential data set that is readable by the basic sequential access method (BSAM).

If the input file is an HFS or zFS file, use a template with the PATH option.

INCURSOR cursor-name
Specifies that the input data set is to be the result table of a SELECT statement, as defined by a cursor. You must declare the cursor before it is used by LOAD. Use the EXEC SQL utility to define the cursor.

cursor-name is the name of the cursor. This name cannot be longer than eight characters. The specified cursor can be used with the Db2® family cross-loader function, which enables you to load data from any DRDA-compliant remote server.

Restrictions:
  • You cannot load data into the same table on which you defined the cursor.
  • You cannot load data into the parent table in an RI relationship by using a cursor that is defined on a dependent table.
  • You cannot specify field specifications or use discard processing with the INCURSOR option.
  • You cannot specify INCURSOR with the following options
    • SHRLEVEL CHANGE
    • NOSUBS
    • FORMAT UNLOAD
    • FORMAT SQL/DS
    • FORMAT INTERNAL
    • CONTINUEIF
    • WHEN
    • SPANNED YES
Recommendation: Start of changeDo not specify a cursor on a table within the same table space as the table that you are loading. If you cannot avoid this situation, disable SQL parallelism by specifying the following EXEC SQL statement:
EXEC SQL
SET CURRENT DEGREE = '1' ;
ENDEXEC                                                                   
Disabling parallelism in this case helps avoid contention between claims and drains. End of change
PREFORMAT
Specifies that the remaining pages are preformatted up to the high-allocated RBA in the table space and index spaces that are associated with the table that is specified in table-name. The preformatting occurs after the data has been loaded and the indexes are built.

PREFORMAT can operate on an entire table space and its index spaces, or on a partition of a partitioned table space and on the corresponding partitions of partitioned indexes, if any exist. Specifying LOAD PREFORMAT (rather than PART integer PREFORMAT) tells LOAD to serialize at the table space level, which can inhibit concurrent processing of separate partitions. If you want to serialize at the partition level, specify PART integer PREFORMAT.

The PREFORMAT keyword also applies to LOB table spaces and auxiliary indexes that are associated with the base table or partitions that LOAD serialized. XML objects are not preformatted.

COPYDICTIONARY integer
Allows the LOAD utility to copy an existing compression dictionary from a partition to other partitions of a partitioned table space. LOAD copies the current compression dictionary from the partition whose partition number is integer, and uses that compression dictionary to compress the input data for partitions that are being replaced. The default value of integer is 1.

COPYDICTIONARY provides a method for copying a compression dictionary to an empty partition. The partition that is being copied must have a valid compression dictionary.

Start of changeCOPYDICTIONARY causes LOAD to copy the compression dictionary only to partitions that are defined with compression.End of change

Use of the COPYDICTIONARY keyword has these restrictions:

  • COPYDICTIONARY can be used only when the target of the LOAD statement is a partitioned (non-UTS) or partition-by-range table space.
  • PART integer REPLACE must also be specified in the LOAD statement.
  • RESUME YES cannot be specified with COPYDICTIONARY.
  • KEEPDICTIONARY cannot also be specified in the LOAD statement.
PRESORTED
Specifies whether the input data set has already been sorted in clustering key order. If the input data set is in clustering key order, the LOAD utility can execute the RELOAD and BUILD phases in parallel, and can skip the sorting of the clustering index.
NO
Specifies that the input data set has not already been sorted. The LOAD utility must sort the clustering index.
YES
Specifies that the input data set has already been sorted. The LOAD utility does not sort the clustering index, and executes the RELOAD and BUILD phases in parallel.

The following requirements must be satisfied when PRESORTED YES is specified:

  • All data sets that are needed for parallel index build need to be available.
  • For partitioned table spaces with a clustering partitioned index, the presorted order of the data rows must be:
    1. By partition number
    2. By key ordering of clustering index within each partition
  • For partitioned table spaces with a clustering nonpartitioned index, or nonpartitioned table space with a single table, the presorted order of the data rows must be by key ordering of the clustering index.
  • For simple and segmented table spaces:
    • The presorted order of the data rows must be by key ordering of the clustering index within the table.
    • The LOAD statement can contain only one INTO TABLE clause.
Restrictions:
  • Under the following conditions, LOAD issues a warning message, and continues with processing as if PRESORTED NO were specified:
    • When SHRLEVEL CHANGE is also specified
    • When partition parallelism is used
    • When the target tables have no indexes
    • When SORTKEYS NO is specified
    • Start of changeWhen PRESORT is also specifiedEnd of change
  • Only LOAD with REPLACE and with PRESORTED YES can be restarted in the RELOAD phase. If LOAD with RESUME and PRESORTED YES is restarted in the RELOAD phase, utility processing abnormally terminates, and LOAD issues an error message.
  • If PRESORTED YES is specified, and LOAD determines that the input data set is not sorted in clustering key order, LOAD tolerates the keys that are not in order. However, for the clustering index, inline statistics are not collected and real-time statistics are invalidated. LOAD issues a warning message.
PARALLEL or PARALLEL num-subtasks
Specifies the maximum number of subtasks that LOAD can process in parallel. By using parallel subtasks, the utility can potentially reduce the elapsed time of the load operation.

If you are loading from a single input data set, PARALLEL enables additional data parallelism.

num-subtasks must be an integer between 0 and 32767, inclusive.

Recommendation: Specify PARALLEL(0) or PARALLEL.

If you specify the PARALLEL keyword without a value, the default value is PARALLEL(0).

The total possible number of subtasks for a particular LOAD job depends on the following factors:

  • the number of data partitions to be loaded
  • the number of indexes on the table to be loaded
  • whether inline statistics are gathered
  • whether the LOAD statement specifies a single input data set for the entire job or one input data set per partition. Start of change(If you specify INDDN with multiple ddname values, LOAD considers that specification to be a single input data set for the purposes of calculating subtasks.)End of change

LOAD calculates the optimal number of these subtasks to process in parallel based on memory constraints and the number of available processors.

LOAD uses the value of PARALLEL as follows:

  • If you specify PARALLEL(0) or PARALLEL: LOAD uses the value that it calculated for the optimal number of parallel subtasks.
  • If you specify PARALLEL(1): The utility loads the data with the minimal amount of parallel subtasks that are needed for the load operation.
  • If you specify a value for PARALLEL other than 0 or 1: If the value is less than the calculated optimal number of parallel subtasks, LOAD uses the specified value or the minimum number of required parallel subtasks. If the value is greater than the calculated optimal number of parallel subtasks, LOAD limits the number of parallel subtasks to the optimal number.

PARALLEL overrides the value of the PARAMDEG_UTIL subsystem parameter.

Recommendation: If you specify PARALLEL and SHRLEVEL CHANGE, set the LOCKSIZE attribute of the table space to ROW to minimize contention on the parallel subtasks.

Start of changePARALLEL is ignored in the following situations:End of change

Start of change
  • A single input data set is specified, and the LOAD statement includes any of the following options:
    • SPANNED YES
    • INCURSOR
    • PRESORTED
    • FORMAT INTERNAL
    • FORMAT SQL/DS
  • The table space to be loaded is a partition-by-growth table space, and the LOAD statement includes the SHRLEVEL NONE option.
  • The table to be loaded has XML columns and is in a simple or segmented table space, and the LOAD statement includes the SHRLEVEL CHANGE option.
  • The table to be loaded has LOB or XML columns, and the LOAD statement includes the SHRLEVEL NONE option.
End of change
Start of changePRESORTEnd of change
Start of changeSpecifies that input records are to be sorted in clustering order before loading them into the target table space. Existing rows in the table space are not affected.

For LOAD to presort the data, a clustering index is required. The clustering index can be implicitly or explicitly created. Otherwise, if a clustering index does not exist, PRESORT is ignored. In the case where a table space contains multiple tables and not all of those tables have a clustering index, all input records are sorted. However, for any of these tables without a clustering index, the order of the records after they are loaded might not be consistent with the order in the input data set.

Presort processing passes records in-memory and does not require a secondary data set to hold the records.

If you specify PRESORT, you must also specify SORTDEVT, so that all necessary sort data sets are dynamically allocated. The names for these sort data sets are described in Data sets that LOAD uses.

You cannot specify PRESORT with any of the following options or objects:

  • A table with LOB or XML columns
  • A table space with a clone relationship
  • A hash-organized table space
  • FORMAT SPANNED
  • FORMAT UNLOAD
  • FORMAT SQL/DS
  • FORMAT SPANNED YES

If you specify PRESORT with PRESORTED YES, PRESORTED YES is ignored.

End of change
ROWFORMAT
Specifies the output row format in the affected table space or partition. This keyword has no effect on LOB, catalog, directory, XML, or universal (UTS) table spaces participating in a CLONE relationship.
Important: ROWFORMAT is deprecated in Db2 12 for z/OS®, meaning that its use is discouraged. Although this keyword remains supported, support is likely to be removed eventually. See Deprecated function in Db2 12 .
BRF
Specifies that the table space or partition being reorganized or replaced will be converted to or remain in basic row format.
RRF
Specifies that the table space or partition being reorganized or replaced will be converted to or remain in reorder row format.
Start of change RBALRSN_CONVERSION End of change
Start of changeSpecifies the RBA or LRSN format of the target object after the completion of the LOAD utility.
Important: RBALRSN_CONVERSION is deprecated, meaning that its use is discouraged. Although this keyword remains supported, support is likely to be removed eventually. The utility fails if BASIC is specified, or if NONE is specified for an object in the 6-byte format.

If you specify RBALRSN_CONVERSION, you must also specify the REPLACE keyword.

EXTENDED
Specifies that if an object is found in basic 6-byte format, it is converted to 10-byte extended format.

EXTENDED is the default value.

NONE
Specifies that no conversion is performed.

The utility fails if RBALRSN_CONVERSION NONE is specified on a table space that is in basic 6-byte format.

If a CLONE relationship exists, the page set conversion cannot be performed. For clone relationships, you must drop the clone table, convert the base table to extended 10-byte format, and then re-create the clone table.

LOAD REPLACE of a base table space, when converting page format to extended, does not convert versioned XML table spaces that are associated with that base table space.

End of change
RESUME
Indicates whether records are to be loaded into an empty or non-empty table space. For nonsegmented table spaces, space is not reused for rows that have been marked as deleted or for rows of dropped tables.
Important: Specifying LOAD RESUME (rather than PART integer RESUME) tells LOAD to serialize on the entire table space, which can inhibit concurrent processing of separate partitions. If you want to process other partitions concurrently, specify PART integer RESUME.
NO
Loads records into an empty table space. If the table space is not empty, and you have not used REPLACE, a message is issued and the utility job step terminates with a job step condition code of 8.

For nonsegmented table spaces that contain deleted rows or rows of dropped tables, using the REPLACE keyword provides increased efficiency.

The default value is NO, unless you override it with PART integer RESUME YES.

YES
Loads records into a non-empty table space. If the table space is empty, a warning message is issued, but the table space is loaded. Loading begins at the current end of data in the table space. Space is not reused for rows that are marked as deleted or for rows of dropped tables.

Start of changeRESUME YES is not valid with SHRLEVEL REFERENCE.End of change

LOAD RESUME YES SHRLEVEL CHANGE activates the before triggers and after triggers for each row that is loaded.

Start of changeIf LOAD RESUME YES SHRLEVEL NONE is specified with COPYDDN or RECOVERYDDN, an inline image copy will be created during LOAD processing. The inline image copy will be a full copy for the table space. If individual partitions are specified using INTO TABLE PART, the image copy and SYSCOPY records will only include those partitions.End of change

Start of changeBACKOUTEnd of change
Start of changeSpecifies whether to delete all rows loaded by the current LOAD operation if any record would leave the object unavailable. YES is the default value if the BACKOUT keyword is specified alone. Start of changeHowever, no BACKOUT processing occurs unless a non-zero DISCARDS value is specified and the number of discarded records exceeds the DISCARDS value.End of change

Start of changeBACKOUT YES is supported only with RESUME YES and SHRLEVEL NONE; you cannot specify REPLACE or RESUME NO at the table space level or in any INTO TABLE PART clauses. You also cannot specify INCURSOR with BACKOUT YES.End of change

YES
Specifies that all rows loaded by the current LOAD operation are deleted if any input record would leave the object unavailable. The table space is available at the completion of the LOAD. YES is the default when BACKOUT is specified.
NO
Specifies that updates made by LOAD are not be rolled back if an error record is found. This behavior is the default when the BACKOUT keyword is not specified.
End of change
SHRLEVEL
Specifies the extent to which applications can concurrently access the table space or partition during the LOAD utility job. The following parameter values are listed in order of increasing extent of allowed concurrent access.
NONE
Specifies that applications have no concurrent access to the table space or partition.
Start of changeREFERENCEEnd of change
Start of changeSpecifies that applications can concurrently read from the table space or partition into which LOAD is loading data, except for the duration of the SWITCH phase.

LOAD REPLACE SHRLEVEL REFERENCE specifies that data is reloaded into a shadow copy of the target objects, and LOAD switches the future access of an application from the original copy to the shadow copy in the SWITCH phase. This option is not supported on table spaces with clone relationship, or on target table defined with LOB or XML column.

Start of changeIf you specify SHRLEVEL REFERENCE, the ENFORCE NO option is used. End of change

Start of changeIf a LOAD REPLACE SHRLEVEL REFERENCE job fails to acquire the necessary drain, the utility terminates with return code 8.End of change

End of change
CHANGE
Specifies that applications can concurrently read from and write to the table space or partition into which LOAD is loading data for the majority of the LOAD duration.

A LOAD SHRLEVEL CHANGE job functions like a mass INSERT. Whereas a regular LOAD job drains the entire table space, LOAD SHRLEVEL CHANGE functions like an INSERT statement and uses claims when it accesses an object.

If you specify SHRLEVEL CHANGE, you cannot specify any of the following parameters:
  • INCURSOR
  • RESUME NO
  • REPLACE
  • KEEPDICTIONARY
  • LOG NO
  • ENFORCE NO
  • STATISTICS
  • COPYDDN
  • RECOVERYDDN
  • MAPDDN
  • PREFORMAT
  • REUSE
  • PART integer REPLACE

If you are loading individual partitions and specify SHRLEVEL CHANGE, RESUME YES must also be specified. You can either specify RESUME YES on the individual PART clauses or inherit it from the main LOAD statement.

LOAD RESUME YES SHRLEVEL CHANGE does not perform the SORT, BUILD, SORTBLD, INDEXVAL, or ENFORCE phases, and the compatibility and concurrency considerations differ.

Normally, a LOAD RESUME YES job loads the records at the end of the already existing records. However, for a LOAD RESUME YES job with the SHRLEVEL CHANGE option, the utility tries to insert the new records in available free space as close to the clustering order as possible. This LOAD job does not create any additional free pages. If you insert many records, these records are likely to be stored out of clustering order. In this case, you should run the REORG TABLESPACE utility after the LOAD utility loads the records.

Recommendation: If the LOAD utility loads many records, run RUNSTATS SHRLEVEL CHANGE UPDATE SPACE and then a conditional REORG.

When an identity column exists in the table that is being loaded, performance can be improved by specifying the CACHE attribute for the identity column.

Lock escalation is disabled on XML table spaces for LOAD RESUME YES SHRLEVEL CHANGE.

Log records that Db2 creates during LOAD RESUME YES SHRLEVEL CHANGE can be used by Db2 DataPropagator, if the tables that are being loaded are defined with DATA CAPTURE CHANGES.

LOAD jobs with the SHRLEVEL CHANGE option do not insert any records into SYSIBM.SYSCOPY.

Before and after row triggers are activated only for SHRLEVEL CHANGE. Statement triggers for each row are also activated for LOAD RESUME YES SHRLEVEL CHANGE.

REPLACE
Indicates whether the table space and all its indexes need to be reset to empty before records are loaded. With this option, 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. ForDb2 STOGROUP-defined data sets, the data set is deleted and redefined with this option, unless you also specified the REUSE option. You must have LOAD authority for all tables in the table space where you perform LOAD REPLACE. If you attempt a LOAD REPLACE without this authority, you get an error message.

You cannot use REPLACE with the PART integer REPLACE option of INTO TABLE; you must either replace an entire table space by using the REPLACE option or replace a single partition by using the PART integer REPLACE option of INTO TABLE.

Specifying LOAD REPLACE (rather than PART integer REPLACE) tells LOAD to serialize at the table space level. If you want to serialize at the partition level, specify PART integer REPLACE. See the information about specifying REPLACE at the partition level under the keyword descriptions for INTO TABLE.

Restrictions:
  • LOAD REPLACE is not allowed on a table that is defined with data versioning.
  • LOAD REPLACE is not allowed on a table space after RECOVER was run on that table space to a point in time before pending definition changes were materialized. Before running LOAD REPLACE, you need to run REORG on the entire table space to complete the point-in-time recovery process.
  • LOAD REPLACE is not allowed on an archive-enabled table. (LOAD REPLACE is allowed on the table space that contains the archive table.)
COPYDDN (ddname1,ddname2)
Specifies the DD statements for the primary (ddname1) and backup (ddname2) copy data sets for the image copy.

ddname is the DD name.

The default value is SYSCOPY for the primary copy. No default exists for the backup copy.

Start of changeThe COPYDDN keyword can be specified with REPLACE or with RESUME YES SHRLEVEL NONE. End of change

If COPYDDN is specified with REPLACE, a full image copy data set (SHRLEVEL REFERENCE) is created for the table or partitions that are specified when LOAD executes. The table space or partition for which an image copy is produced is not placed in COPY-pending status.

Start of changeIf COPYDDN is specified with RESUME YES SHRLEVEL NONE, an inline image copy will be created during LOAD processing. The inline image copy will be a full copy for the table space. If individual partitions are specified using INTO TABLE PART, the image copy and SYSCOPY records will only include those partitions. If COPYDDN is specified at the table space level, it cannot be specified at the INTO TABLE PART level.End of change

Image copies that are taken during LOAD REPLACE are not recommended for use with RECOVER TOCOPY because these image copies might contain unique index violations, referential constraint violations, or index evaluation errors.

If you specify COPYDDN when loading a table with XML data, an inline copy is taken only of the base table space, not the XML table space.

If you specify COPYDDN when loading a table with LOB columns, Db2 does not create a copy of any index, LOB table space, or XML table space. You must perform these tasks separately.

The COPYDDN keyword specifies either a DD name or a TEMPLATE name specification from a previous TEMPLATE control statement. If utility processing detects that the specified name is both a DD name in the current job step and a TEMPLATE name, the utility uses the DD name.

RECOVERYDDN ddname3,ddname4
Specifies the DD statements for the primary (ddname3) and backup (ddname4) copy data sets for the image copy at the recovery site.

ddname is the DD name.

You cannot have duplicate image copy data sets. The same rules apply for RECOVERYDDN and COPYDDN.

Start of changeIf RECOVERYDDN is specified at the table space level, it cannot be specified at the INTO TABLE PART level.End of change

The RECOVERYDDN keyword specifies either a DD name or a TEMPLATE name specification from a previous TEMPLATE control statement. If utility processing detects that the specified name is both a DD name in the current job step and a TEMPLATE name, the utility uses the DD name.

FLASHCOPY
Specifies whether FlashCopy® technology is used to create a copy of the object. Valid values are YES, NO, or CONSISTENT. When FlashCopy is used, a separate data set is created for each partition or piece of the object.

Specify YES or CONSISTENT only if the Db2 data sets are on FlashCopy Version 2 disk volumes.

The FlashCopy specifications on the utility control statement override any specifications for FlashCopy that are defined by using the Db2 subsystem parameters. If the FlashCopy subsystem parameters specify the use of FlashCopy as the default behavior of this utility, the FLASHCOPY option can be omitted from the utility control statement.

Important: If the input data set is less than one cylinder, FlashCopy technology might not be used for copying the objects regardless of the FLASHCOPY settings. The copy is performed by IDCAMS if FlashCopy is not used.
NO
Specifies that no FlashCopy is made. NO is the default value for FLASHCOPY.
YES
Specifies that FlashCopy technology is used to copy the object.
Important: Under the following circumstances, the COPY utility might not use FlashCopy even though YES is specified:
  • FlashCopy Version 2 disk volumes are not available
  • The source tracks are already the target of a FlashCopy operation
  • The target tracks are the source of a FlashCopy operation
  • The maximum number of relationships for the copy is exceeded
In the event that FlashCopy is not used, the LOAD utility uses traditional I/O methods to copy the object, which can result in longer than expected execution time.
CONSISTENT
When SHRLEVEL CHANGE is specified, specifies that FlashCopy technology is used to copy the object and that any uncommitted work included in the copy is backed out of the copy to make the copy consistent. If SHRLEVEL NONE is specified, the image copy is already consistent and you do not need to specify CONSISTENT.

A consistent FlashCopy image copy can by used for recovery without also requiring a sequential format image copy.

Specifying FLASHCOPY CONSISTENT requires additional time and system resources during utility processing, because the utility must read the logs and apply the changes to the image copy. Similarly, recovering from a consistent FlashCopy image copy also requires additional time and system resources to read the logs and reapply work that was previously backed out.

Restriction: CONSISTENT cannot be specified when copying objects that have been defined with the NOT LOGGED attribute. If CONSISTENT is specified for an object that is defined with the NOT LOGGED attribute, the utility does not make a copy of the object and issues message DSNU076I with return code 8.
FCCOPYDDN
Specifies the template to be used to create the FlashCopy image copy data set names. If a value is not specified for FCCOPYDDN on the LOAD control statement when FlashCopy is used, the value specified on the FCCOPYDDN subsystem parameter determines the template to be used.
(template-name)
The data set names for the FlashCopy image copy are allocated according to the template specification. For table space or index space level FlashCopy image copies, because a data set is allocated for each partition or piece, ensure that the data set naming convention in the template specification is unique enough. Use the &DSNUM variable, which resolves to a partition number or piece number at execution time.
STATISTICS
Specifies the gathering of statistics for a table space, index, or both; the statistics are stored in the Db2 catalog.

If you specify the STATISTICS keyword with no other statistics-spec or correlation-stats-spec options, the utility gathers only table space statistics. Statistics are collected on a base table space, but not on a LOB table space or XML table space.

Restrictions:
  • If you specify STATISTICS for encrypted data, Db2 might not provide useful statistics on this data.
  • You cannot specify STATISTICS if the named table is a table clone.

All tables identified by the STATISTICS TABLE keyword must belong to the table space of the specified table or tables in the INTO TABLE option of the LOAD utility control statement.

TABLE
Specifies the table for which column information is to be gathered.

Do not specify STATISTICS TABLE table-name with the LIST keyword. Instead, specify STATISTICS TABLE (ALL).

(ALL)
Specifies that information is to be gathered for all columns of all tables in the table space.
(table-name)
Specifies the tables for which column information is to be gathered. If you omit the qualifier, the user identifier for the utility job is used. Enclose the table name in quotation marks if the name contains a blank.

If you specify more than one table, you must repeat the TABLE option. Multiple TABLE options must be specified entirely before or after any INDEX keyword that may also be specified. For example, the INDEX keyword may not be specified between any two TABLE keywords.

SAMPLE integer
Indicates the percentage of rows to be sampled when collecting statistics on non-leading-indexed columns of an index or non-indexed columns. You can specify any value from 1 through 100.

The default value is 25. The SAMPLE option is not allowed for LOB table spaces.

Start of changeUSE PROFILEEnd of change
Start of changeSpecifies a stored statistics profile that is used to gather statistics for a table. The statistics profile is created using the SET PROFILE option and is updated using the UPDATE PROFILE option.

The column, column group, and index specifications are not allowed as part of the control statement, but are used when stored in the statistics profile.

If no profile exists for the specified table, default statistics are collected:
  • When a table name is not specified, TABLE ALL INDEX ALL is used for the profile specification.
  • When a table name is specified, COLUMN ALL INDEX ALL is used for the profile specification.

Start of changeWhen you specify USE PROFILE, the profile options are included in SYSPRINT in message DSNU1376I.End of change

Start of changeFL 507 Additionally, Db2 deletes existing statistics that are not included in the profile. All frequency, keycard, and histogram statistics that are not part of the profile are deleted from the catalog. These statistics are deleted for only the specified table or partition. Statistics are not deleted from catalog history tables. If you specify UPDATE NONE or UPDATE SPACE, no statistics are deleted. End of change

End of change
COLUMN
Specifies columns for which column information is to be gathered.
You can specify this option only if you specify a particular table for which statistics are to be gathered (TABLE (table-name)). If you specify particular tables and do not specify the COLUMN option, the default, COLUMN(ALL), is used. If you do not specify a particular table when using the TABLE option, you cannot specify the COLUMN option; however, COLUMN(ALL) is assumed.
(ALL)
Specifies that statistics are to be gathered for all columns in the table.
(column-name, …)
Specifies the columns for which statistics are to be gathered.

You can specify a list of column names; the maximum is 10. If you specify more than one column, separate each name with a comma.

INDEX
Specifies indexes for which information is to be gathered. Column information is gathered for the first column of the index. All the indexes must be associated with the same table space, which must be the table space that is specified in the TABLESPACE option.

Do not specify STATISTICS INDEX index-name with the LIST keyword. Instead, specify STATISTICS INDEX (ALL).

(ALL)
Specifies that the column information is to be gathered for all indexes that are defined on tables that are contained in the table space.
(index-name)
Specifies the indexes for which information is to be gathered. Enclose the index name in quotation marks if the name contains a blank.
COLGROUP (column-name, ...)
Indicates that the specified set of columns are treated as a group. This option enables inline statistics to collect a cardinality value on the specified column group. Inline statistics ignores COLGROUP when processing XML table spaces and indexes.

When you specify the COLGROUP keyword, inline statistics collects correlation statistics for the specified column group. If you want inline statistics to also collect distribution statistics, specify the FREQVAL option with COLGROUP.

(column-name, ...) specifies the names of the columns that are part of the column group.

Start of changeWhen you define a column group on a single column, you can potentially improve RUNSTATS performance by specifying the STATCLGMEMSRT option or changing the value of the STATCLGSRT subsystem parameter. You can use these options to avoid column group sorts by external sort programs.End of change

To specify more than one column group, repeat the COLGROUP option.

Restriction: The length of the COLGROUP value cannot exceed the maximum length of the COLVALUE column in the SYSIBM.SYSCOLDIST catalog table.
FREQVAL
Indicates, when specified with the COLGROUP option, that frequency statistics are also to be gathered for the specified group of columns. (COLGROUP indicates that cardinality statistics are gathered.) One group of statistics is gathered for each column. You must specify COUNT integer with COLGROUP FREQVAL. Start of changeThe utility ignores FREQVAL MOST/LEAST/BOTH when processing XML table spaces .End of change
COUNT integer
Start of changeIndicates the number of frequently occurring values to be collected from the specified column group. For example, COUNT 20 means that Db2 collects 20 frequently occurring values from the column group. Start of changeWhen the COUNT keyword is not specified, the utility automatically determines the count value and collects the most frequently occurring values.End of change Specifying a value of 1000 or more can increase the prepare time for some SQL statements. Additionally, specifying a very large COUNT value will use a large amount of storage, which can cause storage constraints during utility execution on partitioned objects with hundreds of partitions.
MOST
Indicates that the utility is to collect the most frequently occurring values for the specified set of columns when COLGROUP is specified.
BOTH
Indicates that the utility is to collect the most and the least frequently occurring values for the specified set of columns when COLGROUP is specified.
LEAST
Indicates that the utility is to collect the least frequently occurring values for the specified set of columns when COLGROUP is specified.
End of change
HISTOGRAM
Indicates, when specified with the COLGROUP option, that histogram statistics are to be gathered for the specified group of columns. Inline statistics ignore HISTOGRAM when processing XML table spaces and indexes.

Histogram statistics that you collect through inline statistics are not the same as histogram statistics that you collect through RUNSTATS. Histogram statistics that you collect with inline statistics are only rough estimates. To obtain more exact statistics, use RUNSTATS.

NUMQUANTILES integer
Indicates how many quantiles that the utility collects. The integer value must be greater than or equal to one. The number of quantiles that you specify must never exceed the total number of distinct values in the column or the column group. The maximum number of quantiles is 100.

When the NUMQUANTILES keyword is omitted, NUMQUANTILES takes a default value of 100. Based on the number of records in the table, the number of quantiles is readjusted down to an optimal number.

KEYCARD
The KEYCARD option is deprecated in the utility control statement and no longer needs to be specified to collect cardinality statistics on the values in the key columns of an index.

When the STATISTICS and INDEX options are specified, the utility always collects all of the distinct values in all of the 1 to n key column combinations in an index.n is the number of columns in the index. With the deprecation of KEYCARD, this functionality cannot be disabled.

The utility tolerates the specification of the KEYCARD option. The utility does not issue any messages if the control statement includes or excludes the KEYCARD option when STATISTICS and INDEX are specified.

FREQVAL
Controls the collection of frequent-value statistics.If you specify FREQVAL, it must be followed by the NUMCOLS keyword.
NUMCOLS
Indicates the number of key columns that are to be concatenated together when collecting frequent values from the specified index. Specifying '3' means that frequent values are to be collected on the concatenation of the first three key columns. The default value is 1, which means that Db2 collects frequent values on the first key column of the index.
COUNT
Indicates the number of frequent values that are to be collected. Specifying '15' means that Db2 collects 15 frequent values from the specified key columns. Start of changeIf the COUNT keyword is not specified, Db2 collects statistics for an automatically determined number of frequently occurring values.End of change
HISTOGRAM
Indicates that histogram statistics are requested for the specified index.
NUMCOLS
The number of key columns that are to be concatenated when collecting histogram statistics from the specified index.
NUMQUANTILES
The integer values that follows NUMQUANTILES indicates the number quantiles are requested. The integer value must be greater than or equal to 1.

Histogram statistics can be collected only on keys with the same order if the specified key columns for histogram statistics are of mixed order, a DSNU633I warning message is issued.

Related information:
REPORT
Specifies whether a set of messages is to be generated to report the collected statistics.
NO
Indicates that the set of messages is not to be sent as output to SYSPRINT.
YES
Indicates that the set of messages is to be sent as output to SYSPRINT. The generated messages are dependent on the combination of keywords (such as TABLESPACE, INDEX, TABLE, and COLUMN) that are specified with the RUNSTATS utility. However, these messages are not dependent on the specification of the UPDATE option. REPORT YES always generates a report of SPACE and ACCESSPATH statistics.
UPDATE
Indicates whether the collected statistics are to be inserted into the catalog tables. UPDATE also allows you to select statistics that are used for access path selection or statistics that are used by database administrators.
ALL
Indicates that all collected statistics are to be updated in the catalog.
ACCESSPATH
Indicates that only the catalog table columns that provide statistics that are used for access path selection are to be updated.
SPACE
Indicates that only the catalog table columns that provide statistics to help database administrators assess the status of a particular table space or index are to be updated.
NONE
Indicates that no catalog tables are to be updated with the collected statistics. This option is valid only when REPORT YES is specified.
Start of changeSTATCLGMEMSRT integerEnd of change
Start of changeSpecifies the amount of memory that the utility can use for sorting records when collecting statistics on a single column that is defined with the COLGROUP option. Use STATCLGMEMSRT to avoid column group sorts by an external sort program, which can negatively affect the performance of statistics collection.

integer specifies the number of megabytes of memory space that the utility can use for an in-memory sort. Valid values are 0 to 4096. If the amount of space that is needed for the sort exceeds the integer value, the utility invokes a sort program. If you specify 0, the utility automatically invokes a sort program.

The amount of space that is needed for the column group sort depends on the following factors:

  • The number of column groups for which the utility is collecting statistics
  • The length of the single-column column group
  • The number of distinct values in the column (cardinality)

The value of STATCLGMEMSRT overrides the value of the STATCLGSRT subsystem parameter.

End of change
Start of change INVALIDATECACHE End of change
Start of changeIndicates whether statements in the dynamic statement cache are invalidated as a result of the inline statistics collection. This option does not prevent the utility from invalidating cached statements for other reasons.
YES
Statements in the dynamic cache are invalidated for the objects that are specified in the job statement.
NO
Statements in the dynamic cache are not invalidated by the collection of inline statistics for the objects that are specified in the job statement. However, cached statements might be invalidated by the utility for reasons other than the inline statistics, such as when the utility resolves objects in restricted states or applies pending ALTER operations.
End of change
HISTORY
Specifies that all catalog table inserts or updates to the catalog history tables are to be recorded.

The default value is the value that is specified in the STATISTICS HISTORY field on panel DSNTIP6.

ALL
Indicates that all collected statistics are to be updated in the catalog history tables.
ACCESSPATH
Indicates that only the catalog history table columns that provide statistics that are used for access path selection are to be updated.
SPACE
Indicates that only space-related catalog statistics are to be updated in catalog history tables.
NONE
Indicates that no catalog history tables are to be updated with the collected statistics.
FORCEROLLUP
Specifies whether aggregation or rollup of statistics is to take place when RUNSTATS is executed even if statistics have not been gathered on some partitions; for example, partitions have not had any data loaded. Aggregate statistics are used by the optimizer to select the best access path.
YES
Indicates that forced aggregation or rollup processing is to be done, even though some partitions might not contain data.
NO
Indicates that aggregation or rollup is to be done only if data is available for all partitions.
If data is not available for all partitions, DSNU623I message is issued if the installation value for STATISTICS ROLLUP on panel DSNTIP6 is set to NO.
KEEPDICTIONARY
Prevents the LOAD utility from building a new compression dictionary. LOAD retains the current compression dictionary and uses it for compressing the input data. This option eliminates the cost that is associated with building a new dictionary.

The KEEPDICTIONARY keyword is ignored for XML table spaces. If you specify REPLACE, any existing dictionary for the XML table space or partition is deleted. If you do not specify REPLACE, any existing dictionary for the XML table space or partition is saved.

Db2 ignores the KEEPDICTIONARY option during execution of a REORG or LOAD REPLACE that changes the table space from basic row format to reordered row format.

Start of changeThis keyword is valid only if the table space that is being loaded is defined with compression.End of change

If the table space or partition is empty, Db2 performs one of these actions:

  • Db2 builds a dictionary if a compression dictionary does not exist, but only if the table space is not a simple table space.
  • Db2 keeps the dictionary if a compression dictionary exists.

If RESUME NO and REPLACE are specified when the table space or partition is not empty, Db2 performs the same actions as it does when the table space or partition is empty.

If the table space or partition is not empty and RESUME YES is specified, Db2 performs one of these actions:

  • Db2 does not build a dictionary if a compression dictionary does not exist.
  • Db2 keeps the dictionary if a compression dictionary exists.
Note: You must use KEEPDICTIONARY to ensure that the compression dictionary is maintained.
REUSE
Specifies (when used with REPLACE) that LOAD is to logically reset and reuse Db2-managed data sets without deleting and redefining them. If you do not specify REUSE, Db2 deletes and redefines Db2-managed data sets to reset them.

REUSE must be accompanied by REPLACE to do the logical reset for all data sets. However, if you specify REUSE for the table space and REPLACE only at the partition level, only the replaced partitions are logically reset.

If a data set has multiple extents, the extents are not released if you specify the REUSE parameter.

LOG
Indicates whether logging occurs during the RELOAD phase of the load process for LOAD SHRLEVEL NONE and LOAD RESUME YES SHRLEVEL CHANGE execution. For LOAD RESUME YES SHRLEVEL REFERENCE, and LOAD REPLACE SHRLEVEL REFERENCE, LOG NO always applies.
YES
Specifies normal logging during the load process. All records that are loaded are logged. If the table space has the NOT LOGGED attribute, Db2 does the LOAD with no logging.
NO
Specifies no logging of data during the load process. If the table space has the LOGGED attribute, the NO option sets the 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 by SQL until the restriction is removed. For ways to remove the restriction, see Resetting COPY-pending status.

If you load a single partition of a partitioned table space and the table space has a secondary index, some logging might occur during the build phase as Db2 logs any changes to the index structure. This logging allows recoverability of the secondary index in case an abend occurs, and it also allows concurrency.

Db2 treats table spaces that were created as NOT LOGGED as if you specified LOG NO. If you specify LOG NO without specifying COPYDDN, the base table space is placed in COPY-pending status. If XML columns are nullable and not loaded, only the base table space is placed in COPY-pending status.

A LOB table space affects logging while Db2 loads a LOB column regardless of whether the LOB table space was defined with LOG YES or LOG NO.

NOCOPYPEND
Specifies that LOAD is not to set the table space in the COPY-pending status, even though LOG NO was specified. A NOCOPYPEND specification does not turn on or change any informational COPY-pending (ICOPY) status for indexes. A NOCOPYPEND specification will not turn off any COPY-pending status that was set prior to the LOAD. Normal completion of a LOAD LOG NO NOCOPYPEND job returns a 0 code if no other errors or warnings exist.

Db2 ignores a NOCOPYPEND specification if you also specified COPYDDN to make a local-site inline image copy during the LOAD. If the table space has the NOT LOGGED attribute, NOCOPYPEND is ignored.

Attention: Specify the NOCOPYPEND option only if the data in the table space can be easily re-created by another LOAD job if the data is lost. If you do not take an image copy following the LOAD, you cannot recover the table space by using the RECOVER utility, and you might lose data.
WORKDDN (ddname1,ddname2)
Specifies the DD statements for the temporary work file for sort input and sort output. Temporary work files for sort input and output are required if the LOAD involves tables with indexes.

ddname1 is the DD name for the temporary work file for sort input. The default value is SYSUT1.

ddname2 is the DD name for the temporary work file for sort output. The default value is SORTOUT.

The WORKDDN keyword specifies either a DD name or a TEMPLATE name specification from a previous TEMPLATE control statement. If utility processing detects that the specified name is both a DD name in the current job step and a TEMPLATE name, the utility uses the DD name. For more information about TEMPLATE specifications, see TEMPLATE.

SORTKEYS
Specifies that index keys are to be sorted in parallel during the RELOAD and SORTBLD phase to improve performance. This action is the default behavior if LOAD can estimate the input file size with at least two or more indexes being built and SORTKEYS NO is not specified.
integer
Specifies an integer to provide an estimate of the number of index keys that are to be sorted. Integer must be a positive integer between 0 and 562 949 953 421 311.
NO
Indicates that the default SORTKEYS behavior is to be turned off.

For sequential data sets on disk, LOAD attempts to compute the number of records being processed based on the input data set size for SORTKEYS processing when neither SORTKEYS n nor NUMRECS n are specified. If SORTKEYS n or NUMRECS n is specified, LOAD uses the provided value as the estimated number of records to be processed.

If the NUMRECS keyword is specified at the table level in the same LOAD statement, you cannot specify an integer value on the SORTKEYS keyword. To turn off parallel sorts, you can specify SORTKEYS NO when the NUMRECS keyword is specified.

Start of changeWhen SORTKEYS is specified, LOAD attempts to do a parallel index build when it can estimate the amount of data or keys to be sorted. In the case of a single index case, LOAD tries to pipe the keys by running the index sort concurrent to reload processing. End of change

Start of changeWhen SORTKEYS is not specified and LOAD cannot determine a valid estimate based on NUMRECS specification, then, in the case of a single index, LOAD does not use parallel index build or key piping but relies on a separate SORT and BUILD phase to process the index keys. End of change

Related information:
FORMAT
Identifies the format of the input record. If you use FORMAT UNLOAD, FORMAT INTERNAL, or FORMAT SQL/DS, it uniquely determines the format of the input, and no field specifications are allowed in an INTO TABLE option.
If you omit FORMAT, the format of the input data is determined by the rules for field specifications.If you specify FORMAT DELIMITED, the format of the input data is determined by the rules that are described in Delimited file format.
UNLOAD
Specifies that the input record format is compatible with the Db2 unload format. (The Db2 unload format is the result of REORG with the UNLOAD ONLY option.)

Input records that were unloaded by the REORG utility are loaded into the tables from which they were unloaded, if an INTO TABLE option specifies each table. Do not add columns or change column definitions of tables between the time you run REORG UNLOAD ONLY and LOAD FORMAT UNLOAD.

Any WHEN clause on the LOAD FORMAT UNLOAD statement is ignored; Db2 reloads the records into the same tables from which they were unloaded. Not allowing a WHEN clause with the FORMAT UNLOAD clause ensures that the input records are loaded into the proper tables. Input records that cannot be loaded are discarded.

If the DCB RECFM parameter is specified on the DD statement for the input data set, and the data set format has not been modified since the REORG UNLOAD (ONLY) operation, the record format must be variable (RECFM=V).

SQL/DS
Specifies that the input record format is compatible with the SQL/DS unload format. The data type of a column in the table that is to be loaded must be the same as the data type of the corresponding column in the SQL/DS table.

If the SQL/DS input contains rows for more than one table, the WHEN clause of the INTO TABLE option indicates which input records are to be loaded into which Db2 table.

LOAD cannot load SQL/DS strings that are longer than the Db2 limit.

SQL/DS data that has been unloaded to disk under DB2® Server for VSE & VM resides in a simulated z/OS-type data set with a record format of VBS. Consider this format when transferring the data to another system that is to be loaded into a Db2 table (for example, the DB2 Server for VSE & VM. FILEDEF must define it as a z/OS-type data set). Processing the data set as a standard CMS file puts the SQL/DS record type field at the wrong offset within the records; LOAD is unable to recognize them as valid SQL/DS input.

INTERNAL
Specifies that the input record format is in Db2 internal format. Db2 internal format is the format that is produced by running UNLOAD with the FORMAT INTERNAL option.
Attention: Start of changeFORMAT INTERNAL is a performance option that bypasses all data validity checks. Incorrect use might result in serious problems, including data overlays and Db2 abnormal terminations. Those problems might occur during LOAD processing or when the target table is accessed after it is loaded.End of change
When FORMAT INTERNAL is specified:
  • LOAD ignores any field specifications in the LOAD control statement.
  • LOAD does no data conversion.
Restrictions:
  • Start of changeThe definition of the table from which the input data was unloaded must match the definition of the table into which the data is loaded exactly. If a column of the table into which data is loaded has a field procedure, the corresponding column in the table from which the data was unloaded must also have that field procedure.End of change
  • The input data must be in decompressed format.
  • LOAD can load only one table at a time when FORMAT INTERNAL is specified.
  • LOAD does not populate LOB or XML columns when FORMAT INTERNAL is specified. LOAD puts the base table space in advisory CHECK-pending status.
  • FORMAT INTERNAL cannot be specified with any of the following options:
    • ASCII
    • CCSID
    • CONTINUEIF
    • DECFLOAT_ROUNDMODE
    • EBCDIC
    • FLOAT
    • OVERRIDE(IDENTITY)
    • IGNOREFIELDS
    • INCURSOR
    • NOSUBS
    • SHRLEVEL CHANGE
    • UNICODE
    • WHEN
DELIMITED
Specifies that the input data file is in a delimited format. When data is in a delimited format, all fields in the input data set are character strings or external numeric values. In addition, each column in a delimited file is separated from the next column by a column delimiter character.

For each of the delimiter types that you can specify, you must ensure that the delimiter character is specified in the code page of the source data. The delimiter character can be specified as either a character or hexadecimal constant. For example, to specify '#' as the delimiter, you can specify either COLDEL '#' or COLDEL X'23'. If the utility statement is coded in a character type that is different from the input file, such as a utility statement that is coded in EBCDIC and input data that is in Unicode, you should specify the delimiter character in the utility statement as a hexadecimal constant, or the result can be unpredictable.

You cannot specify the same character for more than one type of delimiter (COLDEL, CHARDEL, and DECPT). If the input data includes external date, time, and timestamp formats, the separators cannot use the same character that is used for a delimiter. For more information about delimiter restrictions, see Loading delimited files.

Unicode input data for FORMAT DELIMITED must be UTF-8, CCSID 1208.

If you specify the FORMAT DELIMITED option, you cannot use any of the following options:

  • CONTINUEIF
  • INCURSOR
  • Multiple INTO TABLE statements
  • WHEN
  • Start of changeCCSID in a field specificationEnd of change

Also, LOAD ignores any specified POSITION statements within the LOAD utility control statement.

For more information about using delimited output and delimiter restrictions, see Loading delimited files. For more information about delimited files see Delimited file format.

COLDEL coldel
Specifies the column delimiter that is used in the input file. The default value is a comma (,). For most ASCII and UTF-8 data, this value is X'2C', and for most EBCDIC data, this value is a X'6B'.
CHARDEL chardel
Specifies the character string delimiter that is used in the input file. The default value is a double quotation mark (). For most ASCII and UTF-8 data, this value is X'22', and for most EBCDIC data, this value is X'7F'.

To delimit character strings that contain the character string delimiter, repeat the character string delimiter where it is used in the character string. LOAD interprets any pair of character delimiters that are found between the enclosing character delimiters as a single character. For example, the phrase “what a ““nice warm”” day” is interpreted as what a “nice warm” day. The LOAD utility recognizes these character delimiter pairs for only CHAR, VARCHAR, and CLOB fields.

Character string delimiters are required only when the string contains the CHARDEL character. However, you can put the character string delimiters around other character strings. Data that has been unloaded in delimited format by the UNLOAD utility includes character string delimiters around all character strings.

DECPTdecpt
Specifies the decimal point character that is used in the input file. The default value is a period (.). For most ASCII and UTF-8 data, this value is X'2E', and for most EBCDIC data, this value is X'4B'.
Note: If you use an application defaults load module (either DSNHDECP, which is the default, or a user-specified application defaults load module), ensure that the specified decimal value is the same as the decimal value that is used in the input data. You must specify the decimal value to match the decimal value that is used in the input data.
SPANNED
Indicates whether records are to be loaded from a VBS data set in spanned record format.
YES
Indicates that the LOAD utility is to load data from spanned records.

The input data set must be in spanned record format and all LOB and XML data must be at the end of the record.

You must provide a field specification list with all LOB and XML fields at the end of the record. For LOB and XML columns, specify POSITION(*).

If you specify FORMAT SPANNED YES, do not reference LOB or XML data in the field-selection-criterion of a WHEN clause.

You cannot specify the INCURSOR option with SPANNED YES.

If you specify FORMAT SPANNED YES, the LOAD utility does not use parallel processing.

NO
Indicates that the LOAD utility is not to load data in spanned record format.
FLOAT
Specifies that LOAD is to expect the designated format for floating point numbers.
(S390)
Specifies that LOAD is to expect that floating point numbers are provided in System/390® hexadecimal floating point (HFP) format. (S390) is the format that Db2 stores floating point numbers in. It is also the default value if you do not explicitly specify the FLOAT keyword.
(IEEE)
Specifies that LOAD is to expect that floating point numbers are provided in IEEE binary floating point (BFP) format.

When you specify FLOAT(IEEE), Db2 converts the BFP data to HFP format as the data is being loaded into the Db2 table. If a conversion error occurs while Db2 is converting from BFP to HFP, Db2 places the record in the discard file.

FLOAT(IEEE) is mutually exclusive with any specification of the FORMAT keyword. If you specify both FLOAT(IEEE) and FORMAT, Db2 issues message DSNU070I.

BFP format is sometimes called IEEE floating point.

EBCDIC
Specifies that the input data file is EBCDIC. The default is EBCDIC.
ASCII
Specifies that the input data file is ASCII. Numeric, date, time, and timestamp internal formats are not affected by the ASCII option.
UNICODE
Specifies that the input data file is Unicode. The UNICODE option does not affect the numeric internal formats.
CCSID
Specifies up to three coded character set identifiers (CCSIDs) for the input file. The first value specifies the CCSID for SBCS data that is found in the input file, the second value specifies the CCSID for mixed DBCS data, and the third value specifies the CCSID for DBCS data. If any of these values is specified as 0 or omitted, the CCSID of the corresponding data type in the input file is assumed to be the same as the installation default CCSID. If the input data is EBCDIC, the omitted CCSIDs are assumed to be the EBCDIC CCSIDs that are specified at installation, and if the input data is ASCII, the omitted CCSIDs are assumed to be the ASCII CCSIDs that are specified at installation. If the CCSIDs of the input data file do not match the CCSIDs of the table that is being loaded, the input data is converted to the table CCSIDs before being loaded.

integer is any valid CCSID specification.

If the input data is Unicode, the default CCSID values are the Unicode CCSIDs that are specified at system installation.

NOSUBS
Specifies that LOAD is not to accept substitution characters in a string.

Place a substitution character in a string when that string is being converted from ASCII to EBCDIC, or when the string is being converted from one CCSID to another. For example, this substitution occurs when a character (sometimes referred to as a code point) that exists in the source CCSID (code page) does not exist in the target CCSID (code page).

When you specify the NOSUBS option and the LOAD utility determines that a substitution character has been placed in a string as a result of a conversion, it performs one of the following actions:

  • If discard processing is active: Db2 issues message DSNU310I and places the record in the discard file.
  • If discard processing is not active: Db2 issues message DSNU334I, and the utility abnormally terminates.
ENFORCE
Specifies whether LOAD is to enforce check constraints and referential constraints, except informational referential constraints, which are not enforced.
CONSTRAINTS
Indicates that constraints are to be enforced. If LOAD detects a violation, it deletes the errant row and issues a message to identify it. If you specify this option and referential constraints exist, sort input and sort output data sets must be defined.

Start of changeYou cannot specify ENFORCE CONSTRAINTS with SHRLEVEL REFERENCE.End of change

NO
Indicates that constraints are not to be enforced. This option places the target table space in the CHECK-pending status if at least one referential constraint or check constraint is defined for the table.
Start of changeNOCHECKPENDEnd of change
Start of changeSpecifies that LOAD does not set the target table space in the CHECK-pending status if at least one referential constraint or check constraint is defined for the table. This option applies only when ENFORCE NO is specified or enforced by the LOAD utility. A NOCHECKPEND specification does not remove any CHECK-pending status that was set prior to the LOAD.End of change
ERRDDN ddname
Specifies the DD statement for a work data set that is being used during error processing. Information about errors that are encountered during processing is stored in this data set. A SYSERR data set is required if you request discard processing.

ddname is the DD name.

The default value is SYSERR.

The ERRDDN keyword specifies either a DD name or a TEMPLATE name specification from a previous TEMPLATE control statement. If utility processing detects that the specified name is both a DD name in the current job step and a TEMPLATE name, the utility uses the DD name. For more information about TEMPLATE specifications, see TEMPLATE.

MAPDDN ddname
Specifies the DD statement for a work data set that is to be used during error processing. The work data set is used to correlate the identifier of a table row with the input record that caused an error. A SYSMAP data set is required if you specify ENFORCE CONSTRAINTS and the tables have a referential relationship, or if you request discard processing when loading one or more tables that contain unique indexes or extended indexes.

ddname is the DD name.

The default value is SYSMAP.

The MAPDDN keyword specifies either a DD name or a TEMPLATE name specification from a previous TEMPLATE control statement. If utility processing detects that the specified name is both a DD name in the current job step and a TEMPLATE name, the utility uses the DD name. For more information about TEMPLATE specifications, see TEMPLATE.

DISCARDDN ddname
Specifies a DD name or a template name for a discard data set that is to hold copies of records that are not loaded (for example, if they contain conversion errors). The discard data set also holds copies of records that are loaded and then removed (because of unique index errors, referential or check constraint violations, or index evaluation errors). Flag input records for discarding during RELOAD, INDEXVAL, and ENFORCE phases. However, the discard data set is not written until the DISCARD phase when the flagged records are copied from the input data set to the discard data set. The discard data set must be a sequential data set that can be written to by BSAM, with the same record format, record length, and block size as the input data set.

ddname is the DD name.

The default value is SYSDISC.

If you omit the DISCARDDN option, the utility application program saves discarded records only if a SYSDISC DD statement is in the JCL input.

The DISCARDDN keyword is not supported if you use a BatchPipes® file as an input to LOAD, using INDDN name for TEMPLATE SUBSYS.

DISCARDS integer
Specifies the maximum number of source records that are to be written on the discard data set. integer can range 0 - 2147483647. If the discard maximum is reached, LOAD abnormally terminates, the discard data set is empty, and you cannot see which records were discarded. You can either restart the job with a larger limit, or terminate the utility.

DISCARDS 0 specifies that you do not want to set a maximum value. The entire input data set can be discarded.

The default value is 0.

LOAD task parallelism: Start of changeIf a discard maximum is specified, that limit applies separately to each LOAD task running on a target system.End of change
SORTDEVT device-type
Specifies the device type for temporary data sets that are to be dynamically allocated by the external sort program. You can specify any disk device type that is acceptable to the DYNALLOC parameter of the SORT or OPTION options for the sort program. Tape devices are not supported by the sort program.

If you omit SORTDEVT and a sort is required, you must provide the DD statements that the sort application program needs for the temporary data sets.

A TEMPLATE specification does not dynamically allocate sort work data sets. The SORTDEVT keyword controls dynamic allocation of these data sets.

SORTNUM integer
Specifies the number of temporary data sets that are to be dynamically allocated by the sort application program.

integer is the number of temporary data sets that can range from 2 to 255.

If you omit SORTDEVT, SORTNUM is ignored. If you use SORTDEVT and omit SORTNUM, no value is passed to the sort program. In this case, the sort program uses its own default.

You need at least two sort work data sets for each sort. The SORTNUM value applies to each sort invocation in the utility. For example, if three indexes, SORTKEYS is specified, there are no constraints that limit parallelism, and SORTNUM is specified as 8, a total of 24 sort work data sets are allocated for a job.

Each sort work data set consumes both above-the-line and below-the-line virtual storage, so if you specify a value for SORTNUM that is too high, the utility might decrease the degree of parallelism due to virtual storage constraints, and possibly decreasing the degree down to one, meaning no parallelism.

Important: Start of changeThe SORTNUM keyword is ignored if the IGNSORTN subsystem parameter is set to YES.End of change
CONTINUEIF
Indicates that you want to be able to treat each input record as a portion of a larger record. After CONTINUEIF, write a condition in one of the following forms:
(start:end) = X'byte-string'
(start:end) = 'character-string'

If the condition is true in any record, the next record is concatenated with it before loading takes place. You can concatenate any number of records into a larger record, up to a maximum size of 32767 bytes.

Character-string constants should be specified in LOAD utility control statements in the character set that matches the input data record. Specify EBCDIC constants in the LOAD control statement if your data is in EBCDIC and specify UNICODE constants if your data is in UNICODE. You may also code the CONTINUEIF condition using the hexadecimal form. For example, use (1:1)=X'31' rather than (1:1)='1'.
(start:end)
Specifies column numbers in the input record; the first column of the record is column 1. The two numbers tell the starting and ending columns of a continuation field in the input record.

Other field position specifications (such as those for WHEN, POSITION, or NULLIF) refer to the field position within the final assembled load record, not within the input record.

The continuation field is removed from the input record and is not part of the final load record.

If you omit :end, Db2 assumes that the length of the continuation field is the length of the byte string or character string. If you use :end, and the length of the resulting continuation field is not the same as the length of the byte string or character string, the shorter string is padded. Character strings are padded with blanks. Hexadecimal strings are padded with zeros.

X'byte-string'
Specifies a string of hexadecimal characters. This 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. For example, the following CONTINUEIF specification indicates that for any input records that have a value of X'FF'in column 72, LOAD is to concatenate that record with the next input record.
CONTINUEIF (72) = X'FF'
'character-string'
Specifies a string of characters that has the same effect as X'byte-string'. For example, the following CONTINUEIF specification indicates that for any input records that have the string CC in columns 99 and 100, LOAD is to concatenate that record with the next input record.
CONTINUEIF (99:100) = 'CC'
IGNORE
Specifies that the LOAD utility ignores records that it rejects for the specified reasons. If discarding is specified, no ignored rows are loaded or written to the DISCARD data set. If discarding is not specified, ignored records do not cause the LOAD utility to terminate.

Use the record count messages in the Utility output to determine the number of records ignored.

WHEN
Specifies that records that do not satisfy the WHEN clause are ignored.
Start of changePARTEnd of change
Start of changeSpecifies that records that do not satisfy any partition being loaded are ignored.End of change
Start of changeCONVEnd of change
Start of changeSpecifies that records that cause a conversion error are ignored.End of change
Start of changeVALPROCEnd of change
Start of changeSpecifies that records that fail a validation procedure are ignored.End of change
Start of changeIDERROREnd of change
Start of changeSpecifies that records that have an identity column value that is out of range are ignored.End of change
Start of changeDUPKEYEnd of change
Start of changeSpecifies that records that cause a duplicate key error are ignored.End of change

Use the record count messages in the Utility output to determine the number of records ignored.

DECFLOAT_ROUNDMODE
Specifies the rounding mode to use when DECFLOATs are manipulated. The following rounding modes are supported:
ROUND_CEILING
Round toward +infinity. The discarded digits are removed if they are all zero or if the sign is negative. Otherwise, the result coefficient should be incremented by 1 (rounded up).
ROUND_DOWN
Round toward 0 (truncation). The discarded digits are ignored.
ROUND_FLOOR
Round toward -infinity. The discarded digits are removed if they are all zero or positive. Otherwise, the sign is negative and the result coefficient should be incremented by 1 (rounded up).
ROUND_HALF_DOWN
Round to the nearest number. If equidistant, round down. If the discarded digits are greater than 0.5, the result coefficient should be incremented by 1 (rounded up). The discarded digits are ignored if they are 0.5 or less.
ROUND_HALF_EVEN
Round to the nearest number. If equidistant, round so that the final digit is even. If the discarded digits are greater than .05, the result coefficient should be incremented by 1 (rounded up). The discarded digits are ignored if they are less than 0.5. If the result coefficient is .05 and the rightmost digit is even, the result coefficient is not altered. If the result coefficient is .05 and the rightmost digit is odd, the result coefficient should be incremented by 1 (rounded up).
ROUND_HALF_UP
Round to nearest. If equidistant, round up. If the discarded digits are greater than or equal to 0.5, the result coefficient should be incremented by 1 (rounded up). Otherwise the discarded digits are ignored.
ROUND_UP
Round away from 0. If all of the discarded digits are 0, the result is unchanged. Otherwise, the result coefficient should be incremented by 1 (rounded up).

If you do not specify DECFLOAT_ROUNDMODE, the LOAD statement uses the DFPDEFDM value in the application defaults load module as the default value. The application defaults load module is either DSNHDECP, which is the default, or a user-specified application defaults load module.

OVERRIDE
Allows unloaded data to be reloaded into the specified types of GENERATED ALWAYS columns.
SYSTEMPERIOD
Allows unloaded data to be reloaded into a GENERATED ALWAYS row-begin or row-end column. Row-begin and row-end columns are intended to be used in the definition of a system period, but the period does not need to exist when the SYSTEMPERIOD keyword is specified.

If you specify OVERRIDE(SYSTEMPERIOD) and include input field specifications in the LOAD statement, both the row-begin and row-end columns that can be used to define a system period must be specified. In the specification for these columns, the NULLIF and DEFAULTIF options are not allowed.

IDENTITY
Allows unloaded data to be reloaded into a GENERATED ALWAYS identity column.

If you specify OVERRIDE(IDENTITY) and include input field specifications in the LOAD statement, the identity column must be specified. In the specification for this column, the NULLIF and DEFAULTIF options are not allowed.

Specify OVERRIDE(IDENTITY) when you want to run LOAD with the INTO TABLE PART clause and a generated identity column is part of the partitioning index. (The generated identity column can be defined as GENERATED ALWAYS or GENERATED BY DEFAULT.)

TRANSID
Allows unloaded data to be reloaded into a GENERATED ALWAYS column that is defined as a transaction-start-ID column.
NONDETERMINISTIC
Allows unloaded data to be reloaded into a GENERATED ALWAYS column that is defined by a non-deterministic expression.
Start of changeROWCHANGEEnd of change
Start of changeAllows data to be loaded into a row change timestamp column that is defined as GENERATED ALWAYS.

Specify OVERRIDE(ROWCHANGE) when you are loading a table with data that was previously unloaded and you want to preserve the timestamps in that data.

Restriction: You cannot specify ROWCHANGE with the following options:
  • NULLIF
  • DEFAULTIF
  • FORMAT INTERNAL
End of change
Start of changeDRAIN_WAIT integerEnd of change
Start of changeSpecifies the number of seconds that the utility waits when draining the table space or index. The specified time is the aggregate time for objects that are to be reorganized. This value overrides the values that are specified by IRLMRWT and UTIMOUT subsystem parameters. Valid values for integer are from 0 to 1800. If the keyword is omitted or if a value of 0 is specified, the utility uses the value of the IRLMRWT subsystem parameter.End of change
Start of changeRETRY integerEnd of change
Start of changeSpecifies the maximum number of retries that LOAD is to attempt to drain. integer is value 0 - 255.

The default value is the value of the UTIMOUT subsystem parameter.

End of change
Start of changeRETRY_DELAY integerEnd of change
Start of changeSpecifies the minimum duration, in seconds, between retries. integer is a value 1 - 1800.
If you do not specify RETRY_DELAY, LOAD uses the smaller of the following two values:
  • DRAIN_WAIT value × RETRY value
  • DRAIN_WAIT value × 10
End of change
Start of changeSWITCHTIMEEnd of change
Start of changeSpecifies the time for the drain all in the SWITCH phase to begin. The final result and all of the time stamp calculations of SWITCHTIME are in TIMESTAMP(6). This keyword requires SHRLEVEL REFERENCE. If specified, LOAD sits in the beginning of SWITCH phase and does not attempt to quiesce application access until the specified SWITCHTIME is met.
NONE
Does not specify a time for the drain in the SWITCH phase to begin. This option is the default behavior.
timestamp
Specifies the time that the drain in the SWITCH phase is to begin. This time must not have already occurred when LOAD is run.
labeled-duration-expression
Calculates the time for the drain in the SWITCH phase is to begin. The calculation is based on either CURRENT TIMESTAMP or CURRENT DATE. You can add or subtract one or more constant values to specify the switch time. This switch time must not have already occurred when LOAD is run. CURRENT TIMESTAMP and CURRENT DATE are evaluated a single time when the LOAD statement is first processed. If a list of objects is specified, the same value applies for all objects in the list.
CURRENT_DATE
Specifies that the deadline is to be calculated based on the CURRENT DATE.
CURRENT_TIMESTAMP
Specifies that the deadline is to be calculated based on the CURRENT TIMESTAMP.
WITH_TIMEZONE
Specifies that the CURRENT TIMESTAMP is compared with the time zone column. The time stamp precision of the special register CURRENT TIMESTAMP should be the same as the column time stamp precision. Otherwise, the default time stamp precision is used. The time zone of CURRENT TIMESTAMP is the value of special register CURRENT TIMEZONE. The comparison is done by comparing the Coordinated Universal Time portion of the time stamp.
constant
Indicates a unit of time and is followed by one of the seven duration keywords: YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, or MICROSECONDS. The singular forms of these words are also accepted: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MICROSECOND.
End of change
IMPLICIT_TZ
Specifies the implicit time zone to use when the timestamp value that is being loaded does not contain a time zone, and the data type of the target column is TIMESTAMP WITH TIME ZONE.
'timezone-string'
Specifies the implicit time zone value. The time zone is the difference (in hours and minutes) between local time and UTC. The range of the hour component is -12 to 14, and the minute component is 00 to 59. The time zone is specified in the form ±th:tm, with values ranging from -12:59 to +14:00.

If you do not specify the IMPLICIT_TZ option, LOAD uses the value from the IMPLICIT_TIMEZONE DECP value. For more information about this DECP value, see IMPLICIT TIME ZONE field (IMPLICIT_TIMEZONE DECP value).

Start of changeUPDMAXASSIGNEDVALEnd of change
Start of changeSpecifies whether LOAD updates the MAXASSIGNEDVAL column in SYSIBM.SYSSEQUENCES for identity columns.
YES
Specifies that LOAD updates the MAXASSIGNEDVAL column when the loaded value for the identity column is greater or smaller than the current value of the MAXASSIGNEDVAL column, depending on whether ascending or descending sequencing is in use. YES is the default value.
NO
Specifies that LOAD does not update the MAXASSIGNEDVAL column regardless of the value of the loaded identity column.
Related information:
End of change
Start of changeFORCEEnd of change
Start of changeSpecifies the action to be taken when the utility drains the table space.
NONE
No action is taken. LOAD waits for the claimers to commit during drain processing. If the drain fails, LOAD will timeout or retry. NONE is the default behavior.
READERS
Read claimers are canceled when LOAD requests DRAIN ALL on the last drain retry.
ALL
Both read and write claimers are canceled when LOAD requests DRAIN ALL or DRAIN WRITERS on the last drain retry.

When LOAD FORCE cancels threads, it performs an internal cancel operation that is similar to the cancel operation that is done by the CANCEL THREAD command.

If you specify SHRLEVEL CHANGE with FORCE, FORCE is ignored. This option is not applicable to LOAD SHRLEVEL CHANGE operations.

If you specify SHRLEVEL NONE with FORCE READERS or FORCE ALL, the blocking claimers are canceled during the only drain processing that is done at the start of the utility. In this case, drain retry processing does not occur.

End of change
Start of changeDEFINEAUXEnd of change
Start of changeSpecifies whether LOAD is to define all target auxiliary objects with the DEFINE NO attribute, regardless of whether data is to be loaded into these objects.

DEFINEAUX is applicable only when REPLACE and SHRLEVEL NONE are also specified.

NO
Specifies that LOAD takes no special action to define the target auxiliary objects. These objects are defined as needed when LOAD inserts data into them. NO is the default behavior.
YES
Specifies that LOAD defines all target LOB and XML objects and their indexes at the start of the utility execution. The underlying data sets are physically allocated for these objects regardless of whether data is loaded into these objects during the utility execution.

If LOAD DEFINEAUX YES is executed against a partition-by-growth (PBG) table space, and the utility loads enough data to cause a new partition to be added, the newly created LOB table space and auxiliary index are also defined by LOAD, regardless of whether those auxiliary objects have the DEFINE NO attribute.

End of change

INTO-TABLE-spec

The INTO-TABLE-spec control statement, with its multiple options, defines the function that the utility job performs. More than one table or partition for each table space can be loaded with a single invocation of the LOAD utility. At least one INTO TABLE statement is required for each table that is to be loaded. Each INTO TABLE statement:

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

All tables that are specified by INTO TABLE statements must belong to the same table space.

If the data is already in UNLOAD or SQL/DS format, and FORMAT UNLOAD or FORMAT SQL/DS is used on the LOAD statement, no field specifications are allowed.

Related information:

When loading XML or LOB columns from a VBS data set, the LOB and XML values need to be at the end of the record as specified by a field specification list.

INTO-TABLE-spec:

Read syntax diagramSkip visual syntax diagramINTOTABLEtable-nameIGNOREFIELDSNOIGNOREFIELDSYESPARTintegerPREFORMATresume-specINDDNSYSRECINDDNddnameINDDN(, ddname)DISCARDDNddnameINCURSORcursor-nameNUMRECSintegerWHENSQL/DS=' table-name'field selection criterion(,field specification)

resume-spec:

Read syntax diagramSkip visual syntax diagramRESUMENO1REPLACEREUSEcopy-specRESUMEYEScopy-specKEEPDICTIONARY
Notes:
  • 1 The value specified in the INTO TABLE for RESUME or REPLACE overrides the default value for LOAD RESUME.

field selection criterion:

Read syntax diagramSkip visual syntax diagramfield-name( start: end) = X' byte-string'' character-string'G' graphic-string'N' graphic-string'

field specification:

Read syntax diagramSkip visual syntax diagram field-name-spec CHARBIT ( length) strip-specCCSID 1208strip-specMIXEDstrip-specBLOBFPRESERVE WHITESPACEBINARYXMLCLOBFMIXEDPRESERVE WHITESPACECCSID 1208DBCLOBFPRESERVE WHITESPACECCSID 1200VARCHARBITCCSID 1208MIXEDBLOBFPRESERVE WHITESPACEBINARYXMLCLOBFMIXEDPRESERVE WHITESPACECCSID 1208DBCLOBFPRESERVE WHITESPACECCSID 1200strip-specGRAPHICEXTERNAL( length)strip-specCCSID 1200VARGRAPHICstrip-specCCSID 1200SMALLINTINTEGEREXTERNAL( length)BIGINTBINARY( length)strip-specVARBINARYBINARY VARYINGstrip-specdecimal-specFLOATEXTERNAL( length)DATEEXTERNAL(lengthdate-format)DATEDATE_PEXTERNAL(lengthdate-format)TIMEEXTERNAL(lengthtime-format)TIMESTAMPEXTERNAL(lengthtimestamp-format)TIMESTAMP-WITH-TIME-ZONEEXTERNAL( length)ROWIDBLOBCLOBMIXEDCCSID 1208DBCLOBCCSID 1200DECFLOAT(34)(16)EXTERNAL( length)XMLPRESERVE WHITESPACEBINARYXMLNULLIFfield selection criterionDEFAULTIFdefaultif condition

field name spec:

Read syntax diagramSkip visual syntax diagram field-name POSITION( start: end)CONSTANTIFfield selection criterionCONSTANT(' string'X' hex-string'integerCURRENT DATECURRENT TIMECURRENT TIMESTAMPNULL)

strip spec:

Read syntax diagramSkip visual syntax diagramSTRIPBOTH TRAILINGLEADING' strip-char'1X' strip-char'TRUNCATE
Notes:
  • 1 If you specify GRAPHIC, BINARY, VARBINARY, or VARGRAPHIC, you cannot specify 'strip-char'. You can specify only X'strip-char'.

decimal spec:

Read syntax diagramSkip visual syntax diagramDECIMALPACKEDZONEDEXTERNAL( length,0, scale)

Start of changedefaultif condition: End of change

Read syntax diagramSkip visual syntax diagramCONV_ERRORfield-name( start: end)=<>X' byte-string'' character-string'G' graphic-string'N' graphic-string'

Option descriptions for INTO TABLE

table-name
Specifies the name of the table that is to be loaded. The table must be described in the catalog.

The table must not be a catalog table or a system-maintained materialized query table.

If the table name is not qualified by a schema name, the authorization ID of the invoker of the utility job step is used as the schema qualifier of the table name. Enclose the table name in quotation marks if the name contains a blank.

Data from every LOAD record in the data set is loaded into the specified table unless:

  • A WHEN clause is used, and the data does not match the field selection criterion.
  • The FORMAT UNLOAD option is used on the LOAD statement, and the data comes from a table that is not specified in an INTO TABLE statement.
  • A certain partition is specified, and the data does not belong to that partition.
  • Data conversion errors occur.
  • Any errors occur that are not generated by data conversion.
IGNOREFIELDS
Indicates whether LOAD is to skip fields in the input data set that do not correspond to columns in the target table. Examples of fields that do not correspond to table columns are the DSN_NULL_IND_nnnnn, DSN_ROWID, DSN_IDENTITY, and DSN_RCTIMESTAMP fields that are generated by the REORG utility.
NO
Specifies that the LOAD process is not to skip any fields.
YES
Specifies that LOAD is to skip fields in the input data set that do not correspond to columns in the target table.

Specifying YES can be useful if each input record contains a variable-length field, followed by some variable-length data that you do not want to load and then some data that you want to load. Because of the variable-length field, you cannot use the POSITION keyword to skip over the variable-length data that you do not want to load. By specifying IGNOREFIELDS, you can give a field specification for the variable-length data that you do not want to load; and by giving it a name that is not one of the table column names, LOAD skips the field without loading it.

Use this option with care, because it also causes fields to be skipped if you intend to load a column but have misspelled the name.

NUMRECS
Indicates the number of input records for the specified table or table partition.
integer
A positive integer that is used as an estimate of the number of complete input records that are to be loaded into the specified table. The specified number refers to fully assembled input records when CONTINUEIF is used.

Use the NUMRECS keyword for multi-table table spaces to indicate the number of input records that will be loaded into each of the tables or table partitions.

Specifying the number of records improves the sizing of the sort work data sets that the utility requires when indexes are built in parallel. If the LOAD utility underestimates the size of the sort work data sets, the execution of the LOAD utility could fail.

You can also use the NUMRECS keyword when the input data set is located on tape or if only a fraction of the input records will be loaded.

If an integer value is specified on the SORTKEYS keyword at the table-space level, the NUMRECS keyword cannot be specified in the same LOAD statement.

If multiple tables or partitions are loaded in the same LOAD statement, the NUMRECS keyword must be specified either for all of the tables or partitions or for none of the tables or partitions.

PART integer
Specifies that data is to be loaded into a partition of a partitioned table space. This option is valid only for partitioned table spaces, not including partition-by-growth table spaces.

integer is the physical partition number for the partition into which records are to be loaded. The same partition number cannot be specified more than once if partition parallelism has been requested. Any data that is outside the range of the specified partition is not loaded. The maximum is 4096.

LOAD INTO PART integer is not allowed if:

  • An identity column is part of the partitioning index, unless OVERRIDE(IDENTITY) is specified for the identity column GENERATED ALWAYS
  • A row ID is part of the partitioning index
  • The table space is partition-by-growth

For nonpartitioned secondary indexes, LOAD PART:

  • Does not set the page set REBUILD-pending (PSRBD) status
  • Does not consider PCTFREE or FREEPAGE attributes when inserting keys
PREFORMAT
Specifies that the remaining pages are to be preformatted up to the high-allocated RBA in the partition and its corresponding partitioning index space. The preformatting occurs after the data is loaded and the indexes are built.
INDEXDEFER
Specifies whether index builds are done during the BUILD phase of LOAD, or are deferred until REBUILD INDEX is run manually. Deferring index builds is a way to improve LOAD performance, especially for LOAD with PART. If indexes are not built during LOAD, LOAD places the affected indexes in the REBUILD-pending state.
NONE
Specifies that indexes are built during the BUILD phase of LOAD.
ALL
Specifies that no indexes are built as part of a BUILD phase of the LOAD utility. Index builds are deferred until REBUILD INDEX is run manually. ALL is valid only if SHRLEVEL NONE is also specified.
NPI
Specifies that building of nonpartitioned indexes is not done as part of a BUILD phase of the LOAD utility. Nonpartitioned index builds are deferred until REBUILD INDEX is run manually. NPI is valid only if SHRLEVEL NONE is also specified.
NONUNIQUE
Specifies that building of only nonunique indexes is deferred. NONUNIQUE is valid only if ALL or NPI is also specified. If NONUNIQUE is not specified, building of unique and nonunique indexes is deferred. If unique indexes are defined on the tables that are being loaded, specify NONUNIQUE unless the data really is unique. REBUILD INDEX does not resolve duplicate keys for unique indexes.

When INDEXDEFER ALL or INDEXDEFER NPI is specified:

  • If ENFORCE CONSTRAINTS is also specified, building of indexed foreign keys is not deferred.
  • If RESUME is also specified, building of indexes that were created with DEFINE NO and are still undefined is not deferred. Building of undefined indexes is deferred only when REPLACE is specified.

Start of changeAny LOAD REPLACE SHRLEVEL REFERENCE PART execution results in the default of INDEXDEFER NPI, with the affected part-level non-partitioning index placed put into RBDP status on successful LOAD completion. NONUNIQUE is ignored if specified.End of change

RESUME
Specifies whether records are to be loaded into an empty or non-empty partition. For nonsegmented table spaces, space is not reused for rows that have been marked as deleted or by rows of dropped tables is not reused. If the RESUME option is specified at the table space level, the RESUME option is not allowed in the PART clause.

If you want the RESUME option to apply to the entire table space, use the LOAD RESUME option. If you want the RESUME option to apply to a particular partition, specify it by using PART integer RESUME.

NO
Loads records into an empty partition. If the partition is not empty, and you have not used REPLACE, a message is issued, and the utility job step terminates with a job step condition code of 8.

For non-segmented table spaces that contains deleted rows or rows of dropped tables, using the REPLACE keyword provides increased efficiency.

YES
Loads records into a non-empty partition. If the partition is empty, a warning message is issued, but the partition is loaded.

Start of changeIf COPYDDN or RECOVERYDDN is specified with RESUME YES, an inline image copy will be created for the specified partition during processing of the LOAD statement. The image copy is created at the table space level and is a full image copy for each partition that is specified by INTO TABLE PART. If COPYDDN or RECOVERYDDN was specified on the table space level, it cannot be specified at the INTO TABLE PART level.End of change

REPLACE
Indicates that you want to replace only the contents of the partition that is cited by the PART option, rather than the entire table space.

You cannot use LOAD REPLACE with the PART integer REPLACE option of INTO TABLE. If you specify the REPLACE option, you must either replace an entire table space, using LOAD REPLACE, or a single partition, using the PART integer REPLACE option of INTO TABLE. You can, however, use PART integer REPLACE with LOAD RESUME YES.

REUSE
Specifies, when used with the REPLACE option, that LOAD should logically reset and reuse Db2-managed data sets without deleting and redefining them. If you do not specify REUSE, Db2 deletes and redefines Db2-managed data sets to reset them.

If you specify REUSE with REPLACE on the PART specification (and not for LOAD at the table space level), only the specified partitions are logically reset. If you specify REUSE for the table space and REPLACE for the partition, data sets for the replaced parts are logically reset.

KEEPDICTIONARY
Specifies that the LOAD utility is not to build a new dictionary. LOAD retains the current dictionary and uses it for compressing the input data. This option eliminates the cost that is associated with building a new dictionary.

Start of changeThis keyword is valid only if a dictionary exists and the partition that is being loaded is defined with compression.End of change

Start of changeIf the partition is defined with compression, but no dictionary exists, one is built and a warning message is issued.End of change

INDDN ddname
Specifies the input data set or data sets for the partition.

ddname is the name of a DD statement from the job step or the name of a template in the utility input stream. The default value is SYSREC.

Start of changeIf multiple ddname values are specified, those data sets are dynamically concatenated as input to LOAD. You can specify a maximum of 1000 ddname values. You cannot specify the same DD statement from the job step more than once.End of change

The record format for an input data set must be fixed-length or variable-length. The data set must be a sequential data set that is readable by the basic sequential access method (BSAM).

When loading LOB data by using file reference variables, the input data set should include the names of the files that contain the LOB column values. Each file can be either a sequential file, PDS member, PDSE member, or separate HFS file.

If you specify INDDN in one INTO TABLE PART clause, you must specify INDDN in all other INTO TABLE PART clauses in that LOAD statement.

Providing input data sets at the partition level (by specifying multiple INTO TABLE PART INDDN clauses) enables LOAD partition parallelism, which can significantly improve performance. When one or more nonpartitioned secondary indexes exist on the table space, loading all partitions in a single job with partition parallelism is recommended instead of running concurrent separate jobs.

The field specifications apply separately to each input file. Therefore, if multiple INTO TABLE PART INDDN clauses are used, field specifications are required for each one.

DISCARDDN ddname
Specifies the DD statement for a discard data set for the partition. The discard data set holds copies of records that are not loaded (for example, if they contain conversion errors). The discard data set also holds copies of records that were loaded and then removed (due to unique index errors, or referential or check constraint violations).

Start of changeIf DISCARDS n is specified in the LOAD statement and LOAD partition parallelism is enabled, specifying DISCARDDN in an INTO TABLE PART clause is recommended. See Loading partitions.End of change

Flag input records for discarding during the RELOAD, INDEXVAL, and ENFORCE phases. However, the utility does not write the discard data set until the DISCARD phase when the utility copies the flagged records from the input data set to the discard data set.

The discard data set must be a sequential data set, and it must be write-accessible by BSAM, with the same record format, record length, and block size as the input data set.

The ddname is the name of the discard data set. DISCARDDN can be a template name.

If you omit the DISCARDDN option, LOAD does not save discarded records.

INCURSOR cursor-name
Specifies the cursor for the input data set. You must declare the cursor before it is used by the LOAD utility. Use the EXEC SQL utility control statement to define the cursor. You cannot load data into the same table on which you defined the cursor.

The specified cursor can be used as part of the Db2 family cross loader function, which enables you to load data from any DRDA-compliant remote server. For more information about using the cross loader function, see Loading data by using the cross-loader function.

cursor-name is the cursor name. Cursor names that are specified with the LOAD utility cannot be longer than eight characters.

You cannot use the INCURSOR option with the following options:

  • SHRLEVEL CHANGE
  • NOSUBS
  • FORMAT UNLOAD
  • FORMAT SQL/DS
  • CONTINUEIF
  • WHEN
  • SPANNED YES

In addition, you cannot specify field specifications with the INCURSOR option.

WHEN
Indicates which records in the input data set are to be loaded. If no WHEN clause is specified (and if FORMAT UNLOAD was not used in the LOAD statement), all records in the input data set are loaded into the specified tables or partitions. (Data that is beyond the range of the specified partition is not loaded.)

The option following WHEN describes a condition; input records that satisfy the condition are loaded. Input records that do not satisfy any WHEN clause of any INTO TABLE statement are written to the discard data set, if one is being used and the IGNORE(WHEN) option is not specified.

Character-string constants should be specified in LOAD utility control statements in the character set that matches the input data record. Specify EBCDIC constants in the LOAD control statement if your data is in EBCDIC and specify UNICODE constants if your data is in UNICODE. You may also code the WHEN condition using the hexadecimal form. For example, use (1:1)=X'31' rather than (1:1)='1'.
SQL/DS='table-name'
Is valid only when the FORMAT SQL/DS option is used on the LOAD statement.

table-name is the name of a table that has been unloaded into the unload data set. The table name after INTO TABLE tells which Db2 table the SQL/DS table is loaded into. Enclose the table name in quotation marks if the name contains a blank.

If no WHEN clause is specified, input records from every SQL/DS table are loaded into the table that is specified after INTO TABLE.

field-selection-criterion
Describes a field and a character constant. Only those records in which the field contains the specified constant are to be loaded into the table that is specified after INTO TABLE.

A field in a selection criterion must:

  • Contain a character or graphic string. No data type conversions are performed when the contents of the field in the input record are compared to a string constant.
  • Start at the same byte offset in each assembled input record. If any record contains varying-length strings, which are stored with length fields, that precede the selection field, they must be padded so that the start of the selection field is always at the same offset.

The field and the constant do not need to be the same length. If they are not, the shorter of the two is padded before a comparison is made. Character and graphic strings are padded with blanks. Hexadecimal strings are padded with zeros.

field-name
Specifies the name of a field that is defined by a field-specification. If field-name is used, the start and end positions of the field are given by the POSITION option of the field specification.
(start:end)
Identifies column numbers in the assembled load record; the first column of the record is column 1. The two numbers indicate the starting and ending columns of a selection field in the load record.

If :end is not used, the field is assumed to have the same length as the constant.

X'byte-string'
Identifies the constant as a string of hexadecimal characters. For example, the following WHEN clause specifies that a record is to be loaded if it has the value X'FFFF' in columns 33 through 34.
WHEN (33:34) = X'FFFF'
'character-string'
Identifies the constant as a string of characters. For example, the following WHEN clause specifies that a record is to be loaded if the field DEPTNO has the value D11.
WHEN DEPTNO = 'D11'

If the field uses a specific external date, time, or timestamp format, the field specification must use a character string that matches the specified format.

G'graphic-string'
Identifies the constant as a string of double-byte characters. For example, the following WHEN clause specifies that a record is to be loaded if it has the specified value in columns 33 through 36.
WHEN (33:36) = G'<**>'
In this example, < is the shift-out character,* is a double-byte character, and > is the shift-in character.

If the first or last byte of the input data is a shift-out character, it is ignored in the comparison. Specify G as an uppercase character.

N'graphic-string'
Identifies the constant as a string of double-byte characters. N and G are synonymous for specifying graphic string constants. Specify N as an uppercase character.
(field-specification, …)
Describes the location, format, and null value identifier of the data that is to be loaded.

If no field specifications are used:

  • The fields in the input records are assumed to be in the same order as in the Db2 table.
  • The formats are set by the FORMAT option on the LOAD statement, if that option is used.
  • Fixed strings in the input are assumed to be of fixed maximum length. VARCHAR and VARGRAPHIC fields must contain a valid 2-byte binary length field preceding the data; no intervening gaps are allowed between the VARCHAR or VARGRAPHIC fields and the field that follows.
  • BINARY fields are assumed to be of fixed maximum length.
  • VARBINARY fields must contain a valid 2-byte binary length field preceding the data.
  • ROWID fields are varying length, and must contain a valid 2-byte binary length field preceding the data; no intervening gaps are allowed between ROWID fields and the fields that follow.
  • LOB fields are varying length, and require a valid 4-byte binary length field preceding the data; no intervening gaps are allowed between them and the LOB fields that follow.
  • Numeric data is assumed to be in the appropriate internal Db2 number representation.
  • The NULLIF or DEFAULTIF options cannot be used.

If any field specification is used for an input table, a field specification must exist for each field of the table that does not have a default value. Any field in the table with no corresponding field specification is loaded with its default value.

If any column in the output table does not have a field specification and is defined as NOT NULL, with no default, the utility job step is terminated.

Identity columns or row change timestamp columns can appear in the field specification only if you defined them with the GENERATED BY DEFAULT attribute.

If you are loading application or system temporal data and you include field specifications, you must specify both the start and end time column fields.

field-name
Specifies the name of a field, which can be a name of your choice. If the field is to be loaded, the name must be the name of a column in the table that is named after INTO TABLE unless IGNOREFIELDS is specified. You can use the field name as a vehicle to specify the range of incoming data. See Example 4: Loading data of different data types for an example of loading selected records into an empty table space.

The starting location of the field is given by the POSITION option. If POSITION is not used, the starting location is one column after the end of the previous field.

LOAD determines the length of the field in one of the following ways, in the order listed:

  1. If the field has data type VARCHAR, VARGRAPHIC, VARBINARY, ROWID, or XML the length is assumed to be contained in a 2-byte binary field that precedes the data. For VARCHAR, VARBINARY, and XML fields, the length is in bytes; for VARGRAPHIC fields, the length field identifies the number of double-byte characters.

    If the field has data type CLOB, BLOB, or DBCLOB, the length is assumed to be contained in a 4-byte binary field that precedes the data. For BLOB and CLOB fields, the length is in bytes; for DBCLOB fields, the length field identifies the number of double-byte characters.

  2. If :end is used in the POSITION option, the length is calculated from start and end. In that case, any length attribute after the CHAR, GRAPHIC, INTEGER, DECIMAL, FLOAT, or DECFLOAT specifications is ignored.
  3. The length attribute on the CHAR, GRAPHIC, INTEGER, DECIMAL, FLOAT, or DECFLOAT specifications is used as the length.
  4. The length is taken from the Db2 field description in the table definition, or it is assigned a default value according to the data type. For DATE and TIME fields, the length is defined during installation. For variable-length fields, the length is defined from the column in the Db2 table definition, excluding the null indicator byte, if it is present. The following table shows the default length, in bytes, for each data type.
    Table 1. Default length of each data type (in bytes)
    Data type Default length in bytes
    BIGINT 8
    BINARY Length that is used in column definition
    BLOB Varying
    CHARACTER Length that is used in column definition
    CLOB Varying
    DATE 10 (or installation default)
    DBCLOB Varying
    DECFLOAT(16) 8
    DECFLOAT(34) 16
    DECIMAL EXTERNAL Decimal precision for output columns that are decimal, otherwise the length that is used in column definition
    DECIMAL PACKED Length that is used in column definition
    DECIMAL ZONED Decimal precision for output columns that are decimal, otherwise the length that is used in column definition
    FLOAT (single precision) 4
    FLOAT (double precision) 8
    GRAPHIC 2 multiplied by (length that is used in column definition)
    INTEGER 4
    MIXED Mixed DBCS data
    ROWID Varying
    SMALLINT 2
    TIME 8 (or installation default)
    TIMESTAMP 26
    VARBINARY Varying
    VARCHAR Varying
    VARGRAPHIC Varying
    XML Varying
    TIMESTAMP WITH TIME ZONE 33

If a data type is not given for a field, its data type is assumed to be the same as that of the column into which it is loaded, as given in the Db2 table definition.

POSITION(start:end)
Indicates where a field is in the assembled load record.

start and end are the locations of the first and last columns of the field; the first column of the record is column 1. The option can be omitted.

Column locations can be specified as:

  • An integer n, meaning an actual column number
  • *, meaning one column after the end of the previous field
  • *+n, where n is an integer, meaning n columns after the location that is specified by *

Do not enclose the entire POSITION option specification in parentheses; enclose only the start:end description in parentheses. Valid and invalid specifications are shown in the following table.

Table 2. Example of valid and invalid POSITION specifications
Valid Invalid
POSITION (10:20) (POSITION (10:20))
Start of changeCONSTANTIF field-selection-criterionEnd of change
Start of changeDescribes a condition that causes the Db2 column to be loaded with a constant value. You can write the field-selection-criterion with the same options as described for field-selection-criterion. If the contents of the CONSTANTIF field match the provided character constant, the field that is specified in field-specification is loaded with the value specified in the CONSTANT keyword.

If the CONSTANTIF field is defined by the name of a VARCHAR or VARGRAPHIC field, Db2takes the length of the field from the 2-byte binary field that appears before the data portion of the VARCHAR or VARGRAPHIC field.

Character-string constants should be specified in LOAD utility control statements in the character set that matches the input data record. Specify EBCDIC constants in the LOAD control statement if your data is in EBCDIC and specify UNICODE constants if your data is in UNICODE. You may also code the CONSTANTIF condition using the hexadecimal form. For example, if the input data is in EBCDIC and the control statement is in UTF-8, use (1:1)=X'31' in the condition rather than (1:1)='1'.

End of change
Start of changeCONSTANT(...)End of change
Start of changeSpecifies that the column that matches the field name should be loaded with a constant value. The field name that is associated with the CONSTANT keyword must match a column name of the specified table. The length and type of the CONSTANT field is derived from the specified value and must be valid for the target column.

For a CONSTANT field, no other field specification options are allowed. A CONSTANT field cannot be specified for LOB or XML columns.

If a record is discarded because of an invalid value specified with the CONSTANT keyword, the record in the discard data set reflects the original record from SYSREC, not the record as loaded with the constant value(s).

'string'
Specifies the character string that is to be inserted in the target column. If the specified string is in the form 'string', it is assumed to be in the encoding scheme of SYSIN even if the encoding scheme of SYSREC is not the same. Conversion might be applied before inserting the string into the target column.
X'hex-string'
Specifies the hexadecimal value that is to be inserted in the target column. The hexadecimal value must be specified in the format X'hex-string'.
integer
Specifies the integer that is to be inserted into the target column. Valid values are between -2147483648 and 2147483647.
CURRENT DATE
Specifies that the current date is to be inserted into the target column.
CURRENT TIME
Specifies that the current time is to be inserted into the target column.
CURRENT TIMESTAMP
Specifies that the current timestamp is to be inserted into the target column.
NULL
Specifies that the target column should be set to NULL. The target column must be nullable.
End of change

Data types in a field specification: The data type of the field can be specified by any of the keywords that follow. Except for graphic fields, length is the length in bytes of the input field.

All numbers that are designated EXTERNAL are in the same format in the input records.

Start of changeWhen CCSID is specified, LOAD supports the following input field formats:End of change

Start of change
Table 3. Supported source to target field CSSID formats
Source CCSID Target CCSID
ASCII EBCDIC UNICODE EBCDIC table with a UNICODE column
ASCII Yes Yes Yes No
EBCDIC Yes Yes Yes Yes
UNICODE Yes Yes Yes No
EBCDIC table with a UNICODE column No Yes1 Yes Yes
  1. Might require that the EBCDIC CCSID field on installation panel DSNTIPF be set to use graphic.
End of change
CHAR(length)
Specifies a fixed-length character string. If you do not specifylength, the length of the string is determined from the POSITION specification. If you do not specifylength or POSITION, LOAD uses the default length for CHAR, which is determined from the length of the column in the table. You can also specify CHARACTER and CHARACTER(length).

When you specify CHAR as the type for the file name for CLOBF, BLOBF, or DBCLOBF, you must also provide the length so that the LOAD utility can determine the correct file name. Otherwise message DSNU338I will be issued for an invalid column specification.

BIT
Specifies that the input field contains BIT data. If BIT is specified, LOAD bypasses any CCSID conversions for the input data. If the target column has the BIT data type attribute, LOAD bypasses any code page translation for the input data.
Start of changeCCSID 1208End of change
Start of changeSpecifies that the input field contains data in CCSID 1208 (UTF-8). If data conversion occurs, CCSID 1208 is used for the input data.End of change
MIXED
Specifies that the input field contains mixed SBCS and DBCS data. If MIXED is specified, any required CCSID conversions use the mixed CCSID for the input data. If MIXED is not specified, any such conversions use the SBCS CCSID for the input data.
BLOBF
Indicates that the input field contains the name of a BLOB file which is going to be loaded to a specified BLOB/XML column.

BINARYXML Specifies that the XML document to be loaded using file reference variables is in Extensible Dynamic Binary XML Db2 Client/Server Binary XML Format (binary XML) format.

CLOBF
Indicates that the input field contains the name of a CLOB file which is going to be loaded to a specified CLOB/XML column.Start of change
Start of changeCCSID 1208End of change
Start of changeSpecifies that the input field contains data in CCSID 1208 (UTF-8). If data conversion occurs, CCSID 1208 is used for the input data.End of change
End of change
DBCLOBF
Indicates that the input field contains the name of a DBCLOBF file which is going to be loaded to a specified DBCLOB/XML column.Start of change
Start of changeCCSID 1200End of change
Start of changeSpecifies that the data in the CLOB input file is in CCSID 1200 (UTF-16). If data conversion occurs, CCSID 1200 is used for the input data.End of change
End of change
PRESERVE WHITESPACE
Specifies that the white space in the XML column is preserved. The default is not to preserve the white space.
STRIP
Specifies that LOAD is to remove zeros (the default) or the specified characters from the beginning, the end, or both ends of the data. LOAD pads the CHAR field, so that it fills the rest of the column.

LOAD applies the strip operation before performing any character code conversion or padding.

The effect of the STRIP option is the same as the SQL STRIP scalar function.

BOTH
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the beginning and end of the data.
TRAILING
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the end of the data.
LEADING
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the beginning of the data.
'strip-char'
Specifies a single-byte or double-byte character that LOAD is to strip from the data.

Specify this character value in EBCDIC. Depending on the input encoding scheme, LOAD applies SBCS CCSID conversion to the strip-char value before it is used in the strip operation.

If the subtype of the column to be loaded is BIT or you want to specify a strip-char value in an encoding scheme other than EBCDIC, use the hexadecimal form (X'strip-char'). LOAD does not perform any CCSID conversion if the hexadecimal form is used.

X'strip-char'
Specifies in hexadecimal form a single-byte or double-byte character that LOAD is to strip from the data. For single-byte characters, specify this value in the form X'hh', where hh is two hexadecimal characters. For double-byte characters, specify this value in the form X'hhhh', where hhhh is four hexadecimal characters.

Use the hexadecimal form to specify a character in an encoding scheme other than EBCDIC. When you specify the character value in hexadecimal form, LOAD does not perform any CCSID conversion.

If you specify a strip character in the hexadecimal format, you must specify the character in the input encoding scheme.

TRUNCATE
Indicates that LOAD is to truncate the input character string from the right if the string does not fit in the target column. LOAD performs the truncation operation after any CCSID translation.

If the input data is BIT data, LOAD truncates the data at a byte boundary. If the input data is SBCS or MIXED data, LOAD truncates the data at a character boundary. (Double-byte characters are not split.) If a MIXED field is truncated to fit a column, the truncated string can be shorter than the specified column size. In this case, blanks in the output CCSID are padded to the right. If MIXED data is in EBCDIC, truncation preserves the SO (shift-out) and SI (shift-in) characters around a DBCS string.

VARCHAR
Specifies a character field of varying length. The length in bytes must be specified in a 2-byte binary field preceding the data. (The length does not include the 2-byte field itself.) The length field must start in the column that is specified as start in the POSITION option. If :end is used, it is ignored.
BIT
Specifies that the input field contains BIT data. If BIT is specified, LOAD bypasses any CCSID conversions for the input data. If the target column has the BIT data type attribute, LOAD bypasses any code page translation for the input data.
MIXED
Specifies that the input field contains mixed DBCS data. If MIXED is specified, any required CCSID conversions use the mixed CCSID for the input data. If MIXED is not specified, any such conversions use the SBCS CCSID for the input data.
Start of changeCCSID 1208End of change
Start of change Specifies that the data in the CLOB input file is in CCSID 1208 (UTF-8). If data conversion occurs, CCSID 1208 is used for the input data.End of change
BLOBF
Indicates that the input field contains the name of a BLOB file which is going to be loaded to a specified BLOB/XML column.

BINARYXML Specifies that the XML document to be loaded using file reference variables is in binary XML format.

CLOBF
Indicates that the input field contains the name of a CLOB file which is going to be loaded to a specified CLOB/XML column.Start of change
Start of changeCCSID 1208End of change
Start of changeSpecifies that the data in the CLOB input file is in CCSID 1208 (UTF-8). If data conversion occurs, CCSID 1208 is used for the input data.End of change
End of change
DBCLOBF
Indicates that the input field contains the name of a DBCLOBF file which is going to be loaded to a specified DBCLOB/XML column.Start of change
Start of changeCCSID 1200End of change
Start of changeSpecifies that the data in the CLOB input file is in CCSID 1200 (UTF-16). If data conversion occurs, CCSID 1200 is used for the input data.End of change
End of change
PRESERVE WHITESPACE
Specifies that the white space in the XML column is preserved. The default is not to preserve the white space.
STRIP
Specifies that LOAD is to remove zeros (the default) or the specified characters from the beginning, the end, or both ends of the data. LOAD adjusts the VARCHAR length field to the length of the stripped data.

LOAD applies the strip operation before performing any character code conversion or padding.

The effect of the STRIP option is the same as the SQL STRIP scalar function.

BOTH
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the beginning and end of the data.
TRAILING
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the end of the data.
LEADING
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the beginning of the data.
'strip-char'
Specifies a single-byte or double-byte character that LOAD is to strip from the data.

Specify this character value in EBCDIC. Depending on the input encoding scheme, LOAD applies SBCS CCSID conversion to the strip-charvalue before it is used in the strip operation.

If the subtype of the column to be loaded is BIT or you want to specify a strip-char value in an encoding scheme other than EBCDIC, use the hexadecimal form (X'strip-char'). LOAD does not perform any CCSID conversion if the hexadecimal form is used.

X'strip-char'
Specifies in hexadecimal form a single-byte or double-byte character that LOAD is to strip from the data. For single-byte characters, specify this value in the form X'hh', where hh is two hexadecimal characters. For double-byte characters, specify this value in the form X'hhhh', where hhhh is four hexadecimal characters.

Use the hexadecimal form to specify a character in an encoding scheme other than EBCDIC. When you specify the character value in hexadecimal form, LOAD does not perform any CCSID conversion.

If you specify a strip character in the hexadecimal format, you must specify the character in the input encoding scheme.

TRUNCATE
Indicates that LOAD is to truncate the input character string from the right if the string does not fit in the target column. LOAD performs the truncation operation after any CCSID translation.

If the input data is BIT data, LOAD truncates the data at a byte boundary. If the input data is character type data, LOAD truncates the data at a character boundary. If a mixed-character type data is truncated to fit a column of fixed size, the truncated string can be shorter than the specified column size. In this case, blanks in the output CCSID are padded to the right.

GRAPHIC(length)
Specifies a fixed-length graphic type. You can specify both start and end for the field specification.

If you use GRAPHIC, the input data must not contain shift characters. start and end must indicate the starting and ending positions of the data itself.

length is the number of double-byte characters. The length of the field in bytes is twice the value of length. If you do not specify length, the number of double-byte characters is determined from the POSITION specification. If you do not specify length or POSITION, LOAD uses the default length for GRAPHIC, which is determined from the length of the column in the table.

Start of changeFor example, let *** represent three double-byte characters. Then, to describe ***, specify either POSITION(1:6) GRAPHIC or POSITION(1) GRAPHIC(3). A GRAPHIC field that is described in this way cannot be specified in a field selection criterion.End of change

STRIP
Specifies that LOAD is to remove zeros (the default) or the specified characters from the beginning, the end, or both ends of the data.

LOAD applies the strip operation before performing any character code conversion or padding.

The effect of the STRIP option is the same as the SQL STRIP scalar function.

BOTH
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the beginning and end of the data.
TRAILING
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the end of the data.
LEADING
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the beginning of the data.
X'strip-char'
Specifies the hexadecimal form of the double-byte character that LOAD is to strip from the data. Specify this value in the form X'hhhh', where hhhh is four hexadecimal characters.

You must specify the character in the input encoding scheme.

TRUNCATE
Indicates that LOAD is to truncate the input character string from the right if the string does not fit in the target column. LOAD performs the truncation operation after any CCSID translation.

LOAD truncates the data at a character boundary. Double-byte characters are not split.

Start of changeCCSID 1200End of change
Start of changeSpecifies that the data in the CLOB input file is in CCSID 1200 (UTF-16). If data conversion occurs, CCSID 1200 is used for the input data.End of change
GRAPHIC EXTERNAL(length)
Specifies a fixed-length field of the graphic type with the external format. You can specify both start and end for the field specification.

If you use GRAPHIC EXTERNAL, the input data must contain a shift-out character in the starting position, and a shift-in character in the ending position. Other than the shift characters, this field must have an even number of bytes. The first byte of any pair must not be a shift character.

length is the number of double-byte characters. length for GRAPHIC EXTERNAL does not include the number of bytes that are represented by shift characters. The length of the field in bytes is twice the value of length. If you do not specify length, the number of double-byte characters is determined from the POSITION specification. If you do not specify length or POSITION, LOAD uses the default length for GRAPHIC, which is determined from the length of the column in the table.

Start of changeFor example, let *** represent three double-byte characters, and let < and > represent shift-out and shift-in characters. Then, to describe <***>, specify either POSITION(1:8) GRAPHIC EXTERNAL or POSITION(1) GRAPHIC EXTERNAL(3).End of change

STRIP
Specifies that LOAD is to remove zeros (the default) or the specified characters from the beginning, the end, or both ends of the data.

LOAD applies the strip operation before performing any character code conversion or padding.

The effect of the STRIP option is the same as the SQL STRIP scalar function.

BOTH
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the beginning and end of the data.
TRAILING
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the end of the data.
LEADING
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the beginning of the data.
X'strip-char'
Specifies the hexadecimal form of the double-byte character that LOAD is to strip from the data. Specify this value in the form X'hhhh', where hhhh is four hexadecimal characters.

You must specify the character in the input encoding scheme.

TRUNCATE
Indicates that LOAD is to truncate the input character string from the right if the string does not fit in the target column. LOAD performs the truncation operation after any CCSID translation.

LOAD truncates the data at a character boundary. Double-byte characters are not split.

VARGRAPHIC
Identifies a graphic field of varying length. The length, in double-byte characters, must be specified in a 2-byte binary field preceding the data. (The length does not include the 2-byte field itself.) The length field must start in the column that is specified as start in the POSITION option. :end, if used, is ignored.

VARGRAPHIC input data must not contain shift characters.

STRIP
Specifies that LOAD is to remove zeros (the default) or the specified characters from the beginning, the end, or both ends of the data. LOAD adjusts the VARGRAPHIC length field to the length of the stripped data (the number of DBCS characters).

LOAD applies the strip operation before performing any character code conversion or padding.

The effect of the STRIP option is the same as the SQL STRIP scalar function.

BOTH
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the beginning and end of the data.
TRAILING
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the end of the data.
LEADING
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the beginning of the data.
X'strip-char'
Specifies the hexadecimal form of the double-byte character that LOAD is to strip from the data. Specify this value in the form X'hhhh', where hhhh is four hexadecimal characters.

You must specify the character in the input encoding scheme.

TRUNCATE
Indicates that LOAD is to truncate the input character string from the right if the string does not fit in the target column. LOAD performs the truncation operation after any CCSID translation.

LOAD truncates the data at a character boundary. Double-byte characters are not split.

Start of changeCCSID 1200End of change
Start of changeSpecifies that the data in the CLOB input file is in CCSID 1200 (UTF-16). If data conversion occurs, CCSID 1200 is used for the input data.End of change
SMALLINT
Specifies a 2-byte binary number. Negative numbers are in two's complement notation.
INTEGER
pecifies a 4-byte binary number. Negative numbers are in two's complement notation. You can also specify INT.
INTEGER EXTERNAL(length)
A string of characters that represent a number. The format is that of an SQL numeric constant. If you do not specify length, the length of the string is determined from the POSITION specification. If you do not specify length or POSITION, LOAD uses the default length for INTEGER, which is 4 bytes. You can also specify INT EXTERNAL.
BIGINT
Specifies an 8-byte binary number. Negative numbers are in two's complement notation.
BINARY(length)
Specifies a fixed-length binary string. If you do not specify length, the length of the string is determined from the POSITION specification. If you do not specify length or POSITION, LOAD uses the default length for BINARY, which is determined from the length of the column in the table. The default for X'strip-char' is hexadecimal zero (X'00'). No data conversion is applied to the field.
STRIP
Specifies that LOAD is to remove binary zeros (the default) or the specified X'strip-char' from the beginning, the end, or both ends of the data. LOAD pads the BINARY field, so that it fills the rest of the column.

The effect of the STRIP option is the same as the SQL STRIP scalar function.

BOTH
Indicates that LOAD is to remove occurrences of binary zeros or the specified strip character from the beginning and end of the data.
TRAILING
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the end of the data.
LEADING
Indicates that LOAD is to remove occurrences of blank or the specified strip character from the beginning of the data.
X'strip-char'
Specifies, in hexadecimal form, a single-byte or double-byte character that LOAD is to strip from the data. For single-byte characters, specify this value in the form X'hh', where hh is two hexadecimal characters.
TRUNCATE
Indicates that LOAD is to truncate the input character string from the right if the string does not fit in the target column.

LOAD truncates the data at a character boundary.

VARBINARY
Specifies a varying length binary string. The length in bytes must be specified in a 2-byte binary field preceding the data (the length does not include the 2-byte field itself). The length field must start in the column that is specified as start in the POSITION option. If :end is used, it is ignored. The default for X'strip-char' is hexadecimal zero (X'00'). No data conversion is applied to the field.
STRIP
Specifies that LOAD is to remove binary zeros (the default) or the specified characters from the beginning, the end, or both ends of the data. LOAD pads the VARBINARY field, so that it fills the rest of the column.

The effect of the STRIP option is the same as the SQL STRIP scalar function.

BOTH
Indicates that LOAD is to remove occurrences of binary zeros or the specified strip character from the beginning and end of the data.
TRAILING
Indicates that LOAD is to remove occurrences of binary zeros or the specified strip character from the end of the data.
LEADING
Indicates that LOAD is to remove occurrences of binary zeros or the specified strip character from the beginning of the data.
X'strip-char'
Specifies, in hexadecimal form, a single-byte character that LOAD is to strip from the data. For single-byte characters, specify this value in the form X'hh', where hh is two hexadecimal characters.
TRUNCATE
Indicates that LOAD is to truncate the input character string from the right if the string does not fit in the target column.

LOAD truncates the data at a character boundary.

DECIMAL PACKED
Specifies a number of the form ddd...ds, where d is a decimal digit that is represented by four bits, and s is a 4-bit sign value. The plus sign (+) is represented by A, C, E, or F, and the minus sign (-) is represented by B or D. The maximum number of ds is the same as the maximum number of digits that are allowed in the SQL definition. You can also specify DECIMAL, DEC, or DEC PACKED.
DECIMAL ZONED
Specifies a number in the form znznzn...z/sn, where z, n, and s have the following values:
n
A decimal digit represented by the right 4 bits of a byte (called the numeric bits)
z
That digit's zone, represented by the left 4 bits
s
The right-most byte of the decimal operand; s can be treated as a zone or as the sign value for that digit
The plus sign (+) is represented by A, C, E, or F, and the minus sign (-) is represented by B or D. The maximum number of zns is the same as the maximum number of digits that are allowed in the SQL definition. You can also specify DEC ZONED.
DECIMAL EXTERNAL(length,scale)
Specifies a string of characters that represent a number. The format is that of an SQL numeric constant.
length
Overall length of the input field, in bytes. If you do not specify length, the length of the input field is determined from the POSITION specification. If you do not specify length or POSITION, LOAD uses the default length for DECIMAL EXTERNAL, which is determined by using decimal precision.
scale
Specifies the number of digits to the right of the decimal point. scale must be an integer greater than or equal to 0, and it can be greater than length. The default value is 0.

If scale is greater than length, or if the number of provided digits is less than the specified scale, the input number is padded on the left with zeros until the decimal point position is reached. If scale is greater than the target scale, the source scale locates the implied decimal position. All fractional digits greater than the target scale are truncated. If scale is specified and the target column has a data type of small integer or integer, the decimal portion of the input number is ignored. If a decimal point is present, its position overrides the field specification of scale.

FLOAT(length)
Specifies either a 64-bit floating-point number or a 32-bit floating-point number. If length is between 1 and 21 inclusive, the number is 32 bits in the s390 (HFP) format:
Bit 0
Represents a sign (0 for plus and 1 for minus)
Bits 1-7
Represent an exponent
Bits 8-31
Represent a mantissa

If length is between 1 and 24 inclusive, the number is 32 bits in the IEEE (BFP) format:

Bit 0
Represents a sign (0 for plus and 1 for minus)
Bits 1-8
Represent an exponent
Bits 9-31
Represent a mantissa

If length is not specified, or is between 22 and 53 inclusive, the number is 64 bits in the s390 (HFP) format:

Bit 0
Represents a sign (0 for plus and 1 for minus)
Bits 1-7
Represent an exponent
Bits 8-63
Represent a mantissa.

If length is not specified, or is between 25 and 53 inclusive, the number is 64 bits in the IEEE (BFP) format:

Bit 0
Represents a sign (0 for “plus”, and 1 for “minus”)
Bits 1-11
Represent an exponent
Bits 12-63
Represent a mantissa.
You can also specify REAL for single-precision floating-point numbers and DOUBLE PRECISION for double-precision floating-point numbers.
FLOAT EXTERNAL(length)
Specifies a string of characters that represent a number. The format is that of an SQL floating-point constant.

A specification of FLOAT(IEEE) or FLOAT(S390) does not apply for this format (string of characters) of floating-point numbers.

If you do not specify length, the length of the string is determined from the POSITION specification. If you do not specify length or POSITION, LOAD uses the default length for FLOAT, which is 4 bytes for single precision and 8 bytes for double precision.

Start of changeDATE DATE_PEnd of change
Start of changeSpecifies a packed decimal representation of a date. The date value must be 3 bytes in length and in the DATE_P format (X'YYDDDs' where YY is the year, DDD is the day, and s is the sign).End of change
DATE EXTERNAL
Specifies a character string representation of a date. You can specify a length or a specific date format.
(length)
The length, if unspecified, is the specified length on the LOCAL DATA LENGTH installation option, or, if none was provided, the default is 10 bytes. If you specify a length, it must be within the range of 8 - 254 bytes.
If a date format is not specified, dates can be in any of the following formats. You can omit leading zeros for month and day. You can include trailing blanks, but no leading blanks are allowed.
  • dd.mm.yyyy
  • mm/dd/yyyy
  • yyyy-mm-dd
  • Any local format that was defined when Db2 was installed
Start of change(date-format)End of change
Start of changeThe format of the date representation, as shown in the following table.
Table 4. Values for date-format
date-format value Format Length
DATE_A mm-dd-yyyy 1 10 bytes
DATE_B mm-dd-yy 1, 2 8 bytes
DATE_C yyyy-mm-dd 1 10 bytes
DATE_D yy-mm-dd 1, 2 8 bytes
DATE_E dd-mm-yyyy 1 10 bytes
DATE_F dd-mm-yy 1, 2 8 bytes
DATE_G yyyy-ddd 1 8 bytes
DATE_H yy-ddd 1, 2 6 bytes
DATE_I mmddyyyy 8 bytes
DATE_J mmddyy 2 6 bytes
DATE_K yyyymmdd 8 bytes
DATE_L yymmdd 2 6 bytes
DATE_M ddmmyyyy 8 bytes
DATE_N ddmmyy 2 6 bytes
DATE_O yyyyddd 7 bytes
DATE_P yyddd 2 5 bytes

If a date format is specified for a field that is used in a field specification, the field specification must also use the specified date format.

Notes:
  1. If the format includes separators, the separator can be any single-byte character that can be converted to a single-byte EBCDIC character. However, if the input file has a delimited format, you cannot specify the same character that is used for a delimiter, including the COLDEL, CHARDEL, and DECPT delimiters. For more information about delimiter restrictions, see Loading delimited files.
  2. If a two-digit year is specified, it is expanded to a four-digit year. If the two-digit year is less than the sum of the two-digit current year plus 50, then the current century is used in the four-digit year. For example, assume that the current year is 2017. If the two-digit year is 67, 1967 is used. However, if the two-digit year is 66, 2066 is used.
End of change
TIME EXTERNAL
Specifies a character string representation of a time. You can specify a length or a specific time format.
(length)
The length, if unspecified, is the specified length on the LOCAL TIME LENGTH installation option, or, if none was provided, the default is 8 bytes. If you specify a length, it must be within the range of 4 - 254 bytes.
If a format is not specified, times can be in any of the following formats:
  • hh.mm.ss
  • hh:mm AM
  • hh:mm PM
  • hh:mm:ss
  • Any local format that was defined when Db2 was installed

You can omit the mm portion of the hh:mm AM and hh:mm PM formats if mm is equal to 00. For example, 5 PM is a valid time, and can be used instead of 5:00 PM.

Start of change(time-format)End of change
Start of changeThe specific format of the time representation, as shown in the following table.
Table 5. Values for time-format
time-format value Format Length
TIME_A hh.mm.ss 1 8 bytes
TIME_B hh.mm 1 5 bytes
TIME_C hh.mm AM or hh.mm PM 1 8 bytes
TIME_D hhmmss 6 bytes
TIME_E hhmm 4 bytes

If a time format is specified for a field that is used in a field specification, the field specification must also use the specified time format.

Note:
  1. If the format includes separators, the separator can be any single-byte character that can be converted to a single-byte EBCDIC character.
End of change
TIMESTAMP EXTERNAL
Specifies a character string representation of a time. You can specify a length or a specific timestamp format.
(length)
The default for length is 26 bytes. If you specify a length, it must be within the range of 19 - 32 bytes.
If a timestamp format is not specified, timestamps can be in any of the following formats. nnnnnn represents the number of microseconds, and can be 0 - 12 digits. You can omit leading zeros from the month, day, or hour parts of the timestamp; you can omit trailing zeros from the microseconds part of the timestamp.
  • yyyy-mm-dd-hh.mm.ss
  • yyyy-mm-dd-hh.mm.ss.nnnnnn
  • yyyy-mm-dd hh:mm:ss.nnnnnn
(timestamp-format)
The format of the timestamp representation, as shown in the following table.
Table 6. Values for timestamp-format
timestamp-format value Format Length
TIMESTAMP_A yyyy-mm-dd-hh.mm.ss 1 19 bytes
TIMESTAMP_B yyyy-mm-dd-hh.mm.ss.nnnnnn 1 26 bytes
TIMESTAMP_C yyyymmddhhmmss 14 bytes
TIMESTAMP_D yymmddhhmmss 2 12 bytes
TIMESTAMP_E yyyymmddhhmmssnnnnnn 20 bytes
TIMESTAMP_F yymmddhhmmssnnnnnn 2 18 bytes

If a timestamp format is specified for a field that is used in a field specification, the field specification must also use the specified timestamp format.

Notes:
  1. If the format includes separators, the separator can be any single-byte character that can be converted to a single-byte EBCDIC character.
  2. If a two-digit year is specified, it is expanded to a four-digit year. If the two-digit year is less than the sum of the two-digit current year plus 50, then the current century is used in the four-digit year. For example, assume that the current year is 2017. If the two-digit year is 67, 1967 is used. However, if the two-digit year is 66, 2066 is used.
TIMESTAMP WITH TIME ZONE EXTERNAL(length)
Specifies a character string representation of a timestamp with time zone. The default for length is 33 bytes. If you specify a length, it must be within the range of 26 to 39 bytes.
Timestamp with time zone can be in any of the following formats. nnnnnn represents the number of digits in the fractional seconds, and can be 0 - 12 digits. You can omit leading zeros from the month, day, or hour parts of the timestamp; you can omit trailing zeros from the fractional seconds part of the timestamp.
  • yyyy-mm-dd-hh.mm.ss.nnnnnn±th:tm
  • yyyy-mm-dd-hh.mm.ss.nnnnnn ±th:tm
  • yyyy-mm-dd hh:mm:ss.nnnnnn±th:tm
  • yyyy-mm-dd hh:mm:ss.nnnnnn ±th:tm
ROWID
Specifies a row ID. The input data must be a valid value for a row ID; Db2 does not perform any conversions.

A field specification for a row ID column is not allowed if the row ID column was created with the GENERATED ALWAYS option.

If the row ID column is part of the partitioning key, LOAD INTO TABLE PART is not allowed; specify LOAD INTO TABLE instead.

BLOB
Specifies a BLOB field. You must specify the length in bytes in a 4-byte binary field that precedes the data. (The length does not include the 4-byte field itself.) The length field must start in the column that is specified as start in the POSITION option. If :end is used, it is ignored.
CLOB
Specifies a CLOB field. You must specify the length in bytes in a 4-byte binary field that precedes the data. (The length does not include the 4-byte field itself.) The length field must start in the column that is specified as start in the POSITION option. If :end is used, it is ignored.
MIXED
Specifies that the input field contains mixed SBCS and DBCS data. If MIXED is specified, any required CCSID conversions use the mixed CCSID for the input data; if MIXED is not specified, any such conversions use the SBCS CCSID for the input data.
Start of changeCCSID 1208End of change
Start of changeSpecifies that the data in the CLOB input file is in CCSID 1208 (UTF-8). If data conversion occurs, CCSID 1208 is used for the input data.End of change
DBCLOB
Specifies a DBCLOB field. You must specify the length in double-byte characters in a 4-byte binary field that precedes the data. (The length does not include the 4-byte field itself.) The length field must start in the column that is specified as start in the POSITION option. If :end is used, it is ignored.Start of change
Start of changeCCSID 1200End of change
Start of changeSpecifies that the data in the CLOB input file is in CCSID 1200 (UTF-16). If data conversion occurs, CCSID 1200 is used for the input data.End of change
End of change
DECFLOAT (length)
Specifies either a 128-bit decimal floating-point number or a 64-bit decimal floating-point number. The value of the length must be either 16 or 34. If the length is 16, the number is in 64 bit decimal floating-point number format. If the length is 34, the number is in 128 bit decimal floating-point format. If the length is not specified, the number is in 128 bit decimal floating-point format.
DECFLOAT EXTERNAL (length)
Specifies a string of characters that represent a number. The format is an SQL numeric constant. If you do not specify a length, the length of the string is determined from the POSITION specification. If you do not specify a length or POSITION, LOAD uses the default length for DECFLOAT.
XML
Specifies the input field type is XML. Field type XML can only be loaded to a XML column. Specify XML when loading the XML value directly from the input record. If the format of the input record is in nondelimited, you must specify a 2 byte length field precedes the actual data value.

BINARYXML Specifies that the XML document to be loaded using the file reference variables is in binary XML format.

PRESERVE WHITESPACE
Specifies that the white space in the XML column is preserved. The default is not to preserve the white space.
Start of change DEFAULTIF defaultif-condition End of change
Start of change

Specifies that the field (identified in the field-specification) is to be loaded with the default column value if the specified condition is true.

defaultif-condition

The condition that is evaluated. For each field, you can specify only one condition.

Within this condition, specify any character-string constants in the same character set as the input data record. For example, specify EBCDIC constants if your data is in EBCDIC and specify UNICODE constants if your data is in UNICODE. You can also code the DEFAULTIF condition using the hexadecimal form. For example, if the input data is in EBCDIC and the control statement is in UTF-8, use (1:1)=X'31' in the condition rather than (1:1)='1'.

If you use DEFAULTIF with the ROWID keyword, and the condition is met, the column is loaded with a value that Db2 generates.

You cannot specify DEFAULTIF for XML columns.

CONV_ERROR

Specifies that the condition is a conversion error. If a conversion error occurs, the default value is loaded.

field-name

The name of the field to be compared with a constant value. You must specify the name of a field that is defined in the field-specification. If field-name is used, the start and end positions of the field are given by the POSITION option of the field specification.

The field that you specify must meet all of the following requirements:

  • Contain a character or graphic string. No data type conversions are performed when the contents of the field in the input record are compared to a string constant.
  • Start at the same byte offset in each assembled input record. If any record contains varying-length strings, which are stored with length fields, that precede the selection field, they must be padded so that the start of the selection field is always at the same offset.

The field and the constant do not need to be the same length. If they are not, the shorter of the two is padded before a comparison is made. Character and graphic strings are padded with blanks. Hexadecimal strings are padded with zeros.

If this field is a VARCHAR or VARGRAPHIC field, Db2 takes the length of the field from the 2-byte binary field before the data portion of the VARCHAR or VARGRAPHIC field.

= <>
Specifies the type of comparison between the field ( field-name or start:end) and constant (X'byte-string', 'character-string', G'graphic-string', or N'graphic-string').
=
Equal to
<>
Not equal to
(start:end)

Identifies column numbers in the assembled load record to use for the comparison in the DEFAULTIF condition. The first column of the record is column 1. The two numbers indicate the starting and ending columns of a selection field in the load record.

If end is not used, the field is assumed to have the same length as the constant.

X'byte-string'
A string of hexadecimal characters. For example, the following condition specifies that the default value is to be loaded if the record has the value X'FFFF' in columns 33 through 34.
(33:34) = X'FFFF'
'character-string'
A string of characters. For example, the following clause specifies that the default value is to be loaded if the field DEPTNO has the value D11.
DEPTNO = 'D11'

If the field uses a specific external date, time, or timestamp format, the field specification must use a character string that matches the specified format.

G'graphic-string'
A string of double-byte characters. For example, the following clause specifies that the default is to be loaded if the record has the specified value in columns 33 through 36.
(33:36) = G'<**>'
In this example, < is the shift-out character, * is a double-byte character, and > is the shift-in character.

If the first or last byte of the input data is a shift-out character, it is ignored in the comparison. Specify G as an uppercase character.

N'graphic-string'
A string of double-byte characters. N and G are synonymous for specifying graphic string constants. Specify N as an uppercase character.
End of change
NULLIF field-selection-criterion
Describes a condition that causes the Db2 column to be loaded with NULL. You can write the field-selection-criterion with the same options as described under field-selection-criterion. If the contents of the NULLIF field match the provided character constant, the field that is specified in field-specification is loaded with NULL.

If the NULLIF field is defined by the name of a VARCHAR or VARGRAPHIC field, Db2 takes the length of the field from the 2-byte binary field that appears before the data portion of the VARCHAR or VARGRAPHIC field.

To load a null value into a BLOBF, CLOBF, or DBCLOBF field, use a null input file name.

Character-string constants should be specified in LOAD utility control statements in the character set that matches the input data record. Specify EBCDIC constants in the LOAD control statement if your data is in EBCDIC and specify UNICODE constants if your data is in UNICODE. You may also code the NULLIF condition using the hexadecimal form. For example, if the input data is in EBCDIC and the control statement is in UTF-8, use (1:1)=X'31' in the condition rather than (1:1)='1'.

The fact that a field in the output table is loaded with NULL does not change the format or function of the corresponding field in the input record. The input field can still be used in a field selection criterion. For example, assume that a LOAD statement has the following field specification:

(FIELD1 POSITION(*) CHAR(4)
 FIELD2 POSITION(*) CHAR(3) NULLIF(FIELD1='SKIP')
 FIELD3 POSITION(*) CHAR(5))

Assume also that LOAD is to process the following source record:

SKIP   FLD03

In this example, the record is loaded as follows:

FIELD1
Has the value 'SKIP'.
FIELD2
Is NULL (not ' ' as in the source record).
FIELD3
Has the value 'FLD03'.

You cannot use the NULLIF parameter with the ROWID keyword because row ID columns cannot be null.

Field selection criterion

Describes a condition that causes the Db2 column to be loaded with NULL or with its default value.