JR56644: Syntax error in wcs.data.sql introduced by JR56288

APAR status

• Closed as program error.

Error description

• There is a syntax error in wcs.data.sql introduced by JR56288
  which causes creating instance to fail.
  

Local fix

• 1. Before you run instance creation, open the following file
  <WC_InstallDir>/schema/Oracle/wcs.data.sql
  
  2. Replace
  
  INSERT INTO dmelementnvp (dmelement_id, name, value)
  select i,n,v
  from (SELECT dmelement_id, 'socialActivityType', '*' FROM
  dmelement WHERE dmeletemplate_id IN (160, 215) UNION ALL SELECT
  dmelement_id, 'comparison', 'any' FROM dmelement WHERE
  dmeletemplate_id IN (160, 215)) s (i,n,v)
  where not exists (select 1 from dmelementnvp where dmelement_id
  = i and name = n and value = v);
  
  
  with
  
  INSERT INTO dmelementnvp (dmelement_id, name, value)
  select i,n,v from (SELECT dmelement_id i, 'socialActivityType'
  n, '*' v FROM dmelement WHERE dmeletemplate_id IN (160, 215)
                     UNION ALL
                     SELECT dmelement_id i , 'comparison' n, 'any'
   v FROM dmelement WHERE dmeletemplate_id IN (160, 215)
                    ) s
  where not exists (select 1 from dmelementnvp where dmelement_id
  = i and name = n and value = v);
  

Problem summary

• USERS AFFECTED:
  WebSphere Commerce V8 administrator who is running instance
  creation on Version 8.0.1.6.
  
  PROBLEM ABSTRACT:
  Syntax error in wcs.data.sql introduced by JR56288
  
  BUSINESS IMPACT:
  Instance creation will fail on database configuration if the
  database is Oracle.
  
  RECOMMENDATION:
  

Problem conclusion

• Updated the <WC_InstallDir>/schema/<dbType>/wcs.data.sql file to
   replace the following INSERT statement:
  
  INSERT INTO dmelementnvp (dmelement_id, name, value)
  select i,n,v
  from (SELECT dmelement_id, 'socialActivityType', '*' FROM
  dmelement WHERE dmeletemplate_id IN (160, 215) UNION ALL SELECT
  dmelement_id, 'comparison', 'any' FROM dmelement WHERE
  dmeletemplate_id IN (160, 215)) s (i,n,v)
  where not exists (select 1 from dmelementnvp where dmelement_id
  = i and name = n and value = v);
  
  With the following INSERT statement:
  
  INSERT INTO dmelementnvp (dmelement_id, name, value)
  select i,n,v from (SELECT dmelement_id i, 'socialActivityType'
  n, '*' v FROM dmelement WHERE dmeletemplate_id IN (160, 215)
                     UNION ALL
                     SELECT dmelement_id i , 'comparison' n, 'any'
   v FROM dmelement WHERE dmeletemplate_id IN (160, 215)
                    ) s
  where not exists (select 1 from dmelementnvp where dmelement_id
  = i and name = n and value = v);
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  WC BUS EDITION

• Fixed component ID

  5724I3800

Applicable component levels

• R700 PSY

     UP