Oppolzer - Informatik / Blog

I'm not sure if it helps in this special situation, but when I had similar
situation, I had already good results, when changeing such queries to correlated
subqueries, that is

SELECT C1, C2, C3, ...
FROM T1
WHERE EXISTS
(SELECT 1
FROM T2
WHERE C1 = T1.C1
AND C2 = T1.C2
AND C3 = 0);

This way you force the join using the indexes and rule out merge scan join etc.
If you have good indexes on T2, it should work in acceptable time; fast
tablespace scan on T1 and fast direct access on T2. If you don't have indexes on
T2, you're stuck anyway, even with a program.

Kind regards

Bernd



Am 06.01.2012 10:32, schrieb C.G.:
>
> Hello DB2 Community;
>
> This is DB2 9 for z/OS.
>
> Customer has a Multi-Million Table in a Multi Partitioned Tablespace
> let’s say Table T1.
>
> %75 Rows will be Deleted from T1
>
> The rows that ensure the following condition in T1 will be deleted.
>
> SELECTT1.C1, T1.C2,T1.C3….bla, bla…
>
> FROMT1, T2
>
> WHERET1.C1=T2.C1
>
> ANDT1.C2=T2.C2
>
> ANDT2.C3 = 0
>
> 1)We coded the following SQL to DSNTIAUL and output dataset would be
> LOAD REPLACE to T1. But Unload was still running after 24hours. So
> this was not a possible/feasible solution. Forget about NEW Indexes to
> enhance SQL Performance. It’s not an option. Table is BIG!
>
> SELECTT1.C1, T1.C2,T1.C3….bla, bla…
>
> FROMT1, T2
>
> WHERET1.C1=T2.C1
>
> ANDT1.C2=T2.C2
>
> ANDT2.C3 <> 0
>
> 2)IBM Utilities such as REORG with DISCARD and UNLOAD from TABLESPACE
> would be good alternatives but they do not accept view or Multi-Table
> SQL. So We cannot use them
>
> 3)Now we’re thinking the legacy way, a “Classic Batch SQL DELETE
> Programö with frequent COMMITs
>
> I’m still wondering if there is a way to DELETE those rows from T1 w/o
> writing any code. Customer says there is no way to identify the
> candidate rows for deletion without a JOIN.
>
> Happy 1st Friday of 2012
>
> C.
>
>
> -----End Original Message-----