Configuring Multiple Data Sources

Configure multiple data sources so that you can use the EMSE APIs to access and manipulate customer databases.

To configure multiple data sources

  1. Open the jbossjta-properties.xmlfile.

    This file resides in the <Biz Server>\av.biz\conf\ directory. Replace <Biz Server> with the actual directory of the Biz Server.

  2. Locate the 
    <properties depends="arjuna" name="jta"> element
    and add the codes highlighted in red to the context as below if it is not there.
    <properties depends="arjuna" name="jta">
   <!--
     Support subtransactions in the JTA layer?
     Default is NO.
   -->
   <property name="com.arjuna.ats.jta.supportSubtransactions" value="NO"/>
   <property name="com.arjuna.ats.jta.jtaTMImplementation" 
     value="com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionManagerImple"/>
   <!--
      com.arjuna.ats.internal.jta.transaction.jts.TransactionManagerImple
   -->
   <property name="com.arjuna.ats.jta.jtaUTImplementation" 
     value="com.arjuna.ats.internal.jta.transaction.arjunacore.UserTransactionImple"/>
   <!--
      com.arjuna.ats.internal.jta.transaction.jts.UserTransactionImple
   -->
   <property name="com.arjuna.ats.jta.allowMultipleLastResources" value="true" />
</properties>

  3. Add data sources.

    Note:

    You can only use non-XA data source in this step.

    • If the customer database is Oracle

      1. Open the oracle-ds.xml file.

        This file resides in the <Biz Server>\av.biz\deploy\ directory. Replace <Biz Server> with the actual directory of the Biz Server.

      2. Add the following codes under the <datasources> element.

        <local-tx-datasource>
   <jndi-name>${customer.db.jndiname}</jndi-name>
    <connection-url>jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=${customer.db.host})(PORT=${customer.db.port})))(CONNECT_DATA=(SERVICE_NAME=${customer.db.servicename})(SERVER=DEDICATED)))</connection-url>
    <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
    <user-name>${customer.db.username}</user-name>
    <password>${customer.db.password}</password>
    <min-pool-size>${customer.db.minsize}</min-pool-size> 
    <max-pool-size>${customer.db.maxsize}</max-pool-size> 
    <blocking-timeout-millis>${customer.db.blocking.timeout.millis}</blocking-timeout-millis> 
    <idle-timeout-minutes>${customer.db.idle.timeout.minutes}</idle-timeout-minutes> 
    <prepared-statement-cache-size>${customer.db.maxpooledstatements}</prepared-statement-cache-size>
<!--  sql to call on an existing pooled connection when it is obtained from pool -->
<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
    <!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool -->
    <!--valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name-->
    <!-- Checks the Oracle error codes and messages for fatal errors -->
    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
        <!-- sql to call when you create a connection
        <new-connection-sql>some arbitrary sql</new-connection-sql>
        -->
        <!-- sql to call on an existing pooled connection when you obtain it from the pool with OracleValidConnectionChecker
        <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
        -->
  </local-tx-datasource>

    • If the customer database is MSSQL

      1. Open the AVVembuMSSQL-ds.xmlfile.

        This file resides in the <Biz Server>\av.biz\deploy\ directory. Replace <Biz Server> with the actual directory of the Biz Server.

      2. Do the same change as that for Oracle.

  4. Open the ServerConfig.properties file.

    This file resides in the <Biz Server>\av.biz\conf\av\ directory. Replace <Biz Server> with the actual directory of the Biz Sever.

  5. Add customer database information (highlighted in red) to the following context of the file.

    customer.db.jndiname=CUSTOMER
customer.db.description=customer db
customer.db.host=10.50.70.200
customer.db.port=1521
customer.db.servicename=customerDB
customer.db.sid=customerDB
customer.db.username=Johnson
customer.db.password=Johnson
# db access config (oracle-ds.xml)
av.db.jndiname=AA
av.db.description=AA Server Database
av.db.host=10.50.130.39
av.db.port=1521
av.db.servicename=dbs39
av.db.sid=dbs54
av.db.username=auprod1
av.db.password=auprod1
    Note:

    The codes shown above are only for reference. The variables and their values can be different in practice. Refer to the following table to better understand the variables.
    customer.db.jndiname Enter the JNDI name of the customer database. JNDI here represents Java Naming and Directory Interface. For more information about JNDI, see its wikipedia entry.
    customer.db.description Enter the description of the customer database.
    customer.db.host Enter the host IP address of the customer database.
    customer.db.port Enter the port number of the customer database.
    customer.db.servicename Enter the service name of the customer database.
    customer.db.sid Enter the Security Identifier (SID) of the customer database.
    customer.db.username Enter the user name of the customer database.
    customer.db.password Enter the password to access the customer database.

  6. Open the file system.properties.

    This file resides in the <Biz Server>\av.biz\conf\av\ directory. Replace <Biz Server> with the actual directory of the Biz Sever.

  7. Add customer data source (highlighted in red) to the context shown below.

    #================= datasource configuration =============
jdbc.default.datasource=java:/AA
jdbc.customer.datasource=java:/CUSTOMER
jdbc.system.datasource=java:/AA
jdbc.aamain.datasource=java:/AA
jdbc.calendar.datasource=java:/AA
jdbc.emse.datasource=java:/AA
jdbc.structure.datasource=java:/AA
jdbc.inspection.datasource=java:/AA
jdbc.finance.datasource=java:/AA

  8. Pass corresponding parameters to EMSE APIs so they can access and manipulate the third-party database.

    For example, if you want to use SQL statements to insert the name and age of a person, refer to the following script:

    var parmArray=new Array();
parmArray[0]=”Thomas”;
paramArray[1]=18;
var sql=”Insert into person(name, age) values(?,?)”;
aa.util.update(“customer”,sql, parmArray)
    Note:

    The parameter “customer” highlighted with double-underline in this step should be the same as the object name “customer” in the script highlighted with double-underline in .