I have a database of several million subscribers to a couple of eletters. We host the various applications to manage the database, as well as the database itself. Every morning we have a process that does a query, saves all email addresses to a text file, then we FTP it to the email vendor where they load into their oracle database. Then send the emails, and they then send us an FTP file of bounces and other things we need to record to our database.
The problem is... that this just takes too long to create the file and send it. I'd rather get into a mode of just sending updates to them -- and vice versa.
Is this sort of data syncronization across different db platforms an appropriate task for Mobilink?
I should add that my database is SQLA 9.0.2 -- I don't know the version of the Oracle db.
Technically speaking, it sounds like a job for MobiLink. If you have the ability to make DDL and other changes to the Oracle database to support the MobiLink server, it might even be easy... and it will run like a rocket, guaranteed.
But you don't, right? Have access to the Oracle database, I mean.
Practically speaking, unless you have a warm and fuzzy relationship with the email vendor, it's probably a non-starter. In the real world, "Oracle DBA" and "warm and fuzzy" are two phrases not often used together.
Or, you could hire a consultant who knows how to speak "Oracle DBA", someone from Sybase Professional Services, perhaps, or...
answered 12 Oct '10, 18:46
There are two ways you can use MobiLink to sync to a database you don't have DDL access to.
The most straight forward is to use the -cs server option. This lets you put the server's system tables in a different database than the consolidated. In this case you would have an SA database you control which contains the ML system tables and the MobiLink server would have a -cs switch specifying an ODBC connection string to connect to that SA database, and a -c switch specifying an ODBC connection string to connect to the Oracle database. The big caveat with -cs switch is that it requires server to use the Microsoft Distributed Transaction Coordinator, which can be slow. The -cs switch was added in version 11 and you can use a version 11 server to sync with your version 9 SA database. See http://dcx.sybase.com/index.html#1100en/mlserver_en11/ml-syncserver-s-5359674.html*d4e2495
The alternative is a bit more complicated. You can use the MobiLink direct row API to inspect the upload stream and do whatever you want with it. As with the first option, you'll need an SA consolidated for ML to use. See http://dcx.sybase.com/index.html#1100en/mlserver_en11/handle-uploaddata-ml-scriptsref.html*d4e15667
There's an example from that page that will do almost what you want:
This opened a JDBC result set over the data that was uploaded from the remote and then called the magic function for each row in it. You just have to write the magic function to do what you need done. For example, you could connect to the Oracle database and insert the row, or you could toss the rows in a file and later email them. If you also want to send a download to the remote, there is API for modifying the download stream. See http://dcx.sybase.com/index.html#1100en/mlserver_en11/ml-objectbased.html for the full direct row API docs.
answered 13 Oct '10, 21:12