LOAD command using the ADMIN_CMD procedure

Restrictions

The load utility does not support loading data at the hierarchy level. The load utility is not compatible with range-clustered tables. The load utility does not support the NOT LOGGED INITIALLY parameter for the CREATE TABLE or ALTER TABLE statements.

Scope

This command can be issued against multiple database partitions in a single request.

Authorization

Db2 server processes, including all load processes are owned by the instance owner. Because these processes use the identification of the instance owner to access needed files, the instance owner requires read access to input data files. These input data files must be readable by the instance owner, regardless of who runs the command.

Required connection

Database.

Instance. An explicit attachment is not required. If a connection to the database is established, an implicit attachment to the local instance is attempted.

Command syntax

Ignore and Map parameters

Redirect Input or Output parameters

Notes:

• ¹ These keywords can appear in any order.
• ² Each of these keywords can appear only one time.
• ³ For column-organized tables, the default is the STATISTICS USE PROFILE parameter.

Command parameters

FROM filename | remotefilename | pipename | device(query-statement) | (DATABASE database-alias query-statement)

Specifies the file, pipe, or device that refers to an SQL statement that contains the data to load, or the SQL statement itself and the optional source database to load from cursor.

The query-statement option is used to LOAD from a cursor. It contains only one query statement, which is enclosed in parentheses, and can start with VALUES, SELECT or WITH. For example,

LOAD FROM (SELECT * FROM T1) OF CURSOR INSERT INTO T2

When the DATABASE database-alias clause is included before the query statement in the parentheses, the LOAD command attempts to load the data by using the query-statement from the specified database as indicated by the database-alias name. The LOAD runs by using the user ID and password that is explicitly provided for the currently connected database. An implicit connection causes the LOAD to fail.

If the input source is a file, pipe, or device, it must be accessible from the coordinator partition on the server.

A remotefilename refers to a file that is on remote storage, such as IBM® Cloud Object Storage or Amazon Simple Storage Service (S3), and is accessed by using a storage access alias. Local staging space is required to temporarily store the file that is transferred from the remote storage server; refer to Remote storage requirements. The syntax of remote file names is:

DB2REMOTE://<alias>/<container>/<object>

If several names are specified, they are processed in sequence. If the last item specified is a tape device and the user is prompted for a tape, the LOAD fails and the ADMIN_CMD procedure returns an error.

Note:

• A fully qualified path file name must be used and must exist on the server.
• If data is exported into a file with the EXPORT command using the ADMIN_CMD procedure, the data file is owned by the fenced user ID. This file is not usually accessible by the instance owner. To run the LOAD from the CLP or the ADMIN_CMD procedure, the data file must be accessible by the instance owner ID. Therefore, read access to the data file must be granted to the instance owner.
• Loading data from multiple IXF files is supported if the files are physically separate, but logically one file. It is not supported if the files are both logically and physically separate. If more than one logically and physically separate files are specified, then any file after the first one is ignored. (Multiple physical files would be considered one logically file I you created them with one invocation of the EXPORT command.)
• When you load XML data from files into tables in a partitioned database environment, the XML data files must be read-accessible to all the database partitions where loading is taking place.
• If DB2_LOAD_RESTRICTED_IO_PATH is enabled, and a file name is specified, the file or files must exist within the restricted paths.

OF filetype

Specifies the format of the data:

• ASC (nondelimited ASCII format)
• DEL (delimited ASCII format)
• IXF (Integration Exchange Format, PC version) is a binary format that is used exclusively by Db2 databases.
• CURSOR (a cursor declared against a SELECT or VALUES statement).

Note:

• When you use a CURSOR file type to load XML data into a table in a distributed database environment, the PARTITION_ONLY and LOAD_ONLY modes are not supported.
• Note the following difference when you run the LOAD command with a CURSOR file type:
  • If the DATABASE keyword was specified during the DECLARE CURSOR statement, the LOAD command internally creates a separate application to fetch the data.
  • If the DATABASE keyword is not specified during the DECLARE CURSOR statement, the LOAD command fetches data within the same application.
  This difference between the two cases can also cause locking behavior difference. In particular, a lock (such as a lock wait or lock timeout, depending on the database configuration) might be created if the following conditions are met:

  • You specify the DATABASE keyword for the same database as the currently connected database.
  • You connect to the same database as the current connection with the same user ID and password.

  The workaround for this lock issue is to omit the DATABASE keyword.

LOBS FROM lob-path

The path to the data files that contain LOB values to be loaded. The path must end with a slash. The path must be fully qualified and accessible from the coordinator partition on the server. The names of the LOB data files are stored in the main data file (ASC, DEL, or IXF), in the column that is loaded into the LOB column. The maximum number of paths that can be specified is 999. This clause implicitly activates the LOBSINFILE behavior.

This option is ignored when specified with the CURSOR file type.

If DB2_LOAD_RESTRICTED_IO_PATH is enabled, the lob-path must exist within the restricted paths.

MODIFIED BY file-type-mod

Specifies file type modifier options. See File type modifiers for the load utility.

METHOD

L

Specifies the start and end column numbers from which to load data. A column number is a byte offset from the beginning of a row of data. It is numbered starting from 1. This method can be used only with ASC files, and is the only valid method for that file type.

NULL INDICATORS null-indicator-list

This option can be used only when the METHOD L parameter is specified (to indicate that the input file is an ASC file). The null indicator list is a comma-separated list of positive integers that specify the column number of each null indicator field. The column number is the byte offset of the null indicator field from the beginning of a row of data. The null indicator list must contain one entry for each data field that is defined in the METHOD L parameter. A column number of zero indicates that the corresponding data field always contains data.

A value of Y in the NULL indicator column specifies that the column data is NULL. Any character other than Y in the NULL indicator column specifies that the column data is not NULL and column data that is specified by the METHOD L option is loaded.

The NULL indicator character can be changed by using the MODIFIED BY option.

N

Specifies the names of the columns in the data file to be loaded. The case of these column names must match the case of the corresponding names in the system catalogs. Each table column that is not nullable must have a corresponding entry in the METHOD N list. For example, assume that data fields are F1, F2, F3, F4, F5, and F6, and table columns are C1 INT, C2 INT NOT NULL, C3 INT NOT NULL, and C4 INT. In this scenario, method N (F2, F1, F4, F3) is a valid request. However, method N (F2, F1) is not a valid request. This method can be used only with file types IXF or CURSOR.

P

Specifies the field numbers (numbered from 1) of the input data fields to be loaded. Each table column that is not nullable must have a corresponding entry in the METHOD P list. For example, assume that data fields are F1, F2, F3, F4, F5, and F6, and table columns are C1 INT, C2 INT NOT NULL, C3 INT NOT NULL, and C4 INT. In this scenario, method P (2, 1, 4, 3) is a valid request. However, method P (2, 1) is not a valid request. This method can be used only with file types IXF, DEL, or CURSOR, and is the only valid method for the DEL file type.

