How to delete first few rows from a file with IBM i SQL
This morning I was working with a flatfile import from IFS into a DB2 database file. This should have been straight forward but the flat file contained several rows of crappy data at the start of the file. *ugh*
Since I am importing this data in a CLLE program, I quickly googled “how to delete table rows by rrn” only to go down a rabbit hole of different SQL versions, rowid’s and other complications caused by IBM i SQL being slightly different from all other SQL versions.
I did find a neat solution simply using the IBM i SQL RRN() function but then I found the LIMIT option… which is even easier. I like easy stuff.
I will detail both for your delight and pleasure ;).
So, for this blog let’s create a very easy file – with some duff data that I want to delete:
EASY SOLUTION #1
Finally, found an easy solution with the LIMIT option
I want to delete the first 3 rows in this file so I can just do:
delete from 'filename' limit 3
Yes, it’s really that simple.
NEAT SOLUTION #2
Assuming we are starting with a file that does not contain an RRN type field, with a unique row number in it, we can use SQL’s Relative Record Number function (RRN) to address this.
The same example with my BOB file would look like this:
select rrn(bob), bob from bob
The RRN function will show us the RRN of each row in the file:
So, to delete the first 3 rows I might type:
delete from bob where rrn(bob) <= 3
3 rows deleted from BOB in QTEMP.
Easy peasy lemon squesy