468,119 Members | 2,069 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,119 developers. It's quick & easy.

EXPORT and Delete

Friends:

Let's say I'd like perform a delete, but before I delete, I'd like to
export the target rows.

I could use an export whose SELECT clause references the OLD TABLE of
a DELETE, but the issue is I'm deleting the table in chunks, like so:

WHILE (V_NO_DATA = 0) DO
DELETE FROM
(
SELECT 1 FROM X FETCH FIRST 200000 ROWS ONLY
) AS X_D';--
COMMIT;--
END WHILE;--

Since EXPORT won't append to the target export file, I'd end up
creating as many exports (and attendant files) as iterations in my
WHILE loop, which is no good.

As I understand it, EXPORT is not under transactional control, so I'm
wondering how I can ensure that I don't delete more rows than I export
(I don't mind deleting fewer, and I suppose intermediate updates are
an issue, either).

To avoid phantom/DR/Non-RR anomalies, would it be enough to specify
the isolation level of the EXPORT's SELECT and the SELECT in the
DELETE as WITH RR?

Anybody tackled this issue before?

(UDB LUW 8.2.3 on AIX 5.x)

--Jeff

May 7 '07 #1
6 3742
On May 7, 3:05 pm, jefftyzzer <jefftyz...@sbcglobal.netwrote:
Friends:

Let's say I'd like perform a delete, but before I delete, I'd like to
export the target rows.

I could use an export whose SELECT clause references the OLD TABLE of
a DELETE, but the issue is I'm deleting the table in chunks, like so:

WHILE (V_NO_DATA = 0) DO
DELETE FROM
(
SELECT 1 FROM X FETCH FIRST 200000 ROWS ONLY
) AS X_D';--
COMMIT;--
END WHILE;--

Since EXPORT won't append to the target export file, I'd end up
creating as many exports (and attendant files) as iterations in my
WHILE loop, which is no good.

As I understand it, EXPORT is not under transactional control, so I'm
wondering how I can ensure that I don't delete more rows than I export
(I don't mind deleting fewer, and I suppose intermediate updates are
an issue, either).

To avoid phantom/DR/Non-RR anomalies, would it be enough to specify
the isolation level of the EXPORT's SELECT and the SELECT in the
DELETE as WITH RR?

Anybody tackled this issue before?

(UDB LUW 8.2.3 on AIX 5.x)

--Jeff
Serge's "SQL on Fire" Part II has a promising lead--using a CTE I can
INSERT my DELETEd rows into a temp table and then EXPORT its contents.
That'll solve my isolation issues, but at the expense of INSERTing
into a table (I'd likely use a DGTT though, so the insertion cost
should be minimal).

Any other ideas, though?

--Jeff

May 7 '07 #2
jefftyzzer wrote:
Serge's "SQL on Fire" Part II has a promising lead--using a CTE I can
INSERT my DELETEd rows into a temp table and then EXPORT its contents.
That'll solve my isolation issues, but at the expense of INSERTing
into a table (I'd likely use a DGTT though, so the insertion cost
should be minimal).
I have never tried this, but export this query:
SELECT * FROM OLD TABLE(DELETE FROM T ...)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 8 '07 #3
On May 7, 5:45 pm, Serge Rielau <srie...@ca.ibm.comwrote:
jefftyzzer wrote:
Serge's "SQL on Fire" Part II has a promising lead--using a CTE I can
INSERT my DELETEd rows into a temp table and then EXPORT its contents.
That'll solve my isolation issues, but at the expense of INSERTing
into a table (I'd likely use a DGTT though, so the insertion cost
should be minimal).

I have never tried this, but export this query:
SELECT * FROM OLD TABLE(DELETE FROM T ...)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Thanks for your reply, Serge. This issue is that the delete is in a
loop, as it deletes in chunks of 200K, so I'd end up having to do a
separate EXPORT for every iteration of the loop to capture all rows
deleted. I think the technique of yours that I refer to above will
work, though.

--Jeff

May 8 '07 #4
On May 8, 2:24 am, jefftyzzer <jefftyz...@sbcglobal.netwrote:
On May 7, 5:45 pm, Serge Rielau <srie...@ca.ibm.comwrote:
jefftyzzer wrote:
Serge's "SQL on Fire" Part II has a promising lead--using a CTE I can
INSERT my DELETEd rows into a temp table and then EXPORT its contents.
That'll solve my isolation issues, but at the expense of INSERTing
into a table (I'd likely use a DGTT though, so the insertion cost
should be minimal).
I have never tried this, but export this query:
SELECT * FROM OLD TABLE(DELETE FROM T ...)
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thanks for your reply, Serge. This issue is that the delete is in a
loop, as it deletes in chunks of 200K, so I'd end up having to do a
separate EXPORT for every iteration of the loop to capture all rows
deleted. I think the technique of yours that I refer to above will
work, though.

--Jeff


Can you explain why simply performing a single export first, followed
by multiple deletes is unsuitable?

If the table format is such that comma-delimited export output is
feasible, then you could script matching
export and delete commands (creating multiple exported files that you
could later concatenate).
May 8 '07 #5
mike wrote:
Can you explain why simply performing a single export first, followed
by multiple deletes is unsuitable?

If the table format is such that comma-delimited export output is
feasible, then you could script matching
export and delete commands (creating multiple exported files that you
could later concatenate).
Good idea. Simply pipe the pretty printed result set to file.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 8 '07 #6
On May 8, 1:57 am, mike <_lin...@yahoo.comwrote:
On May 8, 2:24 am, jefftyzzer <jefftyz...@sbcglobal.netwrote:


On May 7, 5:45 pm, Serge Rielau <srie...@ca.ibm.comwrote:
jefftyzzer wrote:
Serge's "SQL on Fire" Part II has a promising lead--using a CTE I can
INSERT my DELETEd rows into a temp table and then EXPORT its contents.
That'll solve my isolation issues, but at the expense of INSERTing
into a table (I'd likely use a DGTT though, so the insertion cost
should be minimal).
I have never tried this, but export this query:
SELECT * FROM OLD TABLE(DELETE FROM T ...)
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Thanks for your reply, Serge. This issue is that the delete is in a
loop, as it deletes in chunks of 200K, so I'd end up having to do a
separate EXPORT for every iteration of the loop to capture all rows
deleted. I think the technique of yours that I refer to above will
work, though.
--Jeff

Can you explain why simply performing a single export first, followed
by multiple deletes is unsuitable?

If the table format is such that comma-delimited export output is
feasible, then you could script matching
export and delete commands (creating multiple exported files that you
could later concatenate).- Hide quoted text -

- Show quoted text -
If a user inserts a row after my single EXPORT that I later delete (in
the next step), then I've deleted something I've not archived.

As I mentioned earlier, I have an approach: create a DGTT, insert into
the DGTT the rows I delete (again, in multiple passes) using two
modifying table functions (one for the DELETE the other for the
INSERT), export the contents of the DGTT.

--Jeff

May 8 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Mike MacSween | last post: by
1 post views Thread by steve | last post: by
1 post views Thread by Kevin Blakeley | last post: by
2 posts views Thread by David Richards | last post: by
14 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.