IBM Support

SI62795 - OSP-DB Internal Database Maintenance

PTF Cover Letter


PTF ( Program Temporary Fixes ) Cover letter


Order this fix

Abstract

OSP-DB Internal Database Maintenance


Pre/Co-Requisite PTF / Fix List

REQ  LICENSED      PTF/FIX  LEVEL

TYPE PROGRAM  REL  NUMBER   MIN/MAX  OPTION
---- -------- ---  -------  -------  ------
PRE  5770999  710  MF61845   00/00    0000
PRE  5770SS1  710  SI57611   00/00    0000
PRE  5770999  710  MF57718   00/00    0000
PRE  5770999  710  MF56581   00/00    0000
PRE  5770999  710  MF55767   00/00    0000
PRE  5770999  710  MF51692   00/00    0000
CO   5770SS1  710  SI62793   00/00    0000
CO   5770SS1  710  SI58135   00/00    0000
CO   5770SS1  710  SI58130   00/00    0000
CO   5770SS1  710  SI52858   00/00    0000
CO   5770SS1  710  SI49370   00/00    0000
CO   5770SS1  710  SI48080   00/00    0000
CO   5770SS1  710  SI48078   00/00    0000
CO   5770SS1  710  SI48126   00/00    0000
CO   5770SS1  710  SI48138   00/00    0000
CO   5770SS1  710  SI48142   00/00    0000
CO   5770SS1  710  SI48127   00/00    0000
CO   5770SS1  710  SI40213   00/00    0000
DIST 5770999  710  MF55412   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 license agreement which
accompanied, or was contained in, the Program for which you are obtaining
the PTF.  You are not authorized to install or use the PTF except as part
of a Program for which you have a valid Proof of Entitlement.

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.

The applicable license agreement may have been provided to you in printed
form and/or may be viewed using the Work with Software Agreements (WRKSFWAGR)
CL command.





APAR Error Description / Circumvention

-----------------------------------------------
OSP-DB Internal Database Maintenance

CORRECTION FOR APAR SE65892 :
-----------------------------
OSP-DB Internal Database Maintenance

CIRCUMVENTION FOR APAR SE65892 :
--------------------------------
None.


Activation Instructions


None.




Special Instructions


********************************************************************
THE FOLLOWING ARE SUPERSEDED SPECIAL INSTRUCTIONS. IF THE SUPERSEDED
PTF HAS ALREADY BEEN APPLIED AND ITS SPECIAL INSTRUCTION FOLLOWED,
IT IS NOT NECESSARY TO FOLLOW THAT SPECIAL INSTRUCTION AGAIN.
********************************************************************

SPECIAL INSTRUCTIONS FOR SUPERSEDED PTF SI42908 :
=================================================

This PTF will not fix any currently existing SQL indexes created over
columns that reference fieldprocs and that specify derived keys, the
WHERE clause, or the RCDFMT clause.  These indexes must be recreated
after this PTF has been applied.


To ensure the fix goes into effect immediately, use the command:

ENDPJ SBS(QSYSWRK) PGM(QSYS/QSQSRVR)

before doing an immediate apply of the PTF.

STRPJ SBS(QSYSWRK) PGM(QSYS/QSQSRVR)

After the PTF has been applied to restart the QSQSRVR prestart jobs.

To ensure the fix goes into effect immediately, use the command:

ENDPJ SBS(QSYSWRK) PGM(QSQSRVR)

before doing an immediate apply of the PTF.

To properly enable this PTF for prestarted server jobs,
you will need to stop and restart the SQL server jobs,
the Directory Services server jobs, the DRDA TCPIP server
jobs, and the host server jobs.  Otherwise,
the prestarted server jobs may continue to use older versions
of programs and unexpected results may occur.  Applying this
PTF as delayed would achieve the same results.

To stop and restart the SQL server jobs, run the following:

ENDPJ SBS(QSYSWRK) PGM(QSQSRVR)
STRPJ SBS(QSYSWRK) PGM(QSQSRVR)

To stop and restart the DRDA server jobs, run the following:

ENDPJ SBS(QUSRWRK) PGM(QRWTSRVR)
STRPJ SBS(QUSRWRK) PGM(QRWTSRVR)

To stop and restart the DRDA TCPIP server jobs, run the following:

ENDTCPSVR SERVER(*DDM)
STRTCPSVR SERVER(*DDM)

To stop and restart the Directory Services server job,
run the following:

ENDTCPSVR SERVER(*DIRSRV)
STRTCPSVR SERVER(*DIRSRV)

