Creating a DAV

Procedure

  1. In Domino® Designer, open the Notes® 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.

    1. 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.
    2. Select fields for inclusion in the DAV by highlighting a field source. The fields appear in the list box.
    3. Select all the fields that you want to include in the DAV.
    4. 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 correct 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.

Results

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.

  1. Save the view.
  2. (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.
  3. Close the view.
  4. In the DAV work pane, click Create/Update in DB2®.
  5. 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

About this task

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:

About this task

  • 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

About this task

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 twistie icon with exclamation point
  • DB2® Access View - Successfully created and populated: Checkmark icon