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:

• If you want to create a query view based on data in a DB2 enabled Notes database, you must first have defined and populated a DB2 access view (DAV) for that database.
• Query views for federated data only do not require the use of DAVs. Therefore, IBM® Lotus® Domino(TM) servers that do not have DB2 Access for Lotus Domino installed can still be used to create query views for federated data.
• If the query is based on federated, or non-Domino data, you cannot perform a Note Open of the Notes view, as the view data does not correspond to data in a note.
• If the query view references Domino data through a DB2 Access View and selects the noteID from the DAV, you can open the note.
• A query can be composed dynamically in the Notes client application by using the @prompt function.
• A query can be composed dynamically for Web applications by using the @UrlQueryString function.

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:

• Group by
• Having
• Join
• Union

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:

• The DB2 Access server should be installed. This functionality enforces Domino database security (such as ACLs and reader lists) for DB2 enabled Notes data. If DB2 Access for Lotus Domino is not installed properly, some DB2 Designer functions will not be available.
• In order to run any query views, Notes data or federated data, you need a DB2 OS account name in addition to your Notes user ID. These IDs must be linked in the Domino Directory via a DB2 account name in your Person document (Administration tab). This maps your Notes ID to a DAV or QV. Have the Domino Administrator use the "Set DB2 user name" tool in the IBM® Lotus® Domino(TM) Administrator Client to set this field.
• The DB2 enabled Notes databases on which you will be working should be located on a DB2 Access for Lotus Domino server.
• You should have Read access to the Domino catalog.
• To use Query Views with DB2 Federated Objects a new DB2 setting needs to be applied. Stop DB2, and use the following path and command at the DB2 command prompt, and then restart DB2:

  db2set -g DB2_ALLOW_SETAUTH_WITH_REMOTECONNECT=1

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

Creating a query view
Managing query views
DB2 Access views
Designing views

Glossary