By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,950 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

When will LOAD utility check consistaint

P: n/a
The following is a question of LOAD utility.
Question:
How does the DB2 enforce table check constraints for data added to
table with the LOAD utility?
A. With the BUILD phase of LOAD
B. With the SET INTEGRITY statement
C. With the DELETE phase of the LOAD
D. With the UPDATE CONSTRAINTS statement

Answer is A

I am confused why the correct answer is A. In BUILD phase, LOAD utility
only maintain index. I think LOAD utility check consistaint in LOAD
phase, not BUILD phase.

Is my thought right?

Thanks

James

Sep 18 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Actually, the answer is not A, it's B. Load just shoves data to the
target table. SET INTEGRITY validates it against any constraints.

See below. The table BLAH has a check constraint that col1 can't be
greater than 3. The input file to the load utility has values up to 6.
The load utility loads all six records, it's the SET INGERITY that
discovers the constraint violation, and refuses to give it a stamp of
approval as long as the offending rows are left in the table:

audi$ db2 "create table blah (col1 integer)"
DB20000I The SQL command completed successfully.
audi$ db2 create table blah2 like blah
DB20000I The SQL command completed successfully.
audi$ db2 "alter table blah add constraint chk_blah check (col1 <= 3)"
DB20000I The SQL command completed successfully.
audi$ cat loadme
1
2
3
4
5
6
audi$ db2 load from loadme of del insert into blah
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
"/home/blahblah/loadme".

SQL3500W The utility is beginning the "LOAD" phase at time "09/18/2006

11:22:55.673720".

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

SQL3520W Load Consistency Point was successful.

SQL3110N The utility has completed processing. "6" rows were read
from the
input file.

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

SQL3520W Load Consistency Point was successful.

SQL3515W The utility has finished the "LOAD" phase at time "09/18/2006

11:22:55.822691".
Number of rows read = 6
Number of rows skipped = 0
Number of rows loaded = 6
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 6

audi$ db2 set integrity for blah immediate checked
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL3603N Check data processing through the SET INTEGRITY statement has
found
integrity violation involving a constraint with name
"BLAHBLAH.BLAH.CHK_BLAH".
SQLSTATE=23514
audi$ db2 set integrity for blah immediate checked for exception in
blah use blah2
SQL3602W Check data processing found constraint violations and moved
them to
exception tables. SQLSTATE=01603
audi$ db2 "select * from blah"

COL1
-----------
1
2
3

3 record(s) selected.

audi$ db2 "select * from blah2"

COL1
-----------
4
5
6

3 record(s) selected.

/T

hu*****@gmail.com wrote:
The following is a question of LOAD utility.
Question:
How does the DB2 enforce table check constraints for data added to
table with the LOAD utility?
A. With the BUILD phase of LOAD
B. With the SET INTEGRITY statement
C. With the DELETE phase of the LOAD
D. With the UPDATE CONSTRAINTS statement

Answer is A

I am confused why the correct answer is A. In BUILD phase, LOAD utility
only maintain index. I think LOAD utility check consistaint in LOAD
phase, not BUILD phase.

Is my thought right?

Thanks

James
Sep 18 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.