DB2 FOR LUW / DB2FORLUW / B10 - DB2

The query performance can be sub-optimal when it meets the
following condition;

1) It's a simple SELECT
2) It has at lest 2 MIN or MAX aggregation.
3) The MAX is the last head expression
4) The alias is used instead of the real table name.

For instance, to reproduce;

CREATE TABLE TEST  ("TEST_ID" DECIMAL(14,0) ,"TEST_DT" DATE );
CREATE ALIAS TEST_CURRENT FOR TABLE TEST;

SELECT MAX(TEST_DT), MIN(TEST_DT) FROM TEST_CURRENT WITH UR
SELECT MIN(TEST_DT), MAX(TEST_DT) FROM TEST_CURRENT WITH UR" -
Slower than above

When you run the queries above and get an execution plan, we
will find that "IS NOT NULL" is added to MAX in the first query
which is a good case, but not in the second query.

****************************************************************
* USERS AFFECTED:                                              *
* All Db2 Users                                                *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 11.1.4.5 or later                             *
****************************************************************

Rewrite the query so that the MAX is first or use the table name
instead of the Alias.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

29.01.2019
01.04.2020
01.04.2020