der Integration von

gabonesedestructionDéveloppement de logiciels

17 févr. 2014 (il y a 3 années et 1 mois)

65 vue(s)

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


䑂㈠偡牡摩杭愠呡扥汬敮e捨湩瑴獴t汬e


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


put N/D


䑂㈠2湴敧牡瑩潮t楮i物杨琠捯湴c硴


show innovative & efficient N/D


䑂㈠
楮瑥杲慴楯渠
潰瑩潮o


envision new approaches for N/D & DB2 application
development


start thinking about required changes in infrastructure


take home tips for first implementations


No 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


剄䈠捯湮散B楶楴i
桡h攠慰灥慲a携d
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


剄䈠楮瑥杲t瑩t渠
楳潴
潮汹⤠慢)畴⁴u捨湯汯杹


but (much more) about application domains, IT
-
strategies,
top
-
down system architectures, functionality concepts

Documents vs. Relational Data


strategic orientation & communication
centric


knowledge & information
management


tool paradigm on objects


code re
-
use


compound documents, semi
-
structured, 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


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 server
-
connected workplace

N/D world

DB2/RDB world

Integration, Cooperation, Federation


Challenge: bring together the best of the two worlds


as much as needed and as much as makes sense from a business
value perspective


Position: N/D


剄䈯䑂㈠楮瑥杲t瑩t渠楳畳琠潮攠楮獴s湣n映瑨攠
楮湵浥牡扬攠
獹獴s洠楮瑥杲t瑩t渠瑡t歳 瑡t楮朠灬慣攠e畲牥湴ny


The integration is between equal partners


stay cool, no religion involved, don't demonize


Purpose


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

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]

#1

#2

Start
: two

independent

lists

Goal
: model

Employee
-
Room

relation

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

DB2NSF

DB2

update, insert, delete

Domino data flow

DAV related data flow

Control

Domino

NSFNote Table

Applications

SQL

redundantly

store

Access
Views

Access
Views

DB2

View

Access
Views

Access
Views

DB2

Table

DAV

Access
Views

Access
Views

DB2
Data

Table

read

read

read

UDF

DB2 data flow

Query

Views

Query

Views

N/D

View

Query

View

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

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

䑂㈠摡瑡
數捨c湧攻e
丯N
-
v楥i 慮a汯杵攬e扵t

湯琠慣捥獳楢汥l楮 乯瑥N⁣汩敮e


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

DB2

update, insert, delete

DAV related data flow

Domino data flow

Control

Domino
Designer

Domino

NSFNote Table

Access
Views

Access
Views

DB2

View

Applications

SQL

Access
Views

Access
Views

DB2

Table

DAV Design
Document

design

define

stored in

UDF

read

redundantly

store

DAV

DB2NSF

DAV Data: N/D

䑂㈠䥮瑥牡捴楯c

DB2

update, insert, delete

DAV related data flow

Domino data flow

Control

Domino
Designer

Domino

NSFNote Table

Access
Views

Access
Views

Applications

SQL

Access
Views

Access
Views

DAV Design
Document

design

define

stored in

UDF

read

redundantly

store

DAV

DB2NSF

form based

selection

user based

selection

DB2 Table

DB2 View

DAV


Field Definition

Field 6

DAV Design

Document

Field 5

Field 4

Field 3

Field 2

Field 1

Fields are elements

of forms

Fields are items

in notes

New fields, that do not

exist in N/D database

yet

etc. …

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

DB2NSF

DB2

Domino data flow

Control

Domino

NSFNote Table

redundantly

store

Access
Views

Access
Views

DB2

View

Access
Views

Access
Views

DB2

Table

DAV

Access
Views

Access
Views

DB2
Data

Table

Query View
Design
Document

design

stored in

Domino
Designer

Query

Views

Query

Views

N/D

View

Query

View

SQL Query

SQL Query

Query Views


Data Flow

DB2NSF

DB2

Domino data flow

DAV related data flow

Control

Domino

NSFNote Table

redundantly

store

Access
Views

Access
Views

DB2

View

Access
Views

Access
Views

DB2

Table

DAV

Access
Views

Access
Views

DB2
Data

Table

read

read

UDF

DB2 data flow

Query

Views

Query

Views

N/D

View

Query

View

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

NSFDB2

EmpUSA
.nsf

DAV

EMPLOYEE

NSFDB2

Salary
.nsf

DB2

DAV

SALARY

EMPID,

NAME,

PHONE

EMPID,

SALARY

Query View D1

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”

B

A

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


ensures all documents are displayed


leaves column empty if federated data does not match


Do 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

User selected data

Demo 3


Design a Query View


Dynamic selection using @Prompt


Dynamic user preference

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

DB2

SQL UNION


Aggregated Views

SQL Query View

Notes Client

"
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
"

NSFDB2

EMEA.nsf

DAV

Employee

NSFDB2

USA.nsf

DAV

Employee

NSFDB2

Asia.nsf

DAV

Employee

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

UNION

VARCHAR

VARCHAR

DOUBLE

DATE

Result Set 1

VARCHAR

VARCHAR

DOUBLE

DATE

Result Set 2

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:

Ludwig.Nastansky@notes.upb.de

Ludwig.Nastansky@pavone.de


University of Paderborn

Groupware Competence Center

http://gcc.upb.de