Connecting to databases  Configuring connectors

Chapter 4: Database Access

Configuring connection caches

A connection cache maintains a pool of available connections that EAServer components use to interact with third-tier data servers. You must configure connection caches for the specific user and database combinations used by your components. A connection cache entry improves performance by eliminating the overhead associated with setting up a connection when one is required. Create as many connection caches as you need.

See Chapter 26, “Using Connection Management,” in the EAServer Programmer’s Guide for additional information.

You must refresh a cache before any changes to the cache properties take effect, and you should test the connection with ping before trying to access it from components.

You cannot define two distinct caches that use identical values for server, user, password, and connectivity library. If two caches are defined with matching values for these settings, and your application requests one, EAServer returns the first match that is found.

StepsCreating a new connection cache

  1. Highlight the Connection Caches folder.

  2. Select File | New Connection Cache.

  3. Configure the connection cache properties in the wizard.

Configured connection cache entries appear on the right side of the window of EAServer Manager when you highlight the Connection Cache folder on the left side of the window.

StepsViewing, modifying, or deleting a connection cache entry

  1. Expand the Connection Caches folder.

  2. Highlight the connection cache you want to modify.

  3. From the File menu, select one of:


General properties

The properties that you configure on the General tab are:


About JDBC connection URLs

For JDBC connection caches, the Server Name field is a URL whose syntax depends on the JDBC interface level that the driver class supports.

JDBC level 1 drivers These driver classes implement the java.sql.Driver interface and typically have class names that end in Driver. For example, the Sybase jConnect level 1 driver class is com.sybase.jdbc2.jdbc.SybDriver, which requires syntax like: 

jdbc:sybase:Tds:host:2638

The Oracle level 1 driver is class oracle.jdbc.driver.OracleDriver, which requires syntax like this example:

jdbc:oracle:thin:@dbserver:1521:orcl

For details on the jConnect syntax, see the jConnect documentation. For details on drivers from other vendors such as Oracle, see the vendor documentation.

JDBC level 2 connection pool drivers These driver classes implement the javax.sql.ConnectionPoolDataSource interface and typically have class names that end in PoolDataSource. For example, the Sybase jConnect level 2 driver is com.sybase.jdbc2.jdbc.SybConnectionPoolDataSource, which requires syntax like:

NetworkProtocol=Tds:Server=hostname-or-IPaddress:Port=port-number

The Oracle level 2 driver class is oracle.jdbc.pool.OracleConnectionPoolDataSource, which requires syntax like this example: 

ServerName=server:Port=1521:DatabaseName=ORC:DriverType=thin

Level 2 drivers support a setter method naming scheme to indicate the connection properties that they support. A setter method named setProperty indicates that the driver supports the property named Property. For example, a method named setServer with a string input parameter indicates that the driver recognizes the Server property. You can set any string-valued property in the server URL. EAServer follows these rules to parse the URL:

You can use the Java javap command to list the property setter methods supported by your level 2 driver (or by the DataSource driver class that it extends). Driver properties that do not take string types can be set in the connection cache property com.sybase.jaguar.conncache.config-property, described in com.sybase.jaguar.conncache.config-property. Use the Advanced tab to set this property. For information on what each property configures, see the driver documentation.


About ODBC data source names

On UNIX platforms, ODBC data source names used in EAServer must be defined in the ODBC driver manager configuration, typically by modifying the odbc.ini file in the driver manager installation that is included with EAServer. For instructions, see the EAServer Installation Guide for your platform.

On Windows platforms, EAServer uses SQLDriverConnect to establish ODBC connections, which allows you to specify driver-specific connection information. You can specify either a data source and one or more optional parameters, or a DSN file that contains the data source and other parameter information.

If you use a DSN file to specify connection information, all the servers in an EAServer cluster can share the information. Use this syntax to specify a DSN file, where file.dsn identifies the file name:

FILEDSN=file.dsn

Minimally, a DSN file must contain the keyword “DRIVER” and the name of the data source in the ODBC section. The following is a sample ODBC section for an ASA 9.0 database:

[ODBC]
DRIVER=Adaptive Server Anywhere 9.0
UID=dba
Compress=No
DisableMultiRowFetch=No
Debug=No
Integrated=No
AutoStop=Yes
EngineName=asademo9
DatabaseFile=C:\Sybase\SQL Anywhere 9\asademo.db

See your ODBC driver documentation for information about which parameters can be used with the driver.

To specify a data source, instead of a DSN file, use this syntax, where dataSource is the name of the data source, and param1, value1, param2, and value2 are optional driver-specific connection parameters and their corresponding values:

DSN=dataSource;[param1=value1];[param2=value2];

NoteThese restrictions apply to SQLDriverConnect strings:

For more information about using SQLDriverConnect, see the MSDN Web page.


Caching properties

Configure these properties on the Caching tab:

For information on tuning these settings, see “Tuning the cache size” in the EAServer Performance and Tuning Guide.


Driver properties

Configure these properties on the Driver tab:


XA properties