To stop and restart the host server jobs, the following directions
must be followed.

The database server prestart jobs (QZDAINIT, QZDASOINIT, and
QZDASSINIT) by default are shipped to run in subsystem QSERVER
(QZDAINIT) and QUSRWRK (QZDASOINIT and QZDASSINIT).  However,
systems
can be customized to allow the database server prestart jobs to run
in other subsystems.

SYSTEMS WITH DEFAULT CONFIGURATION (database server prestart jobs
run
ONLY in the QSERVER and QUSRWRK subsystems).

---------------------------------------------------------------------

1) To ensure that the database server prestart jobs are ended,
issue the requests:

QSYS/ENDPJ SBS(QSERVER) PGM(QSYS/QZDAINIT) OPTION(*IMMED)
and
QSYS/ENDPJ SBS(QUSRWRK) PGM(QSYS/QZDASOINIT) OPTION(*IMMED)
and
QSYS/ENDPJ SBS(QUSRWRK) PGM(QSYS/QZDASSINIT) OPTION(*IMMED)

If the above commands fail with CPF0922, nothing more needs
to be done (the prestarted jobs are already inactive).

2) To re-enable the function after the PTF is applied, issue the
requests:

QSYS/STRPJ SBS(QSERVER) PGM(QSYS/QZDAINIT)
and
QSYS/STRPJ SBS(QUSRWRK) PGM(QSYS/QZDASOINIT)
and
QSYS/STRPJ SBS(QUSRWRK) PGM(QSYS/QZDASSINIT)

If the above commands fail with CPF0921, nothing more needs
to be done (the prestarted jobs are already active).

3) To re-enable the function for each subsystem "subsystem-name"
identified, issue the following requests after the PTF is
applied:

QSYS/STRPJ SBS("subsystem-name") PGM(QSYS/QZDAINIT)
and
QSYS/STRPJ SBS("subsystem-name") PGM(QSYS/QZDASOINIT)
and
QSYS/STRPJ SBS("subsystem-name") PGM(QSYS/QZDASSINIT

ENDPJ SBS(QUSRWRK) PGM(QSYS/QZDASOINIT)
STRPJ SBS(QUSRWRK) PGM(QSYS/QZDASOINIT)


Default Instructions

THIS PTF CAN BE APPLIED IMMEDIATE OR DELAYED.



Supersedes

PTF/FIX NO(S).  APAR TITLE LINE
--------------  ------------------------------------------------------------
   SI62107      OSP-DB-Internal Database Maintenance
   SI61057      OSP-DB-OTHER-PERFM SQL STATEMENT CONTAINING COALESCE/IFNULL
   SI60381      F/OPTIMIZER-UDFWITHDETERMINISTICBEHAVIOUR OPTIMIZER-UDF WITH
   SI59031      OSP-DB-OTHER-INCORROUT A QUERY OVER A DDS CREATED LOGICAL FI
   SI58476      OSP-DB-OTHER-F/ASSERT-T/QQQOOOINV-MSGMCH3203 SQL FAILING WIT
   SI58134      OSP-DB-OTHER Query INI Option ALLOW_EVI_ONLY_ACCESS support
   SI58131      OSP-DB-MSGSQL0443 MCH1210 When Visual Explaining a query wit
   SI58005      OSP-DB-OTHER Additional Scalar Support
   SI57952      OSP-DB-OTHER Query INI Option ALLOW_EVI_ONLY_ACCESS support
   SI57551      OSP-MSGCPF5257 SQL DUMP, ERROR MESSAGE CPF5257
   SI56556      OSP-DB - SUPPORT SHARED AND PUBLIC MEMORY POOL NAMES IN QAQQ
   SI56059      OSP-PERFM SQL PERFORMANCE PROBLEM WITH JOIN LF AFTER MIGRATI
   SI56153      OSP-DB-OTHER-LOOP A QUERY WITH NESTED NULLIF, AND MULITPLE O
   SI56073      OSP-DB-OTHER-SQL Plan Cache Event Monitor Locking Fixes
   SI55802      OSP-DB-OTHER Add Index Not Used list to VE
   SI55504      OSP-QQ-RUN Change to debug program
   SI55287      OSP-DB-MSGSQL0952 Process termination running SQL GROUP BY q
   SI54706      OSP-DB database monitor fixes for the STRDBMON HOSTVAR parm.
   SI54626      LIC-DB-PERFM Enable Pessimistic IO costing for Index Probe a
   SI54513      OSP-DB database monitor service option for the 3010 record
   SI54268      LIC-PERFM QUERIES AT R710 ARE RUNNING SLOWER UNLESS THE INLI
   SI54150      OSP-MSGCPF2189-D/TQQQOOOPLNC ATTEMPTING TO RUN VISUAL EXPLAI
   SI53601      OSP-DB-OTHER Add QRO Hash to Visual Explain.
   SI53206      OSP-DB-RUN Add Error Handling During Plan Cache Snapshot
   SI53089      OSP-DB SOME FUNCTIONS IN VISUAL EXPLAIN MAY FAIL IF AN INDEX
   SI53077      OSP-DB-RUN Plan Cache Snapshot Does Not Default to QTEMP
   SI52856      OSP-DB-OTHER-INCORROUT SUBSTITUTION CHARACTER X'00' USED INS
   SI51645      LIC-DB-PERFM Excessive optimization time when MQTs enabled
   SI51376      OSP-DB Visual Explain, Not able to explain a query with very
   SI51183      OSP-DB MSGMCH3203 X/1720 F/DBPMTRAP X/0009D8 T/ QQQOOODBOP M
   SI50961      OSP-DB Internal Maintenance
   SI50955      OSP-DB-OTHER-F/DBPMTRAP-T/QQQOOODBOP-RCX'1720'-MSGMCH3203 WI
   SI50503      OSP-DB-OTHER-INCORROUT OMNIFIND SOME DOCUMENTS NOT INDEXED
   SI49824      OSP-WAIT EVI WITH INCLUDE CAUSES JOBS TO HANG
   SI41826      OSP-DB-OTHER-INCORROUT SQLRPGLE PROGRAM SHOWS INCORRECT ACTI
   SI49243      SQL-DB-PERFM Index advise for ORed predicates
   SI49074      OSP-DB-OTHER-F/DBPMTRAPX/06C8-T/QQQOOODBOPX/4474-MSGMCH3203
   SI48798      OSP-Don't send a 3006 DB Monitor record if the plan is not o
   SI48498      OSP-DB *FC with Extra AnyOf on Query with Order By and Group
   SI48144      OSP-DB INTERNAL MAINTENANCE
   SI48066      OSP-DB-OTHER Changes to Parallel_Degree *OPTIMIZE percentage
   SI48031      OSP-DB-OTHER-F/DBPMTRAP-T/QQQOOODBOP-MSGMCH3203 MSGMCH3203 F
   SI47914      OSP-DB Job Terminates with AnyOf on Query with GROUPING in s
   SI47802      OSP-DB *FC with Extra AnyOf on Query with Order By and Group
   SI47530      OSP-DB-PERFM DataBase Monitor performance problems
   SI47054      OSP-DB-OTHER-INCORROUT WHEN "ORDER BY ORDER OF" IS USED THE
   SI47046      OSP-DB-PERFM Handle packed data that is incorrect
   SI47044      OSP-DB Visual Explain, Not able to explain a query access pl
   SI46481      LIC-DB-PERFM LONG OPTIMIZATION TIMES WITH HIGH NUMBER OF SEL
   SI46391      OSP-DB-OTHER MOVING DERIVED INDEXES FROM 1 LIBRARY TO ANOTHE
   SI46354      OSP-DB-MSGSQL0104 XMLTABLE support
   SI46087      OSP-DB-OTHER-F/DBPMTRAP-T/QQQOOODBOP-MSGMCH3203 MSGMCH320 F/
   SI45835      OSP-DB MCH3203 X/1720 F/DBPMTRAP X/0009B8 T/QQQOOODBOP MODUL
   SI45826      LIC-DB-OTHER Wrong host variables in VE diagram when PSEUDO_
   SI45747      LIC-DB-OTHER SQE Optimizer support for Workload Group
   SI45189      OSP-DB-OTHER-INCORROUT CAST RETURNED INCORRECT RESULT AGAINS
   SI45358      OSP-DB-MSGMCH3203 - Update of table with LOB and fieldproc
   SI45076      OSP-DB Database Monitor host variable value = 222.2
   SI44956      OSP-DB Internal Maintenance
   SI44837      OSP-DB-MSGMCH3402 query with Connectby
   SI44837      OSP-DB SQE Null s/o logical support
   SI44827      OSP-DB Query open exit program changes
   SI44820      OSP-DB-OTHER-F/QQQOOODBOP-T/QQQOOODBOP-MSGMCH3601 OSP-DB-OTH
   SI44529      OSP-DB Database Monitor host variable value = Infinity
   SI44455      OSP-DB-OTHER ACCESS PLAN CHANGES
   SI44455      OSP-DB-MSGMCH3203 f/DbpmTrap t/QQQOOODBOP
   SI44252      LIC-DB-PERFM Add PSEUDO_OPEN_CHECK_HOST_VARS QAQQINI option
   SI44137      OSP-DB-MSGSQL0511 SQL SELECT with FOR UPDATE clause and defl
   SI43996      OSP-DB Internal Maintenance for Database Monitor
   SI43996      OSP-DB Internal Maintenance for Database Monitor
   SI44149      OSP-DB-OTHER-T/QQQOOODBOP-MSGMSG3203 SQL FAILING WITH MSGMCH
   SI43615      OSP-DB-OTHER-F/QQQOOODBOP-T/QQQOOODBOP-MSGMCH3402 UPDATE BEF
   SI42908      OSP-DB-INCORROUT - FieldProc program not called while doing
   SI43456      OSP-DB Change the s/o query processing to not build duplicat
   SI43423      OSP-DB-Database Maintenance
   SI42964      OSP-DB Connect by  support
   SI42800      OSP-DB Excessive optimization for nested UDFs that were inli
   SI42748      OSP-DB-OTHER-INCORROUT INCORROUT WHEN USING SO JOIN LOGICAL
   SI42732      OSP-DB-OTHER-F/QQURB-T/QQURA-MSGCPD4019 CPD4019 WITH SQL PAR
   SI42443      OSP-DB Internal maintenance
   SI42326      OSP-DB Internal maintenance
   SI42232      OSP-INCORROUT WRONG OUTPUT WITH JOIN SELECT/OMIT LOGICAL FIL
   SI42193      OSP-DB SQE Support of Multi dataspace logical
   SI42076      OSP-DB-MSGMCH3601-f/QQQOOODBOP inlining udf with case statem
   SI41935      OSP-DB Internal maintenance
   SI41904      OSP-DB SQE Support of Logical Files - no more service option
   SI41848      OSP-DB Internal maintenance
   SI41801      OSP-DB-OTHER-F/QQQVAP-T/QQQVAP-MSGMCH1210 SQL CCSID CONVERSI
   SI41581      OSP-DB-MSGMCH1884 from #dbenfcn on DELETE
   SI41541      OSP-DB Internal maintenance
   SI41493      OSP-DB-MSGMCH3203 t/QQQOOODBOP SQL MERGE
   SI41493      OSP-DB-INCORROUT Query of logical file with CONCAT
   SI41489      OSP-DB Internal maintenance
   SI41358      OSP-DB OVRDBF function to support buffer length for query
   SI41292      OSP-DB Internal Maintenance
   SI41213      OSP-DB Internal maintenance
   SI41192      OSP-DB Internal Maintenance
   SI38741      OSP-DB-MSGMCH3601 in QQQOOOQSYS
   SI37432      OSP-DB-MSGMCH3402 f/QDMCLOSE on ALTER of table with index
   SI41016      OSP-DB-UNPRED Too many Final Calls for Final Call UDFs
   SI40964      OSP-DB inlining udfs with parameters
   SI40945      OSP-DB SQE Support of Multi dataspace logicals
   SI40806      OSP-DB-OTHER Enable in memory files
   SI40795      OSP-DB-OTHER-F/QDBDLTFI-T/QSQBAS2-MSGCPF3220 MSGCPF3220 F/QD
   SI40748      OSP-DB-SQL Varchar_format & Timestamp_format additional Form
   SI40428      OSP-DB Visual Explain showing incorrect host variable values
   SI40428      OSP-DB SQE Support of Multi dataspace logicals
   SI40311      OSP-DB SQE Support of Multi dataspace logicals
   SI40211      OSP-DB Database monitor internal maintenance.
   SI40093      OSP-DB-MSGSQL0811 using SQL VALUES clause
   SI40038      OSP-DB-SQL-MSGMCH3203  Queries with GROUP BY
   SI39793      OSP-DB-INCORROUT Visual Explain of V5R4 DBMON
   SI39728      OSP-DB-MSGMCH3203 F/QQQOOOUPCL T/QQQOOOUPCL WHEN INVOKING UD
   SI39558      OSP-DB-MSGMCH3203  Fix grouping query with ICU NLSS
   SI39395      OSP-DB-MSGMCH3601  Fix grouping sets problems
   SI39315      OSP-DB Performance enhancement for SQL MERGE statement
   SI39310      OSP-DB-MSGMCH3203  Fix grouping sets problems
   SI39304      OSP-DB-OTHER-INCORROUT
   SI39266      OSP-DB-OTHER VISUAL EXPLAIN IMPROVEMENT
   SI39223      OSP-DB-MSGCPF9898 unexpected CPF9898 when running MERGE
   SI39166      OSP-DB EVI with include recovery
   SI39020      OSP-DB-MSGMCH1819 CQE queries using fieldprocs
   SI38648      SQL-RUN PERF Dispatcher changes
   SI38605      OSP-DB Internal Maintenance
   SI38549      Query a partitioned table in a subquery cause failures.  Mos
   SI38529      OSP-DB-INCORROUT CEILING and FLOOR scalar functions
   SI38419      OSP-DB-MSGMCH1884 Delete from Partitioned Table
   SI38259      OSP-DB-SQL-INCOROUT null value returned for the expression o
   SI38061      OSP-DB-OTHER-F/QQQQUERY-RC6-MSGMSGCPD438C STROBJCVN ON VALID
   SI37960      OSP-DB-OTHER-PERFM OPTIMIZER WILL NOT LOOK AT ALL THE MQTS,
   SI37960      OSP-DB-OTHER-PERFM MQT WILL GET INVALIDATED WHEN IT DOESN' T
   SI37847      OSP-DB-SQL   QAQQINI option ALLOW_ARRAY_VALUE_CHANGES not us
   SI37805      OSP-DB Incorrect result set with partitioned EVI
   SI37785      LIC-DB-SQL Add long index schema to dbmon
   SI37736      OSP-DB-MSGMCH1210 f/t QQQSVRTN/QQINVUDF/IMPLEMENTUDTFINSQE
   SI37397      OSP-DB-OTHER-PERFM NEW QAQQINI OPTION, DETERMINISTIC_UDF_SCO
   SI37194      OSP-DB-F/QQQSRVE1-MSG1210 Date/Time/Timestamp with UNNEST Fu
   SI37149      OSP-DB Longer Schema Support
   SI37127      OSP-DB Internal mainenance for database monitor
   SI36933      OSP-DB-INCORROUT Data mapping errors unexpected for query
   SI36832      OSP-DB-MSGMCH3203 Function check on grouping set query
   SI36464      OSP-DB-MSGMCH3601 f/#cfochkr t/QDBUDR
   SI36464      OSP-DB Incorrect data from logical file cast date to char
   SI52739      OSP-DB-RUN Plan Cache Event Monitor Cannot be Ended
   SI52739      OSP-DB-OTHER-F/RMSLLOCKMIOBJECT-T/QDBOPEN-MSGMCH5802 QDBSRV
   SI52647      OSP-DB-OTHER Change Plan Cache Snapshots creation to avoid j
   SI51409      OSP-DB-Other Enable additional Plan Cache Properties and opt
   SI48867      OSP-DB-OTHER-THREADS-PERFM Capture Job Usage Information in
   SI45979      OSP-DB-OTHER-F/QSRSVPRE-T/QSRSVPRE-MSGCPF3719 A MSGCPF3719 M
   SI45191      OSP-DB-OTHER QQQOOOCACH UPDATES
   SI44638      OSP-UNPRED PLAN CACHE EVENT MONITOR GENERATING MANY VLOGS
   SI43978      LIC-DB-OTHER SQL OPTIMIZER TRACE IMPROVEMENTS
   SI43424      OSP-OTHER-THREADS-F/QCLRTVDA-T/USRPGM-MSGCPF180B ACTIVE THRE
   SI43116      LIC-DB-OTHER Save Plan Cache size changes across IPL
   SI40811      OSP-DB Internal Maintenance
   SI39586      OSP-DB VRM not set correctly on SQL Plan Cache Snapshot
   SI37052      OSP-SQ-Run support on demand reoptimization of query plan

Summary Information

System..............................i
Models..............................
Release.............................V7R1M0
Licensed Program...............5770SS1
APAR Fixed..........................View details for APAR SE65892
Superseded by:......................View fix details for PTF SI68374
Recompile...........................N
Library.............................QSYS
MRI Feature ........................NONE
Cum Level...........................C7192710


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

[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.1.0"},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG15V","label":"PTF Cover Letters - OS\/400 General"},"Component":"","ARM Category":[],"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"V7R1M0","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
17 November 2016