In the pd_lck_pool_partition operand (or pd_fes_lck_pool_partition operand in the case of a front-end server), specify a lock pool partition count. In this way, you can distribute the locking process.

For details about the pd_lck_pool_partition and pd_fes_lck_pool_partition operands, see the manual HiRDB Version 9 System Definition.

The following figure shows the procedure for determining the value to specify.

Figure 9-6 Procedure for determining the value to specify for the pd_lck_pool_partition operand

(a) Initial configuration

Omit the pd_lck_pool_partition operand (for front-end servers, omit the pd_fes_lck_pool_partition operand).

Tuning the lock pool partition count after the system is initially built changes the amount of shared memory used. Therefore, if there is a possibility that tuning will occur, we recommend that you calculate the shared memory usage beforehand and allocate the necessary physical memory, and set up OS parameters.

(b) Tuning

To tune the lock pool partition count, the following information is required:

• SQL execution time (which is output to the SQL trace)
• Inter-thread lock wait count for lock pool partitions of each server (which is output to the statistical information related to system operations)
• Inter-thread lock wait count for lock pool partitions of each lock pool partition (displayed using the pdls -d lck -T command)

To acquire the tuning information, execute applications using the concurrent execution count and operating rate that assume actual job situations. Based on the acquired tuning information, gradually increase the lock pool partition count and set it to a value that minimizes the total SQL execution time for the entire job application.

However, in the following cases, return the lock pool partition count to the pre-tuning value and try to improve performance using some other method:

• Even though the lock pool partition count was increased, the inter-thread lock wait count for lock pool partitions does not decrease.
• Increasing the lock pool partition count decreased the inter-thread lock wait count for lock pool partitions, but SQL execution time does not become shorter.

When the locking process is distributed, the lock pool partition to be used for each locked resource is determined based on hash partitioning. Consequently, the lock pool partitions used might become unbalanced for some locked resources. When the lock pool partitions become unbalanced, the following situations might occur:

• There is no improvement in locking performance
  Execute the pdls -d lck -T command to check whether there are imbalances in the average inter-thread lock wait count for the lock pool partitions. If there are any imbalances, change the value for the pd_lck_pool_partition operand (pd_fes_lck_pool_partition operand in the case of a front-end server) to a prime number greater than the current specification value. This eliminates the imbalance caused by hash partitioning.
  
• The size of the lock pool partition is insufficient
  Execute the pdls -d lck -p -e command to check whether the maximum usage rate for the locked resource used for each lock pool partition varies greatly among partitions. If it varies greatly, change the value for the pd_lck_pool_partition operand (pd_fes_lck_pool_partition operand in the case of a front-end server) to a prime number greater than the current specification value. This eliminates the imbalance caused by hash partitioning. Alternatively, increase the value specified for the pd_lck_pool_size operand (or the pd_fes_lck_pool_size operand in the case of a front-end server). This increases the capacity of the table for managing locked resources.
  

  Reference note

  To change the lock pool size, use the pd_lck_pool_size operand. Since the lock pool is assigned to lock pool partitions by HiRDB, the size of the lock pool partition is proportional to the size of the lock pool. The lock pool partition usage rate is output to the statistical information related to system operations. When the usage rate is high, executing a transaction requiring more locked resources might lead to the size of the lock pool partition being insufficient, preventing transactions from being executed. In such a case, consider increasing the value specified for the pd_lck_pool_size operand. For details about the statistical information related to system operations, see the manual HiRDB Version 9 Command Reference.

The following table shows the advantages and disadvantages of distributing the locking process for each SQL.

Table 9-4 Advantages and disadvantages of distributing the locking process

SQL	When the lock pool partition count is increased		When the lock pool partition count is decreased
	Advantage	Disadvantage	Advantage	Disadvantage
SQL that does not commit or roll back when processing is completed (most data manipulation SQLs and some control SQLs)	SQL performance improves during concurrent execution.	Stand-alone performance of SQL deteriorates.	Stand-alone performance of SQL improves.	SQL performance deteriorates during concurrent execution.
SQL statements that commit or roll back simultaneously upon processing completion (COMMIT, ROLLBACK, definition SQL, PURGE TABLE, and so on)	--	SQL performance deteriorates during concurrent execution.	SQL performance improves during concurrent execution.	--

Legend:

--: Not applicable

• Since distributing the locking process adds management information used for locking for each increase in the lock pool partition count, the size of the shared memory for unit controllers increases. For the formula for calculating the size of the shared memory used by unit controllers, see the HiRDB Version 9 Installation and Design Guide. When you increase the lock pool partition count, change the system configuration as needed so that the size of the shared memory used by unit controllers does not increase in such a way as to cause a shortage in the physical memory and OS parameter specification values.
• Even when the inter-thread lock wait count for lock pool partitions is 0 or close to 0, inter-thread lock wait might have occurred in some cases. In such a case, performance might not improve even if you take the steps described in (1)(b) Tuning. If performance does not improve, take the following steps:
  Reduce the number of UAPs or utilities that are executed concurrently.
  Revise the job or HiRDB system definition to reduce the lock request count.