以as400 db2创建视图 [英] Creating views in as400 db2

问题描述

如果我在db2中创建一个视图，可以这样说：

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

这将基本上从表中拉出最后100行（我使用RNN，因为没有什么别的可以基于这种，不幸的是，不是我的设计）。我的问题是：

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:

• 此视图会自动更新为有更新的


• 该视图，更具体地，删除不匹配的行。例如，让我们说，它从最后的100行中删除了1000个文件。有些人添加一行，排除最低的RN的行被删除并添加了新行。


• 这样做有什么缺点吗？例如，当父表更新时的性能等等，或者也有兼容性问题（如果我选择一个独特的视图名称，并且不太可能被其他运行的视图使用，实际创建自己的视图是安全的视图？请记住，我们实际上并不维护系统本身运行的软件）。

我正在创建这个视图是因为我们必须从超过30m的条目中提取一些信息，而且无法真正排序或索引信息（再次，不是我的设计，而是软件创建者）。

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

编辑：澄清我实际上并不是要自己去测试，或者我会的。对于一个人，我不知道创造我们自己的观点（因此是最后一个问题项目符号）的含义，另外两个我主要收集研究报告给我的老板。

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.

编辑：其实你知道什么，我不会在这里问第二个问题，因为这是一个不同的问题。这不是关于如何找到一个独特的领域或任何一个，这是这个问题的一个附注。这是关于建立一个视图，它有任何意义，如果它会做我以后。

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.

推荐答案

在DB2 for i中的视图中，外部fullselect的定义中支持ORDER BY或FETCH FIRST子句。

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

然而，您可以使用当前支持的公用表表达式发布DB2 for i（6.1,7.1,7.2）。

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

以下内容已在7.1中测试：

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 
)

在回答其余的问题时，是的，视图是动态的。每次你从中读取时，你都会得到该时间点的最后100行。

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.

不过请注意，使用RRN函数，即使没有按照结果，意味着每次读取100行时，您都将进行全表扫描。所以表现可能会吸引。

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.

我觉得很难相信没有什么可以创建一个索引并用于订购。但是，如果真的是这样，请考虑添加一列以满足您的需求。在DB2 for i中为表添加列是相对容易的，而不需要可怕的LVLCHK（* NO）或重新编译现有的RPG应用程序。

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.

这篇关于以as400 db2创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！