LOAD QUERY command

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

>>-LOAD QUERY--TABLE--table-name--+------------------------+---->
                                  '-TO--local-message-file-'   

>--+-------------+--+-----------+------------------------------><
   +-NOSUMMARY---+  '-SHOWDELTA-'   
   '-SUMMARYONLY-'                  

Command parameters

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.

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.

SUMMARYONLY

Specifies that only load summary information is to be reported.

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.

Usage notes

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) prior to 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.

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; 

connect to sample; 
create table newstaff like staff; 
load from staffdata.del of del insert into newstaff; 

connect to sample; 
load query table newstaff; 
connect reset; 

connect to sample; 
create table newstaff like staff; 
load query table newstaff;

update db cfg for sample using logretain recovery; 
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; 

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; 

connect to sample; 
load from staffdata.del of del terminate into newstaff copy yes to /home/melnyk/backups; 
load query table newstaff; 
connect reset; 

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; 

connect to sample; 
load query table newstaff; 
select * from newstaff; 
connect reset;

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; 

update db cfg for sample using logretain recovery; 
backup db sample; 

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; 

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.