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 24, "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.

Creating a new connection cache

1. Highlight the Connection Caches folder.
2. Select either File | New Connection Cache or File | New XA Resource.
3. Configure the connection cache properties.

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

Viewing or modifying 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:
   • Connection Cache Properties - view or modify this connection cache's properties.
   • Delete Connection Cache - delete the connection cache from the system.

General properties

The properties that you configure on the General tab are:

• Connection Cache Name The name for this cache configuration. Connection cache names are limited to one word, which can contain letters, numbers, and underscores. Names are case-sensitive. You cannot modify the name of an existing connection cache.
• Description A description of the connection cache. The description is a string with a maximum of 255 characters.
• Database Type The database type. Required for caches that use the EAServer automatic persistence or stateful failover features, such as EJB CMP entity beans.
  
  Several types are predefined, and you can create your own. See "Database type setting" for more information.
• Server Name For each of these database driver types, enter:
  • ODBC - the ODBC data source name.
  • CTLIB - the server name as it would be specified in a ct_connect call. On UNIX platforms, the server must be listed in the EAServer interfaces file. For Windows NT, it must be listed in the ini\sql.ini file.
  • JDBC - the URL appropriate for use in JDBC calls.
    
    This is an example for an Oracle JDBC connection:

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

  • OCI 7.X - the Oracle SQL*Net connect string or database alias.
  • OCI 8.X - the Oracle SQL*Net connect string or database alias.
  • OCI 9.X - the Oracle SQL*Net connect string or database alias.
  
  
  For ODBC or JDBC connections, see your driver documentation for more information. For OCI connections, see your Oracle documentation.

    

  jConnect metadata support required If you have transactional components that use jConnect caches, make sure the jConnect DatabaseMetaData stored procedures are installed on the database server.

• User Name The user name for this cache. The name used (along with a password) to connect to the database identified by the server entry.
  
  The cache user name and password are always used for the initial connection, but you can configure set-proxy support so that a component's database work is done using the client identity. See com.sybase.jaguar.conncache.ssa for more information. This property must be set on the All Properties tab.
• Password The password for this cache. The password and user name are used to connect to the database identified by the server entry. Passwords are encrypted in the EAServer configuration file.
  
  Jaguar Manager does not display passwords for existing caches. If you need to change a password, enter the new password and click OK.

Cache properties

Configure these properties on the Cache tab:

• Enable Cache-by-Name Access Select this option to allow retrieval of a database connection using the connection cache name instead of requiring a user name and password.
  
  Cache-by-name access allows you to use caches in components without requiring access to the database password from the component source code.
  
  Cache-by-name access is required for JDBC caches that are used in EJB or Web application resource references.
• Enable connection sanity check Specifies whether connections should be verified.
  
  Components may release a connection that is not ready for use by another component. For example, there may be unretrieved results on the connection. When the option is enabled, EAServer verifies that the connection is open and accepts commands before making it available for reuse. Disabling the option increases performance, but may complicate debugging.
  
  The default query for testing the connection is "select 1". If this syntax is invalid for the database, you must configure a valid test query by setting the com.sybase.jaguar.conncache.check property on the All Properties tab. For more information, see com.sybase.jaguar.conncache.check.
• Number of Connections in Cache The number of connections in the pool.
  
  After a connection is released, it is returned to the pool. The default value is 10. You can increase this number if performance suffers due to an insufficient number of available connections.
• Service Name When you are defining a Java connection cache that uses the jConnect driver and that connects to a Sybase DirectConnect gateway, set the Service Name field to the back-end data server name that the DirectConnect gateway connects to.
  
  Service name is ignored for caches that use JDBC drivers other than jConnect for JDBC.

Driver properties

Configure these properties on the Driver tab:

