Transcript DNUG-CeBIT-110305.ppt

Notes/Domino Rel-7 und
NSFDB2 - Neue Potenziale bei
der Integration von
Dokumenten und Daten
Dr. Ludwig Nastansky
Professor of Business Computing
Groupware Competence Center
University of Paderborn, Germany
Profil
 Prof.






Dr. Ludwig Nastansky
Professor of Business Information Systems
head of Groupware Competence Center
in N/D research & business since N/D Rel-1
founder of several university spin-offs
co-founder of German Notes User Group
founder & chairman supervisory board PAVONE AG
Agenda


1 Einführung
2 Domino Access Views (DAV)

Szenarios
 Technologie, Tips
 N/D  DB2 Paradigma Tabellenschnittstelle
 live demo

3 Query Views





Szenarios
Technologie, Tips
Paradigma „federated data“
live demos
4 Zusammenfassung
1 Introduction
 Focus
on DAV & Query Views in NSFDB2
 Goals





 No
put N/D  DB2 integration in right context
show innovative & efficient N/D  DB2 integration
options
envision new approaches for N/D & DB2 application
development
start thinking about required changes in infrastructure
take home tips for first implementations
NSFDB2 basics will be covered
 Presentation mainly based on beta 2 code
Notes/Domino & DB2 Integration, finally… ?






New RDB-functionalities provided by DAV & Query Views
RDB functionalities in N/D have been an issue since N/D
Rel-2
Many successful approaches for N/D  RDB connectivity
have appeared: DECS, LEI, SAP connector, …
NSFDB2 is the smoothest and most radical integration
ever
Besides, that the DB2 guys will finally admit that N/D is
based on a real DB-engine …
… the core issues are unchanged


N/D  RDB integration is not (only) about technology
but (much more) about application domains, IT-strategies,
top-down system architectures, functionality concepts
Documents vs. Relational Data
N/D world








strategic orientation & communication
centric
knowledge & information
management
tool paradigm on objects – code reuse
compound documents, semistructured, very flexible data types
multimedia, links, embedded methods
decentralized, buttom-up, user
workplace & collaboration centric
replication, information sharing,
robust distribution, redundancy,
message objects
support of mobile, nomadic and
disconnected user workplace
DB2/RDB world

operational orientation & data centric

transaction processing & high volume

automation paradigm on data – code
efficiency
records, tables, structured data,
restricted flexibility, strict formats
transactions, dynamic
central organization, top-down,
system centric
access coordination, integrity,
redundancy elimination, 2-phase
commit, ACID
static office-based and serverconnected workplace





Integration, Cooperation, Federation

Challenge: bring together the best of the two worlds



Position: N/D  RDB/DB2 integration is just one instance of the
innumerable system integration tasks taking place currently
The integration is between equal partners


stay cool, no religion involved, don't demonize
Purpose




as much as needed and as much as makes sense from a business
value perspective
integration of data across system boundaries
cooperation of separate applications
federation of N/D applications with DB2-data and, vice versa, DB2
applications with N/D data
NSFDB2 brings RDB/SQL functionalities to N/D – let us start with
one principal N/D-modelling challenge using a typical scenario
Scenario 1 - Model Employee–Room Relation
Start: two
independent
lists
Goal: model
Employee-Room
relation
#1
#2
Solution in N/D is clumsy
We need to hard-code relational
dependancy by duplicating data in
documents and thus creating redundancy
- Option #1: Lookup "LastName" in
"Employees" view and save in Room-form
- Option #2: Lookup "Room" in "Room list"
view and save in Employee-form
- [Option #3: Enforce consistent user entry
in related documents]
N/D challenge: Model Employee–Room Relation

Problem:

View "Employee in room" cannot be generated without duplicating
"Room"-field and "LastName"-field in "Employee"-document and
"Room"-document respectively
 reason: @LookUp not allowed in view columns


Synchronization has to be modeled
Modelling the synchronization is possible, but clumsy, e.g.

use scheduled agents to collect & update changes
 set up (very) disciplined user entry infrastructure enforcing
consistent update over involved document collection


Avoiding redundancy of field entries would help considerably
Solution with new options of NSFDB2: Data duplication is not
necessary using Domino Access Views (DAV) and Query Views
DAV vs. Query Views
update, insert, delete
DAV
NSFNote Table
redundantly
store
Domino
Access
Access
DB2
Views
Views
Table
UDF
Query
View
Query
Query
N/D
Views
Views
View
Access
Access
DB2
Views
Views
View
read
Applications
SQL
read
DB2NSF
read
DB2
Access
DB2
Access
Views
Data
Views
Table
DB2 data flow
Domino data flow
DAV related data flow
Control
Agenda

1 Introduction
2

Domino Access Views (DAV)
 scenario
 technology,
 live demo
3 Query Views





tips
scenarios
technology, tips
federated data paradigm
live demos
4 Summary
DAV - Overview





Redundantly store N/D data in DB2 table
DB2 view that corresponds to the table is used for SQL
operations
DAV is not a N/D UI element, i.e. not N/D standard view
DAV is tabular cross-reference entity for N/DDB2 data
exchange; N/D-view analogue, but not accessible in Notes client
Expose N/D data to






SQL applications
JDBC, ODBC
DB2 tools and applications
relational reporting tools (Crystal Reports etc.)
Query Views
No DECS, LEI, connectors needed
DAV Architecture
update, insert, delete
DAV
NSFNote Table
redundantly
store
Domino
Access
Access
DB2
Views
Views
Table
UDF
stored in
Domino
Designer
design
DAV Design
Document
Access
Access
DB2
Views
Views
View
define
Applications
SQL
read
DB2NSF
DB2
Domino data flow
DAV related data flow
Control
DAV Data: N/DDB2 Interaction
update, insert, delete
DAV
NSFNote Table
redundantly
store
Domino
form based
selection
stored in
Domino
Designer
design
user based
selection
DAV Design
Document
Access
Access
Views
Views
DB2 Table
Access
Access
Views
Views
DB2 View
define
UDF
Applications
SQL
read
DB2NSF
DB2
Domino data flow
DAV related data flow
Control
DAV – Field Definition
DAV Design
Document
Field 1
Field 2
Field 3
Field 4
Field 5
Field 6
etc. …
Fields are elements
of forms
Fields are items
in notes
New fields, that do not
exist in N/D database
yet
DAV – Architecture Summary

Using SQL, you can

read N/D data with security semantics enforced
 from an SQL perspective, this adds "row level security" to
DB2 data
 insert, update, delete with full N/D semantics


DB2 handles security for read operations (fast)
DB2 Access for Lotus Domino (aka UDF server) handles
N/D security on insert, update, delete




DAV calls UDF server
Domino handles replication conflicts, document locking etc.
Domino handles ACL, reader fields etc.
user mapping required for security
(Domino Administrator)
Scenario 2: Integration #1

Application and data integration demands for N/D and
DB2, e. g.







analysis using MIS
reporting using Crystal Reports
HR uses existing DB2 application
salesforce uses mobile N/D application
salesforce orders are processed by DB2 application
customer uses J2EE application with DB2 backend to file
orders
Challenge

integration of applications
 synchronization of data
Scenario 2: Integration #2

Solution: integration via DAV

make N/D document based data available for corporate SQL
applications via DAV:
 expose N/D data to DB2 for read and write

populate N/D documents with DB2 based data from SQL
applications via DAV:
 make DB2 data available for N/D documents
 make DB2 data available in flexible N/D view context

Benefits

enrich N/D environment with DB2 based application options
 enrich DB2 environment with N/D based application options
Scenario 2: Mobile Salesforce
 Salesforce
uses mobile N/D application
 Orders are replicated into central NSFDB2
 DB2 application works on data exposed via DAV


N/D adds offline functionality to DB2 via replication
Transactions are processed in DB2
 Salesforce
replicates transaction status back to
mobile device
Demo 1: DAV Design, Use & Remarks
 Design
a Domino Access View
 Create and populate DAV
 Look at created elements in DB2
 Modify data from SQL application
Updated by DB2 application
DAV Design - #1
 Creation
in DB2 via Domino Designer
 New shared resource
 Database has to be in NSFDB2 format
 Define view selection based on 1 or more forms
 Define one form to calculate fields
for insert and update operations
DAV Design - #2
 Define


advanced properties
include UNID to be able to open documents in Query
views
modified time
DAV Design - #3
 Select
form
 Select N/D fields
 Modify selected field definitions


DB2 data type (automatic default mapping)
modify column length if you expect many values
DAV Design - #4
 Create
DAV in DB2
 Populate DAV


asynchronous background task DAVPOP
can take a long time in large databases
DAV Use
DAV Remarks - #1
 Structure
clash between N/D flexibility
and restricted DB2 tables



DB2 requires fixed column/field length
especially N/D text fields have to be considered
do not modify truncated data
 Think
about multi value handling
 List fields



reduce DB2 column length
sum length for multi value fields
use alias if possible
DAV Remarks - #2
 UDF
server needed
 Design document replicates in NSF, but is not
visible locally
 Does not support formula, rich text and
rich text light data types
 Include only fields you really need
DAV Summary
 Domino
Designer as a development tool for DB2
 Expose N/D data to SQL applications
 Make N/D functionalities easily available for DB2
applications, examples:


N/D semantics offer additional value to SQL
applications ("row level security")
enrich DB2 with disconnected options
 Potential
to lower cost for application
integration with existing infrastructure
Agenda


1 Introduction
2 Domino Access Views (DAV)

scenario
 technology, tips
 live demo
3
Query Views
 scenarios

 technology, tips
 federated data paradigm
 live demos
4 Summary
Query Views - Overview
 N/D
views which are enabled for SQL queries
 SQL queries are used in view selection formula
context
 Usage examples




filter documents (dynamic view selection formula)
add DB2 data to a N/D view
use retrieved DB2 data in column formulas
combine N/D data with external DB2 data
Query Views - Design
DAV
NSFNote Table
redundantly
store
Domino
Query
View
Query
Query
N/D
Views
Views
View
design
Domino
Designer
stored in
Query View
Design
Document
SQL Query
DB2
SQL Query
Access
Access
DB2
Views
Views
Table
Access
Access
DB2
Views
Views
View
DB2NSF
Access
DB2
Access
Views
Data
Views
Table
Domino data flow
Control
Query Views – Data Flow
DAV
NSFNote Table
redundantly
store
Domino
Access
Access
DB2
Views
Views
Table
UDF
Query
View
Query
Query
N/D
Views
Views
View
Access
Access
DB2
Views
Views
View
read
DB2NSF
read
DB2
Access
DB2
Access
Views
Data
Views
Table
DB2 data flow
Domino data flow
DAV related data flow
Control
Query Views – Data Federation #1

Rows can be populated by

N/D documents in the current database (via DAV only)
 DAV data in DB2 not in the current database
 DB2 data
 combinations between these via SQL JOIN

Thus Query Views can display data from

current N/D database exposed by DAV
 other N/D databases exposed by DAV
 N/D documents exposed by DAV, with additional data via
SQL JOIN
 Native (non N/D originating) DB2 data

Keep this in mind during clicking rows
Query Views – Data Federation #2
 "Federated
data" do not originate from current
N/D database
 A row can contain document values plus
federated data

"normal" N/D field content
 has to be included in DAV
 has to be included in SQL selection



result of a column formula calculation
DB2 data objects retrieved using SQL Query
"double click" yields opening of document
Query Views – Data Federation #3
A
row can be defined by federated data only



all values result from DB2 data objects
"double click" on row makes no sense (in most
cases) and yields error message
can be used for data consolidation from different NSF
files
 same application
 different application
 see "Scenario 5"
Query Views – SQL Query Rules


Query is defined in N/D formula language context
Query supports standard SQL

SQL JOIN
 SQL UNION
 ORDER BY
 etc.

Queries that do not produce a result set are not allowed

security mechanism
 prevents deletion/update from view
Query Views Are Dynamic
 No




persistent view index involved
efficient DB2 indexing is used
queries can be user specific
parameterized and personalized
lookups are allowed to collect N/D data for query
construction
Scenario 3 – Federated Data in Views




HR uses N/D to manage resource data
Everyone can view employee name and phone number
Only HR is allowed to view salary
Security challenge

field encryption requires complex key management
 mechanisms such as "hide when" are not security features
 @DBLookup is not supported in column formulas

Solution

store data in different database or document
 use Query Views and SQL JOIN to add federated data
SQL JOIN
A
Query View D1
NSFDB2
EmpUSA.nsf
DAV
EMPLOYEE
EMPID,
NAME,
PHONE
B
NSFDB2
Salary.nsf
DAV
SALARY
DB2
EMPID,
SALARY
NAME
PHONE
SALARY
EMPID
Record Set 1
Record Set 2
Record Set n
Result Set of:
“SELECT
A.NAME , A.PHONE , B.SALARY, A.EMPID
FROM
EMPUSA.EMPLOYEE A
LEFT OUTER JOIN SALARY.SALARY B
ON A.EMPID = B.EMPID
ORDER BY
A.NAME”
Demo 2
 Design
a Query View
 JOIN data from other N/D database
Data from other database
Remarks
 In
most N/D scenarios, LEFT OUTER
JOIN is most applicable


 Do
ensures all documents are displayed
leaves column empty if federated data does not match
not include multiple NoteID fields in result
set


use specific selection instead of *
wrong document might be opened
Scenario 4 – Dynamic Selection






Workflow application
Very large number of documents
Users only need a small subset of documents
Users want to select which documents to display
Users want profile based personal selection settings
Challenge (in N/D context)

@DBLookup in selection formula is not possible
 @UserName only works in private views
 bad performance due to large view index

Solution

Query Views don't have a static view index
 Query statement(s) can be based on N/D dynamic formula
mechanism
Demo 3
 Design
a Query View
 Dynamic selection using @Prompt
 Dynamic user preference
User selected data
Query Views – SQL UNION application
 Dynamic
formulas allow user specific
aggregations
 Aggregate documents from multiple N/D
databases into "single point of access"
 Returns federated data sets and documents
 Allows for design of aggregated views
SQL UNION – Aggregated Views
NSFDB2
Asia.nsf
DAV
Employee
Notes Client
DB2
NSFDB2
EMEA.nsf
DAV
Employee
SQL Query View
DAV
Employee
NSFDB2
USA.nsf
"Select A.Lastname, A.Firstname from Asia.Employee A
UNION
Select B.Lastname, B.Firstname from EMEA.Employee B
UNION
Select C.Lastname, C.Firstname from USA.Employee C"
Scenario 5 – Aggregated Views



User tasks are dispersed over multiple workflow
and project management applications
User is active in multiple teamrooms
Challenge



user has to open many databases to get his job done
user is not up to date if new tasks appear
Solution

a single view displays all documents a user needs from
multiple databases
 use Query View and SQL UNION to display federated
(virtual) "documents"
 retrieving originating (real) documents accessible needs
specific attention…
Demo 4
 Design
an aggregated view
 Display external documents from multiple N/D
databases in Query View
 Implement access mechanism to open external
documents
SQL UNION Remarks
 Take
in account that DAV field order defines
column order of corresponding DB2 table
 Number of fields has to be equal in both result
sets
 Data types have to be equal for merged columns
Result Set 1
VARCHAR
VARCHAR
DATE
DOUBLE
DATE
DOUBLE
UNION
Result Set 2
VARCHAR
VARCHAR
Retrieve Documents From Federated Data - #1

View includes rows with federated data

not related to documents of current database
 which originate from (real) documents of external
databases

Thus, document related N/D objects are not accessible





document collection
document context
caret note ID not available
View entry objects (rows) originating from external
documents do exist
Use caret category to identify row context

requires unique value in sort column
Retrieve Documents From Federated Data - #2
 Include
necessary data to access external
document to (hidden) designated column(s)



UNID / Note ID
servername
path or replica ID
 Intercept
QueryOpenDocument event
 Create backend document object that
represents external document
 Open external document in Notes Client UI
Federated Data - Remarks
 Does
not work with categorized views
(currently)
 Dynamic resort has to be considered

would change caret category
 Wishlist



for subsequent releases
identifier for row selection context
(other than caret category)
provide data of external document automatically
(servername, path or replica ID in addition to UNID)
support response hierarchies
Enterprise Infrastructure Requirements
 Prerequisites
for aggregated views
 Requirements for applications


follow data structure policies
use common set of application templates
 Evaluate
using DB2 as data store
Query Views - Performance



Performance lower than NSF for "as is" usage
Beta code – this is just guessing
DB2 result sets

possible advantage for small result sets out of large
document collections
 no view index stored
 live SQL query on open or refresh

For certain scenarios

Query Views could help to handle performance issues appropriate redesign has to be done
 Query Views do not work locally
Query View Remarks
 DB2
user mapping necessary for security
 Default for maximum number of returned rows
is 500 at server level
 Federated data not available locally
 Federated data must be available in DAV or DB2
table
 Query views with federated data do not update
automatically


query is not re-executed
use SHIFT-F9 to update
Query View Guidelines
 Verify
if schema name matches selection
formula
 N/D column sorting overrides DB2 sorting
 Always use @GetDB2Schema to determine the
schema name for query (available in beta 3)
 Verify if network account names are available on
new server
Query View Summary
 JOINS
solve basic N/D problem with relations
 New features offer the potential to create
applications that enable users to work more
efficiently
 Agregated views offer a new way to create a
single point of access to all documents a user
needs
 Potential to address current problem scenarios
with respect to performance/scalability
Agenda


1 Introduction
2 Domino Access Views (DAV)

scenario
 technology, tips
 live demo

3 Query Views

scenarios
 technology, tips
 federated data paradigm
 live demos
4
Summary
Conclusion/Summary
 Start
thinking about scenarios in your company
 Start consolidating your application landscape
 Look into DB2 administration
 Get skills in SQL programming
 Prepare applications to utilize DAV and Query
Views
 Start nagging IBM to make NSFDB2 to easily run
on client
Contact Information
 Dr.
Ludwig Nastansky
mailto:
[email protected]
[email protected]
 University of Paderborn
Groupware Competence Center
http://gcc.upb.de