IBM Books

Administration Guide


Parallel

The following groups of parameters provide information about parallel operations and partitioned database environments:

Communications

The following parameters provide information about communications in the partitioned databaseenvironment:

Connection Elapse Time (conn_elapse)

Configuration Type
Database manager

Applies To
Partitioned Database Server with local and remote clients

Parameter Type
Configurable

Default [Range]
10 [0-100]

Unit of Measure
Seconds

Related Parameters
Node Connection Retries (max_connretries)

This parameter specifies the number of seconds within which a TCP/IP connection is to be established between two database partition servers. If the attempt completes within the time specified by this parameter, communications are established. If it fails, another attempt is made to establish communications. If the connection is attempted the number of times specified by the max_connretries parameter and always times out, an error is issued.

Number of FCM Message Anchors (fcm_num_anchors)

Configuration Type
Database manager

Applies To

Parameter Type
Configurable

Default [Range]
-1 [-1, 128-fcm_num_rqb]

On non-partitioned database systems, intra_parallel parameter must be active before this parameter can be used.

Related Parameters

This parameter specifies the number of FCM message anchors. Agents use the message anchors to send messages among themselves. The default (-1) indicates 75 percent of the value specified for fcm_num_rqb.

Number of FCM Buffers (fcm_num_buffers)

Configuration Type
Database manager

Applies To

Parameter Type
Configurable

Default [Range]
512, 1 024, or 4 096 [128-65 300]

On single-partition database systems, the intra_parallel parameter must be active before this parameter can be used.

This parameter specifies the number of 4 KB buffers that are used for internal communications (messages) both among and within the database servers in a partitioned database environment.

Refer to "Enable FCM Communications" in the Administration Guide, Design and Implementation for more information on FCM.

If you have multiple logical nodes on a processor, you may find it necessary to increase the value of this parameter. You may also find it necessary to increase the value of this parameter if you run out of message buffers because of the number of users on the system, the number of database partition servers on the system, or the complexity of the applications.

If you are using multiple logical nodes, on non-AIX systems, one pool of fcm_num_buffers buffers is shared by all the multiple logical nodes on the same machine, while on AIX:

Recommendation for existing Parallel Edition customers on AIX: If you are using multiple logical nodes, the value of fcm_num_buffers you used in Parallel Edition Version 1.2 may now result in significantly more storage being used per machine. For example, a four-node multiple logical node configuration may end up with four times as many FCM buffers as before.

Re-examine the value you are using; consider how many FCM buffers in total will be allocated on the machine (or machines) where the multiple logical nodes reside. You may want to change fcm_num_buffers to account for the behavior described above.

Number of FCM Connection Entries (fcm_num_connect)

Configuration Type
Database manager

Applies To

Parameter Type
Configurable

Default [Range]
-1 [-1, 128-fcm_num_rqb]

On non-partitioned database systems, the intra_parallel parameter must be active before this parameter can be used.

Related Parameters
Number of FCM Request Blocks (fcm_num_rqb)

This parameter specifies the number of FCM connection entries. Agents use connection entries to pass data among themselves. The default (-1) indicates 75 percent of the value specified for fcm_num_rqb.

Number of FCM Request Blocks (fcm_num_rqb)

Configuration Type
Database manager

Applies To

Parameter Type
Configurable

Default [Range]
256, 512, or 2 048 [128-120 000]

On non-partitioned database systems, the intra_parallel parameter must be active before this parameter can be used.

This parameter specifies the number of FCM request blocks. Request blocks are the media through which information is passed between the FCM daemon and an agent, or between agents.

The requirement for request blocks will vary according to the number of users on the system, the number of database partition servers in the system, and the complexity of queries that are run. Initially, start with the default number, and use the results from the Database System Monitor when fine tuning this parameter.

Node Connection Retries (max_connretries)

Configuration Type
Database manager

Applies To
Partitioned Database Server with local and remote clients

Parameter Type
Configurable

Default [Range]
5 [0-100]

Related Parameters
Connection Elapse Time (conn_elapse)