For each of the fields specified by method P, you must define a corresponding column in the action statement. This requirement can be lifted if all columns are accounted for, or the first x columns are going to be loaded. This scenario is shown in the following example:

  db2 load from datafile1.del of del method P(1, 3, 4)
      replace into table1 (c1, c3, c4) 

XML FROM xml-path

Specifies one or more paths that contain the XML files. XDSs are contained in the main data file (ASC, DEL, or IXF), in the column that is loaded into the XML column.

If DB2_LOAD_RESTRICTED_IO_PATH is enabled, the xml-path must exist within the restricted paths.

XMLPARSE

Specifies how XML documents are parsed. If this option is not specified, the parsing behavior for XML documents is determined by the value of the CURRENT IMPLICIT XMLPARSE OPTION special register.

STRIP WHITESPACE

Specifies to remove white space when the XML document is parsed.

PRESERVE WHITESPACE

Specifies not to remove white space when the XML document is parsed.

XMLVALIDATE

Specifies that XML documents are validated against a schema, when applicable.

USING XDS

XML documents are validated against the XML schema that is identified by the XML Data Specifier (XDS) in the main data file. By default, if the XMLVALIDATE option is used with the USING XDS clause, the schema that is used for validation is determined by the SCH attribute of the XDS. If an SCH attribute is not present in the XDS, no schema validation occurs unless a default schema is specified by the DEFAULT clause.

The DEFAULT, IGNORE, and MAP clauses can be used to modify the schema determination behavior. These three optional clauses apply directly to the specifications of the XDS, and not to each other. For example, if a schema is selected because it is specified by the DEFAULT clause, it is not ignored if it is also specified by the IGNORE clause. Similarly, a schema is not remapped if the following conditions are met:

• The schema is selected because it is specified as the first part of a pair in the MAP clause.
• The schema is also specified in the second part of another MAP clause pair.

USING SCHEMA schema-sqlid

XML documents are validated against the XML schema with the specified SQL identifier. In this case, the SCH attribute of the XDS is ignored for all XML columns.

USING SCHEMALOCATION HINTS

XML documents are validated against the schemas that are identified by XML schema location hints in the source XML documents. If a schemaLocation attribute is not found in the XML document, no validation occurs. When the USING SCHEMALOCATION HINTS clause is specified, the SCH attribute of the XDS is ignored for all XML columns.

See examples of the XMLVALIDATE option in the following section.

IGNORE schema-sqlid

This option can be used only when the USING XDS parameter is specified. The IGNORE clause specifies a list of one or more schemas to ignore if they are identified by an SCH attribute. No schema validation occurs for the loaded XML document if both of the following conditions are met:

• An SCH attribute exists in the XML Data Specifier (XDS) for a loaded XML document.
• The schema that is identified by the SCH attribute is included in the list of schemas to ignore.

Note:

If a schema is specified in the IGNORE clause, it cannot also be present in the left side of a schema pair in the MAP clause.

The IGNORE clause applies only to the XDS. A schema that is mapped by the MAP clause is not ignored later if it is specified by the IGNORE clause.

DEFAULT schema-sqlid

This option can be used only when the USING XDS parameter is specified. The schema that is specified in the DEFAULT clause identifies a schema to use for validation when the XDS of an XML document does not contain an SCH attribute that identifies an XML schema.

The DEFAULT clause takes precedence over the IGNORE and MAP clauses. If an XDS satisfies the DEFAULT clause, the IGNORE and MAP specifications are ignored.

MAP schema-sqlid

This option can be used only when the USING XDS parameter is specified. Use the MAP clause to specify alternative schemas to use in place of schemas specified by the SCH attribute of an XDS for each loaded XML document. The MAP clause specifies a list of one or more schema pairs, where each pair represents a mapping of one schema to another. The first schema in the pair represents a schema that is referred to by an SCH attribute in an XDS. The second schema in the pair represents the schema to be used for schema validation.

If a schema is present in the left side of a schema pair in the MAP clause, it cannot also be specified in the IGNORE clause.

After a schema pair mapping is applied, the result is final. The mapping operation is nontransitive. Therefore, the schema that is chosen is not applied to another schema pair mapping later.

A schema cannot be mapped more than one time, meaning that it cannot appear on the left side of more than one pair.

SAVECOUNT n

Specifies that the load utility is to establish consistency points after every n rows. This value is converted to a page count, and rounded up to intervals of the extent size. Since a message is issued at each consistency point, selected this option if the load operation is monitored by using LOAD QUERY. If the value of n is not sufficiently high, the synchronization of activities that are done at each consistency point might impact performance.

The default value is zero, meaning that no consistency points are established, unless necessary.

This option is not allowed when specified with the CURSOR file type or when you load a table that contains an XML column.

The SAVECOUNT parameter is not supported for column-organized tables.

ROWCOUNT n

Specifies the number of n physical records in the file to be loaded. If the anyorder file type modifier is enabled, any n rows can be loaded from the file. Otherwise, the first n rows are loaded.

Note: The anyorder file type modifier is enabled by default for certain table types. For more information, see anyorder.

WARNINGCOUNT n

Stops the load operation after n warnings. Set this parameter if no warnings are expected, but you want verification that the correct file and table are being used. If the load file or the target table is specified incorrectly, the load utility generates a warning for each row that it attempts to load, which causes the load to fail. If n is zero, or this option is not specified, the load operation continues regardless of the number of warnings issued.

If the load operation is stopped because the threshold of warnings is encountered, another load operation can be started in RESTART mode. The load operation automatically continues from the last consistency point. Alternatively, another load operation can be initiated in REPLACE mode, starting at the beginning of the input file.

In a partitioned database environment, a LOAD operation can have multiple load and partition agents. Each agent has a WARNINGCOUNT value. If the value of n is reached on a single agent, the LOAD operation fails. The n values are not cumulative. For example, if n is 3 and two agents each have a WARNINGCOUNT of 2, the LOAD operation is successful.

MESSAGES ON SERVER

Specifies that the message file created on the server by the LOAD command is to be saved. The result set returned includes the following two columns:

• MSG_RETRIEVAL, which is the SQL statement that is required to retrieve all the warnings and error messages that occur during this operation.
• MSG_REMOVAL, which is the SQL statement that is required to clean up the messages.

If this clause is not specified, the message file is deleted when the ADMIN_CMD procedure returns to the caller. The MSG_RETRIEVAL and MSG_REMOVAL columns in the result set contain null values.

With or without the clause, the fenced user ID or the instance owner's primary group must have the authority to create files under the directory indicated by the DB2_UTIL_MSGPATH registry variable.

TEMPFILES PATH temp-pathname

Specifies the name of the path to be used when temporary files are created during a load operation, and must be fully qualified according to the server database partition.

