Troubleshooting DAVs

ACLs needed for DAVs

When using DAVs, if users encounter problems while attempting to access your DB2® enabled Notes® database, make sure that the ACL entry for each user is identical to the first entry in the "User Name" field in that user's Person document in the Domino® Directory on the Domino® server.

Data Exception error

If you insert the value -2.225E-308 in a Notes® number field that is mapped to a double in a DAV, and you are using AIX®, you will see the following DB2® Data Exception error on the Domino® console:

SQL0302N The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use. SQLSTATE=22003

Inserting the same value to the same field from a DB2® CLP will not cause an error. This is a limitation of the double-precision floating-point number representation. To resolve this error, ensure that negative numbers fall into the range -1.79769E+308 to -2.225E-307, and positive number fall into the range 2.225E-308 to 1.79769E+308.

Errors using the Select statement

If you attempt to select from the Access View via a select statement in a DB2® Command Line Processor window and get the following error, contact your administrator.

SQL0443N Routine "ISREADER" (specific name "") has returned an error SQLSTATE with diagnostic text "The server is not responding. The server may be down or you ". SQLSTATE=04004

You can also troubleshoot this error using information in the topic about resolving errors when using SELECT, if you have installed the Domino® Administrator Help. Go to http://www.lotus.com/ldd/doc to download or view Domino® Administrator Help.

Errors performing SQL updates

If you attempt to perform a SQL update via a DAV and get the following error, it indicates that the DB2® Access View definitions have changed since the DAV was created.

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0443N  Routine "*ATENOTE2" (specific name "") has returned an error SQLSTATE with diagnostic text "15219 : DB2 Access View definition has the wrong version for this program". SQLSTATE=04009

Rebuild the DAV (Delete in DB2® and Create/Populate in DB2®) to take advantage of the latest design.

Exporting TIMEDATES to DAVs

The Notes® TIMEDATE can store the date, the time, the time zone, and a daylight savings time indicator, but a DB2® timestamp only stores the date and time. DB2® assumes that all dates and times are local to the DB2® server's timezone. This can cause the incorrect time to display when exporting TIMEDATEs to DAVs, or reading TIMESTAMPS from DB2® in a query view (whether they are from DAVs or other DB2® tables).

There are two options for saving the date and time in DAVs -- local time or modified to GMT (standardized). You choose the way you want them handled using the advance tab on the "Access View Entry" properties page, and then set the "Normalize to GMT for time zone conversions" field accordingly.

Note: You must rebuild your DAV if you change this setting for an existing DAV.

Ghost entries

When a DAV is created for a database which contains both main documents and response documents, removing the main document from the database will not remove the response document from either the database or the DAV. It remains, as the response to a ghost document, and will be visible from DB2®. However, a QueryView that uses SQL to SELECT information from the DAV will function as if it is a view in a discussion database, and only show those responses whose main documents are also selected.

Inserting date ranges in a DAV

When inserting or updating date ranges in a Note inside of a DAV via SQL, you must specify the date ranges with the full DB2® timestamp, such as yyyy-mm-dd-hh.mm.ss.subsec or 2005-12-12-08.06.30.123456. Failure to do so will result in the value being inserted as a null.

Inserting multiple values in a DAV using delimeters

In a DAV, specific delimeters can be set for each field. If data is inserted from DB2® using a different delimeter to separate multiple values, the following behavior will occur.

  • In a text field, all values and delimeters will be inserted as a single string
  • In a fixed length data field (e.g. datetime), values will be converted to a range
  • In a number field, the DB2® delimeter is replaced with the delimeter specified in the DAV field

Inserting multivalue date or date ranges in a DAV

When inserting or updating multivalue date or date range fields using a DAV, you must use the full DB2® timestamp, such as yyyy-mm-dd-hh.mm.ss.subsec or 2005-12-24-08.06.30.123456. Failure to do so will result in the last valid timestamp being repeated in the place of the invalid timestamp. No error is generated.

Non-responsive applications or systems

If you use DAVs during normal business hours to perform large updates of 1000 rows or more via SQL against a DB2® Access View, it could lead to a non-responsive application for users. When doing these large updates, the database is effectively unreachable until the process has completed. Depending on the size of the update, this could take a significant amount of time.

Also, if large SQL updates are performed during normal business hours, the SQL command could return a status of "Remote system no longer responding" despite the Domino® server still being up. This gives the appearance that the server is unresponsive when in actuality, the server could still be processing the update, but the SQL command window is unable to communicate with the server because the database is offline.

To avoid this situation, perform large updates at off hours when no one else is using the database.

Unknown form error

The Mail8 template renamed the Memo form, which is now the Message form. If a DAV is created based on the Notes® 8 mail template, using the Message form, and notes exist that have no forms associated with them, an "Access View Definition references an unknown form : Message" error will result.

Note: This error will not occur if the DAV uses All Forms, or if all notes have a form associated with them.

To resolve the error, copy and save the DAV. Rerun Create/Update in DB2®.

Unknown host error

If you ping the Domino® server by name and get an "unknown host" error, it may indicate that the network address of the server is not a fully qualified DNS name or IP address.

Updatable cursors do not work

Updates to a DB2® Access View through the DB2® Control Center do not work. You can perform these functions only through the DB2® Command Line Processor or DB2® Command Window. This is because in Domino® and DB2®, updatable cursors do not work with "Instead of" triggers.

Using GMT TimeDates with Query Views based on DAVs

Query views use only the value in DB2®, they do not interpret it or adjust it. You can see the query result in local time by adding the DB2® time zone into the query and using a column formula to adjust the time. For example:

"select  textcol, (CURRENT_TIMEZONE/10000) as ctz, dtcol from dt.dt"

and then use a column formula to adjust the value shown to the user:

	@Adjust(dt;0;0;0;CTZ;0;0)

You could get the same results by adjusting the returned timestamp using the information from @Zone:

	@Adjust(dtcol;0;0;0;-@If(@Zone - @Integer( @Zone )  > 0.05;@Integer(@Zone)-1;@integer(@Zone));0;0)

Where dtcol is the selected TIMESTAMP column in DB2®.

Note that a production formula may need to be a little more robust because @Zone can return an hhmm value if the server's time zone is not a whole hour multiple away from GMT.

Although this approximates a normal Notes® view, there are a few inconsistencies. For example, this shows the time correctly relative to the GMT, but it displays the date and time values relative to the DST value in the date (that is, it shows whether DST was in effect on that date or not), whereas the formula method shows the time relative to the current DST setting.