Wednesday, February 17, 2016

The Most Misunderstood Features of DB2 – Part 4: Base Table Views

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…


Base table views are not worth the time and effort because the bring no added value of any type whatsoever. Do not use them.


mc said...

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.

We 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.

Craig S. Mullins said...

Yes, using views for security purposes is a very good use...