How did I do it

 
The server setup and configuration is not covered here. There is an installation guide available at Installation wiki and some more information is else where on this site.

You can access DB2 from domino through the following methods:

• @db using ODBC
• LotusScript® and Java™ classes
• Domino Enterprise Connection Services (DECS)
• Lotus Enterprise Integration® (LEI)
• NEW For Domino 8
  • DB2 Access View
  • Query Views

Benefits of DAVs & Query Views

• Real time updates
• Bi-directional connectivity
• It's like normal Notes views and can be easily placed into applications
• The query is performed on the fly – no index
• No need for Private views
• Date based views don't kill the server
• Can show data from multiple databases
• A row is not restricted to data from a single document like Notes
• You can get and use the actual row number
• *** Can accept input to be used to filter the view ***

The requirements for using and developing db2nsf applications

• Domino R8.x Server
• DB2 v9.1.3 Enterprise Server
• Manages storage of data
• DB2 Access Server R8.x
• Provides the connection from Domino to DB2 for DAV and Query Views
• Needs to have a special server id created!
• Notes R8.x Designer Client

·  
DB2NSF Overview


DB2NSF is a database that has been DB2 enabled but not all databases can be DB2 enabled. The database is enabled by running compact with the -p parameter or creating a new replica / copy from the admin client.

> load compact sales.nsf -p12/05/2008 01:15:27 PM  Compacting sales.nsf (DB 2)12/05/2008 01:15:29 PM  Compacted sales.nsf, 472K bytes recovered (92%)

The DB2 server is included with the Domino License – it has restrictions
The DB2 Access server needs a special server id created and is the interface between db2 and the Domino server for DAV and query views.
The  DAV and Query View are design elements that  are only visible in databases that has been DB2 enabled (db2nsf).
With Domino 8.0.2  you could not convert  database that are in ODS48, in Domino 8.5 ALL ODS versions are support. So you can add the notes.ini entry to have all databases be in the new ods51.

CREATE_R85_DATABASE=1

Using the Administration client you can DB2 enable a database when you create a new replica.




DB2 Access Views – DAV

It's not a view in the Notes sense, it's a mapping of Domino fields to a DB2 table that is only available in DB2 enabled databases. You will set which Domino fields are mapped to a column in a DB2 table. You first build the DAV, then you will populate it. The population will go through the Domino database and verify that all the data that is mapped, has the correct datatype and field length. IT can take some time to populate.

For Notes 8 Designer you need a Notes.ini entry to view the design in templates.
·   Designer_ShowDavQv=1
This is not needed for the 8.5 designer client.

Note: DAVs have to be manually rebuilt if it is edited and Templates will install design but not build the DAV

The Dav is found in the Shared Resources area of the 8.x designer.

 It's moved in 8.5 designer.

To select the fields for the DAV use Choice Fields. The dialog box lets you select from Form, subforms, shared fields or Special Fields.

Selecting the DAV Data types

 DAV Considerations

• No encrypted fields
• Numeric fields – Integer or Double
• Rich text - can be text or CLOB
• Multi-value fields are Text List that are merged into a single field with delimiters. You can select the delimiter to use. Do remember to use @explode(field) if you want to see the values as a list in the view. These fields can not be used as DB2 index fields
• Text & Text List field – Do you truncate or not. Truncating will reduce the size of the table but updating the record will cause all truncated fields to be written back to the Notes Document
• Do you really need everything or can use basic data?
• The Field list only shows fields on the form or subform so you will have to manually enter the other fields
• The DB2 type will be from the data type of the field on the form and you will not be able to change the DB2 type.

In Domino, a DB2 Access View (DAV) can have a maximum of 84 columns. If you need more data from a single Notes document, you need to define multiple DAVs.
When you map Notes fields, there is a limit to the total length of data you can save in the DAV table. The length of all data fields, plus DB2 overhead, cannot exceed 16,384 bytes of data.

If you need to store more than 16K, you can map some of your text fields to LONG VARCHAR. When you map a field to LONG VARCHAR, a small locator is stored inline (counting toward the 16,384 byte limit). The actual field data (up to 32,000 bytes per field) is stored as a Character Large Object (CLOB). Using CLOBs gives a DAV more than enough storage capacity (84 * 32,000), but you should only use LONG VARCHAR if you cannot fit the data inline, because CLOB storage is slower than inline storage, and LOBs use more disk space than inline storage.

When you validate or create a DAV, the Domino server will check to see if the DAV definition exceeds the maximum length, so you do not need to calculate the length manually. However, if you would like to check this manually, you can use the following guidelines for estimating the inline data length:

DB2 type	Length in a DAV column	Comment
VARCHAR	5 + declared length	VARCHAR(40) requires 45 bytes of storage
LONG VARCHAR	25	in line locator
DOUBLE	9	fixed size
TIMESTAMP	11	fixed size
DATE	5	fixed size
TIME	4	fixed size
INTEGER	9	fixed size
CBLOB	25	in line locater

Special Fields

The DAV has Special fields that allow the DB2 data to be used like a normal view. You use the Special Fields to:

• To let the user open the document by double-clicking in the view
• Copy view as table
• Show child hierarchy in the view
• Copy document as a doclink
• Open documents from other databases

·  
Only Accessible In Query Views

• #SERVER     Server Common Name    Varchar
• #DBPATH     Full path and NSF name    Varchar
• #DATABASE     NSF name      Varchar
• #PUBLICACCESS   Public Access Notes behavior  Varchar bit - Include to enable Notes public access behavior in query views
• #REPLICAID     ReplicaID      Varchar bit

Accessible In DB2 & Query View

• #UNID        Document UNID         Varchar
• #MODIFIED    Same as @Modified    Timestamp
• #CREATED    Same as @Created    Timestamp
• #ADDEDTOFILE  Date added to this database  Timestamp
• #OID      Notes OID      Varchar    Include to enable document link for query view documents,   copy as a table and display "twisties" (along with #REF)
• #SEQUENCENUMBER  OID Sequence      Integer  
• #REF      From $Ref      Varchar bit ·   Include to support hierarchial views (along with #RESPINFO)
• #RESPINFO    Parent note id, child count,  Long Varchar list of child note id  

Created Automatically & Visible In Both

• #NOTEID        Integer - Include in Query View to be able to open document
• #Readers      BLOB
• #DB2Modified    Timestamp

Database Considerations
Before you start to migrate your database using DAVs, take a look at the data. Most NSF are built for speed not data integrity or normalization. You will find that you have the same data on multiple forms, even on the same form. Notes doesn't set a limit for field size and seldom does the developer.  A data field will show the correct format for a date/time value or text value, but DB2 will not. You will have to clean up the data and possible change the code to ensure data consistency.
.

• Review the actual document data
• Are fields limited in size or free form?
• Do you have a Phone number with 300 characters
• Company names with 250 characters
• Are data types enforced?
• Are date fields set to Date/time or do you use @date(field)?
• Are numeric fields all integers or floats?

If You Don't Clean Up Your Data.

When you try to save the DAV and the data type does not match the form, you will get an error.



If the database has documents with the wrong size or datatype you will not be able to build the DAV
You get a really helpful error message! NOT!



If your design does not validate the data then you will have problems later on. If the document has the wrong size or datatype then the field's data is not sent to DB2 when the data is replicated or updated by an agent. User will get an error if they try to save a document with bad data




How To Verify Data
The easiest is to build a view with 1 column for the field and 1 column with @length(field)  for each field needed. For date and time values add a column with  @isTime or @isNumber to validate data type. Each column should be set to click on header to sort descending ·  







Query Views
Look like a normal view except they have a different icon.

Accessed the same way as a normal view. The difference is an extra design object: SQL Query



Limits

• Query Views can't show all documents
• Result set limit can be set by priority order in the: server document , notes.ini, View properties, Embedded view properties
• You can view but can't update the data
• No view totals
• Can't view the data in Designer

SQL Query Object
The SQL Query object expects to have a text string as the result.  The result is processed as the SQL Query. The Query can only retrieve data - update, inserts and deletes are not allowed. Also supports the SQL commands:  Group by, Having, Join and Union
You can even use @functions to create the query:   “SELECT * FROM Proj.task WHERE Owner = '“ + @username +”'”

To access a DB2 table you use schema.tablename. The DAV's name is the table's name in DB2 and the schema name is created by the DB2 access server. It's usually (changes based on release) the first 7 characters of the filename plus random numbers to fill since it's always 8 characters long.

