MF64403 - LIC-DB-OTHER-INCORROUT Queries which use DECFLOAT columns fo

TYPE PROGRAM  REL  NUMBER   MIN/MAX  OPTION
---- -------- ---  -------  -------  ------
PRE  5770999  710  MF63405   00/00    0000
PRE  5770999  710  MF59776   00/00    0000
PRE  5770999  710  MF59785   00/00    0000
PRE  5770999  710  MF59786   00/00    0000
PRE  5770999  710  MF51275   00/00    0000
CO   5770999  710  MF63421   00/00    0000
CO   5770999  710  MF63231   00/00    0000
CO   5770999  710  MF62796   00/00    0000
CO   5770999  710  MF60758   00/00    0000
CO   5770999  710  MF59650   00/00    0000
CO   5770999  710  MF50843   00/00    0000
CO   5770999  710  MF47880   00/00    0000
DIST 5770SS1  710  SI54626   00/00    0000



NOTICE:
-------
Application of this PTF may disable or render ineffective programs that
use system memory addresses not generated by the IBM translator,
including programs that circumvent control technology designed to limit
interactive capacity to purchased levels.  This PTF may be a prerequisite
for future PTFs.  By applying this PTF you authorize and agree to the
foregoing.

This PTF is subject to the terms of the 'IBM License Agreement for Machine
Code', the terms of which were provided in a printed document that was
delivered with the machine.

SUBJECT TO ANY WARRANTIES WHICH CAN NOT BE EXCLUDED OR EXCEPT AS EXPLICITLY
AGREED TO IN THE APPLICABLE LICENSE AGREEMENT OR AN APPLICABLE SUPPORT
AGREEMENT, IBM MAKES NO WARRANTIES OR CONDITIONS EITHER EXPRESS OR IMPLIED,
INCLUDING BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OR CONDITIONS OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, AND NON INFRINGEMENT,
REGARDING THE PTF.





APAR Error Description / Circumvention

-----------------------------------------------
Queries which select and order on DECFLOAT columns may produce
an incorrect number of results and/or incorrect values. This can
occur when the optimizer chooses an Index Merge access plan to
implement the query. Visual Explain can be used to verify the
access plan used for a query.

CORRECTION FOR APAR MA46736 :
-----------------------------
When building a temporary indexed list to implement the Index
Merge access plan, the optimizer may incorrectly store and
project DECFLOAT data. This fix corrects the optimizer behavior
so that the data is stored correctly in the temporary indexed
list.

CIRCUMVENTION FOR APAR MA46736 :
--------------------------------
None.


Activation Instructions


None.




Special Instructions


None.


Default Instructions

THIS PTF CAN BE APPLIED IMMEDIATE OR DELAYED.



Supersedes

