Create a Lotus Notes application to access DB2 using Domino Designer
Have you ever wanted to learn how to create a client side application which your users can use to access and update data in your DB2 database? Have you ever been given an unbelievable deadline to develop and deploy the application? If you hear yourself saying YES then weâd like to introduce you to Domino Designer, a great tool which is shipped along with the IBM Lotus Notes suite of products.
This article will show you how easy it is to create a Notes application, using Domino Designer, to access enterprise data stored in a DB2 database and the benefits of doing so. You will see how to create an application to select, update, delete and insert data. You will also learn how to distribute this application so that a group of users will be able to use this application either with the Lotus Notes client or a Web browser.
Lotus Notes 101
Lotus Notes fundamentals
Lotus Notes is a workgroup computing environment which facilitates a group of people to work, share information, coordinate activities, and use Internet and intranet applications. This is accomplished regardless of platform and physical location of the users. Lotus Notes also provides an application development environment with an established security model to design and deploy the application across the enterprise.
Lotus Notes main components
Domino is an applications and messaging server which supports the development and deployment of groupware applications. It supports different clients such as Lotus Notes clients, mail clients, and Web browsers. Domino also acts as a Web server for Lotus Notes applications which are accessed over the Internet. You will see this feature at work later in the article when you access your application through a Web browser.
Lotus Notes Clients:
Examples of Lotus Notes Services include the Domino Security Model which is used for user authentication, access control and encryption. Replication Service facilitates synchronization of information across different locations. Another example is the Messaging Service which is a client/server messaging service to allow users to schedule activities, it also supports different mail clients such as POP3, SMTP, and IMAP.
Benefits of integrating Lotus Notes and DB2
Lotus Notes provides a secure and distributed environment within which applications can be created, deployed and accessed enterprise-wide. DB2 is a highly powerful Relational Database Management System (RDBMS) with capabilities of storing and managing high volumes of data. Application developers can get the best of both worlds, by developing Notes applications which act as an âeasy to useâ interface to data stored in DB2. DB2 data can then be used in a wide range of tools and enterprise applications within the Lotus Notes Security model. Lotus Notes applications, as mentioned earlier, can be accessed over the Web, which makes traditional data stored in DB2 databases more accessible to users.
Company profile application overview
In this article you will be developing an application that accesses a DB2 database called COMPDB.You will use the COMPANY table to select, update, delete, and insert data.
Set up the prerequisites
What you need to begin:
- Windows® OS
- Domino Server v6
- Lotus Notes client and Domino Designer v6
- DB2 v8.1
Create the DB2 database and table using the following commands:
db2> create db compdb
db2> create table company (compno varchar(10), compname varchar(30), address varchar(30), phoneno varchar(10), contactnme varchar(20))
Insert three rows of data into the COMPANY table. Please follow the example below to insert the first row and then insert the remaining two rows in the same way:
db2> insert into company values('0001','Mouse Inc.',' 1 Mousey Road, DisneyLand','111111','Mickey Mouse')
|0001||Mouse Inc.||1 Mousey Road, Disney Land||111111||Mickey Mouse|
|0002||Duck Private Eye||1 Duck Road, Disney Land||222222||Donald Duck|
|0003||Donkey Pte Ltd||2 Swamp Street||333333||Donkey|
Start the Domino Server:
To start a Domino Server, Lotus Domino Designer or Lotus Notes client, launch these applications from the Start Menu (see Figure 1):
As Domino server starts to run, you will see the Domino server console which looks like Figure 2:
Before you begin you need to start two tasks, the HTTP task and the Domino Enterprise Connection Services (DECS) task. These are equivalent to NT Services. To start the HTTP task and DECS task, type the following in the Domino server console:
Once you see the message "HTTP Server: Started" in the console, you know that the task has been loaded. Now proceed to start the DECS task.
To ensure that these tasks are started, type the following command on the Domino server console. Your output should look similar to Figure 3.
Create a link to allow Domino Server to access DB2 database
Before designing the application, you need to provide your database information to Domino, specifically to the DECS task so that it knows how to connect to the DB2 database. This information needs to be entered in the DECS Administrator Notes database. In order to open the database, please follow the steps below:
- Start your Lotus Notes client from the Start menu.
- In your Lotus Notes client, click File-> Database-> Open.
- Type in your Domino server name (for example, Longfy/acme), select the DECS Administrator (decsadmin.nsf) database and click Open. Please see Figure 4.
Once you have opened the DECS Administrator database, you will see the screen below (Figure 5):
To create a link to your DB2 database click on Connections in the navigator panel on the left and then click the Add Connection button. This is shown in Figure 6.
Select Connection to DB2 and then click OK. You will be presented with the Connection to DB2 document. Enter the values as shown in Figure 7:
You should be able to select the COMPANY table in the Table Selection section by clicking the arrow beside Name. This will display a dialog box (Figure 8) showing all the tables in the COMPDB database:
If you are not able to do so, then please revisit the Setting up prerequisites section to recreate the table. In addition, ensure that the DECS task is loaded refer to the Start the Domino Server section on how to do the same.
You can now click on Save and Close to save this connection document. What you have done so far is created a link between the Domino Server and the COMPANY table in the COMPDB DB2 database.
Create a Domino Application using Domino Designer
Letâs go through a few terms used in Domino Designer. In the Notes/Domino world, applications are stored in a file with a .NSF file extension. Most Domino application developers call the NSF file, a database. For example, you have your Mail database or Claims database. However, the term database is not the same as that used in the DB2 world. Notes databases are not relational database because there are no pre-defined relationships between the data entities. In Notes, information is stored in fields which in turn are grouped together in a form much like related fields are stored in a table in a DB2 database. Like DB2, there are views and the function of a Notes view is to display information. A Notes Database can have many forms with each form having its own fields to store information. Information can be presented in views. In "DB2-speak", you will use a form to insert data into the Notes application and use views to display the data.
To create your Company Profile application (or NSF file), start the Domino Designer from the Start Menu. From Domino Designer, click File->Database->New to create a new database as shown in Figure 9
In the New Database dialog box, enter name of the server that will be hosting the database. Also enter the name of the database and the filename. In our case, the Domino server is called LongFY/acme, the application name is called Company Profile and the filename is Company.nsf. Please refer to Figure 10. You will be creating a blank database, so you will use the âBlank- Template. Click OK. A database will now be created in your Domino server.
Once a database is created you will see the Domino Designer interface as shown in Figure 11.
The left panel lists the recently opened databases. It also lists all the design elements of a Notes database for example the forms and the views. The right panel shows all the names of the design element. Currently, you do not have any forms created so the right panel is empty.
Now create a new form by clicking on the New Form button. A blank form is created and you can see a new section called Untitled (Form). This section is where the developer can enter programming code. Please refer to Figure 12.
You can type some text in the form. Figure 13 shows an example.
Now you will create a Field. Place your cursor at the end of the "Company no:"line and select Field from the Create menu.
As you can see (Figure 14), a field is created. The field name is Untitled. In addition, a new dialog window appears. This window is called the Property box and shows all the properties of the new Untitled field. You will give a name to this field by typing CompanyNumber in the Property box. You do the same for the other fields as well. Your form should now look like Figure 15.
Save this form by clicking Save from the File menu. When prompted for a form name, save it as CompanyForm.
Lastly, you need to give a name to the view. When Domino Designer creates a NSF database, a view is created. However, the view has no name by default. You need to give it a name. To do that, click on View in the left panel. You will see the following screen (Figure 16):
Double click on the (untitled) view and the design of the view is opened (Figure 17):
Enter "Company View" as the name of the view in the Property box as shown in Figure 17. Click File -> Save to save the view.
So far, you have created a new Domino application called Company.nsf. You have created a form called CompanyForm and created fields in the form to store data. You have also given a name to the default, nameless view. Next, you will map the fields you have created to the fields in the DB2 database.
Map the fields in Domino Designer to the column in DB2
In order for your Domino application to retrieve and update information from DB2, you have to map those fields that you have created to the corresponding columns in the DB2 table. This can be done with the DECS Administrator database. If you recall, this is the database in which you created a link to DB2. Hereâs how it looks (Figure 18).
Click on Activities in the navigator on the left and select Add Activity. You will be presented with the Virtual Fields Activity form as shown in Fig 19. In this form, we can tell Domino which field is the key field and how this field maps to the DB2 key column. You will also tell Domino the non-key fields and what DB2 columns they map to.
As you can see in Figure 19, there are five items you need to configure. They are numbered one to five in the form. First, you need to give a name to the Activity, call it Company DB Field Mapping. This is entered in the Identification section under the Name: field. For item 1, you click on the arrow beside the Domino Application section and choose the Domino application for which you want to specify field mappings. In this case, it is the Company.nsf file. You will also be asked for a particular form name, in our case it is CompanyForm. Second, you need to select which Lotus Connection document we want to use to link to DB2. This connection document was created by us earlier. Your Virtual Fields Activity form should now look similar to Figure 20.
Now comes the part where you will map the fields to the DB2 column. This is done by clicking the arrow besides Mapping section. A dialog box as the one shown in Figure 21 will appear.
In the Box 1 (of Figure 21), you will need to select the key field in Company.nsf application and map it to the key column in DB2. In your case, you ticked CompanyNumber field (Box 1) and ticked the CompNo column (Box 2). Then you select which non-key field to map to the other DB2 columns. To do that, you have to tick the field name in Box 3 to map to the column in Box 4. You have to do it for every field that you want to map. In the context of this tutorial, you are mapping all fields. Box 5 and the corresponding column in Box 6 will show you the fields and columns that you have mapped for your verification. Once this is done, confirm the mapping and then click OK. Now you are back to the Virtual Field Activity form. You have configured three of the configurable items in this form, that leaves two more items.
The fourth item you need to define falls under the Events section. In this section you need to choose the events when the Domino application performs synchronization with the DB2 database. These events are â Create, Open, Update and Delete. What this means is that the Domino application will synchronize the information when:
- Create a new Company form â this will do a DB2 insert into the database.
- Open an existing document and the data will be refreshed if the information in DB2 is changed â essentially a DB2 Select to refresh the data.
- Update the customer information, e.g. a change to the customerâs billing address â this will do a DB2 Update into the database.
- Delete a document â the corresponding DB2 record will be deleted
Finally, in item five, you determine that this activity will be started automatically by Domino. You have now completed mapping all the fields of our application so you can select Save and Close.
Import key fields from DB2
Once this Virtual Field Activity document is filled, we will be able to see the activity in DECS Administrator database (Figure 22)
Open the activity document again and click on the Initialize Key button. This button will import all the records from COMPDB database into the Domino application. This needs to be done only once. You will see the prompt below (Figure 23):
Once you clicked Yes, all the key field from the COMPDB database will be imported. (Figure 24)
Note: Notes/Domino will only import the key field, COMPNO and save it into the NSF file. Other fields like ADDRESS, COMPNAME, CONTACTNME and PHONENO are not copied over from DB2. The reason is that Domino will synchronize these other fields in real time from DB2. That is why you created a Real Time Activity document â to have the ability to select and update the data in DB2 in real time. These other data are stored in DB2 database, not in the Company.nsf.
Start the real time activity to synchronize the data
In order for fields to obtain data from ADDRESS, COMPNAME, CONTACTNME and PHONENO in real time, you must ensure that the activity is started. The activity should auto-start and you can verify its status by looking at the icon after the External Sourcecolumn. The icon should look like a broadcast communication tower as shown in Figure 25.
If the activity is not started, highlight the activity and click the Start button. Also, please ensure that the DECS task is running on the Domino server or the activity will not start. Please refer to the Start the Domino Server sectionon how to start the DECS task. Now, you can go to your Company application to ensure that the three records are being imported. This can be done by double clicking the Company Profile application from your Lotus Notes client (Figure 26).
This will open the Company Profile application. As shown in Figure 27, the three records from DB2 were imported into the application.
Open Document No 1 by double clicking on "1". This will display record no 1 which is a company owned by Mickey Mouse (Figure 28)
Double click any where in the document to go into Edit mode. You are going to change some data in this document. When you are in Edit mode, you can see that the document fields are editable. Please see Figure 29.
Change the name of the Contact Person from Mickey Mouse to Mickey M. Mouse. Save the document by clicking File->Save. After saving, press ESC to close the document.
You have essentially done a DB2 Update to the record. To verify that the update is successful, launched the DB2 command line window and issue a
SELECT statement. As shown in Figure 30, the Contact Person column for Company no 0001 has been updated.
To insert a record into the COMPDB database, you will create a new company document in Lotus Notes. To do that, click CompanyForm from the Create menu in the Company Profile application (refer to Figure 27 to locate the Create menu). This will open an empty Company Profile document. Fill in the new company information as shown in Figure 31.
Save the document by clicking Fileâ>Save and press ESC to close it. Now you will see a new record in the Company Profile application (Figure 32).
Verify that the record is inserted into DB2. See Figure 33.
What you have done so far is create a connection document to tell the Domino Server how to connect to DB2. You developed a sample Company Profile application using Domino Designer. You also created an Activity document in which you desginated thekey field in Domino and mapped a Notes field to a column in DB2. You then imported the data from DB2 to the application. You have also manipulated the data by changing information in the record and ensuring that the same information is changed in DB2. You have also inserted a new record into DB2. To delete a record, highlight the document in Notes and press the Delete key in your keyboard. The record will be marked with an X (Figure 34)
Press F9 to refresh the application and the document will be deleted from the Notes application as well as from DB2. Again, you can verify that by issuing a
SELECT statement in the DB2 Command Line window.
Enhance the application
You can enhance the Company Profile application by creating action buttons to allow users to create new company documents (instead of using the Create menu). You can also create a button which will Save the document and automatically exit, instead of using the File->Save menu option and then pressing ESC to exit the document. In addition, you can format the form with different font and colors for better presentation. All these will not be covered in this article. However, you can find out how to do the above by referring to the extensive information in the Domino Designer Help database. This database is launched from the Domino Designer by pressing F1.
Distribute the application to Lotus Notes users
In order for others to be able to use this application you will need to follow the steps below:
- Right-click on the icon for the Company application, this icon is as shown in Figure 26.
- Select Database Copy -> Link from the context menu
- Paste this link onto the body of a new memo using the Ctrl+V hotkey.
- Send this email out to the users. Once received, all the users need to do is double-click on this attached link. Doing this will open up the application and they can proceed to use it.
Access the application using a Web browser
Open up your Web browser and enter a URL in the following format. URL http://server/Company.nsf for example, http://longfy.dts.com/Company.nsf Enter your Notes user id and password and you should see a page similar to Figure 35.
Click on the Company View link and then click on any of the documents (Figure 36 and 37) and you will be able to see the DB2 data shown in an Internet browser.
Note: As an application developer, you can further customize the Company Profile application such that the user can edit and update the data from the Internet Browser. This will not be covered in this article. You can refer to the Domino Designer help database for more information.
You have received a brief introduction to Lotus Notes and learned why integrating Notes and DB2 is beneficial to developers and users. You have also gone through steps on how to quickly develop a Notes application to access and manipulate data stored in a DB2 database, and how the application can be accessed with a Web browser. This simple example will hopefully set you on the right path so that you can begin to develop Notes applications which facilitate a group of users to share and work on DB2 data.