The IBM Lotus Domino and IBM DB2 integration features were originally released in a trial form with Lotus Notes® and Domino 7 and are now fully supported in Lotus Notes and Domino 8.
These features give you the opportunity to easily create applications that dynamically integrate native DB2 data (or data held in repositories that are accessible from DB2) with data by Lotus Domino. Moreover, this integrated data can then be accessible through either (or both) Lotus Domino views or DB2 views depending on the application requirements. These features create many interesting possibilities for you in organizations that already use DB2 or other relational databases that can be accessed from DB2, for example, SAP or PeopleSoft. Web-based access to this integrated data can be made available either through HTTP access to the Lotus Domino applications or through JDBC access to the DB2 views.
In addition, Lotus Notes and Domino 8 includes a license to use DB2 as a data store for Lotus Domino data. This license allows you, at no additional software cost, to take advantage of some potentially useful enhancements for applications where the data is solely located in a Lotus Domino data store. This document focuses on the potential advantages of the Lotus Domino and DB2 integration features for pure Lotus Domino applications where there is no requirement to integrate with existing external DB2 data.
It is important to understand that the Lotus Domino and DB2 integration features might not be appropriate for all Lotus Domino applications. As a rule of thumb, Lotus Domino applications that have the following characteristics can be good candidates:
- They have no requirement for full replication of application to a Lotus Notes client.
- They have no (or limited) need to display the entire database in a view.
- They have many similar views that categorize the same information in different ways.
- Their users reference small numbers of documents at a time to do 80-90 percent of their work.
- They would benefit from being able to combine data from multiple Lotus Notes databases.
- They would benefit from being able to use SQL's reporting capabilities.
It is important to note, though, that simply taking an existing Lotus Domino application with these characteristics and converting it to use the DB2 data store does not bring any benefit to the application. The benefit comes only from redesigning the application to make use of the new features available to DB2-enabled applications. In fact, in many cases, it would be best not to take an existing design and move it into DB2, but instead to design and develop a useful DB2-enabled application and then populate it with the required data.
This article uses a sample application to describe four possible usage scenarios for the Lotus Domino and DB2 integration features in pure Lotus Domino applications, explaining when and how they could bring benefit to an application in terms of improved performance or enhanced functionality. Some knowledge of Lotus Notes application design is helpful when you read this article, but the design and coding of the sample application have been kept as simple as possible so that you can easily understand the principles even if you do not have extensive application development experience.
Note that the features described in the following scenarios are available only when the application is being hosted on a DB2-enabled Lotus Domino server. If the application is replicated to a non-DB2-enabled Lotus Domino server, or to a Lotus Notes client, the features described here are not available.
Scenario one: Implementing data independence in a Lotus Notes application
Lotus Notes and Domino is not a relational database application platform. There are occasions, though, when it would be very helpful to add some relational database capabilities to a Lotus Notes application to give more flexibility in sorting and categorizing the data within the application.
For example, imagine that we have an application in which we record the details of events that our company hosts. We want to keep information on the event (title, region in which it is being hosted, date, cost), the topics that are presented, and the participants who attended the event. Each event covers several topics and has several attendees. A topic can be covered in the agenda at multiple events, and a participant can attend multiple events. In addition, each of the participants has an associated region to which he or she belongs. Diagrammatically, the relationships look like figure 1.
Figure 1. Entity relationship diagram
We could record this scenario in a single form using multi-value fields for the topic and participant entries, with associated lookups for the participant regions as shown in figure 2.
Figure 2. Event document with multi-value fields
Because all the data for an event is stored in a single document, it is more difficult to define and manage specific relationships between the different data items associated with the same event. For example, in the document shown in figure 2, we might want Jasmine to be associated with each of the topics that were covered at the event, but with only the single participant region to which she belongs.
When the data is stored in separate, independent documents, it is not easy, using standard Lotus Domino application techniques, to combine data from these documents in a single row in a view.
Using the Lotus Domino and DB2 integration features we can implement data independence by storing the data in separate documents when appropriate, but still have the ability to combine the data in a single row in a view when required.
For example, we can create topic-related data specific to each event with a separate Agenda Item form and participant-related data specific to each event with a separate Attendee form. To allow the topic and participant information to be linked back to the event, we can use a key that is unique to each event and store this data in each Agenda Item and Attendee document.
Diagrammatically, the relationships now look like figure 3.
Figure 3. Amended entity relationship diagram
Event forms can still be filled out to display topics and attendees, but this time the information for each agenda item and each attendee is stored in separate documents and displayed in the Events form using embedded views. In the example shown in figure 4, note that we now have an Event Key field. When the user uses the button to add an Agenda Item, a new document is created where this event key is inherited. The embedded view contains all the agenda item records categorized by event key but is configured to show a single category – that is, the category that matches the event key of the current document.
Figure 4. Event document with embedded view
To create a view that can combine data from multiple documents in the same row of a view, we can use a combination of DB2 access views (to expose the data in DB2) and query views (to use SQL statements to retrieve the data into Lotus Notes views). For more information on creating DB2 access views and SQL query views, refer to the Lotus Domino 8 Designer Help.
In the first example, a DB2 access view is created for each of three forms: Event, AgendaItem, and Attendee. Note the check marks next to certain fields in figure 5. These are fields that we are likely to use in our query view selection criteria. The checkmarks indicate that these fields are indexed in DB2, which makes data retrieval more efficient.
Figure 5. DB2 access views in Lotus Domino Designer
This selection results in three views that can be viewed through the DB2 control center as shown in figure 6.
Figure 6. DB2 access views viewed through the DB2 control center
Note that both DB2 tables (which have a _T and an _X suffix in the table name) and DB2 views are associated with DB2 access views. Any access to data exposed through the DB2 access view should always be done through the DB2 views and not the DB2 tables because the DB2 views that contain the security triggers validate the access with the Lotus Domino server.
An SQL query view is then created with an SQL SELECT statement as shown in the code shown in listing 1.
Listing 1. Creating an SQL query view
MySchema := @DB2Schema(@DbName); "SELECT ATTENDEES.ATTENDEENAME, ATTENDEES.REGION, AGENDA.TOPICNAME, EVENTS.EVENTDATE, EVENTS.EVENTNAME, EVENTS.#NOTEID FROM " + MySchema + ".ATTENDEES AS ATTENDEES, " + MySchema + ".EVENTS AS EVENTS, " + MySchema + ".AGENDAITEMS AS AGENDA " + "WHERE ATTENDEES.EVENTKEY = AGENDA.EVENTKEY AND AGENDA.EVENTKEY = EVENTS.EVENTKEY"
Note that @DB2Schema is used to calculate the schema name that is being used for the views so that it doesn't have to be hard-coded into the query. The schema is unique for each Lotus Notes database and ensures that DB2 access views from different databases can be distinguished from each other even if they have the same name.
Note how the fields being selected come from the three separate document types (Attendee Name from the Attendee documents, Topic Name from the Agenda Item documents, and Event Date and Event Name from the Events documents).
The inclusion of a #NOTEID in the SELECT statement is required if you want to be able to open documents from the query view. In the SELECT statement in listing 1, the #NOTEID field from the Events documents is included; this inclusion means that when a row in the view is double-clicked, the default option is to open the Event document that links the attendee and the topic.
As described previously, the schema name is important to ensure that we have identified the correct DB2 view. To make the SQL statement more readable, we can use AS to assign aliases for selected views. Thus, instead of having to use <MySchema>.ATTENDEES.ATTENDEENAME every time we want to refer to that field, we can replace <MySchema>.ATTENDEES with an alias of ATTENDEES.
Finally, our SQL statement uses WHERE to join the three tables based on matching EventKey fields. This coding is how we can create a single row in the view that contains fields from multiple documents. Note that EventKey was one of the fields to which we added a DB2 index. This addition allows DB2 to locate and return the records with matching EventKey fields very efficiently.
We can use the SQL assist tool in the DB2 control center to help construct the SQL statement that we need and to check that we are returning the expected results. Note that, as shown in figure 7, our query takes the documents that are associated with one event (one event document, four attendee documents, three topic documents) and presents all the possible document combinations as separate rows.
Figure 7. SQL query results as displayed in the DB2 control center
The DB2 query result shown in figure 7 can be reproduced in a Lotus Notes view by adding columns with these names. In addition, the Lotus Notes view can be sorted, categorized, and formatted using the standard Lotus Notes view and column properties as shown in figure 8.
Figure 8. Lotus Notes view sorted by region and attendee
The independence of the data gives us a lot more flexibility in sorting and categorizing. For example, it might be helpful to be able to send out invitations to the local participants when an event is being hosted in their region. We can add a Participant DB2 access view to our collection as shown in figure 9.
Figure 9. Participant DB2 access view
We can then write an SQL query like the one shown in listing 2 to join events with potential attendees.
Listing 2. Creating an SQL query to join events with potential attendees
MySchema := @DB2Schema(@DbName); "SELECT " + "EVENTS.EVENTNAME || ' - ' || CHAR(EVENTS.EVENTDATE) AS EVENT, " + "PARTICIPANT.#NOTEID, PARTICIPANT.EMPLOYEE FROM " + MySchema + ".EVENTS AS EVENTS, " + MySchema + ".PARTICIPANT AS PARTICIPANT " + "WHERE EVENTS.REGION = PARTICIPANT.DEPARTMENT"
We want to be able to distinguish between separate occurrences of repeated events, so this query combines the EventName and EventDate field using the concatenation function. Note that, because EventDate is a date field, we have to convert it to a text field before we can concatenate it. To display this concatenated value in the view, we have to give it an alias, EVENT. This alias is what we use in our view column formula to group the potential invitees.
This time, we included the #NOTEID from the participant DB2 access view, which allows us to open the participant records by double-clicking a row in the view.
The resulting view looks like figure 10.
Figure 10. Lotus Notes query view showing events with potential invitees
We could have selected the EventName and EventDate separately in the query and used a Lotus Notes column formula like EventName + “-” + EventDate. It is more efficient, though, to have DB2 do the concatenation and simply display the result in the Lotus Notes view.
If we are storing more profile-based information about our participants, for example topics in which they were interested, we could use a more complex WHERE clause that compares participants' interests with the topics on the planned agenda and lists only invited participants where these interests were a match.
Scenario two: Use dynamic query views to improve application efficiency
As we add more and more data to our application, our Event form with embedded views can take longer and longer to display. To present a single category in the embedded view, the Lotus Notes client first retrieves the whole categorized view before displaying the required category. It is much more efficient if only the records that match the required category are retrieved.
Because our application is DB2-enabled, we can configure our embedded views to use query views with dynamically created SQL queries instead of standard Lotus Notes views.
With standard Lotus Notes views, there is no way to pass any parameters into the view selection formula when the view is retrieved. With query views, though, there are three main ways that the SQL query can be changed at view retrieval time.
First, the SQL query can look up information from other Lotus Notes documents or views (for example, profile documents) or read values from environment variables. In the example that follows, code has been entered into the PostOpen event on our Event form so that, as each document is opened, the embedded views use the event key to determine which topic and which attendee records to retrieve.
The LotusScript® code in listing 3 sets the environment variable EventkeyENV to have the value in the EventKey field.
Listing 3. Setting the environment variable
Sub Postopen(Source As Notesuidocument) Dim session As New NotesSession Call session.SetEnvironmentVar _ ("EventKeyENV",source.FieldGetText("EventKey")) End Sub
The SQL query shown in listing 4 retrieves the environment variable EventKeyENV and uses its value in the WHERE clause of the SQL statement to retrieve only the records with the matching event key.
Listing 4. Retrieving the environment variable
MySchema := @DB2Schema(@DbName); Keyword := @Environment("EventKeyENV"); "SELECT * FROM " + MySchema + ".ATTENDEES AS ATTENDEES WHERE ATTENDEES.EVENTKEY = '" + Keyword + "'"
Because our embedded views now contain only the data that is required, we no longer need to use the Show single category option in the embedded view object properties to achieve the same results.
Note that it was not possible to use the value in the event key field directly on the document within the SQL query because the embedded view does not have any visibility of the contents of the document in which it is being displayed.
The second way in which we can configure a dynamic query view is to prompt the user to enter or select some information when opening the view. The information supplied by the user can then be used to restrict the rows returned to just those in which the user is interested.
For example, we have a query view that categorizes Attendees by Topic as shown in figure 11.
Figure 11. query view categorizing attendees by topic
A user opening this view might be interested only in finding out which attendees have learned about a specific topic. With the current view, all the records are returned and categorized, and then users have to navigate to and browse the Topic category in which they are interested. It is much more efficient if users can select or enter the category of interest before the records are selected.
We can do this by adding an @Prompt function to the SQL query to prompt users for the topic of interest and then add this to the WHERE clause in the SQL statement as shown in the SQL query in listing 5.
Listing 5. Adding an @Prompt function
MySchema := @DB2Schema(@DbName); TopicChoice := @Prompt([OkCancelEdit]; "Topic Name"; "Enter topic name";""); "SELECT AGENDA.TOPICNAME, ATTENDEES.ATTENDEENAME, EVENTS.EVENTDATE, EVENTS.EVENTNAME, EVENTS.#NOTEID FROM " + MySchema + ".ATTENDEES AS ATTENDEES, " + MySchema + ".EVENTS AS EVENTS, " + MySchema + ".AGENDAITEMS AS AGENDA WHERE ATTENDEES.EVENTKEY = AGENDA.EVENTKEY AND AGENDA.EVENTKEY = EVENTS.EVENTKEY AND " + "AGENDA.TOPICNAME = '" + TopicChoice + "'"
When users select this view they are prompted to enter the name of the topic of interest, as shown in figure 12. In this case, the user enters Notes 8 Client in the topic field.
Figure 12. User is prompted to enter a topic name
Only the records associated with this topic, Notes 8 Client, are returned as shown in figure 13.
Figure 13. Lotus Notes view showing only the records associated with the chosen topic
Note that the @Prompt function could use a lookup function to present the user with a list of appropriate topics from which to choose, to ensure that only valid data is entered. To select a different topic name, users can press F5 (or F9) to refresh the view, and the prompt dialog box is displayed again.
Note that, although for simplicity of coding we haven't done so here, in any SQL operation where free-form user input is allowed, it is a best practice to ensure that the input does not contain any DB2 special characters such as quotation marks and semicolons before running the query. This approach ensures that users cannot hack into the query and delete or amend data inappropriately. Although there are DB2 permission settings that should stop some of this behavior, it's a good idea to avoid the problem in the first place.
The third way in which an SQL query can be modified dynamically is through URL value substitution.
We can use the @URLQueryString function to extract information from the current URL to modify the query view.
For example, if we consider a Web version of a view that prompts users for an attendee name before displaying the associated records, we can use the SQL query shown in listing 6 to take the value given to the Attendee parameter in the URL and use this in the record selection.
Listing 6. Using a SQL query with the @URLQueryString function
MySchema := @DB2Schema(@DbName); AttendeeChoice := @UrlQueryString("Attendee"); "SELECT ATTENDEES.ATTENDEENAME, AGENDA.TOPICNAME, EVENTS.EVENTDATE, EVENTS.EVENTNAME, EVENTS.#NOTEID FROM " + MySchema + ".ATTENDEES AS ATTENDEES, " + MySchema + ".EVENTS AS EVENTS, " + MySchema + ".AGENDAITEMS AS AGENDA WHERE ATTENDEES.EVENTKEY = AGENDA.EVENTKEY AND AGENDA.EVENTKEY = EVENTS.EVENTKEY AND ATTENDEES.ATTENDEENAME = '" + AttendeeChoice + "'"
Users entering (or clicking a link that generates) the following URL see the records shown in figure 14:
Figure 14. Web-based query view of topics by attendee
In addition to the examples described previously, there is a potential for additional performance gain associated with using SQL query views over standard Lotus Notes views in other areas.
First, standard Lotus Notes views in a Lotus Domino database are maintained and updated by the Lotus Domino server. If the database is DB2-enabled, then the view indexes for these views are physically stored on the DB2 server, but they are still maintained and updated by the Lotus Domino server. If the view indexes are large and the rate of data change in the application is high, then the Lotus Domino server may expend considerable resources keeping these view indexes up to date.
The Lotus Domino server has no responsibility for the maintenance of indexes on query views; these indexes are all handled by the DB2 server. Therefore, in an environment where the Lotus Domino server and the DB2 server are hosted on separate hardware devices, the use of query views instead of standard Lotus Notes views can significantly reduce the load on the Lotus Domino server. Also, when users open a standard Lotus Notes view, the Lotus Domino server has to update the view indexes before displaying the view. In DB2, the indexes are updated whenever a document is added, updated, or deleted, so there is no need to perform an index update before running the SQL statement. Thus, the data can be retrieved more quickly.
Second, traditional Lotus Notes applications can contain a large number of views because users need to sort and categorize the same set of data in various ways. It is very common for an application to start out with 10 views and 5,000 documents and, after a few years, have 500 views and 500,000 documents. The UPDATE/UPDALL task on the Lotus Domino server might then be seriously affecting the Lotus Domino server performance as it tries to keep all the view indexes up to date.
With Lotus Domino andDB2 integration features, it is possible to reduce the number of views associated with an application significantly by replacing a group of standard views with a single dynamic query view. In the preceding example, we saw value substitution: The view contents are determined based on a matching topic name. We can substitute entire portions of the SQL query as shown in listing 7.
Listing 7. Substituting portion of the SQL query
MySchema := @DB2Schema(@DbName); CategoryChoice := @Prompt([OkCancelList]; "Category"; "Select category";""; "Topic":"Attendee"); CategoryCol := @If(CategoryChoice = "Topic"; "AGENDA.TOPICNAME"; "ATTENDEES.ATTENDEENAME"); SecondCol := @If(CategoryChoice = "Topic"; "ATTENDEES.ATTENDEENAME"; "AGENDA.TOPICNAME"); "SELECT " + CategoryCol + " AS CATEGORY, " + SecondCol + " AS SECOND " + ", EVENTS.EVENTDATE, EVENTS.EVENTNAME, EVENTS.#NOTEID FROM " + MySchema + ".ATTENDEES AS ATTENDEES, " + MySchema + ".EVENTS AS EVENTS, " + MySchema + ".AGENDAITEMS AS AGENDA WHERE ATTENDEES.EVENTKEY = AGENDA.EVENTKEY AND AGENDA.EVENTKEY = EVENTS.EVENTKEY"
In this query, we prompt users as to whether they want to see the set of data categorized by Topic or by Attendee.
The field chosen is then allocated an alias of CATEGORY and the other field (which, in this case, we still want to display in the view) is given an alias of SECOND. If we use CATEGORY and SECOND as the field names in the first and second columns in our view (and leave the second column name blank), the same view displays as shown in figure 15 if users select Topic as the category.
Figure 15. Lotus Notes view when user selects Topic as the category
The same view appears as shown in figure 16 if the user selects Attendee as the category.
Figure 16. Lotus Notes view when user selects Attendee as the category
In this example, a single SQL query view has replaced the functionality of two standard NSF views.
Scenario three: Spreading an application over multiple databases
Many organizations develop Lotus Domino applications that become business critical and are in use for many years during which more and more data is added to the applications. As the size of an application grows, the application performance can deteriorate and the application may become difficult to maintain. As described in the previous section, the use of query views rather than standard Lotus Notes views can be a method of improving the performance and reducing the size of the view indexes associated with the application. A customer recently reported being able to reduce the size of an application by 40 percent by replacing standard Lotus Notes views with query views. For an application that has a large volume of data, though, this reduction might still not be enough.
The storing of a Lotus Domino application in a DB2 data store does not mean that a single Lotus Domino database can grow in size beyond the current NSF limitations (64 GB). There are ways that the Lotus Domino and DB2 integration features can be used to manage a large application more efficiently and allow the total data associated with the application to exceed this limitation.
For example, as the Events application grows larger, it might be more efficient to split the application into separate databases, perhaps one for each year. For reporting purposes, however, users might still want to be able to see a view that combines records that are now being held in separate databases.
As long as all databases in the application are DB2-enabled, we can use a single SQL query view to retrieve records from the DB2 access views associated with each separate database.
For example, imagine that we have an archive database (of the same design as our current database) that holds event records for 2007. The database design has a dynamic query view that prompts users for an attendee name and then displays the topics that the attendee has covered.
In our current database the view looks like the one shown in figure 17.
Figure 17. Lotus Notes view showing event attendance records from the current database
In our 2007 archive database, the view looks like the one shown in figure 18.
Figure 18. Lotus Notes view showing event attendance records from the archive database
We can create a query view in our current database that combines the records from both databases using an SQL query as shown in listing 8.
Listing 8. Combining records from both databases
MySchema := @DB2Schema(@DbName); ArchSchema := @DB2Schema("":"DB2Example\\events2007.nsf"); AttendeeChoice := @Prompt([OkCancelEdit]; "AttendeeName"; "Enter attendee name";""); "SELECT ATTENDEES.ATTENDEENAME, AGENDA.TOPICNAME, EVENTS.EVENTDATE, EVENTS.EVENTNAME, EVENTS.#NOTEID FROM " + MySchema + ".ATTENDEES AS ATTENDEES, " + MySchema + ".EVENTS AS EVENTS, " + MySchema + ".AGENDAITEMS AS AGENDA WHERE ATTENDEES.EVENTKEY = AGENDA.EVENTKEY AND AGENDA.EVENTKEY = EVENTS.EVENTKEY AND ATTENDEES.ATTENDEENAME = '" + AttendeeChoice + "'" + " UNION SELECT ATTENDEES.ATTENDEENAME, AGENDA.TOPICNAME, EVENTS.EVENTDATE, EVENTS.EVENTNAME, EVENTS.#NOTEID FROM " + ArchSchema + ".ATTENDEES AS ATTENDEES, " + ArchSchema + ".EVENTS AS EVENTS, " + ArchSchema + ".AGENDAITEMS AS AGENDA WHERE ATTENDEES.EVENTKEY = AGENDA.EVENTKEY AND AGENDA.EVENTKEY = EVENTS.EVENTKEY AND ATTENDEES.ATTENDEENAME = '" + AttendeeChoice + "'"
Note that we now have to calculate the schema name for the archive database. In the preceding example, it is calculated from the hard-coded database name. This name could be the replica ID of the database and could be stored elsewhere in the application configuration to prevent the need to hard-code the database name.
Note also that our SQL statement uses UNION to join the two sets of records.
Our view, with the records sorted by attendee name, now looks like the one shown in figure 19 with the records from our current database interspersed with records from our archive database.
Figure 19. Lotus Notes view showing event attendance records from both databases
As configured here, if users attempt to double-click a record that is not stored in the current database, the correct record does not open because the #NOTEID value in these records is used to locate documents in the current database only. To be able to locate and open documents in databases other than the current database we have to do a little more work.
DB2 access views can be used to store some special fields that can help us identify the source location of a document. In this case we use the following:
- #DBPATH, which gives us the file path and directory of the database in which the DB2 access view is located
- #SERVER, which gives us the name of the server on which the database is stored
- #UNID, which gives us the document unique ID associated with each record in the DB2 access view
See figure 20.
Figure 20. Special fields available for DB2 access views
If we add these special fields to a DB2 access view we can now see this extra information in our DB2 view as shown in figure 21.
Figure 21. DB2 access view with special fields viewed through the DB2 control center
With this information we can configure the QueryOpenDocument event to locate and open the correct document.
To retrieve these new fields in our SQL query we can use an SQL query like the one shown in listing 9.
Listing 9. Retrieving new fields in a SQL query
MySchema := @DB2Schema(@DbName); ArchSchema := @DB2Schema("":"DB2Example\\events2007.nsf"); AttendeeChoice := @Prompt([OkCancelEdit]; "AttendeeName"; "Enter attendee name";""); "SELECT ATTENDEES.ATTENDEENAME, AGENDA.TOPICNAME, EVENTS.EVENTDATE, EVENTS.EVENTNAME, EVENTS.#NOTEID," + " EVENTS.#SERVER || '~' || EVENTS.#DBPATH || '~' || EVENTS.#UNID AS DOCID " + "FROM " + MySchema + ".ATTENDEES AS ATTENDEES, " + MySchema + ".EVENTS AS EVENTS, " + MySchema + ".AGENDAITEMS AS AGENDA WHERE ATTENDEES.EVENTKEY = AGENDA.EVENTKEY AND AGENDA.EVENTKEY = EVENTS.EVENTKEY AND ATTENDEES.ATTENDEENAME = '" + AttendeeChoice + "'" + " UNION SELECT ATTENDEES.ATTENDEENAME, AGENDA.TOPICNAME, EVENTS.EVENTDATE, EVENTS.EVENTNAME, EVENTS.#NOTEID, EVENTS.#SERVER || '~' || EVENTS.#DBPATH || '~' || EVENTS.#UNID AS DOCID FROM " + ArchSchema + ".ATTENDEES AS ATTENDEES, " + ArchSchema + ".EVENTS AS EVENTS, " + ArchSchema + ".AGENDAITEMS AS AGENDA WHERE ATTENDEES.EVENTKEY = AGENDA.EVENTKEY AND AGENDA.EVENTKEY = EVENTS.EVENTKEY AND ATTENDEES.ATTENDEENAME = '" + AttendeeChoice + "'"
Note that we have concatenated the new fields to create a single document identifier field, which we have given an alias of DOCID.
Because there is no local back-end document associated with the entries that refer to documents in another database, we have a limited ability to manipulate values that are displayed in the view. The only LotusScript property that we can use is the CaretCategory property of the NotesUIView class. This property returns the current category for a selected document.
To pass information to the CaretCategory property, we have to put the document identifier information in the first column in the view and make it a sortable column. The column does not have to be categorized and it can be hidden, but it does mean that this technique does not work for views that need to be categorized by a different column.
If we have a column formula of DOCID, then our first view column looks like the one shown in figure 22.
Figure 22. First column of Lotus Notes view
We can put the code shown in listing 10 into the Queryopendocument event for this view to ensure that the correct document is located and opened when a row in the view is double-clicked:
Listing 10. Ensuring that the correct document is located and opened
Sub Queryopendocument(Source As Notesuiview, Continue As Variant) continue = False Dim ws As New NotesUIWorkspace Dim db As New NotesDatabase("","") Dim target As String Dim server As String Dim path As String Dim unid As String target = Source.CaretCategory server = Strleft(target, "~") path = Strleft(Strright(target, "~"), "~") unid = Strrightback(target, "~") Call db.Open(server, path) Set originalDoc = db.GetDocumentByUNID ( unid ) Set uidoc = ws.EditDocument(False, originalDoc, False, "", False, True) End Sub
If we remove the categorization from our Attendee column and hide the new first column, the view now looks like the one shown in figure 23. When users click any row in this view, the correct document is opened regardless of the database in which it is stored.
Figure 23. Lotus Notes view with first column hidden
Scenario four: Reporting on Lotus Notes data
By default, Lotus Notes has a fairly limited capability for producing summary reports on data. Totals and averages can be added to categorized views to give a certain level of reporting within a single database. As described in the previous section, if an application has been DB2-enabled, then query views can also be used to create cross-database views of information. Totals and averages can then be added to these query views to provide this basic reporting functionality across disparate databases.
Reporting using this method, however, requires that all the source records be retrieved and displayed in the view before the summarizing can take place. To produce a summary report, it is more efficient if only the summary rows are retrieved. DB2 has an extensive and powerful function library, and many of its functions can be used in an SQL query to provide summary report information without the need to retrieve and display all of the underlying data.
For example, figure 24 shows a standard Lotus Notes view that summarizes the costs to each region of the events that their participants have attended, by categorizing all the event documents by Attendee and Region and adding totals to the Cost column.
Figure 24. Lotus Notes view using standard column totals feature
If all that we are interested in is reporting on the total event costs for each user, rather than retrieving all the events they attended, we could use an SQL query as shown in listing 11.
Listing 11. Using an SQL query to report total costs
MySchema := @DB2Schema(@DbName); "SELECT ATTENDEES.ATTENDEENAME, ATTENDEES.REGION, " + "SUM(EVENTS.COST) AS SUM " + "FROM " + MySchema + ".ATTENDEES AS ATTENDEES, " + MySchema + ".EVENTS AS EVENTS WHERE ATTENDEES.EVENTKEY = EVENTS.EVENTKEY " + "GROUP BY ATTENDEES.REGION, ATTENDEES.ATTENDEENAME"
Note that rather than just selecting the EVENTS.COST field, we now add a SUM function to this code. In conjunction with the SUM function, we need to add a GROUP BY clause to tell the SQL query which groups of EVENTS.COST values have to be added.
We then use SUM, the alias we gave to SUM(EVENTS.COST), as the field value in our Cost column. Note that if we want summary totals by Region to be displayed in the same view, we can still use the standard view totals capability, but this time the totals are calculated from the pre-aggregated attendee total costs.
The result is a view (shown in figure 25) that gives us the same summary information as the standard NSF view but, in an application with thousands of event records, the result is retrieved more quickly.
Figure 25. Lotus Notes view using SQL query to calculate totals
As another example, users might be interested in viewing the skills coverage in each region and might want a view of the events that have been attended by at least one participant from a region. To do this view, we could categorize all event attendees by region, but that would mean retrieving more records than necessary. For a more efficient retrieval of the required data, we can use a query like the one shown in listing 12.
Listing 12. Retrieving required data more efficiently
MySchema := @DB2Schema(@DbName); "SELECT DISTINCT EVENTS.EVENTNAME, ATTENDEES.REGION FROM " + MySchema + ".ATTENDEES AS ATTENDEES, " + MySchema + ".EVENTS AS EVENTS " + "WHERE ATTENDEES.EVENTKEY = EVENTS.EVENTKEY"
Note the DISTINCT parameter that follows the SELECT parameter. This parameter allows us to return a single row for each event that has been attended by at least one participant from a region as shown In figure 26.
Figure 26. Lotus Notes view showing event attendance by region
Note that in the preceding two examples, we showed how a DB2 function could reproduce the same results as a standard Lotus Notes view much more efficiently. The DB2 function library also contains many functions that allow you to report on Lotus Notes data in ways that are not possible using standard Lotus Notes view capabilities. For example, the query shown in listing 13 uses aggregation functions to provide some statistical information on events that have been attended by each region.
Listing 13. Using aggregation functions
MySchema := @DB2Schema(@DbName); "SELECT COUNT(EVENTS.COST) AS COUNT, SUM(EVENTS.COST) AS SUM, AVG(EVENTS.COST) AS AVG, MAX(EVENTS.COST) AS MAX, MIN(EVENTS.COST) AS MIN, ATTENDEES.REGION FROM " + MySchema + ".ATTENDEES AS ATTENDEES, " + MySchema + ".EVENTS AS EVENTS " + "WHERE ATTENDEES.EVENTKEY = EVENTS.EVENTKEY" + " GROUP BY ATTENDEES.REGION"
The results of this SQL query can be displayed in a view as shown in figure 27 using the aliases defined (COUNT, SUM, AVG, MAX, MIN) as column fields.
Figure 27. Lotus Notes view showing event statistics aggregated for each region
In a previous scenario we saw the use of the string concatenation function "||" to create a single document identifier field for a document.
In that case, we used the function to concatenate fields from the same document. However, there is no reason why it could not be used to concatenate fields from separate documents to produce an aggregated report across one or more databases.
The preceding examples showed how to produce summary reports within a Lotus Notes application, but as detailed in the introduction, the same data can be dynamically available in a spreadsheet application or third-party reporting product such as Crystal Reports (using a direct JDBC connection to the data held in DB2). This approach allows the use of any built-in charting and reporting capabilities available to these programs when reporting on data stored in Lotus Notes applications.
In addition to the variety of ways in which the Lotus Domino and DB2 integration features can be used to integrate data held in Lotus Notes and Domino with data held in (or accessible from) DB2, there are also benefits that can be derived for Lotus Domino applications where no external data integration is required.
This document summarized four simple scenarios in which the Lotus Domino and DB2 integration features could be used to enhance application functionality or performance for pure Lotus Domino applications. The techniques used here could be combined and extended to build more complex applications.
It is important to note that the features described in this article are available only in applications that reside on a DB2-enabled server. If an application that includes any of these features is replicated to a non-DB2-enabled server or to a Lotus Notes client, then the design is replicated but attempting to use any of the features (for example, opening a query view) results in an error and no data is displayed.
Appendix: Sample application
To run the sample application without any modification, you need to do the following:
- Set up a Lotus Domino 8 and DB2 9.1 FP2 server including setting up the DB2 Access Server as described here. Make sure that you follow all the steps related to using DB2 access views and SQL query views with your Domino applications.
- Copy the databases (EVENTS.NSF and EVENTS2007.NSF) to a DB2Example directory on the Lotus Domino server and (if your default database format is NSF rather than DB2) type the following at the server console:
load compact -p DB2Example\events.nsf
load compact -p DB2Example\events2007.nsf
These commands convert the databases to use the DB2 datastore.
- Open each database in Lotus Domino Designer, and navigate to Shared Resources - DB2 Access Views. Select each DB2access view in turn, and select the following actions in order:
- Create/update in DB2 action
- Populate in DB2
- Refresh status
When all DB2access views have been processed, the green icon should appear against all of them as shown in figure A1.
Figure A1. Domino Designer showing that DB2 access views have been processed
The application should now be ready for use.
Note that if you do not locate the databases in the DB2Example directory as described above you need to amend the SQL query in the view “03-All Topics by Attendee-with prompt” to reflect the database path for the EVENTS2007 database.
Description of views
The main application EVENTS.NSF contains the views shown in figure A2. They are numbered according to the scenario that they demonstrate.
The EVENTS2007.NSF has a subset of these views.
Figure A2. Navigator showing all available views in the Events database
- The 00 views are standard NSF views for ease of locating records.
- The 02a views are used as embedded views in the Events form. They initially display empty if selected from the main navigator, and after you have opened an Event document they display the Attendees and Topics associated with that Event. These views would obviously normally be hidden but are exposed for ease of identification.
- The 02b and 02c views prompt you for a topic name and an attendee name, respectively. Note that, to make the query as simple as possible, these are case-sensitive searches.
- The 02d view displays empty in the Lotus Notes client; however, accessing the application through HTTP and using a URL similar to that shown in the scenario, populates the view in a Web browser.
- Participate in the discussion forum.
- Read the developerWorks® Lotus article, "Exploiting IBM DB2 in your Lotus Domino 7 application." The information included in this article is still valid for Lotus Domino 8.
- Read the developerWorks Lotus article, "IBM Lotus Domino with IBM DB2 storage for Lotus Notes mail users."
- Read the IBM white paper, "IBM – Improving Domino and DB2 performance."
- Refer to the IBM Lotus Notes and Domino Information Center.
- Refer to the IBM DB2 Information Center.