Lotus Domino Designer 8.5 Help

DB2 ACCESS VIEWS

Creating a DAV
1. In IBMR LotusR Domino(TM) Designer, open the IBMR LotusR NotesR database that resides in DB2 for which you want to create a DAV.

2. In the Applications Navigator, select Data-DB2 Access Views.

3. Double-click the DB2 Access Views design element.

4. Click New DB2 Access views. The New DB2 Access View panel appears

5. Provide a name for the view.

6. Select the form and application to be associated with the DB2 Access view and click OK. The DB2 Access View InfoBox appears.

7. Specify the fields (columns) to use in the DAV. Choose one of these options:

Choose Field

-- to chose from existing design elements.

In the Type field, select the design element to be used as a field source, or choose All. All instances of the chosen field selection source now appear in the left-hand list box.

Note

New with Release 8, the field types include Special # Fields. This category includes previously available metadata that was selected on the advanced DAV properties tab, as well as new Lotus Notes and DB2 metadata.

Select fields for inclusion in the DAV by highlighting a field source. The fields appear in the right-hand list box.
Select all the fields that you want to include in the DAV.
Repeat for each field selection source, as required.

Insert Field

-- To add a blank field to the DAV in which you can type an existing field name or the name of one that will be added to a form associated with the DAV.

8. Set the properties for each field by double-clicking on the field and completing the Access View Entry dialog box:

Field	Action
Field name	Enter the field name if adding a new field. This is the matching field name for the DB2 column in the DAV, so it must be the actual name of a field that appears in the note (otherwise no matching field would be found and the column in the DB2 view would be blank) Note If you used the Choose Field method of specifying the fields for the DAV, you will be able to edit the field names in the properties, but if you change the field name so that it no longer matches a field name in the note, the corresponding column in the DB2 will be blank.
Notes type	Indicate the Notes data type for this field Note Formula, rich text, and rich text light Notes data types are not supported for use in DAVs.
DB2 type	Indicate the DB2 data type for this field. Notes will indicate a default value associated with the Notes type you choose. Note If you have an integer defined in the DAV, and an insert from DB2 supplies a real number (with a decimal), the insert will succeed and the decimal value will be truncated.
DB2 index field	Creates this column as a DB2 index field, which keys the database record for rapid retrieval
Allow truncation of Notes data	Specifies that the DB2 view can "clip" the notes data (only show DB2 column length characters)
Store multiple values Note: this option is enabled only for multi-value fields.	If a Notes field has multiple values, you can select to use: first value in the field only delimited values. For multi-value fields, all of the data values can appear in the view as delimited text.
DB2 multi-value delimiter	If you have chosen to use delimited values in this field, indicate the delimiter used. This delimiter is the one that will be used to separate multiple values in the DB2 column and the delimiter that will be used to parse DB2 INSERT or UPDATES into separate values. It is not related to the delimiter specified in the form design that is used by the Notes client. The default is a semicolon.
DB2 column length	This is used to define the column length in DB2 (essentially field length in Notes). This is only required for columns mapped to the DB2 Varchar data type (all other types are fixed length). The default for varchar is 100.

Note

You can change the order of the fields in the view by dragging and dropping them. For example, you may want to position frequently accessed fields at the beginning of the view.

9. Click Design - DB2 Access Views to specify the properties for the DAV itself. In the DB2 Access View dialog, complete the following:

Field	Action
Name	Enter a name for the DAV. This is the actual name of the DB2 view and must be a valid DB2 view name. If you enter spaces in the name, they will be converted to underscores in DB2 (e.g. zip code becomes zip_code).
Comment	Enter information about the DAV
Select the form(s) associated with this DB2 Access view	Specify the row selection criteria by doing one of the following: Select all forms to associate all forms in the database with the DAV. All data notes in the NSF will have a row in the access view. Select individual forms from the list box to associate with the DAV. This list is used by DB2 to determine the data with which to populate the DAV. Only data notes with a FORM item matching one of the selected values will be in the view.

10. Specify options for adding data to the DAV from DB2:

Field	Action
Compute with form on DB2 insert or update	Enable this option if the selected form(s) contain computed fields and you want the formulas to be computed when the note is created or updated using SQL.
Default form to use for DB2 inserts	DB2 users can perform inserts, updates, and deletes (given the right permissions) against data in the DAV. However, you can only perform these operations against one form associated in the DAV definition, even if multiple forms are selected. Specify the form that will be used for DB2 inserts, updates, and deletes.

11. Specify DAV options on the Advanced tab:

Field	Action
Normalize to GMT for time zone conversions	Select this option to standardize all dates and times in the DB2 view to GMT. This is especially useful for distributed DB2 applications that are accessed by users in different time zones. Note This setting only applies to columns mapped to Date/Time fields in the document. Special fields (e.g. #CREATED) are always written to DB2 in GMT.

Note With Release 8, the options to select #UNID, #OID, and modified time are no longer advanced options. They are selected as Special # Fields when other fields are selected.

12. Save the view.

13. (Optional - recommended) Click Validate. Validating the DAV verifies that the definition meets the minimum requirements to build a valid DB2 view and gives you a quick way to check the validity of the Access View definition while you are still in the DAV designer.

14. Close the view.

15. In the DAV work pane, click Create/Update in DB2.

16. Click Populate in DB2. This populates the view with the field data.

Notes:

The Populate task is an asynchronous server task. No notification of task progress is provided. Click Refresh List to check on the task status.

Populating a DAV against a large data set can be time consuming and contribute to server load. If you have created a DAV for a large database, consider populating the view during off hours.

Populating the view is a one-time event. After the view has been populated, it is kept in sync with normal Note updates and DB2 updates.

Special Fields for the DAV

New with Release 8, a wide range of metadata about the Notes database previously only accessible via Notes formula language are available to the DAV. Metadata about the DAV itself is also available.

Field name Description Type Stored

#ADDEDTOFILE Date when document was added to this database Timestamp Y

#CREATED Same as @Created Timestamp Y

#DATABASE NSF name Varchar N

#DBPATH Full path and NSF name Varchar N

#MODIFIED Same as @Modified Timestamp Y

#OID Notes OID for each note Varchar Y

#PUBLICACCESS Enable Public Access Notes behavior Varchar bit N

#REF From $Ref Varchar bit Y

#REPLICAID ReplicaID Varchar bit N

#RESPINFO Parent note id, followed by a child count, followed by a list of child note ids Long Varchar Y

#SEQUENCENUMBER OID.Sequence Integer Y

#SERVER Server CN Varchar N

#UNID Notes UNID for each field Varchar Y

Special fields which apply to individual notes have their values stored in the DAV table. Special fields which apply to the database or DAV as a whole are not stored in the DAV table, and instead have their values encoded in the view definition. Unstored columns can be queried from the view, and included as part of a SELECT in the Query View's SQL formula. Unstored columns are not available when the Access Table is accessed directly.

Some of the special fields perform additional modifications to the underlying notes.

SELECT #NOTEID in the Query View's SQL formula to allow document to be opened by double clicking the view entry.

Specify the #OID special field in the DAV definition and SELECT #OID in the Query View's SQL formula to enable the following capabilities.

Enable document link for query view documents.
Enable copied as a table.
Enable "twisties" (along with #REF).

#REF in the DAV formula creates a $REF item on the note.

#RESPINFO in the DAV formula sets Parent and response note information.

Specify the #PUBLICACCESS special field in the DAV definition and SELECT #PUBLICACCESS in the Query View's SQL formula to enable Notes public access behavior in query views.

Timestamps are normalized to GMT.

Version compatibility notes:

In 7.0, the #UNID column is written as a 16 bit binary value. In 8.0, it is written as a 32 bit string which can be used directly in functions such as getDocumentByUNID().
In 7.0, the advanced property for #MODIFIED was an internal value which was not synonymous with the @Modified function. In 8.0, this internal value has been renamed #DB2MODIFIED and it can no longer be exposed in the Access View. The new #MODIFIED special field is synonymous with the @Modified function.
Existing DAV definitions will be automatically converted to the 8.0 style (version 3.1) when they are opened in the 8.0 designer. The old advanced properties for #OID and #UNID will be converted to the new special field format.
Existing DB2 Access Views will continue to work as they are. They will not be updated until the DAV is deleted in DB2 and recreated.
With release 8.0, the limit on the number of DAV columns has been removed. Domino uses an additional bufferpool with a 32K pagesize, called DOMINO_TMP32, and a temporary tablespace called DOMINO_STMP32, where DOMINO is the name of the server's main schema.

Viewing the status of your DAV

The following icons show the status of the DAV you are creating:

DB2 Access View - New View : View icon with a star
DB2 Access View - Created and Empty: Plain view icon
DB2 Access View - Read-only: Pencil icon with cross
DB2 Access View - Warning: Yellow triangle icon with exclamation point
DB2 Access View - Successfully created and populated: Checkmark icon

See Also

Managing DAVs

DB2 Access views

Glossary

Feedback on Help or Product Usability?

Help on Help

All Help Contents

Glossary

Field name	Description	Type	Stored
#ADDEDTOFILE	Date when document was added to this database	Timestamp	Y
#CREATED	Same as @Created	Timestamp	Y
#DATABASE	NSF name	Varchar	N
#DBPATH	Full path and NSF name	Varchar	N
#MODIFIED	Same as @Modified	Timestamp	Y
#OID	Notes OID for each note	Varchar	Y
#PUBLICACCESS	Enable Public Access Notes behavior	Varchar bit	N
#REF	From $Ref	Varchar bit	Y
#REPLICAID	ReplicaID	Varchar bit	N
#RESPINFO	Parent note id, followed by a child count, followed by a list of child note ids	Long Varchar	Y
#SEQUENCENUMBER	OID.Sequence	Integer	Y
#SERVER	Server CN	Varchar	N
#UNID	Notes UNID for each field	Varchar	Y