IBM Support

MF61984 - OSP-DB_OTHER FULL OUTER JOIN ESCAPING QUERY NORMALIZATION

PTF Cover Letter


PTF ( Program Temporary Fixes ) Cover letter


Order this fix

Abstract

OSP-DB_OTHER FULL OUTER JOIN ESCAPING QUERY NORMALIZATION


Pre/Co-Requisite PTF / Fix List

REQ  LICENSED           PTF/FIX  LEVEL

TYPE PROGRAM  RELEASE   NUMBER   MIN/MAX  OPTION
---- -------- --------- -------  -------  ------
PRE  5770999  V7R2M0    MF60675   00/00    0000
PRE  5770999  V7R2M0    MF58880   00/00    0000
CO   5770999  V7R2M0    MF61933   00/00    0000
CO   5770999  V7R2M0    MF61923   00/00    0000
CO   5770999  V7R2M0    MF61259   00/00    0000
CO   5770999  V7R2M0    MF60775   00/00    0000
CO   5770999  V7R2M0    MF59853   00/00    0000
CO   5770999  V7R2M0    MF58957   00/00    0000
DIST 5770SS1  V7R2M0    SI54318   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

-----------------------------------------------
Query with a FULL OUTER JOIN that is not getting normalized out.

CORRECTION FOR APAR MA45591 :
-----------------------------
Full outer join queries are rewritten at query normalization
time. A full outer join query is not getting rewritten correctly

