WCS KEYS and SUBKEYS

WCS uses OOB KEYS and SUBKEYS tables to maintain primary key counters for both custom and OOB tables, the schema and purpose of both these tables seems to be identical, then why do we need two tables for similar purpose?
Although undocumented, it turns out that any non J2EE transactions like the dataload or any other external tools should be using SUBKEYS table first before checking KEYS table and J2EE transactions such as EJB's should be looking at KEYS table first before checking SUBKEYS, I believe the idea here is to reduce overall impact in terms of database performance on KEYS table.

1. During WC instance startup following query is executed and information cached.

select tablename, prefetchsize from keys
2. By design every WCS EJB makes use of atleast two different signatures of ejbCreate() method, one of these methods makes use of keys which forms the unique index for a table, once invoked it spawns a new transaction to retrieve the new KEY.

3. Spawn a new Transaction Manager by calling TransactionManager.begin(), this is the case of a nested transaction within the main Web Container thread TransactionManager.

4. Queries the KEYS table where KEYS.tablename='<TABLE NAME>', WCS uses OOB com.ibm.commerce.key.ECKeyManager Singleton class for this purpose.

5. KEYS are always allocated in blocks to the requesting JVM, hence it calculates a new block of keys by Computing the new counter by first incrementing KEYS.COUNTER by 1 and last value of the block is new COUNTER value + value in the KEYS.PREFETCHSIZE.

E.g. Following is an example of KEY fetch for ORDERITEMS table

ECKey.getNextValue() is executed.
SELECT T1.KEYS_ID, T1.TABLENAME, T1.LOWERBOUND, T1.UPPERBOUND, T1.COUNTER, T1.PREFETCHSIZE, T1.COLUMNNAME, T1.OPTCOUNTER FROM KEYS  T1 WHERE T1.tablename = 'orderitems' FOR UPDATE

NOTE: The row is locked, hence any similar  requests from WCS instance within the cluster will be blocked until this transaction is complete.

SELECT T1.KEYS_ID, T1.TABLENAME, T1.LOWERBOUND, T1.UPPERBOUND, T1.COUNTER, T1.PREFETCHSIZE, T1.COLUMNNAME, T1.OPTCOUNTER FROM KEYS  T1 WHERE T1.KEYS_ID =-39

In this case -39 is the KEYS_ID for ORDERITEMS table.

UPDATE KEYS  SET TABLENAME = 'orderitems', LOWERBOUND = 0, UPPERBOUND = 9223372036849999872, COUNTER = 185005, PREFETCHSIZE = 1000, COLUMNNAME = 'orderitems_id', OPTCOUNTER = 38 WHERE KEYS_ID = -39 AND OPTCOUNTER = ?

6. In step 3 if value of new counter exceeds the value of KEYS.UPPERBOUND then the value will be wrapped back to KEYS.LOWERBOUND

7. End the transaction TransactionManager.commit()

ECKeyManager repeats steps 3 through 7 in case it needs more KEYS for a particular table , in most cases by tuning the PREFETCHSIZE we can reduce number of DB round trips to KEYS table, this will reduce two SELECT and one UPDATE statement on KEYS table, this should be considered as an important performance tuning parameter in large scale WCS clusters.