WCS KEYS and SUBKEYS

Posted by Hariharan Vadivelu on
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.



7 comments:

  1. Hi,Hari.
    I have a question.

    From other System(not WCS) ,you can call function
    'com.ibm.commerce.key.ECKeyManager.singleton().GetNextKey ()' of WCS ?

    thanks

    ReplyDelete
    Replies
    1. To my knowledge as it requires WCS runtime, you can probably wrap it into a WCS command and expose as a service to other systems.

      Delete
  2. Hi Hari,
    My question is reg ORDERS_ID series which I have changed by updating COUNTER and LOWERBOUND columns in KEYS table. I am able to get the new order-id but the problem is some orders are being generated with old series as well. I am sure all server instances in the cluster are restarted after this change. The orders are also not saved orders (in P state) as we delete such orders which are one day old. Do you have any idea from where the old order-id is being picked up as KEYS table has all new values.

    Thankyou in advance,
    Raghu

    ReplyDelete
  3. Hi Hari,

    I want to update the transaction information which is in the table PPCPAYTRAN ( I want to update reference number and tracking Id columns), can you share how to do it.. I couldn't find any access bean to do that.

    Thanks in advance.

    ReplyDelete
  4. Thanks for sharing this Information, Got to learn new things from your Blog on SAP SF.SAP SF

    ReplyDelete
  5. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in TECHNOLOGY , kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on TECHNOLOGY. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Saurabh Srivastava
    MaxMunus
    E-mail: saurabh@maxmunus.com
    Skype id: saurabhmaxmunus
    Ph:+91 8553576305 / 080 - 41103383
    http://www.maxmunus.com/


    ReplyDelete