Mastering PowerBuilder

Transaction Pooling

In the two-tier architecture, PowerBuilder application directly connects to the database using either the global default transaction object SQLCA or a custom one. 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 as long as the client application is open even though the user is not doing anything.

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 straightforward, 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 spends time in navigation and reading/printing/analyzing 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 and disconnecting a database connection 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 as long as the transaction object parameters match. 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.

When you first start the application server, there are zero connection open to the database. As users keep connecting, the number of connections increases. For example, say 120 clients are connected that means there will be 120 connections open to the database. As users keep disconnecting the number of open connections keep decreasing till it 100. When 20 users disconnect, there are 100 connections open. When the 21st client disconnects, there are still 100 connections open; this is because we set minimum open connections 100 in the above function.

This is a good feature especially for web server applications. Typically, web related transactions are very short in nature and also most of those applications use few standard logins to connect to the database. That means, these applications do the user validation checking in the application server and connect to the database using some standard—internal to the company—logins. So, if you keep good amount of minimum connections open, then the overhead of connecting to the database server for each user request is very minimal versus connecting and disconnecting for every user request.

On the other hand this feature also helps to put cap on number of connection that a particular application can open to the database server. What I mean is, enterprises have a lot of applications running all the time. Sometimes, a particular application—may be running on the same machine or on different machines—opens most of the connections and block other applications for whatever is the beneath reason. For example, a batch query & printing job which runs in the night may open too many connections that even the DBA can't open a connection till the application completes. All databases do not support putting cap on connections according to our needs which we can accomplish using this feature.

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 will not reuse the database connection since the user id from two clients are different.

Home	Previous Lesson: Application Object Events for Distributed PB Next Lesson: DataWindow Synchronization in DPB