IBM Support

MF66414 - LIC-DB-OTHER Query fails for predicate comparing zoned field

PTF ( Program Temporary Fixes ) Cover letter


Order this fix

Abstract

LIC-DB-OTHER Query fails for predicate comparing zoned field


Pre/Co-Requisite PTF / Fix List

REQ  LICENSED           PTF/FIX  LEVEL

TYPE PROGRAM  RELEASE   NUMBER   MIN/MAX  OPTION
---- -------- --------- -------  -------  ------
PRE  5770999  V7R2M0    MF64481   00/00    0000
PRE  5770999  V7R2M0    MF64036   00/00    0000
CO   5770999  V7R2M0    MF65361   00/00    0000
CO   5770999  V7R2M0    MF65363   00/00    0000
CO   5770999  V7R2M0    MF65277   00/00    0000
CO   5770999  V7R2M0    MF65086   00/00    0000
CO   5770999  V7R2M0    MF60573   00/00    0000
CO   5770999  V7R2M0    MF64875   00/00    0000
CO   5770999  V7R2M0    MF60904   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 have a predicate that compares a column of one
data type to a literal or host variable value of a different
type may fail with message MCH3203 F/DbstMdQilArray. There will
be an accompanying 0600 BADD LIC log referencing MODULE
DbstMdQilArray.

CORRECTION FOR APAR MA47801 :
-----------------------------
The statistics engine was incorrectly calculating the size of
internal structures used to make estimates for certain
predicates involving different data types. This could possibly
cause other storage to be overwritten and could occasionally
cause the query to fail as described. This fix corrects the size
calculation that is used.

