10 Replies Latest reply on Jul 22, 2016 3:13 AM by Prakash Jape

    Need to setup XA datasource

    Kulbhushan Chaskar Expert

      Hi ,

       

      I need to set-up XA-datasource using  teiid adminapi. Currently we are creating non-xa datasource.

      Can we create using admin api ? What all properties specifically we need to set for creating XA-Datasource using admin api ?

       

      Thanks

        • 2. Re: Need to setup XA datasource
          Kulbhushan Chaskar Expert

          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
            Ramesh Reddy Master

            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

            • 5. Re: Need to setup XA datasource
              Prakash Jape Newbie

              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
                Ramesh Reddy Master

                What have tried? Can you post the code?

                • 7. Re: Need to setup XA datasource
                  Prakash Jape Newbie

                  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
                    Prakash Jape Newbie

                    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

                      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: Invalid Oracle URL specified: OracleDataSource.makeURL

                      at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)

                      at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)

                      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)

                      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)

                      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)

                      at oracle.jdbc.pool.OracleDataSource.makeURL(OracleDataSource.java:1234)

                      at oracle.jdbc.pool.OracleDataSource.getURL(OracleDataSource.java:984)

                      at oracle.jdbc.xa.client.OracleXADataSource.getPooledConnection(OracleXADataSource.java:208)

                      at oracle.jdbc.xa.client.OracleXADataSource.getXAConnection(OracleXADataSource.java:159)

                      at oracle.jdbc.xa.client.OracleXADataSource.getXAConnection(OracleXADataSource.java:130)

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

                      ... 12 more

                    • 9. Re: Need to setup XA datasource
                      Ramesh Reddy Master

                      > For SqlServer -

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


                      You need to add "Instance" property as following. Any custom properties needed to be added this way.

                      properties.setProperty("connection-properties", "Instance=XXXX");


                      "connection-properties" takes the comma separated property list, so you can supply more than one.


                      >For Oracle -

                      >1) DatabaseName, PortNumber, ServerName are not in example xa ds xml file but admin api code

                      We showed one way to create in our example that does not mean that is only way to create it


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

                      Especially to support Oracle, you can choose to supply either "connection-url" OR DatabaseName, PortNumber, ServerName properties.  if all the properties are present, then "connection-url" is given preference. Property name is "connection-url" NOT "URL" as you defined in your code. BTW, the exception with TEIID70054 does not match the code you shown, it says DatabaseName is null or empty, you showed value, either provide it, or omit it, do not provide empty value.

                       

                      Both the exceptions you shown in next message shown are due to either not able to reach the Oracle, and providing a wrong property. I advise you double checking before posting



                      • 10. Re: Need to setup XA datasource
                        Prakash Jape Newbie

                        Both Datasources are work when I set  connection-properties parameter through java.

                         

                        1. For Sql Server

                        properties.setProperty("connection-properties", "Instance=XXXX");


                        2. For Oracle 

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

                         

                        Any way, Thanks for you help.