I'm facing the following problem. I've got BMC ProactiveNet running on SQL Anywhere OEM. My intention is to ship the data from SQL Anywhere to MySQL standing at a remote location. MySQL will live-feed the application.
The problem is that I need to use MySQl(PostgreSQL possibly) due to budget constraints. It definitely limits my options. Is there anything you'd recommend to implement this solution?
I had an idea to use an open-source tool, Pentaho to pull/push the data. I'm not sure however if it will be quick enough and what's more if trasactions will be transfered in a live mode.
Thanks a lot.
asked 29 May '12, 04:50
I will assume you are using SQL Anywhere 11.0.1... if that is not correct, please let us know.
First of all, your "authenticated OEM" copy of SQL Anywhere may or may not be set up to allow you to run unrestricted home-grown applications and queries against the database... you may only be able to run applications that come with vendor's product.
I will assume you CAN run unrestricted stuff on your database; e.g., you can connect from dbisql etcetera, and you have a user id and password that will let you run stuff ... if that is not correct, please let us know.
Version 11.0.1 of SQL Anywhere does support server class 'MYSQLODBC' in the CREATE SERVER statement which is SQL Anywhere's way of implementing "linked" or "federated" databases, also known as "proxy tables": http://dcx.sybase.com/1101/en/dbreference_en11/create-server-statement.html
If you can establish an ODBC connection from SQL Anywhere to your MySQL database, you can use CREATE EXISTING TABLE http://dcx.sybase.com/1101/en/dbreference_en11/create-existing-table-statement.html on SQL Anywhere to create a "proxy table" (a kind of view) that points to a real table on MySQL. You can then INSERT to the proxy table on SQL Anywhere and the rows will appear on MySQL.
If that kind of row-level transactional processing isn't fast enough for you, you can use UNLOAD TABLE or UNLOAD SELECT http://dcx.sybase.com/1101/en/dbreference_en11/unload-statement.html on SQL Anywhere to create a file that can be processed by MySQL's LOAD DATA INFILE statement http://dev.mysql.com/doc/refman/5.1/en/load-data.html
CAVEAT EMPTOR: I have no experience with LOAD DATA INFILE. In particular, I don't know what limitations it has, or how fast it runs... but the docs seem promising.
If you want to fully automate the UNLOAD / LOAD process, you might be able to use SQL Anywhere's FORWARD TO statement http://dcx.sybase.com/1101/en/dbreference_en11/forward-to-statement.html to ship a string containing the MySQL LOAD statement, from SQL Anywhere to MySQL, where it will be executed: Step 1, UNLOAD the data, Step 2, build the LOAD statement in a string, Step 3, send the statement to MySQL via FORWARD TO... with all those steps run on SQL Anywhere; MySQL is just a slave.
CAVEAT EMPTOR: I have no experience using FORWARD TO to send SQL statements to MySQL. However, it does work with MS SQL's BULK INSERT statement to do exactly the same thing, as described here: http://sqlanywhere.blogspot.ca/2011/05/unload-select-bulk-insert.html
If you have any questions about any of this, just ask.