Solved

LS/ODBC agent

Posted on 2006-07-06
18
635 Views
Last Modified: 2013-12-18
Hi all,

I have a lotusscript agent imports almost 20000 records from an external data source into a lotus notes database and that's working very well.

However, the data in any of those imported records changes frequently in the external database, so I am having to delete all those records from my lotus database and import them again every night which is cumbersome and I am sure inefficient for the database.
Now I have gone through similar path with Marylin to whom I am very greatful because she has tried to help with one of those tough agents at one stage, but that agent we worked on had static old records, nothing changed expect the daily data which was imported.

In this agent most fields in the records may need to change, product id, supplier, etc, and the change may need to happen on old and new records so doing a checking while import on every field on 20,000 record I believe maybe another nightmare.

It would really appreciate some ideas/ direction on the best way to change this agent so that I don't have to delete and reimport ever record into the database every night.

Thank you all in advance.
Varvoura
0
Comment
Question by:varvoura
  • 10
  • 8
18 Comments
 
LVL 63

Expert Comment

by:Zvonko
ID: 17050064
I am doing that check for a userid maintenence (RACF user, not Domino) in a Domino database like this:
1.) Before I start the compare (the replication with RACF), I set a start time stamp.
2.) Every user from RACF is searched in Domino database userid table. That is my primary key. If such document does not exist is a new user document is created.
3.) I store beside all fields used in the user document also a "compare" composite field. That field is the combination of all relevant field values which define userid as changed. For example, the last logon time value is not such a change value. With that one composite field I can compare at once wether my record changed or not.
4.) For every document, new one or changed one, I set the new timestamp for last looking in that document, even wen nothing changed. That is for delete purposes. Documents with that last lookup timestamp older then my initial timestamp are not looked up and therefore no more in the external RACF repository. They can be deleted or archived.

That sounds like an overhead to delete all and create all fresh. But deleting and creating documents is from my experiance more costintensive operation especialy when you have many views on that documents. Also the onDiskStructure of the database is colapsing and expanding. So basicaly is my expression that comparing is less expensive then deleting and creating. And when you want to store some history informations which you cannot get from external source, then you have no choice; you have to do the upper compare and update.

0
 
LVL 63

Expert Comment

by:Zvonko
ID: 17050123
One more word for the history: the history is the field change history. So when my upper composite field record is not matching the RACF side compare record, then I go field by field and store the old and new value for every changed field, but only for changed fields. That change hostory log is a log that I cannot get from outside. Therefore I have to keep my user documents or at least store an archived copy for the change history. Of course that log could be kept also in log documents or event in external log database, but by that way I have al user relavant informations in one place without aditional aganets or views to collect the history.



0
 

Author Comment

by:varvoura
ID: 17050294
OK, since I am not an expert, I'll have to go through this with you step by step to really understand the process.

1)Where do you set a time stamp, and how? in the agent? on those documents that you want to import again?

2)Can I do something similar to this in lotus notes? maybe a lookup view which store all the productId or whatever field that's suppose to be unique in the external database. and if a productid does not exist then create the entire record in notes, otherwise wait?

3)OK, a bit confused here, so sorry.  
Are you storing beside every field (which was imported from external source) a composite key to identify whether on not that specific field has been changed?
I don't understand this too well

4) I don't need to archive or delete any, do I still need this timestamp on documents

Yup it is an overkill and especially when this database has to replicate to other servers too and that's why we're not forced to find a better way to do this.

I think if you explain a bit more about your composite key, i'll be able to understand better your last comment.

Thanks heaps
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 17050558
1.) The initial timestamp is the time value in a local var in the replication Agent. The lookup timestamp is set in every looked up or new created document as document field value. Deleted are all document with that lookup timestamp field value older the Agent lookup run starting initial time.

2.) I described that I search for the primary key in lotus notes view. If not found, then I create new document. I do not understand the wait you stated at end. Wait for what?