Since it's not guaranteed to stay the same, especially between Dev and production environments, use @DB2Schema to get the naem. THis way you don't have to change code.

Examples:
@DB2Schema(@dbname)
@DB2Schema(“server”:“dbname”)
@DB2Schema(“server” ; “repid”)
@isDB2 will tell you if it the database is DB2 enabled



Basic SQL
The Query needs to contain at least a SELECT and FROM command: “SELECT * FROM Tasklist.task”

This pulls all the fields from the Tasklist.task table. Use “*” for all fields or list only the fields you need:
“SELECT #NoteID, Taskname, Owner, Duedate FROM " + @db2schema(@dbname) +".task "

The SQL Query will need to include the #NoteID or you get an error




The WHERE command allows you to select which rows to return, like a filter..
“SELECT * FROM Prj.task WHERE Owner ='“+ @username +”'”

Case counts for comparisons! DB2 is case sensitive, like @functions, when you do comparisons.
“SELECT * FROM Prj.task WHERE LCASE(Owner) ='“ + @lowercase(@name([CN];@username)) +”'”

Using Dates to filter

 
 yr := @Text(@Year(@today)); 
 mo := @Text(@Month(@today)); 
 dy := @Text(@Day(@today)); 
 “SELECT * FROM Prj.task WHERE startdate > DATE ('"+ yr +"-"+ mo +"-"+ dy +"') "; 
 

Use LIKE to get results similar to @contains. The “_” means any character and “%” any string

“SELECT * FROM Prj.task WHERE LCASE(Owner) LIKE 'br_ce%'”
returns bruce, brice, bruce lill, bruce johns, etc.

“SELECT * FROM Prj.task WHERE LCASE(Owner) LIKE 'b_%'”
return all owners that start with “b” and have at least 2 characters.

Use AS to return the cell value with a different name
“SELECT #NoteID, Taskname, Owner AS name, Duedate AS Due FROM @db2schema(@dbname).task

You can use this to combine 2 tables that use different field names for the same data such as Salesperson and ContactName.
“SELECT #NoteID, a.Salesperson AS name, b.contactname as name ...”

Using AS to Make Views Resusable  
This is also used to make generic views where you can put any value into any column based on the parameters passed to the view.
The column formulas refer to the fields returned by the query string:

“SELECT dept as db2cat, name as sCol, title as colA, hired as colB FROM “+ @Dbschema(@dbname)+”.dept”

Date and numeric values may need to be converted to text or assigned a specific column
Sorting is all the same direction, you can't change it


JOINS
The most common join is called the inner join where a value in a column in one table is used to select matching row(s) in another table. This means the data can be from different Domino Databases.

This example would result in the Customer name and their order in one row even though they are from different tables:
SELECT cust.custnum, cust.name, order.ordernum, order.desc FROM crm.customers AS cust INNER JOIN sales.orders AS order ON order.custnum = cust.custnum

NOTE:  If the value being used for the join is not unique, multiple matches will be found.

SQL also supports an older style less explicit syntax
SELECT cs.Companyname as Name, sl.Description, sl.amount, sl.OrderDate  
FROM customer AS cs, sales AS sl WHERE sl.CustID = cs.CustID

OUTER JOINS - Includes the rows that did not satisfy the join condition

LEFT OUTER JOIN:
 Return matching rows PLUS unmatched rows from the table on the left hand side of the join

RIGHT OUTER JOIN:
  Return matching rows PLUS unmatched rows from the table on the right hand side of the join

FULL OUTER JOIN:
   Return matching rows PLUS unmatched rows from both tables (need the equality comparison in the join)


UNION
Data from multiple tables are pulled into one view that is a Union of the result sets of the individual queries.  The individual query results must have the same number and type of columns

Example:
SELECT '2008', ordernum, desc, qty FROM Sales.Orders WHERE item='Notes 8.5'
UNION SELECT '2007', ordernum, desc, qty FROM Sales.Orders07 WHERE item='Notes 8.5'
UNION SELECT 'ARCHIVE',ordernum, desc, qty FROM SO.Archive WHERE item='Notes 8.5'


Open Documents From Other Databases
You can even select which document to open when the row is clicked on in the view. All you need to do is add some fields to the DAV, add a column to the Query view, and put code in the Queryopendocument.
Then you can open the documents from other databases!

