Notes/Domino Rel-7 und NSFDB2 - Neue Potenziale bei der Integration von Dokumenten und Daten

1. 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

2. 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

3. 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

4. 1 Introduction • Focus on DAV & Query Views in NSFDB2 • Goals • 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 • No NSFDB2 basics will be covered • Presentation mainly based on beta 2 code

5. 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

6. 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 Documents vs. Relational Data N/D world DB2/RDB world

7. 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  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 • 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

8. 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]

9. 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

10. update, insert, delete DAV Access Views redundantly store Access Views DB2 View UDF Query Views Query Views N/D View Access Views read Access Views Query View DB2 Data Table Access Views Access Views DB2 Table read DB2 data flow read DAV related data flow Control DAV vs. Query Views NSFNote Table Domino Applications SQL DB2NSF Domino data flow DB2

11. 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

12. 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

13. Access Views Access Views DB2 View UDF stored in Access Views Domino Designer design Access Views DAV Design Document DB2 Table define DAV Architecture update, insert, delete DAV NSFNote Table redundantly store Domino Applications SQL read DB2NSF Domino data flow DAV related data flow DB2 Control

14. Access Views Access Views UDF stored in Access Views Domino Designer design Access Views DAV Design Document define DAV Data: N/DDB2 Interaction update, insert, delete DAV NSFNote Table redundantly store Domino form based selection DB2 Table user based selection Applications SQL DB2 View read DB2NSF Domino data flow DAV related data flow DB2 Control

15. Fields are elements of forms Fields are items in notes New fields, that do not exist in N/D database yet DAV – Field Definition DAV Design Document Field 1 Field 2 Field 3 Field 4 Field 5 Field 6 etc. …

16. 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)

17. 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

18. 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

19. 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

20. 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

21. 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 fieldsfor insert and update operations

22. DAV Design - #2 • Define advanced properties • include UNID to be able to open documents in Query views • modified time

23. 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

24. DAV Design - #4 • Create DAV in DB2 • Populate DAV • asynchronous background task DAVPOP • can take a long time in large databases

25. DAV Use

26. DAV Remarks - #1 • Structure clash between N/D flexibilityand 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

27. DAV Remarks - #2 • UDF server needed • Design document replicates in NSF, but is not visible locally • Does not support formula, rich text andrich text light data types • Include only fields you really need

28. 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

29. 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

30. 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

31. DAV Access Views redundantly store Access Views DB2 View Query Views Query Views N/D View stored in Access Views Access Views Query View DB2 Data Table SQL Query Access Views Access Views Query View Design Document DB2 Table design Domino Designer SQL Query Domino data flow Control Query Views - Design NSFNote Table Domino DB2NSF DB2

32. DAV Access Views redundantly store Access Views DB2 View UDF Query Views Query Views N/D View Access Views read Access Views Query View DB2 Data Table Access Views Access Views DB2 Table DB2 data flow read DAV related data flow Control Query Views – Data Flow NSFNote Table Domino DB2NSF Domino data flow DB2

33. 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

34. 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

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

36. 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

37. 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

38. 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

39. Query View D1 NAME PHONE SALARY EMPID Record Set 1 Record Set 2 Record Set n SQL JOIN NSFDB2 EmpUSA.nsf A DAV EMPLOYEE EMPID, NAME, PHONE 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” NSFDB2 Salary.nsf B DAV SALARY DB2 EMPID, SALARY

40. Demo 2 • Design a Query View • JOIN data from other N/D database Data from other database

41. 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

42. 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

43. Demo 3 • Design a Query View • Dynamic selection using @Prompt • Dynamic user preference User selected data

44. 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

45. SQL Query View DB2 "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" SQL UNION – Aggregated Views NSFDB2 Asia.nsf Notes Client DAV Employee NSFDB2 EMEA.nsf DAV Employee DAV Employee NSFDB2 USA.nsf

46. Scenario 5 – Aggregated Views • User tasks are dispersed over multiple workflowand 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…

47. Demo 4 • Design an aggregated view • Display external documents from multiple N/D databases in Query View • Implement access mechanism to open external documents

48. Result Set 1 VARCHAR VARCHAR DATE DOUBLE UNION Result Set 2 VARCHAR VARCHAR DATE DOUBLE 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

49. 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

50. 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