Creating a query view

Procedure

  1. In Domino® Designer, open the database for which you want to create a query view.
  2. Select Create - Design - View.
  3. In the Create View dialog box, complete the following:

    Field

    Action

    View name

    Enter a name for this view

    View type

    Choose one of the following:

    • Shared
    • Private

    Select a location for the new view

    If you want the view to appear at the top level in the list of views, do not select anything in this field. Otherwise, click the name of the view under which you want the new view to appear.

    Copy style from view

    Click this option and then:

    • Click Blank if you do not want to copy another view's style.
    • Click the view whose style you want to copy.

    Selection conditions

    Enable "By SQL Query." Note that when this option is selected, the View types are limited to the four options described previously.

  4. Click Query Window. Type your SQL query in the Edit SQL Query Formula dialog box. Make sure the query is contained in quotes. For example: 
    "Select * from acmedb1.dav1"

    where acmedb1 is the schema name of the DB2® table/view and dav1 is the name of the DAV that contains the information you want to query.

    Note: You can determine the schema name for your query (which is usually the same as the DB2® enabled Notes® database filename) from DB2®. Issue the query by selecting nsfschema from the Domino® Catalog, where the filepath = 'myfile.nsf' (substitute the name of your Notes® database for myfile.nsf.) This file name is relative to the data directory. You need to have Read access to the catalog.

    If you delete a DB2® enabled Notes® database and create a new one with the same name, Domino® will reuse the same schema name if possible. However, if this is not possible, Domino® may add a number to the schema name. For example, if acme.nsf is deleted, and a new acme.nsf is created, the fully qualified name of the DAV for the new database may change from acme.dav, for example, to acme2.dav in DB2®.

    The new @function @DB2Schema can be used in a Query View SQL query to eliminate the guess work of the actual DB2® schema name being used. See the topic @DB2Schema for more details on this new function.

  5. Optional: Click Functions to obtain a list of functions to copy and paste into your query.
  6. When you are finished, click Done.
  7. Click Save and Customize. This opens the Designer View workpane. From here, you may continue to customize your view.

Results

Note: You can specify the maximum number of rows returned by an SQL query in View Properties. However, there is also a row limit set by the administrator at the server level, so you should know this limit prior to setting up any query views. As a designer, you can still set a row limit (in the View Properties) less than that set at the server. The default limit is 500 rows.
Note: DB2® column names that have disallowed Notes® field name characteristics (for example, columns starting with a digit) are reported when the query is run. You will get the following Designer error dialog:

"Field names must begin with a letter or the symbols _ and $ and . The rest of the name may contain the letters A-Z, the digits 0-9, or the symbols _ and $ and . Spaces are not allowed."

and the console will report something like:

05/02/2005 10:25:57 AM  Invalid field name: 1

Additionally, the #UNID and #MODIFIED fields are not created. However, you will not get an invalid field name warning in these cases.

If you want to use the values returned by any of these invalid columns, use the AS syntax in the Query View's SQL formula to rename them.