Delete First Rows in DB2 (Mainframe z/OS)

[jethrow]

Anyone happen to know how to delete the first n rows from a table in Mainframe DB2? By first, I mean the way you can utilize fetch first n rows in a select statement. The order doesn't actually matter - I'm just trying to limit the result set so I can generically create a commit limit for deleting. I'd rather not find the key & try to create a condition to return a given n rows because I'd like this to be applied to several tables, and not become too complex. The following works of distributed DB2, which is quite frustrating:

Code: Select all

delete from
(select * from <TABLE> fetch first 2 rows only)

Executing this in Mainframe DB2 gives the folloiwng:

Error: SQL0104N An unexpected token "(" was found following "(" ...

[joedf]

Code: Select all

DELETE FROM table_name
WHERE some_column=some_value;

????

[jethrow]

Thanks for the input, but that will not satisfy my request. This is an automated process in production, so I cannot just execute a delete statement as such - since there could be millions+ of deletes - which could bog down the server. That's why I'm attempting to create a commit threshold.

The following works:

Code: Select all

delete from <TABLE_NAME>
where (<KEY>) in (
        select <KEY> from <TABLE_NAME>
        where <CONDITION>
        FETCH first <N> rows only     )

The problem with this, however, is there is a limit not only on the IN-statement, but there's also a Lock limit. Again, my goal is to create a generic SQL statement to consolidate code for a purge process on multiple tables.

[joedf]

hmmm im no expert in SQL, but have you tried StackOverflow? they answer quickly, and im sure youll find your answer there...

[jethrow]

So, I was just provided this SQL today, though I'm not completely sure it's fool-proof:

Code: Select all

DELETE FROM <TABLE_NAME>
WHERE rid(<TABLE_NAME>) <= (SELECT max(rid) FROM (
						        SELECT rid(<TABLE_NAME>) AS rid
						        FROM <TABLE_NAME>
						        ORDER BY rid(<TABLE_NAME>)
						        FETCH FIRST <N> ROWS ONLY) i)