3.) No. I do not store for a field a comapre value. I store a value mask for all fields. Here an example:
Field Name: RACFDATA
Data Type: Text List
Data Length: 518 bytes
Seq Num: 215
Dup Item ID: 0
Field Flags: SUMMARY

"UID=NA2PAN;Zvonko;FULL;XIOIF5;18.01.05 19:51 CMWKBR;Com IT 5;zvonko@mycompany.com;MCH X;+1 (809) 321-29251;I114;NOTIFY IS JOB; ;DOT IT;M:ALFRED.BROWN@MYCOMPANY.COM;AL000371;C=C1406*G=61610000;UK:10002521,RK:10004544; ;ACCOUNT0;LOGONNA2;1000000;1300000;NAT;A;8000; ;RZTSO;SRZSHR;56300;/tmp;/bin/sh;96.276;** 24.01.06/NA2CHM/ ** ACCOUNT AENDERUNG ** USERID ENTSPERRT;EE9EA255;06.128;06.187;0;90; ; ;A; ;0000000;NADMN;8;NAXSYSP;NADMN;#MYCOMPANY;ATXSYSAD;USER;CMINF1;CMINFO;USERVERW;"

The composite key is not realy neccessary if you not want to observe chnage history log. Then you can store all field values at every store. But...

4.) You definitely need that timestamp. Otherwise you do not know which dataset to delete because they was existant in external database but now are deleted in external database. You can delete all local documents which are NOT updated at this run.

So simplified for your scenario is your agent someting like this:

Sub Initialize
  Dim StartTime As New NotesDateTime( "" )
  Call StartTime.SetNow

      Set session = New NotesSession
      Set db = session.CurrentDatabase
      Set UserView = db.GetView("($User)")
  '// get every record from your external source
  '// and compare the primary key to you notes view

      Do While " new Records comming "
            UserID = "your primary key"
                  Set doc = UserView.GetDocumentByKey(UserID)
                  If doc Is Nothing Then
                        Set doc = db.CreateDocument
                        doc.Form = "UserForm"
                  End If
                  doc.LastCheck = Now
' // set all document fields from record to document
                  Call doc.Save( True, False )
      Loop
      Set doc = UserView.GetFirstDocument
      While Not ( doc Is Nothing )
            Set nextdoc = UserView.GetNextDocument( doc )
            Set docRemoved = doc.GetFirstItem( "Removed" )
            If (docRemoved Is Nothing) Then
                  Set LastCheck = doc.GetFirstItem( "LastCheck" )
                  If Not( LastCheck Is Nothing) Then
                        If (StartTime.TimeDifference( LastCheck.DateTimeValue ) > 0) Then
                              Msgbox "UserID deleted: " & doc.UserID(0)
                              Call doc.Remove( True )
                        End If
                  End If
            End If
            Set doc = nextdoc
      Wend
      Call UserView.Refresh


0
 
LVL 63

Expert Comment

by:Zvonko
ID: 17050604
Sorry, that Removed item is confusing in your scenario.
Look better at this:

Sub Initialize
  Dim StartTime As New NotesDateTime( "" )
  Call StartTime.SetNow

     Set session = New NotesSession
     Set db = session.CurrentDatabase
     Set UserView = db.GetView("($User)")
  '// get every record from your external source
  '// and compare the primary key to you notes view

     Do While " new Records comming "
          UserID = "your primary key"
               Set doc = UserView.GetDocumentByKey(UserID)
               If doc Is Nothing Then
                    Set doc = db.CreateDocument
                    doc.Form = "UserForm"
               End If
               doc.LastCheck = Now
' // set all document fields from record to document
               Call doc.Save( True, False )
     Loop
     Set doc = UserView.GetFirstDocument
     While Not ( doc Is Nothing )
          Set nextdoc = UserView.GetNextDocument( doc )
          Set LastCheck = doc.GetFirstItem( "LastCheck" )
          If Not( LastCheck Is Nothing) Then
             If (StartTime.TimeDifference( LastCheck.DateTimeValue ) > 0) Then
                Msgbox "UserID deleted: " & doc.UserID(0)
                Call doc.Remove( True )
             End If
          End If
          Set doc = nextdoc
     Wend
     Call UserView.Refresh

