import from NUL converted to ordinary delete

[fynn00]

Hi folks,

we are running DPF Version 8.2 on windows 2000 (don't blame me).

We try to truncate a table. However when I issue

import from NUL of del replace into [tbcreator.tbnam e]

and take a snapshot for that application I see the following SQL
statement:

delete from [tbcreator.tbnam e]

Is this how the "truncate" is implemented for DB2 Version 8.2 DPF on
windows????
There is no pint in doing the import from NUL that way because this
will still lead to a great deal of logging...

Thnaks
Florian

[Serge Rielau]

fy****@googlema il.com wrote:

Hi folks,

we are running DPF Version 8.2 on windows 2000 (don't blame me).

We try to truncate a table. However when I issue

import from NUL of del replace into [tbcreator.tbnam e]

and take a snapshot for that application I see the following SQL
statement:

delete from [tbcreator.tbnam e]

Is this how the "truncate" is implemented for DB2 Version 8.2 DPF on
windows????
There is no pint in doing the import from NUL that way because this
will still lead to a great deal of logging...

Florian,

The proof is in the pudding. Is it faster or not. Do you see the logging
or not? I'm not familiar with the implementation of IMPORT, but it may
be that it passes special instructions to DB2 along with the DELETE.
For example a DELETE FROM SESSION.TEMPTAB LE will truncate a temp table
instead of deleting from it row by row.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[fynn00]

Thank you Serge.

Interesting:
I created a table with 2 Million rows and started the import replace
from NUL

A snapshot while the import was running showd the following SQL:
SELECT T.TYPE, SUM(CASE WHEN TC.ENFORCED='Y' THEN 1 ELSE 0 END) AS
CHILDREN, SUM(CASE WHEN TC.ENFORCED='Y' AND R.TABNAME=T.TAB NAME AND
R.TABSCHEMA=T.T ABSCHEMA THEN 1 ELSE 0 END) AS SELFREFS FROM
TABLE(SYSPROC.B ASE_TABLE('ADMD B2','TMABIN_ALL _DEL')) B, SYSCAT.TABLES
T LEFT OUTER JOIN SYSCAT.REFERENC ES R ON R.REFTABNAME=T. TABNAME AND
R.REFTABSCHEMA= T.TABSCHEMA LEFT OUTER JOIN SYSCAT.TABCONST TC ON
TC.TABNAME=R.TA BNAME AND TC.TABSCHEMA=R. TABSCHEMA AND
TC.CONSTNAME=R. CONSTNAME AND TC.TYPE='F' where T.TABNAME=B.BAS ENAME
AND T.TABSCHEMA=B.B ASESCHEMA GROUP BY T.TYPE

However:
UOW log space used (Bytes) = 0

the import replace took 1 second
the delete for the same amount of data took 13 seconds and showed - of
course - logging

Thank you Serge, next time I will try before asking :)

Regards,
Florian

>
The proof is in the pudding. Is it faster or not. Do you see the logging
or not? I'm not familiar with the implementation of IMPORT, but it may
be that it passes special instructions to DB2 along with the DELETE.
For example a DELETE FROM SESSION.TEMPTAB LE will truncate a temp table
instead of deleting from it row by row.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab