Simplify your DECS DB2 environment

This article explains how you can make your Domino DECS applications retrieve DB2 data more efficiently, in a way that's easier to maintain and is more scalable. We walk you through a sample employee directory application, showing how you can optimize your DECS/DB2 interactions every step of the way.

Scott Morris (Scott_Morris@us.ibm.com), Senior Software Engineer, IBM

Scott Morris is a Senior Software Engineer with IBM. He has been with Lotus/IBM since 1990. Since 1996, he has worked on developing products that are offered by the Domino Enterprise Integration group. He was previously a member of the Notes API team, the Notes server team (before there was a Domino) , and somewhere in the deep dark past the Lotus 1-2-3 for Macintosh team. Scott has an MS in CS from Boston University and a BS in Mathematics from Carnegie Mellon University.



Ethan Sencer (ESencer@commerceinsurance.com), Programmer/Analyst, The Commerce Insurance Company

Ethan Sencer is a Programmer/Analyst for The Commerce Insurance Company, where he has worked developing insurance applications since 2001. Prior to that, he worked as a programmer for Progressive Insurance and Thomas & Betts. He is an experienced ILE RPG developer with strong SQL skills. Ethan studied Computer Science at Marist College for two years and is currently working to complete his IT degree with the University of Phoenix.



08 March 2004

Also available in Japanese

Imagine that you have built a robust Domino application that uses Domino Enterprise Connection Services (DECS) to retrieve data from your enterprise system (for example, DB2 UDB or DB/400). This application uses DECS to monitor multiple forms. A typical form might use two, three, or even twenty-one different DECS activities to get the data from the many enterprise tables needed to make your application do its magic.

After this is set up, it works fine for a while. But then performance problems start to creep in due to the fact that the Domino database now contains many hundreds of thousands of documents. To help address this, you decide to create eight copies of the original Domino database, distribute documents evenly among them, and then have a master launching application direct each user to the appropriate database, based on a key entered by the user. This allows each database to hold fewer documents, improving performance.

When there was only one database, you could imagine 50 DECS activities supporting all the forms in the application. Now that you have spilt your Domino database into eight databases, you need 400 (eight times 50) activities to support them all. Managing 50 activities is reasonable, but 400 is difficult. For instance, if you need to update a form's design, you might impact 21 corresponding DECS activities. Multiplying these across eight databases results in 168 separate changes -- a lot to get right!

To avoid situations such as this, you need to be creative with exactly how you get your data from DB2 to use with DECS. This article describes one way to do this, using an employee directory application as an example. (You can download the two databases containing the administration code and employee data for this sample application from the Sandbox.) This article assumes that you're familiar with DECS and Domino application development.

The sample Employee form

Let us work on improving the sample Employee form, which currently has five different DECS activities supporting it. The goal of this form is to display a quick summary of everything about an employee, including some historical data. We will look first at the DB2 table that holds data to display in Domino, and then the corresponding DECS activity to make that happen. The first DB2 table is called EMPLOYEES:

Column NameDatatype
EMPIDINTEGER
FNAMECHARACTER(30)
LNAMECHARACTER(30)
DEPARTMENTCHARACTER(3)
DOBDATE

You want these columns connected to the fields EMPID, FNAME, LNAME, DEPARTMENT, and DOB in the Domino Employee form, as shown here:

Figure 1. Domino Employee form
Domino Employee form

Defining the connection to DB2

When using DECS, you must define a connection to your enterprise system. Here is a generically defined connection to the DB2 database. (We call it generic because no table, view, or procedure is selected.)

Figure 2. Generically defined connection to DB2
Generically defined connection to DB2

This screen shows that all you need for a generic connection to DB2 is the name of the DB2 database connection (as it is configured in the DB2 client), a user name, and a password. Creating the connection document this way allows all activities to share the same connection information.


Mapping employee information

Now you must create DECS activities to map exployee information between DB2 and your Domino application.

Basic employee data

Let us look at the first DECS activity called EMPLOYEES:

Figure 3. EMPLOYEES activity
EMPLOYEES activity

