| Transaction pooling In the two-tier
architecture, PowerBuilder applicaton directly
connects to the database using the transaction
object SQLCA or another. As you know, a typical
PowerBuilder application connects to the database
in the beginning ( either in the login window or
in the application’s Open event) and closes the
database connection in application’s Close event. That means, if
there are 1000 clients, a total of 1000
connections are open in the database server.
In the n-tier
model, the client will not connect to the
database server directly, instead the client does
all the database specific operations via the
application server. The application server in
turn connects to the database and executes the
client’s requests and sends the results
back.
In the
application server, you can handle the
client’s database requests in one of two
ways. The first one would be most straight
forward, in which you open one connection for
each client. That means, you open 1000
connections for a total of 1000 clients. As you
know, all clients won’t be doing the
database operations all the time. Most of the
time user spend time in navigation and
reading/printing/analysing the data that is
displayed on the client machine. That means, each
client’s database connection has a lot of
idle time.
To maximize the
database performance, you can make use of the
above described idle database connection time for
each client and reduce the number of database
connections that are required to be open at any
given time. To make use of this, you need to pool
the connections. When you don’t use the
transaction pooling, PowerBuilder physically
terminates the database connection when the Disconnect statement is issued.
Connecting/Disconnecting physically takes a lot
of resources and time. When you use transaction
pooling, PowerBuilder disconnects the database
connection logically and commits all the changes
and it reuses the disconnected database
connection for another client. To use the
transaction pooling, you need to call SetTransPool() function.
PBMSServer.SetTransPool(
100, 150, 10 )
The above code
is setting a minimum of 100 database connections
and a maximum of 150 database connections. The
last parameter specifies the number of seconds to
wait for a database connection. If the client is
not able to get the database connection within
the timeout period (10 seconds in this example),
the server application sends the error to the
client.
You might have
got a doubt about database connection pooling, if
client 1 uses a connection as "dba" and
disconnects it, and if the client 2 is trying to
connect as "prasad", will the
application server use the first client’s
database connection.
A good
question. If the application server reuse the
connection, it would be wrong, since the
application server is connecting to the database
as "dba" instead of "prasad".
PowerBuilder will reuse the database connection
ONLY when the transaction object’s attribute
values match. In the above example, it won’t
reuse the database connection since the user id
from two clients are different.
|
