Parameterize FETCH FIRST n ROWS ONLY in DB2

Question

I'm trying to do the following:

  select * 
  from table      
  fetch first @param rows only

@param is an int.

DB2 would not have it. I've heard of concatenating it with ||, but I can't seem to get that to work.

Anyone have experience with this?

(PS I saw a similar question) but didn't understand his approach using ':1'.

Is this a stored procedure? Are you executing this from a client? If so, what is the client code written in, and do you have access to it? — AJ., Sep 20 '10 at 15:18
@AJ It's a stored procedure, and I do have access to it. Can call the sp fine, just can't compile when passing in paramter for fetch first n rows only. — mint, Sep 20 '10 at 16:17

score 4 · Accepted Answer · answered Sep 22 '10 at 23:48

4

You could try the following:

select t.*
from (select r.*, row_number() over() as row_num  
      from table r) as t
where row_num <= @param

answered Sep 22 '10 at 23:48

Christian Maslen

I may try that... I just think there should be a way to pass in a parameter to the above query! – mint Sep 23 '10 at 11:18
I think (not 100% on this) the reason you can't with the above is the fetch first x rows only syntax is more of an optimization instruction than say a syntax feature for paging. It's instructing DB2 to not perform the usual aggressive prefetch reads thus saving some disk access. – Christian Maslen Sep 27 '10 at 22:34
This may be extremely inefficient with large tables, as you are assigning a row number to every row, then choosing the ones with low numbers. – WarrenT Jun 12 '14 at 17:31

score 1 · Answer 2 · edited Nov 09 '11 at 01:01

1

Try this, where V_NBR is your passed in parameter for the number of rows you want:

FETCH FIRST ' || DIGITS ( V_NBR ) || '  ROWS ONLY '

edited Nov 09 '11 at 01:01

sth

answered Nov 09 '11 at 00:54

rocket

Hi @rocket. I'm trying to do your solution on a db2 i-series machine programm. But It doesn't work. can you help me with that? – Nahid Bandi Nov 05 '18 at 08:47

2 Answers2