0
 

Author Comment

by:varvoura
ID: 17057368
Zvonko,

Great, I'll try your idea today and we'll let you kow how I go

call doc.remove(True), I presume that you are removing the documents when the last checkdate has a different value than the start time.
Is this it?

0
 
LVL 63

Expert Comment

by:Zvonko
ID: 17057480
Different is not enough.
If the difference is Positive, then is the document time stamp ("LastCheck") older then agent run start timestamp ("StartTime"). That mean, the document was not updated and is candidate for deletion.

Here is that difference calculation line:
  If (StartTime.TimeDifference( LastCheck.DateTimeValue ) > 0) Then
     '// delete because it is last checked older then this run start time


0
 

Author Comment

by:varvoura
ID: 17057727
Yes, i understood the line, but I was rather concern about the remove bit.
Anyway, let me try to see how you go.

Thanks a million
0
 

Author Comment

by:varvoura
ID: 17058828
Almost working.

1)First I create a small database & a table in microsoft access
2)set the primary key in table
3) then create a field LastCheck on the form(editable)
4)modified my agent to accomodate changes recommended by you
5)then created a document in notes database so that I don't have a problem with "object variable not set" when I try to match the external source primary key with a field no on the form
6) ran the agent manually and here's the result

 a) On the first import when the agent runs, it seems to delete the first record in the notes view and imports the others(obviously because the primary key doens't find a match) but why is the first document deleted from the view, no changes were made to it externally so by right it should remain intact in the view

b)Then when i ran the agent again, that document is imported(for the same reason above)

c) however, when I change fields on my external source records and reimport again, nothing gets imported and the fields in my lotus notes remain with the old field values which are different than those in the datasource.


Dim Con As New ODBCConnection      
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim view As NotesView
      Dim nextDoc As NotesDocument
      dim nstatus as integer
      
      nstatus = Con.ConnectTo("","newdb", "newtable")
      
      If nstatus = False Then
            Msgbox "Unable to connect to database, please see your network administrator"
            Exit Sub
      End If
      
      Dim Qry As New ODBCQuery
      Set Qry.Connection = HOCon
      Qry.SQL = "select * FROM newtable"
      
      
      Dim res As New ODBCResultSet
      Set Res.Query = Qry
      Res.CacheLimit = 1000
      Res.FetchBatchSize = 1000
      Res.MaxRows = 0
      Res.Execute
      
      '-- Get First Row in ResultSet
      nstatus = Res.FirstRow
      
      If nstatus = True Then
            
            Dim StartTime As New NotesDateTime( "" )
            Call StartTime.SetNow
            
            Set db = session.CurrentDatabase      
            Set view = db.GetView("notesview")
            Dim doc1 As NotesDocument
            Set doc1 = session.DocumentContext
            Dim nunique As String
            
            nunique=doc1.field1(0) ' i am using this later to set it = to the primary key of external source
            
      End If
      
      Do While nstatus = True
            
            doc1.field1 = Res.GetValue("field1")
            Set doc1 = view.GetDocumentByKey(nunique)
            
            If doc1 Is Nothing Then
                  Set doc1 = db.CreateDocument
                  Doc1.form = "notesform"
            End If
            
           '-- Set form item
      '      Doc1.form = "notesform"
            doc1.LastCheck = Now
            Call doc1.Save( True, False )
                  
            Doc1.field1 = Cstr( Res.GetValue("field1") )
            Doc1.field2 = Cstr( Res.GetValue("field2") )
            Doc1.field3 = Cstr( Res.GetValue("field3") )
            Doc1.field4 = Cstr( Res.GetValue("field4") )            
            doc1.fieldn = Cstr(Res.GetValue("fieldn"))            
            
            
            Call doc1.ComputeWithForm(False,False)
            Call doc1.save (True, True)
            
            nstatus = Res.NextRow
      Loop      
      
      Set doc1 = view.GetFirstDocument
      While Not ( doc1 Is Nothing )
            Set nextdoc = view.GetNextDocument( doc1 )
            Set LastCheck = doc1.GetFirstItem( "LastCheck" )
            If Not( LastCheck Is Nothing) Then
                  If (StartTime.TimeDifference( LastCheck.DateTimeValue ) > 0) Then
                        Call doc1.Remove( True )
                  End If
            End If
            Set doc1 = nextdoc
      Wend
      Call view.Refresh
      
      nstatus = Res.Close( DB_CLOSE )
      nstatus = Con.Disconnect          
      

