JR47685: INCORRECT WQL RESULTS WHEN QUERY CONTAINS SEARCH OVER 2 HIERARCHIES

APAR status

• Closed as program error.

Error description

• In IBM InfoSphere Master Data Management Collaboration Server
  Version 10.1.0, the Websphere Query Language (WQL) delivers zero
   result when the query is run over 2 hierarchies.
  
  The search query is run with different hierarchies using the
  following query string, but the result is empty even though it
  should return some items.
  
  
  Below is the WQL query string:
  
  //
  // Script InfoSphere MDM Collaboration Server
  //
  
  var exp = " SELECT item FROM catalog('ItemClassification')"
  + " WHERE item.category['Classification_Spec/code'] IN
  ('11111','22222')"
  + " AND item.category['Supplier_Spec/supplierName'] like
  '%MySupplier%'";
  
  out.writeln("exp: " + exp);
  
  var result;
  if((exp!=null) || (exp!=""))
  {
      var searchQuery = new SearchQuery(exp);
      if(null == searchQuery)
      {
          out.writeln("searchQuery est null!");
      }
      var res = searchQuery.execute();
      if(null == res)
      {
          out.writeln("res est null!");
      }
  
      result = res.size();
  
      if(res.size() == 0)
      {
          out.writeln("searchQuery res = 0");
      }
      else
      {
          while(res.next())
          {
              var item = res.getItem(1);
              var pk = item.getCtgItemPrimaryKey();
              out.writeln("pk: " + pk);
          }
      }
  }
  out.writeln("result: " + result);
  
  
  Steps to reproduce :
  
  1. create a catalog and assign this catalog to 2 hierarchies
  2. create some items and make sure these items are assigned to
  both hierarchies as well
  3. run the WQL script similar to the one above
  
  Result: <empty>
  
  
  Workaround:
  
  1. create the WQL as usual
  
  2. check the generated sqls from $TOP/logs/Appsvr/query.log and
  copy the last sql and make changes to that sql by adding similar
  lines like those in the "modified sql" below
  
  Generated sql :
  
  select distinct itm1.itm_id as c1
      from cab cab1, cab cab2, icm icm1, itm itm1
     where itm1.itm_container_id = 22402
       and itm1.itm_version_id <= 999999999
       and itm1.itm_next_version_id >= 999999999
       and cab1.cab_value_string in ('11111', '22222')
       and cab2.cab_value_string like '%MySupplier%'
       and icm1.icm_item_id = itm1.itm_id
       and icm1.icm_version_id <= 999999999
       and icm1.icm_next_version_id >= 999999999
       and cab1.cab_node_id = 33022
       and cab1.cab_category_id = icm1.icm_category_id
       and cab1.cab_version_id <= 999999999
       and cab1.cab_next_version_id >= 999999999
       and cab2.cab_node_id = 33014
       and cab2.cab_category_id = icm1.icm_category_id
       and cab2.cab_version_id <= 999999999
       and cab2.cab_next_version_id >= 999999999
  
  Modified sql:
  
  select itm_primary_key from itm where itm_id in
  (
  select distinct itm1.itm_id as c1
  from cab cab1, cab cab2, icm icm1, icm icm2, itm itm1
  where itm1.itm_container_id = 22402
  and itm1.itm_version_id <= 999999999
  and itm1.itm_next_version_id >= 999999999
  and cab1.cab_value_string in ('11111', '22222')
  and cab2.cab_value_string = '%MySupplier%'
  and icm1.icm_item_id = itm1.itm_id
  and icm1.icm_version_id <= 999999999
  and icm1.icm_next_version_id >= 999999999
  and icm2.icm_item_id = itm1.itm_id
  and icm2.icm_version_id <= 999999999
  and icm2.icm_next_version_id >= 999999999
  and cab1.cab_node_id = 33022
  and cab1.cab_category_id = icm1.icm_category_id
  and cab1.cab_version_id <= 999999999
  and cab1.cab_next_version_id >= 999999999
  and cab2.cab_node_id = 33014
  and cab2.cab_category_id = icm2.icm_category_id
  and cab2.cab_version_id <= 999999999
  and cab2.cab_next_version_id >= 999999999
  );
  
  3. copy the modified sql and run this under MDM GUI -> System
  Administrator -> DB Admin
  
  
  Result: correct
  

Local fix

• N/A (only workaround that is cumbersome)
  

Problem summary

• IBM recommends that customers facing the issue reported by this
  APAR apply  Master Data Management Collaboration Server version
  10.1.0-FP008 to gain resolution to this problem.
  

Problem conclusion

• A fix for this problem has been included in Master Data
  Management Collaboration Server version 10.1.0-Fp008
  

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

  MDM SERVER FOR

• Fixed component ID

  5724V5100

Applicable component levels

• RA10 PSY

     UP