wareSource

DB2 for Domino 8 Administrators and Developers

Course Code: DB2D8
Product: Lotus Notes/Domino R8
Duration: 2 days
Topics/Pages: 12 topics / 332 pages
Price (US dollars):
$45 per user

Description

An exciting feature of Domino is its ability to use DB2 as an alternate data store. While this opens up expansive vistas for both Domino and DB2 shops, it requires Domino administrators and developers not only to learn how to work with DB2 but how it works with Domino.

During this course you will learn the essentials of DB2 installation, configuration, and security. You will learn how to interact with the DB2 server using the DB2 Control Center and query DB2 tables using the Command Editor and SQL Assist. The course then turns to implementing DB2 as the Domino data store. You will create DB2 Access Views that give external SQL applications access to Domino data and create Query Views that give Notes access to DB2 data.

Course goals

In this course, you will learn how to:

  • Build a test/development environment that demonstrates the full capabilities of Domino and DB2 integration.
  • Install the DB2 server and monitor its status and health using various administration tools.
  • Use the DB2 Control Center to configure the server and the License Center to apply a license file.
  • Create a sample DB2 database and use graphical tools to open and query the database using SQL.
  • Configure Domino to use DB2 as its data store.
  • Install and configure the DB2 Access Server and create several DB2 Access Views to make Domino data available to external SQL applications.
  • Create Query Views to give Notes access to DB2 federated data.


Audience

This course is designed for individuals who are new to DB2, relational databases, and SQL but who have experience programming Notes applications using Domino Designer 8 and experience administering Domino using Domino Administrator 8.

This course provides enough DB2, relational database, and SQL knowledge to set up a test/development environment in which you can begin to work with DB2 databases and Domino 8. This course does not, however, fully prepare you to become a DB2 administrator.

Course design

This is a lecture/lab course that follows a task-oriented approach. During the many exercises you will create a working test/development environment where you will work with the all aspects of the Domino 8/DB2 integration.

Outline


Topic 1: Domino and DB2 Overview

A history of Domino data storage
A birds-eye perspective of DB2
The present and future of Domino data storage
How Domino data is stored in DB2
Notes and DB2
Local or remote DB2 Server
Benefits that DB2 brings to Domino
Benefits that Domino brings to DB2
DB2 resources


Topic 2: Install DB2 Server 

Classroom environment
DB2 on Windows platform requirements
Obtain DB2 software installation packages
Extract DB2 Server installation packages
Firewalls and DB2
Instructor Demonstration: Install DB2 Server product
Two types of DB2 commands
Command Line Tools
Instructor Demonstration: Add license key
Instructor Demonstration: Create SAMPLE database
Instructor Demonstration: List databases
DB2 Control Center
Instructor Demonstration: Confirm license key
DB2 Control Center Object View tab

Topic 3: Administer DB2

Client platform requirements
Obtain DB2 (Administration) Client installation package
Exercise: Install DB2 (Administration) Client
DB2 Security
Assign system Administrators
DB2 security and NSFDB2s
Database object right-click actions
Database status
Database backup and recovery
Health Center
Configuration files
Send alerts to administrative contact(s)
Activity Monitor
Journal
Resources


Topic 4: Define Table 

Table columns
Alter table
Add Column
Add predefined column
DB2 limits
Keys tab
Check Constraints tab
Table tab


Topic 5: Open Table 

View table data
Open Table window actions
Modify table data
Exercise: Add a row
Filter rows
Logical expressions
Exercise: Apply a filter


Topic 6: Query from the Command Editor

Open Table window versus the Command Editor
SQL queries
SQL language rules and conventions
SQL built-in functions
Qualified and correlation names
Execute SQL query from the Command Editor
Exercise: Execute SQL query
Build a query using the SQL Assist
Exercise: SQL Assist
GROUP BY
Multiple table queries
Three table join
Alternate join syntax
Join tables using SQL Assist
Outer join types
Optional exercise: Perform additional queries
What’s next


Topic 7: Install DB2 Runtime Client