Could the reason be because i am running the agent manually?
Are you able to test my code from your end?
This is a simple example, but the real one is much more complex, but conceptually it is the same.

Thank you
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 63

Expert Comment

by:Zvonko
ID: 17058900
Did you try to Trace the Agent?
0
 

Author Comment

by:varvoura
ID: 17063841
Yes I have.

All good now except one small problem.

when I have the first record selected in the notes view and click to run the agent manually, the first record gets deleted, then when I run the agent again, this record is reimported into the notes database.

The funny thing is that when i select the second record in the view,it all runs fine and the first document doesn't get deleted.

I think this has something to with the way that I am initially setting doc1 = session.documentcontext but that's the only way I could do this to set the primary key to a value in the notes document.

Are you able to advise on this one?
0
 

Author Comment

by:varvoura
ID: 17063863
Even when I change the setting doc1 to set doc1 = view.getfirstdocument I still have the problem of the first record deleted on first run.

0
 

Author Comment

by:varvoura
ID: 17063870
NOTES HELP - remove methods on the Document object returned by DocumentContext,
I think that this is where the problem is, how can I avoid it?
Plus this agent usually runs in the background, I am only running this manually because I don't have access to the server.

0
 
LVL 63

Accepted Solution

by:
Zvonko earned 500 total points
ID: 17065164
You can start localy in Designer a local http by calling the local databse view or document or agent in browser.
That agent run in http is simmelar to scheduled agents. There you have anyway to change the agent schedul and selection.
0
 

Author Comment

by:varvoura
ID: 17066951
All working fine, thank  you so much for your ideas and help.
I was overwritten the document context with the create document, one I fixed this it all worked fine. Still try to run it manually.

I tried your local scheduled agent idea but I seem to be always asked for authentication for localhost.


The points are finally yours
0
 

Author Comment

by:varvoura
ID: 17067003
....If I delete a document in the external data source and run the agent, the record dosen't get deleted from the notes view. How can I can this to update correctly?

It doesn't seem to have a problem with modification.

0
 
LVL 63

Expert Comment

by:Zvonko
ID: 17071660
Then is something in time stamp compare going wrong.
Add a view refresh please:

     Call view.Refresh
     Set doc1 = view.GetFirstDocument
     While Not ( doc1 Is Nothing )
          Set nextdoc = view.GetNextDocument( doc1 )
          Set LastCheck = doc1.GetFirstItem( "LastCheck" )
          If Not( LastCheck Is Nothing) Then
               If (StartTime.TimeDifference( LastCheck.DateTimeValue ) > 0) Then
                    Call doc1.Remove( True )
               End If
          End If
          Set doc1 = nextdoc
     Wend
     Call view.Refresh
     
Also look into Document items for the value set in LastCheck.


0
 

Author Comment

by:varvoura
ID: 17080014
I already have view refresh in my script, so this wouldn't be the problem.
I'll check lastcheck field on document.
But if the lastcheck property is not populating correctly in the documents, why then I don't have any problems updating the fields in the view when they are changed in the ODBC source.

0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now