How to truncate table ? 
Author Message
 How to truncate table ?
Hello,
Do you know how to TRUNCATE TABLE, in DB2 UDB v 7.2? (= delete * from table
(RDBMS ORACLE or SYBASE) )


Tue, 03 Aug 2004 03:43:42 GMT
 How to truncate table ?

You can load an empty file into the table.
Nanchil
Quote:

> Hello,
> Do you know how to TRUNCATE TABLE, in DB2 UDB v 7.2? (= delete * from table
> (RDBMS ORACLE or SYBASE) )



Tue, 03 Aug 2004 06:34:27 GMT
 How to truncate table ?

Quote:

> Hello,
> Do you know how to TRUNCATE TABLE, in DB2 UDB v 7.2? (= delete * from table
> (RDBMS ORACLE or SYBASE) )

The DB2 equivalent is LOAD utility, using the REPLACE option, and an
Empty input file.
No logging is done...


Tue, 03 Aug 2004 06:45:38 GMT
 How to truncate table ?
Use either the load or import utility and perform a REPLACE into the table
using an empty file.  If using unix /dev/null (if defined) generally does
the trick.  This will REMOVE all data from the table.


Quote:
> Hello,
> Do you know how to TRUNCATE TABLE, in DB2 UDB v 7.2? (= delete * from
table
> (RDBMS ORACLE or SYBASE) )



Tue, 03 Aug 2004 06:34:41 GMT
 How to truncate table ?
If you are using retain logging it is quicker to use import replace with the
empty file as it will not require a backup.

...Dwaine



Quote:
> > Hello,
> > Do you know how to TRUNCATE TABLE, in DB2 UDB v 7.2? (= delete * from
table
> > (RDBMS ORACLE or SYBASE) )

> The DB2 equivalent is LOAD utility, using the REPLACE option, and an
> Empty input file.
> No logging is done...



Tue, 03 Aug 2004 09:54:35 GMT
 How to truncate table ?
You can also use  -  Alter table ... activate not logged initially with
empty table.  Only thing is the table would have had to be created with
the not logged initially option.
Quote:

>Hello,
>Do you know how to TRUNCATE TABLE, in DB2 UDB v 7.2? (= delete * from table
>(RDBMS ORACLE or SYBASE) )



Thu, 05 Aug 2004 09:11:13 GMT
 How to truncate table ?
For those interested..... in the Windows world, NUL is the equivalent
/dev/null. Yes, only one L to confuse database people. So to truncate
a table in DB2/NT use:
db2 import from nul of del replace into tablename
You need to use a filetype of del - if you try ixf it will say it is
not a valid ixf file.

Phil Castle.
www.querytool.com

Quote:

> Use either the load or import utility and perform a REPLACE into the table
> using an empty file.  If using unix /dev/null (if defined) generally does
> the trick.  This will REMOVE all data from the table.



> > Hello,
> > Do you know how to TRUNCATE TABLE, in DB2 UDB v 7.2? (= delete * from
>  table
> > (RDBMS ORACLE or SYBASE) )



Fri, 06 Aug 2004 04:04:40 GMT
 How to truncate table ?
If you have to do a lot of truncates it is generally worth your while to
code a stored procedure that takes a schema and table name and does an
import from /dev/null (nul on windoze). This way you can run it from any
tool that supports sql. Unfortunately import is part of the administrative
api and IBM doesn't currently have support java bindings for the
administrative api so you have to use C/C++. BTW they must have java
bindings for the control center to use, they just haven't made them public.
Just another one of those things that make you go hmmmmmmmmm.


Quote:
> You can also use  -  Alter table ... activate not logged initially with
> empty table.  Only thing is the table would have had to be created with
> the not logged initially option.


> >Hello,
> >Do you know how to TRUNCATE TABLE, in DB2 UDB v 7.2? (= delete * from
table
> >(RDBMS ORACLE or SYBASE) )



Fri, 06 Aug 2004 05:29:48 GMT
 How to truncate table ?
Dwaine,  Serge, et al: I've spent a fair amount of debate with the DB2 Data
Manager guys on this issue on whether to simulate truncate with LOAD or IMPORT +
Replace. This is the authoritative answer (until I change my mind, or BobH or
JanN corrects me):

