Restrictions and behavior when configuring max_coordagents and max_connections

The Version 9.5 default for the max_coordagents and max_connections parameters will be AUTOMATIC, with max_coordagents set to 200 and max_connections set to -1 (that is, set to the value of max_coordagents). These settings set Concentrator to OFF.

While configuring max_coordagents or max_connections online, there will be some restrictions and behavior to be aware of:
  • If the value of max_coordagents is increased, the setting takes effect immediately and new requests will be allowed to create new coordinating agents. If the value is decreased, the number of coordinating agents will not be reduced immediately. Rather, the number of coordinating agents will no longer increase, and existing coordinating agents might terminate after finishing their current set of work, in order to reduce the overall number of coordinating agents. New requests for work that require a coordinating agent are not serviced until the total number of coordinating agents is less than the new value and a coordinating agent becomes free.
  • If the value for max_connections is increased, the setting takes effect immediately and new connections previously blocked because of this parameter will be allowed. If the value is decreased, the database manager will not actively terminate existing connections; instead, new connections will not be allowed until enough of the existing connections are terminated so that the value is less than the new maximum.
  • If max_connections is set to -1 (default), then the maximum number of connections allowed is the same as max_coordagents, and when max_coordagents is updated offline or online; the maximum number of connections allowed will be updated as well.

While changing the value of max_coordagents or max_connections online, you cannot change it such that connection Concentrator will be turned either ON, if it's off, or OFF, if it's ON. For example, if at START DBM time max_coordagents is less than max_connections (Concentrator is ON), then all updates done online to these two parameters must maintain the relationship max_coordagents < max_connections. Similarly, if at START DBM time, max_coordagents is greater than or equal to max_connections (Concentrator is OFF), then all updates done online must maintain this relationship.

When you perform this type of update online, the database manager does not fail the operation, instead it defers the update. The warning SQL1362W message is returned, similar to any case when updating the database manager configuration parameters where IMMEDIATE is specified, but is not possible.

When setting max_coordagents or max_connections to AUTOMATIC, the following behavior can be expected:
  • Both of these parameters can be configured with a starting value and an AUTOMATIC setting. For example, the following command associates a value of 200 and AUTOMATIC to the max_coordagents parameter:
        UPDATE DBM CONFIG USING max_coordagents 200 AUTOMATIC
    These parameters will always have a value associated with them, either the value set as default, or some value that you specified. If only AUTOMATIC is specified when updating either parameter, that is, no value is specified, and the parameter previously had a value associated with it, that value would remain. Only the AUTOMATIC setting would be affected.
    Note: When Concentrator is ON, the values assigned to these two configuration parameters are important even when the parameters are set to AUTOMATIC.
  • If both parameters are set to AUTOMATIC, the database manager allows the number of connections and coordinating agents to increase as needed to suit the workload. However, the following caveats apply:
    1. When Concentrator is OFF, the database manager maintains a one-to-one ratio: for every connection there will be only one coordinating agent.
    2. When Concentrator is ON, the database manager tries to maintain the ratio of coordinating agents to connections set by the values in the parameters.
      • The approach used to maintain the ratio is designed to be unintrusive and does not guarantee the ratio will be maintained perfectly. New connections are always allowed in this scenario, though they might have to wait for an available coordinating agent. New coordinating agents will be created as needed to maintain the ratio. As connections are terminated, the database manager might also terminate coordinating agents to maintain the ratio
      • The database manager will not reduce the ratio that you set. The initial values of max_coordagents and max_connections that you set are considered a lower bound.
  • The current and delayed values of both these parameters can be displayed through various means, such as CLP or APIs. The values displayed will always be the values set by the user. For example, if the following command were issued, and then 30 concurrent connections performing work on the instance were started, the displayed values for max_connections and max_coordagents will still be 20, AUTOMATIC:
        UPDATE DBM CFG USING max_connections 20 AUTOMATIC, 
          max_coordagents 20 AUTOMATIC
    To determine the real number of connections and coordinating agents currently running monitor elements, you can also use the Health Monitor.
  • If max_connections is set to AUTOMATIC with a value greater than max_coordagents (so that Concentrator is ON), and max_coordagents is not set to AUTOMATIC, then the database manager allows an unlimited number of connections that will use only a limited number of coordinating agents.
    Note: Connections might have to wait for available coordinating agents.
The use of the AUTOMATIC option for the max_coordagents and max_connections configuration parameters is only valid in the following two scenarios:
  1. Both parameters are set to AUTOMATIC
  2. Concentrator is enabled with max_connections set to AUTOMATIC, while max_coordagents is not.
All other configurations using AUTOMATIC for these parameters will be blocked and will return SQL6112N, with a reason code that explains the valid settings of AUTOMATIC for these two parameters.