Pooling database transactions – PB Docs 150

Transaction pooling

To optimize database processing, an application can pool database transactions. Transaction
pooling maximizes database throughput while controlling
the number of database connections that can be open at one time. When
you establish a transaction pool, an application can reuse connections made
to the same data source.

How it works

When an application connects to a database without
using transaction pooling, PowerBuilder physically terminates
each database transaction for which a DISCONNECT statement
is issued.

When transaction pooling is in effect,
PowerBuilder logically terminates the database connections and commits
any database changes, but does not physically remove them. Instead,
the database connections are kept open in the transaction pool so
that they can be reused for other database operations.

When to use it

Transaction pooling can enhance the performance of an application
that services a high volume of short transactions to the same data
source.

How to use it

To establish a transaction pool, you use the SetTransPool function.
You can code SetTransPool anywhere in your application,
as long as it is executed before the application connects to the
database. A logical place to execute SetTransPool is
in the application Open event.

Example

This statement specifies that up to 16 database connections
will be supported through this application, and that 12 connections
will be kept open once successfully connected. When the maximum
number of connections has been reached, each subsequent connection
request will wait for up to 10 seconds for a connection in the pool
to become available. After 10 seconds, the application will return
an error:

For more information

For more information about the SetTransPool function,
see the PowerScript Reference.