Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Partial delete of rows in DB2 Version 8

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Partial delete of rows in DB2 Version 8
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    Post subject:
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: 1187
Location: Bangalore,India

PostPosted: Thu Jan 07, 2010 7:29 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts Skip file from delete pema_yozer All Other Mainframe Topics 3 Tue Aug 29, 2017 3:19 pm
No new posts Delete IMS remote transaction when MO... Sam Singh IMS DB/DC 0 Wed Aug 23, 2017 9:12 pm
No new posts compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us