DB2® Access views

• DB2® Access view (DAV) - a shared resource that lets you define a DB2® view of Notes® data.
• Query view - another type of NSF view, this view is populated by using the SQL language queries. If you want to create a query view based on data in a Notes® database that resides in DB2®, you must first have defined and populated a DAV.

This documentation refers to the following products: HCL Domino®, HCL Domino® Designer, HCLNotes®, HCL iNotes®, and HCL Traveler.

A Notes® database typically includes many different types of notes. When you enable a Domino® server for DB2®, Domino® stores its Notes® data in DB2®. A DB2® database consists of multiple tables that are composed of columns and rows. Data in DB2® databases is accessed and manipulated through Structured Query Language (SQL) statements.

When a Notes® database is enabled for DB2®, Domino® creates a DB2® database schema for it, as well as a set of tables in that schema to hold the Notes® database data. The schema name is based on the NSF file name. Any DAVs you create will also be located in this schema.

Including all of the notes in an NSF in a single DB2® table would not be effective for SQL access, because a set of notes does not typically have a uniform set of fields (or columns, in a DB2® database) and therefore would probably not be able to be grouped in ways that make sense for SQL queries. To make SQL access useful and manageable, you must define DB2® views that specify the columns you want to access via SQL, and the notes that belong in this view (which constitute the rows of the view).

You define and manage these DB2® views of your data using a design element called the DB2® Access view (DAV). The DAV is a shared resource that enables Notes® designers to create views of data in DB2® enabled Notes® databases. While the DAV actually exists in DB2®, it is accessible by both Domino® and non-Domino applications.

You do the following to define a DAV:

• Define the columns in the DB2® view. You can do this manually by inserting fields in the DAV definition and setting the field attributes (column name, DB2® data type, matching Notes® data type, and so on), or you can choose fields from existing forms and subforms. In this case, Designer will create a suitable column definition in DB2®, which you can then modify to suit your needs.
  Note: With release 8, you can not include encrypted fields in the DAV. Previously, you could include encrypted fields, but could not update values in those fields.
• Define the row selection criteria. This defines which notes in the NSF should be visible in the DB2® view. This can be notes created from a specific form, notes created from a specified set of forms, or all notes in the NSF.
• Set the DAV properties. These control the DAV behavior; for example, whether notes inserted using SQL perform a "Compute with form" operation to generate computed fields on the note.

For example, suppose you have an NSF that contains mailing list information for several different groups, and you want to consolidate information for three of those mailing lists: PTA, Book group, and Playground fund raiser. These mailing lists (forms) all contain the same basic information -- name, address, city, zip -- as well as some other information specific to each activity.

When you create a DAV for this NSF data, you select the four address fields from the 'PTA' form to define the columns in DB2®. (As these fields are the same on all forms, it does not matter which form you use to create the column definitions.)

Next, you select which notes you want to appear in the DB2® view by selecting all three of these forms.

After the DAV is successfully created, you will be able to query the consolidated mailing list in DB2®. This DB2® view would contain the four specified columns for all the notes created with the selected forms. Other fields on the selected notes would not be visible in DB2® because they are not part of the view column specification. For example, phone numbers would not be visible in DB2® because the phone number field was not selected as a column definition. Furthermore, notes created with other forms would not appear in the view because they are not part of the views row selection criteria. For example, notes created from the mailing list form 'Holiday cards' would not appear in the DAV.

Once the DAV is created, any subsequent changes made to the Notes® data will immediately be visible in DB2®. Any changes to the notes made using SQL will immediately be visible in Notes® (you may need to refresh the Notes® view to see change in the Notes® client).

Prerequisites for working with Notes® databases that reside in DB2®

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

• You must have successfully DB2® enabled a Domino® server.
• DB2® Access for Domino® should be installed on the same machine as the DB2® server that hosts Notes® data. This might be the same computer as that on which Domino® is installed ("local" configuration), or a different computer ("remote" configuration). DB2® Access for Domino® acts as an extension to DB2® and enforces Notes® database security (such as ACLs and reader lists) for DB2® enabled data. If DB2® Access for Domino® is not installed properly, the DB2® Designer functions will not be available and you will not be able to access DB2® using SQL.
• 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 NSFs on which you will be working should have been migrated to DB2®. Only NSFs that are stored in DB2® can be accessed using SQL. If you have existing NSFs that you would like to migrate to DB2®, you can do so by making a new copy or a new replica to a DB2® enabled Domino® server.
• The only file type stored in DB2® is an NSF. DAVs and QVs are supported only in DB2® enabled databases; for example, templates cannot contain DAVs.