Exploiting IBM DB2 in your Lotus Domino 7 application

In this article, we discuss how you can work with the new IBM DB2 integration features offered in Lotus Domino 7.

Debbie Branco, Software Engineer, IBM, Software Group

Debbie Branco has been working on Notes since 1988, first in the Lotus Notes Quality Assurance group testing and coding the templates, and has worked primarily on developing templates for the Notes product since 1994. She currently works on the templates for Mail, Personal Name and Address book, TeamRoom, Bookmarks (which includes the Welcome Page) and Personal Journal.



Tom McGary, Software Engineer, IBM, Software Group

Tom McGary has worked in the software field since 1970, at IBM/Lotus since 1988, and on Lotus Notes since 1999. In addition to being the Notes replication guy and the DB2-enabled Domino deployment guy, and working on various aspects of DB2-enabled Domino, he is the Miñister of Side Effeçts of the D.N.R.C.



Wai-ki Yip, Development manager, IBM, Software Group

Wai-ki Yip is the development manager for the effort to enable Domino databases for DB2. He has been with Lotus Development for 18 years and has been involved with Domino since 1995.



04 January 2006

Also available in Russian

Lotus Domino 7 introduced the ability to use DB2 as a data store. This feature allows you to use both DB2 and Domino databases, accessing and viewing data stored in either format. This brings the best of both worlds to your environment. Notes users gain access to DB2 features, such as relational constructs and System Query Language (SQL)-based views. While DB2 users can take advantage of Domino features such as replication and security.

DB2 as an optional storage capability allows Lotus Domino customers to store the internal representation of their messaging and collaboration data in an enterprise relational database, maintaining full compatibility with NSF functionalities. In addition, Domino 7 introduced two features, DB2 Access Views (DAVs) and Query Views, which allow Domino application developers to fully exploit relational capabilities with Domino and DB2 data.

This article briefly describes what these views are, how you might use them, and how to develop such an application. We assume that you have already enabled your Domino server for DB2, have it up and running, and have intermediate experience with Domino Designer.

Note: Domino 7 with DB2 is available to all Lotus Domino customers for evaluation purposes only (no support is provided) through the DB2 Feature Trial. There is also a Limited Availability Program for qualified customers to utilize the DB2 feature with technical support and to access IBM Development resources. For further information, refer to the Lotus Domino and DB2 feature page.

Application development in a DB2 environment

You can use Domino Designer to build applications that exploit the collaborative features of the Domino server, as well as the transactional power of the IBM WebSphere server and the data storage capacity of a DB2 database. This results in an end-to-end business solution for applications, such as supply chain management, sales force automation, or customer relationship management.

Using Java technologies such as Java servlets, you can share code with and link to IBM WebSphere applications. Single Sign-On (SSO), a shared authentication service, allows for further seamless integration between Lotus Domino and WebSphere applications.

As mentioned previously, Domino Designer 7 includes two new types of design elements to assist you in managing data contained in DB2-enabled Lotus Notes databases:

  • DB2 Access View (DAV) is a shared resource that lets you define a DB2 view of Lotus Notes data.
  • Query View is a new type of NSF view. This view is populated by use of SQL queries. A Query View allows you to show DB2 data within a Notes view. If you want to create a Query View based on data in a Notes database that resides in DB2, you must first have defined and populated a DAV.

DB2 Access View

When the server is DB2-enabled, user data is not changed to relational tables directly. Only the internal representation of user data in Domino is changed into table formats. You should never directly manipulate these tables. Application developers are, however, allowed to define their own DB2 Access views through Domino Designer.

Most Domino content consists of messaging and collaboration data unsuitable for relational storage and manipulation. So, application developers can now select only the data/fields that they need for relational processing. After a DAV is defined in Domino Designer, you can create a corresponding DB2 view that will be populated with Domino data from the specified fields. Once that is done, all your SQL applications, such as Crystal Reports, can now operate on Domino data through this DB2 view (see figure 1). In addition, the Domino server will be responsible for maintaining the data integrity on any updates through both Domino and DB2, with Domino security features enabled.

