PI97154: REPORT GIVES DIFFERENT TOTAL WHEN CHANGED FROM CQM TO DQM

APAR status

• Closed as Permanent restriction.

Error description

• The report list contains a calculated data item in one column.
  At the bottom, the total is displayed - this total is calculated
  in a different query and put into a singleton. When the Package
  for the report is changed from CQM to DQM the total that is
  being given is different.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Users                                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  

Problem conclusion

• Here is the pattern of the generated sql:
  
  SELECT DISTINCT
      SUM(TQ0_Query1.Revenue - SQ1_Query1.Test_Total) AS
  Test_Total
  FROM
      (
      SELECT
          Branch_Country__alias_5.COUNTRY_EN AS Branch_Country,
          SUM(Sales_Fact.Margin) AS Test_Total FROM <xxxx>
      GROUP BY
          Branch_Country__alias_5.COUNTRY_EN
      ) SQ1_Query1,
      (
      SELECT
          Sales_Fact.Revenue AS Revenue,
          Branch_Country__alias_.COUNTRY_EN AS Branch_Country,
          Sales_Fact.Margin AS Margin
      FROM <xxxx>
      ) TQ0_Query1
  WHERE
      TQ0_Query1.Branch_Country IS NOT DISTINCT FROM
  SQ1_Query1.Branch_Country
  
  
  Notice the top calculation between TQ0 and SQ1
      SQ1_Query1.Test_Total is an aggregated total by country.  In
  the gosales example, 20 rows are returned.
      TQ0_Query1.Revenue  is a detailed value.  The total number
  of rows returned by TQ0 is 446023.
  
      TQ0 and SQ1 are joined by country.  Each SQ1 row will join
  to thousands of TQ0 rows.
  
      The resulting dataType of SQ1_Query1.Test_Total is double.
      The resulting dataType of TQ0_Query1.Revenue is decimal.
  
  When the whole sql is pushed to the database, for both CQ and
  DQ, the results are identical.
  
  To test this, you can set the useLocalCache=no on Query1 and
  re-execute the DQM report.
  
      * * *
  
  The localCachePolicy governor in the DQ model is set to "Lowest
  summary sub-query".
  The impact of this setting is that SQ1_query is executed by
  itself and its results are cached for further usage.  Then TQ0
  is executed.  Finally, the top projection and the TQ0-SQ1 join
  are processed locally.  So for the DQM execution of the report,
  a significant amount of processing is done locally which will
  lead into rounding differences when compared to the whole sql
  being pushed to the database.
  
  In addition to that, when the whole sql is pushed to the
  database, it is up to the database vendor to decide how to
  process and reconcile the dataTypes (remember the top projection
  where we are diff'ing a double with a decimal, then aggregating
  the results).  Therefore, the exact same sql, pushed to a
  different db vendor, could also lead to slightly different
  results.
  
      * * *
  
  Conclusion:
  It is impossible to perfectly align the local processing of DQM
  with what is being done by a database vendor, especially when
  having to reconcile dataTypes.
  To work-around this issue, the model can be modified to change
  the localCachePolicy to either "Explicitely per query" or "Query
  referenced by layout".  By doing so, the whole query, including
  TQ0 and SQ1, is pushed to the database.  This will make the
  results consistent with the CQM execution which is also pushing
  the whole sql to the database.
  
  Alternatively, the useLocalCache property can be modified in the
  report for Query1.
  Select Query1 to get the properties.  Then set the useLocalCache
  to no (property panel on the right).
  

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

Applicable component levels