Using 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.

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.

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

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.

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:

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

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:

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

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

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 Instance\com\sybase\djc\sql\DataSource 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.

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.

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.

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:

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