MF63590 - OSP-OTHER-PERFM PSEUDO_OPEN_CHECK_HOST_VARS QAQQINI SETTING

TYPE PROGRAM  REL  NUMBER   MIN/MAX  OPTION
---- -------- ---  -------  -------  ------
PRE  5770999  710  MF60230   00/00    0000
PRE  5770999  710  MF58658   00/00    0000
PRE  5770999  710  MF55844   00/00    0000
PRE  5770999  710  MF51012   00/00    0000
CO   5770999  710  MF63420   00/00    0000
CO   5770999  710  MF62991   00/00    0000
CO   5770999  710  MF61749   00/00    0000
CO   5770999  710  MF60502   00/00    0000
CO   5770999  710  MF59964   00/00    0000
CO   5770999  710  MF59650   00/00    0000
CO   5770999  710  MF57116   00/00    0000
CO   5770999  710  MF56160   00/00    0000
CO   5770999  710  MF56161   00/00    0000
CO   5770999  710  MF53805   00/00    0000
CO   5770999  710  MF53696   00/00    0000
CO   5770999  710  MF53695   00/00    0000
DIST 5770SS1  710  SI53601   00/00    0000
DIST 5770SS1  710  SI53226   00/00    0000
DIST 5770SS1  710  SI49242   00/00    0000
DIST 5770SS1  710  SI49222   00/00    0000
DIST 5770SS1  710  SI43994   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

-----------------------------------------------
Improvements are needed to the QAQQINI
PSEUDO_OPEN_CHECK_HOST_VARS parameter to improve the selectivity
threshold of the *YES value.

CORRECTION FOR APAR MA46273 :
-----------------------------
One of the definitions of Data Skew is when one set of host
variables per local selection predicates results in an estimate
of a small number of rows and another set of host variables for
the exact same query results in a much larger set of rows.  When
optimizing for one set of Host Variables vs the other, the plans
may be quite different.  This can cause performance problems
when one or the other plan then gets locked in via pseudo mode.
There is an INI PSEUDO_OPEN_CHECK_HOST_VARS = *YES  that allows
the query engine, once in pseudo mode,  to be more sensitive to
these host variable record counts swings.  Although this is not
a new INI, this code change enhances the algorithm for
determining when we should consider a new plan because of Host
Variable differences.   Essentially this change allows the INI
with the *YES setting to be a bit more responsive and hopefully
smooth out the impact of data skew.