On one side is the Domino application the activity will watch. This is the name of the Domino database file (empdata.nsf). After selecting the file, you must choose which Domino form to watch, in this case Employee. On the other side is the Lotus Connection, which in this example is a reference to the newly created DB2 connection document (DB2 TOOLSDB). After this connection is selected, you must choose which DB2 table to work with. Here we selected DB2ADMIN.EMPLOYEES.

Now that you know how to get to both the Domino and DB2 sides, you must map the fields from the Domino form to those in the DB2 table. DECS requires one or more keys to identify which record(s) should be returned and displayed in a Domino document. In this example the key is the Employee ID. In both DB2 and the Domino Employee form this field is called EMPID.

After you finish key mapping, you map the RealTime fields. The field names are also the same on both sides, making things very easy. The RealTime fields mapped are FNAME, LNAME, DOB, and DEPARTMENT. Finally, you must tell this activity which document events to watch for. Here event Open is selected. This is all reasonably straightforward for the experienced DECS user.

Now turn your attention to the next activity, which maps employee salary information. First, examine your DB2 table:

Salaries
Column NameDatatype
EMPIDINTEGER
BASEDECIMAL
BONUSDECIMAL

You want these columns connected to the fields BASE and BONUS in the Domino Employee form contained in the Salary section. To do this, create a DECS activity called SALARIES. This activity only includes a default options section, so it is not shown here:

Figure 4. SALARIES activity
SALARIES activity

On the Domino application side, you're watching empdata.nsf and its Employee form (as you did with the EMPLOYEES activity). On the Lotus Connection side, the data source is again DB2 TOOLSDB. After that connection is selected, choose the DB2 table to work with, which for this activity is DB2ADMIN.SALARIES. The key is the same as in the EMPLOYEES activity: EMPID. The RealTime fields are BASE and BONUS. Again, you monitor the Open event.

You also want to map job history information. Start by looking at the next DB2 table, which contains multi-value data (meaning that for each item found there might be more than one row of result set to display):

Job history
Column NameDatatype
EMPIDINTEGER
DEPARTMENTCHARACTER(3)
TITLECHARACTER(30)
POSITION_CODECHARACTER(10)
STARTDATE
ENDDATE
CURRENTINTEGER

Map these DB2 columns into multi-value Domino fields, which show up as Binary in the DECS activity field mapping section. You want to connect these columns to the fields HIST_DEPARTMENT, TITLE, POSITION_CODE, START, END, and CURRENT in the Domino Employee form in the Job History section. Note that for the first time not all fields have exactly the same name as in the DB2 table.

Now create a DECS activity called JOB_HISTORY:

Figure 5. JOB_HISTORY activity
JOB_HISTORY activity

In this activity, the Domino application is the same as in the previous activities. On the DB2 side, the table used is DB2ADMIN.JOB_HISTORY. The key field is once again EMPID. The fields with the same names on both sides are mapped to each other: CURRENT, END, START. TITLE, and POSITION_CODE. The field HIST_DEPARTMENT in Domino is mapped to DEPARTMENT in the DB2 table. Again the Open event is monitored. Also note that under Options Multi-Value Data we have the checked off Use multi-value data fields, and sorted in descending order via the START field:

Figure 6. Use multi-value data fields option
Use multi-value data fields option

Now look at how to handle job performance data. Start by examining the DB2 table that holds job performance information, which is also multi-value data:

Performance reviews
Column NameDatatype
EMPIDINTEGER
YEARINTEGER
RATINGCHARACTER
SUMMARYVARCHAR(512)

This will be mapped to the fields YEAR, RATING, and SUMMARY in the Employee form, under the Performance History section. Do this through a DECS activity called REVIEWS:

Figure 7. REVIEWS activity
REVIEWS activity

The Domino side is the same as in the previous activities with empdata.nsf and the Employees form selected. The DB2 side shows that you want to work with the table DB2ADMIN.REVIEWS. The key for lining up the data is once again EMPID, and the RealTime fields have the same names on both sides: RATING, SUMMARY, and YEAR. Again the Open event is monitored.