The "CaretCategory" property of a Notesuiview corresponds to the first sortable column in the Query View. The CaretCategory is used to pass to the Queryopendocument the database and document to open. If the view does not contain the field #NoteID, then you will get an error when you click on the documents. The #NoteID tells the view that there are documents that can be opened in the view.

In the DAV select the following fields:
#SERVER, #DBPATH, #UNID , #NotesID

In the Query View add the fields needed to get the document:
ini.#SERVER || '~' || ini.#DBPATH || '~' || ini.#UNID AS DOCID

The first sorted column needs to be the field DOCID

Sample Query:
nSchema := @DB2Schema("":"DB2\\notesini.nsf");
"Select ini.#NoteID, ini.Subject, ini.Categories, ini.release,"+
" ini.#SERVER || '~' || ini.#DBPATH || '~' || ini.#UNID AS DOCID from " + nSchema +".notesini as ini "

If you need a different sort for the view, then add the sorting field to the front of the DOCID field.
ini.subject || '~' || ini.#SERVER || '~' || ini.#DBPATH || '~' || ini.#UNID AS DOCID

This allows the Queryopendocument to trigger and the new code to run. The result is the document is opened from the target database.

 
 Queryopendocument 
 Sub Queryopendocument(Source As Notesuiview, Continue As Variant) 
 continue = False 
 Dim ws As New NotesUIWorkspace   
 Dim db As New NotesDatabase("","")   
 Dim target, server, path, unid As String 
 target = Source.CaretCategory 
 server = Strleft(target, "~") 
 path = Strleft(Strright(target, "~"), "~")   
 unid = Strrightback(target, "~") 
 Call db.Open(server, path) 
 Set originalDoc = db.GetDocumentByUNID ( unid ) 
 Set uidoc = ws.EditDocument(False, originalDoc, False, "", False, True) 
 End Sub 
 

Dynamic Views
The SQL Query can include the following @functions:

•  @environment   – Used by Client
• @Prompt      – Used by Client
• @Picklist     – Used by Client
• @URLQueryString – Used by Browser

These functions let you modify the query while the view is being opened. Since you know who the user is, you can limit what they see so it will show only their tickets or their reports. You also can modify the query with LotusScript


Using @Prompt or @Picklist
You can prompt the user to enter a value to filter the view
dpt := @Prompt([OkCancelEdit]; "Department Name"; "Enter Department name";"");
"SELECT D.DeptID , D.DeptName As Dept , E.Lastname AS Lastname FROM " + schema +".dept AS D WHERE D.Deptname ='" + dpt +”'”

OR

dpt := @PickList([Custom]:[Single]; "":""; "vDept"; "Departments"; "Select a Department"; 1)

Using @Environment

You can set variables in the environment and use the values in the Query String. The problem is setting it reliably every time the user selects the view. If you use a form with an embedded view, you can set a computed field to update the environment variable when the form is refreshed.

I was able to replace 43 views with one form and view. It lets them select how and what the view displays. You could set environment fields when the database is opened so you could save users department to only show that departments records or save employee ID to use in searches.


Using @urlquerystring
The parameter is passed at the end of the URL:  /db.nsf/view?openview&dept=math. You will have to adjust for case and URL encoding for the values of the parameter. Space and special characters do not work in a URL.

Example:

 Parm := @lowercase(@UrlQueryString("Dept")); 
 filter := @If( Parm ="" ; "" ; "WHERE LCASE(D.Deptid)='"+Parm+"'"); 
 "SELECT D.DeptID , D.DeptName, D.head, FROM " + @dbschema(@dbname) +".dept AS D " + filter 
 

Building A Web View
By passing parameters to the view you can use the one view to show different data.   You can have a large number of parameters to control your view so you could select the field to be used for categorization, sorting etc. The following example is used to show support tickets that are categorized by Company, Assigned, Status, Contact or any field in the table.  The status field is always included to reduce the number of tickets returned. If a ticket number was passed, the other parameters were ignored and just that ticket is returned..