"Emptying a table without telling the logger (TRUNCATE TABLE)

Now that youve got all these neat ways to fool around with your data, its time
for your boss to announce that its time to kill the design and start over.
Other products have something called TRUNCATE TABLE delete all rows in the
table, dont log the operation (so its fast) but leave the table intact
(otherwise youd just issue a DROP TABLE).  There are two ways to do this in
DB2.  A fast way is IMPORT REPLACE with an IXF file that contains no rows:

 import from c:\ixf\rep.ixf of ixf replace into testvar

IMPORT is usually the best choice, because it does not lock the table space. If
the table has many active pages in the bufferpool, LOAD will be faster, as
IMPORT will flush the bufferpool. LOAD must be used if the target table has
Referential Integrity dependencies or summary tables defined on it. In Version 7
and earlier, LOAD requires exclusive access to the table space. If you plan to
use LOAD a lot, put each table that gets LOADed (or uses LOAD to be emptied) in
its own table space, or use IMPORT REPLACE."

This is from my Breaking Relational Vows article on the DB2 Developer's Domain.

Quote:

> If you are using retain logging it is quicker to use import replace with the
> empty file as it will not require a backup.

> ...Dwaine





> > > Hello,
> > > Do you know how to TRUNCATE TABLE, in DB2 UDB v 7.2? (= delete * from
> table
> > > (RDBMS ORACLE or SYBASE) )

> > The DB2 equivalent is LOAD utility, using the REPLACE option, and an
> > Empty input file.
> > No logging is done...



Fri, 13 Aug 2004 03:46:26 GMT
 How to truncate table ?

Quote:

> Dwaine,  Serge, et al: I've spent a fair amount of debate with the DB2 Data
> Manager guys on this issue on whether to simulate truncate with LOAD or IMPORT +
> Replace. This is the authoritative answer (until I change my mind, or BobH or
> JanN corrects me):

> "Emptying a table without telling the logger (TRUNCATE TABLE)

> A fast way is IMPORT REPLACE with an IXF file that contains no rows:

>  import from c:\ixf\rep.ixf of ixf replace into testvar

> IMPORT is usually the best choice, because it does not lock the table space. If
> the table has many active pages in the bufferpool, LOAD will be faster, as
> IMPORT will flush the bufferpool. LOAD must be used if the target table has
> Referential Integrity dependencies or summary tables defined on it. In Version 7
> and earlier, LOAD requires exclusive access to the table space. If you plan to
> use LOAD a lot, put each table that gets LOADed (or uses LOAD to be emptied) in
> its own table space, or use IMPORT REPLACE."

I wouldn't even have courage to dream about correcting Blair, I would
just add to his wise advice(s):

Instead of

  import from c:\ixf\rep.ixf of ixf replace into testvar

I would use

  import from /dev/null of del replace into testvar
  - on Unix platform;
or
  import from NUL of del replace into testvar -
  on platforms originated from that little company in Seattle - which
created and sold to another    little company in Seattle a program
loader called 86-DOS.

The reason is that when using IXF format - one has to create first an
empty .ixf file - presumably by:

  export to empty.ixf of ixf select * from testvar where 1=0

One cannot use 0 length file as input to import routine - import would
terminate with:

SQL3054N  The input file is not a valid PC/IXF file.  The file is too
short to
contain a valid H record.

Jan M. Nelken



Fri, 13 Aug 2004 04:38:49 GMT
 
 [ 10 post ] 

 Relevant Pages 

1. Using TRUNCATE TABLE" instead of DELETE TABLE

2. Problem with Drop Table and truncate table

3. Wrapping a truncate table within a transaction?

4. Problem with Truncate Table

5. Access2000 (MSDE) IMPORT TRUNCATES TABLES AT 10,000 recs..?!

6. DTS Copy SQL Server Objects Task Truncating tables

7. Urgent: How to tell who issued truncate table command

8. SP3 , truncate table problem

9. How to Truncate Table Referenced by Foreign Key?

10. Error when truncating table, please help me

11. Help with Truncate Table


 
Powered by phpBB® Forum Software