Creating views in as400 db2

Question

If I create a view in db2, say something like:

select 
  RNN(sometable) as rn, 
  c1,c2,c3 
from sometable 
order by rn desc 
fetch first 100 rows only

This would essentially pull the last 100 rows from the table (I use RNN because there's nothing else to base that sort by, sadly. Not by my design). My questions on this are:

• Does this view automatically get updated as sometable gets updated
• Does this view, more specifically, remove non-matching rows. For instance, lets say it pulls the last 100 rows out of a file that has 1000. Some one adds a row, does the row with the lowest RN get removed and the new row added?
• Are there any drawbacks to doing this? For instance, performance when the parent table is updated, etc. Or also, are there any compatibility concerns (if I pick a view name that's unique and unlikely to be used by anything else running on it, is it safe to actually create our own views? Keeping in mind we don't actually maintain the software running on the system itself).

The reason I'm creating this view is because we have to pull some information out of a table with over 30m entries, and no way to really order or index that information (again, not by my design, but the software creators).

Any help is appreciated! Thanks

Edit: to clarify I'm not actually in a position to just go test this myself or I would. For one I don't know the implications of creating our own view (hence the last question bullet), and two I'm mostly gathering research to present to my boss.

Edit: actually you know what, I'm not going to ask that second question here because it's a different question. This is not about how to find a unique field or any of that, that was a side note in this question. This was about building a view, any implications it has, and if it would do what I'm after.

Answer 1

Neither the ORDER BY nor FETCH FIRST clauses are supported in the definition of the outer fullselect in a view in DB2 for i.

You can however, use a common table expression in currently supported releases of DB2 for i (6.1, 7.1, 7.2).

The following was tested on 7.1:

create view myview 
 as ( with tbl as (select RNN(sometable) as rn, 
                          c1,c2,c3 
                          from sometable 
                          order by rn desc 
                          fetch first 100 rows only  
                  )
select * from tbl 
)

In answer to the rest of your questions, yes the view is dynamic. Every time you read from it you'll get the last 100 rows at that point in time.

However note that the use of the RRN function, even without ordering by the results, means you'll be doing a full table scan every time you read those 100 rows. So performance is probably going to suck.

I find it hard to believe there's nothing you can create an index over and use for ordering. However, if that's really the case, consider adding a column to suit your needs. It is relatively easy to add a column to a table in DB2 for i without requiring either the dreaded LVLCHK(*NO) or recompiling existing RPG applications.