Configuring Data Sources | Oracle Application Server 10g: J2EE Deployment and Administration

By default, OC4J data sources are configured within data-sources.xml inside in the OC4J_HOME/j2ee/home/config directory . However, the name and location of this file can be changed within the data-sources element of the application.xml configuration file.

Initially, data-sources.xml contains one sample data source definition that uses DriverManagerDataSource to connect to an Oracle database. If your application only accesses a single Oracle database, then you can just customize the JNDI locations, username, password, and URL in this sample definition. If you need to access a non-Oracle database, you'll also need to change the connection-driver property. If you need support for transactions across multiple databases or non-Oracle extensions, then you'll need to specify a different data-source implementation in the class property.

The following example shows possible configurations for a variety of drivers and data sources:

 <?xml version="1.0" standalone='yes'?> <!DOCTYPE data-sources PUBLIC "Orion data-sources"                    "http://xmlns.oracle.com/ias/dtds/data-sources.dtd"> <data-sources>     <!-- Oracle DataSource that does NOT support two-phase commit -->     <data-source         class="com.evermind.sql.DriverManagerDataSource"         name="ProductCatalogDataSource"         location="jdbc/ProductCatalogDS"         xa-location="jdbc/xa/ProductCatalogXADS"         ejb-location="jdbc/ProductCatalogDS"         connection-driver="oracle.jdbc.driver.OracleDriver"         username="scott"         password="tiger"         url="jdbc:oracle:thin:@acmeserver:1521:productdb"         min-connections="3"         max-connections="10"         inactivity-timeout="30"         wait-timeout="120"     />     <!-- Oracle DataSource that DOES support two-phase commit -->     <data-source         class="  com.evermind.sql.OrionCMTDataSource  "         name="DistributedProductCatalogDataSource"         location="jdbc/DistributedProductCatalogDS"         connection-driver="oracle.jdbc.driver.OracleDriver"         username="scott"         password="tiger"         url="jdbc:oracle:thin:@betaserver:5521:product"         min-connections="10"         max-connections="15"     >         <property name="cacheScheme" value="FIXED_WAIT_SCHEME"/>         <property name="dblink" value="productlink"/>     </data-source>     <!-- PostgreSQL DataSource -->     <data-source         class="  com.evermind.sql.  DriverManagerDataSource "         name="PGInventoryDataSource"         location="jdbc/PGInventoryDS"         xa-location="jdbc/xa/PGInventoryXADS"         ejb-location="jdbc/PGInventoryDS"         connection-driver="org.postgresql.Driver"         username="admin"         password="letmein"         url="jdbc:postgresql://gammaserver.acme.com/inventory"         inactivity-timeout="45"     />     <!-- MS-SQLServer DataSource -->     <data-source         class="  com.evermind.sql.  DriverManagerDataSource "         name="MSInventoryDataSource"         location="jdbc/MSInventoryDS"         xa-location="jdbc/xa/MSInventoryXADS"         ejb-location="jdbc/MSInventoryDS"         connection-driver=" com.oracle.ias.jdbc.sqlserver.SQLServerDriver "         username="admin"         password="letmein"         url="jdbc:sqlserver//someserver:8000;User=admin;Password=letmein"         inactivity-timeout="45"     />     <!-- IBM DB/2 DataSource -->     <data-source         class="  com.evermind.sql.  DriverManagerDataSource"         name="DB2InventoryDataSource"         location="jdbc/DB2InventoryDS"         xa-location="jdbc/xa/DB2InventoryXADS"         ejb-location="jdbc/DB2InventoryDS"         connection-driver=" com.oracle.ias.jdbc.db2.DB2Driver"         username="admin"         password="letmein"         url=" jdbc:db2://myserver:8000;LocationName=jdbc;CollectionId=default;"         inactivity-timeout="45"     />     <!-- Sybase DataSource -->     <data-source         class="  com.evermind.sql.  DriverManagerDataSource"         name="SybaseInventoryDataSource"         location="jdbc/SybaseInventoryDS"         xa-location="jdbc/xa/SybaseInventoryXADS"         ejb-location="jdbc/SybaseInventoryDS"         connection-driver=" com.oracle.ias.jdbc.sybase.SybaseDriver "         username="admin"         password="letmein"         url="jdbc:sybase//myserver:9000;User=admin;Password=letmein"         inactivity-timeout="45"     />     <!-- Oracle DataSource that uses native OCI driver and local tns-name-->     <data-source         class="  com.evermind.sql.  DriverManagerDataSource "         name="OCIProductCatalogDataSource"         location="jdbc/OCIProductCatalogDS"         xa-location="jdbc/xa/OCIProductCatalogXADS"         ejb-location="jdbc/OCIProductCatalogDS"         connection-driver="oracle.jdbc.driver.OracleDriver"         username="scott"         password="tiger"         url="jdbc:oracle:oci8:@product"         inactivity-timeout="30"     /> </data-sources>

Customizing Data-Source Attributes

As seen in the preceding example, a number of different attributes may be used to configure data sources. Some are required, while others have default values if not specified. Table 5-3 lists some of the common data-source properties that you will need to set, and then we move on to discuss some of the properties that determine how the data source behaves in operation.

