View previous topic :: View next topic
|
Author |
Message |
Anand Kumar
New User
Joined: 29 Aug 2007 Posts: 24 Location: chennai
|
|
|
|
Hello Experts,
I have a very big table, I need to delete rows partially like little by little.
I tried the below query, but in version 8.0 it gives me the following error.
Code: |
DELETE FROM GWCC100 WHERE KANT_KEY IN(
SELECT KANT_KEY FROM GWCC100
FETCH FIRST ROW ONLY);
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD FETCH. TOKEN ) UNION
EXCEPT WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 506 0 0 -1 145 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001FA' X'00000000' X'00000000' X'FFFFFFFF'
X'00000091' X'00000000' SQL DIAGNOSTIC INFORMATION
|
Please let me know if there is any other solution to my problem? |
|
Back to top |
|
|
Ronald Burr
Active User
Joined: 22 Oct 2009 Posts: 293 Location: U.S.A.
|
|
|
|
Would the following meet your needs? It would still delete rows matching just one KANT_KEY per invocation.
Code: |
DELETE FROM GWCC100
WHERE KANT_KEY =
(SELECT MIN(KANT_KEY)
FROM GWCC100); |
|
|
Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005 Posts: 1208 Location: Bangalore,India
|
|
|
|
Hello Anand,
I have few questions:
First Do you wnat to delete all the rows of the table then run dummy load REPLACE?
Secondly You have specified that you want to delete little by little ...What does that mean & whats the business justification for this? |
|
Back to top |
|
|
Anand Kumar
New User
Joined: 29 Aug 2007 Posts: 24 Location: chennai
|
|
|
|
hi Guptae,
We have a table which is being loaded 24/7. Every day we take back up of the table in the evening and delete the rows for which we have back up. Mean while if any data is inserted the data is being deleted next day. In this process, when we delete data for more than 10 million rows it takes more time and there is a Lock on the table and the online systems are not able to update table creating chaos. Hope you understand the situation. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Code: |
declare CS1 cursor with hold for select ... with rowset positioning
open CS1
fetch CS1 for 1000 rows
perform until sqlcode not = 0
delete from table from T1 where current of CS1
commit
fetch CS1 for 1000 rows
end-perform
if sqlerrd(3) > 0 then
delete from table from T1 where current of CS1
end-if
close CS1 |
|
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
guptae wrote: |
First Do you wnat to delete all the rows of the table then run dummy load REPLACE?
|
Don't tell this without reservation if you don't know how the table is structured. A lot of other data could get lost or inconsistent . |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Another possible solution for this kind of requirement :
In Version 9 : clone tables
In Version 8 : make a table with 2 partitions and rotate.
ALTER TABLE t1 ALTER PARTITION 2 ENDING AT(currentmaxvalue);
ALTER TABLE t1 ROTATE PARTITION FIRST TO LAST ENDING AT(highvalue) RESET;
Then you can start with backup/processing of partition 1
Sorry for the 3 seperate posts, my brain works in chunks today. |
|
Back to top |
|
|
|