Procedure 1 (Instructor): Create Domino account on DB2 server
Procedure 2 (Instructor): Add Domino user to DB2ADMNS
Procedure 3 (Everyone): Install Runtime Client on Domino
Procedure 4 (Instructor): Display DB2 communication parameters
Configure Runtime Client to connect to DB2
Procedure 5 (Everyone): Catalog the TCP/IP node from the DB2 client
Procedure 6 (Everyone): Attach to the TCP/IP node from DB2 client
Procedure 7 (Everyone): Catalog a database from the DB2 client
Procedure 8 (Everyone): Connect to a database from the DB2 client
Procedure 9 (Everyone): Issue SQL command from the DB2 client
What’s next


Topic 8: Configure Domino to Use DB2 

Caution: Before you configure Domino to use DB2
Procedure 1 (Everyone): Enable transaction logging on Domino
Procedure 2 (Everyone): Run the Enable Server for DB2 tool
Change 1: TCP/IP Node added for DB2 Server
Change 2: DB2 container database created and cataloged
Change 3: DB2 name and password added to Server ID file
Change 4: NOTES.INI variables added
Change 5: Server document changes
Change 6: Domino startup messages
Change 7: Domino recognizes DB2 and default database type
Exercise: Create new NSFDB2
Exercise: Explore container database in the DB2 Control Center
Exercise: Create Form and View in Support Contacts
Convert existing NSF to NSFDB2
Change default database type back to NSF
DB2-related tools in Domino Administrator
Performance tuning resources


Topic 9: Install DB2 Access Server 

The big picture
DB2 Access Server overview
Procedures to install the DB2 Access Server
Procedure 1 (Instructor): Create a Domino Server ID for DB2 server
Procedure 2 (Instructor): Install DB2 Access Server on DB2
Procedure 3 (Everyone): Edit your Domino Servers Server document
Procedure 4 (Everyone): Run DB2 ACCESS SET console command
Procedure 5 (Everyone): Verify configuration of DB2 Access Server
DAV security overview
DB2 user mapping to Domino user
Set DB2 Username tool
Procedure 6 (Everyone): Map DB2 user to Domino user names
Validate DB2 User Names tool
Allow anonymous access to Notes data in DAVs


Topic 10: Create DB2 Access Views 

DB2 Access Views overview
Domino data translation
Procedures to create a DAV
Procedure 1: Create the DAV design
Procedure 2: Choose or insert fields
Procedure 3: Set DB2 Access View properties
Procedure 4: Save and validate the DAV
Procedure 5: Create/Update the table in DB2
Procedure 6: Populate in DB2
Exercise: Design DAV
Procedure 7: Grant access to the DB2 view
Exercise: Grant privileges to DAV user
Debugging the DB2 Access Server
Uninstall DB2 Access Server
Inserts, updates, and deletes


Topic 11: Create Query Views 

Query Views overview
Grant SETSESSIONUSER privilege to Domino Server user
Exercise (Everyone): Grant SETSESSIONUSER privilege to Domino Server user
Create a QV
SQL Query
Exercise: Create QV of one DAV
Limit number of rows returned by QVs
QV of two DAVs
Exercise: Create new Form and View in Support Contacts
Exercise: JOIN two DAVs
Using QVs in Notes applications
Set DB2 default user


Topic 12: Create Query Views of Federated Data 

Enable remote DB2 database object federation
Procedure 1 (Instructor): Allow Domino to set session authorization
Procedure 2 (Instructor): Enable DB2 instance to support federation
Procedure 3 (Everyone): Create wrapper for external data source
Procedure 4 (Everyone): Create external server definition
Server definition object folders
Procedure 5 (Everyone): Map DB2 user name to remote user name
Procedure 6 (Everyone): Create nickname to external table/view
Procedure 7 (Instructor): Grant SELECT privilege to remote table
Procedure 8 (Everyone): Grant SELECT privilege to nickname table
End-to-end federation and QV settings
Exercise: QV of the EMPLOYEE table
Exercise: QV to JOIN a DAV and EMPLOYEE table