Use IBM DB2 integration features in Lotus Domino 7 Applications

Lotus Domino 7 introduces the function of using DB2 as data storage. This function allows you to access and view data stored in two formats using the DB2 and Domino databases. This will bring the best of the two products into your environment. Notes users can obtain DB2 functions, such as relational structures and views based on system Query Language (SQL. At the same time, DB2 users can use Domino functions, such as replication and security.

DB2, as an optional storage feature, allows Lotus Domino customers to store internal representations of their messages and collaboration in enterprise-level relational databases to maintain full compatibility with NSF features. In addition, Domino 7 introduces two features: DB2 access view (DB2 access view, Dav) and query view, which allow Domino applicationsProgramDevelopers make full use of the relational functions on Domino and DB2 data.

This article briefly describes what these two views are, how to use them, and how to develop and use their applications. We assume that you have enabled DB2 on the Domino server to start and run it, and have some experience with Domino designer.

Note:Enabling DB2 Domino 7 is provided to all Lotus Domino customers through DB2 feature trial, which is only used for evaluation (not supported ). There is also a limited availability program provided to licensed users so that they can use DB2 functionality and access IBM development resources with technical support. For more information, see the functional pages of Lotus Domino and DB2.

Develop applications in the DB2 Environment

You can use Domino designer to build applications to leverage the collaboration functions of the Domino server, the transaction processing functions of the IBM WebSphere server, and the data storage capabilities of the DB2 database. In this way, the application can obtain an end-to-end business solution, such as supply chain management, sales automation, or customer relationship management.

Using Jave technology such as Java Servlet can be shared with IBM WebSphere applicationsCodeOr. Single Sign-On (SSO)-a shared verification service that enables seamless integration of Lotus Domino and WebSphere applications.

As mentioned above, Domino designer 7 Includes two new design elements to help manage data stored in the DB2-enabled Lotus Notes database:

• The DB2 access view (DAV) is a shared resource that enables you to define a DB2 view for Lotus Notes data.
• Query view is a new NSF view. This view is filled with SQL queries. The query view allows you to display DB2 data in the Notes view. To create a query view based on data residing in the DB2 Notes database, you must first define and fill in the dav.

DB2 access View

When DB2 is enabled on the server, user data is not directly converted to a relational table. Only the internal representation of user data in Domino is converted into a table. In no case should these tables be directly manipulated. However, application developers are allowed to define their own DB2 access views through Domino designer.

Many Domino contents contain messages and Collaboration Data that are not suitable for relational storage and manipulation. Therefore, application developers can now select only the data/fields for Link processing. After Dav is defined in Domino designer, you can create a corresponding DB2 view and fill the view with Domino data from the specified field. Once these operations are completed, all your SQL applications (such as Crystal Reports) can now operate Domino data through this DB2 view (see figure 1 ). In addition, when Domino security is enabled, the Domino server is responsible for maintaining data integrity when any updates are made through Domino and DB2.

Figure 1. DB2 access view Overview

This new feature applies to users who want to perform the following operations:

• Provide Domino data to other related applications, such as reporting tools.
• Effectively use SQL application development skills to operate Domino data.
• Publish Domino data so that it can be integrated into other Domino databases or SQL applications.

Query View

This feature is available to Domino application developers who are familiar with the notes view. This function allows you to use SQL to define the domino view. You can use Domino designer to define the domino view. The result set of the view is obtained based on an SQL query statement. The query view allows applications to pull data from areas where data cannot be previously pulled (connected ). The data source can be a relational table/view of another DB2, or a Dav of another domino database with DB2 enabled.

The query view is dynamic. When the domino view is refreshed, the SQL query is re-executed (see figure 2 ).

Figure 2. dynamic query view

This new feature applies to users who want to perform the following operations:

• Use standard SQL to pull data from other data sources.
• Create a view on Domino data based on the SQL function.

Create query view

To use the query view with Dav, you must first ensure that DB2 is enabled for data storage and the NSF to be used must be stored in the DB2 database. You must also install and correctly configure the DB2 access server. In addition, any notes user who wants to access the query view must have a valid DB2 user account, which must be set on the domino administration client before deployment. In addition, if any "local" DB2 data is accessed through the query view, the DB2 administrator must set appropriate permissions for the DB2 object. All of these prerequisites are the tasks that the Domino and/or DB2 administrators need to complete and are beyond the scope of this article. For more information about these topics, see the domino 7 administrator help.

Once these conditions are met and DB2 is enabled on the server, DB2 will be enabled for any database created on the server. You can click File-database-properties, and click the second "I" tab in the database attribute information dialog box to quickly verify whether the database has enabled DB2. The database is DB2-enabled, as shown in 3.

Figure 3. Database attribute display DB2 Enabled

Note that some system databases (such as names. nsf and events4.nsf) are not supported as databases with DB2 enabled. For a complete list of unsupported databases, see the domino 7 administrator help.

When the database enables DB2, Domino stores NSF as a group of tables in DB2. You cannot view this data because it is a dedicated data of Domino. You can only open data by creating a Dav and view NSF data in DB2. With SQL, Dav can directly access data. Third-party applications that use open database connection (ODBC) can read this data. For more information about the DB2 access view, see the domino designer 7 Documentation.

Now we will show you how to create a DB2 database and how to create your own query view step by step. The example provided here references a database created using the discussion template, which is included in the Notes Client installation.

When DB2 is enabled for the domino database, Domino creates a DB2-enabled database mode for DB2 and creates a group of tables in the mode to save the data of the Notes database. This mode is based on the NSF file name. All davs to be created are saved in this mode. If you place all messages in an NSF file in a single Dav table, this will be invalid for SQL access. This is because a group of messages usually do not have consistent fields (that is, columns in the DB2 database), so it may not be able to group messages in a meaningful way for SQL queries. To make SQL access available and easy to manage, you should specify the fields to be accessed using SQL and the message groups that belong to this DB2 view (these messages constitute the rows in the DB2 view) to define the DB2 view.

Step 1. Create a Dav

In the Lotus Notes client, to pull Domino data into the DB2 query view, you must create a dav. (DAV is not required if you only pull the DB2 view of DB2 data .) To display the same fields in the View column, you must create an entry in dav. Dav occupies a large amount of space. Because each Dav is a copy of data in the database, we recommend that you use one (or as few) Dav to contain all the fields required for different query views.

Write down the names of all fields referenced in the View column. In this example, the query view uses a format similar to the all arguments view in discussion. We will use the SQL query formula to specify the "dynamic" category, instead of the message category. Our field lists are as follows: form, from, altfrom, subject, readers, and expiredate.

Start Domino designer and click the DB2 access views option under shared resources (see figure 4 ).

Figure 4. Example dialog box showing the DB2 access views Option

Click new to create a new dav. In the attribute box, name Dav summary2. Insert a field in the field list. Make sure that the All Forms option is selected in the Dav attribute box. 5. This ensures that the Dav contains all the documents. Save and disable dav.

Figure 5. The dav attribute box with all forms selected is displayed.

To complete the Dav, you must create it in DB2 and fill it in. At the top of the Dav list in designer, click Create/update in DB2. Click the populate in DB2 button. This operation sends a request to start filling Dav to the Domino server. Note: If the database is large, this operation may take some time. To view the progress, click Refresh status (see figure 6 ).

Figure 6. Dav refresh status button

When a Dav can be used, a check mark is displayed on the left side of the Dav name in designer, as shown in 7.

Figure 7. Check that Dav can be used

Step 2. Create a query view

Query view is another type of notes view. Like other types of notes views, query views are designed as part of Domino applications. The query view uses the SQL query formula to fill the data, instead of using the view to select the formula. To create a query view based on the data residing in the DB2 Notes database, you must first define and fill the dav for the database (see step 1 ). Because query views are not persistent, they do not occupy the Notes database space. You cannot convert an existing view to a query view. You must specify this view as a query view when creating a view.

You can use SQL statements to access and manipulate data in a DB2 database. When a new view is created in a database with DB2 enabled, a new selection condition, called by SQL query, appears. If you cannot immediately know what the SQL query formula is, enter double quotation marks in the formula box. Note that the entered SQL query is an SQL query formula.

In this example, a query view called New SQL view is created. To perform this operation, click Copy From and select the all documents view. Select selection condition as by SQL query and enter "" as the formula (see figure 8 ).

Figure 8. Create view dialog box selected by SQL query

Click Save and customize. When the view is opened in Domino designer, all columns except date and topic are deleted, and a column for classification is added to the leftmost side, as shown in figure 9.

Figure 9. Category column

After saving the view, you can differentiate the view by the gray and blue icons displayed on the left of the view name in Domino designer (see figure 10 ). If you place the cursor over the icon, the system will prompt that the view is a DB2 query view.

Figure 10. The gray and blue icons next to the view name

Step 3. Create an SQL query Formula

Important:The SQL query formula selection statement must include all fields referenced in the View column.

Edit the new query view. View the formulas of each column and write down one or more fields referenced in the formulas on a piece of paper. For example, we create a query view based on the all arguments view in the discussion database. These are the fields we wrote:

Column	Field
Categories	Categories Here we do not use data from the categories field. This column is a dynamic column generated by the SQL query formula.
Date	No field. All the classes use @ functions.
Topic	From Altfrom Readers Expiredate Subject

The next step is to construct an SQL query. The most important element of SQL query is the query statement. The statement selection rules are as follows:

• Double quotation marks must be added to both sides of the SQL query.
• To include all fields referenced in the View column.
• Use commas to separate field names.
• If the query view references Domino data through Dav and selects # noteid from Dav, the message can be opened.
• The reference mode must be correct. When the domino database is stored in DB2, the information in the database is contained in the table residing in a single DB2 group structure, which is called the schema. All references to these tables are separated by the schema name and period (for example, schema. Table "). A simple and quick way to determine whether the domino database mode name of DB2 is enabled is to use @ function @ db2schema and use it in the query formula, the specified DB2 mode name for the domino database with DB2 enabled will be returned.
• Dav must be referenced correctly.

When editing the SQL query view in Domino designer, a new event (SQL query) appears in the programming pane, as shown in 11.

Figure 11. display new SQL query events in the programming pane

You can write SQL queries in the same way as the selection formula. The following code is a selection statement for building a new view. The discussion_dav created in step 1 is used in the Code and is based on the field table shown above. Note: Hard carriage return is not supported in the SQL section of the formula. The SQL statement must be a full segment, and double quotation marks are added on both sides.

Rem {DB2 schema}; myschema: = @ db2schema (@ dbname); REM {Dav name constructed with schema}; mytable: = myschema + ". summary2 ";" with categorized (categories, # noteid, from, form, altfrom, readers, expiredate, subject) as (select 'agendas', # noteid, from, form, altfrom, readers, expiredate, subject from "+ mytable +" where (lcase (subject) like '% meeting %' or lcase (subject) like '% Agenda % ') union all select 'backup/restore', # noteid, from, form, altfrom, readers, expiredate, subject from "+ mytable +" where (lcase (subject) like '% backup %') Union all select 'performance ', # noteid, from, form, altfrom, readers, expiredate, subject from "+ mytable +" where (lcase (subject) like '% performance %') Union all select 'tools', # noteid, from, form, altfrom, readers, expiredate, subject from "+ mytable +" where (lcase (subject) like '% tools %') Union all select 'admin', # noteid, from, form, altfrom, readers, expiredate, subject from "+ mytable +" where (lcase (subject) like '% DB2 %') Select * From categorized ";

This is the SQL part of the formula, which is divided into the following parts:

The first part is called "regular table expression ". This Part creates a virtual table named "categorized". The table contains a group of columns provided by the inner query. Each inner query must match these columns in terms of position and data type."With categorized (categories, # noteid, from, form, altfrom, readers, expiredate, subject)

• CategorizedIs the name of the specified table.
• CategoriesIs the name of the first specified column; the name will be dynamic.
• # NoteidAs described above, put it here to enable the message.
• From, form... subjectIs the name of the field to be displayed in the View query.

This is one of the inner queries that provide data in a relational table (in this example mytable table) to a virtual table categories:

(Select 'agendas', # noteid, from, form, altfrom, readers, expiredate, subject from "+ mytable +" where (subject like '% meeting %' or subject like '% Agenda % ')

In this example, the Union all keyword is used to combine the output from the inner query with the output from other inner queries:

Union all select 'backup/restore', # noteid, from, form, altfrom, readers, expiredate, subject from "+ mytable +" where (lcase (subject) like '% backup %') Union all... (next inner query)

The columns in the inner query are directly mapped to the corresponding columns in the virtual table categorized. Therefore:

• 'Endas'Or'Backup/restore'Is the value mapped to the categories column in the virtual table categorized. Note that the text values in this example are dynamic. These values can be derived from any expression in SQL.
• From, form... subjectIs the field to be displayed. These fields must match the columns in the virtual table categorized in terms of position and data type.

Note that the selection statement from any inner query can be used to pull columns from other tables with other column names, however, you must first ensure that the data type of the selected column must match the data type of the categorized column in the virtual table. The following is an example:

(Select 'Somewhere else', foreign_noteid, somefrom, myform, youraltfrom, the_readers, the_expiredate, topic from "+ anothertable +" where topic like '% this isn' t that hard, really % ')

Here, foreign_noteid, somefrom, myform, youraltfrom, the_readers, the_expiredate, and topic are all columns in the virtual table categorized (these columns are # noteid, from, form, altfrom, readers, expire).

In addition:

• Union allIs a keyword that connects all the selected statements. It displays data from all selected statements as a single stream to the view. This is a way to select data from multiple nsf or other tables to query the view. Another method is to connect.
• Select * From categorizedIs the last query statement. If all the content is spelled out (* wildcard is not used), this statement is select categories, # noteid, from, form, altfrom, readers, expiredate, subject.

Now you can use the Notes client to view the new SQL view. When you open the view, the status bar displays the entire selection statement you have created. Figure 12 shows the created view.

Figure 12. Newly created SQL View

Conclusion

For Domino 7, we provide a local method for storing Domino data in relational database DB2. In addition, a domino application development environment with two major new features (DB access view and query view) is provided to enhance existing applications and develop new applications based on Relational concepts.

Through the tips and examples provided in this article, we encourage all Domino application developers to try new features and development concepts when developing new and existing applications. The relational operation of Domino data ends here!

References

Learning

• For more information, see the original article on the developerworks global site.

• Developerworks: LotusArticle, "New features in Lotus Domino 7.0", including other information about DB2 integration in Notes/Domino 7.
• The Lotus Domino and DB2 functional pages are another excellent resource for Domino/DB2 integration information.
• You can also access the Lotus Domino 7 administrator to obtain more information about this function.

Discussion

• Participate in Forum discussions.

• Join developerworks by participating in developerworks blogsCommunity.

Author Profile

Debbie Branco has been engaged in notes since 1988. She initially tested and compiled templates in the Lotus Notes Quality Assurance team. Since 1994, she has been mainly working on notes product development templates. She currently works on templates for mail, Personal Name and Address Book, teamroom, bookmarks (including welcome page), and personal journal.

Tom McGary has been working in the software field since 1970, has been working in IBM/Lotus since 1988, and has been working in Lotus Notes since 1999. In addition to working on notes replication, enabling Domino deployment for DB2, and enabling all aspects of Domino for DB2, he is also the MI n ister of d.n. r.c side effe çts.

Wai-ki Yip is the development director for enabling DB2's domino database. He has been working in Lotus Development for 18 years and has been engaged in Domino since 1995.