LOAD QUERY command

The LOAD QUERY command checks the status of a load operation during processing and returns the table state. If a load is not processing, then the table state alone is returned.

A connection to the same database, and a separate CLP session are also required to successfully invoke this command. It can be used either by local or remote users.

Authorization

None

Required connection

Database

Command syntax

Command parameters

TABLE table-name: Specifies the name of the table into which data is currently being loaded. If an unqualified table name is specified, the table will be qualified with the CURRENT SCHEMA.
Note: In partitioned database environments, results are returned from the current partition only. No results are returned on partitions where the table is not defined.
TO local-message-file: Specifies the destination for warning and error messages that occur during the load operation. This file cannot be the message-file specified for the LOAD command. If the file already exists, all messages that the load utility has generated are appended to it.
NOSUMMARY: Specifies that no load summary information (rows read, rows skipped, rows loaded, rows rejected, rows deleted, rows committed, and number of warnings) is to be reported.
SUMMARYONLY: Specifies that only load summary information is to be reported.
SHOWDELTA: Specifies that only new information (pertaining to load events that have occurred since the last invocation of the LOAD QUERY command) is to be reported.

Examples

A user loading a large amount of data into the STAFF table in the BILLYBOB database, wants to check the status of the load operation. The user can specify:

db2 connect to billybob
db2 load query table staff to /u/mydir/staff.tempmsg

The output file /u/mydir/staff.tempmsg might look like the following output:

SQL3501W  The table space(s) in which the table resides will not be placed in 
backup pending state since forward recovery is disabled for the database.

SQL3109N  The utility is beginning to load data from file 
"/u/mydir/data/staffbig.del"

SQL3500W  The utility is beginning the "LOAD" phase at time "03-21-2002 
11:31:16.597045".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

SQL3519W  Begin Load Consistency Point. Input record count = "104416".

SQL3520W  Load Consistency Point was successful.

SQL3519W  Begin Load Consistency Point. Input record count = "205757".

SQL3520W  Load Consistency Point was successful.

SQL3519W  Begin Load Consistency Point. Input record count = "307098".

SQL3520W  Load Consistency Point was successful.

SQL3519W  Begin Load Consistency Point. Input record count = "408439".

SQL3520W  Load Consistency Point was successful.

SQL3532I  The Load utility is currently in the "LOAD" phase.


Number of rows read         = 453376
Number of rows skipped      = 0
Number of rows loaded       = 453376
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 408439
Number of warnings          = 0

Tablestate:
  Load in Progress

Usage notes

In addition to locks, the load utility uses table states to control access to the table. The LOAD QUERY command can be used to determine the table state; LOAD QUERY can be used on tables that are not currently being loaded. For a partitioned table, the state reported is the most restrictive of the corresponding visible data partition states. For example, if a single data partition is in the Read Access Only state and all other data partitions are in Normal state, the load query operation returns the Read Access Only state. A load operation will not leave a subset of data partitions in a state different from the rest of the table. The table states described by LOAD QUERY are as follows:

Normal: A table is in Normal state if it is not in any of the other (abnormal) table states. Normal state is the initial state of a table after it is created.
Set Integrity Pending: The table has constraints which have not yet been verified. Use the SET INTEGRITY statement to take the table out of Set Integrity Pending state. The load utility places a table in Set Integrity Pending state when it begins a load operation on a table with constraints.
Load in Progress: This is a transient state that is only in effect during a load operation. For information about bringing a table out of Load in Progress state if your load operation has failed or was interrupted, see the section on pending states after a load operation in the Related links section. See also Load in Progress table space state.
Load Pending: A load operation has been active on this table but has been aborted before the data could be committed. Issue a LOAD TERMINATE, LOAD RESTART, or LOAD REPLACE command to bring the table out of this state.
Read Access Only: A table is in this state during a load operation if the ALLOW READ ACCESS option was specified. Read Access Only is a transient state that allows other applications and utilities to have read access to data that existed before the load operation.
Reorg Pending: A REORG command recommended ALTER TABLE statement has been executed on the table. A classic REORG must be performed before the table is accessible again.
Unavailable: The table is unavailable. The table can only be dropped or restored from a backup. Rolling forward through a non-recoverable load operation will place a table in the unavailable state.
Not Load Restartable: The table is in a partially loaded state that will not allow a load restart operation. The table will also be in load pending state. Issue a LOAD TERMINATE or a LOAD REPLACE command to bring the table out of the not load restartable state. A table is placed in not load restartable state when a rollforward operation is performed after a failed load operation that has not been successfully restarted or terminated, or when a restore operation is performed from an online backup that was taken while the table was in load in progress or load pending state. In either case, the information required for a load restart operation is unreliable, and the not load restartable state prevents a load restart operation from taking place.
Unknown: The LOAD QUERY command is unable to determine the table state.

