IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Partial delete of rows in DB2 Version 8


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Anand Kumar

New User


Joined: 29 Aug 2007
Posts: 24
Location: chennai

PostPosted: Thu Jan 07, 2010 7:01 pm
Reply with quote

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
View user's profile Send private message
Ronald Burr

Active User


Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.

PostPosted: Thu Jan 07, 2010 7:27 pm
Reply with quote

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
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India

PostPosted: Thu Jan 07, 2010 7:29 pm
Reply with quote

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
View user's profile Send private message
Anand Kumar

New User


Joined: 29 Aug 2007
Posts: 24
Location: chennai

PostPosted: Thu Jan 07, 2010 7:42 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Jan 07, 2010 8:16 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Jan 07, 2010 8:23 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Jan 07, 2010 8:32 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts DELETE SPUFI DB2 1
No new posts To get the count of rows for every 1 ... DB2 3
No new posts isfline didnt work in rexx at z/OS ve... CLIST & REXX 7
No new posts DSNTIAUL driven delete IBM Tools 0
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
Search our Forums:

Back to Top