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 15316
"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: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |