Connection Pooling and Data Sources

This page contains information and reference about the following topics/questions/how to's

  1. How to setup a JNDI data-source (equip/setup a Tomcat server with JNDI data-sources)
  2. How to encrypt data-base password file(s) for consumption by Tomcat and Unit-tests
  3. How to configure Automatic Data-source Environment Detection
  4. How to name your data-sources (Data Source Naming Conventions)
  5. How to configure a data-source for unit-testing locally
  6. Who is the data-source owner
  7. What is shared and not shared across different groups/servers

Architecture Overview

Overall Data-source Architecture

Data Sources

Connection Pooling

Connection Pooling will be done by the server using Global JNDI data-sources

Advantages of this approach:

  • Uses Tomcat JDBC connection pooling
  • Logging: Server will log and close abandoned connections
  • Helps identify application design problems
  • Connections will not max out per data-source since they are managed globaly
  • Advanced (Optional); JTA (Java Transactions API): JTA requires the use of JNDI data-sources, if any applicaitons needs to implement this API

Database Authentication (Password Files)

Password files need to be encrypted for use by tomcat. please see section: How to encrypt data-base password file(s)

Best Practices & How To's

How to setup a JNDI data-source

  1. Server Side
    • server.xml
      • JNDI Data-source Naming Convention:

        • the data-source name has to be in the following format for the factory to pick up the correct password file:

           jdbc/${schema_name}.{database_username}
          

          as an example look at finlinkr user: jdbc/fnlnk_db.finlinkr

    • Defining a new data-source in server.xml

      • See the templates section, example:

        <GlobalNamingResources>   
        <!-- Editable user database that can also be used by
                 UserDatabaseRealm to authenticate users
            -->
            <Resource name="UserDatabase" auth="Container"
                      type="org.apache.catalina.UserDatabase"
                      description="User database that can be updated and saved"
                      factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
                      pathname="conf/tomcat-users.xml" />
        
            <!-- START::FINANCIAL::GROUP::DATA-SOURCES -->
                <Resource
                    name="jdbc/fnlnk_db.finlinkr"
                    description="OWNER=FINANCIAL::AUTH=SELECT"
                    auth="Container"
                    type="javax.sql.DataSource"
                    username="finlinkr"
                    password="default"
                    driverClassName="com.ibm.db2.jcc.DB2Driver"
                    factory="edu.ucsd.act.org.apache.tomcat.dbcp.dbcp.UCSDBasicDataSourceFactory"
                    url="jdbc:db2://uri:port/fnlnk_db"
                    maxActive="8"
                    maxIdle="5"
                    maxWait="-1"
                    removeAbandoned="true"
                    removeAbandonedTimeout="60"
                    logAbandoned="true"
                    readonly="true"
                    validationQuery="select indx from coa_db.indx fetch first 1 rows only"
                    validationQueryTimeout="20"
                    />
        
                <Resource
                    name="jdbc/fnlnk_db.finlink"
                    description="OWNER=FINANCIAL::AUTH=SELECT/INSERT/UPDATE/DELETE"
                    auth="Container"
                    type="javax.sql.DataSource"
                    username="finlink"
                    password="default"
                    driverClassName="com.ibm.db2.jcc.DB2Driver"
                    factory="edu.ucsd.act.org.apache.tomcat.dbcp.dbcp.UCSDBasicDataSourceFactory"
                    url="jdbc:db2://uri:port/fnlnk_db"
                    maxActive="8"
                    maxIdle="5"
                    maxWait="-1"
                    removeAbandoned="true"
                    removeAbandonedTimeout="60"
                    logAbandoned="true"
                    readonly="false"
                    validationQuery="select indx from coa_db.indx fetch first 1 rows only"
                    validationQueryTimeout="20"
                    />
            <!-- END::FINANCIAL::GROUP::DATA-SOURCES -->    
          </GlobalNamingResources> 
        
      • Important data-srource flags (for the complete list of flags see tomcat's documentaion: JNDI Resources HOW-TO
        property name=value description
        factory factory="
        edu.ucsd.act.org.apache.tom
        cat.dbcp.dbcp.UCSDBasic
        DataSourceFactory"
        We have implemented our own factory for use by ACT.
        Make sure to set the factory to our implmenetation
        logAbandoned logAbandoned="true" make sure this is set to true, this flag helps identify applications that do not use or close connections properly.
        readonly readonly="true" set to true for readonly db users, this will increase the performance as the database knows there is no need to deal w/update/delete operations
  2. Application Side
    • web.xml, one entry per data-source
      • ...
        <resource-ref>
            <description>${WEBAPP_PERSISTENCE_UNIT}</description>
            <res-ref-name>jdbc/${DB_NAME.USERNAME}</res-ref-name>
            <res-type>javax.sql.DataSource</res-type>
            <res-auth>Container</res-auth>
        </resource-ref>
        ...
        
         
    • context.xml, one entry per data-source
      • ...
        <Context path="${webappname}" 
                      crossContext="true" 
                      override="true">
            <ResourceLink global="jdbc/${DB_NAME.USERNAME}" 
              name="jdbc/${DB_NAME.USERNAME}" 
              type="javax.sql.DataSource"/>
            ...
        </Context>
        ...
        
         
    • Required by JPA, persistence.xml, one entry per data-source
      • ...
        <persistence-unit name="${DB_NAME.USERNAME}_pu_01" 
                                     transaction-type="RESOURCE_LOCAL" >
            <description>${DB_NAME.USERNAME}_pu_01</description>
                <!-- Persistence Provider -->
                <provider>org.hibernate.ejb.HibernatePersistence</provider>
                <!-- JPA Entities -->
                <class>edu.ucsd.act.costshare.domain.Fund</class>
                <class>...</class>
                ...
            <exclude-unlisted-classes>true</exclude-unlisted-classes>
        </persistence-unit>
        ...
        
      • You Also need to setup Spring JPA/JNDI access via Spring Configuration (xml) files
        • please See the Template/Entity/Entity and Transaction Management Sections

How to encrypt data-base password file(s) for consumption by Tomcat and Unit-tests

  • 1) Setup new folders/permissions 
    • create folder: /opt2/ds_jpa_properties
    • create folder: /opt2/webapp_jndi_keys/
    • note: 
      • this folder is ONLY open in DEV, it will be locked in all other environments
      • use your own credentials and go through access provisioning process to gain access in all Environments except DEV
    • create folder: /opt2/webapp_jndi_pwds/
    • note: 
      • this folder is ONLY accessible in DEV, it will be locked in all other environments
      • use your own credentials and go through access provisioning process to gain access in all Environments except DEV
  • 2) Copy shared jar (this jar is now included in the ITAG library)
    • copy the following jar to your tomcat's lib directory
      • /opt2/tomcat/tomcat6-8120/lib/ucsd-tomcat-dbcp-ext.jar
        
  • 3) Create new key/password files using utility provided
    • create database user '${YOUR_USER_NAME}' key/password file, follow directions below to use the utility that creates key/pass pair files:
      • run the following:
        • ...
          #!/bin/sh -fx
          
          export JAVA_HOME="java"
          #environments: dev,qa,train,stage,prod
          export SERVER_ENVIRONMENT=dev
          
          #password: key/encrypted pass pair
          export DS_KEYS_LOC="/${CHANGE}/webapp_jndi_keys/"
          export DS_PWDS_LOC="/${CHANGE}/webapp_jndi_pwds/"
          
          #data-source properties for JPA and unit-testing
          export DS_PROP_LOC="/${CHANGE}/ds_jpa_properties"
          
          #include all above in one variable to be included in the JAVA_OPTS
          export ACT_JAVA_OPTS="\
          -Dglobal.var.server.env="$SERVER_ENVIRONMENT" \
          -Dglobal.var.ds.keys.path="$DS_KEYS_LOC" \
          -Dglobal.var.ds.pwds.path="$DS_PWDS_LOC" \
          -Dglobal.var.ds.prop.path="$DS_PROP_LOC" \
          "
          
          $JAVA_HOME -cp .:/${CHANGE}/ucsd-tomcat-dbcp-ext.jar $ACT_JAVA_OPTS edu.ucsd.act.datasource.jndi.digest.CryptoUtils
          ...
          
      • the program will ask you the following questions input the answers below:
        • for username: ${YOUR_USER_NAME}
        • for password:  enter ${YOUR_DB_PASSWORD}
        • for environment: ${ENVIROMENT} (DEV,QA,TRAIN,PROD)
        • for dialect: db2 (or other dialects)
        • make sure no errors were shown on the console
  • 4) Copy new shell file that includes ACT Specific Gloabal Variables
    • copy new ACT variable file and include in tomcat's /opt2/tomcat/tomcat6/bin/setenv.sh
    • i.e. follow commands below
      • i) copy act-var.sh to tomcat's /opt2/tomcat/tomcat6/bin/ directory
      • ii) add the following to /opt2/tomcat/tomcat6/bin/setenv.sh source "/opt2/tomcat/tomcat6/bin/act-var.sh"
      • iii) modify JAVA_OPTS in  /opt2/tomcat/tomcat6/bin/setenv.sh to reflect below: 
      • 
        export JAVA_OPTS="$JAVA_OPTS $ACT_JAVA_OPTS -Djava.library.path=/usr/local/apr/lib"
        
  • 5) Create new JNDI data-srouces 
    • modify server.xml and add JNDI data-sources under "GlobalNamingResources"
    • See section How to setup a JNDI data-source
  • 6) Create corresponding data-source JPA properties files (one per data-source)
    • see Templates Section: "Shared Data-source JPA/Spring Properties: One Per Data-source for All Environments"
    • put this properties file under this folder: /opt2/ds_jpa_properties/