CIRCUMVENTION FOR APAR MA46273 :
--------------------------------
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
--------------  ------------------------------------------------------------
   MF63421      OSP-DB-OTHER-MSGCPI0999 XMLAGG QUERIES MAY RESULT IN LARGE A
   MF63372      LIC-DB Internal maintenance
   MF63028      OSP-DB-OTHER-SRCB6XX0302-UNPRED AQP THREADS CORRUPTING THE S
   MF63005      LIC-DB-OTHER-INCORRECT INAVIGATOR PLAN CACHE ANALYSIS OF ACC
   MF62520      LIC-DB-OTHER-PERFM REMOVE CORRECT PLAN FROM PLAN CACHE
   MF61979      LIC-DB-OTHER-PERFM QUERY PERFORMS POORLY DUE TO BAD STATISTI
   MF61907      OSP-DB-OTHER-WAIT SUPERVISOR HEAP GROWTH DUE TO PENDING SIGN
   MF61748      OSP-DB-OTHER OUTPUT QRO HASH OF QUERY INITIATING MTI IN TRAC
   MF61490      OSP-DB-OTHER-F/IXMACHINEINDEXEXTEND-T/QDBPUTMX-MSGMCH2804 MS
   MF61436      LIC-DB Bad estimate in the GCM after pseudo-opens of LPG pla
   MF61276      LIC-DB-PERFM - EAO with Partitioned Tables
   MF60648      LIC-DB-OTHER-MSGMCH1202 WHEN BLANK RECORDS RETURNED FROM SQL
   MF60501      LIC-DB-OTHER-UNPRED Internal Database Maintenance
   MF60280      LIC-DB Prevent bad AQP estimate with large IN list or simila
   MF60196      LIC-DB-OTHER-F/STRINGHIGHUSE-T/QQQSRVE1-MSGMCH3203 A MSGMCH3
   MF59965      LIC-DB-OTHER-UNPRED SQE ROQ USAGE IN PLAN CACHE GROWING DAIL
   MF59936      LIC-DB-OTHER-F/STRINGHIGHUSE-T/QQQSRVE1-MSGMCH3203 MAY OCCUR
   MF59912      OSP-OTHER-PERFM LONG OPTIMIZATION TIME DUE TO NUMEROUS UDFS/
   MF59783      LIC-DB-OTHER Internal maintenance
   MF59652      LIC-DB-UNPRED Ensure order of partitions in partitioned tabl
   MF59609      LIC-DB-OTHER-PERFM SQL STATEMENT PERFORMS INTERMITTENTLY PER
   MF59404      OSP-DB SYSTEM CRASH WITH SRCB6000302
   MF59295      LIC-DB-PERFM not using best index when multiple not equal pr
   MF59041      OSP-PERFM INVALID ESTIMATE BY AQP WHEN PORTION OF TABLE HAS
   MF58891      LIC-DB-OTHER-UNPRED UNPREDICTABLE EXCEPTION HANDLING
   MF58888      LIC-DB-OTHER Internal Maintenance
   MF58781      LIC-DB-Other Internal Maintenance
   MF58719      LIC-DB-OTHER - Add QRO Hash to Visual Explain
   MF58651      LIC-DB-OTHER Internal maintenance
   MF58235      LIC-DB-OTHER-PERFM SLOW PARTITION DUE TO CONCURRENT IDENTICA
   MF58231      OSP-DB-OTHER-THREADS-WAIT JOB TAKES UP TEMP STORAGE AND CAN'
   MF58203      LIC-DB-OTHER-PERFM AQP RE-OPTIMIZATION RESULTS IN A SLOWER R
   MF58158      OSP-DB-OTHER-INCORROUT VISUAL EXPLAIN SHOWS ROW VALUE OF 0.0
   MF57716      OSP-DB-Other Enable additional Plan Cache Properties and opt
   MF57666      OSP-DB-OTHER-INCORROUT API QQQVEXPL ACTUAL ROW VALUE INCORRE
   MF57339      OSP-INCORROUT VISUAL EXPLAIN 3 OF TIMESTAMPS ARE INCORRECT.
   MF57338      LIC-DB-OTHER-PERFM Temp storage steadily increases with SQL
   MF57257      OSP-DB-OTHER-MSGMCH3203 BADB VLOG in QQQOOOCACH 'P'
   MF57117      OSP-THREADS-F/QDBSIGEX-MSGCPF523F MSGCPF523F ISSUED FOR A SI
   MF57115      LIC-DB-OTHER-UNPRED V/L 1300000C DURING PROCESS TERMINATION
   MF57015      OSP-OTHER-PERFM QUERY PERFORMANCE
   MF56947      OSP-PERFM QUERY OPTIMIZATION TAKES TOO LONG WHEN MANY INDEXE
   MF56590      SQL-DB-PERFM Index advice for ORed predicates
   MF56579      OSP-DB-PERFM Visual Explain while running fails for short ru
   MF56573      OSP-PERFM Not reusing SQL plans with override optimization g
   MF56497      LIC-DB-OTHER Advise indexes when MTIs are used for stats
   MF56475      OSP-PERFM SQL STATEMENTS THAT GET AN OPTIMIZATION GOAL OVERR
   MF56432      OSP-DB-OTHER MSGMCH3203 F/HMDERIVEDHEAPBLA MSGCPF5257 F/QDBI
   MF56410      LIC-DB-PERFM Add job scoping information to SQE Plan Cache
   MF56378      LIC-DB-OTHER-LOOP Database jobs not ending
   MF56360      LIC-DB-OTHER-F/QQQSRVI1-T/QQQVFMT-MSGCPF9898 SQE STATEMENT F
   MF56348      LIC-DB-OTHER-PERFM QUERY IS REOPTIMIZED MORE OFTEN AFTER A R
   MF56339      LIC-DB-OTHER SQE TRACING IMPROVEMENTS
   MF56255      OSP-DB SQL Plan Cache - filter by index advise key columns
   MF56207      LIC-DB-PERFM-OTHER ADDITIONAL TRACING FUNCTIONALITY TO DEBUG
   MF56188      OSP-DB SQL Plan Cache - filter by index advise key columns
   MF56143      OSP-PERFM LONG RUNNING SQL DUE TO BAD ESTIMATE WITH FETCH N
   MF56036      OSP-INCORROUT OSP INCORROUT PLANCACHE POOLID
   MF55987      OSP-DB-OTHER-F/ASSERT-T/QQQOOODBOP-MSGMCH3203 A MSGMCH3203 F
   MF55952      LIC-DB-MSGMCH3601 relating to partitioned tables and EVIs
   MF55948      DB Issue#48254-BADA - DbopParmContainer Ptr not set
   MF55943      LIC-DB-PERFM Performance Problems when DBMON active
   MF55879      LIC-DB-MSGMSG3203 0600 BADA from DbopHandlerAQP
   MF55867      LIC-DB-PERFM Overflow condition on EVI with INCLUDE over tab
   MF55837      OSP-DB-OTHER-PERFM OPTIMIZATION ENHANCMENT
   MF55806      OSP-DB-OTHER-SRCB6005121-UNPRED WORD 1: SRCB6005121 WORD 3:
   MF55767      OSP-DB Visual Explain, Not able to explain a query access pl
   MF55651      LIC-DB-OTHER Internal maintenance
   MF55516      LIC-DB-OTHER-PERFM INCONSISTENT PERFORMANCE FOR A JOIN QUERY
   MF55443      LIC-DB-PERFM-OTHER AQP LOGIC CHANGES
   MF55434      LIC-DB-OTHER-PERFM SUPPORT PLAN CACHE AUTOSIZING
   MF55328      OSP-DB-OTHER Avoid indexes when creating EVI INCLUDE index
   MF55301      LIC-DB-OTHER Add targeted tracing support for CHECK_PSEUDO_H
   MF55266      LIC-DB-OTHER not revalidating on Workload Group changes
   MF55257      LIC-DB-PERFM Optimization of partitioned table query with de
   MF55250      LIC-DB Index advice missing or incorrect
   MF55125      OSP-OTHER-UNPRED VL0600BADD GENERATED WHEN SYSTEM CREATES AN
   MF55120      LIC-DB-OTHER TARGETED TRACE FIX FOR AQP IN THE SQL ENGINE
   MF55019      LIC-DB-MSGMCH3402 SQE Optimizer is not handling numeric(32)
   MF54954      LIC-DB-OTHER IMPROVE SQE TRACING
   MF54815      LIC-DB-UNPRED referencing deleted storage when PSEUDO_OPEN_C
   MF54815      OSP-OTHER-F/#DBCINDS-T/QQQOOODBOP-MSGMCH2804 QUERY FAILS WIT
   MF54458      OSP-DB Database Monitor host variable value = 222.2
   MF54264      LIC-DB-OTHER-LOOP Job loops on list spinlock
   MF54051      LIC-DB Actively maintain MTIs
   MF54023      LIC-DB-PERFM-OTHER Problems with costing Select/Omit indexes
   MF53920      OSP-DB Database Monitor host variable value = Infinity
   MF53849      LIC-DB Internal maintenance
   MF53757      OSP-DB-OTHER-INCORROUT UDF USING THE CURRENT_TIME SQL PREDIC
   MF53622      OSP-DB-OTHER-UNPRED SQL INSERT WITH SUM HANGS WHEN RAN WITH
   MF53588      OSP-DB Internal Maintenance for Database Monitor
   MF53588      LIC-DB-PERFM BAD STAT IN THE GLOBAL CONTEXT.
   MF53556      LIC-DB-OTHER SQL OPTIMIZER TRACE IMPROVEMENTS
   MF53476      LIC-DB-PERFM Add PSEUDO_OPEN_CHECK_HOST_VARS QAQQINI option
   MF53357      LIC-DB-OTHER-PERFM TOO MANY PLANS IN THE PLAN CACHE FOR A GI
   MF53023      OSP-DB-OTHER-INCORROUT A FIELDPROC MAY SHOW CACHED RESULTS I
   MF53023      OSP-DB-OTHER-SAP400-F/DBPMTRAP-T/QQQOOODBOP-MSGMCH3203 SAP C
   MF53023      LIC-DB-OTHER-PERFM COMPLEX QUERY PLAN IS TOO LARGE TO BE SAV
   MF53023      LIC-DB-INCORROUT S/O INDEX INCORRECTLY USED IS CAUSING INCOR
   MF52863      OSP-DB-OTHER-INCORROUT SPARSE PREDICATE VALIDATION NOT HANDL
   MF52506      OSP-DB-OTHER-UNPRED SQL ROQ SIZES TOO LARGE AND BEING CACHED
   MF52489      LIC-DB-OTHER-UNPRED 0600 BADA VLOG may be produced after can
   MF52458      LIC-DB Connect By infrastructure
   MF52419      LIC-DB-PERFM Performance on query of MULTI-DS LFs
   MF52251      LIC-PERFM DBOPAQCM/findLowerLeftNode looping starting the CP
   MF52183      LIC-DB-OTHER Internal Maintenance
   MF51929      LIC DB - 0600 BADD LIC logs from removeIter
   MF51380      LIC-DB-PERFM SQE optimization performance
   MF50491      OSP-DB-PERFM Not using MQT when MQT matches query selection
   MF51322      LIC-DB-OTHER-PERFM SQE performance improvements
   MF51189      OSP-DB Creation of SQL deflated table
   MF49927      LIC-DB-PERFM Improve EVI with INCLUDE costing in low memory
   MF48571      OSP-OTHER-PERFM DUPLICATE TRANSLATE TABLES
   MF50862      LIC-DB-OTHER Internal maintenance
   MF50650      OSP-DB Visual Explain showing incorrect host variable values
   MF50649      OSP-DB Visual Explain showing incorrect host variable values
   MF50462      LIC-DB-OTHER Plan Cache Dump not showing the correct pool in
   MF49991      LIC-DB Generating DbopTrace on MSGMCH6260
   MF49959      LIC-DB-OTHER LPG HIGHLIGHTING IS NOT WORKING CORRECTLY IN TH
   MF49911      LIC-DB-INCORROUT Correctly transfer translate table storage
   MF49798      OSP-DB Performance enhancement for SQL MERGE statement
   MF49766      LIC-DB Array fixes
   MF49636      LIC-DB-INCORROUT Graphic values in the DBMON 3010 record
   MF48338      LIC-DB Not deallocating storage
   MF49611      DB-LIC-PERFM Enable CREATE EVI with INCLUDE  with SMP settin
   MF49529      LIC-DB-PERFM STRDBMON missing QQQ3010 record and BADD vlog
   MF49417      LIC-DB-OTHER Update Detailed Plan Cache Dump
   MF49397      DB-LIC-INCORROUT Wrong results possible with Encoded Vector
   MF49346      LIC-DB-PERFM REDUCE PAGE FAULTING DURING SQL PLAN VALIDATION
   MF49346      LIC-DB-OTHER INDEX ADVISE INFRASTRUCTURE CHANGE
   MF49346      LIC-DB-OTHER SQL OPTIMIZER TRACING IMPROVEMENTS
   MF49341      LIC-DB 0600 BADA VLOGs generated while running SQL queries
   MF49158      OSP-DB Array fixes
   MF49112      LIC-DB-INCORROUT Missing decFloat values in DBMON 3010 recor
   MF49037      LIC-DB SQL query performs poorly
   MF48981      OSP-DB-OTHER-PERFM SQL STATEMENTS PERFORM WORSE AT R610 OVER
   MF48970      LIC-DB-SQL query runs very poorly after AQP plan replacement
   MF47999      LIC DB DBST Static Variable Cleanup
   MF48909      LIC DB PERFM Allow MQTs and EVIs with INCLUDE to coexist.
   MF48793      OSP-DB-OTHER-F/ASSERT-T/QQQOOODBOP-RC1720-MSGMCH3203 COMPLEX
   MF48753      LIC-DB Visual Explain of plan contains value of 'UNKNOWN' fo
   MF48753      LIC-DB Beam fix for NULL class pointer
   MF48622      OSP DB OTHER EVI Include and aggregation rounding mode
   MF48567      LIC-DB SQL query does not complete.
   MF48567      OSP-DB-OTHER-PERFM NEW QAQQINI OPTION, DETERMINISTIC_UDF_SCO
   MF48424      LIC-DB VLOG contains many VLOGs with major code 0600, minor
   MF48354      LIC-DB Numerous Database VLOGs, major code 0600, minor code
   MF48030      LIC-DB-MSG3203 SQL query with grouping sets clause fails
   MF48125      LIC-DB-OTHER-F/ASSERT-T/QQQOOODBOP-MSGMCH3203 AND TEMP STORA
   MF47919      LIC-DB Too many DataBase Monitor Records

Summary Information

IBM i Support