APPLICATION DESIGN


DB2 query views
Like other types of Notes views, query views are design elements that are part of IBMR LotusR NotesR applications. However, a query view uses an SQL query to populate its data, instead of using a view formula that selects notes from a Notes database.

Query views are dynamic. The query runs as a result of a view open or view rebuild event, and is recalculated each time the view is opened or refreshed. You can also re-execute a query view with different SQL queries to quickly access specific notes. This is in contrast to regular Notes views, which need to be rebuilt by using the Indexer tasks UPDATE and UPDALL.

Because they are not persistent, query views do not occupy space in a Notes database.

Query views and federated data

Query views can be built to view any data that is visible to a DB2 database, either because the data is contained in that database or because it is available to that database through DB2 federation. Federated data is any data stored in a database other than a Notes database. For example, any data in existing DB2 tables or databases is considered federated data. You can choose to create query views that show Notes data only, federated data only, or a combination of Notes and federated data.

Once a query view has been created, you can open individual records in the view to edit Notes data contained in that record. However, you cannot open a record of federated data from the query view, or make changes to one - you can only view federated data in the query view itself.

You can also order or sort a query view by specifying the sort order in the SQL query. However, the ORDER BY clause only affects the view ordering if a #noteID column is not returned by the query. Otherwise, by default, the view is sorted by Note ID. In any case, a Notes column sort overrides a DB2 sort.

Note If a #noteID column used in the query SELECT statement is part of a table outside of the schema associated with your DB2 enabled Notes database, it is considered DB2 data, not Notes data, as it does not belong to the current DB2 enabled Notes database. Therefore, you may not be able to open the resulting note, or you may open a random note or a design document instead.

You need to be aware of the following when creating query views:


Using complex SQL queries in query views

You can use SQL to create complex queries that incorporate data from multiple DB2 tables and views in one Notes query view. For example, a query view can join data from multiple DB2 tables/views; therefore, an application designer can join data from two DB2 enabled Notes databases indirectly by joining two separate DAVs.

Specifically, you can create a SELECT statement with the following clauses:


Caution Query view functionality is designed so that you cannot build an SQL statement that does not produce a result set. This is a security measure against inadvertent record deletion or change.

Prerequisites for working with Notes databases that reside in DB2

In order to work with DB2 enabled Notes databases, your Domino administrator should have set up your environment as follows:


For more information on setting up DB2 Access for Lotus Domino see the Domino Administrator help.

For information on configuring DB2 federated objects, see the Domino Administrator help topic "Using federated data with query view," or go to the DB2 Information Center http://publib.boulder.ibm.com/infocenter/db2help/index.jsp.

See Also