Configure these properties on the XA tab:


SQL tracing properties

Configure these properties on the SQL Tracing tab:


Advanced tab

To set the cache properties described in this section, use the Advanced tab. You can set any property on this tab, as listed in Connection cache properties. In most cases, the controls on the other tabs provide a more convenient way to set properties.

You can also configure additional driver- or library-specific properties on this tab.


JDBC driver properties

For a JDBC connection cache, you can specify settings beyond those shown in the Connection Cache Properties dialog using the com.sybase.jaguar.conncache.config-property property. For details, see com.sybase.jaguar.conncache.config-property.

For backward compatibility, cache properties that are defined in this form are still supported:

propertyName=value

Any property whose name does not begin with com.sybase.jaguar is passed to the JDBC driver as a connection property. For example:

PACKETSIZE=2048

Different JDBC drivers recognize different sets of properties. See your driver documentation for more information on the properties that can be set.


Client-Library connection settings

You can set the following properties for Client-Library connections. See the Client-Library documentation for the ct_con_props routine for more information:

This example sets all of these properties:

CS_HOSTNAME=myhostname
CS_TDS_VERSION=CS_TDS_46
CS_PACKETSIZE=512
CS_APPNAME=myapp

Any property name not recognized by EAServer is ignored.

The CS_TDS_VERSION property takes one of these values: CS_TDS_50, CS_TDS_495, CS_TDS_46, CS_TDS_42, or CS_TDS_40.

The CS_PACKETSIZE property takes a numeric value within the range of legal packet sizes for the server. If the server does not support the packet size, the cache cannot connect to the server.

NoteMake sure there is no extra white space between the property name, the equal sign, and the property value, or after the property value.


Database type setting

In connection caches used to support automatic persistence or stateful failover, you must configure the Database Type connection cache property. This property defines database-specific information required by the storage component, for example, the commands to verify a table exists and create new tables. Several types are predefined, as described in Table 4-6.

Table 4-6: Database type identifiers

Database identifier

To indicate

Oracle8i

Oracle version 8

Oracle9i

Oracle version 9

SQL_Server

Microsoft SQL Server

Sybase_ASA

Sybase Adaptive Server Anywhere

Sybase_ASE

Sybase Adaptive Server Enterprise

Unknown

Any other database

You can create your own database type definitions as described in Database type properties.


Connection cache refresh

If you have just modified connection cache properties, refresh the cache before you test it.

To refresh the cache:

  1. Highlight the Connection Caches folder.

  2. Highlight the cache’s icon and choose File | Refresh.

Refreshing a cache may affect running components that are using the cache, specifically:


Connection cache ping

This feature allows you to test the cache configuration to verify that connections can be made using the supplied parameters. If you have just changed any settings, refresh the cache before testing it.

To test the cache with ping:

  1. Highlight the Connection Caches folder.

  2. Highlight the cache’s icon and choose File | Ping.

  3. EAServer Manager reports whether the connection attempt succeeded.

If ping fails, check the message text for a description of the problem. The server log file may contain additional information about the cause of the error.

If you change the cache properties to correct the problem, you must refresh the cache before testing again.


Using XA resources with Adaptive Server Enterprise

To use XA resources with an Adaptive Server Enterprise 12.0 or later database running on UNIX or Windows, perform these tasks:

  1. (Windows only.) Verify that you have the correct license file, which is located in %SYBASE%\SYSAM-1_0\licenses\license.dat. To run XA transactions, you must have the ASE_DTM license.

  2. Configure the database to use XA transactions. For more information, see Using the Adaptive Server Distributed Transaction Management Features, available on the Sybase Product Manuals Web page.

  3. Create a database to use for XA transactions.

  4. Configure the database tables and stored procedures required for JDBC connection caches, which are available in $JAGUAR/html/classes/sp/sql_server12.sql.

  5. Configure the database tables required for C/C++ connection caches. You can install these tables by running xa_load.sh (UNIX) or xa_load.bat (Windows), which is located in the EAServer html/classes/sp directory.

  6. Create a login, other than sa, for connecting to the database. You cannot use the sa login to connect to an Adaptive Server Enterprise databases for XA transactions.


Modifying the Adaptive Server configuration files

When you configure an XA resource for C/C++ connection caches, you need to modify two configuration files.

On UNIX:

On Windows:

interfaces and sql.ini

On UNIX, add these lines to the interfaces file:

DbServer
   master tcp ether host 5300
   query tcp ether host 5300

On Windows, add these lines to the sql.ini file:

[DbServer]
master=NLWNSCK,host,5200
query=NLWNSCK,host,5200

where DbServer is the name of the database server and host is the name of the machine on which the database server runs. DbServer should have the same value that you entered in the Server Name field when you configured the XA resource.

xa_config

For both operating systems, add these lines to the xa_config file:

[xa]
   lrm=serverName
   server=DbServer

where:

NoteserverName and DbServer represent the same server and should have the same value.




Copyright © 2005. Sybase Inc. All rights reserved. Configuring connectors