Or, put the delete statement within a cursor in a stored procedure or atomic sql code block
Set you commit limit so you do not run out of log space
30 min of coding and you are done
Good night all
monty6
-----Original Message-----
From: "Goethel Rob " <db2udbdba-ezmlmshield-x4910536.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: 6/26/2007 3:50 PM
Subject: RE: SQL delete question.
Good afternoon Ed! I think your delete statement will not work because
it only uses a single key value of EMPNO. I have a two part key batch#,
seq#. I'm trying to speed up a delete flow that reads a extract file
and then does a delete on four tables. If I put the "extract" into a
table, then at least DB2 can do the work once instead of 4 index scans
(one for each table times #keys in extract file).
We could group the deletes into bunches (say read 50 records) and then
do a delete from table where (a.key1=:extractkey1 and
a.key2=:extractkey1a) or (a.key1=:extractkey2 and
a.key2=:extractkey2a)..... (key1=:extractkey50 and key2=:extractkey50a)
Thoughts?
Thanks!
-----Original Message-----
From: Edwards Ed
[mailto:db2udbdba-ezmlmshield-x89871259.[Email address protected]
Sent: Tuesday, June 26, 2007 10:23 AM
To: LazyDBA Discussion
Subject: RE: SQL delete question.
Well, well, well!
It's Goethel Rob! Hehe. How have you been?
Try the following code.
DELETE FROM EMP2 E2
WHERE EMPNO =
(SELECT EMPNO
FROM EMP1 E1
WHERE E2.EMPNO = E1.EMPNO);
Please take a few minutes to provide feedback on the quality of service
you received from our staff. The Department of Education values your
feedback as a customer. Commissioner of Education Jeanine Blomberg is
committed to continuously assessing and improving the level and quality
of services provided to you.Simply use the link below. Thank you in
advance for completing the survey.
http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]
-----Original Message-----
From: Goethel Rob
[mailto:db2udbdba-ezmlmshield-x16040074.[Email address protected]
Sent: Tuesday, June 26, 2007 11:06 AM
To: LazyDBA Discussion
Subject: SQL delete question.
I know there's probably an easy answer to this one, but I can't think of
it today! Help!
How do you do a delete from a target table where the keys are in another
table.
Example:
[truncated by sender]
DB2 & UDB email list listserv db2-l LazyDBA home page