CIRCUMVENTION FOR APAR MA47801 :
--------------------------------
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
--------------  ------------------------------------------------------------
   MF66165      OSP-DB-OTHER-PERFM SKEWED RRN LIST SCAN ESTIMATE CAUSING BAD
   MF66038      OSP-OTHER-PERFM QUERY TAKES SEVERAL MINUTES TO OPTIMIZE
   MF65704      OSP-DB-OTHER-F/QSQRUN3-T/QSQRUN3-RC6-MSGSQL0952 SQL FAILS WI
   MF65588      OSP-PERFM POOR PERFORMANCE INTERMITTENTLY FOR QUERY WITH COM
   MF65503      OSP-DB Index key range cache gate contention
   MF65446      OSP-PAR-940XMISC POOR RECORD COUNT ESTIMATE AFFECTS JOIN ORD
   MF65398      OSP-DB-OTHER-PERFM MULTI-KEY COMBINED CARDINALITY ESTIMATE I
   MF65362      LIC-DB-OTHER Internal Database Maintenance.
   MF65294      OSP-OTHER-PERFM RCTE QUERY PERFORMS POORLY
   MF65278      LIC-DB-OTHER-F/DBOPEXPRQDS-T/QQQOOODBOP-MSGMCH3203 MAY OCCUR
   MF65085      LIC-DB-OTHER-UNPRED CARDINALITY CAPPED AT 4B (MAX 4-BYTE INT
   MF64960      OSP-OTHER-PERFM QUERY SPENT SEVERAL MINUTES IN OPTIMIZATION
   MF64886      OSP-PAR-940XMISC IMPROVING ESTIMATES WITH LPG AND PARTITIONE
   MF64876      OSP-PERFM JOIN ORDER NOT CHOSEN WELL AT 7.2
   MF64853      LIC-DB-OTHER Internal maintenance
   MF64855      OSP-PAR-940XMISC INACCURATE INDEX PROBE I/O COSTING LEADS TH
   MF64848      PERFM-HLIC-94XXBASE INACCURATE ESTIMATE FROM GLOBAL STATS CA
   MF64685      OSP-PERFM QUERY OVER LARGE TABLES THAT HAVE EVIS MAY GET OPT
   MF64539      OSP-OTHER-PERFM QUERY OPTIMIZATION TIME
   MF64415      OSP-PERFM RCTE QUERY CAUSING HIGH CPU UTILIZATION IN DATABAS
   MF64294      OSP-DB-OTHER-PERFM SQL BAD ESIMATE
   MF64164      LIC-DB Statistics engine gives a poor estimate for partition
   MF64037      LIC-DB Internal Maintenance
   MF63979      OSP-DB-OTHER-THREADS-SRCB6000302 SRCB6000302
   MF63961      OSP-OTHER-PERFM QUERY, OVER PARTITIONED TABLES, IS PERFORMIN
   MF63821      OSP-PAR-940XBASE STATS ARE NOT UPDATED ON A PARTITIONED TABL
   MF63796      LIC-DB-OTHER-PERFM Query runs slowly because it uses MTI ins
   MF63728      OSP-DB-OTHER-F/QLIDLOBJ-MSGCPF2105 A TEMP FILE THAT IS CREAT
   MF63643      OSP-OTHER-PERFM QUERY PERFORMANCE
   MF63454      OSP-DB-OTHER-PERFM INEFFICIENT MTI USED IN A PLAN
   MF63379      LIC-DB-OTHER-PERFM IMPROVE DB ESTIMATE
   MF63246      LIC-DB-PERFM FETCH FIRST ROW query over table runs slowly
   MF63010      LIC-DB Internal Maintenance
   MF62501      LIC-DB-OTHER-PERFM SQL STATEMENT CONTAINING A RCTE IS RE-OPT
   MF62436      LIC-DB Handle missing index during predicate checking on pla
   MF62220      LIC-DB-OTHER-PERFM IMPROVE COSTING EVALUATION FOR AUX STORAG
   MF62171      OSP-PERFM ESTIMATES IN LARGE IN LIST CONVERSION TREES CAUSIN
   MF62066      LIC-DB Poor record count estimates in complex RRN AND/OR pla
   MF62064      LIC-DB-OTHER Increase database random Input/Output initial b
   MF61898      LIC-DB-OTHER-PERFM Improved query optimizer costing for tabl
   MF61753      LIC-DB Keep track of longest estimate key range during optim
   MF61735      LIC-DB-OTHER Temporary storage may leak when running SQL que
   MF61631      LIC-DB-OTHER-LOOP A query predicate with multiple large NOT
   MF61458      OSP-LOOP JOB LOOPING IN DBSTMDEXPRTREEADMIN
   MF61402      LIC-DB-OTHER-PERFM SQE query may run slowly when OPTIMIZE FO
   MF61370      LIC-DB Add QRO hash in optimization trace for AQP/GCM estima
   MF61115      LIC-DB Estimate improvement for EOA plans with partitioned t
   MF61098      LIC-DB-OTHER-PERFM Stale column statistics not analyzed duri
   MF61081      LIC-DB-OTHER-PERFM STATEMENT WITH LEFT OUTER JOIN AND COALES
   MF60905      LIC-DB-OTHER-PERFM Improved column statistics collection for
   MF60841      OSP-DB-OTHER-PERFM BAD DBST ESTIMATE WHEN GETTING INVALID CA
   MF60813      LIC-DB Improve optimizer estimate for partitioned tables wit
   MF60686      OSP-OTHER-PERFM QUERY WITH RECURSIVE COMMON TABLE EXPRESSION
   MF60675      LIC-DB Estimate Improvement for complex bitmap AND/OR tree s
   MF60631      OSP-PAR-940XMISC BETTER STATS USAGE FOR PARTITONED TABLES
   MF60513      OSP-PAR-940XMISC PARTITION TABLE ESTIMATE IMPROVEMENT
   MF60485      OSP-OTHER-PERFM AFTER LOADING GROUP PTFS AND IPLING, QUERY C
   MF60301      LIC-DB-OTHER-PERFM Improved record counts for certain join q
   MF60278      OSP-LOOP JOB LOOPING ON DBSTPOINTERMAPHOMEMACH+0X98A8
   MF60274      OSP-DB-OTHER-PERFM SELECT/OMIT INDEX IN FROM CLAUSE NOT USED
   MF60260      LIC-DB-OTHER-PERFM TABLE SCAN FOR AGGREGATION QUERY RESULTS
   MF60164      OSP-DB-OTHER-LOOP SQE LOOP
   MF60050      LIC-DB-OTHER-PERFM TABLE SCAN FOR AGGREGATION QUERY RESULTS
   MF59986      OSP-OTHER-PERFM LONG CURSOR OPEN TIME WITH INCREASED CPU CON
   MF59986      LIC-DB Prevent 0600/BADA VLOG during AQP analysis
   MF59870      LIC-DB Improved record counts for left outer join queries wi
   MF59827      LIC-DB-OTHER-PERFM INTERMITTENT POOR SQL PERFORMANCE
   MF59827      OSP-PAR THE STATISTICS ENGINE IS INCORRECTLY UPDATING HOST V
   MF59729      OSP-PAR-940XMISC MTI BEING BUILT WITH A BUFFER SCAN OVER AN
   MF59729      OSP-DB-OTHER-PERFM SQL SELECT INTERMITTENT RUNS VERY MUCH LO
   MF59700      LIC-DB Improve cardinality, selectivity estimates with compl
   MF59449      OSP-PERFM QUERIES AT R710 ARE RUNNING SLOWER UNLESS THE INLI
   MF59417      OSP-PERFM SQL OVER A VIEW CAUSES LONG OPTIMIZATION TIMES
   MF59417      LIC-DB-OTHER-PERFM LONG EKR (ESTIMATE KEY RANGE) DURING QUER
   MF59351      OSP-DBSMP-PERFM Query runs slowly after a bad AQP estimate r
   MF59351      LIC-DB Improved cardinality estimate over multiple PT column
   MF59000      OSP-DB-OTHER-PERFM LARGE SYSTEMS CAN EXPERIENCE MACHINE GATI
   MF59000      OSP-DB-OTHER-PERFM QUERY SLOW, USES MORE CPU, GETKEYRANGEEST
   MF58904      LIC-DB-Other Internal Maintenance
   MF58904      LIC-DB Internal changes to improve statistical estimates
   MF58860      LIC-DB-OTHER-PERFM QUERY OPTIMIZER DOES NOT IMPLEMENT VIA AN
   MF58793      OSP-LOOP DBSTMDEXPRESSIONVALUEBASE
   MF58438      LIC-DB-OTHER-PERFM Incorrect disk response time estimates fo
   MF58402      LIC-DB-OTHER-PERFM DISTINCT QUERY PERFORMS POORLY FOLLOWING
   MF58283      LIC-DB Internal Maintenance
   MF58176      LIC-DB-OTHER-PERFM SQL OVER PARTITION TABLE HIGH CPU.
   MF58176      LIC-DB Incorrectly using EVI for cardinality estimate
   MF58001      LIC-DB Internal Maintenance
   MF57875      LIC-DB Internal Maintenance
   MF57627      LIC-DB Internal Maintenance
   MF60575      LIC-DB Internal maintenance

Summary Information

System.............................. i
Models..............................
Release............................. V7R2M0
Licensed Program............... 5770999
APAR Fixed.......................... View details for APAR MA47801
Superseded by:......................
Recompile........................... N
Library............................. QSYS
MRI Feature ........................ NONE
Cum Level........................... C9297720


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 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.

Document information

More support for: i family

Software version: V7R2M0

Operating system(s): OS/400

Reference #: MF66414

Modified date: 28 August 2019