Hello everybody,

I have a question concerning views in DB2.

In our current performance tuning efforts, we noticed in some Application logs, that fetching rows from a result set which involves a view is significantly slow. Also, we have severe problems with memory consumption of our database.

Therefore my question: How are views processed by the database? My impression is that views are handled like normal subselects, but and the SQL is just rewritten by the query optimizer, so that the "view expression" is more or less replaced by the select which implements the view.
(I know that the optimizer does further rewrites of the query, if it improves the performance).
Thus, there should be no overhead like caching of rows for the view, which impacts the usage of buffer pools, package cache or the like.

Is this correct? If so, what might be the reason for the long fetch times?

Greetings, Ruediger.

Google-ing for db2 "view materialization" I got the following useful links (There are many more)

http://publib.boulder.ibm.com/html/a...rzajqmst42.htm

http://www.quest-pipelines.com/pipel...ps03.htm#april

HTH

Sathyaram

Hello Sathyaram,

thanks for the links. Even after browsing the DB2 PDF documentation for several hours, I was rather clueless. Now I have some keywords to start with and some general hints, which will greatly reduce the effort.

Greetings, Ruediger.