How to configure Automatic Data-source Environment Detection

 Automatic Data-source Environment Detection is done via the system variables configured at the server level:

  • ACT's Tomcat JNDI factory requires the following system variables when the server starts up. Environments: dev,qa,train,prod
    • global system variable description
      global.var.server.env environment: dev,qa,train,prod
      global.var.ds.keys.path directory where keys for encrypted passwords are kept
      global.var.ds.pwds.path directory where encrypted passwords are kept
      global.var.ds.prop.path directory where JPA/Spring property files are kept
  • Spring Data-source Template requires the following system variables in-order to access the correct data-base user/data-source
    •    global.var.server.env  

  • Here is an example shell script to be included in tomcat's start-up scripts
    • ...
      export SERVER_ENVIRONMENT=dev
      
      #password: key/encrypted pass pair
      export DS_KEYS_LOC="/opt2/webapp_jndi_keys/"
      export DS_PWDS_LOC="/opt2/webapp_jndi_pwds/"
      
      #data-source properties for JPA and unit-testing
      export DS_PROP_LOC="/opt2/ds_jpa_properties"
      
      #include all above in one variable to be included in the JAVA_OPTS
      export ACT_JAVA_OPTS="\
      -Dglobal.var.server.env="$SERVER_ENVIRONMENT" \
      -Dglobal.var.ds.keys.path="$DS_KEYS_LOC" \
      -Dglobal.var.ds.pwds.path="$DS_PWDS_LOC" \
      -Dglobal.var.ds.prop.path="$DS_PROP_LOC" \
      "
      ...
      

