I am having a hard time finding information regarding the Load command and the critieria that it uses to know that it has to do a delete.

I know that the load did do a delete, but I dont know why.

Number of rows read = 292518
Number of rows skipped = 0
Number of rows loaded = 292518
Number of rows rejected = 0
Number of rows deleted = 77264
Number of rows committed = 292518

Can anyone point me to the correct documentation so that I can get a better understanding of what is going on?

Regards

I found this online

"INSERT and DELETE privilege on the table when the load utility is invoked in REPLACE mode"

this is my code
From what I can see online it looks like i have a primary key violation on the table that is causing the deletes. Am I thinking correctly?

Not necessarily a primary key - any unique constraint violation will cause LOAD to delete duplicate rows after the build phase.

...Just like an Index...which I have just deleted and am reloading now. I will let you know the results.

You used the REPLACE option. This means that existing rows are deleted.

Other constraint violations also cause rows to be deleted. For example, if you have a column with "CHECK ( col IN ('A', 'B', 'C') )", then DB2 must delete the row if the value in such a column is 'D'. Otherwise, you would have inconsistencies in the database.

I would think that in the case of violated check or referential constraints, no deletes would happen during the load; instead, the table would be put into the check pending state upon the load completion, and it would be the administrator's task to deal with that.

Knut, IIRC, during the delete phase of a load command, only unique key violations are deleted.

After load, SET INTEGRITY has to be run on a table that has check constraints and referential constraints. By specifying an exception table, the constraint violating rows are deleted from the main table and placed in the exception table. If no exception table is specified, the table continues in the check pending status.

Please correct me if I am wrong.

Thanks


Sathyaram

You're right and I'm wrong (again). SET INTEGRITY has to be used to bring a table out of check pending state by verifying RI/check constrainsts. Unique constraints are handled during index rebuild in load already.