CIRCUMVENTION FOR APAR MA45591 :
--------------------------------
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
--------------  ------------------------------------------------------------
   MF61934      LIC-DB-OTHER-PERFM DBPMSERVER TASKS DRIVING HIGH DISK LOAD F
   MF61921      LIC-DB Internal Maintenance
   MF61860      OSP-DB-OTHER-F/ASSERT-T/QQQOOODBOP-MSGMCH3203 QUERY FAILS WI
   MF61751      LIC-DB-OTHER-PERFM POOR PERFORMING QUERY WHEN IMPLEMENTED WI
   MF61723      LIC-DB PERFM LONG RUNNING SQL QUERY WITH EVI ACCESS.
   MF61682      LIC-DB Internal Database Maintenance
   MF61669      LIC-DB-OTHER-INCORROUT Left outer join query returns wrong r
   MF61653      OSP-DB-OTHER-UNPRED TMPSTGLK SQE BUCKET 8 *DATABASE SEGMENT
   MF61570      LIC-DB-INCORROUT Grouping query with OLAP
   MF61504      LIC-DB-PERFM SQL Improve column elimination in union queries
   MF61501      OSP-INCORROUT WHEN FULL OUTER JOIN QUERY HAS SPECIFIC STRUCT
   MF61419      OSP-DB-OTHER-THREADS-F/DBOPAFFINITY-T/QQQOOODBOP-MSGMCH3203
   MF61331      LIC-DB-MSGMCH3203 DbstMdDadDataSpaceConcrete
   MF61307      OSP-DB-OTHER-INCORROUT INCORRECT OUTPUT MAY OCCUR WITH A CRO
   MF61264      LIC-DB-PERFM - EAO with Partitioned Tables
   MF61234      OSP-DB-OTHER-PERFM SLOW PERFORMANCE AFTER CUME JOIN OPTIMIZA
   MF61234      OSP-PERFM JOIN QUERY INTERMITTENTLY PERFORMS POORLY DUE TO T
   MF61213      OSP-DB-OTHER-F/DBOPSTRATEGY-T/QQQOOODBOP-MSGMCH3601 WHEN A D
   MF61206      OSP-DB MSGMCH3601 F/DBPMFORMAT T/QQQOOODBOP VL0600 BADD
   MF61199      OSP-DB-OTHER-INCORROUT INCORRECT RESULTS MAY OCCUR WITH REPL
   MF61191      LIC-DB-OTHER-PERFM SLOW PERFORMANCE ON A RECURSIVE COMMON TA
   MF61142      LIC-PERFM-PAR-940XMISC Bad Query Plan
   MF61089      OSP-DB-OTHER MSGMCH3203 F/DBPMTRAP T/QQQOOODBOP
   MF61079      OSP-DB-OTHER-INCORROUT A SQL QUERY MAY SHOW A NULL FOR A COL
   MF61055      OSP-DB-OTHER-F/DBLISTHOME-T/QQQOOODBOP-MSGMCH3203 MSGMCH3203
   MF60943      LIC-DB-PERFM Union Queries
   MF60888      LIC-DB Internal maintenance
   MF60821      OSP-UNPRED WHEN USING SQE QUERY ENGINE DUPLICATE MTI'S MAY B
   MF60785      OSP-OTHER-PERFM LONG OPTIMIZATION TIME DUE TO NUMEROUS UDFS/
   MF60776      OSP-PAR-940XBASE ALLOW REMOVAL OF NON-SHAREABLE MTIS IN A MO
   MF60761      LIC-DB-PERFM SQL EVI Only Access query optimization techniqu
   MF60748      LIC-DB-INCORROUT SMP XML INCORRECT OUTPUT AFTER UPGRADE
   MF60741      LIC-DB-OTHER-UNPRED Internal Database Maintenance
   MF60736      LIC-DB-OTHER-F/DBPMTRAPX/0958-T/QQQOOODBOP-RCX/1720-MSGMCH32
   MF60712      F/QSQFETCH-MSGSQL0811-RCTYPE1 FROM SQL QUERY WITH FETCH FIRS
   MF60697      OSP-DB-OTHER-UNPRED A SQLSTATE 01003 (SQLCODE 000) MAY OCCUR
   MF60696      F/QQQOOODBOP-MSGMCH3203-RCX'1720'-MOD/QQQOOOINV- OPCS/CALLDB
   MF60692      OSP-MSGMCH3203-D/TQQQOOODBOP-PAR QUERY WITH COMMON TABLE EXP
   MF57962      OSP-DB-OTHER-T/CALLDBMAINTFOROPENOROPTIMIZE-MSGMCH3203 IN SE
   MF57962      LIC-DB-OTHER-F/DBPMTRAP-T/QQQOOOUPCL-MSGMCH3203 SQL SELECT F
   MF60655      LIC-DB-OTHER-PERFM SQL WITH DISTINCT AND UNION PERFORMS POOR
   MF06008      OSP-DB-OTHER-F/DBPMFORMAT-T/QDBGETMQO-MSGMCH3203 A MSGMCH320
   MF60550      LIC-DB-OTHER-PERFM QUERY WITH OLAP FUNCTIONS AND DATA SKEW M
   MF60453      OSP-PERFM JOIN QUERY PERFORMS POORLY DUE TO FIRST N ROWS (FN
   MF60451      LIC-DB-OTHER-F/QDBSIGEX-MSGCPF5149 MAY OCCUR WHEN USING USIN
   MF60439      SQL-RUN THE NEW OPTIMIZER USES DIFFERENT TECNIQUES TO LOCK T
   MF60433      OSP-DB-OTHER-WAIT FILEORD COLUMN WOULD AFFECT THE ORDERING O
   MF60354      F/ASSERT-MSGMCH3203-MOD/QQQOOODBOP QUERY WITH AGGREGATE FAIL
   MF60299      OSP-DB-OTHER-INCORROUT A SELECT STMT RETURNS THE WRONG RESUL
   MF60254      OSP-DB-OTHER-T/QQQOOODBOP-MSGMCH3203 A MCHMSG3203 MAY OCCUR
   MF60251      OSP-OTHER-PERFM SUBOPTIMAL INDEXES BEING CHOSEN CAUSING POOR
   MF60224      OSP-DB-OTHER-F/DBOPRELINDEX-T/QQQOOODBOP-MSGMCH3203 OPNQRYF
   MF60169      OSP-PAR-940XMISC QUERY RUNS LONGER AFTER R720 UPGRADE. OPTIM
   MF60119      OSP-DB-OTHER-PERFM INDEX NOT USED, LPG
   MF60058      LIC-DB-PERFM SQL Enable Index Merge Ordering query optimizat
   MF60034      LIC-DB-OTHER-INCORROUT OLAP ORDER BY ASC NULLS FIRST WITH NU
   MF59993      LIC-DB-OTHER-PERFM IMPROVE SQL PLAN VALIDATION
   MF59985      OSP-PAR-940XMISC LARGE IN LISTS HANDLED DIFFERENT AT 720 COM
   MF59976      LIC-DB-INCORROUT LIKE predicate on a VARCHAR column
   MF59976      LIC-DB-INCORROUT SMP and Subquery
   MF59891      LIC-DB-OTHER Internal maintenance
   MF59880      OSP-DB QUERY WITH TIME/TIMESTAMP FUNCTIONS IS FAILING WITH M
   MF59855      LIC-DB-UNPRED Ensure order of partitions in partitioned tabl
   MF59846      OSP-DB-OTHER-F/DBOPQUERY-T/QQQOOODBOP-MSGMCH3203 MSGMCH3203
   MF59841      LIC-MSGMCH3203 OPTIMIZING QUERY WITH CONSTANT SUBSTITUTION I
   MF59830      OSP-DB-OTHER-PERFM WHEN RUNNING A QUERY AGAINST A FILE WITH
   MF59821      OSP-DB-OTHER-F/ASSERT-T/QQQOOODBOP-MSGMCH3203 WHEN USING AN
   MF59820      OSP MCH3203 FUNCTION CHECK WHEN QUERY PROCESSED BY OPTIMIZER
   MF59820      OSP-DB-OTHER-MSGMCH3203 A MSGMCH3203 MAY OCCUR WITH A DELETE
   MF59765      LIC-DB-PERFM IN list query
   MF59727      LIC-DB-PERFM not using sparse index
   MF59627      OSP-DB-OTHER-F/DBOPEXPRVALMISC-T/QQQOOODBOP-MSGMCH3601 QUERY
   MF59585      LIC-DB-OTHER-INCORROUT UDFS WITHIN RCAC PERMISSIONS
   MF59573      LIC-DB-OTHER-UNPRED Query fails when VALUES clause reference
   MF59553      LIC-DB-PERFM Enable Index Merge Ordering
   MF59526      LIC-DB-PERFM not using best index when multiple not equal pr
   MF58248      OSP-PERFM PERFORMANCE PROBLEMS WITH UNION QUERY USING DISTIN
   MF59463      LIC-DB-OTHER-INCORROUT SCALAR FUNCTION RAND()
   MF59463      LIC-DB-OTHER-PERFM DISTINCT QUERY TAKES TWICE AS LONG AFTER
   MF59463      LIC-DB-OTHER-INCORROUT SCALAR FUNCTION RAND()
   MF59363      LIC-DB-OTHER-PERFM LEFT EXCEPTION JOIN QUERY PERFORMS POORLY
   MF59341      LIC-DB-OTHER-UNPRED QUERY WITH FAILING WITH MCHMCH3203 T/QQQ
   MF59302      OSP-PERFM LONGER RUNNING QUERY WHEN IN LIST IS LARGER THAN E
   MF59244      LIC-DB-OTHER-PERFM HIGHER CPU CONSUMPTION FOR QUERY AFTER AP
   MF59244      LIC-PERFM SQL PERFORMANCE ISSUES INVOLVING ORDERING PLANS
   MF59137      LIC-DB-OTHER-PERFM RECURSIVE QUERY CONNECT BY PERFORMANCE FO
   MF59121      LIC-DB-OTHER-UNPRED QUERY WITH OLAP FUNCTION FAILING AFTER U
   MF59094      LIC-DB-OTHER-PERFM UPDATE STATEMENT INTERMITTENTLY PERFORMS
   MF59094      LIC-DB-OTHER-PERFM QUERY OPTIMIZER IMPROVEMENTS TO COSTING.
   MF58954      LIC-DB-OTHER Internal Maintenance
   MF58859      LIC-DB-OTHER-PERFM QUERY OPTIMIZER DOES NOT IMPLEMENT VIA AN
   MF58842      LIC-DB-OTHER-F/QDBGETM-T/DBPMNATIVEACCESS-MSGMCH1825 ON A _R
   MF58812      OSP-DB-OTHER-UNPRED 0600DEAD VLOGS ARE GENERATED WHEN RUNNIN
   MF58812      LIC-DB-OTHER-PERFM SMP IMPLEMENTATION LEADS TO POOR SQL QUER
   MF58787      LIC-DB-OTHER QRY/400 fails with selection and ordering on co
   MF58771      OSP-DB-OTHER-PERFM CALLDBMAINTFOROPENOROPTIMIZE
   MF58752      OSP-DB-OTHER-INCORROUT NO RECORDS RETURNED FROM PARTITIONED
   MF58680      LIC-DB-OTHER-PERFM OPTIMIZER SELECTS TABLE SCAN RATHER THAN
   MF58544      LIC-DB-OTHER-PERFM VIEW CONTAINING SELECT LIST UDFS ALSO USE
   MF58476      LIC-DB-PERFM not using ICU index
   MF58380      LIC-DB-MSGMCH3203 CONNECT BY when running SMP
   MF58336      OSP-PERFM GROUPING QRY OVER SINGLE TABLE RUNS LONG WITH PLAN
   MF58333      OSP-DB-OTHER-F/QDBIOERRQO-T/QSQRUN2-MSGCPF503E MSGSQL0502-F/
   MF58330      LIC-DB-MSGMCH3203 during UPDATE statement
   MF58330      LIC-DB-INCORROUT DELETE under commit with subquery
   MF58247      LIC-DB-OTHER-PERFM QUERY OPTIMIZER DOES NOT USE ALL AVAILABL
   MF58228      LIC-DB-PERFM Internal Maintenance
   MF58191      OSP-DB-OTHER-F/QSQRUN3-T/QSQRUN3-RC6-MSGSQL0952 MSGSQL0952 F
   MF58174      OSP-DB-OTHER-F/ASSERT-T/QQQOOODBOP-MSGMCH3203 SQL MCH3203 QQ
   MF58140      LIC-DB-OTHER-PERFM AN INSERT STATEMENT WITH A SUBSELECT OVER
   MF58140      OSP-DB-OTHER-F/QSQRUN3-MSGSQL0243 A MSGSQL0243 MAY OCCUR WIT
   MF58140      OSP-F/DBPMTRAP-T/QQQOOODBOP-MSGMCH3203 MSGMCH3203 F/DBPMTRAP
   MF58140      OSP-DB SQL STATEMENT THAT USES HIERARCHICAL RECURSION FAILS
   MF58132      LIC-DB-MSGMCH3203 Delete from a Partitioned Table
   MF58130      LIC-DB-OTHER-F/DBPMTRAP-T/QQQOOODBOP-MSGMCH3203 MSGMCH3203
   MF58064      LIC-DB-MSGMCH3203 Max query and SMP
   MF57977      OSP-PERFM SPARSE MTI CHOSEN OVER PERMANENT INDEX RESULTING I
   MF57975      LIC-DB-OTHER-PERFM A COMPLEX QUERY PERFORMS POORLY.
   MF57975      OSP-DB-OTHER-T/QQQOOODBOP-RC1720-MSGMCH3203 WITH A LEFT OUTE
   MF57947      LIC-DB Internal Maintenance
   MF57932      OSP-DB-OTHER-F/ASSERT-T/QQQOOODBOP-MSGMCH3203 SELECT * FROM
   MF57920      OSP-DB-OTHER-F/DBPMTRAP-T/QQQOOODBOP-MSGMCH3203 SQL TRAPS IN
   MF57773      LIC-DB Internal Maintenance
   MF57704      LIC-DB-MSGMCH3203 Grouping Sets
   MF57622      LIC-DB Internal Maintenance
   MF57805      LIC-DB Internal Maintenance
   MF59425      LIC-DB-OTHER-F/ASSERT-T/QQQOOODBOP-MSGMCH3203 QUERY FUNCTION

Summary Information

System..............................i
Models..............................
Release.............................V7R2M0
Licensed Program...............5770999
APAR Fixed..........................View details for APAR MA45591
Superseded by:......................View fix details for PTF MF68917
Recompile...........................N
Library.............................QSYS
MRI Feature ........................NONE
Cum Level...........................C6306720


IBM i Support

IBM disclaims all warranties, whether express or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. By furnishing this document, IBM grants no licenses to any related patents or copyrights. Copyright © 1996,1997,1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021 IBM Corporation. Any trademarks and product or brand names referenced in this document are the property of their respective owners. Consult the Terms of use link for trademark information.

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.2.0","Product":{"code":"SWG60","label":"IBM i"},"Component":"5770999","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Document Information

Modified date:
01 October 2021