Because this is multi-value data, you select the Use multi-value data fields option. In addition, we have done a descending sort on the YEAR field, giving the most current info first. The SUMMARY field can hold a lot of data, more than you really want to see in Domino. To fix this, add a Post Open Event formula to the DECS activity to just show the first 60 characters. Do this by opening the Event Options tab and adding the formula FIELD SUMMARY:=@left(SUMMARY;60);"" in the Post-Open Formula field:

Figure 8. Post-Open Formula field
Post-Open Formula field

Finally, you need to map information about each employee's dependents. The DB2 table that holds this data is as follows:

Dependents
Column NameDatatype
EMPIDINTEGER
FNAMECHARACTER(30)
LNAMECHARACTER(30)
RELATIONCHARACTER(10)
DOBDATE

Because employees can have more than one dependent, this too is multi-value data. The Domino fields to store this information are DEP_FNAME, DEP_LNAME, RELATION, and DEP_DOB. These are found under the Dependents section of the Employees form.

The following is the DECS activity for this data named DEPENDENTS:

Figure 9. DEPENDENTS activity
DEPENDENTS activity

The preceding illustration shows that the setup for the Domino side is once again the same as in previous activities. On the DB2 side, the table to be used is DB2ADMIN.DEPENDANTS. The key field is again EMPID. Under the RealTime mapping sections, you have fields with different names. The Domino fields DEP_DOB, DEP_FNAME, DEP_LNAME, RELATION map to these DB2 fields in this order: DOB, FNAME, LNAME, RELATION. Again the event to monitor is Open, and the Use multi-value data fields option is selected.


Optimizing the application

You now have your complete application. Opening a document in Domino gives you data from each of the DB2 tables, showing up under different sections of the Domino form. First comes employee ID and name, then Salary Info, then Job History, then Performance History, and finally Dependents:

Figure 10. Complete Employee Data form
Complete Employee Data form

It is not the most beautiful form in the world, but it suits this example's needs.

Reducing the number of activities

Now let's discuss how you can optimize your application's design and reduce the number of activities it needs. Recall that you might make multiple copies of your Domino application, and for this one form, each copy means eight more DECS activities to maintain. If each activity also allows 20 connections to DB2 to service the user demand, you can see the load on both Domino and DB2 climbing quickly.

One way to simplify things is with DB2 stored procedures. You can have a stored procedure return a result set that is built with data from each table, both where single records are returned as well as multi-value data. To do this, you must first design a new "super table" with all columns from all tables. This lets you fill out the field mapping section in the new consolidated activity. This table will also hold the result set from which you fetch data. Fortunately, the key EMPID is common to all of the tables and is used to find everything that you are looking for. In the case where column names are the same in different tables, you need to rename them, in much the same way that you have already done in the Domino Employee form.

The new DB2 table (called EMPLOYEE_APP) could look like this:

Column NameDatatype
EMPIDINTEGER
INDEXINTEGER
FNAMECHARACTER(30)
LNAMECHARACTER(30)
DEPARTMENTCHARACTER(3)
DOBDATE
BASEDECIMAL
BONUSDECIMAL
HIST_DEPARTMENTCHARACTER(3)
TITLECHARACTER(30)
POSITION_CODECHARACTER(10)
STARTDATE
ENDDATE
CURRENTINTEGER
YEARINTEGER
RATINGCHARACTER(10)
SUMMARYVARCHAR(512)
REL_FNAMECHARACTER(30)
REL_LNAMECHARACTER(30)
RELATIONCHARACTER(10)
REL_DOBDATE

This table includes a column named INDEX. This is the key to making this all work. When the records are placed into this table by the stored procedure, the columns of each of the original DECS activities will have an index value associated with them. All single record result sets (of which you have two) are written to the first row of this table. Any row in the result set with an INDEX of 2, for example, refers to the REVIEWS DECS activity. You can then "massage" the data to find only these records to populate the corresponding multi-value fields.

The following is a DB2 stored procedure that will generate this table. The DECS key EMPID is the only input parameter of the procedure:

