truncate table for DB2 - differences

Stefan Mueller

Hello all,

what is the internal difference between the following two truncate
table commands:
db2 import from /dev/null of del replace into <table_name>

and

alter table <table_nameactivate not logged initially with empty
table
Thank you very much in advance.

Kind regards,

Stefan

--
Stefan Mueller
Hamburg, Germany

Jun 20 '07 #1

Subscribe Reply

6084

Serge Rielau

The IMPORT can be handled by roll-forward recovery.
The ALTER TABLE cannot.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Jun 20 '07 #2

Stefan Mueller

Hi Serge,

thank you very much for your fast reply.

Is it more likely to get a dead lock by using the IMPORT command
instead using the ALTER TABLE?

Kind regards,

Stefan

Jun 20 '07 #3

Serge Rielau

Stefan Mueller wrote:

Is it more likely to get a dead lock by using the IMPORT command
instead using the ALTER TABLE?

In general I would assume that table truncation is at the beginning of a
transaction (or on its own). I fail to see how that could cause a deadlock.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Jun 20 '07 #4

Ekta

You may consider using LOAD instead of import.

@Ekta

Jun 23 '07 #5

Troels Arvin

On Sat, 23 Jun 2007 19:28:25 +0000, Ekta wrote:

You may consider using LOAD instead of import.

I generally try to use INSERT instead of LOAD whenever feasible. I've
seen LOAD result in rather nasty need-to-involve-the-DBA-and-even-
sometimes-IBM-support situations because of LOAD operations that failed.

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/

Jun 24 '07 #6

Troels Arvin

On Sun, 24 Jun 2007 19:33:39 +0000, I wrote:

I generally try to use INSERT instead of LOAD whenever feasible. I've
seen LOAD result in rather nasty need-to-involve-the-DBA-and-even-
sometimes-IBM-support situations because of LOAD operations that failed.

I meant to write "IMPORT" (not INSERT).

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/

Jun 25 '07 #7

Dave Hughes

On Mon, 25 Jun 2007 17:58:10 +0000, Troels Arvin scribbled:

On Sun, 24 Jun 2007 19:33:39 +0000, I wrote:
>I generally try to use INSERT instead of LOAD whenever feasible. I've
seen LOAD result in rather nasty need-to-involve-the-DBA-and-even-
sometimes-IBM-support situations because of LOAD operations that
failed.

I meant to write "IMPORT" (not INSERT).

FYI - I've only seen what you describe with LOAD in situations where the
database is set up to use archived logging. With circular logging, I've
always found LOAD to be perfectly reliable (although a few SET INTEGRITY
calls may be required afterward, if foreign keys are defined).

Admittedly, circular logging has several limitations (no online backup,
no rollforward recovery, etc.) and certainly isn't suitable for many
installations - but if it is used, LOAD should be seriously considered as
an alternative to IMPORT.

Cheers,

Dave.

Jul 4 '07 #8

Similar topics

16032

Truncate Table vs. Drop Table

by: Ed | last post by:

I am trying to get some information to compare and contrast the Truncate Table function and the Drop Table function. I know that using Truncate Table is faster and saves the structure of the table while the Drop Table will delete the table totally (data and structure). My question is whether using one function over the other will use up...

Microsoft SQL Server

4520

What resources does truncate table use

by: martin | last post by:

Hi, We have a heavily used production server and a table which logs every hit on a web site. This table has grown large over time and we want to clear it down as efficiently as possible. We would like to issue a truncate table statement, but with millions of rows we are a bit wary of how this will affect server performance. The alternative is...

Microsoft SQL Server

8643

Dynamic Sql: Truncate Table: Trap @@ERROR

by: LineVoltageHalogen | last post by:

Greeting All, I have a stored proc that dynamically truncates all the tables in my databases. I use a cursor and some dynamic sql for this: ...... create cursor Loop through sysobjects and get all table names in my database. .... exec ('truncate table ' + @TableName)

Microsoft SQL Server

3097

Log Invalidated after truncate table

by: ronin 47th | last post by:

Hi group, In one of the books 'Gurus Guide to Transact SQL' i found this info: ------------------------------------------------------------ TRUNCATE TABLE empties a table without logging row deletions in the transaction log. It can't be used with tables referenced by FOREIGN KEY constraints, and it invalidates the transaction log for the...

Microsoft SQL Server

14680

truncate in db2

by: Sumanth | last post by:

Are there any implementations of truncate in db2. Is it going to be implemented in the future? Is there an alternate way of doing a truncate of a table that has a high record count without using "load" and is fast? Thanks, Sumanth

DB2 Database

13096

Truncate Table

by: pramod | last post by:

Hi I know we can truncate a table in DB2 by first creating it with NOT LOGGED INITIALLY option. and when we need to truncate it, run the following command alter table <table name> activate not logged initially with empty table My question is: 1. Do we have to connect to the database with auto-commit off every

DB2 Database

18656

Truncate Table On Linked Server?

by: Neil | last post by:

Can one use Truncate Table on a linked server table? When I try it, I get a message that only two prefixes are allowed. Here's what I'm using: Truncate Table svrname.dbname.dbo.tablename

Microsoft SQL Server

13868

TRUNCATE TABLE really exists, or?

by: Troels Arvin | last post by:

Hello, Until this date, I believed that DB2 has no TRUNCATE TABLE command. But then I came across http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/c0023297.htm where it says: After an ALTER TABLE statement containing REORG-recommended operations, you can execute only the following statements on a...

DB2 Database

7677

How to reduce table size using delete or truncate statement in 10G

by: ananthaisin | last post by:

How to reduce the table size for any table while using truncate or delete statements. In oracle 8i it was truncating the storage space but in 10g it is not .... I have given truncate statement in a procedure to drop the storage of a table used... when its record count exceeds 500. BEGIN i:= 0; FOR CC IN NLD LOOP

Oracle Database

7357

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...

General

7282

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...

Windows Server

7526

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...

C / C++

7675

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...

Online Marketing

5208

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...

Microsoft Access / VBA

4840

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...

C# / C Sharp

3334

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

1746

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

906

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP