Using CURRENT MEMBER default value in a Db2 pureScale environment to improve contention issues

In a Db2 pureScale environment, you can set the default value for a column to the CURRENT MEMBER special register. This member information can then be used to partition a table or an index, and therefore reduce database contention.

The following scenarios outline some of the situations where creating a new index using a CURRENT MEMBER column improves database contention issues. Once this new index is created, the Db2 pureScale cluster can make use of the member number information to reduce the amount of active sharing between members when referencing the table index. This resource reduction can improve the speed and overall performance of the Db2 pureScale environment.

Inserts with unique and increasing sequence values or timestamps

One row in the table can be used to store an ever-increasing value sequence (for example, purchase order numbers). These values can be generated automatically as an identity column, or programmatically through an external application. However, when these inserts occur with a significant frequency, contention issues may result, slowing down user queries.

To improve query performance, you can create a new CURRENT MEMBER column, and then index that data in conjunction with the column that stores the sequence.

Add the new column to store the member number:

ALTER TABLE ordernumber 
  ADD COLUMN curmem SMALLINT DEFAULT CURRENT MEMBER IMPLICITLY HIDDEN

Create (or drop and re-create) the index on the sequence column (seqnumber in this example), adding the new column to the index:
```
CREATE INDEX seqindex 
  ON ordernumber (curmem, seqnumber)
```

A similar approach can be taken with database designs where the sequence is a series of timestamp values. The index for the timestamp column would use the PAGE SPLIT HIGH option, and include the new CURRENT MEMBER column as well.

Indexes with only a few unique values

Some tables may have columns with only a few different values, relative to the number of rows in the table. This is another case where adding a CURRENT MEMBER column can be beneficial.

To improve the database performance in this case:

Add the new column to store the member number:

ALTER TABLE customerinfo 
  ADD COLUMN curmem SMALLINT DEFAULT CURRENT MEMBER IMPLICITLY HIDDEN

Create (or drop and re-create) the index on the one or more columns with the few different values (for example, zipcode and country):
```
CREATE INDEX lowvaridx 
  ON customerinfo (zipcode, country, curmem)
```

In all these cases, index compression will likely reduce the size of the index on the new CURRENT MEMBER values.

To reduce the impact on existing SQL statements, use the IMPLICITY HIDDEN option in the CREATE TABLE or ALTER TABLE command to hide the new column. This hidden column option ensures that the member information is only available to explicit SQL statement references.