DB2 for Domino
From InstallationWiki
| Official Page |
| Project Documentation |
| Download |
|
For years, Lotus Domino has provided flexible data storage using "self-describing" documents. This has allowed developers to add and remove fields from document types on the fly. Each document stored within a Domino database contains a list of fields and their values.
Domino Designer combines visual form design with a data definition tool that allows you to update the fields stored within a document type throughout the lifetime of the application. This often tempts developers to create tables of fields within a form. In a relational word this may seem sacrilege, but in Notes/Domino development, this is more often than not considered acceptable.
Domino DB2 integration (see the note below) provides the opportunity to close the gap between document-centric Notes/Domino and relational, SQL-based DB2. It allows you to bring the scalability features of DB2 and the flexibility of SQL into Domino applications. This feature encapsulates the storage facility from both end users and APIs.
In this tutorial, we examine how to configure Domino 8 to use DB2 as an alternative data store to the NSF format. We also look at two important concepts in Domino/DB2 integration:
- DB2 Access Views (DAVs), where selected data sets are pushed from Domino to DB2 in conjunction with a DB2 Access Server.
- Query Views, which are Notes views based on a SQL query. This design artifact is supported by the DB2 Access Server and a DB2 Access View.
Note. The first release of Domino 7 and DB2 Integration is provided by IBM, solely for evaluation and testing purposes, with Limited Availability. It is currently unsupported. Full support for this feature is provided in the current release - Domino 8. Domino 8 is delivered with a limited-license version of db2 v9.1.
[edit] DB2 as a Domino Data Store
When planning a DB2-based Domino application, remember that end users do not need their own DB2 connectivity. The Domino server fulfills this responsibility.
Creating a DB2-hosted Domino database results in a small NSF file that is created on the file system of the Domino server in its data directory. This file is typically smaller than a megabyte (often only a few kilobytes). DB2-backed Domino servers can replicate and cluster with traditional NSF-based Domino servers, so that both environments can co-exist if necessary. This can be particularly useful when testing Domino DB2 integration with existing Domino data.
The Domino and DB2 servers have the following possible relationships:
- The DB2 UDB server is installed locally on the Domino server.
- The DB2 UDB Enterprise server is installed on a remote host accessible to the Domino server.
Testing environments typically leverage a DB2 UDB server that is installed locally on the machine where Domino is installed. The obvious advantage of this is local connectivity to the DB2 data store.
Note. A number of Domino system databases are not supported for hosting by DB2. These include LOG.NSF, NAMES.NSF, DIRCAT.NSF, ADMIN4.NSF, DDM.NSF, and RESRC7.NSF (Rooms and Resources). Any database with the type of Domino Directory is prevented from being migrated. This will include personal address books.
[edit] Prerequisites
Before you can install and configure DB2 as a Domino data store, there are several considerations you must address.
[edit] Software Required for the Domino Server
The Domino server must run Domino 8 or later. You must also install either the DB2 v9.1 UDB server locally, or a DB2 Run-Time client, to catalog the DB2 "DOMINO" database. For remote installations where the DB2 server does not reside on the same host as the Domino server, one of the following must be installed locally on the machine where the Domino server executes in order for it to communicate with the DB2 host:
- DB2 Run-Time client
- DB2 UDB Enterprise Server Edition
- DB2 Workgroup Edition
Domino 8 and DB2 are available for these operating-system platforms:
- Microsoft Windows 2003
- Microsoft Windows 2003 64bit - Domino 8.0.1 supports 64bit.
- IBM AIX 5.2 and 5.3
- SUSE Enterprise Server 10
- Red Hat Enterprise Server 4
[edit] Transactional Logging
The Domino server that will store its data in DB2 must have transactional logging enabled prior to installing DB2. To enable transaction logging, edit the Domino Server document belonging to the Domino server that will use DB2-based storage. Select the Transactional Logging tab and set the Transactional logging option to Enabled. Next, allocate at least 192 MB in the Maximum log space field:
Note. The preceding screen shot depicts a Circular Logging style. Circular, Archived, or Linear styles are all allowed for this setting.
After transactional logging has been enabled, restart the Domino server. Domino will create the requested storage for the new transaction logs upon restart. You should see a message similar to the following, indicating that the requested logs are being created:
[edit] DB2 Installation Accounts
A DB2 installation account is necessary to install the DB2 software. Create this account and use it to log in to a local computer to install DB2. This user account is also used by the DB2 Server Enablement tool. This account can be either a local or domain user account created via either Windows or AIX. It must be a member of the Administrators group in use by the computer on which the DB2 software is to be installed. The account must also have the following privileges:
- Act as part of the operating system
- Create a token object
- Log in as a service
- Increase quotas
- Replace a process level token
When you're ready to install the DB2 software, use this account to log in to the target machine. Start the setup program, which includes important information detailing DB2 installation prerequisites. There is also a link to begin the product installation. Click it when all installation requirements have been met.
[edit] Installation and Configuration
This section examines an installation where the DB2 server is installed on the same machine as Domino.
The Setup wizard first prompts for an installation type. For DB2/Domino integration, you can select a Typical installation; Data warehousing and Satellite administration capability are not needed:
As the install program completes its work, it starts the DB2 software and displays a First Steps dialog. This dialog provides a link to create the sample database, which provides a convenient testing mechanism to verify connectivity to the DB2 server and the ability to catalog its databases. To create the sample database, click the Create Sample Database link located at the top left of the First Steps dialog. This displays the Create Sample Databases dialog. Confirm that the DB2 UDB sample option is checked, and then click OK.
It can take several minutes to create the sample database. If successful, a message will confirm that the sample database was created.
[edit] Testing Connectivity to the DB2 Instance
The DB2 Server Enablement tool will fail if it cannot connect to the DB2 instance hosting the DOMINO database. We recommend that you test connectivity from the Domino host to the DB2 instance prior to enabling DB2 Domino storage. To do so, you must first determine which port the DB2 instance hosting the DOMINO database binds to. If you don't have this information, you can determine it by launching the DB2 Control Center: right-click on the Server, and select the Export Server Profile option from the Context menu.
Selecting Export Server Profile prompts you for a destination filename. If successful, the Control Center responds by displaying the following dialog box:
After you export the file, open it and locate a section titled [inst>Instance Name], where Instance Name is the name of DB2 instance hosting the DOMINO database. Be sure that the Instance Name matches; many DB2 deployments have more than one instance deployed. After you find a match, locate the entry for PortNumber.
This entry appears in bold in the following output (in this example, the instance is located at port 50000):
[inst>DB2]
NodeType=4
NodeNumber=0
DB2Comm=TCPIP
Authentication=SERVER
HostName=db2domino.acme.com
ServiceName=db2c_DB2
PortNumber=50000
IpAddress=192.168.1.104
QuietMode=No
SPMName=ISSL
TMDatabase=1ST_CONN
If you have uninstalled and reinstalled any versions of the DB2 software, then your DB2 instance may not communicate via port 50000. Instead, DB2 may increment to a higher port number, such as 50001. Verifying the contents of the PortNumber variable in the Domino DB2 instance increases the likelihood that the connectivity test succeeds.
[edit] Verifying DB2 Version Information
In some cases, it may be necessary to verify the version of DB2 installed to ensure it meets the requirements of Domino. To verify the version information of your DB2 software, open a DB2 Command Line Processor window and enter the db2level command:
[edit] Additional DB2 Configuration
The SYSCTRL group name must be set within DB2. The Domino online help documentation describes how to do this using the Command Line Processor. It can also be done using the Control Center. This section examines this step using the Command Line Processor.
To update SYSCTRL_GROUP, open a Command Line Processor window. Review the current configuration by typing GET DBM CFG at the prompt. Verify that the value is not set by reviewing the results. Set this value if needed.
The following screen shot shows the DB2 instance in need of this configuration step:
To set this value to DB2DOM, type the following into the Command Line Processor window:
UPDATE DBM CFG USING SYSCTRL_GROUP DB2DOM
After DB2DOM has been added to the SYSCTRL_GROUP, stop, and restart the DB2 instance using the DB2STOP and DB2START commands.
[edit] The DB2 Server Enablement Tool
The DB2 Server Enablement tool configures Domino to use DB2 as its data store. The tool is available via the Domino Administrator and is disabled in 7 by default. It resides in the Configuration tab, under DB2 Server Tools (you will notice the grayed-out tasks indicating that all DB2 Server Tools are disabled):
If you are using Domino 7 then the DB2 Server Enablement tool requires that a DLL file be added to the Domino executable directory. In this example, the file is added to C:\Lotus\Domino as shown in the following figure:
If this file is added to the executable directory while the Domino server is running, restart it as well as any Domino Administrator clients connected to it. Then verify that the Enable Server for DB2 tool has been enabled.
Click Enable Server for DB2 when you are ready to enable your DB2-based Domino server. The following dialog box appears:
Verify that all requirements have been met, and then click Continue. The Domino 7 Administrator displays the DB2 Server Enablement tool. The DB2 Server tab of the dialog prompts for information needed to create the DB2 DOMINO database. Be sure that you have the name of the DB2 instance you intend to use for your DB2 DOMINO database. This value is specified in the DB2 database name field and defaults to DOMINO. You can choose another name for the DB2 database the tool creates, if needed. The DB2 datastore directory field is an optional field.
This dialog also allows you to specify whether the default Domino datastore for new databases is NSF-based or DB2-based. There is an option to Immediately update the server's Domino Directory with DB2 information, which when selected causes the tool to process this update immediately. If deselected, update is later processed by AdminP.
Next, click the DB2 Access tab and enter an OS account name to be used by Domino to access DB2. This dialog also requires the password associated with the OS account. After you enter this information, the Enable button is activated.
Clicking Enable displays the Enable Server for DB2 Results dialog, similar to the following figure:
This step adds notes.ini variables to the server document. These variables are processed when the server restarts. Specifically, this step sets the notes.ini variable DB2Init=Create to direct the Domino server to create the requested DB2-based DOMINO database upon restart. If the Domino server successfully creates this database upon restart, it sets the notes.ini variable from DB2Init=Create to DB2Init=OK.
After the Domino server has been enabled for DB2 storage, the DB2 tab appears in the DB2-enabled Domino server document as shown in the following figure:
The field values should reflect those entered via the DB2 Server Enablement tool.
Note. It is a good idea to open the DB2 Control Center and confirm that the DOMINO database was created on the DB2 server. There are cases when the Domino server console may report that the database was created, when in fact it wasn't. You should see something similar to the screen shot that follows if the DB2 Domino data store was successfully created.
After the Domino server has been enabled for DB2 storage, you can take advantage of the benefits of using DB2 storage for new Domino databases. (Bear in mind that this does not convert existing Domino databases to DB2 storage.) At this point, you can optionally install the DB2 Access Server, which is necessary for creating DB2 Access Views and Query Views. These are discussed in the remainder of this chapter.
[edit] The DB2 Access Server, DB2 Access Views, and Query Views
The DB2 Access Server is an optional Domino 7 DB2 integration feature that governs access to Domino data when accessed from DB2. It is not required for DB2-based Domino storage, but DB2-based Domino storage is a requirement for the DB2 Access Server. The DB2 Access Server is, however, required for the use of DB2 Access Views and Query Views.
DB2 Access Views (DAVs) are Domino design artifacts that allow developers to push a set of Domino data into a DB2 view. Developers specify in each DAV a specific set of fields, from one or more forms, to be mapped from Domino to a table in DB2. Any change in Domino will result in a change in db2 AND changes to fields in db2 are also saved to the Domino data. The DB2 Access Server regulates access to the data stored in DAVs, using Domino security. Use of DAVs is not required for Domino to store data in DB2, but it is an optional extension of this integration that allows both Domino and DB2 clients to access this data.
Query Views are Domino design artifacts. They are similar to views but use SQL to populate their data sets. A Query View does not require a matching DB2 Access View. You can query any data stored in db2 using a standard SQL statement. The access to the tables in db2 are controlled by the db2 privileges for the tables accessed. Each user that open a Query View gets their own instance of that view, meaning that the SQL is used to create the view - it is not stored like a normal Notes view. This means you can use @username without impacting other users or performance. Selecting all documents in a date range is simple and fast. DB2 Access Views and Query Views both require a DB2 Access Server.
[edit] Installing the DB2 Access Server
Refer to the Domino 7 Release Notes prior to installing DB2 Access for Lotus Domino, for the latest information specific to your version of Domino. You can find the latest Release Notes by visiting www.lotus.com/ldd/notesua.nsf.
The first step in configuring a DB2 Access Server is to create a Domino server ID for it. To do so, open the Domino Administrator and click on the Configuration tab. Click Registration | Server Task in the Tools pane. This prompts the administrator to create the DB2 Access Server ID by either providing a Certifier ID or using the CA process if available.
The following procedure uses a Certifier ID. Open the Basic tab of the Register New Server(s) dialog, and do the following:
- Set the ID file password field to blank when registering a Server ID for a DB2 Access Server.
- Deselect the option to store the server ID in the Domino directory.
- Specify a file path for the server ID. This file will be needed by the DB2 Access Server software.
The Advanced tab of the Register New Server(s) dialog contains a new setting for DB2 Access Server IDs. When registering this server, enable the setting This server is a DB2 Access server only.
Click the green checkmark to add the DB2 Access Server to the registration queue. When you are ready, click Register or Register All to register your server. You are now ready to install the DB2 Access for Lotus Domino software.
Note. The DB2 Access for Lotus Domino software is installed on the DB2 server. This can be the same machine hosting the Domino server in a local DB2 configuration, but is not in remote DB2 configurations. At the time of writing, the DB2 Access for Lotus Domino software is available for download at www.software.ibm.com/webapp/iwm/web/preLogin.do?source=ESD-BETA19.
Ensure that the DB2 Access Server's ID is available on the DB2 server's file system prior to the DB2 Access Server software installation. When you are ready, launch the setup program to start the InstallShield Wizard for DB2 Access for Lotus Domino. Click Next on the splash screen, where you will be given the opportunity to specify where to install the DB2 Access for Lotus Domino software for the DB2 server. The default setting is C:\Program Files\IBM\SQLLIB\FUNCTION. Be sure that this setting matches your DB2 server's FUNCTION directory.
You are then prompted to specify the location of the ID file for the DB2 Access Server. Be sure that this directory is accessible by the DB2 server. If the DB2 server is not on the same machine as the Domino Administrator client that registered the server ID, it may have to be copied to the DB2 server host.
The InstallShield will then echo your installation selections back to you. Click Next if you are ready to install the product. When InstallShield has finished, it displays a dialog box indicating whether it was successful.
This completes the installation of the DB2 Access for Lotus Domino server software. When you are ready to enable the DB2 Access Server, proceed to the Domino server console and enter DB2 ACCESS SET as shown in the following figure.
When this process completes, restart the Domino and DB2 servers. You can now test the DB2 Access Server using the Domino Administrator. To do so, open the DB2 Server tools and select Test DB2 Access.
You should see a dialog similar to the following, if you have set up your DB2 Access Server correctly.
Congratulations! You have installed and configured the DB2 Access Server. You are now ready to create DB2 Access Views and Query Views.
[edit] DB2 Access Views
DB2 Access Views provide a means of pushing out a set of field data from Domino to a DB2 view. After the data arrives in the DB2 view, both Domino and DB2 clients can work with it in a manner consistent with Domino security. DB2 clients can also leverage their tool sets to take advantage of working with the data directly in relational format. The DB2 Access Server governs security. (Remember that it must be installed prior to creating DB2 Access Views.)
[edit] Creating the DB2 Access View Definition
DB2 Access Views are created via Domino Designer 7. To create one, select Shared Resources | DB2 Access Views, and click New DB2 Access View.
This opens the DB2 Access View Designer. You will need to assign a name to your DB2 Access View. This name becomes the name of the DB2 view when it is later created there.
The DB2 Access View's infobox provides several options worth reviewing. There is an option to select which forms' data to include in the DB2 Access View. You can select all forms, or select them individually. There is also an option, Compute with form on DB2 insert or update, which can be applied to inserts or updates that occur to the data via DB2, as well as an option to specify a Default form to use for DB2 inserts in cases where the form field is not set. Setting a default form value ensures that the
data displays using the correct form when viewed via a Notes client.
The DB2 Access View Designer is visually similar to the Outline Designer. You create entries using buttons located at the top of the Designer. Use the Choose Field button to select a field from the Notes database. Use the Insert Field button to manually define data for the DB2 View that is created as a result of this process.
Selecting Choose Field displays a dialog box that allows you to select fields from a list defined for the current database. Fields can be selected from throughout the database, or the list can be filtered to enumerate field definitions based on form, subform, or shared field definitions. The fields selected in this dialog box define the set of data to be pushed into the corresponding DB2 view.
After you have specified the set of field data to be published to the DB2 view, click OK. This adds the set of selected fields to the DB2 Access View Outline.
Note. It may seem tempting to export all of your data using a single DB2 Access View. This puts most data at your fingertips but may make queries against your data more complicated. Defining concise data sets for your DB2 Access View simplifies querying it for data, and allows for greater flexibility when joining it with other data sets.
When you are satisfied with the fields selected in your outline, click Save, and close the DB2 Access View Designer. This returns you to the list of DB2 Access View design elements.
The next step is to propagate your DB2 Access View definition from Domino Designer to the DB2 server. To do this, close the DB2 Access View Designer so that you are returned to the DB2 Access View design view. Select the DB2 Access View you created, and click the Create/Update in DB2 action button.
If this action is successful, you should see a dialog box similar to the following, indicating that the Access Definition was created:
[edit] Populating DB2 with Domino Data
So far, we have only created the DB2 Access View definition. The next step is to populate DB2 with Domino data. To do so, select the DB2 Access View in the design view, and click the Populate in DB2 button.
Clicking this button initiates a request for the Domino server to populate the DB2 view with data. You should see a dialog box similar to the following:
This is an asynchronous process; large data sets could take some time to populate. If the DB2 Access Server is correctly configured, the Domino server console should send messages similar to those appearing in the next illustration, indicating the status of the operation.
The DB2 Access View is fully populated after the DAVPOP task confirms its shutdown.
[edit] Mapping DB2 User Names to Domino
The DB2 Access View has now been populated with data. If you intend to access Domino data in the DB2 Access View using a DB2 client, you should map Domino to DB2 names using the Domino Administrator's DB2 tools. If you do not map DB2 user names to Domino Person documents, the DB2 Access Server will request access to Domino data for DB2 users as Anonymous.
To map a DB2 user to a Domino Person document, open the Domino 7 Administrator, and click the People & Groups tool. Select the Person document belonging to the ID that you want to use to access Domino data in DB2. If you plan to populate the DB2 user name with the Domino shortname field, verify that this field has been populated prior to launching the Set DB2 User Name... tool. You can edit the selected Person document directly within the Domino Administrator if necessary. When ready, click the Set DB2 User Name tool.
This launches the Set DB2 User Name dialog:
If you plan to populate the DB2 user name field using the Domino shortname field, check Copy from shortname field, if available. Also check Make resulting name uppercase, if needed.
Click OK when you have populated the requested input fields. This instructs the Set DB2 User Name tool to populate the selected Person document with a DB2 ID that maps the user to a Domino name. This allows the DB2 Access Server to enforce Domino security for users accessing Domino data via a DB2 client. If this operation is successful, you should see a message similar to the following:
DB2 user names must be unique. If you have a large directory, you may need to run the Validate DB2 User Names tool from the Domino Administrator. When running this tool, the Domino Administrator prompts the user to select the scope of validation.
Select The currently selected Domino Directory option if your DB2 use of Domino data is limited to the primary Domino directory. Select All configured Domino Directories if DB2 client use of Domino data occurs via users in secondary Domino directories.
You can verify the results of this tool by viewing the Person document. Open it to the Administration tab, and verify that the DB2 account name field was updated as requested.
In the preceding example, the DB2 user DB2ADMIN is mapped to the Domino user Domino Administrator/acme when accessing data from a DB2 client. To test accessing your Domino data from DB2, open the DOMINO database in DB2. Select Views, and locate the name of the DB2 Access View. If spaces occur in the name of the DB2 Access View, they are replaced with underscores. In the following illustration, a DB2 user selects the CDS DB2 Access View via the Control Center.
Double-click View to open it. You should see your Domino data as hosted by DB2.
Congratulations! You have created a DB2 Access View and can now access its data from a DB2 client. This allows you to aggregate the DB2 tool set with your Domino data, providing the power and flexibility of SQL along with it. The last design artifact we will look at is the Query View.
[edit] Query Views
Query Views allow you to leverage the data stored in DB2 Access Views within a Domino view using SQL. Query Views require a DB2 Access View to function.
Query Views are created in the same manner as standard Domino views. Open Domino Designer, and select Views. Then click the Create View action. When the DB2 Access Server is enabled, a new radio button option appears for Selection conditions, labeled By SQL Query. Select this radio button to create a Query View. Your SQL must also be enclosed in quotes, or Domino Designer will process your SQL as an @Formula:
When you click OK, the View Designer appears. Query Views contain an additional object labeled SQL Query. Select this to update the SQL your view consists of. Your view can inherit a standard Domino view selection formula if your view was copied from another. Verify that the view selection formula does not contain additional selection criteria that alter the results of your SQL query. If necessary, remove the contents of the View Selection object so that only the SQL query governs the result set. The SQL query object is highlighted in the following:
After you have finished defining your view, Save and close it. Query Views cannot be previewed in Notes. To verify the result set, open the Query View in the Notes client, where you should see your data. Notice that Query Views identify themselves using a different icon from standard Domino views.
Query Views do not store their data in Domino in the same manner that a Domino view caches its data. Query Views are Domino views of data stored in DB2 that are constructed dynamically. Query Views also provide the capability to join data from multiple data sets stored in DB2. This lets you join data from multiple Domino databases, if those databases have all exported their data to a DB2 Access View.
[edit] Summary
In this tutorial, we studied how to use DB2 as a data store for Domino databases. We reviewed the installation and configuration procedures for DB2-based Domino storage. We then looked at how to export Domino data to a DB2 View using DB2 Access Views. We identified the process for mapping DB2 user IDs to Domino user IDs. We then went on to look at accessing Domino data from a DB2 client. Finally, we examined how to work with Domino data using relational design constructs using SQL-based Query Views. At this point, you are ready to leverage the power of DB2 and Domino!
[edit] Source
The source of this content is Chapter 3: Lotus Notes/Domino 7 and DB2 of Domino 7 Lotus Notes Application Development by Tim Speed, Stephen Cooke, Raphael Savir, and Dick McCarrick (Packt Publishing, 2007).
Updated for Domino 8 by Bruce Lill (Kalechi Designs)