Figure 1. Overview of DB2 Access Views
Overview of DB2 Access Views

This new feature is for users who want to:

  • Expose Domino data for other relational applications, such as reporting tools.
  • Leverage SQL application development skills to work on Domino data.
  • Expose Domino data so that it can be pulled together via joins into other Domino databases or SQL applications.

Query View

This feature is intended for Domino application developers who are familiar with Notes views. It allows you to define a Domino view, using SQL. Through Domino Designer, you can define a Domino view, the result set of which is based on an SQL query statement. It allows the application to pull in data (via a join) from places it previously could not. The source of data can be from other DB2 relational tables/views, or from other DB2-enabled Domino databases' DAVs.

A Query View is dynamic, and the SQL query will be re-executed every time the Domino view is refreshed (see figure 2).

Figure 2. Dynamic Query Views
Dynamic Query Views

This new feature is for users who want to:

  • Pull in data from other sources through the standard SQL.
  • Create a view based on SQL capabilities on Domino data.

Creating a Query View

To use a Query View with a DAV, you must first ensure that Domino is enabled to use DB2 as the data store, and the NSF you plan to use must be stored in DB2. The DB2 Access Server must also be installed and configured correctly. In addition, any Notes users expected to access the Query Views must have a valid DB2 user account, which must be set from the Domino Administration Client prior to deployment. Additionally, proper DB2 privileges must be set on the DB2 objects by the DB2 administrator if any "native" DB2 data is accessed via a Query View. All these requirements are the responsibility of the Domino and/or DB2 administrator, and are beyond the scope of this article. For more details on any of these topics, refer to the Domino 7 Administrator Help.

Once these criteria are met and the server is enabled to use DB2, any database created on that server will be DB2-enabled. You can quickly verify that the database is DB2-enabled by clicking File - Database - Properties, and clicking the second "i" tab in the Database properties infobox. It will read 'Database is DB2-enabled', as shown in figure 3.

Figure 3. Database properties showing database is DB2-enabled
Database properties showing database is DB2-enabled

Note that some system databases (such as Names.nsf and Events4.nsf) are not supported as DB2-enabled databases. For a complete list of unsupported databases, refer to the Domino 7 Administrator Help.

When a database is DB2-enabled, Domino stores the NSF as a set of tables in DB2. You cannot view this data; it is private to Domino. You can only view NSF data in DB2 by creating a DAV to expose that data. The DAV makes the data available directly via SQL. Third-party applications that use Open Database Connectivity (ODBC) can also read the data. For more information about DB2 Access Views, refer to the Domino Designer 7 documentation.

We now take you though a step-by-step example showing how to create a DB2 database and how to create your own Query Views. The examples that are provided refer to a database created with the Discussion template that is included with the Notes client installation.

When a Domino database is enabled for DB2, Domino creates a DB2-enabled database schema for it, as well as a set of tables in that schema to hold the Notes database data. The schema name is based on the NSF file name. Any DAVs you create will also be located in this schema. If you were to include all the messages in an NSF in a single DAV table, it would not be effective for SQL access. This is because a set of messages does not typically have a uniform set of fields (or columns, in a DB2 database), and would therefore probably not be able to be grouped in ways that make sense for SQL queries. To make SQL access useful and manageable, you should define DB2 views that specify the fields you want to access via SQL and the set of messages that belong in this DB2 view (these messages constitute the rows of the DB2 view).

Step 1. Create the DAV

Before you can pull any Domino data into a DB2 Query View in the Lotus Notes Client, you must create a DAV. (DB2 views that pull only DB2 data do not require a DAV.) The same fields that would be displayed in a view's columns must be created as entries in the DAV. DAVs use a large amount of space because each DAV is a duplication of the data in the database, so it is recommended that you have one (or very few) DAVs that contain all the fields you need for the different Query Views.

