hi, i have a table with create ddl using not logged initially,
CREATE TABLE "NJIPD "."IVPWGMR" ("IVDY01" DECIMAL(2,0) , "IVMO01"
DECIMAL(2,0) , "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY
( START WITH +1 , INCREMENT BY +1 , NO CACHE ) ) IN "USERSPACE1" NOT
LOGGED INITIALLY ;
right now i have to delete almost one million rows in the table, so i
found that i can use alter table activate not logged initially before
i execute delete, but i found that delete still use the transaction
log and cause log error full problem , why is that? 4 15326
"xixi" <da****@yahoo.com> wrote in message
news:c0**************************@posting.google.c om... hi, i have a table with create ddl using not logged initially,
CREATE TABLE "NJIPD "."IVPWGMR" ("IVDY01" DECIMAL(2,0) , "IVMO01" DECIMAL(2,0) , "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1 , INCREMENT BY +1 , NO CACHE ) ) IN "USERSPACE1" NOT LOGGED INITIALLY ;
right now i have to delete almost one million rows in the table, so i found that i can use alter table activate not logged initially before i execute delete, but i found that delete still use the transaction log and cause log error full problem , why is that?
"not logged initially" is turned off after the first commit. So it will
only help you if you're deleting all the rows via one DELETE statement and
then committing the changes.
--
Matt Emmerton
"xixi" <da****@yahoo.com> wrote in message
news:c0**************************@posting.google.c om... hi, i have a table with create ddl using not logged initially,
CREATE TABLE "NJIPD "."IVPWGMR" ("IVDY01" DECIMAL(2,0) , "IVMO01" DECIMAL(2,0) , "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1 , INCREMENT BY +1 , NO CACHE ) ) IN "USERSPACE1" NOT LOGGED INITIALLY ;
right now i have to delete almost one million rows in the table, so i found that i can use alter table activate not logged initially before i execute delete, but i found that delete still use the transaction log and cause log error full problem , why is that?
"not logged initially" is turned off after the first commit. So it will
only help you if you're deleting all the rows via one DELETE statement and
then committing the changes.
--
Matt Emmerton
"Matt Emmerton" <me******@yahoo.com> wrote in message
news:e1**********************@twister01.bloor.is.n et.cable.rogers.com... "xixi" <da****@yahoo.com> wrote in message news:c0**************************@posting.google.c om... hi, i have a table with create ddl using not logged initially,
CREATE TABLE "NJIPD "."IVPWGMR" ("IVDY01" DECIMAL(2,0) , "IVMO01" DECIMAL(2,0) , "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1 , INCREMENT BY +1 , NO CACHE ) ) IN "USERSPACE1" NOT LOGGED INITIALLY ;
right now i have to delete almost one million rows in the table, so i found that i can use alter table activate not logged initially before i execute delete, but i found that delete still use the transaction log and cause log error full problem , why is that?
"not logged initially" is turned off after the first commit. So it will only help you if you're deleting all the rows via one DELETE statement and then committing the changes.
-- Matt Emmerton
To add to what Matt has said, you need turn off auto-commit for this to
work, and then explicitly commit after the delete. This can be done with the
+c parm on the db2 command line. For example.
db2 +c -tvf input.sql
In input.sql you will have 3 SQL statements:
alter table ... (to set not logged initially) ;
delete ... ;
commit;
"Matt Emmerton" <me******@yahoo.com> wrote in message
news:e1**********************@twister01.bloor.is.n et.cable.rogers.com... "xixi" <da****@yahoo.com> wrote in message news:c0**************************@posting.google.c om... hi, i have a table with create ddl using not logged initially,
CREATE TABLE "NJIPD "."IVPWGMR" ("IVDY01" DECIMAL(2,0) , "IVMO01" DECIMAL(2,0) , "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1 , INCREMENT BY +1 , NO CACHE ) ) IN "USERSPACE1" NOT LOGGED INITIALLY ;
right now i have to delete almost one million rows in the table, so i found that i can use alter table activate not logged initially before i execute delete, but i found that delete still use the transaction log and cause log error full problem , why is that?
"not logged initially" is turned off after the first commit. So it will only help you if you're deleting all the rows via one DELETE statement and then committing the changes.
-- Matt Emmerton
To add to what Matt has said, you need turn off auto-commit for this to
work, and then explicitly commit after the delete. This can be done with the
+c parm on the db2 command line. For example.
db2 +c -tvf input.sql
In input.sql you will have 3 SQL statements:
alter table ... (to set not logged initially) ;
delete ... ;
commit; This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: xixi |
last post by:
hi, i found that in alter table command , you can do activate not
logged initially to help not log any changes made to the table to log
file, cause i have to delete almost 1 million records and my...
|
by: DB_2 |
last post by:
Greetings,
I was searching Google for ways to turn off transaction logging for
some queries. I came across this old post from Feb 2003:
> From: fareeda (fareeda@pspl.co.in)
> Subject: Re:...
|
by: db2group88 |
last post by:
hi, we install db2 udb v8.1 on windows 64bit, in our application, we
have sql execute "create table .... not logged initially", but from
the operating system event viewer showing error stating that...
|
by: Daniel Chou |
last post by:
Hello,
I have two questions about "not logged initially":
1. Before using "alter table tbname activate not logged initially",
should the table be created with "not logged initially"?
2....
|
by: efiryago |
last post by:
I have about 200 tables in a DB2 V8.1.4 LUW application schema. There
is a third party java appllication that accesses this schema. It
started failing and relevant entries in db2diag.log have this:...
| |
by: p175 |
last post by:
People, I have the following table and I need to add a
column that uses a the listed UDF to count the number of days within
specific quarters by year.
CREATE TABLE HISTORY_MB (...
|
by: tojigneshshah |
last post by:
Hi,
In a stored procedure,
If i use "alter table <tablename> activate not logged intially with
empty table", i get SQL104n error.
If i use "execute immediate alter table <tablename>...
|
by: Klemens |
last post by:
I want to create an MQT, intialize it manually and then use staging table to
refresh it with new data, but i don' t see a way for not doing full refresh
after creating the staging table.
I tried...
|
by: AutumnsDecay |
last post by:
Hey everyone.
I have been writing a testimonials backend for a client who wishes to use the feature. How it is supposed to work is like this:
The user writes a testimonial of their experience...
|
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,...
|
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...
| |
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...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
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...
|
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...
|
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 ...
| |
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...
| |