Temporary files take up file system space. Sometimes, this space requirement is substantial. The following list provides an estimate of how much file system space to allocate for all temporary files:

• 136 bytes for each message that the load utility generates.
• 15 KB if the data file contains long field data or LOBs. This quantity can grow significantly if you specify the INSERT parameter and the table already has a large amount of long field or LOB data.
• On the server, storage space that is equivalent to the raw size of the input data, if the following conditions are met:
  • The column compression dictionary is to be built.
  • The data source, such as a pipe or a socket, cannot be reopened.

If DB2_LOAD_RESTRICTED_IO_PATH is enabled, the temp-pathname must exist within the restricted paths.

INSERT

Adds the loaded data to the table without changing the existing table data.

A LOAD INSERT operation into a column-organized table updates the table statistics by default if the table is new, or was truncated, and is empty at the start of the load operation.

REPLACE

Deletes all data from the table, and inserts the new data. The table definition and index definitions are not changed. If you specify this parameter when you move data between hierarchies, you can replace only the data for an entire hierarchy, not individual subtables.

You cannot use this parameter to load data into system-period temporal tables.

A LOAD REPLACE operation into a column-organized table updates table statistics by default.

KEEPDICTIONARY

An existing compression dictionary is preserved across the LOAD REPLACE operation.

This option is the default for row-organized tables.

If the table COMPRESS attribute is YES, the newly replaced data is subject to being compressed by using the dictionary that existed before the invocation of the load. A new dictionary is built by using the data that is being replaced into the table if the following conditions are met:

• A dictionary didn't exist previously in the table.
• The table COMPRESS attribute is YES.

The amount of data that is required to build the compression dictionary in this case is subject to the policies of ADC. This data is populated into the table as decompressed data. After the dictionary is inserted into the table, the remaining data to be loaded is subject to being compressed with this dictionary. For a summary, see the following table.

The following example keeps the current dictionary if it exists in the table:

CALL SYSPROC.ADMIN_CMD('load from staff.del of del replace
 keepdictionary into SAMPLE.STAFF statistics use profile
 data buffer 8')

Table 1. LOAD REPLACE KEEPDICTIONARY keyword.

Compress	Table row data dictionary exists	XML storage object dictionary exists1	Compression dictionary	Data compression
YES	YES	YES	Preserve table row data and XML dictionaries.	Data to be loaded is subject to compression.
YES	YES	NO	Preserve table row data dictionary and build a new XML dictionary.	Table row data to be loaded is subject to compression. After XML dictionary is built, remaining XML data to be loaded is subject to compression.
YES	NO	YES	Build table row data dictionary and preserve XML dictionary.	After table row data dictionary is built, remaining table row data to be loaded is subject to compression. XML data to be loaded is subject to compression.
YES	NO	NO	Build new table row data and XML dictionaries.	After dictionaries are built, remaining data to be loaded is subject to compression.
NO	YES	YES	Preserve table row data and XML dictionaries.	Data to be loaded is not compressed.
NO	YES	NO	Preserve table row data dictionary.	Data to be loaded is not compressed.
NO	NO	YES	No effect on table row dictionary. Preserve XML dictionary.	Data to be loaded is not compressed.
NO	NO	NO	No effect.	Data to be loaded is not compressed.

Note:

1. A compression dictionary can be created for the XML storage object of a table only when either of the following conditions are met:
   • The XML columns are added to the table in Db2 Version 9.7 or later.
   • The table is migrated by using an online table move.
2. If LOAD REPLACE KEEPDICTIONARY operation is interrupted, load utility can recover after either LOAD RESTART or LOAD TERMINATE is issued. An existing XML storage object dictionary cannot be preserved after recovery from interrupted LOAD REPLACE KEEPDICTIONARY operation. A new XML storage object dictionary is created if LOAD RESTART is used.

RESETDICTIONARY

This directive instructs LOAD REPLACE processing to build a new dictionary for the table data object, if the table COMPRESS attribute is YES.

If the COMPRESS attribute is NO and a dictionary was already present in the table, it is removed and a new dictionary is not inserted into the table.

This option is the default for column-organized tables.

For column-organized tables, you cannot specify LOAD REPLACE … RESETDICTIONARY on a subset of database partitions (SQL27906N). If you include the OUTPUT_DBPARTNUMS option, all database partitions must be specified.

A compression dictionary can be built with just one user record. If the loaded data set size is zero and a dictionary exists, the existing dictionary is not preserved. The amount of data required to build a dictionary with this directive is not subject to the policies of ADC. For a summary, see the following table.

The following example resets the current dictionary and makes a new one:

CALL SYSPROC.ADMIN_CMD('load from staff.del of del replace
 resetdictionary into SAMPLE.STAFF statistics use profile
 data buffer 8')

Table 2. LOAD REPLACE RESETDICTIONARY keyword.

Compress	Table row data dictionary exists	XML storage object dictionary exists1	Compression dictionary	Data compression
YES	YES	YES	Build new dictionaries2. If the DATA CAPTURE CHANGES option is enabled on the CREATE TABLE or ALTER TABLE statements, the current table row data dictionary is kept (and referred to as the historical compression dictionary).	After dictionaries are built, remaining data to be loaded is subject to compression.
YES	YES	NO	Build new dictionaries2. If the DATA CAPTURE CHANGES option is enabled on the CREATE TABLE or ALTER TABLE statements, the current table row data dictionary is kept (and referred to as the historical compression dictionary).	After dictionaries are built, remaining data to be loaded is subject to compression.
YES	NO	YES	Build new dictionaries.	After dictionaries are built, remaining data to be loaded is subject to compression.
YES	NO	NO	Build new dictionaries.	After dictionaries are built, remaining data to be loaded is subject to compression.
NO	YES	YES	Remove dictionaries.	Data to be loaded is not compressed.
NO	YES	NO	Remove table row data dictionary.	Data to be loaded is not compressed.
NO	NO	YES	Remove XML storage object dictionary.	Data to be loaded is not compressed.
NO	NO	NO	No effect.	All table data is not compressed.

Notes:

1. A compression dictionary can be created for the XML storage object of a table only when either of the following conditions are met:
   • The XML columns are added to the table in Db2 Version 9.7 or later.
   • The table is migrated by using an online table move.
2. If a dictionary exists and the compression attribute is enabled, but no records exist to load into the table partition, a new dictionary cannot be built. In this scenario, the RESETDICTIONARY operation does not keep the existing dictionary.

RESETDICTIONARYONLY

This option creates a column compression dictionary that is based on the input file, without loading any rows. You can use this option to create the compression dictionary before you ingest any data by using SQL-based utilities.

This option is applicable to column-organized tables only.

You cannot specify LOAD REPLACE … RESETDICTIONARYONLY on a subset of database partitions (SQL27906N). If you include the OUTPUT_DBPARTNUMS option, all database partitions must be specified.

TERMINATE

One of four modes under which the load utility can run. This mode stops a previously interrupted load operation, and rolls back the operation to the point in time at which it started, even if consistency points were passed. The states of any table spaces that are involved in the operation return to normal, and all table objects are made consistent. Index objects might be marked as invalid, in which case index rebuild automatically takes place at next access. If you are ending a LOAD REPLACE operation, the table is truncated to an empty table after the LOAD TERMINATE operation. If you stop a LOAD INSERT operation, the table retains all of its original records after the LOAD TERMINATE operation. For summary of dictionary management, see Table 3.

The LOAD TERMINATE option does not remove a backup pending state from table spaces.

RESTART

Important: The RESTART option of the LOAD command is deprecated for Db2 version 11.5.8, and will be discontinued in a future release or modification pack. Use LOAD TERMINATE followed by LOAD to achieve equivalent behavior.

Restarts an interrupted load operation. The load operation automatically continues from the last consistency point in the load, build, or delete phase. For summary of dictionary management, see Table 4.

The RESTART parameter is not supported for the following tables:

• Column-organized tables.
• Random distribution tables that use the random by generation method.

To recover a table of this type after a failed load operation, use the TERMINATE or REPLACE parameter.

INTO table-name

Specifies the database table into which the data is to be loaded. This table cannot be a system table, a declared temporary table, or a created temporary table. An alias, or the fully qualified or unqualified table name can be specified. A qualified table name is in the form schema.tablename. If an unqualified table name is specified, the table is qualified with the current schema.

If the database table contains implicitly hidden columns, you must specify whether data for the hidden columns is included in the load operation. Use one of the following methods to indicate whether data for hidden columns is included:

• Use insert-column to explicitly specify the columns into which data is to be inserted.

  db2 load from delfile1 of del
     insert into table1 (c1, c2, c3,...)

• Use one of the hidden column file type modifiers: specify implicitlyhiddeninclude when the input file contains data for the hidden columns, or implicitlyhiddenmissing when the input file does not.

  db2 load from delfile1 of del modified by implicitlyhiddeninclude
     insert into table1

• Use the DB2_DMU_DEFAULT registry variable on the server-side to set the default behavior when data movement utilities encounter tables with implicitly hidden columns. Specify IMPLICITLYHIDDENINCLUDE when utilities assume that the implicitly hidden columns are included, or IMPLICITLYHIDDENMISSING when utilities assume that the implicitly hidden columns are not included.

  db2set DB2_DMU_DEFAULT=IMPLICITLYHIDDENINCLUDE
  db2 load from delfile1 of del insert into table1

insert-column

Specifies the table column into which the data is to be inserted.

The load utility cannot parse columns whose names contain one or more spaces. The following example fails:

CALL SYSPROC.ADMIN_CMD('load from delfile1 of del noheader 
   method P (1, 2, 3, 4, 5, 6, 7, 8, 9)
   insert into table1 (BLOB1, S2, I3, Int 4, I5, I6, DT7, I8, TM9)')

The failure occurs because of the Int 4 column. The solution is to enclose such column names with double quotation marks:

CALL SYSPROC.ADMIN_CMD('load from delfile1 of del noheader 
   method P (1, 2, 3, 4, 5, 6, 7, 8, 9)
   insert into table1 (BLOB1, S2, I3, "Int 4", I5, I6, DT7, I8, TM9)')

FOR EXCEPTION table-name

Specifies the exception table into which rows in error are copied. Any row that violates a unique index or a primary key index is copied. If you specify an unqualified table name, the table name is qualified with the current schema. The table cannot be a column-organized table.

Information that is written to the exception table is not written to the dump file. In a partitioned database environment, an exception table must be defined for those database partitions on which the loading table is defined. The dump file, otherwise, contains rows that cannot be loaded because they are invalid or have syntax errors.

When you load XML data, you cannot use the FOR EXCEPTION clause to specify a load exception table in the following cases:

• When you use label-based access control (LBAC).
• When you load data into a partitioned table.

NORANGEEXC

Indicates that if a row is rejected because of a range violation, it is not be inserted into the exception table.

NOUNIQUEEXC

Indicates that if a row is rejected because it violates a unique constraint, it is not be inserted into the exception table.

STATISTICS USE PROFILE

Instructs load to collect statistics during the load according to the profile defined for this table. Collecting statistics is the default behavior for column-organized tables. The profile must be created before you run the LOAD command. The profile is created by the RUNSTATS command. For row-organized tables, if the profile does not exist and this parameter is specified, a warning is returned and no statistics are collected. For column-organized tables, if the profile does not exist and this parameter is specified, the load utility uses the same default RUNSTATS command options that are used during an automatic RUNSTATS operation.

During load, distribution statistics are not collected for columns of type XML.

STATISTICS NO

Specifies that no statistics are to be collected, and that the statistics in the catalogs are not to be altered. This parameter is the default for row-organized tables.

COPY NO

Specifies that the table space in which the table exists is placed in backup pending state, if forward recovery is enabled. Forward recovery is enabled if the logarchmeth1 or logarchmeth2 configuration parameters are set to a value other than OFF. The COPY NO option also puts the table space state into the Load in Progress table space state. This state, which is transient, disappears when the load completes or fails. The data in any table in the table space cannot be updated or deleted until a table space backup or a full database backup is made. However, it is possible to access the data in any table by using the SELECT statement.

Specifying COPY NO on a recoverable database leaves the table spaces in a backup pending state. For example, indexes need to be refreshed if you run LOAD with COPY NO and INDEXING MODE DEFERRED. Certain queries on the table might require an index scan but fail until the indexes are refreshed. The index cannot be refreshed if it exists in a table space that is in the backup pending state. In that case, access to the table is not be allowed until a backup is taken. Index refresh is done automatically by the database when the index is accessed by a query.

COPY NO is the default option when the following conditions are met:

• COPY NO, COPY YES, or NONRECOVERABLE is not specified.
• The database is recoverable, which is the case when the logarchmeth1 or logarchmeth2 configuration parameters are set to value other than OFF.

COPY YES

Saves a copy of the loaded data. This parameter is invalid if forward recovery is turned off.

USE TSM

Specifies that the copy is stored by using IBM Tivoli® Storage Manager.

OPEN num-sess SESSIONS

The number of I/O sessions to be used with IBM Tivoli Storage Manager or the vendor product. The default value is 1.

TO device/directory

Specifies the device or directory on which the copy image is created.

The directory can be on a remote storage, such as IBM® Cloud Object Storage or Amazon Simple Storage Service (S3), and can be accessed by using a storage access alias. When DB2_ENABLE_COS_SDK is set to OFF, local staging space is required to temporarily store the copy image that is to be transferred to the remote storage server. The maximum copy image size for the remote storage is 5 GB. Refer to Remote Storage Requirements. When DB2_ENABLE_COS_SDK is set to ON, local staging space is not required. The maximum copy image size is determined by the MULTIPARTSIZEMB size, which is multiplied by the maximum number of parts that are allowed by the Cloud Object Storage provider.

If DB2_LOAD_RESTRICTED_IO_PATH is enabled, the copy image path must exist within the restricted paths.

MULTIPARTSIZEMB

When the DB2_ENABLE_COS_SDK registry variable is set to ON, Db2 remote storage communication with Cloud Object Storage is facilitated through an embedded vendor Cloud Object Storage SDK. Remote storage communications allow Db2 to stream objects or files to Cloud Object Storage in multiple parts. This type of streaming is called multipart upload. This keyword specifies the part size, in megabytes (MB), for the copy image, and overrides the value that is specified in the MULTIPARTSIZEMB database manager configuration parameter. This option is available starting in version 11.5.7, in Linux® (x86) environments only.

LOAD lib-name

The name of the shared library (DLL on Windows operating systems) containing the vendor backup and restore I/O functions to be used. It can contain the full path. If the full path is not given, the default path is the location of the user exit programs.

NONRECOVERABLE

Specifies that the load transaction is to be marked as unrecoverable and that it is not possible to recover it by a subsequent rollforward action. The rollforward utility skips the transaction and marks the table into which data was being loaded as invalid. The utility also ignores any subsequent transactions against that table. After the rollforward operation completes, the only supported operations on such a table are:

• Drop the table.
• Restored the table from a backup (full or table space) that was taken after a commit point that follows the completion of the unrecoverable load operation.

With this option, table spaces are not put in backup pending state after the load operation. A copy of the loaded data is not made during the load operation. If one of COPY NO, COPY YES, or NONRECOVERABLE is not specified, and the database is not recoverable (logarchmeth1 and logarchmeth2 are both set to OFF), then NONRECOVERABLE is the default.

WITHOUT PROMPTING

Specifies that the list of data files contains all the files that are to be loaded, and that the devices or directories that are listed are sufficient for the entire load operation. If a continuation input file is not found, or the copy targets are filled before the load operation finishes, the load operation fails, and the table remains in load pending state.

This option is activated by default. Any actions that normally require user intervention return an error message.

DATA BUFFER buffer-size

Specifies the number of 4 KB pages (regardless of the degree of parallelism) to use as buffered space for transferring data within the utility. If you specify a value that is less than the algorithmic minimum, the minimum required resource is used, and no warning is returned.

This memory is allocated directly from the utility heap, whose size can be modified through the util_heap_sz database configuration parameter. Beginning in version 9.5, the value of the DATA BUFFER option of the LOAD command can temporarily exceed util_heap_sz if more memory is available in the system. In this situation, the utility heap is dynamically increased as needed until the database_memory limit is reached. This memory is released after the load operation completes.

If a value is not specified, an intelligent default is calculated by the utility at run time. The calculation is based on the following items:

• A percentage of the free space available in the utility heap at the instantiation time of the loader.
• Some characteristics of the table.

SORT BUFFER buffer-size

This option specifies a value that overrides the sortheap database configuration parameter during a load operation. It is relevant only when loading tables with indexes and only when the INDEXING MODE parameter is not specified as DEFERRED. The value that is specified cannot exceed the value of sortheap. This parameter is useful for throttling the sort memory that is used when you load tables with many indexes without changing the value of sortheap, which would also affect general query processing.

CPU_PARALLELISM n

Specifies the number of processes or threads that the load utility creates for parsing, converting, and formatting records when table objects are built. This parameter is designed to use the number of processes that run per database partition. This parameter is useful when you load presorted data, because record order in the source data is preserved. If the value of this parameter is zero, or was not specified, the load utility uses an intelligent default value (usually based on the number of CPUs available) at run time.

Note:

1. If this parameter is used with tables that contain LOB or LONG VARCHAR fields, its value becomes 1, regardless of the number of system CPUs or the value that was specified by the user.
2. Specifying a small value for the SAVECOUNT parameter causes the loader to run many more I/O operations to flush both data and table metadata. When CPU_PARALLELISM is greater than one, the flushing operations are asynchronous, which allows the loader to maximize CPU resources. When CPU_PARALLELISM is set to one, the loader waits on I/O during consistency points. A load operation with CPU_PARALLELISM set to two, and SAVECOUNT set to 10 000, completes faster than the same operation with CPU_PARALLELISM set to one, even with only one CPU.

DISK_PARALLELISM n

Specifies the number of processes or threads that the load utility creates for writing data to the table space containers. If a value is not specified, the utility selects an intelligent default based on the number of table space containers and the characteristics of the table.

INDEXING MODE

Specifies whether the load utility rebuilds indexes or extends them incrementally. The following values are valid:

AUTOSELECT

The load utility automatically decides between REBUILD or INCREMENTAL mode. The decision is based on the amount of data that is loaded and the depth of the index tree. Information relating to the depth of the index tree is stored in the index object. RUNSTATS is not required to populate this information. AUTOSELECT is the default indexing mode.

REBUILD

All indexes are rebuilt. The utility must have sufficient resources to sort all index key parts for both old and appended table data.

If the LogIndexBuild database configuration parameter is turned on, the transaction log contains the image of each index page after it is created. If the LogIndexBuild database configuration parameter is turned off, only the allocation and initialization of each page is logged by the Index Manager. The amount of logging is approximately 250 bytes per page, as opposed to the non-empty portion of each page.

INCREMENTAL

Indexes are extended with new data. This approach uses index free space. It requires only enough sort space to append index keys for the inserted records. This method is only supported in cases where the index object is valid and accessible at the start of a load operation. Conversely, it is not valid immediately following a load operation in which the DEFERRED mode was specified. If this mode is specified, but not supported due to the state of the index, a warning is returned, and the load operation continues in REBUILD mode. Similarly, if a load restart operation is begun in the load build phase, INCREMENTAL mode is not supported.

If the LogIndexBuild database configuration parameter is turned on, Db2 generates the log records for the insertion of every key into the index, and any pages that are split. The LogIndexBuild database configuration parameter is commonly turned off when HADR is not used. If this database configuration parameter is turned off, the amount of indexing that is logged by the Index Manager depends on whether the ALLOW READ ACCESS option was specified. If the ALLOW READ ACCESS option is specified, the log record is generated including logs for page splits. If the ALLOW READ ACCESS option is not specified, no log record from the Index Manager is generated.

DEFERRED

The load utility does not attempt index creation if this mode is specified. Indexes are marked as needing a refresh. The first access to such indexes that is unrelated to a load operation might force a rebuild, or indexes might be rebuilt when the database is restarted. This approach requires enough sort space for all key parts for the largest index. When you take this approach, the total time that is needed for index construction is longer than the time required in REBUILD mode. Therefore, when you run multiple load operations with deferred indexing, it is advisable (from a performance viewpoint) to allow the last load operation in the sequence run an index rebuild. Otherwise, the indexes need to be rebuilt at first non-load access.

Deferred indexing is only supported for tables with non-unique indexes so that duplicate keys that are inserted during the load phase are not persistent after the load operation.

ALLOW NO ACCESS

The load operation locks the target table for exclusive access during the load. The table state is set to Load In Progress during the load. ALLOW NO ACCESS is the default behavior. It is the only valid option for LOAD REPLACE.

When the table has constraints, the table state is set to Set Integrity Pending and Load In Progress. The SET INTEGRITY statement must be used to take the table out of Set Integrity Pending state.

ALLOW READ ACCESS

The load operation locks the target table in a share mode. The table state is set to both Load In Progress and Read Access. Readers can access the non-delta portion of the data while the table is being load. In other words, data that existed before the start of the load is accessible by readers to the table. Data that is being loaded is not available until the load is complete.

The ALLOW READ ACCESS parameter is not supported for column-organized tables.

LOAD TERMINATE or LOAD RESTART of an ALLOW READ ACCESS load can use this parameter. LOAD TERMINATE or LOAD RESTART of an ALLOW NO ACCESS load cannot use this parameter. Furthermore, this option is not valid if the indexes on the target table are marked as requiring a rebuild.

When the table has constraints, the table state is set to Set Integrity Pending, Load In Progress, and Read Access. At the end of the load, the table state Load In Progress is removed but the table states Set Integrity Pending and Read Access remain. The SET INTEGRITY statement must be used to take the table out of Set Integrity Pending. While the table is in Set Integrity Pending and Read Access states, the non-delta portion of the data is still accessible to readers. The new (delta) portion of the data remains inaccessible until the SET INTEGRITY statement completes. You can run multiple loads on the same table without issuing a SET INTEGRITY statement. However, only the original (checked) data remains visible until the SET INTEGRITY statement is issued.

ALLOW READ ACCESS also supports the following modifiers:

USE tablespace-name

If the indexes are being rebuilt, a shadow copy of the index is built in table space tablespace-name. The indexes are copied over to the original table space at the end of the load during an INDEX COPY PHASE. Only system temporary table spaces can be used with this option. If not specified, then the shadow index is created in the same table space as the index object. If the shadow copy is created in the same table space as the index object, the copy of the shadow index object over the old index object is instantaneous. If the shadow copy is in a different table space from the index object, a physical copy is created. This copy operation might involve considerable I/O and time. The copy happens while the table is offline at the end of a load during the INDEX COPY PHASE.

Without this option, the shadow index is built in the same table space as the original. By default, the original index and shadow index exist in the same table space simultaneously. Therefore, the one table space might have insufficient space to hold both indexes. Use option to ensure that you retain enough table space for the indexes.

This option is ignored if the user does not specify INDEXING MODE REBUILD or INDEXING MODE AUTOSELECT. This option is also ignored if INDEXING MODE AUTOSELECT is chosen and load chooses to incrementally update the index.

FETCH_PARALLELISM YES | NO

When this option is set to YES, the load utility attempts to parallelize fetching from the remote data source in either of the following scenarios:

• When you run a load from a cursor where the cursor is declared by using the DATABASE keyword.
• When you use the API sqlu_remotefetch_entry media entry.

You can parallelize fetching of data only if the cursor's select-statement is of the simple form "SELECT * FROM <tablename>". If set to NO, no parallel fetching is done. The default value is YES. For more information, see Moving data using the CURSOR file type.

SET INTEGRITY PENDING CASCADE

If LOAD puts the table into Set Integrity Pending state, use SET INTEGRITY PENDING CASCADE to specify whether the Set Integrity Pending state of the loaded table immediately cascades to all descendant tables. Descendant tables include descendant foreign key tables, descendant immediate materialized query tables, and descendant immediate staging tables.

IMMEDIATE

Indicates that Set Integrity Pending state is immediately extended to all descendant foreign key tables, descendant immediate materialized, query tables, and descendant staging tables. For a LOAD INSERT operation, Set Integrity Pending state is not extended to descendant foreign key tables even if the IMMEDIATE option is specified.

When the loaded table is later checked for constraint violations, descendant foreign key tables that were placed in Set Integrity Pending Read Access state are put into Set Integrity Pending No Access state. The loaded table is checked for constraint violations by using the IMMEDIATE CHECKED option of the SET INTEGRITY statement.

DEFERRED

Indicates that only the loaded table is placed in the Set Integrity Pending state. The states of the descendant foreign key tables, descendant immediate materialized query tables, and descendant immediate staging tables remain unchanged.

Descendant foreign key tables might later be implicitly placed in Set Integrity Pending state when their parent tables are checked for constraint violations. Constraint violations are checked by using the IMMEDIATE CHECKED option of the SET INTEGRITY statement. Descendant immediate materialized query tables and descendant immediate staging tables are implicitly placed in Set Integrity Pending state when one of its underlying tables is checked for integrity violations. A query of a table that is in the Set Integrity Pending state might succeed if an eligible materialized query table that is not in the Set Integrity Pending state is accessed by the query instead of the specified table. A warning (SQLSTATE 01586) is issued to indicate that descendant tables were placed in Set Integrity Pending state. See the Notes section of the SET INTEGRITY statement for when these descendant tables are put into Set Integrity Pending state.

If the SET INTEGRITY PENDING CASCADE option is not specified, only the loaded table is placed in Set Integrity Pending state. The state of descendant foreign key tables, descendant immediate materialized query tables, and descendant immediate staging tables remain unchanged. These tables can later be implicitly put into Set Integrity Pending state when the loaded table is checked for constraint violations.

If LOAD does not put the target table into Set Integrity Pending state, the SET INTEGRITY PENDING CASCADE option is ignored.

LOCK WITH FORCE

While loading data, the utility acquires various locks, including table locks. Rather than wait and possibly timeout when a lock is acquired, this option allows load to force off other applications that hold conflicting locks on the target table. Applications holding conflicting locks on the system catalog tables are not be forced off by the load utility. Forced applications roll back and release the locks the load utility needs. The load utility can then proceed. This option requires the same authority as the FORCE APPLICATIONS command (SYSADM, SYSCTRL, or SYSMAINT).

ALLOW NO ACCESS can force applications that hold conflicting locks at the start of the load operation. At the start of the load operation, the utility can force applications that are attempting to either query or modify the table.

ALLOW READ ACCESS can force applications that hold conflicting locks at the start or end of the load operation. At the start of the load operation, the load utility can force applications that are attempting to modify the table. At the end of the load operation, the load utility can force applications that are attempting to either query or modify the table.

SOURCEUSEREXIT executable

Specifies the name of an executable file that is called to feed data into the utility.

The SOURCEUSEREXIT parameter is not supported for column-organized tables.

You cannot use SOURCEUSERXIT if DB2_LOAD_RESTRICTED_IO_ALLOW_SOURCEUSEREXIT is set to NO and DB2_LOAD_RESTRICTED_IO_PATH is enabled.

REDIRECT

INPUT FROM

BUFFER input-buffer

The stream of bytes specified in input-buffer is passed into the STDIN file descriptor of the process that is running the executable file.

FILE input-file

The contents of this client-side file are passed into the STDIN file descriptor of the process that is running the executable file.

OUTPUT TO

FILE output-file

The STDOUT and STDERR file descriptors are captured to the fully qualified server-side file specified.

PARALLELIZE

Increases the throughput of data that enters the load utility by using multiple user exit processes simultaneously. This option is applicable only in multi-partition database environments and is ignored in single-partition database environments.

For more information, see Moving data using a customized application (user exit).

PARTITIONED DB CONFIG partitioned-db-option

Use this option to load into a table that is distributed across multiple database partitions. You can specify configuration options that are exclusive to partitioned databases. The partitioned-db-option values can be any of the following options:

PART_FILE_LOCATION x
OUTPUT_DBPARTNUMS x
PARTITIONING_DBPARTNUMS x
MODE x
MAX_NUM_PART_AGENTS x
ISOLATE_PART_ERRS x
STATUS_INTERVAL x
PORT_RANGE x
CHECK_TRUNCATION
MAP_FILE_INPUT x
MAP_FILE_OUTPUT x
TRACE x
NEWLINE
DISTFILE x
OMIT_HEADER
RUN_STAT_DBPARTNUM x

Detailed descriptions of these options are provided in Load configuration options for partitioned database environments.

RESTARTCOUNT

Deprecated.

USING directory

Deprecated.

Usage notes

• Data is loaded in the sequence that appears in the input file. If a particular sequence is preferred, sort the data before a load is attempted. If preservation of the source data order is not required, you can use the ANYORDER file type modifier, described in the following File type modifiers for the load utility section.
• The load utility builds indexes based on existing definitions. The exception tables are used to handle duplicates on unique keys. The utility does not enforce referential integrity, check for constraints, or update materialized query tables that depend on the tables that are part of the load operation. Tables that include referential or check constraints are placed in Set Integrity Pending state. Summary tables that are defined with REFRESH IMMEDIATE, and that depend on tables that are part of the load operation, are also placed in Set Integrity Pending state. Issue the SET INTEGRITY statement to take the tables out of Set Integrity Pending state. Load operations cannot be carried out on replicated materialized query tables.
• If a clustering index exists on the table, sort the data on the clustering index before loading. However, you do not need to sort data before you load it into a multidimensional clustering (MDC) table.
• If you specify an exception table when you load data into a protected table, any rows that are protected by invalid security labels are sent to that table. This operation might allow users that have access to the exception table to access data that they are otherwise unauthorized to access. Follow these guidelines for better security:
  • Be careful who you grant exception table access to.
  • Delete each row as soon as it is repaired and copied to the table that you are loading.
  • Drop the exception table as soon as you are done with it.
• Security labels in their internal format might contain newline characters. If you load the file by using the DEL file format, those newline characters can be mistaken for delimiters. If you have this problem, use the older default priority for delimiters by specifying the delprioritychar file type modifier in the LOAD command.
• The LOAD utility issues a COMMIT statement at the beginning of the operation. For Type 2 connections, this COMMIT causes the procedure to return SQL30090N with reason code 1.
• Any path that is used in the LOAD command must be a valid fully qualified path on the server coordinator partition.
• To run LOAD with the CURSOR file type where the DATABASE keyword was specified during the DECLARE CURSOR statement, the same credentials are used for the following authentication:
  • The user ID and password that were used to authenticate against the currently connected database for the load operation.
  • The user ID and password that were used to authenticate against the source database, which was specified in the DATABASE option of the DECLARE CURSOR statement.

  If no user ID or password was specified for the connection to the loading database, a user ID and password for the source database must be specified during the DECLARE CURSOR statement.

• Loading a multiple-part PC/IXF file whose individual parts are copied from a Windows system to an AIX® system is supported. The names of all the files must be specified in the LOAD command. For example, LOAD FROM DATA.IXF, DATA.002 OF IXF INSERT INTO TABLE1. Loading to the Windows operating system from logically split PC/IXF files is not supported.
• When a failed LOAD is restarted, the behavior follows the existing behavior in that the BUILD phase is forced to use the REBUILD mode for indexes.
• The load utility might generate a large copy of the image file when the COPY YES option is used. This behavior is expected when the LOAD command writes out an entire buffer of data to the copy image for every LOB/LF column value that is loaded. The buffer is an internal object, and its size is determined by several internal and external factors. Typically, the buffer size is between 68 KB and a few hundred KB.
• Loading XML documents between databases is not supported and returns error message SQL1407N.
• The LOAD utility does not support loading into tables that contain columns that reference fenced procedures. If you issue the LOAD command on such a table, an error message is returned (SQL1376N). To work around this restriction, you can redefine the routine to be unfenced, or use the import utility.
• If a table contains a generated column expression in which the user-defined function is a compiled compound SQL, you can use the LOAD utility only with the generatedoverride file type modifier to insert data into the table. You can also use the import utility to insert data into these tables.
• If the database table contains implicitly hidden columns, you must specify whether data for the hidden columns is included in the load operation.
• The IMPORT utility does not match the number of columns in a table and the number of fields in a data file. The utility checks for sufficient data in the data file. If a row in the data file does not contain sufficient columns of data, either of the following actions are taken:
  • The row is rejected with a warning message if the corresponding table columns without data are defined as NOT NULL.
  • The row is inserted successfully without a warning message if the corresponding table columns are defined as NULL.

  Conversely, if a row contains a higher number of columns than required, the sufficient number of columns are processed while the remaining columns of data are omitted. When this scenario occurs, no warning message is given.

• The STATISTICS options work only for the LOAD REPLACE option and do not work for other LOAD command options.
• When the LOAD utility is used with the COPY YES option, and the table contains LOB columns, LOAD always enforces COMPACT behavior even when the LOB column is defined with NOT COMPACT.

Summary of LOAD TERMINATE and LOAD RESTART dictionary management

The following chart summarizes the compression dictionary management behavior for LOAD processing under the TERMINATE directive.

LOAD RESTART truncates a table up to the last consistency point reached. As part of LOAD RESTART processing, a compression dictionary exists in the table if it was present in the table at the time the last LOAD consistency point was taken. In that case, LOAD RESTART does not create a new dictionary. For a summary of the possible conditions, see Table 4.

Result set information

SELECT SQLCODE, MSG
   FROM TABLE 
   (SYSPROC.ADMIN_GET_MSGS
   ('2203498_thx')) AS MSG

CALL SYSPROC.ADMIN_REMOVE_MSGS
   ('2203498_thx')

File type modifiers for the load utility

SET INTEGRITY FOR table-name GENERATED COLUMN
  IMMEDIATE UNCHECKED

SET INTEGRITY FOR table-name IMMEDIATE CHECKED.

modified by maxanalyzesize=1G
modified by maxanalyzesize=100M

"CONFIDENTIAL:ALPHA:G2"
"CONFIDENTIAL;SIGMA:G2"
"TOP SECRET:ALPHA:G2"

LOAD FROM input.del OF DEL MODIFIED BY SECLABELCHAR INSERT INTO t1

"LABEL1"
"LABEL1"
"LABEL2"

   LOAD FROM input.del OF DEL MODIFIED BY SECLABELNAME INSERT INTO t1

YYYY - Year (four digits ranging from 0000 - 9999)
M    - Month (one or two digits ranging from 1 - 12)
MM   - Month (two digits ranging from 01 - 12;
         mutually exclusive with M)
D    - Day (one or two digits ranging from 1 - 31)
DD   - Day (two digits ranging from 01 - 31;
         mutually exclusive with D)
DDD  - Day of the year (three digits ranging
         from 001 - 366; mutually exclusive
         with other day or month elements)

"D-M-YYYY"
"MM.DD.YYYY"
"YYYYDDD"

db2 load from data of del
   modified by dumpfile = /u/user/filename
   insert into table_name

H     - Hour (one or two digits ranging from 0 - 12
           for a 12 hour system, and 0 - 24
           for a 24 hour system)
HH    - Hour (two digits ranging from 00 - 12
           for a 12 hour system, and 00 - 24
           for a 24 hour system; mutually exclusive
             with H)
M     - Minute (one or two digits ranging
           from 0 - 59)
MM    - Minute (two digits ranging from 00 - 59;
           mutually exclusive with M)
S     - Second (one or two digits ranging
           from 0 - 59)
SS    - Second (two digits ranging from 00 - 59;
           mutually exclusive with S)
SSSSS - Second of the day after midnight (5 digits
           ranging from 00000 - 86400; mutually
           exclusive with other time elements)
TT    - Meridian indicator (AM or PM)

"HH:MM:SS"
"HH.MM TT"
"SSSSS"

YYYY   - Year (four digits ranging from 0000 - 9999)
M      - Month (one or two digits ranging from 1 - 12)
MM     - Month (two digits ranging from 01 - 12;
            mutually exclusive with M and MMM)
MMM    - Month (three-letter case-insensitive abbreviation for 
            the month name; mutually exclusive with M and MM)              
D      - Day (one or two digits ranging from 1 - 31)
DD     - Day (two digits ranging from 01 - 31; mutually exclusive with D)
DDD    - Day of the year (three digits ranging from 001 - 366; 
            mutually exclusive with other day or month elements)
H      - Hour (one or two digits ranging from 0 - 12
            for a 12 hour system, and 0 - 24 for a 24 hour system)
HH     - Hour (two digits ranging from 00 - 12 
            for a 12 hour system, and 00 - 24 for a 24 hour system; 
            mutually exclusive with H)
M      - Minute (one or two digits ranging from 0 - 59)
MM     - Minute (two digits ranging from 00 - 59;
            mutually exclusive with M, minute)
S      - Second (one or two digits ranging from 0 - 59)
SS     - Second (two digits ranging from 00 - 59;
            mutually exclusive with S)
SSSSS  - Second of the day after midnight (5 digits
            ranging from 00000 - 86400; mutually
            exclusive with other time elements)
U (1 to 12 times)
         - Fractional seconds(number of occurrences of U represent the 
              number of digits with each digit ranging from 0 to 9

TT     - Meridian indicator (AM or PM)

   "YYYY/MM/DD HH:MM:SS.UUUUUU"

YYYY-MM-DD-HH.MM.SS 
YYYY-MM-DD HH:MM:SS

db2 load from delfile2 of del
    modified by timestampformat="yyyy.mm.dd hh:mm tt"
    insert into schedule

+ = 0xC 0xA 0xE 0xF
- = 0xD 0xB

+ = 0xC 0xA 0xE 0xF 0x3
- = 0xD 0xB 0x7

modified by chardel""

modified by chardel''

db2 load ... modified by delprioritychar ...

"Smith, Joshua",4000,34.98<row delimiter>
"Vincent,<row delimiter>, is a manager", ...
... 4005,44.37<row delimiter>

db2 load from delfile3 of del
   modified by keepblanks
   insert into table1

Notes

1. Double quotation marks around the date format string are mandatory. Field separators cannot contain any of the following characters: a-z, A-Z, and 0-9. The field separator must not be the same as the character delimiter or field delimiter in the DEL file format. A field separator is optional if the start and end positions of an element are unambiguous. Ambiguity can exist if (depending on the modifier) elements such as D, H, M, or S are used, because of the variable length of the entries.
   For timestamp formats, care must be taken to avoid ambiguity between the month and the minute descriptors, since they both use the letter M. A month field must be next to other date fields. A minute field must be next to other time fields. Following are some ambiguous timestamp formats:

   "M" (could be a month, or a minute)
   "M:M" (Which is which?)
   "M:YYYY:M" (Both are interpreted as month.)
   "S:M:YYYY" (adjacent to both a time value and a date value)

   In ambiguous cases, the utility reports an error message, and the operation fails.
   Following are some unambiguous timestamp formats:

   "M:YYYY" (Month)
   "S:M" (Minute)
   "M:YYYY:S:M" (Month....Minute)
   "M:H:YYYY:M:D" (Minute....Month)

   Some characters, such as double quotation marks and backslashes, must be preceded by an escape character (for example, \).

2. Character values provided for the chardel, coldel, or decpt file type modifiers must be specified in the code page of the source data.
   The character code point (instead of the character symbol), can be specified by using the syntax xJJ or 0xJJ, where JJ is the hexadecimal representation of the code point. For example, to specify the # character as a column delimiter, use one of the following statements:

   ... modified by coldel# ...
   ... modified by coldel0x23 ...
   ... modified by coldelX23 ...

3. Delimiter considerations for moving data lists restrictions that apply to the characters that can be used as delimiter overrides.
4. The load utility does not issue a warning if an attempt is made to use unsupported file types with the MODIFIED BY option. If you attempt to use an unsupported file type with the MODIFIED BY option, the load operation fails, and an error code is returned.
5. When multiple modifiers suffixed with ignore, include, missing, and override are specified, they are applied in the order that they are listed. In the following statement, data for implicitly hidden columns that are not identity columns is included in the input data. While data for all identity columns, regardless of their implicitly hidden status, is not.

   db2 load from delfile1 of del modified by 
      implicitlyhiddeninclude identitymissing insert into table1

   However, changing the order of the file type modifiers in the following statement means that data for all implicitly hidden columns (including hidden identity columns) is included in the input data. While data for identity columns that are not implicitly hidden is not.

   db2 load from delfile1 of del modified by 
      identitymissing implicitlyhiddeninclude insert into table1