DB2 ACCESS VIEWS


Creating a DAV
1. In IBM® Lotus® Domino(TM) Designer, open the IBM® Lotus® Notes® database that resides in DB2 for which you want to create a DAV.

2. Select Create - Design - DB2 Access View.

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


4. Set the properties for each field by double-clicking on the field and completing the Access View Entry dialog box:
FieldAction
Field nameEnter 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 typeIndicate 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 typeIndicate 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 fieldCreates this column as a DB2 index field, which keys the database record for rapid retrieval
Allow truncation of Notes dataSpecifies 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 delimiterIf 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.


5. Click Design - DB2 Access Views to specify the properties for the DAV itself. In the DB2 Access View dialog, complete the following:
FieldAction
NameEnter 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).
CommentEnter information about the DAV
Select the form(s) associated with this DB2 Access viewSpecify 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.
6. Specify options for adding data to the DAV from DB2:
FieldAction
Compute with form on DB2 insert or updateEnable 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.
7. Specify DAV options on the Advanced tab:
FieldAction
Normalize to GMT for time zone conversionsSelect 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.

8. Save the view.

9. (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.

10. Close the view.

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

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

Notes:


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 nameDescriptionTypeStored
#ADDEDTOFILEDate when document was added to this databaseTimestampY
#CREATEDSame as @CreatedTimestampY
#DATABASENSF nameVarcharN
#DBPATHFull path and NSF nameVarcharN
#MODIFIEDSame as @ModifiedTimestampY
#OIDNotes OID for each noteVarcharY
#PUBLICACCESSEnable Public Access Notes behaviorVarchar bitN
#REFFrom $RefVarchar bitY
#REPLICAIDReplicaIDVarchar bitN
#RESPINFOParent note id, followed by a child count, followed by a list of child note idsLong VarcharY
#SEQUENCENUMBEROID.SequenceIntegerY
#SERVERServer CNVarcharN
#UNIDNotes UNID for each fieldVarcharY
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.

#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:


Viewing the status of your DAV

The following icons show the status of the DAV you are creating:
New DAV iconNew DAV, not yet created or populated in DB2
New DAV that has been populated iconNew DAV that has been created in DB2 (but not yet populated)
DAV has been successfully createdDAV has been successfully created and populated in DB2
Error in DAVThere is an error in the DAV

See Also