CREATE PROCEDURE DB2ADMIN.EMPLOYEE_SUMMARY (IN EMPID INT )
RESULT SETS 1
LANGUAGE SQL
P1: BEGIN
  DECLARE EMPID_VAR INT;

  /* This will be the cursor that we will fetch from for the result set */
  DECLARE cursor1 CURSOR WITH RETURN FOR
  SELECT * FROM DB2ADMIN.EMPLOYEE_APP
  WHERE DB2ADMIN.EMPLOYEE_APP.EMPID = EMPID_VAR;

  SET EMPID_VAR = EMPID;

  /* First clean up from any previous runs */
  DELETE FROM DB2ADMIN.EMPLOYEE_APP WHERE 
  	DB2ADMIN.EMPLOYEE_APP.EMPID = EMPID_VAR;

  /* Get wanted fields from EMPLOYEES and SALARIES tables */
  INSERT INTO DB2ADMIN.EMPLOYEE_APP (EMPID, INDEX, FNAME, LNAME, 
  	DEPARTMENT, DOB, BASE, BONUS)
  SELECT DB2ADMIN.EMPLOYEES.EMPID, 0, FNAME, LNAME, DEPARTMENT, 
  	DOB, BASE, BONUS FROM
  DB2ADMIN.EMPLOYEES, DB2ADMIN.SALARIES WHERE DB2ADMIN.EMPLOYEES.EMPID = 
  	DB2ADMIN.SALARIES.EMPID AND DB2ADMIN.EMPLOYEES.EMPID = EMPID_VAR;

  /* Get wanted multi-value fields from JOB_HISTORY table */
  INSERT INTO DB2ADMIN.EMPLOYEE_APP (EMPID, INDEX, HIST_DEPARTMENT, TITLE, 
  	POSITION_CODE, START, END, CURRENT)
  SELECT DB2ADMIN.JOB_HISTORY.EMPID, 1, DEPARTMENT, TITLE, POSITION_CODE, 
  	START, END, CURRENT FROM
  DB2ADMIN.JOB_HISTORY WHERE DB2ADMIN.JOB_HISTORY.EMPID = EMPID_VAR;

  /* Get wanted multi-value fields from REVIEWS table */
  INSERT INTO DB2ADMIN.EMPLOYEE_APP (EMPID, INDEX, YEAR, RATING, SUMMARY)
  SELECT DB2ADMIN.REVIEWS.EMPID, 2, YEAR, RATING, SUMMARY FROM
  DB2ADMIN.REVIEWS WHERE DB2ADMIN.REVIEWS.EMPID = EMPID_VAR 
  	ORDER BY YEAR DESC;

  /* Get wanted multi-value fields from DEPENDENTS table */
  INSERT INTO DB2ADMIN.EMPLOYEE_APP (EMPID, INDEX, REL_FNAME, REL_LNAME, 
  	RELATION, REL_DOB)
  SELECT DB2ADMIN.DEPENDANTS.EMPID, 3, FNAME, LNAME, RELATION, DOB FROM
  DB2ADMIN.DEPENDANTS WHERE DB2ADMIN.DEPENDANTS.EMPID = EMPID_VAR;

  /* Finally, open the result set cursor */
  OPEN cursor1;

END P1

A potential problem with this stored procedure is that if two people open the same employee ID document simultaneously, they could cause a conflict in the DB2ADMIN.EMPLOYEE_APP table, producing inconsistant results You can prevent this by enabling document locking in your Domino database. This allows only one person at a time to open a document. More robust solutions can create new tables dynamically to store the temporary results, or use locks or semaphores to control access.

Now you can create a single new DECS activity called EMPLOYEE_SUMMARY that (almost) does the job of all of the others:

Figure 11. EMPLOYEE_SUMMARY activity
EMPLOYEE_SUMMARY activity

The Domino side is still empdata.nsf and the Employee form. The DB2 side is the new super table DB2ADMIN.EMPLOYEE_APP. Under field mapping, the key is still EMPID. The RealTime fields are now every previous DECS activity's RealTime fields, all in one, as shown. The watched event is once again Open. (Notice that we changed START and END in the Domino form to HIST_START and HIST_END. More on this later.) Multi-value data is in use.

