We are currently one of the many environments that utilize UDB DB2 environment as our back-end. While browsing MOS I discovered this article about App. Server DBFlag Settings. I hope you find this helpful.
When the issue of DB2 threads was mentioned, this is one of the items to help relieve this situation.
We would like you to implement this change, and here’s why:
This dbflags setting controls the treatment of secondary database connections used primarily during GetNextNumberWithGapsCommit() function calls that are widely utilized in PeopleSoft applications.
By using secondary database connections for this operation, we are able to improve performance and concurrency by placing the GetNextNumber operation, which must update and will therefore require lock waits, into it’s own transaction boundary, outside that of the rest of the component’s operation.
The value of 8 for dbFlags controls whether or not this connection remains persistent for the life of the PSAPPSRV process that opens it. A value of 0 (the default) will keep this connection open until the PSAPPSRV process is ended or recycled. A value of 8 will cause the PSAPPSRV to open and close the secondary connection as it is needed. This does not affect the primary database connections that are opened when a PSAPPRV process initially boots. The trade-off for persistent vs. non-persistent secondary connections is overhead of building and tearing-down connections vs. memory buffer and other resource cleanups.
As newer versions of PeopleTools came to existence, the complexity of code, spawned threads, built in performance features also increased.
Such setups necessitate extensive use of threading and spawning and the value of dbFlags=8 promoted these efficiency features keeping the spawning and threading at its optimum performance levels. This is the main reason why PT 8.48 onwards the recommended setting for DBFlags is 8.
This not only increases performance by invoking resources only when required, but also permits proper cleanup of secondary processes after their function is accomplished.
Specifically in the DB2 arena, the PeopleSoft “connect” and “disconnect” calls that one might see in a SQL trace for example, are really “pseudo” connects from a DB2 perspective. They don’t result in “real” database connections every time. Rather, they are logically in our application code, but DB2 manages the actual connections as needed. If the threads stay busy, they never close. So for DB2, whether or not we choose to make secondary connections persistent, should make little difference in performance. But by making the dbFlags setting *not*, persistent, you are able to avoid PeopleTools code that might be problematic.