Need to setup XA datasource| JBoss.org Content Archive (Read Only)

1. Re: Need to setup XA datasource

rareddy Jun 28, 2016 11:20 AM (in response to kulbhushanc)

see Create XA datasource with the help of Teiid Admin Api

2. Re: Need to setup XA datasource

kulbhushanc Jun 29, 2016 3:41 AM (in response to rareddy)

In provided link, specify how to create XA datasource with CLI script,

I need to create/Setup XA Datasource with the Admin API (org.teiid.adminapi.Admin) I am using Teiid 8.13 version.

Below is the way, I used to create NON XA Datasource.

Properties properties = new Properties();

properties.setProperty("jndi-name", teiidconn.getConnName());

properties.setProperty("connection-url", teiidconn.getConnURL());

properties.setProperty("user-name", teiidconn.getConnUsrNm());

properties.setProperty("password", teiidconn.getConnUsrPw());

properties.setProperty("max-pool-size", "20");

properties.setProperty("enabled", "true");

properties.setProperty("use-java-context", "true");

and Then calling below statement

this.admin.createDataSource(deploymentName, driverName, properties);

Similarly by using Admin API, I need to setup XA datasource.

Please guide me

3. Re: Need to setup XA datasource

rareddy Jun 29, 2016 10:47 AM (in response to kulbhushanc)

The CLI needed is like from WildFly documents

# Add an XA datasource
xa-data-source add \
    --name=ApplicationXADS \
    --driver-name=postgresql \
    --jndi-name=java:jboss/jdbc/ApplicationXADS \
    --user-name=demouser \
    --password=password \
    --recovery-username= demouser \
    --recovery-password = password \
    --use-ccm=false \
    --max-pool-size=25 \
    --blocking-timeout-wait-millis=5000 \
    --new-connection-sql="set datestyle = ISO, European;"
/subsystem=datasources/xa-data-source=ApplicationXADS/xa-datasource-properties=ServerName:add(value=localhost)
/subsystem=datasources/xa-data-source=ApplicationXADS/xa-datasource-properties=PortNumber:add(value=5432)
/subsystem=datasources/xa-data-source=ApplicationXADS/xa-datasource-properties=DatabaseName:add(value=DemoDB

However, it looks like Teiid Admin API does NOT have capability to create one. I was under assumption that this is already done, I apologize for that. Now you have two options

1) Create your own java code based on above CLI

2) Create JIRA, I will add to the Teiid Admin API

4. Re: Need to setup XA datasource

praksah Jun 30, 2016 2:18 AM (in response to rareddy)

Ramesh

I have created a JIRA for same as "[TEIID-4311] Teiid Admin Api not support to create XA datasource on Jboss server. - JBoss Issue Tracker"

Thanks

5. Re: Need to setup XA datasource

praksah Jul 21, 2016 4:06 AM (in response to praksah)

With the teiid 9.0.2, Teiid Admin API starts supporting to create XA datasource. but when tried to create XA datasource for MySql, SqlServer,Oracle;

Only MySql data source able to create through admin api and able to test successfully.

For SqlServer and Oracle below xa-datasource-property not able to set through admin api code , as result Data source able to create but test connection failed.

SQl Server - "Instance"

Oracle - "URL"

Please guide me;

6. Re: Need to setup XA datasource

rareddy Jul 21, 2016 7:55 AM (in response to praksah)

What have tried? Can you post the code?

7. Re: Need to setup XA datasource

praksah Jul 21, 2016 10:50 AM (in response to rareddy)

I have java file which has below methods

NOTE - For property I have referrred xml file form "teiid-9.0.2-wildfly-server\teiid-9.0.2\docs\teiid\datasources\******\******-xa-ds.xml"

Java code as below -

public String createMySqlXADataSource() throws AdminApiClientException {
  Properties properties = new Properties();
  properties.setProperty("jndi-name", "java:/mySqlXADS");
  properties.setProperty("user-name", "XXXX");
  properties.setProperty("password", "XXXXX");

  properties.setProperty("max-pool-size", "20");
  properties.setProperty("min-pool-size", "10");
  properties.setProperty("prefill", "true");
  properties.setProperty("use-java-context", "true");

  properties.setProperty("PortNumber","3306");
  properties.setProperty("DatabaseName","XXXXX");
  properties.setProperty("ServerName","XXXX");

  properties.setProperty("prefill","false");
  properties.setProperty("use-strict-min","false");
  properties.setProperty("flush-strategy","FailingConnectionOnly");
  properties.setProperty("is-same-rm-override","false");
  properties.setProperty("no-tx-separate-pools","false");

  String deploymentName = "mySqlXADS";
  String templateName = "mysql"; // driver name

  this.client.createDataSource(deploymentName, templateName+"-xa", properties);

  return deploymentName;


}

