replicate a table from one sql datasource to another?

[m.ardito]

Hi,

I have a multisource dynamic vdb with just two models

- one connected to a mssqlserver datasource A

- one connected to a mysqlserver datasource B

with full rights on both.

I would like to "replicate" a table from A to B, keeping the same exact structure.

This could be needed at least once a day (overnight), the more, the better.

The target table could be from few thousands rows to  half a million

I digged a bit in to possible uses of:

- SELECT INTO  syntax but while it works with simple statements like "select 1 as id into A.test" (still quite unuseful to me), docs say it's deprecated

- INSERT INTO, with something like "INSERT INTO A.tablename (field1,field2) VALUES (value1,value2)"an dthis also works (pk not insereted, autoincrementing)

I never thought of useing Teiid like this, up to now, but what I wish if possible, would be to keep the B.table as soon as possible in sync with A.table

I know there are other solutions to do db sync (like SymmetricDS, eg) but I can't touch the A source (so I can't insert triggers there..)

Is this possible, atm, and what is the best approach in Teiid, if yes?

(probably related to Insert Into Support)

I also tried this

insert into dbtestB.table (DocumentDate, AccrualDate, LogNo, DocNo,FeeId)

select DocumentDate, AccrualDate, LogNo, DocNo,FeeId from dbtestA.table

but I get

Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 mysql5_dbtest: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@290f7995[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@55f98493 connection handles=0 lastUse=1455730804545 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@706efafd pool internal context=SemaphoreArrayListManagedConnectionPool@4a90a993[pool=dbtest] xaResource=LocalXAResourceImpl@2448dc28[connectionListener=290f7995 connectionManager=20381dfd warned=false currentXid=null productName=MySQL productVersion=5.1.63-0ubuntu0.10.04.1-log jndiName=java:/dbtest] txSync=null]

SQLState:  50000

ErrorCode: 30504

Thx

[rareddy]

insert into (...) select is best option to copy.

I believe transaction is timing out. You can try

- "autoCommitTxn=OFF;disableLocalTxn=true" on JDBC URL connection when you are running the query

- Try increasing the transaction timeout; search the forums, I believe I answered this one before, but do not remember top of my head.

- if your PK is sequenced, then try less amount of data each time, and run multiple times.

As for as sync is considered, yes you can do that in the Teiid, as long as updates to A are flowing through Teiid. You can write the "create insert/update/delete triggers" on the table A in Teiid, such that when there is an update, it is also reflected in the Table B. But if the update happens outside of Teiid then that will not be noticed.

Ramesh..

[m.ardito]

Thanks, Ramesh.

Unfortunately tableA is owned by a proprietary app and I can't make it flow through Teiid in order to allow triggers on its side...

For now, until I can completely change the app using tableB, which is mysql only, to use Teiid for all, the replication job could allow me a better workflow, skipping some external overnight ETL job, or at least so I hope.

I'll try your suggestion and will search past threads, in order to avoid the timeout, I'll report here later.

Marco

[rareddy]

When I say triggers on A, I mean triggers on the View table in Teiid that is representing the Table A in proprietary app. Not in the native app. The only restriction is any updates to native Table A in the proprietary app, must flow through Teiid, i.e your application.

[m.ardito]

Sorry, I guess it's me not understanding, or not explaining this well...

The owner proprietary app only speaks sqlserver, and although I have sysdba access, I prefer not to alter its dedicated sqlserver, eg, adding triggers, for safety reasons. I already discarded SymmetricDS for this, in the past.

I can read all its tables (over 400) in Teiid through a datasource (tableA is read on that DS, in fact).

Do you mean that I could create a Teiid view with its own triggers that could detect changes in the sqlserver source, and reflect those changes in my tableB model, updating/inserting/deleting records accordingly?

If yes, Teiid never stops to surprise me...

In the meanwhile, I am getting some results using connection parameters autoCommitTxn=OFF;disableLocalTxn=true;

atm I'm on a vpn, but tomorrow I'll do more externsive tests,

But the triggers path could allow a light constant update which is what I was looking for in the first place...

Marco

[rareddy]

I am NOT asking to modify the sqlserver table. I am asking to create INSTEAD OF TRIGGERS on a View in Teiid that represents the Table A. See example here

Update Procedures (Triggers) - Teiid 9.0 (draft) - Project Documentation Editor

DDL Metadata - Teiid 9.0 (draft) - Project Documentation Editor

for example:

CREATE VIEW A (x varchar(50),  y integer)
  AS
  SELECT x , y from sqlserver.A;

CREATE TRIGGER ON A INSTEAD OF INSERT AS
  FOR EACH ROW
  BEGIN ATOMIC
      INSERT INTO sqlserver.A (...) VALUES (NEW.value ...);
      INSERT INTO mysql.A (...) VALUES (NEW.value ...);
  END

CREATE TRIGGER ON A INSTEAD OF UPDATE AS
FOR EACH ROW
BEGIN
    IF (CHANGING.y)
    BEGIN
        UPDATE sqlserver.A SET y = NEW.y WHERE x = OLD.x;
        UPDATE mysql.A SET y = NEW.y WHERE x = OLD.x;
    END
END

CREATE TRIGGER ON A INSTEAD OF DELETE AS
FOR EACH ROW
BEGIN
    DELETE FROM sqlserver.A WHERE x = OLD.x;
    DELETE FROM mysql.A WHERE x = OLD.x;
END

Will create a View table that will keep both the Table A in mysql and SQLServer in sync, as long as all the updates to the "sqlserver.A" are flowing through the above View A.

HTH

Ramesh..

[m.ardito]

ok, I'm hopefully starting to get it, sorry