• DLL or Class Name Enter the file name of the connection library, and select the connection library type used for this cache. Your choices for library type are:
  • Client Library 11.x - for Sybase Open Client Client-Library connections.
  • ODBC - for connections using an open database connectivity driver.
  • JDBC - for connections using a Java Database Connectivity driver.
  • OCI 7.x - for connections using OCI 7.x.
  • OCI 8.x - for connections using OCI 8.x.
  • OCI 9.x - for connections using OCI 9.x.
  
  
  For UNIX platforms, use one of the connection libraries listed in Table 4-1.

  Table 4-1: UNIX platform connection libraries

  Type	Connection library name
  Client Library 11.x	libjct_r.so for Solaris, AIX, Digital UNIX, and Linux. libjct_r.sl for HP-UX. You must use the version in the EAServer lib subdirectory. This version has been optimized for EAServer threading.
  ODBC	libodbc.so (installed in $JAGUAR/intersolv/lib) for Solaris and AIX. libodbc.so (installed in /var/opt/DAU100/connect/lib) for Digital UNIX. libodbc.sl (installed in $JAGUAR/intersolv/lib) for HP-UX.
  JDBC	The Java class name for the driver class. For example, if you are using a Sybase ASA or Adaptive Server Enterprise database, set one of these values: com.sybase.jdbc2.jdbc.SybConnectionPoolDataSource To access the connection cache with JNDI, you must use this driver and this syntax for the Server Name: NetworkProtocol=Tds:Server=localhost:Port=2638 com.sybase.jdbc2.jdbc.SybDriver When you use this driver, use this syntax for the Server Name: jdbc:sybase:Tds:host:2638 The Oracle connectivity driver requires oracle.jdbc.pool.OracleConnectionPoolDataSource
  OCI 7.x	libclntsh.so for Solaris, AIX, Digital UNIX, and Linux. libclntsh.sl for HP-UX.
  OCI 8.x
  OCI 9.x

    

  Library location must be in library path On Solaris, Digital UNIX, and Linux, the location of the connection library must be in your LD_LIBRARY_PATH for ODBC, Client-Library, or OCI caches, and in CLASSPATH for JDBC caches.

  On AIX, the file must be in your LIBPATH for ODBC, Client-Library, or OCI caches, and in CLASSPATH for JDBC caches.On HP-UX, the file must be in your SHLIB_PATH for ODBC, Client-Library, or OCI caches, and in CLASSPATH for JDBC caches.

• For Windows platforms, use one of the connection libraries listed in Table 4-2.

  Table 4-2: Windows platform connection libraries

  Type	Connection library name
  Client Library 11.x	libjct.dll - you must use the version in the EAServer dll subdirectory. This version has been optimized for EAServer threading.
  ODBC	odbc32.dll
  JDBC	The Java class name for the driver class. For example, if you are using a Sybase ASA or Adaptive Server Enterprise database, set one of these values: com.sybase.jdbc2.jdbc.SybConnectionPoolDataSource To access the connection cache with JNDI, you must use this driver and this syntax for the Server Name: NetworkProtocol=Tds:Server=localhost:Port=2638 com.sybase.jdbc2.jdbc.SybDriver When you use this driver, use this syntax for the Server Name: jdbc:sybase:Tds:host:2638 The Oracle connectivity driver requires oracle.jdbc.pool.OracleConnectionPoolDataSource
  OCI 7.x	ociw32.dll
  OCI 8.x	oci.dll
  OCI 9.x	oci.dll

    

  Library location must be in PATH or CLASSPATH The location of the connection library must be in your PATH environment variable for ODBC, Client-Library, or OCI caches, and in CLASSPATH for JDBC caches.

• Use HA Connection When using Client Library 11.0 to connect to an Adaptive Server Enterprise database, this enables the high availability failover feature.
  
  To use this feature, you also need to modify the client connection information and pass CS_HAFAILOVER when calling ct_config or ct_con_props--see "Open Client Client-Library" for more information.

All Properties tab

To set the cache properties described in this section, use the All Properties tab. For a detailed list of the properties you can define for a connection cache, see Connection cache properties. Additional driver-specific properties may be required depending on the type of cache and driver class or library.

JDBC driver properties

For JDBC connection caches, properties that do not begin with com.sybase.jaguar.connection are passed through as driver properties for the connection.

Different JDBC drivers recognize different sets of properties. For a JDBC connection cache, these additional connection properties allow you to specify settings beyond those shown in the Connection Cache Properties dialog.

Cache properties are defined in this form:

propname=value specification

PACKETSIZE=2048

See your driver documentation for more information.

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:

• CS_HOSTNAME
• CS_APPNAME
• CS_TDS_VERSION
• CS_PACKETSIZE

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.

   Make 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-3.

You can create your own database type definitions by copying one of the existing files in the EAServer Repository/DatabaseType directory, then editing the property settings. Any changes made to the predefined database type files may be overwritten by subsequent EAServer installs.

Use of prepared statements To support EJB CMP or automatic persistence for entity components, EAServer uses JDBC prepared statements to execute database queries. On some databases, the use of prepared statements may exhaust database resources such as cursors in Oracle. If you encounter this problem, edit the database type properties file in the EAServer Repository/DatabaseType subdirectory. Look for a line that contains "statementCache" and edit the setting to look like this:

com.sybase.jaguar.databasetype.statementCache=false 

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 | Connection Cache Properties.
3. In the Connection Cache Properties dialog, click Refresh.

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

• If you change the connectivity library setting, cache references held by components become invalid. Attempts to retrieve connections or query cache properties will cause errors. In this case, the component must retrieve a new cache handle.
• If you change other properties, such as user name, password, server name, or the number of connections in a cache, cache references remain valid, but components may be affected by the changed settings. For example, if you change the server name, connections retrieved after the cache has been refreshed will go to the server indicated by the new name.

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 | Connection Cache Properties.
3. In the Connection Cache Properties dialog, enter the password. (Though the password is recorded in the repository, the window does not display it.)
4. Click Ping.
5. Jaguar 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.

Copyright © 2002 Sybase, Inc. All rights reserved.