public String createSqlServerXADataSource() throws AdminApiClientException {
  Properties properties = new Properties();
  properties.setProperty("jndi-name", "java:/sqlSrvrXADS");

  properties.setProperty("user-name", "XXXX");
  properties.setProperty("password", "XXXX");

  properties.setProperty("max-pool-size", "20");
  properties.setProperty("min-pool-size", "10");
  properties.setProperty("prefill", "false");
  properties.setProperty("use-java-context", "true");

  properties.setProperty("PortNumber","1433");
  properties.setProperty("DatabaseName","XXXX");
  properties.setProperty("ServerName","XXXX");
  properties.setProperty("Instance","XXXX");

  properties.setProperty("prefill","false");
  properties.setProperty("use-strict-min","false");
  properties.setProperty("flush-strategy","FailingConnectionOnly");
  properties.setProperty("is-same-rm-override","true");
  properties.setProperty("no-tx-separate-pools","true");

  String deploymentName = "sqlSrvrXADS";
  String templateName = "sqlserver"; // driver name
  this.client.createDataSource(deploymentName, templateName+"-xa", properties);

  return deploymentName;

}

public String createOracleXADataSource() throws AdminApiClientException {
  Properties properties = new Properties();
  properties.setProperty("jndi-name", "java:/oracleXADS");

  properties.setProperty("user-name", "XXXX");
  properties.setProperty("password", "XXXX");

  properties.setProperty("max-pool-size", "20");
  properties.setProperty("min-pool-size", "10");
  properties.setProperty("prefill", "false");
  properties.setProperty("use-java-context", "true");

  properties.setProperty("URL","jdbc:oracle:thin:@XXXXX:1521:XX");

  properties.setProperty("DatabaseName","XXXX");
  properties.setProperty("ServerName","XXXX");
  properties.setProperty("PortNumber","1521");

  properties.setProperty("prefill","false");
  properties.setProperty("use-strict-min","false");
  properties.setProperty("flush-strategy","FailingConnectionOnly");
  properties.setProperty("is-same-rm-override","true");
  properties.setProperty("no-tx-separate-pools","true");

  String deploymentName = "oracleXADS";
  String templateName = "oracle"; // driver name
  this.client.createDataSource(deploymentName, templateName+"-xa", properties);

  return deploymentName;
}

After running these methods below XA datasource are created on jboss

<xa-datasource jndi-name="java:/mySqlXADS" pool-name="mySqlXADS" enabled="true">
    <xa-datasource-property name="DatabaseName">
        XXXXXX
    </xa-datasource-property>
    <xa-datasource-property name="PortNumber">
        3306
    </xa-datasource-property>
    <xa-datasource-property name="ServerName">
        XXXX
    </xa-datasource-property>
    <driver>mysql</driver>
    <xa-pool>
        <min-pool-size>10</min-pool-size>
        <max-pool-size>20</max-pool-size>
        <flush-strategy>FailingConnectionOnly</flush-strategy>
    </xa-pool>
    <security>
        <user-name>XXXXX</user-name>
        <password>XXXXX</password>
    </security>
</xa-datasource>

<xa-datasource jndi-name="java:/sqlSrvrXADS" pool-name="sqlSrvrXADS" enabled="true">
    <xa-datasource-property name="DatabaseName">
        XXXXX
    </xa-datasource-property>
    <xa-datasource-property name="PortNumber">
        1433
    </xa-datasource-property>
    <xa-datasource-property name="ServerName">
        XXXXX
    </xa-datasource-property>
    <driver>sqlserver</driver>
    <xa-pool>
        <min-pool-size>10</min-pool-size>
        <max-pool-size>20</max-pool-size>
        <flush-strategy>FailingConnectionOnly</flush-strategy>
    </xa-pool>
    <security>
        <user-name>XXXXX</user-name>
        <password>XXXXXX</password>
    </security>
</xa-datasource>

