Insert data from one database into another

[guido_enexis]

Hi,

I would like to use squirrel or another sql client to insert data from one database into another database using a virtual database.

I have created a demo for testing purposes.

The demo consists of two azure sql databases (SVT and ENEXIS). These are connected to a vdb using JDBC Datasources (jdbc sql server driver - sqljdbc4.jar)

I have created a table 'TEST_DV_INSERT' in the database SVT. I want to insert data from another database ENEXIS. This fails:

Insert into B_VIRTUAL_BASE_LAYER.TEST_DV_INSERT (waarde) select CONVERT(T1.BOUWJAAR,INTEGER) from A_SRC_ENEXIS.DWH_ASSET_ESTATION T1 FETCH FIRST 1 ROWS ONLY

;

-- Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 A_SRC_SVT_DATALAKE_SQL:

-- TEIID11009 java.sql.SQLException: javax.resource.ResourceException:

-- IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@60be9ed5[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@434716ab connection handles=0 lastUse=1491904370206 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@4aea5a21 pool internal context=SemaphoreArrayListManagedConnectionPool@e20e092[pool=SVT_DATALAKE_SQL_DS] xaResource=LocalXAResourceImpl@229226d8[connectionListener=60be9ed5 connectionManager=197dcfd warned=false currentXid=null productName=Microsoft SQL Server productVersion=12.00.304 jndiName=java:/SVT_DATALAKE_SQL_DS] txSync=null]

-- SQLState:  50000

-- ErrorCode: 30504

However if i try the same thing inserting data from a table in the SVT database into the TEST_DV_INSERT in table which resides also in SVT database everything is fine:

Insert into B_VIRTUAL_BASE_LAYER.TEST_DV_INSERT (waarde)

select convert(substring(ean,1,2),integer) from "A_SRC_SVT_DATALAKE_SQL".frdet_eans WHERE EAN <> '' FETCH FIRST 1 ROWS ONLY

-- Works fine, insert data from one table in an Azure SQL database into another table in that same database.

I am quite new to the subject so i have a couple of questions....

While searching the internet it seems that i have a to use XA Datasources instead of regular datasources. Is this correct?

And if so, do i have to create XA Datasources for all source databases to the vdb?

And do i have to create an XA datasource for the vdb (for squirrel to be able to connect) as well?

I am using JBOSS Datavirtualization 6.3.0.

Thanks in advance!

Regards,

Guido

[rareddy]

>While searching the internet it seems that i have a to use XA Datasources instead of regular datasources. Is this correct?

Yes, since the transaction is spanning multiple sources, you would need XA data sources for 2PC. If you do not care for transactions, then regular data sources is fine, but for that provide "autoCommitTxn=OFF" on your JDBC URL to the Teiid, and make sure the autoCommit is set to true.

And if so, do i have to create XA Datasources for all source databases to the vdb?

All the data sources that are involved in the query.

And do i have to create an XA datasource for the vdb (for squirrel to be able to connect) as well?

No, you do not need to. If you are associating Teiid JDBC connection in a another global transaction then you would need the XA. The transaction under Teiid is atomically controlled through autoCommit = true or false.

It worked on single database case because the query was only spun around single datasource, then that is optimized using that JDBC driver's auto-commit behavior.

[guido_enexis]

Hi Ramesh,

Thank you for the answer! I will try the XA datasources. I will keep you posted.

Regards,

Guido

[guido_enexis]

Hi Ramesh,

I have managed to create two XA datasources for the two Microsoft Azure SQL Databases.

I made some progress but unfortunately i have run into another problem:

13:31:07,060 WARN  [com.arjuna.ats.jta] (Periodic Recovery) ARJUNA016027: Local XARecoveryModule.xaRecovery got XA exception XAException.XAER_RMERR: javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Reference to database and/or server name in 'master..xp_sqljdbc_xa_recover' is not supported in this version of SQL Server.

Regards,

Guido

[guido_enexis]

Hi Ramesh,

It seems i got lucky thanks to your help.

After changing the connection url to teiid (autoCommitTxn=OFF) the insert statement executed succesfully.

After executing a manual 'commit' in squirrel the new records were visible.

Do you have an explanation for this behaviour?

Thanks for your help!

Regards,

Guido

[rareddy]

Guido,

You need to turn on the XA support in SQLServer database to work with "'master..xp_sqljdbc_xa_recover' is not supported in this version of SQL Server" error. Consult your DBA and/or SQLServer documentation on how to achieve that. When autoCommitTxn=OFF, Teiid does not wrap a transaction around your "INSERT INTO SELECT..." command, thus no need for XA drivers/connections. However in the second scenario, each INSERT and SELECT queries are executed independent of each other, so in case of failure you will not have any recovery.

Ramesh..