EXPORT and Delete

[jefftyzzer]

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

[jefftyzzer]

On May 7, 3:05 pm, jefftyzzer <[e-mail]:

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

[Serge Rielau]

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

[jefftyzzer]

On May 7, 5:45 pm, Serge Rielau <[e-mail]:

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

[mike]

On May 8, 2:24 am, jefftyzzer <[e-mail]:

On May 7, 5:45 pm, Serge Rielau <[e-mail]:

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).

[Serge Rielau]

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

[jefftyzzer]

On May 8, 1:57 am, mike <[e-mail]:

On May 8, 2:24 am, jefftyzzer <[e-mail]:

On May 7, 5:45 pm, Serge Rielau <[e-mail]:

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