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

    Need to setup XA datasource

    kulbhushanc

      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

        • 1. Re: Need to setup XA datasource
          rareddy
          • 2. Re: Need to setup XA datasource
            kulbhushanc

            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

              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
                • 5. Re: Need to setup XA datasource
                  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

                    What have tried? Can you post the code?

                    • 7. Re: Need to setup XA datasource
                      praksah

                      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

                        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
                          rareddy

                          > 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
                            praksah

                            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.