Note that sorting is no longer used. Because you can control the creation of the stored procedure, you can add the following code to select the data of REVIEWS, the one previous activity that sorted multi-value data:

/* Get wanted multi-value fields from REVIEWS table */
  INSERT INTO DB2ADMIN.EMPLOYEE_APP (EMPID, INDEX, YEAR, RATING, SUMMARY)
  SELECT DB2ADMIN.REVIEWS.EMPID, 2, YEAR, RATING, SUMMARY FROM
  DB2ADMIN.REVIEWS WHERE DB2ADMIN.REVIEWS.EMPID = EMPID_VAR 
  	ORDER BY YEAR DESC;

Yes, DECS can sort for us, but DB2 is optimized for these operations, so you perform the sort in DB2 where it will happen fastest.

The DECS Event Options for the Open event section shows that you will not read directly from the table DB2ADMIN.EMPLOYEE_APP:

Figure 12. Event Options - Open tab
Event Options - Open tab

You call the DB2ADMIN.EMPLOYEE_SUMMARY stored procedure that you defined earlier, which returns the result set that you want. This procedure must have an INPUT parameter EMPID. This means that you will not follow the traditional Select method of getting a result set. Instead, you invoke a stored procedure that creates a result set for you.

Data massaging

The first data massaging you do is in the EMPLOYEE_SUMMARY activity's Event Options/Open tab. (See the preceding illustration.) Every field fetched in the result set (except the key field EMPID) is multi-value data, due to how the EMPLOYEE_APP table was filled out. The original EMPLOYEES and SALARIES activities only return one record of the result set. An easy way to purge the unwanted remaining elements from these fields is with the following code, where the @SUBSET function is used to take just the first element from a list field. This is called once for each field where you do not have multi-value data: FNAME, LNAME, DEPARTMENT, DOB, BASE, and BONUS. This resets the single value fields to the first element of the list fields that DECS returned. That was the easy part. The multi-value data requires more work. Although a more complex Post-Open formula could be developed to massage the data as needed, it is far easier to do it in LotusScript in the Employee form's QueryOpen event. This code requires a Notes 6 client; Notes 5 clients can't process NotesDateTime arrays the same way. Our code creates new arrays made up of only records with a searched for INDEX value with each INDEX corresponding to a single table used previously by a single DECS activity.

The code begins as follows:

Sub Queryopen(Source As Notesuidocument, Mode As Integer, Isnewdoc As Variant, 
 Continue As Variant)
	Dim doc As notesdocument
	Set doc = source.Document

These arrays hold just the data you care about for a given field:

	Dim tmpArray1() As String
	Dim tmpArray2() As String
	Dim tmpArray3() As String
	Dim tmpArray4() As String

	Dim tmpIntArray() As Integer
	
	Dim tmpDateArray1() As NotesDateTime
	Dim tmpDateArray2() As NotesDateTime
	
	Dim count As Integer
	Dim tmpIndex As Integer

This is the actual number of rows of data plus one returned in the result set:

	count = Ubound(doc.HIST_DEPARTMENT)

This snippet makes the arrays the correct size to hold the maximum possible amount of data:

	Redim tmpArray1(count)
	Redim tmpArray2(count)
	Redim tmpArray3(count)
	Redim tmpIntArray(count)
	Redim tmpDateArray1(count)
	Redim tmpDateArray2(count)

And these lines deal with massaging the fields from the Job History Activity:

	tmpIndex = 0
	For i = 0 To count
		' We only care about records with INDEX having a 1
		If(doc.INDEX(i) = 1) Then
			tmpArray1(tmpIndex) = doc.HIST_DEPARTMENT(i)
			tmpArray2(tmpIndex) = doc.TITLE(i)
			tmpArray3(tmpIndex) = doc.POSITION_CODE(i)
			tmpIntArray(tmpIndex) = doc.CURRENT(i)

                 ' The reason that START and END became HIST_START and HIST_END
                  ' is that they are Lotus Script key words.
			Set tmpDateArray1(tmpIndex) = 
			  New NotesDateTime(doc.HIST_START(i))
			Set tmpDateArray2(tmpIndex) = 
			  New NotesDateTime(doc.HIST_END(i))
			tmpIndex = tmpIndex + 1
		End If
	Next
	tmpIndex = tmpIndex -1

