Using connection caching

Benefits of connection caching

To optimize database processing, EAServer provides support for connection caching. Connection caching allows EAServer components to share pools of preallocated connections to a remote database server, avoiding the overhead imposed when each instance of a component creates a separate connection. By establishing a connection cache, a server can reuse connections made to the same data source.

How it works

Ordinarily, when a PowerBuilder application connects to a database, PowerBuilder physically terminates each database connection for which a DISCONNECT statement is issued. By contrast, when a PowerBuilder component uses an EAServer connection cache, EAServer logically terminates the database connection but does not physically remove the connection. Instead, the database connection is kept open in the connection cache so that it can be reused for other database operations.

NoteDo not disconnect in destructor event EAServer releases all connection handles to the cache when a transaction is completed or when the component is deactivated. If you place a DISCONNECT statement in the destructor event, which is triggered after the deactivate event, the connection has already been logically terminated and the DISCONNECT causes a physical termination. DISCONNECT statements can be placed in the deactivate event.

All connections in a cache must share a common user name, password, server name, and connectivity library.

Accessing a cache by user

If you want to retrieve a connection from the cache that uses a specified set of user name, password, server, and connectivity library values, you do not need to modify your database access code to enable it to use the cache. You simply need to create a new cache in EAServer Manager that has the database connection properties (user name, password, server name, and connectivity library) required by the component. At runtime, when the component tries to connect to the database, EAServer automatically returns a connection from the cache that matches the connection values requested by the component.

Accessing a cache by name

If you want to retrieve a connection from a cache by specifying the cache name, set the CacheName DBParm to identify the cache you want to use. Accessing a cache by name allows you to change the user name, password, or server in EAServer Manager without requiring corresponding changes to your component source code.

NoteEnabling cache-by-name access To access a cache by name, select the Enable Cache-By-Name Access option for the cache in EAServer Manager. By default, this option is not selected.

To enable cache-by-name access, you need jagadmin rights.

This code for a PowerBuilder component shows how to access a cache by name:

SQLCA.DBMS = "ODBC"
SQLCA.Database = "EAS Demo DB"
SQLCA.AutoCommit = FALSE
SQLCA.DBParm = "ConnectString='DSN=EAS Demo DB;
   UID=dba;PWD=sql',CacheName='mycache'"

NoteCache names are case-sensitive Cache names are case-sensitive; therefore, make sure the case of the cache name you specify in your script matches the case used for the name in EAServer.

Retrieving a connection by proxy

Regardless of whether you access a cache by user or name, you can retrieve a connection by proxy. Retrieving a connection by proxy means that you can assume the identity and privileges of another user by providing an alternative login name.

This feature can be used with any database that recognizes the SQL command set session authorization. In order for user A to use the ProxyUserName DBParm to assume the identity of another user B, user A must have permission to execute this statement. For example, for ASA, user A must have DBA authority, and for ASE, user A must have been granted permission to execute set session authorization by a System Security Officer.

For more information about the PowerBuilder database interfaces that support proxy connections, see Connecting to Your Database.

To use proxy connections, set the ProxyUserName DBParm to identify the alternative login name. This example shows how to retrieve a connection by proxy:

SQLCA.DBMS = "ODBC"
SQLCA.DBParm = "CacheName='MyEAServerCache',
   UseContextObject='Yes',ProxyUserName='pikachu'"

NoteBefore you can use a connection by proxy Set-proxy support must be enabled in the cache properties file before components can take advantage of it. EAServer Manager does not automatically create an individual cache properties file when you create a cache, so you must create this file manually. Name the file cachename.props and put it in the EAServer\Repository\ConnCache directory. Once you have created the cache properties file, add the following line:

com.sybase.jaguar.conncache.ssa=true

For this setting to take effect, you must refresh EAServer. For more information on managing connection caches, see the EAServer System Administration Guide.

You must also set up your database server to recognize and give privileges to the alternative login name defined in the ProxyUserName DBParm.

What happens when all connections are in use

You can control what happens if all connections in a cache are in use. To do this, set the GetConnectionOption DBParm to one of the following values:

Value

Description

JAG_CM_NOWAIT

Causes the attempt to connect to fail with an error if no connection can be returned.

JAG_CM_WAIT

Causes the component to wait until a connection becomes available.

JAG_CM_FORCE

Allocates and opens a new connection. The new connection is not cached and is destroyed when it is no longer needed.

By default, PowerBuilder uses JAG_CM_FORCE.

What happens when a connection is released

You can also control what happens when a connection is released. To do this, set the ReleaseConnectionOption DBParm to one of the following values:

Value

Description

JAG_CM_DROP

Closes and deallocates the connection. If the connection came from a cache, a new connection is created in its place. Use JAG_CM_DROP to destroy a connection when errors have made it unusable.

JAG_CM_UNUSED

If the connection was taken from a cache, it is placed back in the cache. A connection created outside of a cache is closed and destroyed.

By default, PowerBuilder uses JAG_CM_UNUSED.

EAServer connection caches for Unicode support

The following EAServer native connection caches support Unicode connections for PowerBuilder components:

These connection cache types accept Unicode connection parameters and then send a request to the database driver to open a Unicode connection to the database. With a Unicode connection, PowerBuilder components can communicate with the database using Unicode.

If you are using the Oracle9i native interface (O90) to access an Oracle9i database in a PowerBuilder component in EAServer, use the database driver type OCI_9U for the connection cache. If you do not, access will fail.

For an ODBC connection cache, use the database driver type ODBCU to access multiple-language data in an ASA Unicode database or DBCS data in an ASA DBCS database and set the database parameter ODBCU_CONLIB to 1. For example:

SQLCA.DBParm = "CacheName='EASDemo_u',
   UseContextObject='Yes',ODBCU_CONLIB=1"