Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

To delete N rows from a table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
RAJA-RAJA

New User


Joined: 11 Sep 2009
Posts: 10
Location: chennai

PostPosted: Wed Jul 11, 2012 1:46 pm    Post subject: To delete N rows from a table
Reply with quote

Can anyone pls let me know to delete the 'n' number of rows from a table ?

Already i tried with the query:
Delete from table where name in (select name from table where name = 's ' order by fetch first 10 rows only);

The above query is deleting all the records selected not only 10 records.

Can you suggest any other to do this?

Thanks for your help
Back to top
View user's profile Send private message

Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Wed Jul 11, 2012 1:51 pm    Post subject:
Reply with quote

Quote:
Delete from table where name in (select name from table where name = 's ' order by fetch first 10 rows only);

Is the query correct? It will return syntax error.

And, how you do run that query? using user-written program or any other?
Back to top
View user's profile Send private message
RAJA-RAJA

New User


Joined: 11 Sep 2009
Posts: 10
Location: chennai

PostPosted: Wed Jul 11, 2012 2:04 pm    Post subject:
Reply with quote

I have given the model query which i ran in QMF and its deleting all the selected rows , not only 10 rows.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1218
Location: Israel

PostPosted: Wed Jul 11, 2012 2:45 pm    Post subject: Reply to: To delete N rows from a table
Reply with quote

Then you should provide the criteria that will uniquely identify the 10 rows you want to delete!
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jul 11, 2012 6:52 pm    Post subject:
Reply with quote

Hello,

Your "requirement" seems to be rather "loose". . .

And as written, it cannot work.

What business function will be supported be deleting any 10 rows? As described, there would be no control of which rows were deleted even if this would work. . .

If you clearly explin what you are trying to accomplish (rather than how you tried to code it), someone may have a suggestion.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed Jul 11, 2012 8:08 pm    Post subject:
Reply with quote

Which version of DB2 are you at? Can you use ROW_NUMBER()?

And you're missing the column-name in "order by", so yes, as shown the SQL should not work.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Jul 12, 2012 3:08 pm    Post subject:
Reply with quote

Code:
Delete from table
where UniqueKeyColumns in (select UniqueKeyColumns from table where name = 's ' fetch first 10 rows only);
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Jul 12, 2012 3:26 pm    Post subject:
Reply with quote

Guy - dare I ask, but should not one use ORDER BY with "fetch first n rows", though the original post does not really specify about the "first 10" or "last 10" from the "result-set"? Please assist.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Jul 12, 2012 4:42 pm    Post subject:
Reply with quote

I thought so too, but apparantly an order by is not necessary.
Of course then it is just random 10 rows. You can add an order by (it could even be wise to do so on the clustering index)

the idea of deleting in "batches" is that you can do this in a loop with a commit, thus avoiding locking problems.
If the where-clause is too complicated and/or a temp result set must be built , then this could become costly.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Jul 12, 2012 5:10 pm    Post subject:
Reply with quote

Thanks GuyC. (Guy? What do you prefer? icon_smile.gif)

As you talked about "batch usage", the point make sense, however, my first perception about this thread was that there is a(n) STP on z/OS, request is origniated from a JAVA-screen and then ORDER BY might become a necessity, no? (ofsourse, much depend on the application).

Lately, I asked about ROW_NUMBER() so that this can be used:
Code:
DELETE from table A WHERE (A.name) in
(SELECT T.name FROM
(SELECT ROW_NUMBER() OVER (ORDER BY name) AS ROWNUM, name FROM table) AS T
WHERE ROWNUM < 11);
I've not checked it but I believe - with 10-rows on stake it won't be expensive enough too, yeah?

PS. Taking advantage of having a friendly DBA around here... icon_smile.gif.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Jul 12, 2012 8:45 pm    Post subject:
Reply with quote

you can indeed use row_number() to do the same. but the cost remains the same: if a temp resultset/ordering has to happen before DB2 can determine the rownumber / first 10 rows it will cost a lot for each set of 10 rows.

My first name is Guy , but because in english Guy is a synonym for dude, man, on forums I prefer GuyC so I know it's about me and not just any person.

I used the word "batch" as in group (of rows), not as a type of processing.
For mainframe jobs we have other and better coding techniques to manipulate commitfrequency/restartability.
We've used this principle in Java (coding native SQL, not calls to stp) because they also need to delete sometimes and they caused timeouts and lock-escalations. this way we ensure timely commits and communication between MF and servers keeping the thread open.

Ofcourse I would recommend a number higher than 10. closer to 10000 (depending on lock-granularity and so on).
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Jul 13, 2012 6:28 pm    Post subject:
Reply with quote

Thank you GuyC - this helps. Have a good one. And
Quote:
on forums I prefer GuyC
That's pretty logical, GuyC... icon_smile.gif

Regards,
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 JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us