DB2 Access Views
DB2 Access Views provide a means of pushing out a set of field data from Domino to a DB2 view. After the data arrives in the DB2 view, both Domino and DB2 clients can work with it in a manner consistent with Domino security. DB2 clients can also leverage their tool sets to take advantage of working with the data directly in relational format. The DB2 Access Server governs security. (Remember that it must be installed prior to creating DB2 Access Views.)
Creating the DB2 Access View Definition
DB2 Access Views are created via Domino Designer 7. To create one, select Shared Resources | DB2 Access Views, and click New DB2 Access View.
This opens the DB2 Access View Designer. You will need to assign a name to your DB2 Access View. This name becomes the name of the DB2 view when it is later created there.
The DB2 Access View's infobox provides several options worth reviewing. There is an option to select which forms' data to include in the DB2 Access View. You can select all forms, or select them individually. There is also an option, Compute with form on DB2 insert or update, which can be applied to inserts or updates that occur to the data via DB2, as well as an option to specify a Default form to use for DB2 inserts in cases where the form field is not set. Setting a default form value ensures that the data displays using the correct form when viewed via a Notes client.
The DB2 Access View Designer is visually similar to the Outline Designer. You create entries using buttons located at the top of the Designer. Use the Choose Field button to select a field from the Notes database. Use the Insert Field button to manually define data for the DB2 View that is created as a result of this process.
Selecting Choose Field displays a dialog box that allows you to select fields from a list defined for the current database. Fields can be selected from throughout the database, or the list can be filtered to enumerate field definitions based on form, subform, or shared field definitions. The fields selected in this dialog box define the set of data to be pushed into the corresponding DB2 view.
After you have specified the set of field data to be published to the DB2 view, click OK. This adds the set of selected fields to the DB2 Access View Outline.
It may seem tempting to export all of your data using a single DB2 Access View. This puts most data at your fingertips but may make queries against your data more complicated. Defining concise data sets for your DB2 Access View simplifies querying it for data, and allows for greater flexibility when joining it with other data sets.
When you are satisfied with the fields selected in your outline, click Save, and close the DB2 Access View Designer. This returns you to the list of DB2 Access View design elements.
The next step is to propagate your DB2 Access View definition from Domino Designer to the DB2 server. To do this, close the DB2 Access View Designer so that you are returned to the DB2 Access View design view. Select the DB2 Access View you created, and click the Create/Update in DB2 action button.
If this action is successful, you should see a dialog box similar to the following, indicating that the Access Definition was created:
Populating DB2 with Domino Data
So far, we have only created the DB2 Access View definition. The next step is to populate DB2 with Domino data. To do so, select the DB2 Access View in the design view, and click the Populate in DB2 button.
Clicking this button initiates a request for the Domino server to populate the DB2 view with data. You should see a dialog box similar to the following:
This is an asynchronous process; large data sets could take some time to populate. If the DB2 Access Server is correctly configured, the Domino server console should send messages similar to those appearing in the next illustration, indicating the status of the operation.
The DB2 Access View is fully populated after the DAVPOP task confirms its shutdown.
Mapping DB2 User Names to Domino
The DB2 Access View has now been populated with data. If you intend to access Domino data in the DB2 Access View using a DB2 client, you should map Domino to DB2 names using the Domino Administrator's DB2 tools. If you do not map DB2 user names to Domino Person documents, the DB2 Access Server will request access to Domino data for DB2 users as Anonymous.
To map a DB2 user to a Domino Person document, open the Domino 7 Administrator, and click the People & Groups tool. Select the Person document belonging to the ID that you want to use to access Domino data in DB2. If you plan to populate the DB2 user name with the Domino shortname field, verify that this field has been populated prior to launching the Set DB2 User Name... tool. You can edit the selected Person document directly within the Domino Administrator if necessary. When ready, click the Set DB2 User Name… tool.
This launches the Set DB2 User Name dialog:
If you plan to populate the DB2 user name field using the Domino shortname field, check Copy from shortname field, if available. Also check Make resulting name uppercase, if needed.
Click OK when you have populated the requested input fields. This instructs the Set DB2 User Name tool to populate the selected Person document with a DB2 ID that maps the user to a Domino name. This allows the DB2 Access Server to enforce Domino security for users accessing Domino data via a DB2 client. If this operation is successful, you should see a message similar to the following:
DB2 user names must be unique. If you have a large directory, you may need to run the Validate DB2 User Names… tool from the Domino Administrator. When running this tool, the Domino Administrator prompts the user to select the scope of validation.
Select The currently selected Domino Directory option if your DB2 use of Domino data is limited to the primary Domino directory. Select All configured Domino Directories if DB2 client use of Domino data occurs via users in secondary Domino directories.
You can verify the results of this tool by viewing the Person document. Open it to the Administration tab, and verify that the DB2 account name field was updated as requested.
In the preceding example, the DB2 user DB2ADMIN is mapped to the Domino user Domino Administrator/acme when accessing data from a DB2 client. To test accessing your Domino data from DB2, open the DOMINO database in DB2. Select Views, and locate the name of the DB2 Access View. If spaces occur in the name of the DB2 Access View, they are replaced with underscores. In the following illustration, a DB2 user selects the CDS DB2 Access View via the Control Center.
Double-click View to open it. You should see your Domino data as hosted by DB2.
Congratulations! You have created a DB2 Access View and can now access its data from a DB2 client. This allows you to aggregate the DB2 tool set with your Domino data, providing the power and flexibility of SQL along with it. The last design artifact we will look at is the Query View.
Query Views
Query Views allow you to leverage the data stored in DB2 Access Views within a Domino view using SQL. Query Views require a DB2 Access View to function.
Query Views are created in the same manner as standard Domino views. Open Domino Designer, and select Views. Then click the Create View action. When the DB2 Access Server is enabled, a new radio button option appears for Selection conditions, labeled By SQL Query. Select this radio button to create a Query View. Your SQL must also be enclosed in quotes, or Domino Designer will process your SQL as an @Formula:
When you click OK, the View Designer appears. Query Views contain an additional object labeled SQL Query. Select this to update the SQL your view consists of. Your view can inherit a standard Domino view selection formula if your view was copied from another. Verify that the view selection formula does not contain additional selection criteria that alter the results of your SQL query. If necessary, remove the contents of the View Selection object so that only the SQL query governs the result set. The SQL query object is highlighted in the following:
After you have finished defining your view, Save and close it. Query Views cannot be previewed in Notes. To verify the result set, open the Query View in the Notes client, where you should see your data. Notice that Query Views identify themselves using a different icon from standard Domino views.
Query Views do not store their data in Domino in the same manner that a Domino view caches its data. Query Views are Domino views of data stored in DB2 that are constructed dynamically. Query Views also provide the capability to join data from multiple data sets stored in DB2. This lets you join data from multiple Domino databases, if those databases have all exported their data to a DB2 Access View.