Problem Replicating Domino to DB2 -- DOMINO400

Greetings,

We have a Domino Database located on AS/400 A (V4R5 OS/400 and 5.0.2a on
Domino).  We have an agent we have written to perform the replication to a
DB2 database located on AS/400 B (V4R5 OS/400).  The agent looks like the
following:

Option Public
Option Declare

Uselsx "*LSXODBC"

Dim ODBCcon As ODBCConnection
Dim ODBCconnected As Variant ' set to true when connected to an ODBC data
source

Sub Initialize
     ' setup the connection to the as400
     connectODBC

     'Print "Start run-----------------------------------<br>"

End Sub

Sub connectODBC
     Set ODBCcon = New ODBCConnection
     If ODBCcon.ConnectTo("...", "..." , "...") Then    ' Names removed to
protect the innocent.
          ODBCconnected = True
     Else
          Print "Replicate Item Documents Is NOT Connected"
          Print ODBCcon.GetExtendedErrorMessage,,
          ODBCconnected = False
     End If
End Sub

Sub process
     ' get all changed documents
     Dim session As New notessession
     Dim db As notesdatabase
     Set db = session.currentdatabase

     Dim dc As notesdocumentcollection
     Set dc = db.unprocesseddocuments
     'Set dc = db.alldocuments

     Print "Item Document Replication Running on " & dc.count & "
documents."

     Dim doc As notesdocument
     Set doc = dc.getfirstdocument()

     Dim ctr As Integer

     ' for each document
     While Not doc Is Nothing
          Print "Running on document " & doc.DocumentID(0)

          'Print "Processing Document " & doc.DocumentNumber(0) & "
datasource " & doc.datasource(0) & "<br>"
'         Print doc.form(0) & "-" & doc.DataSource(0) & "<br>"
          If ( doc.form(0) = "DocumentDetail" ) Then
          ' see if need to update existing document.
               If ( existsAS400 ( doc ) ) Then
                    If ( doc.DeleteDocument(0) <> "Yes" )  Then
          '  if so update the as400
                         'Print "Update Document " & doc.DocumentNumber(0)
& "<br>"
                         Call updateAS400 ( doc )
                    Else
                    ' See if need to delete document.
                         If ( doc.DeleteDocument(0) = "Yes" ) Then
                    '         'Print "Delete Document " &
doc.documentnumber(0) & "<br>"
                              Call deleteAS400 ( doc )
                         End If
                    End If
               Else
                    '  See if need to insert document.
'                   Print "add<br>"
                    If  (doc.DeleteDocument(0) <> "Yes") Then
                         'Print "Insert Document " & doc.documentnumber(0)
& "<br>"
                         Call addAS400 ( doc )
                    End If
               End If
          End If
     ' mark the document as processed
          Call session.updateprocesseddoc ( doc )
     ' loop
          Set doc = dc.getnextdocument ( doc )
     Wend

End Sub

Sub Terminate
     Print "Finish Run----------------------------"
End Sub

Function existsAS400( doc As notesdocument ) As Integer
     ' return true if this document exists on the as400

     Dim result As ODBCResultSet

     Set result = runQuery ( " select count(*) from bpcsf.itemdocs " & _
     "where DOCUMENTID = '"_
     & doc.GetItemValue( "DocumentID" )(0) & "'")
     result.NextRow
     existsAS400 = ( result.GetValue(1) <> "0" )

End Function

Sub updateAS400 ( doc As notesdocument )
     ' update the selected document on the as400

     Dim sql As String

     Print "Updating a document " & doc.getitemvalue("DocumentID")(0)

     sql = "update bpcsf.itemdocs set ITEMNUMBER"  & " = '" &
doc.GetItemValue ( "ItemNumber" )(0) & "'"_
     & ",  DOCSEQNBR = " & doc.GetItemValue( "DocumentSequenceNumber" )(0)
& ", "_
     & "REVNBR = '" & doc.GetItemValue( "RevisionNumber" )(0) & "', " _
     & "DOCTYPE = '" & doc.GetItemValue( "DocumentType" )(0) & "', " _
     & "DOCDESC = '" & doc.GetItemValue("DocDescription")(0) & "', " _
     & "PRINTONSO = '" & doc.GetItemValue("PrintOnShopOrder")(0) & "', "_
     & "WORKURL = '" & doc.GetItemValue("WorkingDocumentURL")(0) & "', "_
     & "VIEWURL = '" & doc.GetItemValue("ViewingDocumentURL")(0) & "', "_
     & "ACTIVEREV = '" & doc.GetItemValue("ActiveRevision")(0) & "', "_
     & "WHODISC = '" & doc.GetItemValue("WhoDiscontinued")(0) & "', " _
     & "DOCVENDOR = '" & doc.GetItemValue("Vendor")(0) & "', "_
     & "CREATEDBY = '" & doc.GetItemValue("CreatedBy")(0) & "', "_
     & "AUTHORS = '" & doc.GetItemValue("Authors")(0) & "', "_
     & "DOCUMENTID = '" & doc.GetItemValue("DocumentID")(0) & "'"

     sql = sql & " where DOCUMENTID = '"_
     & doc.GetItemValue( "DocumentID" )(0) & "'"

     Print sql

     runQuery ( sql )

