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.
Required connection
Database
Command syntax
>>-LOAD QUERY--TABLE--table-name--+------------------------+---->
'-TO--local-message-file-'
>--+-------------+--+-----------+------------------------------><
+-NOSUMMARY---+ '-SHOWDELTA-'
'-SUMMARYONLY-'
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 statesState |
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: 20040629182012connect 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.