--------------------------------------------------------------------------------
--
-- DB2 / Lotus Notes Table Functions
--
-- This file contains DDL/DML for 2 table functions and 1 DB2 table:
-- (A) AddressBook (VARCHAR(60), VARCHAR(40), TIMESTAMP) for all
-- "People" notes or modified since.
-- (B) AddressBook (VARCHAR(60), VARCHAR(40), VARCHAR(70)) for "People"
-- notes with full text query search.
--
-- Table function implementation and documentation can be found in
-- file lnaddrbk.c
--
-- The parameters in the DML samples are specific for Windows.
--
-- A full text index for a Lotus Notes database can be created the following way:
-- Lotus Notes Workspace -> Database Properties -> Full Text -> Create/Update
-- Index.
--
--------------------------------------------------------------------------------
--
--
--------------------------------------------------------------------------------
--
-- AddressBook Table Function
--
-- Purpose: Retrieve all "People" notes or modified since from Lotus Notes
-- Names and Addressbook on local system or on Notes server.
--
--------------------------------------------------------------------------------
DROP FUNCTION AddressBook (VARCHAR(), VARCHAR(), TIMESTAMP);
CREATE FUNCTION AddressBook (VARCHAR(60), VARCHAR(40), TIMESTAMP)
--
-- 1st parameter: Lotus Notes Server:
-- server name or
-- '' or CAST (NULL as VARCHAR(60)) for local
-- !!! options for providing Lotus Notes password:
-- (A) Share Password with Notes Add-Ins: In Notes Release 4, there is
-- a new user ID option in the File - Tools - User ID InfoBox to
-- enable sharing of the Notes password with other processes. When you
-- select this option, once one process on a machine has authenticated
-- the Notes user ID, all other processes inherit that
-- authentication. For example, if the user launches the Notes user
-- interface and provides a password, that user can then launch a
-- Notes C API application and the application does not prompt for the
-- password.
-- (B) Run DB2 server as user process (db2start/d). Lotus Notes pops
-- up a Window requesting a password if necessary.
-- (C) Run DB2 server as system process (db2start). Make sure that
-- service can interact with desktop (see NT service panel Startup
-- option). Lotus Notes pops up a Window requesting a password if
-- necessary.
-- 2nd parameter: NSF addressbok file name:
-- relative path within Notes data directory if server or
-- absolute path if local
-- 3rd parameter: Modified Since:
-- timestamp since for modified or deleted notes or
-- cast(null as timestamp) for all (wild card)
--
RETURNS TABLE
(
UNID CHAR(16) FOR BIT DATA,
Title VARCHAR (5 ),
FirstName VARCHAR (40 ),
MiddleInitial VARCHAR (20 ),
LastName VARCHAR (70 ),
Suffix VARCHAR (5 ),
JobTitle VARCHAR (270),
CompanyName VARCHAR (180),
BusinessAddress VARCHAR (60 ),
OfficeZIP VARCHAR (15 ),
OfficeCountry VARCHAR (30 ),
OfficeStreetAddress VARCHAR (80 ),
OfficeCity VARCHAR (20 ),
OfficeState VARCHAR (25 ),
StreetAddress VARCHAR (60 ),
City VARCHAR (70 ),
State VARCHAR (25 ),
AreaCodeFromLoc VARCHAR (20 ),
OfficePhoneNumber VARCHAR (230),
OfficeFAXPhoneNumber VARCHAR (140),
CellPhoneNumber VARCHAR (140),
PhoneNumber VARCHAR (60 ),
HomeFAXPhoneNumber VARCHAR (30 ),
PhoneNumber_6 VARCHAR (40 ),
MailAddress VARCHAR (80 ),
PhoneLabel_1 VARCHAR (10 ),
PhoneLabel_3 VARCHAR (10 ),
PhoneLabel_5 VARCHAR (10 ),
PhoneLabel_2 VARCHAR (10 ),
PhoneLabel_4 VARCHAR (10 ),
PhoneLabel_6 VARCHAR (10 ),
WebSite VARCHAR (70 ),
HomeAddress VARCHAR (20 ),
DisplayHomeAddress VARCHAR (20 ),
Zip VARCHAR (35 ),
country VARCHAR (40 ),
Categories VARCHAR (30 ),
Location VARCHAR (240),
Department VARCHAR (100),
Manager VARCHAR (70 ),
Assistant VARCHAR (70 ),
Spouse VARCHAR (50 ),
Children VARCHAR (35 ),
FullName VARCHAR (100),
MailDomain VARCHAR (10 ),
MailSystem VARCHAR (10 ),
ShortName VARCHAR (160),
Type VARCHAR (15 ),
DocumentAccess VARCHAR (20 ),
"$UpdatedBy" VARCHAR (20 ),
"$Revisions" VARCHAR (20 )
)
EXTERNAL NAME 'lnaddrbk!DB2NSFSearch'
SPECIFIC DB2NSFSearch
LANGUAGE C PARAMETER STYLE DB2SQL
SCRATCHPAD FINAL CALL
NOT FENCED NULL CALL
DETERMINISTIC NO SQL NO EXTERNAL ACTION
DISALLOW PARALLEL DBINFO;
--------------------------------------------------------------------------------
--
-- AddressBook Table Function
--
-- Purpose: Full text search all "People" notes from Lotus Notes
-- Names and Addressbook on local system or on Notes server.
--
-- Requires full text index for addressbook (database property)
--
--------------------------------------------------------------------------------
DROP FUNCTION AddressBook (VARCHAR(), VARCHAR(), VARCHAR());
CREATE FUNCTION AddressBook (VARCHAR(60), VARCHAR(40), VARCHAR(70))
--
-- 1st parameter: Lotus Notes Server:
-- server name or
-- '' or CAST (NULL as VARCHAR(60)) for local
-- !!! Options for providing Lotus Notes password:
-- (A) Share Password with Notes Add-Ins: In Notes Release 4, there is
-- a new user ID option in the File - Tools - User ID InfoBox to
-- enable sharing of the Notes password with other processes. When you
-- select this option, once one process on a machine has authenticated
-- the Notes user ID, all other processes inherit that
-- authentication. For example, if the user launches the Notes user
-- interface and provides a password, that user can then launch a
-- Notes C API application and the application does not prompt for the
-- password.
-- (B) Run DB2 server as user process (db2start/d). Lotus Notes pops
-- up a Window requesting a password if necessary.
-- (C) Run DB2 server as system process (db2start). Make sure that
-- service can interact with desktop (see NT service panel Startup
-- option). Lotus Notes pops up a Window requesting a password if
-- necessary.
-- 2nd parameter: NSF addressbok file name:
-- relative path within Notes data directory if server or
-- absolute path if local
-- 3rd parameter: Lotus Notes Full Text Search Query:
-- full text query string or NULL for all
--
RETURNS TABLE
(
UNID CHAR(16) FOR BIT DATA,
Title VARCHAR (5 ),
FirstName VARCHAR (40 ),
MiddleInitial VARCHAR (20 ),
LastName VARCHAR (70 ),
Suffix VARCHAR (5 ),
JobTitle VARCHAR (270),
CompanyName VARCHAR (180),
BusinessAddress VARCHAR (60 ),
OfficeZIP VARCHAR (15 ),
OfficeCountry VARCHAR (30 ),
OfficeStreetAddress VARCHAR (80 ),
OfficeCity VARCHAR (20 ),
OfficeState VARCHAR (25 ),
StreetAddress VARCHAR (60 ),
City VARCHAR (70 ),
State VARCHAR (25 ),
AreaCodeFromLoc VARCHAR (20 ),
OfficePhoneNumber VARCHAR (230),
OfficeFAXPhoneNumber VARCHAR (140),
CellPhoneNumber VARCHAR (140),
PhoneNumber VARCHAR (60 ),
HomeFAXPhoneNumber VARCHAR (30 ),
PhoneNumber_6 VARCHAR (40 ),
MailAddress VARCHAR (80 ),
PhoneLabel_1 VARCHAR (10 ),
PhoneLabel_3 VARCHAR (10 ),
PhoneLabel_5 VARCHAR (10 ),
PhoneLabel_2 VARCHAR (10 ),
PhoneLabel_4 VARCHAR (10 ),
PhoneLabel_6 VARCHAR (10 ),
WebSite VARCHAR (70 ),
HomeAddress VARCHAR (20 ),
DisplayHomeAddress VARCHAR (20 ),
Zip VARCHAR (35 ),
country VARCHAR (40 ),
Categories VARCHAR (30 ),
Location VARCHAR (240),
Department VARCHAR (100),
Manager VARCHAR (70 ),
Assistant VARCHAR (70 ),
Spouse VARCHAR (50 ),
Children VARCHAR (35 ),
FullName VARCHAR (100),
MailDomain VARCHAR (10 ),
MailSystem VARCHAR (10 ),
ShortName VARCHAR (160),
Type VARCHAR (15 ),
DocumentAccess VARCHAR (20 ),
"$UpdatedBy" VARCHAR (20 ),
"$Revisions" VARCHAR (20 )
)
EXTERNAL NAME 'lnaddrbk!DB2FTSearch'
SPECIFIC DB2FTSearch
LANGUAGE C PARAMETER STYLE DB2SQL
SCRATCHPAD FINAL CALL
NOT FENCED NULL CALL
DETERMINISTIC NO SQL NO EXTERNAL ACTION
DISALLOW PARALLEL DBINFO;
--------------------------------------------------------------------------------
--
-- AddressBook Table
--
--------------------------------------------------------------------------------
DROP TABLE addressbook;
CREATE TABLE addressbook
(
UNID CHAR(16) FOR BIT DATA,
Title VARCHAR (5 ),
FirstName VARCHAR (40 ),
MiddleInitial VARCHAR (20 ),
LastName VARCHAR (70 ),
Suffix VARCHAR (5 ),
JobTitle VARCHAR (270),
CompanyName VARCHAR (180),
BusinessAddress VARCHAR (60 ),
OfficeZIP VARCHAR (15 ),
OfficeCountry VARCHAR (30 ),
OfficeStreetAddress VARCHAR (80 ),
OfficeCity VARCHAR (20 ),
OfficeState VARCHAR (25 ),
StreetAddress VARCHAR (60 ),
City VARCHAR (70 ),
State VARCHAR (25 ),
AreaCodeFromLoc VARCHAR (20 ),
OfficePhoneNumber VARCHAR (230),
OfficeFAXPhoneNumber VARCHAR (140),
CellPhoneNumber VARCHAR (140),
PhoneNumber VARCHAR (60 ),
HomeFAXPhoneNumber VARCHAR (30 ),
PhoneNumber_6 VARCHAR (40 ),
MailAddress VARCHAR (80 ),
PhoneLabel_1 VARCHAR (10 ),
PhoneLabel_3 VARCHAR (10 ),
PhoneLabel_5 VARCHAR (10 ),
PhoneLabel_2 VARCHAR (10 ),
PhoneLabel_4 VARCHAR (10 ),
PhoneLabel_6 VARCHAR (10 ),
WebSite VARCHAR (70 ),
HomeAddress VARCHAR (20 ),
DisplayHomeAddress VARCHAR (20 ),
Zip VARCHAR (35 ),
country VARCHAR (40 ),
Categories VARCHAR (30 ),
Location VARCHAR (240),
Department VARCHAR (100),
Manager VARCHAR (70 ),
Assistant VARCHAR (70 ),
Spouse VARCHAR (50 ),
Children VARCHAR (35 ),
FullName VARCHAR (100),
MailDomain VARCHAR (10 ),
MailSystem VARCHAR (10 ),
ShortName VARCHAR (160),
Type VARCHAR (15 ),
DocumentAccess VARCHAR (20 ),
"$UpdatedBy" VARCHAR (20 ),
"$Revisions" VARCHAR (20 )
);
CREATE INDEX firstname_index ON addressbook (firstname);
CREATE INDEX lastname_index ON addressbook (lastname);
--------------------------------------------------------------------------------
--
-- Sample Queries
--
--------------------------------------------------------------------------------
-- Retrieve firstname, lastname
-- from Lotus Notes
-- on Windows
SELECT firstname, lastname
FROM TABLE (addressbook('',
'e:\sqllib\samples\ole\msvb\names.nsf',
cast(null as timestamp))) as n;
-- on UNIX
SELECT count(lastname)
FROM TABLE (addressbook('',
'/localfs/reinwald/ibmca.nsf',
cast(null as timestamp))) as n;
-- Lotus Notes full text search query
-- and retrieve firstname, lastname
SELECT firstname, lastname
FROM TABLE (addressbook('',
'e:\sqllib\samples\ole\msvb\names.nsf',
'Hamid and Pirahesh')) as n;
-- Insert Lotus Notes address book
-- into DB2 table
INSERT INTO addressbook
SELECT *
FROM TABLE (addressbook('',
'e:\sqllib\samples\ole\msvb\names.nsf',
cast(null as timestamp))) as n;
-- This view gives the most used part of DB2 addressbook
-- table. Fullname is formatted so that it can be cut and paste into
-- Lotus Notes mail.
DROP VIEW ebook;
CREATE VIEW ebook AS
WITH dt AS
(SELECT fullname, shortname, firstname, lastname, location,
POSSTR(fullname, '/OU=') fnpart2,
POSSTR(fullname, '/O=') fnpart3,
maildomain
FROM addressbook)
SELECT CAST (SUBSTR(fullname, 4, fnpart2-4+1) ||
SUBSTR(fullname, fnpart2+4, fnpart3-fnpart2-4+1) ||
SUBSTR(fullname, fnpart3+3) ||
'@' || maildomain
AS VARCHAR(50)) AS ln_email,
CAST (lastname AS VARCHAR(20)) AS lastname,
CAST (firstname AS VARCHAR(20)) AS firstname,
CAST (location AS VARCHAR(25)) AS location,
CAST (shortname AS VARCHAR(20)) AS shortname
FROM dt;
-- Export from DB2 addressbook table to compact address book in ASCII
-- file. It is specially useful to look up addresses while not
-- connected to the server.
EXPORT TO c:\temp\addr.txt OF DEL
WITH dt AS
(SELECT fullname, lastname, firstname, location, shortname,
POSSTR(fullname, '/OU=') fnpart2,
POSSTR(fullname, '/O=') fnpart3,
maildomain,
rownumber() over() AS rownumber
FROM addressbook)
SELECT CASE WHEN fnpart2 > 0 AND fnpart3 > 0
THEN SUBSTR(fullname, 4, fnpart2-4+1) ||
SUBSTR(fullname, fnpart2+4, fnpart3-fnpart2-4+1) ||
SUBSTR(fullname, fnpart3+3) ||
'@' || maildomain
END AS fullname,
lastname, firstname, location, shortname
FROM dt
ORDER BY lastname;
--
-- Same export as above but exports from Lotus Notes table function.
--
EXPORT TO c:\temp\addr.txt OF DEL
WITH dt AS
(SELECT fullname, lastname, firstname, location, shortname,
POSSTR(fullname, '/OU=') fnpart2,
POSSTR(fullname, '/O=') fnpart3,
maildomain,
rownumber() over() AS rownumber
FROM TABLE (addressbook('',
'e:\sqllib\samples\ole\msvb\names.nsf',
cast(null as timestamp))) as n;
SELECT CASE WHEN fnpart2 > 0 AND fnpart3 > 0
THEN SUBSTR(fullname, 4, fnpart2-4+1) ||
SUBSTR(fullname, fnpart2+4, fnpart3-fnpart2-4+1) ||
SUBSTR(fullname, fnpart3+3) ||
'@' || maildomain
END AS fullname,
lastname, firstname, location, shortname
FROM dt
ORDER BY lastname;
--
-- Retrieve modified and deleted addresses since 2 days ago
--
select lastname
from table (addressbook('',
'e:\sqllib\samples\ole\msvb\names.nsf',
current timestamp - 2 day)) as t;
--
-- Retrieve lastname from Lotus Notes server with Lotus Notes address
-- book located relative to Notes data directory on server.
select lastname
from table (addressbook('d29cn12.research.ibm.com/Almaden/IBM',
'names.nsf',
cast(null as timestamp))) as t;
select count (*)
from table (addressbook('D03NMX41/03/M/IBM',
'names.nsf',
cast(null as timestamp))) as t;
--
-- Full text search 'Wilson or Pirahesh'
--
select firstname, lastname
from table (addressbook ('',
'e:\sqllib\samples\ole\msvb\names.nsf',
'Wilson or Pirahesh')) as t;