End Sub

Sub addAS400 ( doc As notesdocument )
     ' add the selected document to the as400

     Dim sql As String

     Print "Adding a document " & doc.getitemvalue("DocumentID")(0)

     sql = "insert into bpcsf.itemdocs ( " & _
     "ITEMNUMBER, "_
     & "DOCSEQNBR, "_
     & "REVNBR, "_
     & "DOCTYPE, "_
     & "DOCDESC, "_
     & "PRINTONSO, "_
     & "WORKURL, "_
     & "VIEWURL, "_
     & "ACTIVEREV, "_
     & "WHODISC, "_
     & "DOCVENDOR, "_
     & "DATECREATED, "_
     & "CREATEDBY, "_
     & "LASTACCDTE, "_
     & "LASTMODDTE, "_
     & "AUTHORS, "_
     & "DOCUMENTID) "

     sql = sql &_
     "values("_
     & "'" & doc.GetItemValue("ItemNumber")(0) & "', "_
     & doc.GetItemValue("DocumentSequenceNumber")(0) & ", "_
     & "'" & doc.GetItemValue("RevisionNumber")(0) & "', "_
     & "'" & doc.GetItemValue("DocumentType")(0) & "', "_
     & "'" & doc.GetItemValue("DocDescription")(0) & "', "_
     & "'" & doc.GetItemValue("PrintOnShopOrder")(0) & "', "_
     & "'" & doc.GetItemValue("WorkingDocumentURL")(0) & "', "_
     & "'" & doc.GetItemValue("ViewingDocumentURL")(0) & "', "_
     & "'" & doc.GetItemValue("ActiveRevision")(0) & "', "_
     & "'" & doc.GetItemValue("WhoDiscontinued")(0) & "', "_
     & "'" & doc.GetItemValue("Vendor")(0) & "', "_
     & "Current Date, "_
     & "'" & doc.GetItemValue("CreatedBy")(0) & "', "_
     & "Current Date, "_
     & "Current Date, "_
     & "'" & doc.GetItemValue("Authors")(0) & "', "_
     & "'" & doc.GetItemValue("DocumentID")(0) & "')"

     Print sql

     runQuery ( sql )

End Sub

Sub deleteAS400 ( doc As notesdocument )
     ' delete the selected document on the as400

     Dim sql As String

     Print "Deleting a document " & doc.getitemvalue("DocumentID")(0)

     sql = "Delete from bpcsf.itemdocs "

     sql = sql & " where DOCUMENTID = '"_
     & doc.GetItemValue( "DocumentID" )(0) & "'"

     Print sql

     runQuery ( sql )

End Sub

Function runQuery( sql As String ) As ODBCResultSet

     Dim qry As New ODBCQuery
     Dim result As New ODBCResultSet
     ODBCcon.disconnect
     connectODBC
     result.close
     Set qry.Connection = ODBCcon
     qry.SQL = sql
'    debug "<!-- runQuery = " & qry.SQL & " -->"
     'Print "runQuery " & qry.SQL & "<br>"
     Set result.Query = qry
     result.Execute
     Set runQuery = result

End Function

This agent is set to run on Add/Change of documents in Domino.  If I change
the agent to run on Selected documents, in effect it appears to be running
from my PC and everything works great.

When it is set to run on Add/Change of documents and run on the server,
based on the "debug" print statements I have in the above source, I get
connected to AS/400 B okay.  It appears to function as far as finding
existing entries or determining it is a new entry and makes it to the
proper Sub (Insert, Update, or Delete).

What is NOT happening is the actual SQL does not appear to do anything when
run from the Server (again, if I run the agent on selected documents from
my PC everything works great).  That tells me the basic SQL syntax is
correct.

My questions are these:
1. What is wrong?
2. With no answer there, how can I debug this stuff on AS/400 B.  I try to
watch for the QRWTSRVR jobs but I can't seem to catch them on a consistent
basis and the one I did catch provided the message "Token
<END-OF-STATEMENT> was not valid.  Valid tokens: (."  That message
indicates an SQL syntax problem but again, if I run the agent on selected
documents, everything works fine which should mean the SQL syntax is
correct.

Any help will be greatly appreciated.

Dwight Slessman