IBM Support

IT09137: WRONG RESULT FROM STATEMENT WITH TWO OR MORE OLAP FUNCTIONS WITH COMPATIBLE PARTITION-BY CLAUSES WITH ONE EQUATING TO CONSTANTS

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • This problem only occurs in a DPF or SMP parallelism enabled
    environment.
    
    You might encounter an incorrect result set for a SQL statement
    that contains two or more OLAP functions with compatible
    partition-by clauses wherein at least one OLAP function contains
    a partition-by clause where all columns equate to constant
    values. A column can equate to a constant value by specifying
    the constant values explicitly in the partition by clause or the
    columns in the partition by clause are referenced in equality
    predicates that equate to constants.
    
    The following example specifies the constant value explicitly
    SELECT ... SUM(X) OVER (PARTITION BY 1 ...) ...
    
    and the following example references the partition by column in
    an equality predicate
    SELECT ... SUM(X) OVER (PARTITION BY Y ...) ... WHERE Y = 1 ...
    

Local fix

  • You can rewrite your query to move the OLAP function with
    constant partition-by clause into a nested table expression.
    For example, if you have two compatible OLAP functions in a
    query as follows:
    
    SELECT SUM(X) OVER (PARTITION BY A), SUM(Y) OVER (PARTITION BY
    A,B) FROM T1 WHERE T1.A=1
    
    then you can rewrite it as a nested table expression to avoid
    the incorrect result:
    
    SELECT SUMX, SUM(Y) OVER (PARTITION BY A,B) FROM (SELECT A,B,Y,
    SUM(X) OVER (PARTITION BY A) SUMX FROM T1 WHERE A=1) AS TMP
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * DPF or SMP parallelism enabled environments                  *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 10.1.0.5.                             *
    ****************************************************************
    

Problem conclusion

  • The problem is first fixed in DB2 version 10.1.0.5.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT09137

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2015-05-27

  • Closed date

    2015-05-27

  • Last modified date

    2015-07-10

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

    IT05825

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA10 PSN

       UP

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1"}]

Document Information

Modified date:
25 September 2021