Summary

Running PRPC 6.2 SP1 on a DB2 9 for z/OS database with the PR_SYS_LOCKS table PSYSLOCK table space defined with LOCKSIZE ROW and MAXROWS=1 boosts the transaction throughput on the PR_SYS_LOCKS table significantly. However, DB2 10 for z/OS does not show the same performance boost with that default LOCKSIZE setting because IBM improved the row locking performance. For DB2 10 for z/OS, specifying LOCKSIZE ROW with MAXROWS=255 is most effective.

Explanation

In the PRPC-provided DB2 for z/OS schema generation scripts, the PSYSLOCK table space for database table PR_SYS_LOCKS is defined with the clauses LOCKSIZE ROW and MAXROWS=1.

• LOCKSIZE - Specifies the size of locks used within the table space and, in some cases, also the threshold at which lock escalation occurs.
• MAXROWS - Specifies the maximum number of rows that DB2 will consider placing on each data page. This value is considered for Insert operations, LOAD, and REORG.​
  • The integer can range from 1 through 255. 
  • MAXROWS=1 means that every row occupies a whole physical page (typically used with LOCKSIZE PAGE or LOCKSIZE ANY to create the effect of row-level locking).
  • If you do not specify MAXROWS, the default number of rows is 255.

When rows are archived (deleted), the physical page is still allocated until REORG TABLE is run. This results in unconstrained growth in physical pages. This also explains why REORG TABLE reduces the footprint to an optimal amount of physically used pages.

In tested environments running DB2 10 for z/OS with the PSYSLOCK table space defined as LOCKSIZE ROW with MAXROWS=1, a SELECT query on the PR_SYS_LOCKS table returned 950,000 getpages for a table of 200+ rows. The Suggested Approach section explains how to alleviate this performance problem.

Suggested Approach

Testing has confirmed the following for the table space definition of PSYSLOCK for table PR_SYS_LOCKS:

• On a DB2 9 for z/OS system, when MAXROWS=1, a big boost in the throughput of transactions on the PR_SYS_LOCKS table is achieved.
• On a DB2 10 for z/OS system, with IBM's improvements in row locking performance, using LOCKSIZE ROW with MAXROWS=255 is most effective.
• On a DB2 10 for z/OS system, specifying both settings LOCKSIZE ROW and MAXROWS=1 is redundant and could potentially cause contention and deadlock issues.

To improve database performance for PRPC 6.2 SP1 with DB2 10 for z/OS:

1. Alter the table space definition of PSYSLOCK to LOCKSIZE ROW and MAXROWS=255.
2. Run REORG TABLE on the database table PR_SYS_LOCKS.

Additional Information

Working with the PegaRULES database

References

• DB2 10 for z/OS, Specifying the size of locks for a table space
• DB2 10 for z/OS, Improving concurrency
• DB2 10 for z/OS, Designing databases for concurrency