Write down the names of all fields referenced in the views' columns. For this example, the Query View will use a similar format to the All Documents view in Discussion. We will assign "dynamic" categories, using the SQL Query formula, rather than use the categories in the messages themselves. Our field list is as follows: Form, From, AltFrom, Subject, Readers, ExpireDate.

Launch Domino Designer and click the DB2 Access Views option under Shared Resources (see figure 4).

Figure 4. Example dialog box showing DB2 Access Views option
Example dialog box showing DB2 Access Views option

Click New to create a new DAV. In the properties box, name the DAV SUMMARY2. Insert the fields on your field list. Make sure that the All forms option is checked in the DAV properties box, as shown in figure 5. This will ensure that all documents in the database are included in the DAV. Save and exit the DAV.

Figure 5. DAV properties box showing All forms option checked
DAV properties box showing All forms option checked

To complete the DAV, it needs to be created in DB2 and populated. At the top of the list of DAVs in Designer, click the Create/Update in DB2 button. Then click the Populate in DB2 button. This action sends a request to the Domino server to begin populating the DAV. Note that, with a large database, this could take some time. To check on the progress, click the Refresh Status button (see figure 6).

Figure 6. DAV Refresh Status button
DAV Refresh Status button

When the DAV is ready to use, a checkmark will appear to the left of the DAV name in Designer, as shown in figure 7.

Figure 7. Checkmark indicating the DAV is ready to use
Checkmark indicating the DAV is ready to use

Step 2. Create a Query View

A Query View is another type of Notes view. Like other types of Notes views, Query Views are design elements that are part of Domino applications. A Query View uses a formula that evaluates to an SQL query to populate its data, instead of using a view selection formula. To create a Query View based on data in a Notes database that resides in DB2, you must first have defined and populated a DAV for that database (see Step 1). Because Query Views are not persistent, they do not take up any space in a Notes database. Existing views cannot be converted into Query Views; you must specify that a view is a Query View at the time the view is created.

Data in DB2 databases is accessed and manipulated through SQL statements. When you create a new view in a DB2-enabled database, a new selection condition is present, called By SQL Query. If you don't know immediately what your SQL Query formula is going to be, enter double-quotes into the formula box. Note that the SQL Query that you enter is a formula that evaluates to an SQL Query.

For this example, we create a Query View called New SQL view. To do this, click the Copy From button and select the All Documents view. Then choose the selection condition of By SQL Query, and enter "" as the formula (see figure 8).

Figure 8. Create View dialog showing By SQL Query selected
Create View dialog showing By SQL Query selected

Click the Save and Customize button. When the view opens in Domino Designer, delete all columns except Date and Topic, and add a categorized column at the far left, as shown in figure 9.

Figure 9. Categorized column
Categorized column

After the view is saved, you can distinguish SQL Query Views by the grey and blue icon that appears to the left of the view name in Domino Designer (see figure 10). If you hover your mouse over the icon, it tells you that the view is a DB2 Query View.

Figure 10. Grey and blue icon next to view name
Grey and blue icon next to view name

Step 3. Create the SQL Query Formula

Important: The Select statement of the SQL Query formula must include all the fields referenced in the view's columns.

Edit the new Query View. On a piece of paper, look at each column formula and write down the field(s) referenced in the formulas. For example, we created a query view based on the All Documents view in the discussion database. These are the fields that we wrote down:

ColumnField(s)
CategoriesCategories
We will not be using the data from the Categories field here. This will be a dynamic column generated by the SQL Query formula.
DateNo field. This column uses all @functions.
TopicFrom
AltFrom
Readers
ExpireDate
Subject