There are currently at least 25 table or table space states supported by the IBM® Db2® database product. These states are used to control access to data under certain circumstances, or to elicit specific user actions, when required, to protect the integrity of the database. Most of them result from events related to the operation of one of the Db2 database utilities, such as the load utility, or the backup and restore utilities.

Although dependent table spaces are no longer quiesced (a quiesce is a persistent lock) before a load operation, the Load in Progress table space state prevents the backup of dependent tables during a load operation. The Load in Progress table space state is different from the Load in Progress table state: All load operations use the Load in Progress table state, but load operations (against a recoverable database) with the COPY NO option specified also use the Load in Progress table space state.

The following table describes each of the supported table states. The table also provides you with working examples that show you exactly how to interpret and respond to states that you might encounter while administering your database. The examples are taken from command scripts that were run on AIX®; you can copy, paste and run them yourself. If you are running the Db2 database product on a system that is not UNIX, ensure that any path names are in the correct format for your system. Most of the examples are based on tables in the SAMPLE database that comes with the Db2 database product. A few examples require scenarios that are not part of the SAMPLE database, but you can use a connection to the SAMPLE database as a starting point.

Table 1. Supported table states
State	Examples
Load Pending	Given load input file staffdata.del with a substantial amount of data (for example, 20000 or more records), create a small table space that contains the target table of the load operation, a new table called NEWSTAFF: `connect to sample; create tablespace ts1 managed by database using (file '/home/melnyk/melnyk/NODE0000 /SQL00001/ts1c1' 256); create table newstaff like staff in ts1; load from staffdata.del of del insert into newstaff; load query table newstaff; load from staffdata.del of del terminate into newstaff; load query table newstaff; connect reset;` Information returned by the LOAD QUERY command shows that the NEWSTAFF table is in Load Pending state; after a load terminate operation, the table is in Normal state.
Load in Progress	Given load input file staffdata.del with a substantial amount of data (for example, 20000 or more records): `connect to sample; create table newstaff like staff; load from staffdata.del of del insert into newstaff;` While the load operation is running, execute the following script from another session: `connect to sample; load query table newstaff; connect reset;` Information returned by the LOAD QUERY command shows that the NEWSTAFF table is in Load in Progress state.
Normal	`connect to sample; create table newstaff like staff; load query table newstaff;` Information returned by the LOAD QUERY command shows that the NEWSTAFF table is in Normal state.
Not Load Restartable	Given load input file staffdata.del with a substantial amount of data (for example, 20000 or more records): `update db cfg for sample using logarchmeth1 logretain; backup db sample; connect to sample; create tablespace ts1 managed by database using (file '/home/melnyk/melnyk/NODE0000 /SQL00001/ts1c1' 256); create table newstaff like staff in ts1; connect reset; backup db sample;` The timestamp for this backup image is: 20040629205935 `connect to sample; load from staffdata.del of del insert into newstaff copy yes to /home/melnyk/backups; connect reset; restore db sample taken at 20040629205935; rollforward db sample to end of logs and stop; connect to sample; load query table newstaff; connect reset;` Information returned by the LOAD QUERY command shows that the NEWSTAFF table is in Not Load Restartable and Load Pending state. `connect to sample; load from staffdata.del of del terminate into newstaff copy yes to /home/melnyk/backups; load query table newstaff; connect reset;` Information returned by the LOAD QUERY command shows that the NEWSTAFF table is now in Normal state.
Read Access Only	Given load input file staffdata.del with a substantial amount of data (for example, 20000 or more records): `connect to sample; export to st_data.del of del select * from staff; create table newstaff like staff; import from st_data.del of del insert into newstaff; load from staffdata.del of del insert into newstaff allow read access;` While the load operation is running, execute the following script from another session: `connect to sample; load query table newstaff; select * from newstaff; connect reset;` Information returned by the LOAD QUERY command shows that the NEWSTAFF table is in Read Access Only and Load in Progress state. The query returns only the exported contents of the STAFF table, data that existed in the NEWSTAFF table before the load operation.
Set Integrity Pending	Given load input file staff_data.del with content: 11,"Melnyk",20,"Sales",10,70000,15000: `connect to sample; alter table staff add constraint max_salary check (100000 - salary > 0); load from staff_data.del of del insert into staff; load query table staff;` Information returned by the LOAD QUERY command shows that the STAFF table is in Set Integrity Pending state.
Unavailable	Given load input file staff_data.del with content: 11,"Melnyk",20,"Sales",10,70000,15000: `update db cfg for sample using logarchmeth1 logretain; backup db sample;` The timestamp for this backup image is: 20040629182012 `connect to sample; load from staff_data.del of del insert into staff nonrecoverable; connect reset; restore db sample taken at 20040629182012; rollforward db sample to end of logs and stop; connect to sample; load query table staff; connect reset;` Information returned by the LOAD QUERY command shows that the STAFF table is in Unavailable state.

For additional information about table states, see the Related links section.

The progress of a load operation can also be monitored with the LIST UTILITIES command.