Wednesday, July 31, 2013

Keys table EJB | script to correct counter values

When you do migration from one version of commerce to the next or any other scenarios or due to data load scenarios some times the keys table counters are messed and causes lot of errors in application server logs when using those corresponding EJB's.

A lot of discretion is warranted when doing stuff to the keys table e.g. counter should be greater than the lower bound and lesser than the upper bound
You can generate the SQL statements from the script below and run them against the corresponding commerce schema where the issue was found.

SQL Script to see which tables might need to be fixed:

select 'select max(' || columnname||')+1 - (select counter from keys where tablename = '''|| tablename||'''), '''|| tablename || ''' from ' || tablename from keys

 SQL script to do the actual updates:
select concat(concat(concat('update keys set counter=',concat(concat(
concat(concat('(select max(',columnname),')+1 from ' ),tablename),') where tablename=''')),tablename),''' and counter <= '|| '(select max('||columnname||') from '|| tablename||')' ||';') from keys

1 comment:

  1. Wonderful post! Youve made some very astute observations and I am thankful for the the effort you have put into your writing. Its clear that you know what you are talking about. I am looking forward to reading more of your sites content.
    Hadoop training