PTF/FIX NO(S).  APAR TITLE LINE
--------------  ------------------------------------------------------------
   MF64227      OSP-DB SELECT FOR UPDATE WITH RS UNSELECTED RECORDS ARE LOCK
   MF63749      LIC-DB-INCORROUT OLAP with ORDER BY of LOB
   MF63420      OSP-DB-OTHER-MSGCPI0999 XMLAGG QUERIES MAY RESULT IN LARGE A
   MF63232      LIC-DB-OTHER-PERFM COMPLEX QUERY IS BEING RE-OPTIMIZED UNEXP
   MF62797      LIC-DB-OTHER-F/DBPMTRAP-T/QQQOOODBOP-RC1720-MSGMCH3203 Outer
   MF62631      LIC-DB Internal Maintenance
   MF61275      LIC-DB-PERFM - EAO with Partitioned Tables
   MF60760      LIC-DB-PERFM SQL EVI Only Access query optimization techniqu
   MF60709      LIC-DB-OTHER-PERFM Slow query execution due to Index Distinc
   MF60699      LIC-DB-OTHER-UNPRED Internal Database Maintenance
   MF60632      LIC-DB-OTHER-PERFM Distinct query may use sub-optimal plan
   MF60570      OSP-DB-OTHER-F/DBPMFORMAT-T/QDBGETMQO-MSGMCH3203 A MSGMCH320
   MF60504      LIC-DB-OTHER-UNPRED Internal Database Maintenance
   MF60347      F/ASSERT-MSGMCH3203-MOD/QQQOOODBOP QUERY WITH AGGREGATE FAIL
   MF59914      OSP-OTHER-PERFM LONG OPTIMIZATION TIME DUE TO NUMEROUS UDFS/
   MF59789      LIC-DB-OTHER Internal maintenance
   MF59655      LIC-DB-UNPRED Ensure order of partitions in partitioned tabl
   MF59335      LIC-DB-PERFM Preload for DinDistinctScan
   MF59111      LIC-DB-PERFM Enable Pessimistic IO costing for Index Probe a
   MF58887      LIC-DB-OTHER Internal Maintenance
   MF58857      LIC-DB-OTHER-INCORROUT Queries involving field procedures ma
   MF58828      LIC-DB-INCORROUT Distinct of LOB field
   MF58721      LIC-DB-OTHER Internal Maintenance
   MF58467      OSP-DB-PERFM  AVOID PLAN CACHE DUMP TOLERATED BADD DUE TO CO
   MF58387      LIC-DB-MSGMCH3203 CONNECT BY when running SMP
   MF58012      LIC-DB-OTHER-PERFM PRELOAD OF QUERY DATA SOURCES CAUSES PERF
   MF57677      OSP-DB-OTHER-F/ASSERT-T/QQQOOODBOP-MSGMCH3203 SQL MCH3203 QQ
   MF56940      OSP-PERFM QUERY OPTIMIZATION TAKES TOO LONG WHEN MANY INDEXE
   MF56794      LIC-DB-OTHER-INCORROUT SQL QUERY USING WITH ROLLUP INCORROUT
   MF56746      LIC-DB-OTHER-PERFM Slow SQL query performance with field pro
   MF56473      OSP-DB-OTHER-INCORROUT WHEN SQL CUBE GROUPING SET IS RUN WIT
   MF56374      LIC-DB-OTHER-INCORROUT SPECIFYING NUMBER OF TASKS FOR SMP WI
   MF56237      LIC-DB-OTHER-T/QQQOOOINV-MSGMCH3203 failure when running gro
   MF55900      OSP-DB-Visual Explain diagram with disconnected icons
   MF55856      OSP-DB-OTHER-F/DBOPRELQDSPTL-T/QQQ-MSGMCH3601 SELECT USING M
   MF55770      OSP-DB-OTHER INCOROUT Connect Results Not Displayed Correctl
   MF55654      LIC-DB-OTHER-F/ASSERT-T/QQQOOODBOP-MSGMCH3203 COMPLEX SQL QU
   MF55642      LIC-DB-PERFM Optimizer choosing the wrong plan when SMP
   MF55499      LIC-DB-UNPRED Using LOBs as input to UDTF
   MF55470      LIC-DB-MSGMCH3203 Error with query running SMP *OPTIMIZE
   MF54962      OSP-PERFM QUERY RUNNING LONGER WHEN DATE RANGE GREATER THAN
   MF54962      LIC-DB-PERFM Table Probe IO Costing when secondary in a join
   MF54925      OSP-DB-OTHER INCORROUT OLAP with Partition by
   MF54331      PERFM-LIC FILTERING BY STATEMENT TEXT ON INAV 'SHOW STATEMEN
   MF54297      LIC-DB-PERFM Remove unnecessary Preload of QDS objects
   MF54022      LIC-DB-PERFM-OTHER Problems with costing Select/Omit indexes
   MF53513      LIC-DB-PERFM Bad plan
   MF53279      OSP-DB-OTHER-LOOP SQL WITH NESTED REPLACE EXPRESSIONS LOOPS,
   MF52953      OSP-DB-OTHER-F/ASSERT-T/QQQOOODBOP-MSGMCH3203 SELECT STATEME
   MF52862      LIC-DB-INCORROUT SQL SELECT WITH INNER JOIN RETURNS NO DATA
   MF52803      LIC-DB-OTHER-PERFM SMP IMPLEMENTATION IS TOO AGGRESSIVE
   MF52508      LIC-DB-INCORROUT-UNPRED Update with Subquery not creating fu
   MF52462      LIC-DB Connect by infrastructure
   MF52416      LIC-DB-PERFM Performance on query of MULTI-DS LFs
   MF52314      LIC-DB-PERFM FIX A PROBLEM IN COSTING RRN BITMAP PLANS
   MF52314      LIC-DB-OTHER-PERFM SQL JOIN SLOWER AFTER UPGRADE FROM R530 T
   MF52314      OSP-DB-OTHER-PERFM SQL RUNS LONGER WHEN HASHING IS EMPLOYED
   MF52308      LIC-DB-PERFM SQE AND UDF IN a VIEW CAUSING PERFORMANCE ISSUE
   MF52305      LIC-DB-PERFM Performance on query of MULTI-DS LFs
   MF52122      LIC-DB INDEX ADVISOR SUGGESTS AN INDEX OVER A 'NOTUSED' COLU
   MF52076      OSP-DB-OTHER-F/QSQRUN3-MSGSQL0527 when ALWCPYDTA(*NO)
   MF51897      LIC-DB-OTHER improve IO costing for in memory files
   MF51873      OSP-DB-OTHER-PERFM JOB PROCESSING LOB FIELD RAN SLOW, WITH H
   MF51873      LIC-DB-PERFM WITH LOB FIELD, JOB USES EXCESSIVE TEMP STORAGE
   MF51862      OSP-DB-OTHER-INCORROUT LIKE PREDICATE with SORTSEQ INDEX
   MF51579      LIC-DB-PERFM SQL Select with inner join on RRN performs poor
   MF51435      OSP-DB-OTHER Enable in memory files for indexes
   MF51400      OSP-DB-OTHER-LOOP CREATE FUNCTION LOOPS
   MF51306      OSP-DB Deflated tables cannot use SMP parallelism
   MF51186      OSP-DB Creation of SQL deflated table
   MF51115      OSP-OTHER-PERFM QUERY IS PERFORMING POORLY AND LOCKING MANY
   MF51045      LIC-DB-MSGMCH3203 JOIN Query using PTL Probe
   MF51045      LIC-DB-OTHER FINAL CALL not happening sometimes when running
   MF51045      LIC-DB-OTHER-PERFM SELECT OVER VIEW WITH UNION AND DISTINCT
   MF51045      LIC-DB-INCORROUT Updatable ORDER BY columns
   MF50844      LIC-DB-PERFM Not matching index between UTF8 and UTF16
   MF50760      LIC-DB-OTHER-PERFM SQL SQE QUERY RUNS MUCH LONGER AFTER UPGR
   MF50652      LIC-DB-PERFM Poorly performing query
   MF50595      LIC-DB-PERFM SSD support during query optimization
   MF50595      LIC-DB-OTHER Internal Maintenance
   MF49387      LIC-DB-INCORROUT COLLATE_ERRORS *YES not being honored for G
   MF50533      LIC-DB-OTHER Internal Maintenance
   MF50406      DB-LIC-PERFM Improve EVI symbol table select and mapping cod
   MF50206      LIC-DB-MSGMCH3203 Assert DbopAccessPlanTempIndexScan + 00005
   MF49928      LIC-DB-PERFM Improve EVI with INCLUDE costing in low memory
   MF47926      LIC-DB-MSGMCH3601  DbopStatDadIndexMisc + 00014694
   MF49311      LIC-DB-OTHER-SAP400-INCORROUT USING CORRELATED LOB FIELDS IN
   MF50497      LIC-DB-OTHER-PERFM QUERY RUNS FOR DAYS AND CONSUMES HIGH CPU
   MF50497      OSP-OTHER-PERFM QUERY USING A LOT OF CPU AFTER AN UPGRADE
   MF50497      LIC-DB-INCORROUT Cached Probe over COUNT(*) subselect may re
   MF50366      LIC-DB-OTHER-PERFM LONG RUNNING JOIN QUERY AFTER GOING TO RE
   MF50315      LIC-DB-PERFM SQL Field Procedure Index only access
   MF49963      LIC-DB-OTHER-T/QQQOOODBOP--MSGMCH3203 RUNNING SQL SELECT WIT
   MF49963      LIC-DB-INCORROUT Subquery IS NULL returns wrong result
   MF49963      OSP-DB SQL0802 F/QSQRUN2 T/QSQRUN2 HEX of Left Outer Join co
   MF49695      LIC-DB-MSGMCH0601 From DbopDataSourceMisc; To QQQOOODBOP
   MF49556      LIC-DB SMP CAN CHOOSE A VERY HIGH SMP NUMBER WHEN USING EVI'
   MF49556      LIC-DB-OTHER-PERFM QUERY WITH MANY VALUES IN AN IN PREDICATE
   MF49556      LIC-DB-Other Improved Heap usage
   MF49556      LIC-DB-MSGMCH3203- Common Table Expression query Fails
   MF49353      LIC-DB-OTHER-PERFM QUERY PERFORMANCE PROBLEM FOR COMPLEX JOI
   MF49182      OSP-DB-OTHER-PERFM INDEX ADVISOR RECOMMENDS AN EVI INDEX OVE
   MF49107      LIC-DB-MSGMCH3203 Query with ROLLUP in grouping clause fails
   MF49036      LIC-DB An SQL query performs poorly
   MF48905      LIC-PERFM SQL Field Procedures
   MF48791      OSP-DB-OTHER-F/ASSERT-T/QQQOOODBOP-RC1720-MSGMCH3203 COMPLEX
   MF48492      LIC-DB-MSGMCH3203 SQL Arrays
   MF48379      LIC-DB-MSGMCH3203 DbpmAttributeOperation + 00003D20
   MF48357      LIC-DB MSGMCH3203
   MF47886      LIC-DB Aggregate EVI build error data is not being retrieved
   MF48031      LIC-DB-MSGMCH3203 VLOG '80' in DbopAccessPlanComplicatedExpr
   MF48031      LIC-DB-OTHER-PERFM SQE QUERY PERFORMS WORSE WHEN A SPECIFIC
   MF48514      LIC-DB-INCORROUT Wrong results for query with XMLAGG express

Summary Information

System i Support