Ship/externalize data to MySQL

[Former Member]

Hey,

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.

[VolkerBarth]

For ordinary databases, I would suggest to use SQL Anywhere's builtin Remote Data Access (aka "Proxy tables") feature - both for one-time data migration and continuous data transfer.

However, this may be inappropriate for your OEM database.

[Former Member]

Will it affect the performance of the source server? I can't hit the source database server, only ship the data and play with it somewhere else (mysql).

What do you mean by this being inappropriate for OEM database?

[Former Member]

What's more is that it makes it possible to pull the data from a remote location into my SQL anywhere db. Not the other way round:(

[reimer_pods]

AFAIK remote tables are not per se read only. Configured accordingly it would be possible to insesrt rows into such a table.

See http://sqlanywhere-forum.sap.com/questions/11767/proxy-tables-live-update-or-one-time-only

[VolkerBarth]

Well, OEM databases are bound to a specific application and are therefore usually restricted in the way you can use them with other tools - particularly if you're the user (and not the developer/vendor) of such an application and database:)

[Former Member]

That's what I thought at the beginning before I started working on my "project";) So far, to be honest, I haven't bumped into any issues (creating my own views/tables included). The chart below kinda shows that it is just a different edition of the database:

http://www.sybase.com/detail?id=1068247

Once again, I'm working with BMC ProaciveNet.

[Breck_Carter · Accepted solution]

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.

[Former Member · Accepted solution]

Hm..I wonder...is it against the license to hit the OEM database? Frankly speaking I was able to set up an etl job with Pentaho Spoon that tranfers data from SQL Anywhere to MySQL. That refers to your two concerns i.e. I can odbc to this version and I can run queries against it.

Per suggestion I will try to set up proxy tables and set up remote inserts to MySQL.

The thing needs to be real quick. The data, as I said at the beginning, comes from BMC ProactiveNet which collects monitoring data and so lots of data is generated every second.

[VolkerBarth · Accepted solution]

Just two remarks:

• There may be a difference between "you CAN" and "you ARE ALLOWED TO" w.r.t. licensing questions - but that's beyond our scope...

• The export feature as described in Breck's blog article is really great and fast - I just used it the other day to migrate a v12 database to MS SQL. If that does work as well with MySQL, too, then I would congratulate:) - And yes, Breck, that's a BIG THANKS:)

[Former Member · Accepted solution]

I've raised the inc with BMC to enquire about the legality of my intention:)

Thanks much for some brilliant suggestions!

[Breck_Carter · Accepted solution]

Volker's argument about "you CAN" versus "you ARE ALLOWED TO" certainly applies to, for example, using the Developer's Edition of SQL Anywhere. However, I would be very suprised if there was a difference between "you CAN" and "you ARE ALLOWED TO" when it comes to a company like BMC.

Please keep us informed about both your legal and technical progress!

[Former Member · Accepted solution]

I'm trying to connect to MySQL with Sybase Central. I tried both, adding the server manually (create server ...), and with the wizard. Servers get added but whenever I try to test the connetion it fails with:

[Sybase][ODBC Driver][SQL Anywhere]Unable to connect to server 'xxxx': [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified SQLCODE: -656 SQLSTATE: HY000 SQL Statement: SELECT FIRST table_name FROM dbo.sp_remote_tables( 'xxxx', NULL, NULL, NULL, 1 ) ORDER BY 1

It's odd. Other applications are able to use DSN and connect to MySQL and the dsn itself is ok (you can test the connection with the ODBC administrator).

It's not a problem with bitness. It is Win Server 2008, 32.

[VolkerBarth · Accepted solution]

How are you running the database engine - as normal application or as service?

Note that the ODBC DSN must exist on the machine the database engine is running on (and not for the client machine, in case they are different), and for engines running as service, it needs to be a system DSN.

---

I guess it would be worthwhile to ask this as a separate question:)

[Breck_Carter · Accepted solution]

@Volker: Well said!

"Note that the ODBC DSN must exist on the machine the database engine is running on (and not for the client machine, in case they are different), and for engines running as service, it needs to be a system DSN."

...except it is exactly wrong :)... the DSN has to exist in the registry of the client machine.

And the comment about being a system DSN only applies to DSNs on the same machine as the server.

I once mocked an end-user for not understanding where DSNs were located, and now I realize that I will burn in that level of hell devoted to arrogant techies 🙂

(mocking YOU is a small sin, by comparison 🙂

[Breck_Carter · Accepted solution]

@Maciej: The message says "Data source name not found"... so the fact that "Other applications are able to use DSN" is moot... in THIS case, you have (a) omitted the DSN name from the connection string, (b) spelled the DSN name wrong, or (c) the DSN doesn't exist on this client machine, from which you are connecting.

[VolkerBarth · Accepted solution]

@Breck: I'm quite sure that for remote data access, the DSN is specific to the database server, not to the client machine.

[Breck_Carter · Accepted solution]

@Volker: Although I did forget this was a remote data access question, "client machine" means "the machine making the database connection", and in the case of remote data access it is the local database server itself that is the client machine for the remote database. DSNs must always exist on the client side of a client-server connection, not the server side.

[Breck_Carter · Accepted solution]

Volker has reminded me this is a remote data access discussion, so option (c) needs clarification: For a remote data access client/server connection from a "local" SQL Anywhere database to a "remote" MySQL database, the client is the local SQL Anywhere database (because that is where the CREATE SERVER statement is executed), so the machine where that local SQL Anywhere database is running is where the DSN needs to be located.

[VolkerBarth · Accepted solution]

@Breck: Ah, this is discussion strives for preciseness - step by step:)

You're fully right, and my wording was not exact - "the database server" was meant to be the SQL Anywhere server machine making the remote data access connection to the MySQL server.

My personal experience is that I often forget to create ODBC DSNs on the SA server for remote access - just because my client machine (having theses DSNs) can connect to those remote databases directly... and I suspected Maciej running into a similar problem:)