Data Source Configuration in Oracle Application Server 10g
Data Source Configuration in Oracle Application Server 10g
Written by Frances Zhao, Oracle Corporation
June, 2005
Oracle Application Server 10g (9.0.4) and 10g (10.1.2) Data Source Configuration
In Oracle Application Server 10g (9.0.4) and Oracle Application Server 10g (10.1.2),
there are 3 types of data sources: non-emulated, emulated, and
native. Data sources are defined and
configured via the data-sources.xml file. In this file, the root element
<data-sources> may contain 0 or more of the <data-source>
elements, regardless of the data source type that element represents. An
example looks like:
For non-emulated data sources, the <data-source> element
has the following attributes (each attribute is optional except when marked
as 'Required'):
Attribute Name
Description
Default
class
<Required>.
Names the class that implements the data source, which is com.evermind.sql.OrionCMTDataSource.
N/A
location
The JNDI logical
name for the data source object. OC4J binds the class instance into the
application JNDI name space with this name. This JNDI lookup name is used
for non-emulated data sources.
None
name
The data source name.
Must be unique within the application.
None
URL
The URL for database connections. Vendor
specific.
None
username
Default user name used when getting data
source connections.
None
password
Default password used when getting data
source connections.
None
inactivity-timeout
Time (in seconds) to cache an unused connection
before closing it.
60
connection-retry-interval
Time (in seconds) to wait before retrying a
failed connection attempt.
1
max-connections
The maximum number of open connections for
a pooled data source.
Depends on the data source type.
min-connections
The minimum number of open connections for
a pooled data source. OC4J does not open these connections until the DataSource.getConnection
method is invoked.
0
wait-timeout
The number of seconds to wait for a free connection
if the pool has reached max-connections used.
60
max-connect-attempts
The number of times to retry making a connection.
Useful when the network or environment is unstable for any reason that
makes connection attempts fail.
3
clean-available-connections-threshold
This optional attribute specifies the threshold
(in seconds) for when a cleanup of available connections will occur. For
example, if a connection is bad, the available connections are cleaned
up. If another connection is bad (that is, it throws an exception), and
if the threshold time has elapsed, then the available connections are cleaned
up again. If the threshold time has not elapsed, then the available connections
are not cleaned up again.
30
rac-enabled
This optional attribute specifies whether or
not the system is enabled for Real Application Clusters (RAC). If the
data source points to an RAC database, you should set this property to
true. This lets OC4J manage its connection pool in a way that performs
better during RAC instance failures.
false
schema
This optional attribute specifies the database-schema
associated with a data source. It is especially useful when using CMP with
additional data types or third-party databases.
None
Emulated data sources
For emulated data sources, the <data-source> element
has the following attributes (each attribute is optional except when marked
as 'Required'; and 'Same' means the attribute has the same meaning
as in the non-emulated case):
Attribute Name
Description
Default
class
<Required>.
Names the class that implements the data source, which is com.evermind.sql.DriverManagerDataSource.
N/A
location
A logical name for
the data source. Not used for JNDI lookup.
None
name
The data source name.
Must be unique within the application.
None
connection-driver
The JDBC-driver class name for this data source,
some data sources that deal with java.sql.Connection need. For most data
sources, the driver should be oracle.jdbc.driver.OracleDriver.
None
username
Default user name used when getting data
source connections.
None
password
Default password used when getting data
source connections.
None
URL
The URL for database connections. Vendor
specific.
None
xa-location
The logical name of an XA data source.
None
ejb-location
Use this for JTA single-phase commit transactions
or looking up emulated data sources. If you use it to retrieve the data
source, you can map the returned connection to oracle.jdbc.OracleConnection.
None
stmt-cache-size
A performance tuning attribute set to a non-zero
value to enable JDBC statement caching and to define the maximum number
of statements cached. Enabled to avoid the overhead of repeated cursor
creation and statement parsing and creation. Applicable only for emulated
data sources where connection-driver is oracle.jdbc.driver.OracleDriver
and class is com.evermind.sql.DriverManagerDataSource.
0 (disabled)
inactivity-timeout
Same
60
connection-retry-interval
Same
1
max-connections
Same
Depends on the data source type.
min-connections
Same
0
wait-timeout
Same
60
max-connect-attempts
Same
3
clean-available-connections-threshold
Same
30
rac-enabled
Same
false
schema
Same
None
Native data sources
For native data sources, the <data-source> element has
the following attributes (each attribute is optional except when marked
as 'Required'; and 'Same' means the attribute has the same meaning
as in the non-emulated case):
Attribute Name
Description
Default
class
<Required>.
Names the class that implements the data source, For example, oracle.jdbc.pool.OracleConnectionCacheImpl.
N/A
location
Same
None
name
Same
None
username
Same
None
password
Same
None
URL
Same
None
inactivity-timeout
Same
60
connection-retry-interval
Same
1
max-connections
Same
Depends on the data source type.
min-connections
Same
0
wait-timeout
Same
60
max-connect-attempts
Same
3
clean-available-connections-threshold
Same
30
rac-enabled
Same
false
schema
Same
None
For native data sources, the <data-source> element may
have 0 or more of the <property> element, which is used to
set generic properties on the data source object defined by the 'class'
attribute. Each <property> element contains 2 required attributes:
'name' and 'value'.
Example data source configurations for Oracle Application Server 10g (9.0.4) and 10g (10.1.2):
Oracle Application Server 10g (10.1.3) Data Source Configuration
In Oracle Application Server 10g (10.1.3), data sources have been recategorized and now
there are only 2 types of data sources: managed and native.
Data sources are still defined and configured via the data-sources.xml
file. In this file, the root element <data-sources> now may
contain 0 or more of these XML elements:
<native-data-source>, which defines one Native Data
Source;
<managed-data-source>, which defines one Managed
Data Source;
<connection-pool>, which defines one connection
pool that can be used by any of the Managed Data Sources (but not Native
Data Sources).
The <managed-data-source> element has the following attributes
(each attribute is optional except when marked as 'Required'):
Attribute Name
Description
Default
name
<Required>.
The name of the managed data source. Must be unique.
None
jndi-name
<Required>. The name with which
this data source will be bound into JNDI.
None
connection-pool-name
The
name of the connection pool that this managed data source uses to pool
its connections.
None
user
The default user
to use to get connections from the data source.
None
password
The default password to use to get connections
from the data source.
None
login-timeout
The maximum time in seconds that this
data source will wait while attempting to connect to a database.
A value of zero specifies that the timeout is the default system timeout
if there is one; otherwise, it specifies that there is no timeout.
0
tx-level
The transaction level supported by this data
source. A value of "local" indicates that this data source and the
connections it produces may participate in local transactions only.
A value of "global" indicates that this data source and the connections
it produces may participate in local and global transactions.
global
<native-data-source>
The <native-data-source> element has the following attributes
(each attribute is optional except when marked as 'Required'):
Attribute Name
Description
Default
name
<Required>.
The name of the managed data source. Must be unique.
None
jndi-name
<Required>. The name with which
this data source will be bound into JNDI.
None
data-source-class
<Required>.
The fully qualifed class of the data source implementation to use for
this data source. This must be an implementation of javax.sql.DataSource.
None
user
The default user
to use to get connections from the data source.
None
password
The default password to use to get connections
from the data source.
None
login-timeout
The maximum time in seconds that this
data source will wait while attempting to connect to a database.
A value of zero specifies that the timeout is the default system timeout
if there is one; otherwise, it specifies that there is no timeout.
0
url
The url that the data source uses to connect
to the database.
None
Besides the above attributes, the <native-data-source>
element may have 0 or more of the <property> element, which
is used to set generic properties on the data source object defined by
the 'data-source-class' attribute. Each <property>
element contains 2 required attributes: 'name' and 'value'.
<connection-pool>
The <connection-pool> element has the following attributes
(each attribute is optional except when marked as 'Required'):
Attribute Name
Description
Default
name
<Required>.
The name of the connection pool. Must be unique.
None
min-connections
The
minimum number of connections that the connection pool will maintain.
0
max-connections
The maximum number
of connections that can be open at any given time. A value of 0 or less
indicates that there is no maximum limit.
0
initial-limit
This sets the size of the connection cache
when the cache is initially created or reinitialized. When this property
is set to a value greater than 0, that many connections are pre-created
and are ready for use. This property is typically used to reduce the "ramp
up" time in priming the cache to its optimal size.
0
used-connection-wait-timeout
The amount of time to wait (in seconds)
for a used connection to be released by a client. This only applies
when the maximum number of connections has been retrieved from the data
source and are in use. In this case when a client tries to borrow
a connection from the pool and all connections are in use, the connection
pool will wait for a connection to be released back to the pool.
60
inactivity-timeout
The amount of time (in seconds) that an
unused connection is inactive before it is removed from the pool.
60
login-timeout
The maximum amount of time (in seconds)
that this data source will wait while attempting to connect to a database.
A value of zero specifies that the timeout is the default system timeout
if there is one; otherwise, it specifies that there is no timeout.
0
connection-retry-interval
The interval to wait (in seconds) before
retrying a failed connection attempt. This is used in conjunction
with "max-connnect-attempts".
1
max-connect-attempts
The number of times to retry making a
connection. This is used in conjunction with "connection-retry-interval".
3
validate-connection
Indicates whether or not a connection,
when borrowed from the pool, will be validated against the database.
A value of "true" indicates that when a connection is borrowed from the
connection pool, the statement, stated by "validate-connection-statement",
is executed to verify that the connection is valid. A value of "false"
means that no statement will be executed when a connection is borrowed
from the pool. Used in conjunction with "validate-connection-statement".
false
validate-connection-statement
The SQL statement to execute when a connection
is borrowed from the pool. Used in conjunction with "validate-connection".
None
num-cached-statements
The maximum number of statements that
should be cached for each connection. Any value greater than 0
automatically enables statement caching for the data source.
0
time-to-live-timeout
The maximum time, in seconds, a used connection
may be active. When this timeout expires, the used connection is unconditionally
closed, the relevant statement handles canceled, and the connection
is returned to the connection pool. A value of –1 means that the
feature is not enabled.
-1
abandoned-connection-timeout
Oracle databases only. Abandoned-connection-timeout
is similar to the inactivity-timeout, but on a logical connection. When
set, JDBC monitors SQL database activity on this logical connection (connection
borrowed from the cache by the user). For example, when a stmt.execute()
is invoked on this connection, a heart beat is registered to convey
that this connection is active. The heart beats are monitored only at
places (to lower the cost of monitoring), that result in database execute
calls. If a connection has been inactive for the specified amount of time,
the underlying PooledConnection is reclaimed and returned to the cache
for reuse. The default value is -1, meaning, this feature is not in effect.
-1
disable-server-connection-pooling
Determines whether or not to disable the
application server's connection pool. This attribute is available
because some JDBC drivers provide connection pooling inside the driver.
When the JDBC driver is Oracle and the driver is using the Implicit Connection
Cache then this attribute will be ignored.
false
property-check-interval
Oracle databases only. The time interval
(in seconds) for the cache daemon thread to enforce the time out limits.
900
lower-threshold-limit
Oracle databases only. The lower threshold
limit on the connection pool. Defaults to 20% of the max-connections.
20%
Besides above attributes, each <connection-pool>
element must contain one <connection-factory> element,
which defines the connection factory used to create connections for
the connection pool. It has the following attributes (each attribute
is optional except when marked as 'Required'):
Attribute Name
Description
Default
factory-class
<Required>.
Defines the fully-pathed class of the connection-factory implementation.
It must be an implementation of java.sql.Driver, javax.sql.DataSource,
javax.sql.ConnectionPoolDataSource, or javax.sql.XADataSource.
None
url
<Required>.
The url to use to connect to the underlying database.
None
user
The
default user to use to connect to the database.
None
password
The default password to use to connect
to the database.
None
login-timeout
The maximum time in seconds to wait while
attempting to connect to a database. A value of zero specifies
that the timeout is the default system timeout if there is one; otherwise,
it specifies that there is no timeout.
0
Example data source configurations for Oracle Application Server 10g (10.1.3):
1. Native Data Source - Oracle JDBC to Oracle Database
<native-data-source
name="nativeDataSource"
jndi-name="jdbc/nativeDS"
description="Native DataSource"
data-source-class="oracle.jdbc.pool.OracleDataSource"
user="scott"
password="tiger"
url="jdbc:oracle:thin:@//dbhost:1521/dbservicename">
</native-data-source>
2. Native Data Source - DataDirect JDBC to DB2 UDB
<native-data-source
name="nativeDataSource"
jndi-name="jdbc/nativeDS"
description="Native DataSource"
data-source-class="com.ddtek.jdbcx.db2.DB2DataSource"
user="frank"
password="frankpw"
url="jdbc:datadirect:db2://server_name:50000;DatabaseName=your_database">
</native-data-source>
4. Native Data Source - DataDirect JDBC to SQLServer
<native-data-source
name="nativeDataSource"
jndi-name="jdbc/nativeDS"
description="Native DataSource"
data-source-class="com.ddtek.jdbcx.sqlserver.SQLServerDataSource"
user="frank"
password="frankpw"
url="jdbc:datadirect:sqlserver://server_name:1433;User=usr;Password=pwd">
</native-data-source>
5. Native Data Source - SQLServer JDBC to SQLServer
<native-data-source
name="nativeDataSource"
jndi-name="jdbc/nativeDS"
description="Native DataSource"
data-source-class="com.microsoft.jdbc.sqlserver.SQLServerDataSource"
user="frank"
password="frankpw"
url="jdbc:microsoft:sqlserver://hostName:Port;DatabaseName=pubs">
</native-data-source>
6. Managed Data Source Using an XADataSource Connection Factory
<managed-data-source
jndi-name="jdbc/ManagedDS"
description="Managed DataSource"
connection-pool-name="myConnectionPool"/>
7. Managed Data Source Using a DataSource Connection Factory
<managed-data-source
jndi-name="jdbc/ManagedDS"
description="Managed DataSource">
connection-pool-name="myConnectionPool"/>
8. Managed Data Source Using a Driver Connection Factory
<managed-data-source
jndi-name="jdbc/ManagedDS"
description="Managed DataSource">
connection-pool-name="myConnectionPool"/>
Bookmark
