MVSFORUMS.com

umj · Beginner Joined: 25 Nov 2008 Posts: 8 Topics: 4

In my application, a cascade delete is done. There is a commit after each delete. However, this delete causes over 10,000,000 records to be deleted from the child tables. The DBA team is having an issue with this job.
I have thought of the following options:
(1)
changing the program logic to delete from child tables first and then going up the hierarchy. At the same time deleting N number of records by using a cursor.
(2)
Unload the tables into a file and then delete fromt he files and then load them again into the tables.

Can anyone suggest another option

Thanks

kolusu · Posted: Tue Nov 25, 2008 2:19 pm Post subject:

umj,

Ideally I would do this

1. Declare cursor on the Parent table
2. Fetch the cursor
3. Now issue delete statements on each of the child tables with the key from parent table
4. Once step3 is complete, issue the delete on parent table

Unloading and re-loading the tables is a mute exercise

Kolusu

umj · Beginner Joined: 25 Nov 2008 Posts: 8 Topics: 4

hi Kolusu

This is whaat is currently done.

but since the child tables hold huge data for each entry in parent table it is becoming a problem.

Is there a way by which we can specify the # of records to delete from the child table (in a delete stmt)
Commit and then reissue the delete stmt until no correspondng reocrds are present in the child table.

Thanks
umj

kolusu · Posted: Tue Nov 25, 2008 6:02 pm Post subject:

umj,

hmm. May be a crude approach but might work faster

1. Declare cursor on the Parent table
2. Fetch the parent table cursor
3. Now open a cursor on each individual child tables with parent table key
4. Issue a delete statement with where current of
5. repeat step 3 and 4 for all the child tables
6. now delete the parent table key

Also you can minimize the steps 3 and 4 using the count of records on each child table for the parent table key.

get the count from child table for the parent table key

jsharon1248 · Posted: Wed Nov 26, 2008 8:58 am Post subject:

Deleting 10 million rows, huh? That's a big matzah ball.

Anyway. I've used the following in UDB for AIX, so I'm not sure the z/OS product will honor the syntax:

jsharon1248 · Posted: Wed Nov 26, 2008 3:11 pm Post subject:

Forget about the first option I mentioned. The z/OS product doesn't support that syntax. The REORG is probably a better solution anyway.

umj · Beginner Joined: 25 Nov 2008 Posts: 8 Topics: 4

thansk jsharon1248

I think I'll be working on the Reorg with Discard option.

Thanks once again