Regarding Delete Restrict

[razheev]

Hi,
I am doing a purge process and trying to delete rows .Let me know how
efficiently I can handle the purge process because of the RI on the
tables.
TABLE A is a PARENT of TABLE B, TABLE C, TABLE D. I want to purge
rows from all the tables TABLE A (Parent), TABLE B (Child), TABLE C
(Child) and TABLE D (Child).
All these tables have DELETE RESTRICT . I dont want to have the orphans
in the table.
I dont want to have the child row exist without a parent. I thought of
purging the data starting by identifying what rows need to be purged
from the PARENT and delete from the CHILD.
Is this a good approach or Is there a better way so that Performance
will not be problem too.
Let me know
Thanks

[razheev]

Actually it is like TABLE B is a CHILD of TABLE A, TABLE A1, TABLE A2.
(1 CHILD with Many PARENTS)
I need to purge rows from all the Parent and Child.

[Knut Stolze]

razheev wrote:

Hi,
I am doing a purge process and trying to delete rows .Let me know how
efficiently I can handle the purge process because of the RI on the
tables.
TABLE A is a PARENT of TABLE B, TABLE C, TABLE D. I want to purge
rows from all the tables TABLE A (Parent), TABLE B (Child), TABLE C
(Child) and TABLE D (Child).
All these tables have DELETE RESTRICT . I dont want to have the orphans
in the table.
I dont want to have the child row exist without a parent. I thought of
purging the data starting by identifying what rows need to be purged
from the PARENT and delete from the CHILD.
Is this a good approach or Is there a better way so that Performance
will not be problem too.

Do you want to delete all the rows from the tables you mentioned? If the
answer is yes, simply truncate all tables, possibly starting at D, which
has no further dependencies.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Phil Sherman]

Have you really thought this out? with A, A1, A2 as parents of a common
child, what happens to other children of the parents when you go to
delete a row from the parent? Foreign keys on B mean that each of the
A(n) tables is expected to have rows with values that don't match a row
in the B table.

If each of the A(n) tables can have at most one child, then you will
have to read to B table rows to get the A(n) table values to identify
rows to delete.

Your RESTRICT rule in the foreign key definitions of table B will
prevent deleting rows from the A(n) tables as long as there is an
existing B row. You'll have to delete the B table row first, then the
matching rows from the parents.

I'd try to use an updatable cursor to retrieve the B table rows, then
use a positioned delete to delete the B table row. Use the data values
from the retrieval to delete the other parent rows.

Phil Sherman
razheev wrote:

Actually it is like TABLE B is a CHILD of TABLE A, TABLE A1, TABLE A2.
(1 CHILD with Many PARENTS)
I need to purge rows from all the Parent and Child.