PowerExchange Connection Pools
A PowerExchange connection pool is a set of network connections to a PowerExchange Listener. The Data Integration Service connects to a PowerExchange data source through the PowerExchange Listener.
PowerExchange uses connection pools for the following types of database connection objects:
- •Adabas
- •DB2 for i5/OS
- •DB2 for z/OS
- •IMS
- •Sequential
- •VSAM
To define a connection to a PowerExchange Listener, include a NODE statement in the DBMOVER file on the Data Integration Service machine. Then define a database connection and associate the connection with the Listener. The Location property specifies the Listener node name. Define database connection pooling properties in the Pooling view for a database connection.
PowerExchange Connection Pool Management
The Data Integration Service connects to a PowerExchange data source through the PowerExchange Listener. A PowerExchange connection pool is a set of connections to a PowerExchange Listener.
When a DTM process or the Data Integration Service process runs a data transformation job, it requests a connection instance from a connection pool. If the DTM process or the Data Integration Service process requires a PowerExchange connection instance, it requests the connection instance from PowerExchange.
When PowerExchange receives a request for a connection to a Listener, it uses a connection in the pool that has matching characteristics, including user ID and password. If the pool does not contain a connection with matching characteristics, PowerExchange modifies and reuses a pooled connection to the Listener, if possible. For example, if PowerExchange receives a request for a connection for USER1 on NODE1 and finds only a pooled connection for USER2 on NODE1, PowerExchange reuses the connection, signs off USER2, and signs on USER1.
When PowerExchange returns a Listener connection to the pool, it closes any files or databases that the Listener had opened.
If you associate multiple database connection objects with the same Listener node name, PowerExchange combines the connections into a single pool. For example, if you associate multiple database connections to NODE1, a connection pool is used for all PowerExchange connections to NODE1. To determine the maximum size of the connection pool for the Listener, PowerExchange adds the Maximum # of Connections values that you specify for each database connection that uses the Listener.
If you want each database connection object to use a separate connection pool, define multiple NODE statements for the same PowerExchange Listener and associate each database connection object with a different Listener node name.
Note: PowerExchange connection pooling does not reuse netport connections unless the user name and password match.
Connection Pooling for PowerExchange Netport Jobs
Netport jobs that use connection pooling might result in constraint issues.
Depending on the data source, the netport JCL might reference a data set or other resource exclusively. Because a pooled netport connection can persist for some time after the data processing has finished, you might encounter concurrency issues. If you cannot change the netport JCL to reference resources nonexclusively, consider disabling connection pooling.
In particular, IMS netport jobs that use connection pooling might result in constraint issues. Because the program specification block (PSB) is scheduled for a longer period of time when netport connections are pooled, resource constraints can occur in the following cases:
- •A netport job on another port might try to read a separate database in the same PSB, but the scheduling limit is reached.
- •The netport runs as a DL/1 job, and you attempt to restart the database within the IMS/DC environment after the mapping finishes running. The database restart fails, because the database is still allocated to the netport DL/1 region.
- •Processing in a second mapping or a z/OS job flow relies on the database being available when the first mapping has finished running. If pooling is enabled, there is no guarantee that the database is available.
- •You might need to build a PSB that includes multiple IMS databases that the Data Integration Service accesses. In this case, resource constraint issues are more severe as netport jobs are pooled that tie up multiple IMS databases for long periods.
This requirement might apply because you can include up to ten NETPORT statements in a DBMOVER file. Also, PowerExchange data maps cannot include program communication block (PCB) and PSB values that PowerExchange can use dynamically.
PowerExchange Connection Pooling Configuration
To configure PowerExchange connection pooling, include statements in the DBMOVER configuration files on each machine that hosts the PowerExchange Listener or the Data Integration Service. Also, define connection pooling properties in the Pooling view of the connection.
DBMOVER Configuration Statements for PowerExchange Connection Pooling
To configure PowerExchange connection pooling, define DBMOVER configuration statements on each machine that hosts the PowerExchange Listener or the Data Integration Service.
Define the following statements:
- LISTENER
Defines the TCP/IP port on which a named PowerExchange Listener process listens for work requests. Include the LISTENER statement in the DBMOVER configuration file on the PowerExchange Listener machine.
- MAXTASKS
Defines the maximum number of tasks that can run concurrently in a PowerExchange Listener. Include the MAXTASKS statement in the DBMOVER configuration file on the PowerExchange Listener machine.
Ensure that MAXTASKS is large enough to accommodate twice the maximum size of the connection pool for the Listener. The maximum size of the connection pool is equal to the sum of the values that you enter for the Maximum # of Connections pooling property for each database connection that is associated with the Listener.
Default is 30.
- NODE
Defines the TCP/IP host name and port that PowerExchange uses to contact a PowerExchange Listener. Include the NODE statement in the DBMOVER file on the Data Integration Service machine.
- TCPIP_SHOW_POOLING
Writes diagnostic information to the PowerExchange log file. Include the TCPIP_SHOW_POOLING statement in the DBMOVER file on the Data Integration Service machine.
If TCPIP_SHOW_POOLING=Y, PowerExchange writes message PWX-33805 to the PowerExchange log file each time a connection is returned to a PowerExchange connection pool.
Message PWX-33805 provides the following information:
- - Size. Total size of PowerExchange connection pools.
- - Hits. Number of times that PowerExchange found a connection in a PowerExchange connection pool that it could reuse.
- - Partial hits. Number of times that PowerExchange found a connection in a PowerExchange connection pool that it could modify and reuse.
- - Misses. Number of times that PowerExchange could not find a connection in a PowerExchange connection pool that it could reuse.
- - Expired. Number of connections that were discarded from a PowerExchange connection pool because the maximum idle time was exceeded.
- - Discarded pool full. Number of connections that were discarded from a PowerExchange connection pool because the pool was full.
- - Discarded error. Number of connections that were discarded from a PowerExchange connection pool due to an error condition.
Pooling Properties in PowerExchange Connection Objects
Configure connection pooling properties in the Pooling view for a PowerExchange database connection.
- Enable Connection Pooling
Enables connection pooling. When you enable connection pooling, each connection pool retains idle PowerExchange Listener connection instances in memory. When you disable connection pooling, the DTM process or the Data Integration Service process stops all pooling activity. To delete the pool of idle connections, you must restart the Data Integration Service.
Default is enabled for DB2 for i5/OS and DB2 for z/OS connections. Default is disabled for Adabas, IMS, Sequential, and VSAM connections.
- Minimum # of Connections
The minimum number of idle connection instances that a pool maintains for a database connection after the maximum idle time is met. If multiple database connections are associated with a PowerExchange Listener, PowerExchange determines the minimum number of connections to the PowerExchange Listener by adding the values for each database connection.
- Maximum # of Connections
The maximum number of idle connection instances that a pool maintains for a database connection before the maximum idle time is met. If multiple database connections are associated with a PowerExchange Listener, PowerExchange determines the maximum number of connections to the PowerExchange Listener node by adding the values for each database connection.
Verify that the value of MAXTASKS in the DBMOVER configuration file is large enough to accommodate twice the maximum number of connections to the PowerExchange Listener node.
Enter 0 to specify an unlimited connection pool size.
Default is 15.
- Maximum Idle Time
The number of seconds that a connection instance that exceeds the minimum number of connection instances can remain idle before the connection pool drops it. The connection pool ignores the idle time when the connection instance does not exceed the minimum number of idle connection instances.
If multiple database connections are associated with a PowerExchange Listener, PowerExchange calculates the arithmetic mean of the non-zero values for each database connection to determine the maximum idle time for connections to the same Listener.
Default is 120.
Tip: Assign the same maximum idle time to each database connection.