Table 5-3: Data-Source Properties
Attribute	Description
class	Fully qualified name of the java.sql.DataSource implementation class. As described earlier in this chapter, this should be com.evermind.sql.OrionCMTDataSource when using multiple Oracle databases with two-phase commit or com.evermind.sql.DriverManagerDataSource in most other cases (third-party data sources can also be used if non-Oracle extensions are needed, but don't work with OC4J's container-managed transactions).
location	JNDI name used to look up data sources that don't use the DriverManagerDataSource implementation (Note: DriverManagerDataSource data sources still have to specify a value here, but it isn't used. See ejb-location instead.)
xa-location	DriverManagerDataSource data sources must specify some value for this property, but it's never used to access them. See ejb-location instead.
ejb-location	JNDI name used to look up data sources that use the DriverManagerDataSource implementation.
name	Unique name for this data source. Defaults to the value given for ejb-location .
connection-driver	Fully qualified name of the JDBC driver class. This will usually be oracle.jdbc.driver.OracleDriver for Oracle databases.
username	Name of the schema to which this data source should connect (for example, "scott").
password	Password that should be used to connect (for example, "tiger"). Alternately, this can be "->"followed by the name of the user whose password should be used, as long as that user is available through the configured UserManager . For example, if you have configured a user in OC4J called "scott" with password "tiger," then instead of putting "tiger" here as the password, you can put "->scott" and OC4J will look up and use the password of the user "scott."
URL	JDBC URL for database connections. If using the pure-Java Oracle driver, this will resemble jdbc:oracle:thin:@myserver:1521:mysid . If using the OCI driver, this will resemble jdbc:oracle:oci8:@:mytnsname . For non-Oracle drivers, consult the documentation or the examples earlier in this chapter.

The following sections discuss those properties that determine exactly how the data source behaves, such as how long it will cache an idle connection before closing it.

Inactivity-Timeout

This is the number of seconds to cache a connection that's no longer in use before closing it. Defaults to 60 seconds. This is a bit low for most applications and is typically raised to at least 120 seconds or whatever length of idle time you normally expect between traffic surges. At low values, OC4J will spend a lot of time giving up and reacquiring connections between requests . At high values, your connection pool will tend to stay full unless there is a long period of inactivity. To tune this parameter, observe your connection pool size during periods of normal and high traffic. If it's constantly oscillating between min-connections and some higher value, then you may want to raise this value. Of course, if you don't need to conserve connections during low traffic, then you're probably better off just raising min-connections .

Min-Connections

This is the minimum number of connections kept open for this data source (though not opened until the data source is first accessed). This defaults to 0, but that's usually much too low for a production application. It's important to stay within a range that your database can handle, but try to set it to at least as many connections as are used during normal load. If resources aren't scarce , consider setting it to the number of connections in use during peak load, since you probably don't want the expense of opening and closing connections during your heaviest periods (raising the inactivity-timeout can also help with this problem by preventing excess connections from being closed too rapidly ). Once your application is stable, be sure to load-test your application with several different values to see how it responds. Though most applications perform better with larger connection pools, you may find that the reverse is true if your application relies on very complex queries that overwhelm the database when executed in parallel.

Max-Connections

This is the maximum number of pooled connections allowed for this data source. By default, there's no maximum, but you should almost always set one. Under heavy load, a shared data-base often becomes the bottleneck and can quickly become overwhelmed or run out of connections, thereby causing unexpected errors and potentially starving other applications. It's usually a good idea to coordinate max-connections values among all applications using the database. You should ensure that the database is properly configured to handle the total. Typically, you can use load tests to determine the value at which your database becomes overwhelmed and performance begins to suffer. As noted under min-connections , some applications actually perform better when connections are limited.

Wait-Timeout

This is the number of seconds that the data source will wait for a free connection before timing out. The default value is 60 seconds. In general, if you're seeing timeouts on connection requests, you should try raising max-connections first However, if your database can't handle more connections, then you can raise this wait-timeout value to get a bit of extra breathing room until you can tune the application or add more hardware. If you expect these kinds of delays during normal usage, it may be worthwhile to move to an asynchronous architecture and throttle the number of simultaneous requests at the application or JMS level.

Max-Connect-Attempts

This defines the maximum number of unsuccessful attempts to open a connection before returning an error. The default value is 3. This is useful if you expect connection attempts to occasionally fail (for example, due to spotty network connectivity or overloaded database traffic). Of course, where possible it's usually better to address the source of the instability rather than increasing the time the application server spends making unsuccessful requests.

Customizing Data Source Properties

A data source definition can also include <property> elements, whose values are passed into the data source when it's initialized . In particular, the OrionCMTDataSource accepts two properties: cacheScheme and dblink .

This cacheScheme property controls what happens when the maximum number of connections is exceeded. For example, to cause requests to block until a connection becomes available, you would add a child element to <data-source> like this:

 <data-source ...>       <property name="cacheScheme" value="FIXED_WAIT_SCHEME"/> </data-source>

Possible values for this property are

FIXED_WAIT_SCHEME . Requests beyond the maximum will block until a connection becomes available.
FIXED_RETURN_NULL_SCHEME . Requests beyond the maximum will return null (usually causing an error).
DYNAMIC_SCHEME . Requests beyond the maximum will cause extra connections to be opened, but each will be closed and freed immediately when it's no longer in use.

Caution

The cacheScheme property defaults to the DYNAMIC_SCHEME value, which means that the max-connections you set isn't a hard limit. If you need to cap the number of possible connections, be sure to change this to FIXED_WAIT_SCHEME or FIXED_RETURN_NULL_SCHEME .

The dblink property specifies the name of the database link to be used for this database by the commit coordinator during two phase commits. Examples are given later in this chapter in the "Configuring Two-Phase Commit" section.

Deploying Data Sources

Once the data source has been configured in data-sources.xml , OC4J must be restarted before the changes will take effect. At this point, the new data source will be available through JNDI to all deployed applications. See Chapter 9 for more information about restricting data sources and other resources to particular applications.