DB2® query views

Like other types of Notes® views, query views are design elements that are part of Notes® 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:

  • 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.

For more information on DAVs, see the chapter "Working with DB2® Enabled Notes® Databases."

  • Query views for federated data only do not require the use of DAVs. Therefore, Domino® servers that do not have DB2® Access for 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.

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:

  • Group by
  • Having
  • Join
  • Union
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:

  • 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 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 Domino® 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 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 more information on setting up DB2® Access for 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.