If the attempt to establish communication between two database partition servers fails (for example, the value specified by the conn_elapse parameter is reached), max_connretries specifies the number of connection retries that can be made to a database partition server. If the value specified for this parameter is exceeded, an error is returned.

Maximum Time Difference Among Nodes (max_time_diff)

Configuration Type
Database manager

Applies To
Partitioned Database Server with local and remote clients

Parameter Type
Configurable

Default [Range]
60 [1-1 440]

Unit of Measure
Minutes

Each database partition server has its own system clock. This parameter specifies the maximum time difference, in minutes, that is permitted among the database partition servers listed in the node configuration file.

If two or more database partition servers are associated with a transaction and their clocks are not synchronized to within the time specified by this parameter, the transaction is rejected and a warning or an error message is logged in the db2diag.log file. (The transaction is rejected only if data modification is associated with it.)

DB2 Universal Database Enterprise - Extended Edition uses Coordinated Universal Time, (UTC) so different time zones are not a consideration when you set this parameter. The Coordinated Universal Time is the same as Greenwich Mean Time.

Start and Stop Timeout (start_stop_time)

Configuration Type
Database manager

Applies To
Partitioned Database Server with local and remote clients

Parameter Type
Configurable

Default [Range]
10 [1-1 440]

Unit of Measure
Minutes

This parameter is applicable in a partitioned database environment only. It specifies the time, in minutes, within which all database partition servers must respond to a DB2START or a DB2STOP command. It is also used as the timeout value during an ADDNODE operation.

Database partition servers that do not respond to a DB2START command within the specified time send a message to the db2start error log in the log subdirectory of the sqllib subdirectory of the home directory for the instance. You should issue a DB2STOP on these nodes before restarting them.

Database partition servers that do not respond to a DB2STOP command within the specified time send a message to the db2stop error log in the log subdirectory of the sqllib subdirectory of the home directory for the instance. You can either issue DB2STOP for each database partition server that does not respond, or for all of them. (Those that are already stopped will return stating that they are stopped.)

Parallel Processing

The following parameters provide information about parallel processing:

Maximum Query Degree of Parallelism (max_querydegree)

Configuration Type
Database manager

Applies To

Parameter Type
Configurable

Default [Range]
-1 (ANY) [ANY, 1-32 767] (ANY means system determined)

Related Parameters

This parameter specifies the maximum degree of intra-partition parallelism that is used for any SQL statement executing on this instance of the database manager. An SQL statement will not use more than this number of parallel operations within a partition when the statement is executed. The intra_parallel configuration parameter must be set to "YES" to enable the database partition to use intra-partition parallelism.

The default value for this configuration parameter is -1. This value means that the system uses the degree of parallelism determined by the optimizer; otherwise, the user-specified value is used.
Note:The degree of parallelism for an SQL statement can be specified at statement compilation time using the CURRENT DEGREE special register or the DEGREE bind option.

The maximum query degree of parallelism for an active application can be modified using the SET RUNTIME DEGREE command. The actual runtime degree used is the lower of:

An exception regarding the determination of the actual query degree of parallelism occurs when creating an index. In this case, if intra_parallel is "YES" and the table is large enough to benefit from the use of multiple processors, then creating an index uses the number of online processors (to a maximum of 6) plus one. There is no effect from the other parameter, bind option, or special register mentioned above.

Enable Intra-Partition Parallelism (intra_parallel)

Configuration Type
Database manager

Applies To

Parameter Type
Configurable

Default [Range]
NO (0) [SYSTEM (-1), NO (0), YES (1)]

A value of -1 causes the parameter value to be set to "YES" or "NO" based on the hardware on which the database manager is running.

Related Parameters
Maximum Query Degree of Parallelism (max_querydegree)

This parameter specifies whether the database manager can use intra-partition parallelism.

Some of the operations that can take advantage of parallel performance improvements when this parameter is "YES" include database queries and index creation.
Note:If you change this parameter value, packages may be rebound to the database. If this occurs, a performance degradation may occur during the rebinding.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]