I have been doing some research regarding to performance on DB2 on both LUW and Mainframe and I want to throw out a question to other DB2 Experts to clarify few things. The scenario is: View on a table with indexes that are nearly optimal for the view. The view limits the user to see sertain cols and rows by using current sqlid in the where clause like this where colA > case when substr(current sqlid, 1, 3) like 'UP%' then 00 ..... and colB > case when substr(current sqlid, 1, 3) like 'UP%' then AA ..... ..... Now a user or package executes a sql query on that view. The questions are : How does DB2 execute the query ? Does DB2 merge the view and sql query to a Nested Query and then execute it or execute them separatly and then merge the results ? If I had an optimal index on the view I would probably have gained a slight better performance on the overall process ?