Now you resize the temporary arrays to the actual size needed:

	Redim Preserve tmpArray1(tmpIndex)
	Redim Preserve tmpArray2(tmpIndex)
	Redim Preserve tmpArray3(tmpIndex)
	Redim Preserve tmpIntArray(tmpIndex)
	Redim Preserve tmpDateArray1(tmpIndex)
	Redim Preserve tmpDateArray2(tmpIndex)

Next you replace the fields in the document with the new arrays:

	doc.HIST_DEPARTMENT = tmpArray1
	doc.TITLE = tmpArray2
	doc.POSITION_CODE = tmpArray3
	doc.CURRENT = tmpIntArray
	doc.HIST_START = tmpDateArray1
	doc.HIST_END = tmpDateArray2

This code massages the fields from the Reviews Activity, resizing to the maximum size possible:

	Redim tmpArray1(count)
	Redim tmpArray2(count)
	Redim tmpIntArray(count)
	
	tmpIndex = 0
	For i = 0 To count
		' Only records with INDEX of 2 are wanted now
		If(doc.INDEX(i) = 2) Then
			tmpArray1(tmpIndex) = doc.RATING(i)
			tmpArray2(tmpIndex) = Left(doc.SUMMARY(i),60)
			tmpIntArray(tmpIndex) = doc.YEAR(i)
			tmpIndex = tmpIndex + 1
		End If
	Next
	tmpIndex = tmpIndex -1

And these lines resize the temporary arrays to the actual size needed:

	Redim Preserve tmpArray1(tmpIndex)
	Redim Preserve tmpArray2(tmpIndex)
	Redim Preserve tmpIntArray(tmpIndex)

Now you must replace the fields in the document with the new arrays:

	doc.RATING = tmpArray1
	doc.SUMMARY = tmpArray2
	doc.YEAR = tmpIntArray

Next you massage the fields from the Dependents Activity, again resizing to the maximum possible:

	Redim tmpArray1(count)
	Redim tmpArray2(count)
	Redim tmpArray3(count)
	Redim tmpDateArray1(count)
	
	tmpIndex = 0
	For i = 0 To count
		' Only records with INDEX of 3 are wanted now
		If(doc.INDEX(i) = 3) Then
			tmpArray1(tmpIndex) = doc.DEP_FNAME(i)
			tmpArray2(tmpIndex) = doc.DEP_LNAME(i)
			tmpArray3(tmpIndex) = doc.RELATION(i)
			Set tmpDateArray1(tmpIndex) = 
			  New NotesDateTime(doc.DEP_DOB(i))
			tmpIndex = tmpIndex + 1
		End If
	Next
	tmpIndex = tmpIndex -1

Finally, you resize the temporary arrays to the actual size needed:

	Redim Preserve tmpArray1(tmpIndex)
	Redim Preserve tmpArray2(tmpIndex)
	Redim Preserve tmpArray3(tmpIndex)
	Redim Preserve tmpDateArray1(tmpIndex)

And you replace the fields in the document with the new arrays:

	doc.DEP_FNAME = tmpArray1
	doc.DEP_LNAME = tmpArray2
	doc.RELATION = tmpArray3
	doc.DEP_DOB = tmpDateArray1
	
	' DONE!
End Sub

Summary

We admit our methods push some work into the Domino application form that previously had been managed by DECS. But on the whole, we require Domino and Notes and also DECS to do far less work to give us our application data than it used to do. Additionally, as we scale our application across multiple Domino databases, we have only one DECS activity for the Employee form per database, instead of five. In this way, our application demonstrates that with just a bit of creativity, you can make things a lot faster, easier to maintain, and scalable.

Resources

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. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. 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
ArticleID=12879
ArticleTitle=Simplify your DECS DB2 environment
publish-date=03082004