Hello,
Until this date, I believed that DB2 has no TRUNCATE TABLE command. But
then I came across http://publib.boulder.ibm.com/infoce...c/c0023297.htm
where it says:
After an ALTER TABLE statement containing
REORG-recommended operations, you can execute
only the following statements on a table:
REORG TABLE
DROP TABLE
ALTER TABLE
RENAME TABLE
TRUNCATE TABLE
So there _is_ a TRUNCATE TABLE command, or?
My DB2 Express-C v. 9.1 will NOT accept a "TRUNCATE TABLE foo" command.
--
Regards,
Troels Arvin <tr****@arvin.d k> http://troels.arvin.dk/ 10 13872
Troels Arvin wrote:
Until this date, I believed that DB2 has no TRUNCATE TABLE command. But
then I came across http://publib.boulder.ibm.com/infoce...c/c0023297.htm
where it says:
After an ALTER TABLE statement containing
REORG-recommended operations, you can execute
only the following statements on a table:
REORG TABLE
DROP TABLE
ALTER TABLE
RENAME TABLE
TRUNCATE TABLE
Doc defect... don't loose your faith just yet.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
On Jan 31, 3:13 pm, Troels Arvin <tro...@arvin.d kwrote:
Hello,
Until this date, I believed that DB2 has no TRUNCATE TABLE command. But
then I came acrosshttp://publib.boulder. ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2....
where it says:
After an ALTER TABLE statement containing
REORG-recommended operations, you can execute
only the following statements on a table:
REORG TABLE
DROP TABLE
ALTER TABLE
RENAME TABLE
TRUNCATE TABLE
So there _is_ a TRUNCATE TABLE command, or?
My DB2 Express-C v. 9.1 will NOT accept a "TRUNCATE TABLE foo" command.
--
Regards,
Troels Arvin <tro...@arvin.d k>http://troels.arvin.dk/
Hi, Troels:
This is somewhat well-known, so forgive me if you already know it, but
if it happens that you're looking not for the TRUNCATE command per se
but rather in the meantime a way to accomplish the same thing in the
absence of an explicit TRUNCATE command, in DB2 there are two ways to
get the same result of fast cleanout of all rows in a table:
1. ALTER TABLE <NOT LOGGED INITIALLY WITH EMPTY TABLE;
2. IMPORT from /dev/null
Regards,
--Jeff
Hello,
On Thu, 01 Feb 2007 10:12:59 -0800, jefftyzzer wrote:
a way to accomplish the same thing in the
absence of an explicit TRUNCATE command, in DB2 there are two ways to
get the same result of fast cleanout of all rows in a table:
1. ALTER TABLE <NOT LOGGED INITIALLY WITH EMPTY TABLE;
2. IMPORT from /dev/null
Which one requires the least privileges?
--
Regards,
Troels Arvin <tr****@arvin.d k> http://troels.arvin.dk/
On Feb 1, 11:42 am, Troels Arvin <tro...@arvin.d kwrote:
Hello,
On Thu, 01 Feb 2007 10:12:59 -0800, jefftyzzer wrote:
a way to accomplish the same thing in the
absence of an explicit TRUNCATE command, in DB2 there are two ways to
get the same result of fast cleanout of all rows in a table:
1. ALTER TABLE <NOT LOGGED INITIALLY WITH EMPTY TABLE;
2. IMPORT from /dev/null
Which one requires the least privileges?
--
Regards,
Troels Arvin <tro...@arvin.d k>http://troels.arvin.dk/
According to the SQL Reference Volume 2 and the Command Reference,
respectively:
ALTER needs at least one of the following:
ALTER on the table to be altered
CONTROL on the table to be altered
ALTERIN on the schema of the table
SYSADM
DBADM
IMPORT (to an existing table using the REPLACE or REPLACE_CREATE
option) requires one of the following:
SYSADM
DBADM
CONTROL on the table or view
INSERT, SELECT, and DELETE on the table or view
So I suppose IMPORT requires the least privileges, if you are generous
in giving INSERT, SELECT, and DELETE.
--Jeff
Hello,
On Thu, 01 Feb 2007 10:12:59 -0800, jefftyzzer wrote:
in DB2 there are two ways to
get the same result of fast cleanout of all rows in a table:
1. ALTER TABLE <NOT LOGGED INITIALLY WITH EMPTY TABLE;
2. IMPORT from /dev/null
Is there a performance difference between these two approaches?
--
Regards,
Troels Arvin <tr****@arvin.d k> http://troels.arvin.dk/
Troels Arvin wrote:
Hello,
On Thu, 01 Feb 2007 10:12:59 -0800, jefftyzzer wrote:
>in DB2 there are two ways to get the same result of fast cleanout of all rows in a table:
1. ALTER TABLE <NOT LOGGED INITIALLY WITH EMPTY TABLE; 2. IMPORT from /dev/null
Is there a performance difference between these two approaches?
Not that I'm aware of, but option 2 is fully recoverable.
If you truncate an important table using ALTER TABLE you should follow
it up with a backup, otherwise roll forward recovery will hiccup.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
On Fri, 02 Feb 2007 08:43:35 -0500, Serge Rielau wrote:
>>1. ALTER TABLE <NOT LOGGED INITIALLY WITH EMPTY TABLE; 2. IMPORT from /dev/null
Is there a performance difference between these two approaches?
Not that I'm aware of, but option 2 is fully recoverable.
If you truncate an important table using ALTER TABLE you should follow
it up with a backup, otherwise roll forward recovery will hiccup.
Ouch. I'll stay away from ALTER TABLE ... NOT LOGGED INITIALLY. (I've
recently been bit by a rather time consuming situation because we tried to
cut some corners using NOT LOGGED INITIALLY.)
--
Regards,
Troels Arvin <tr****@arvin.d k> http://troels.arvin.dk/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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...
|
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...
|
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)
|
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...
|
by: rdraider |
last post by:
Hi,
I am trying to create a script that deletes transaction tables and leaves
master data like customer, vendors, inventory items, etc. How can I use
TRUNCATE TABLE with an Exists? My problem is I have 200+ tables, if I
simply use a list like:
truncate table01
truncate table02
truncate table03
....
| |
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
|
by: bob |
last post by:
I am writing some code to create new tables in a SQL database. However,
I don't want to try to create a table if it already exists. How can I
test beforehand to see if a particular named table already exists in
the database, without actually trying to read records into a dataset?
Thanks.
|
by: Sala |
last post by:
Hi
I want to truncate all data in database ... pls help me how i ll
truncate?
|
by: Boogha |
last post by:
I have a cursor looping through a list of tables that I want to
truncate and then do a bulk insert into, Is this possible in SQL
Server 2000 or do script each table individually.
Cheers,
Adam
|
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. ...
|
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...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
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...
|
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...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| | |