By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,851 Members | 1,729 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,851 IT Pros & Developers. It's quick & easy.

import from NUL converted to ordinary delete

P: n/a
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.tbname]

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

delete from [tbcreator.tbname]

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

Jul 25 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
fy****@googlemail.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.tbname]

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

delete from [tbcreator.tbname]

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.TEMPTABLE will truncate a temp table
instead of deleting from it row by row.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jul 25 '07 #2

P: n/a
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.TABNAME AND
R.TABSCHEMA=T.TABSCHEMA THEN 1 ELSE 0 END) AS SELFREFS FROM
TABLE(SYSPROC.BASE_TABLE('ADMDB2','TMABIN_ALL_DEL' )) B, SYSCAT.TABLES
T LEFT OUTER JOIN SYSCAT.REFERENCES R ON R.REFTABNAME=T.TABNAME AND
R.REFTABSCHEMA=T.TABSCHEMA LEFT OUTER JOIN SYSCAT.TABCONST TC ON
TC.TABNAME=R.TABNAME AND TC.TABSCHEMA=R.TABSCHEMA AND
TC.CONSTNAME=R.CONSTNAME AND TC.TYPE='F' where T.TABNAME=B.BASENAME
AND T.TABSCHEMA=B.BASESCHEMA 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.TEMPTABLE will truncate a temp table
instead of deleting from it row by row.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Jul 25 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.