The next step is to construct the SQL Query. The most important element of the SQL query is the Select statement. The rules for Select statements are as follows:

  • The full SQL query must be enclosed in double-quotes.
  • All the fields referenced in the view's columns are included.
  • Field names are separated by a comma.
  • If the Query View references Domino data via a DAV, and selects the #NOTEID from the DAV, you can open the message.
  • The Schema must be referenced properly. When a Domino database is stored in DB2, the information in that database is contained in tables that reside in a single DB2 grouping construct known as a schema. All references to those tables use the table name preceded by the schema name, separated by a period (for example, "schema.table"). A quick, easy way to determine the schema name of a DB2-enabled Domino database is to use the @function @DB2Schema, which, when used in a query formula, returns the name of the DB2 schema for a specified DB2-enabled Domino database.
  • The DAV must be referenced properly.

When you edit the SQL Query View in Domino Designer, a new event, SQL Query, appears in the programmer's pane, as shown in figure 11.

Figure 11. Programmer’s pane showing new SQL Query event
Programmer’s pane showing new SQL Query event

You can write the SQL Query the same way you would write a selection formula. The following code is the Select statement constructed for our new view, using the DISCUSSION_DAV that we created in Step 1, based on the table of fields shown previously. Note that hard carriage returns are not supported within the SQL portion of the formula. It must be one single paragraph, enclosed in double-quotes.

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";

Here is the SQL portion of the formula, broken down into parts:

The first part is called a "regular table expression." This creates a virtual table by the name "CATEGORIZED" with a set of columns supplied from inner queries. Each inner query must match these columns in position and data type. "with categorized (categories, #noteid, from, form, altfrom, readers, expiredate, subject) as

  • categorized is the name that we have given to the table.
  • categories is the name we have given to the first column; this will be dynamic.
  • #noteid as mentioned previously, is placed here so the messages can be opened.
  • from, form.... subject are the names of the fields that will display in the Query View.

This is one of the inner queries supplying the virtual categories table with data from relational tables (in this case, MyTable):

(select 'Agendas', #noteid, from, form, altfrom, readers, expiredate, subject from " + MyTable + " where (subject like '% meeting %' or subject like '% agenda %')

In this example, the output from this inner query is combined with that from other inner queries with the key words UNION ALL:

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 queries map directly to those in the virtual table CATEGORIZED, so:

  • 'Agendas' or 'Backup/Restore' are values mapped to the categories column in the virtual categorized table. Note that these are dynamic, literal values in this example. The values can be derived from any expression in the SQL language.
  • from, form... subject are the fields that will display. These must match in position and data type with the columns in the virtual table CATEGORIZED.

Also, note that the Select statement from any inner query could pull in columns from any other table and have any other name, but only if the columns selected matched the data type of the columns in the CATEGORIZED virtual table. Here's 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 %')

This works as long as foreign_noteid, somefrom, myform, youraltfrom, the_readers, the_expiredate, and topic all matched the data type of the columns in the virtual CATEGORIZED table (that is #noteid, from, form, altfrom, readers, expiredate, subject).

In addition:

  • UNION ALL is a keyword that concatenates all the Select statements. It makes the data from all the Select statements appear as a single stream to the view. This is one of the ways we can get data from multiple NSFs, or even other tables, into a Query View. Another method is via a join.
  • select * from categorized is the final Select statement. If spelled out (without using the * wildcard), it would be select categories, #noteid, from, form, altfrom, readers, expiredate, subject.

The new SQL view is now ready to be viewed, using the Notes client. When you open the view, the status bar shows the entire Select statement that you created. Figure 12 shows the view we just created.

Figure 12. Newly created SQL view
Newly created SQL view

Conclusion

For Domino 7, we have provided a native way to store Domino data in a relational database, DB2. In addition, we have provided the Domino application development environment with two new major capabilities, DB Access views and Query Views, to enhance existing applications and to develop new applications based on relational concepts.

Using the tips and examples provided in this article, we urge all Domino application developers to try out the new features and develop ideas on how to develop new and exciting applications. Relational manipulation of Domino data is finally here!

Resources

Learn

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into IBM collaboration and social software on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Lotus, Information Management
ArticleID=101225
ArticleTitle=Exploiting IBM DB2 in your Lotus Domino 7 application
publish-date=01042006