To delete N rows from a table

RAJA-RAJA · New User Joined: 11 Sep 2009 Posts: 10 Location: chennai

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

Gnanas N · Posted: Wed Jul 11, 2012 1:51 pm

RAJA-RAJA · New User Joined: 11 Sep 2009 Posts: 10 Location: chennai

I have given the model query which i ran in QMF and its deleting all the selected rows , not only 10 rows.

Marso · Posted: Wed Jul 11, 2012 2:45 pm

Then you should provide the criteria that will uniquely identify the 10 rows you want to delete!

dick scherrer · Posted: Wed Jul 11, 2012 6:52 pm

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.

Anuj Dhawan · Posted: Wed Jul 11, 2012 8:08 pm

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.

GuyC · Posted: Thu Jul 12, 2012 3:08 pm

Anuj Dhawan · Posted: Thu Jul 12, 2012 3:26 pm

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.

GuyC · Posted: Thu Jul 12, 2012 4:42 pm

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.

Anuj Dhawan · Posted: Thu Jul 12, 2012 5:10 pm

Thanks GuyC. (Guy? What do you prefer?

)

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:

GuyC · Posted: Thu Jul 12, 2012 8:45 pm

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).

Anuj Dhawan · Posted: Fri Jul 13, 2012 6:28 pm

Thank you GuyC - this helps. Have a good one. And