Many of you have heard about the "trick" to purge deletion stubs from a database. The trick is to move the replication cutoff date (under Replication Settings, Other, Only replicate incoming documents saved or modified after) to a date two days in the future. When a database gathers many deletion stubs because of frequent creating and deleting of documents, the database performance can go downhill quickly. A customer of ours was seeing this performance degradation in one application, and it was specifically because of deletion stubs. We wrote this script library where a database can have that specific replication setting updated programmatically.

First, a couple of custom data types are needed to interface with the replication information. These custom types are set up in the (Declarations) section of the script library.

Type TIMEDATE
   Innards(1) As Long
End Type

Type DBREPLICAINFO
   ID As TIMEDATE
   Flags As Integer
   CutoffInterval As Integer
   Cutoff As TIMEDATE
End Type

You can read more information about these structures in the Notes API documentation, but a Notes date/time value has two pieces - one for the date and one for the time. The replication information has several pieces. Notice that the replica ID is of a TIMEDATE structure. The replica ID is the exact date and time the database was created. The cutoff piece is what our code will be manipulating. It is the value that you see in the user interface when you go to the "other" tab of replication settings, under the "only replicate incoming documents saved or modified after" value.

Also in the (Declarations) section is the code that is needed to interface with the API classes. The code used here is specific to Windows. Minor changes (like the DLL name) are needed to make it work in Linux or other environments.

Declare Function W32_OSPathNetConstruct Lib "nnotes.dll" Alias "OSPathNetConstruct" _
(Byval portName As Integer, Byval serverName As String, Byval fileName As String, _
Byval pathName As String) As Integer
Declare Function W32_ConvertTIMEDATEToText Lib "nnotes.dll" Alias "ConvertTIMEDATEToText" _
(Byval intlFormat As Integer, Byval textFormat As Integer, inpTime As TIMEDATE, _
Byval retBuffer As String, Byval inpBufferLen As Integer, retBufferLen As Integer) As Integer
Declare Function W32_NSFDbOpen Lib "nnotes.dll" Alias "NSFDbOpen" _
(Byval pathName As String, hDb As Long) As Integer
Declare Function W32_NSFDbClose Lib "nnotes.dll" Alias "NSFDbClose" _
(Byval hDb As Long) As Integer
Declare Function W32_NSFDbReplicaInfoGet Lib "nnotes.dll" Alias "NSFDbReplicaInfoGet" _
(Byval hDb As Long, retReplicaInfo As DBREPLICAINFO) As Integer
Declare Function W32_NSFDbReplicaInfoSet Lib "nnotes.dll" Alias "NSFDbReplicaInfoSet" _
(Byval hDb As Long, inpReplicaInfo As DBREPLICAINFO) As Integer

Many of these API calls have been made in other tips posted at the Breaking Par web site, so they will not be covered in further detail here. We will be getting the API path to the database to update, opening that database, reading its current replication settings, changing the cutoff date (while keeping the other values), setting the new replication settings, and then closing the database.

Sub PurgeDeletionStubs(server As String, fileName As String)
   On Error Goto Repeater
   Dim fullPath As String*256
   Dim hDb As Long
   Dim status As Integer
   Dim formulaTime As Variant
   Dim inTwoDays As TIMEDATE
   Dim tempTime As String*81
   Dim textTime As String
   Dim retLen As Integer
   Dim replicaInfo As DBREPLICAINFO

The script library takes two parameters. The first is the server name that holds the database. The second is the path to the database that needs to be updated. The server name could be blank if the database is local (on the machine that is running the agent). But using the trusted servers feature available in the Notes 6 and higher server, you could centralize this agent processing. (One agent on one server could purge deletion stubs in databases on remote servers). In fact, our recommendation to the customer was that they have a "purge control" database that has a series of documents. Each document lists the server name, file name, and day(s) of the week to process. A daily agent reads these control documents and performs the actual purge on the databases scheduled for that day of the week.

   formulaTime = Evaluate({@Text(@Adjust(@Now; 0; 0; 2; 0; 0; 0); "*")})
   inTwoDays.Innards(0) = Val("&H" & Strright(formulaTime(0), ":") & "L")
   inTwoDays.Innards(1) = Val("&H" & Strleft(formulaTime(0), ":") & "L")
   status = W32_ConvertTIMEDATEToText(0, 0, inTwoDays, tempTime, Len(tempTime)-1, retLen)
   textTime = Left(tempTime, retLen)
   Print "Setting replication cutoff to " & textTime & " in database " & server & "!!" & fileName & "..."

