Database management for Sterling B2B Integrator

JDBC connection pool overview

Sterling B2B Integrator uses internal connection pooling to minimize delays in the creation of new connections to the database server. When a connection is requested, an existing connection is used whenever possible. Internal connection pooling improves performance by removing the need to go to the driver and creating and delete a new connection each time one is needed.

Internal connection pooling mechanism implements a connection pool in every JVM started for the product. In a cluster with multiple nodes, the number of connections the database must establish is the total of all connections for all the connection pools.

For example, if you have four JVM instances and each connection pool averages around 30 connections, your database server must establish 120 database connections.

With this implementation, the database reports any idle connections to the system, which is the expected behavior. JDBC properties can be tuned in the jdbc.properties file to delete idle connections and minimize usage.

Implementation of connection pooling

Each database pool is created with an initial number of connections, as defined by the initsize property in the jdbc.properties file. As more connections are required, Sterling B2B Integrator requests additional connections up to the maximum number defined by the maxsize property. When Sterling B2B Integrator finishes with a connection, the connection is returned to the pool of connections for later use.

If Sterling B2B Integrator requires connections beyond the maximum size limit for each database pool, and every pool is in use, Sterling B2B Integrator can request new connections up to the value defined by the buffer_size property. Connections in this “buffer range” (that is, beyond the number of connections that are defined by the maxsize property) are created as needed, and deleted when the calling process is completed. To detect this condition, search for warning messages such as connections exceeded in the noapp.log file. Performance drops if Sterling B2B Integrator runs in this range for a long time.

The buffer_max value is a hard maximum. When the number of connections that are defined by buffer_max (maxsize + buffersize) is reached, additional requests for connections are denied. An error is written to the logs that indicates that the pool is exhausted and the attempt to create a new connection failed.

Summary

Each JVM can create connections for each of the pools that are defined in the jdbc.properties file and configured in the customer_overrides.properties file. Each pool grabs the initial number of connections (initsize) configured.

Because a pool requires additional connections beyond the initial size, it creates new connections up to the maxsize limit for that pool. Connections that are created with fewer than the maxsize are returned to that pool for reuse, thus improving performance.

If a pool is using all the connections up to the maxsize limit, it creates a new connection as needed within the buffer size limit. Connections beyond maxsize and below the buffersize are not reused. Each connection is created when needed, and deleted when no longer needed by the calling process. This method is expensive and harms performance if Sterling B2B Integrator runs for continued amounts of time in this state.

When the number of connections (maxsize + buffersize) is reached, new requests for connections are refused.

You can now manage JDBC pools dynamically. For more information about dynamically managing JDBC pools, see Dynamically Manage JDBC Pools.

Configuring parameters for a database pool

The following table describes the primary parameters of a database pool and the recommended value for each parameter:

Frequently asked questions

• Q: How many connections will I need?

  A: You can start with the recommended settings provided by the tuning wizard and then monitor the noapp.log file to ensure that you are not close to the maximum size of the connection pool (maxsize). Monitor the usage over a period of time and observe if the usage is increasing. If the usage limits exceed 25% of the settings that are recommended by the tuning wizard and the demand for connections is increasing, contact the IBM® Professional Services onsite consultant or IBM Customer Support.

• Q: Why are connections shown as being idle?

  A: Getting a new connection from the database is expensive. Connections are held in a pool to be available when the system needs them, which means that connections are often shown as being idle. This method is a performance trade-off that enables connections to be available when needed.

• Q: Can I kill idle connections?

  A: Configure the pool to keep fewer connections, and release connections after a specified amount of time.

Schema objects

This topic covers the following concepts:

• Placement of Schema Table Objects
• Placement of Indexes
• Sterling B2B Integrator Database Tables

Placement of Schema Table Objects

The Sterling B2B Integrator installation guide provides directions about how to create the database, including the necessary changes to some critical parameters. The DDL statements allow you to create a simple schema that is suitable for general use. You need to review, and possibly modify, these statements before production.

Placement of Indexes

The DDL statements create a minimal set of indexes for general use. You might need to create more indexes to suit your business needs. Contact IBM Professional Services or IBM Customer Support to create more indexes. You should also maintain a list of the indexes added and ensure that they are present after you upgrade Sterling B2B Integrator and monitor its usage. These indexes may or may not be added to the Sterling B2B Integrator and is specific to your operating environment.

You might want to create more table spaces for storing indexes in addition to those table spaces for the base tables. Table spaces should have multiple data files for I/O load balancing.

Database tables that can substantially grow

The following table lists some of the tables that are defined in Sterling B2B Integrator experience substantial input, output, and rapid growth.

These tables are candidates for moving to a table space or segment that is separate from the rest of the database.

Controlling entries in the CORRELATION_SET table

You can control when correlation entries for different activities of Sterling B2B Integrator are written to the CORRELATION_SET table. Reducing the number of records in the CORRELATION_SET table improves the performance of Sterling B2B Integrator.

The doc_tracking.properties file includes properties that enable or disable correlation entries for the following activities. The properties are in parentheses.

• Mailbox (makeCorrelationForMailbox)
• FTP (makeCorrelationForFTP)
• SFTP (makeCorrelationForSFTP)
• System tracking extension (makeTrackingExtensions)

The default value for each property is true, which means that correlation entries are written to the CORRELATION_SET table.

The doc_tracking.properties file is in the install_dir\install\properties directory.