<xa-datasource jndi-name="java:/oracleXADS" pool-name="oracleXADS" enabled="true">
    <xa-datasource-property name="DatabaseName">
        XX
    </xa-datasource-property>
    <xa-datasource-property name="PortNumber">
        1521
    </xa-datasource-property>
    <xa-datasource-property name="ServerName">
        XXXXXX
    </xa-datasource-property>
    <driver>oracle</driver>
    <xa-pool>
        <min-pool-size>10</min-pool-size>
        <max-pool-size>20</max-pool-size>
        <flush-strategy>FailingConnectionOnly</flush-strategy>
    </xa-pool>
    <security>
        <user-name>XXXX</user-name>
        <password>XXXXX</password>
    </security>
</xa-datasource>

Here I notice that; some properties are set in Java code but not reflected on Jboss. e.g. is-same-rm-override etc

Out of these three data sources, I able to do a test connection for MySql DS only.

For SqlServer -

1. Instance property is mentioned in jtds/sqlserver-xa-ds.xml (I am using JTDS).

2. If I manually add instance property in above xa datasource in standalone-teiid.xml file and restart server it work

For Oracle -

1. DatabaseName, PortNumber, ServerName are not in example xa ds xml file but admin api code need database name else it through error as "TEIID70054 Invalid empty or missing value for property DatabaseName."

2. URL property is mentioned in "oracle-xa-ds.xml" file

3. If manually I removed DatabaseName, PortNumber, ServerName and and URL property , it work for me.

Let me know in case of you need more info

8. Re: Need to setup XA datasource

praksah Jul 21, 2016 10:27 AM (in response to praksah)

One more thing When I make test connection below errors are logged in JBoss server log

2016-07-21 19:54:25,652 ERROR [org.jboss.jca.core.tx.jbossts.XAResourceRecoveryImpl] (Periodic Recovery) IJ000906: Error during crash recovery: java:/sqlSrvrXADS (IJ031084: Unable to create connection): javax.resource.ResourceException: IJ031084: Unable to create connection

at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.getXAManagedConnection(XAManagedConnectionFactory.java:496)

at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory$1.run(XAManagedConnectionFactory.java:392)

at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory$1.run(XAManagedConnectionFactory.java:389)

at java.security.AccessController.doPrivileged(Native Method)

at javax.security.auth.Subject.doAs(Subject.java:422)

at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.createManagedConnection(XAManagedConnectionFactory.java:388)

at org.jboss.jca.core.tx.jbossts.XAResourceRecoveryImpl.open(XAResourceRecoveryImpl.java:355)

at org.jboss.jca.core.tx.jbossts.XAResourceRecoveryImpl.getXAResources(XAResourceRecoveryImpl.java:193)

at com.arjuna.ats.internal.jbossatx.jta.XAResourceRecoveryHelperWrapper.getXAResources(XAResourceRecoveryHelperWrapper.java:51)

at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.resourceInitiatedRecoveryForRecoveryHelpers(XARecoveryModule.java:510)

at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkFirstPass(XARecoveryModule.java:176)

at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.doWorkInternal(PeriodicRecovery.java:747)

at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.run(PeriodicRecovery.java:375)

Caused by: java.sql.SQLException: Network error IOException: Connection refused: connect

at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:436)

at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)

at net.sourceforge.jtds.jdbcx.JtdsDataSource.getConnection(JtdsDataSource.java:186)

at net.sourceforge.jtds.jdbcx.JtdsDataSource.getXAConnection(JtdsDataSource.java:116)

at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.getXAManagedConnection(XAManagedConnectionFactory.java:479)

... 12 more

Caused by: java.net.ConnectException: Connection refused: connect

at java.net.TwoStacksPlainSocketImpl.socketConnect(Native Method)

at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:345)

at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)

at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)

at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)

at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)

at java.net.Socket.connect(Socket.java:589)

at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSocket.java:288)

at net.sourceforge.jtds.jdbc.SharedSocket.<init>(SharedSocket.java:251)

at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:331)

... 16 more

2016-07-21 19:54:25,676 ERROR [org.jboss.jca.core.tx.jbossts.XAResourceRecoveryImpl] (Periodic Recovery) IJ000906: Error during crash recovery: java:/oracleXADS (IJ031084: Unable to create connection): javax.resource.ResourceException: IJ031084: Unable to create connection