Mastering PowerBuilder

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.