CODEWITHSUNDEEP
db2ibm-midrangerpgle
AS400 User
AS400 User

Reputation: 187

Using Views and CTE on DB2/AS400

a Generic question..

I have an employee Table(EMPMAST) which has the New as well old Employee data. There is a flag called Current? which is 'Y' if he/she is a current employee. Now I have to select records in my SQLRPGLE with only the current records and also some other criteria's(For Example EMPNAME = 'SAM') .What is the best way to deal with it. (in terms of performance and system usage)

  1. To create a View over the EMPMAST with Current? = 'Y'. Then use it in the program with other conditions.
  2. Use a CTE(With AS) in the Program which would have the Condition Current = 'Y' and use it.
  3. use the table directly without CTE and View
  4. any other option

Upvotes: 0

Views: 759

Answers (2)

jmarkmurphy
jmarkmurphy

Reputation: 11493

A CTE vs. a View are two different things. A View would be appropriate for a query that is going to be used in multiple locations, A CTE is only available in the query in which it is defined. I usually don't use the CTE except to replace a complex subquery. In your case the condition is simple enough to be contained in the where clause so I don't see the need to introduce additional complexity.

Some folks will tell you not to query the table directly in the program, but to always use a view. That way you add an extra layer of insulation between the program and the database, and you can still define record structures with ExtName, and not have to worry about changes to the table unless they affect the view itself. In this case you would likely have a dedicated view for each program that uses the table.

I tend to just use a hybrid of these techniques. I query tables, CTE's, or Views depending on the situation, and define my record structures explicitly in the program. I prefer to just query the table, but if I have some complex query logic which is unique to the program, I will use a CTE. I do have a few Views, but these are limited to those queries that happen in multiple programs where I want to ensure the same logic is applied consistently.

Upvotes: 1

Paul Vernon
Paul Vernon

Reputation: 3901

Options 1, 2 and 3 would all perform the same. They would likely all have the same optimized query and access plan.

Upvotes: 1

Related Questions