Web View Example – @URLQuerystring

 
 REM { category field}; 
 CatField := @UrlQueryString("cat"); 
 stat := @UrlQueryString("stat"); 
 ticket := @UrlQueryString("tkt"); 
 REM { Search by date range}; 
 dr:=@UrlQueryString("dr"); 
 REM { Search by support engineer}; 
 se:=@ReplaceSubstring(@UrlQueryString("se");"_";" "); 
 REM { Search by description}; 
 prob:=@LowerCase(@ReplaceSubstring(@UrlQueryString("prob");"_";" ")); 
 REM { show only feedback}; 
 fb:=@LowerCase(@UrlQueryString("fbf")); 
 user := @ReplaceSubstring(@UrlQueryString("user");"_";" "); 
 cmpy := @LowerCase(@ReplaceSubstring(@UrlQueryString("cmp");"_";" ")); 
 Web View Example – Filters 
 tday := @If(dr="";@Today ; @Adjust(@Today;0 ;0 ;-@TextToNumber(dr);0 ; 0 ;0) ); 
 yr := @Text(@Year(tday)); 
 mo := @Text(@Month(tday)); 
 dy := @Text(@Day(tday)); 
 Range := @If(dr="";"";" AND opened > DATE ('"+ yr +"-"+ mo +"-"+ dy +"') "); 
 feedback := @If(fb="";""; " AND feedback = 'Yes' "); 
 company := @If(cmpy="";""; " AND LCASE(company) LIKE '%" + cmpy + "%' "); 
 ufilter := @If(user="";""; " AND contact LIKE '%" + user + "%' "); 
 problem := @If(prob="";""; " AND ( LCASE(description) LIKE '%" + prob + "%' OR LCASE(problem) LIKE '%" + prob + "%' " ); 
 status := @If(stat="" | stat="undefined";"Open"; Stat); 
 REM {Tickets are always shown by status}; 
 SEng := @If( se="";"" ; " AND assigned LIKE '%" + se + "%' "); 
 filters := "Where STATUS = '"+ status +"' "+ company + ufilter + problem + feedback + range + SEng; 
 Web View Example – SQL Query 
 swhere := @If(ticket !=""; "Where TICKETID LIKE '%"+ticket+"%' "; filters); 
 cat := @If(CatField ="";"assigned";CatField) + " as db2Cat,"; 
 "SELECT "+ cat +" #NOTEID, #UNID, datecreated, feedback, company, contact, Description, updated, assigned, status, ticketID " + 
 "FROM " + @DBSchema(@dbname))+".TICKETS " + swhere 
 

Add Javascript
With a little Javascript you can really be creative. The JSON can be used for type-ahead lists, select lists and look-ups  You can return data in in JSON format with the view parameter &outputformat=json. You can use a page with embedded view to build your own JSON or use SQL to get the row count and generate the Jason in the view!

Using LotusScript
The Query is accessible through LotusScript BUT it results in the design changing for the view. This does let you build a query, refresh the view and access the data. It is faster then doing a full text search.

View Properties:

• view.isqueryview  - read
• view.selectionquery  - read/write

Database method:

• db.createqueryview(viewname, sqlquery, template, prohibitdesignrefresh)

Example:

 
 strSQL= |"SELECT |+ catfield +| as db2Cat, Rep, finishDate, Status, companyName, Probability, MarginTotal FROM CRM.OPPS " + 
 " WHERE finishDate BETWEEN DATE ('| + bdate +|') AND DATE ('|+edate+|')"| 
 view.selectionQuery= strSQL 
 Call view.Refresh 
 

Reports
You can build views that are just used for reporting, you don't have to always have documents in the view. With SQL you can SUM, COUNT, AVG and GROUP your data.  You can build a view that is just a summary of the data and with Grouping you can aggregates data.

Example:

SELECT item, SUM(qty), AVG(price) FROM sorders6.SalesOrders GROUP BY item

This will return a view that has one row for each item with the Item, total quantity and the average price.


Resources

• Installation wiki – It's being updated to Domino 8
  • www.installationwiki.org/DB2_for_Domino
• DeveloperWorks wikis – If in doubt, go to the source!
  • www.ibm.com/developerworks/lotus/community/wikis.html
• Db2 Cookbooks – Graeme has some great DB2 reference guides for FREE!
  • mysite.verizon.net/Graeme_Birchall/id1.html
• Lotus Designer Help – They even have a DB2 section!

Comments (0)