There is an undocumented parameter to the @Text function. The parameter is an asterisk (*). When you use @Text with this parameter, the result looks like a replica id. This undocumented parameter has been around since at least version 2 of Notes, and is still applicable in version 7 of Notes. About the only information I could find out about this parameter was in the Notes forums: http://www-10.lotus.com/ldd/46dom.nsf/0/8ae60229ab58af9385256a85006b5818?OpenDocument. I'm sure there other references to the parameter out there.

This code uses that parameter to convert a date/time value (exactly two days into the future) into a couple of hex values that are placed into the custom TIMEDATE structure. Everything to the right of the colon is converted from hex to decimal and stored in the first array position as a Long value. Everything to the left of the colon is also converted and stored similarly in the second array position. This gives an API-format date/time value of exactly two days into the future.

Also note that our code prints out the date/time that will be set in the database. If you don't want to use that, then you can eliminate that statement from the script library. It also makes the "W32_ConvertTIMEDATEToText" declaration unnecessary, so that could be removed. We found it useful to have the statements in the log that indicate what value is going to be set. It also helps for debugging to convert the date/time structure to a string so you can see the value - the structure is very difficult to understand when you're looking at it in debugger.

   Call W32_OSPathNetConstruct(0, server, fileName, fullPath)
   status = W32_NSFDbOpen(fullPath, hDb)
   If status <> 0 Then
      textTime = "API error " & Cstr(status) & " opening database " & server & "!!" & fileName
      Error 4000, textTime
   End If

The path to the database is generated through the API. Notice back in the declarations how the fullPath variable is defined as String*256. That definition tells LotusScript to allocate exactly 256 characters for the string size. Normally LotusScript dynamically allocates string space. But that won't work in the API - the fixed size is needed because that variable acts like a return value from the API call (the API call sets the value).

Once the API path is generated, the database is opened through the API. If this fails, we use our error handling routine to trigger an error that is bubbled up to the calling subroutine. The error message is placed into a variable that has other purposed in the code. This is only done so we don't have to define another variable just for the error handling.

   status = W32_NSFDbReplicaInfoGet(hDb, replicaInfo)
   replicaInfo.Cutoff = inTwoDays
   status = W32_NSFDbReplicaInfoSet(hDb, replicaInfo)
   If status <> 0 Then
      textTime = "API error " & Cstr(status) & " setting replicaion info in db " & server & "!!" & fileName
      Call W32_NSFDbClose(hDb)
      Error 4000, textTime
   End If

The replication information is then read in from the database that was just opened. All other settings in the replication information are left alone with the exception of the cutoff date. That value is set to the "2 days into the future" value set above. The updated value is then stored back into the database. Note that we don't have to save anything - setting the value is all that is needed. If setting the replication information results in an error, then again we use the built in error trapping to bubble an error back up to the calling subroutine.

   status = W32_NSFDbClose(hDb)
   Exit Sub
Repeater:
   Error Err, Error$ & Chr$(10) & " at line " & Cstr(Erl) & " of procedure " & Getthreadinfo(1)
End Sub

Finally, the database is closed and the subroutine is exited. At the bottom is our standard error handing routine. So, this subroutine can be called for any server and file name you want, and the deletion stubs will be purged from the database. Note that, immediately after using this code, the replication settings will still have a date in that field if you look at the settings with a client. (The setting will revert back to the current date/time that the value was saved - Notes won't store the two days in the future value but just knows to purge deletion stubs).

Please note that you probably shouldn't use this code on a database that is replicated. Removing deletion stubs from a distributed database opens up the possibility that previously deleted documents will be replicated back in (the previously deleted documents will reappear). We only use this on stand-alone databases that have temporary documents (documents getting created/deleted frequently).