How to name your data-sources (Data Source Naming Conventions)

  • JNDI Data-source Naming Convention:

    • the data-source name has to be in the following format for the factory to pick up the correct password file:

       jdbc/${schema_name}.{database_username}
      
    •  as an example look at finlinkr user: jdbc/fnlnk_db.finlinkr

How to configure a data-source for unit-testing locally

  • When unit-testing you can NOT use a JNDI data-source since your application is running outside of a web-container, so you have to access the data-base via a JDBC data-source
    • We have implemented this feature already in the provided "Spring Data-source Template
    • i.e. when unit-testing 
      1. the JNDI data-source will fail since you are running your application outside of a web-container
      2. the fall-back local JDBC data-source will take effect to access the data-base
        • For this to work, you have the enable the unit-test system variable flag when running a unit-test
          • global.var.unit_test.flag  =true
            
      3. ACT's Tomcat JNDI factory will switch to unit-testing mode and provide the credentials needed to Spring's JDBC data-source configured in the provided template

Who is the data-source owner

  • Every group owns their own data-sources in their own tomcat's server.xml
  • Keep in mind the data-base credential properties files are SHARED across team

What is shared and not shared across different groups/servers

  • Not Shared (managed per group)
    • Every group owns their own data-sources in their own tomcat's server.xml
    • Every group owns their own jpa/spring data-base properties files
  • Shared (managed globally)
    • Data-base credential properties files are SHARED across team these include:
      • generated encrypted key/password files
      • new ACT Tomcat start up variables

Governance

  • A code review of your configuration files should take place by your group to address guidelines mentioned in this page
  • Password files need to be encrypted for use by tomcat. please see section: How to encrypt data-base password file(s)
  • Use the templates provided, consult your group before making changes to the templates
    • We can not guarantee support when a template is modified
  • Follow the naming conventions provided here
  • Go through access-provisioning when trying to access/unit-test in any environment besides DEV

Unit testing 

  • Spring unit-testing and JDBC data-sources are used to give you the ability to unit-test
    • note: you can connect to any environment as long as your own username has access to the target db environment
      • i.e. data-base credentials are locked/protected in all environments except DEV
    • You can unit-test
      • on your local machine
      • on the server

Reference
Tomcat's documentaion: JNDI Resources HOW-TO