Pooling database transactions – PB Docs 2022

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 section called “SetTransPool” in PowerScript Reference.