Is there a technical document to overview mining data from SQL to Lotus?
Is datamining (picking data from sql database and putting in lotus notes database)time consuming and would use server resources considerably?
if i assume that i need to pick 2-3 thousand records from sql and put them in lotus notes through an agent(lotus script) , how long would it take approximately?
if the agent used for above mentioed purpose has to search for new additions if any in SQL table and search for updations if any in 2-3 thousand records , how long would it take approximately?
Please help.
Thanks,
Babita
Subject: SQL and Lotus
Have you looked at Lotus Enterprise Integrator (LEI)? http://www.lotus.com/lei
Subject: SQL and Lotus
If you expect that your needs will remain small - 3000 rows isn’t much - you can do just fine with Lotusscript, though it will be script-intensive. Performance will be ok. I would expect maybe 25-35 seconds to create the documents, depending on a whole raft of variables. The limiting factor here is complexity. A script to do as you describe can get pretty complex in a hurry.
To connect to SQL server/Oracle/other RDBMS from Lotusscript you have two options, the ODBC extension for Lotusscript or LSXODBC, and the Lotus connectors. The Lotus connectors are the better option not least because using them you do not need a connection on the odbc control panel, or DSN. Both options are described in Designer Help under \Help on the server or on the workstation if you have Designer installed.
The lsx - a DLL in a Lotusscript wrapper - for the odbc extension is called LSXODBC, likewise the lsx for the Lotus Connectors is the LCLSX. Look in the enterprise integration forum - eiforum.nsf - for discussion threads on the LCLSX and the OLE DB connector. There are some more or less complete script samples there under my name.
If you have a little money to spend or if you expect your requirements to get more complex you should look at Lotus Enterprise Integrator or LEI. This is a form-based tool for doing just as you describe. (Percussion makes a similar product called Notrix.) You can think of LEI as a form-based front end for the Lotus Connectors, for example there’s a form representing the OLE DB connector and so on, and instead of writing a script you fill out a form mapping the various fields.
There might be something helpful at redbooks.ibm.com.
Subject: SQL and Lotus
Search the Help db for ODBC and you’ll get lots of info about the code you can write to run an SQL query against a relational db. I personally use such code to both create records in Notes from Oracle and to pull Oracle data into Notes to use real-time.
I’ve got a schedule agent that runs weekly to pull product data into Notes from Oracle. The agent creates >40K documents and runs in less than 15 minutes. I just delete all docs and let the agent recreate them because it is much faster than trying to figure out what changed and then update the data.
Subject: RE: SQL and Lotus
I hate that, when people delete all the documents and create new ones.
Yes, the script might take a little longer to run if you do a proper replication, but think of the advantages:
Doclinks (and web browser bookmarks if a web app) will continue to work.
You won’t have hundreds of thousands of deletion stubs cluttering up the database, taking up space, and slowing view indexing.
Replication with other Notes replicas will be reasonably efficient because you’re only replicating the data that’s changed.
Plus, on the other hand, the script might not take any longer to run. It depends how many changes you have. Reading documents is faster than creating new ones, and deleting lots of documents can also take time.
There’s a replication algorithm designed for easy reuse in this download, so you don’t even have to write it yourself: http://www-10.lotus.com/ldd/sandbox.nsf/ecc552f1ab6e46e4852568a90055c4cd/96d6da0e9224105485256fa800506d46?OpenDocument
In answer to the original question, you might also find it helpful to read this book: http://publib-b.boulder.ibm.com/Redbooks.nsf/RedbookAbstracts/sg246067.html?Open
If you can’t afford to go with LEI, then the chapter on the LC LSX will be especially helpful.