I didn't know about INSTEAD OF triggers... but, if I got it well this time, your example above supposes that my own app, flowing through Teiid could write both tables, together, A and B, using those triggers, so that they are always in sync. Good to know, anyway, but my own app will never write tableA data (in sqlserver or elsewhere), it will always only read from sqserver, since the proprietary data (an accounting networked app) owns it completely.

What I am trying to do in this case is to use Teiid just to replicate tableA from sqlserer to an identical tableB in mysql, frequently if possible, because in this way my current (mysql-only odbc) app could benefit from having a recent replica of tableA in the same mysql server it's currently using (in this way I query joins between mysql native tables and those replicated sqlserver tables. It's a kind of a temporary workaround).

Soon or later, my own app will just connect to a multisource vdb in teiid which reads all needed source on the same connection, allowing my app to reach whatever it needs, directly, without any replication.

[rareddy]

Ok, in hat case your MySQL table is copy of a SQLServer table. Then in that case take a look at Materialization feature in Teiid. Materialized Views | documents This does not exclude you from doing the above copy etc, but let you manage much better, and you will have access to materialization procedures for incremental updates etc.

[m.ardito]

Thanks, I'll dig into materialization, but that will be probably more useful when my own apps have teiid as their source so, not now.

Also, in this case I need to just read a whole remote table, I guess I could not have advantages materializing it, am I correct?

But it could be more useful if (when) I'll need to store something like a "cached view" based on one or more vdb sources... which could happen soon, anyway

About my replication job, as said above it seems that something like

insert into dbtestB.table (DocumentDate, AccrualDate, LogNo, DocNo,FeeId)

select DocumentDate, AccrualDate, LogNo, DocNo,FeeId from dbtestA.table

works now,thanks to your suggestion. But  to update the whole table, with this syntax I need to empty it before, and then run the statement to refill the table copy.

In one case, a source dbtestA.table of mine has many (half million) rows, and this workflow isn't a good idea, plus, only a few (fecent) records could change each day, but there are both a pk and a "last modifiied" timestamp so, after the first initial replica, each day I can probably just delete and insert again just changed rows...

This could be manageable (atm this is the overnight job done by a script using two separate recordsets, the source (sqlserver) and the destination (mysql).

If you think I could benefit from some particular teiid feature in this respect, please, let me know, I could easily miss some important info...

Marco

[m.ardito]

a further info about disabling transaction autocommit: from an ODBC client, or from squirrel with JDBC, I would like to set this option at request level, like is said here

Request Level Transactions - Teiid 8.12 - Project Documentation Editor

If I got it right, it should be possible to set it with something like  SET PROP_TXN_AUTO_WRAP OFF;

but, eg, in squirrel, on a standard connection, opened without the autoCommitTxn=OFF parameter,

if I use that statement (which runs fine) and then issue my replciating SQL, it fails as it did before.

Is there an example on using this mode, anywhere?

Marco

[rareddy]

but that will be probably more useful when my own apps have teiid as their source so, not now.

Also, in this case I need to just read a whole remote table, I guess I could not have advantages materializing it, am I correct?

So, you are only using Teiid for replication? then yes, materialization is no use to you. When you use Teiid as source, instead of mysql as source then materialization is useful, especially if you want keep up with all the the updates to the mysql table.

In one case, a source dbtestA.table of mine has many (half million) rows, and this workflow isn't a good idea, plus, only a few (fecent) records could change each day, but there are both a pk and a "last modifiied" timestamp so, after the first initial replica, each day I can probably just delete and insert again just changed rows...

yes, You can add the where clause to "select" just to pull in the new/updated rows to go into the mysql table.

If you think I could benefit from some particular teiid feature in this respect, please, let me know, I could easily miss some important info...

Teiid is not an ETL tool, but we have a similar request at [TEIID-3746] Add ETL functionality to Teiid - JBoss Issue Tracker

>>but, eg, in squirrel, on a standard connection, opened without the autoCommitTxn=OFF parameter,

>>if I use that statement (which runs fine) and then issue my replciating SQL, it fails as it did before.

You need to add "autoCommitTxn=OFF" on URL connection string, not without

[m.ardito]

Ramesh Reddy wrote:

So, you are only using Teiid for replication? then yes, materialization is no use to you. When you use Teiid as source, instead of mysql as source then materialization is useful, especially if you want keep up with all the the updates to the mysql table.

No, I am mostly planning to use Teiid as multisource aggregator, but for this one particular job, I need the replication really asap, and since classic ETL tools could be overkill, and manual replication is more complicated, I just wished to explore if Teiid could be used instead and how.

yes, You can add the where clause to "select" just to pull in the new/updated rows to go into the mysql table.

yes, this could be sufficient by now.In the next months Teiid will be hopefully used only as a federation/integration tool, so this replication will not be necessary at all.

Teiid is not an ETL tool, but we have a similar request at [TEIID-3746] Add ETL functionality to Teiid - JBoss Issue Tracker

I don't need Teiid to become an ETL tool, it is just wonderful as it is now... it is just so versatile and full of features that I just try to use it for everything .

You need to add "autoCommitTxn=OFF" on URL connection string, not without

Ok, that works, as I said before, but I thought that was a "connection level" method and that there was also a "request" method that could be set in for each SQL insert/update statement, as needed. No problem.

Thanks for your support

[m.ardito]

>> You need to add "autoCommitTxn=OFF" on URL connection string, not without

Sorry to still bother... can I use in any way that connection property (or an equivalent one) also through ODBC? The doc reference talks about jdbc only...

now I remember, this is why I was thinking about a rewuest-level switch...

Marco

[rareddy]

You can use SET call with ODBC like

SET autoCommitTxn OFF