Welcome to my continuing series focusing on The Most
Misunderstood Features of DB2. Today’s topic is an oldie but a goodie: base
table views.
What is a Base
Table View?
I am going to assume that regular readers of this blog know what
a view is (if not, go here). So what is a base table view? It is a view that
contains all of the columns and all of the rows of the base table and nothing
else. In other words, it is like a SELECT * against the base table with no WHERE
clauses.
Now why would somebody create such a beast? This gets to the
heart of the misunderstanding here. There is a continuing notion “out there”
that it is a good idea to create a base table view for every table and to give programmers
access to the base table view only, not the base table itself.
The reason given for doing so is to insulate programs from
change. But this is a weak argument at best. I admit that this particular
misunderstanding is less prevalent than it was in the earlier years of DB2. But
I do still find shops adhering to this archaic, and poorly founded, idea.
Reasons Given for
Base Table Views
One reason given by base table view proponents is that when you
add a column to a table you do not have to change the program. But this is the
case, too, if you code your programs appropriately, avoiding SELECT * and
coding only the specific columns you need to access. Most shops do this even
when using base table views.
Other reasons given revolve around removing columns or splitting
tables. If you are interested in all of the reasons and rebuttals I urge you to
read my article (PDF) on this topic from 1991 titled One View Per Base Table? Don’tDo It! (Wow! This is a quarter of a Century old now!)
Another View
Fallacy
I recall yet another misunderstanding about views from many
years ago that I have not heard in a long time. Basically, the argument was
that accessing a base table view would out-perform accessing the base table.
This was never proven to me – and I do not believe it was ever
true. How could it be? Static SQL against a base table view would use view
merge so it would be the same as going against the base table. And dynamic SQL
would have the added step of having to resolve the view to the base table. Sooo…
Summary
Base table views are not worth the time and effort because the
bring no added value of any type whatsoever. Do not use them.
In our company different departments need to export using Excel, the DB.MASTER_PART table (id,description,unitprice) but only some departments may see the prices.
ReplyDeleteWe created a view on DB.MASTER_PART for each department.
If DEPNAME can see prices the view is:
select id,descriprtion,unitprice from DB.MASTER_PART
otherwise is:
select id,description from DB.MASTER_PART
Such view is named like DEPNAME.MASTER_PART and has select privilege for user DEPNAME only.
User logs in with the DEPNAME and run the query select * from MASTER_PART from Excel.
We tought was a good use of views on base table.
Yes, using views for security purposes is a very good use...
ReplyDelete