I'm building a sales reporting interface that will use the all the new features of Domino and db2. These are the notes from installation on Windows 2003 (later Linux so I can use 64 bit apps) though the building of the application. I will be posting a modified version of the Notes.ini database that utilizes the db2 views. I don't know why Lotus calls them db2 Access Views, as you will see it's really a field mapping, not a view.

First the gotchas for Domino 8 and 8.5:

SQL Query Views:

  • When the view has a column with Total selected, there isn't a bottom total line, only totals at the category level. If you do a GROUP in SQL then the last line is shown twice
  • There is a limit to the search results so don't expect Notes like view of everything in the database.

db2 access views (DAV):
  • Date/Time fields don't convert to db2 date fields correctly. If the Domino field has a time component then the resulting date in db2 is 1 day ahead. You have to make sure the field is @date(fieldname) in it's translation formula for db2 to tie to the field correctly. It converts the time based on the current GMT area to GMT based time.

DB2NSF:
  • Using Notes 8.0.x, the views only appears in the Designer if the database is db2 enabled, so it doesn't show up in templates. You can set a Notes.ini entry to have DAV views show up in your designer all the time: Designer_ShowDavQv=1.  The 8.5 designer doesn't need this, it shows them all the time.
  • R8 databases can't be converted so don't use the notes.ini entry :Create_R8_Databases=1. For Domino 8.5 this is no longer a problem. R7, R8 , R8.5 databases can be converted, so use 8.5 and put Create_R85_Databases=1.
  • Encrypted data can be stored in a DB2 based DB2, but encrypted data is not accessible in DB2 Access views.  In 8.5 encrypted fields can't be used in DAVs.
  • Can't use DAOS, at this time in the beta you can set this property on the database but it's ignored.
  • Compacting works differently. The only compact options that work for db2 are B, g, and G.

Now the features:
  • Each person's view is a distinct instance and changes to the SearchQuery are  used only for that instance. This means that views using @username are possible. Since the query is called when the view is opened or refresh, having date calculation such as @now, do not impact the view.  A Notes view that show the current years sales opportunities would take 7 minutes. to open now take 2-4 sec. with the Query View.
  • You can change the sql query with LotusScript by using the view properties. This changes the actual design of the view and the string is enter as the view query. I have not been able to include @formula in the query like you can do if you edit the design directly.  The new properties are:
    view.isqueryview      - read
    view.selectionquery  - read/write
  • You can create the views with LotusScript using the new method - db.createqueryview( viewname, sqlselectionformula, template view, prohibitdesignrefreshmodifications).
  • The SQL Query can create variables that are referenced in column formulas. You want to show the current year's sales but need it to be categorized by Salesperson, sector, product, region, manager, etc. This would mean a view by each field needing to be categorized. With the Query Views this can be done with just one view. Since SQL Query can return a variable that is in any column formula, you   can display a view with a new category value by changing the SQL Query string.
  • By using environment variables in the Notes Client or URL parameters for the web, you can make dynamic views. @environment("Variablename") and @URLQueryString("Parmname") can be used to pass information to the SQL Query to be used in the selection of the data.
  • The 1 row = 1 document is no longer the case. You can build reports that show different data in the row.
  • DAVs can include database and document information such as server, dbpath, UNID and OID. these will allow you to locate the document in Domino.
  • You can show data from different sources (called Federated data). You can have a view that shows documents from multiple Domino databases and even have the documents open! There is some code needed in the view queryopendocument routine to allow this.
  • You can get and use the row number and even the count of rows! Now you can build views that know which row is first and last also. There is an entry here to show how to build views with color and web support using row number.
  • You can do lookups in the view
  • You have the power of SQL to do JOIN, UNIONS, GROUPS and HAVING. Learn SQL and go wild.
 

DAV Limits

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




Building the db2 Access View (DAV)
The DAV is a mapping of domino data to db2,  you can map by form or include multiple forms in the table. If the fields is not on the design form, it won't show up in the Create field list. You will just have to enter the field name by hand. There are some fields listed as Special Fields, these let you get to information such as the server, database path and UNID. These are useful if you are building views the contain data from other databases.

The hardest part is cleanup on the Notes data. In db2 each field has a type & length, you can't mix types. In Notes the fields can be different types based on anything. Finding why some company name's is 400 characters long or that some docs have a text field instead of date if really fun. I end up building a view with the fields listed and a column the indicates the length. This helped me in cleaning up the data.

When you populate the table you have to keep pressing the refresh action to see the results. If there is a problem such as too long a field length, you will get an error message. The error message just indicates which field has the wrong value but not the doc it on. You have to fix that field and then try again.

db2 Security: You have to set the access for each table in db2, which isn't hard. The problem is how easy it is in Notes to change the view and repopulate db2, which cause the table to be deleted and re-created. So you need to remember to set the security after any DAV change.  You can set a notes.ini variable to allow anonymous access to the data in the views.  This will require you to make sure the database and views have the security needed.
Allow_Anonymous_Access_From_DB2=0 / 1 .

Building the SQL View
Now that you have data in your db2 table, it's time to play with it.
To use the SQL Search in a view, the view has to be NEW, you can't change a previous view to use db2. When you select new view, set you title and the view to copy from, then select SQL Query for the search method.  If you plan to copy the design of a current view, do that first as it will reset the Selection conditions.



SQL Query: "SELECT manager, manager AS CatCol, company, salesperson, total, DateClosed, sector, product FROM crm_sal0.oppdav_t WHERE DateClosed Between DATE ('2007-07-01') AND DATE ('2008-06-30')  ""<br>

Categorized Column Formula:  CatCol


 

Comments (0)