Using connection caching – PB Docs 126

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. Connection caches are
called data sources in EAServer 6.x.

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.

Do 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. In EAServer 6.x, you create a data
source (cache) by selecting Resources>Data Sources>Add
in the Management Console. 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 the Management Console without
requiring corresponding changes to your component source code.

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

Cache 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 SQL Anywhere, 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:

Before 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. In EAServer 5.x, 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 EAServerRepositoryConnCache directory. Once
you have created the cache properties file, add the following line:

For this setting to take effect, you must refresh EAServer.

In EAServer 6.x, you create
a data source by selecting Resources>Data Sources>Add
in the Management Console. Select Set Session Authorization and
specify a name in the Set Session Authorization System ID box. The properties
file for the data source is stored in the Repository in the InstancecomsybasedjcsqlDataSource directory.

For more information on managing connection caches (or data
sources), see the EAServer documentation.

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:

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:

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.

For EAServer 5.x:

• OCI_9U – Oracle9i Unicode
  Cache

• OCI_10U – Oracle 10g Unicode
  Cache

• ODBCU – ODBC Unicode Cache

For EAServer 6.x:

• JCM_Oracle_Unicode – Oracle9i or
  10g Unicode Cache

• JCM_Odbc_Unicode – ODBC
  Unicode Cache

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 5.x,
use the database driver type OCI_9U for the connection
cache. If you do not, access will fail.

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