1ACF0C038 ACF2 LOGONID ATTRIBUTES HAVE REPLACED DEFAULT USER ATTRIBUTES READY %HOLDDATA WDB2I.R.DB2101S.SMPPTS IKJ56247I FILE LNKLST NOT FREED, IS NOT ALLOCATED UK73587 HOLD DATA START UK73587 HOLD DATA END UK74491 HOLD DATA START ++ HOLD(UK74491) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12046) COMMENT (***Action for PM53780: A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM53780 corrects a problem in which incorrect statistics are used for a DGTT or CGTT when the table is empty. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK73797) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12046) COMMENT (***Action for PM50507: See PM50507 APAR/PTF text for additional information about why a REBIND is necessary. PM50507 corrects a problem of the DB2 optimizer in calculating the cost of hash access for a hash-organized table. The fix will get a more accurate estimation of hash access, and enable the DB2 optimizer to choose hash access methods over other suboptimal method(s). To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK72787) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12046) COMMENT (A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM45292 corrects a problem in which a DB2 intentional abend may occur when a query contains the RID function. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK74491 HOLD DATA END 1UK74560 HOLD DATA START ++ HOLD(UK74560) SYS FMID(HDBAA10) REASON(ACTION) DATE(11341) COMMENT (This PTF fixes an 0C4 abend in the user-defined function DSNADM.ADMIN_TASK_STATUS(MAX_HISTORY) when it is processing a DB2 scheduler task that has multiple execution statuses. *************************************************************** Actions for customers who are already using DB2 V10: *************************************************************** *********************************************************** * This action is required for all V10 customers *********************************************************** Run your customized copy of job DSNTIJRT with MODE(INSTALL) to alter the scratchpad length of the modified user-defined function DSNADM.ADMIN_TASK_STATUS(MAX_HISTORY) from 16 to 8208 and to bind this function's DBRM. If you have previously run DSNTIJRT, rerunning with MODE(INSTALL) will cause it to detect and correct only missing and downlevel SQL objects and packages for DB2-supplied routines. Note: Use MODE(INSTALL-PREVIEW) to obtain a report of any changes without processing them. The PREVIEW option will also generate and output a JCL job to the JCLOUT DD that contains any SQL and bind statements to be processed. After reviewing the changes, either rerun DSNTIJRT without the PREVIEW option or customize and run the generated job.). UK74560 HOLD DATA END UK75539 HOLD DATA START ++ HOLD(UK75539) SYS FMID(HDBAA10) REASON(ACTION) DATE(12066) COMMENT (***Action for PM53303 (DB2 10 for z/OS) This PTF modifies the DB2 scheduler for administrative tasks so that it can purge the job output of a JCL task it executed on z/OS 1.13. The following post-apply actions are required of all current DB2 10 for z/OS customers to activate the changes in this PTF: (1) Stop the DB2 scheduler for administrative tasks. (2) Start the DB2 scheduler for administrative tasks. Each of these actions is detailed below. (1) If the DB2 scheduler is running, stop it first by executing the following console command: MODIFY DSNADMT,APPL=SHUTDOWN where DSNADMT is the name of the scheduler. (2) To start the DB2 scheduler again, execute the following console command: START DSNADMT where DSNADMT is the name of the scheduler.). UK75539 HOLD DATA END 1 UK75943 HOLD DATA START UK75943 HOLD DATA END UK75949 HOLD DATA START ++ HOLD(UK75949) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12036) COMMENT (***Action for PM50451: See PM50451 APAR/PTF text for additional information about why a REBIND is necessary. PM50451 corrects a problem of ABEND04E RC00E72018 at CSECT DSNXSBUC offset 3E7A may happen when running query with groupby clause and parallelism enabled. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK72877) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12036) COMMENT (***Action for PM45209: See PM45209 APAR/PTF text for additional information about why a REBIND is necessary. PM45209 corrects a problem of incorrect output on query containing aggregate function distinct w/o group by and running with dpsi index and parallelism. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK71915) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12036) COMMENT (***Action for PM43740: See PM43740 APAR/PTF text for additional information about why a REBIND is necessary. PM43740 corrects a problem of ABEND04E RC00E70005 in DSNXGDT2 M201 may occur when one of the following condition met: 1. a UNION/INTERSECT/EXCEPT query with COUNT COLUMN function, MIN/MAX aggregate function in the selection list and the input of the COUNT COLUMN function, MIN/MAX aggregate function has FIELD PROC defined on it running in parallel. 2. Or a UNION ALL/INTERSECT ALL/EXCEPT ALL query with ORDER BY clause and COUNT COLUMN function, MIN/MAX aggregate function in the selection list. Also,the input of the COUNT COLUMN function, MIN/MAX aggregate function has FIELD PROC defined on it running in parallel. 3. Or a query with ORDER BY clause and a COUNT COLUMN function, 1 MIN/MAX aggregate function in the selection list. Also, the input of the COUNT COLUMN function, MIN/MAX aggregate function has FIELD PROC defined on it running in parallel. 4. Or a SELECT DISTINCT statement with COUNT COLUMN function, MIN/MAX aggregate function in the selection list. Also, the input of the COUNT COLUMN function, MIN/MAX aggregate function has FIELD PROC defined on it running in parallel. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK71922) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12036) COMMENT (***Action for PM39444: See PM39444 APAR/PTF text for additional information about why a REBIND is necessary. PM39444 corrects a problem of ABEND04E RC00E72018 at DSNXSTSE offset 0A9C may occur when a query contains a column function in the selection list and its result data type is a LOB running in parallel. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK68830) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12036) COMMENT (***Action for PM36118: See PM36118 APAR/PTF text for additional information about why a REBIND is necessary. PM36118 corrects a problem of ABEND0C4 RC00000011 DSNXRSGB offset 5E5C may occur when view has duplicate group by columns To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK75949 HOLD DATA END UK76112 HOLD DATA START UK76112 HOLD DATA END UK76162 HOLD DATA START ++ HOLD(UK76162) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12044) COMMENT (***Action for PM49443: See PM49443 APAR/PTF text for additional information about 1 why a REBIND is necessary. PM49443 allows LIKE predicates with the UPPER scalar function to become indexable. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK73601) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12044) COMMENT (***Action for PM48939: See PM48939 APAR/PTF text for additional information about why a REBIND is necessary. PM48939 corrects a problem of incorrect output may occur when a pair-wise join query uses a multi-column index between dimension table and Fact table and there is a Fact table local predicate covered by the same multi-column index. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK76162 HOLD DATA END UK76249 HOLD DATA START UK76249 HOLD DATA END UK76309 HOLD DATA START ++ HOLD(UK76309) SYS FMID(HDBAA10) REASON(ACTION) DATE(12047) COMMENT (The PTF for APAR PM38329 has modified CSECTs in both the DSNUT101 and DSNLDTML load modules, affecting the utility batch and DB2 DDF address spaces. After the PTF has been applied, it will become active in the utility batch address space immediately upon job submission, while it will not take effect in the DB2 DDF address space until DB2 is stopped and started. The PTF does not have to be applied to all members in a data sharing environment simultaneously, and may be staged across each member. However, until the fix is active in both the utility batch and DB2 DDF address spaces, the problem the PTF addresses will not be resolved.). UK76309 HOLD DATA END UK76370 HOLD DATA START UK76370 HOLD DATA END UK76480 HOLD DATA START 1++ HOLD(UK75592) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12059) COMMENT (***Action for PM53062: A rebind is necessary to rebuild package and/or plan structures based on the changes in this APAR. PM53062 corrects a problem that incorrect output may be returned by a query with GROUP BY clause and a set function with DISTINCT. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK70468) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12059) COMMENT (***Action for PM38744: A rebind is necessary to rebuild package and/or plan structures based on the changes in this APAR. PM38744 corrects a problem of inaccurate cost estimation for an index on the inner table in a join. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK76480 HOLD DATA END UK76615 HOLD DATA START UK76615 HOLD DATA END UK76645 HOLD DATA START UK76645 HOLD DATA END UK76698 HOLD DATA START UK76698 HOLD DATA END UK76782 HOLD DATA START ++ HOLD(UK76782) SYS FMID(HDBAA10) REASON(DOC) DATE(12062) COMMENT (The "RACF Access Control Module Guide" (SC19-2982-01) is updated with the changes below. Chapter 14: RACF authorization checking reference - In "System privileges" section, a note is added for SYSDBADM. SYSDBADM 1 XAPLPRIV value: DB2AAUTHU Privcode 287 (x'11F') The user must have sufficient authority to: One of these resources: In class: ------------------------------------------------------------ DB2-subsystem.SYSDBADM MDSNSM or GDSNSM DB2-subsystem.SYSCTRL DSNADM DB2-subsystem.SYSADM DSNADM Note: DB2® turns on bit 7 of the XAPLFLG1 field for a user table that includes user defined data type or user defined function. If this bit is on, the RACF® access control module bypasses checking for the SYSCTRL authority.). UK76782 HOLD DATA END UK76808 HOLD DATA START UK76808 HOLD DATA END UK76865 HOLD DATA START UK76865 HOLD DATA END UK76986 HOLD DATA START UK76986 HOLD DATA END UK77001 HOLD DATA START UK77001 HOLD DATA END UK77003 HOLD DATA START ++ HOLD(UK77003) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12072) COMMENT (***Action for PM55479: See PM55479 APAR/PTF text for additional information about why a REBIND is necessary. PM55479 corrects a problem that DB2 incorrectly handles the length of histogram statistics of a DECIMAL column. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK74866) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12072) COMMENT (***Action for PM50516: A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. 1 PM50516 corrects a problem in which an incorrect compound filter factor is computed when there is data skew and that data skew is represented by frequency statistics. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK77003 HOLD DATA END UK77007 HOLD DATA START UK77007 HOLD DATA END UK77039 HOLD DATA START UK77039 HOLD DATA END UK77097 HOLD DATA START UK77097 HOLD DATA END UK77119 HOLD DATA START UK77119 HOLD DATA END UK77130 HOLD DATA START ++ HOLD(UK77130) SYS FMID(HDBAA10) REASON(DOC) DATE(12076) COMMENT (Implicitly created XML table space names will be Xyyynnnn, where yyy is the first three bytes of the base table name. With PM54014, if the table name is shorter than three bytes, the implicitly created XML table space name will be padded with 'X' instead of '#'. For example,if we create table named 'T' which included an XML column, the implicitly created XML table space will be named XTXX0000 instead of XT##0000. This apar will not affect existing XML table space names. The approximate text is to be added to the DB2 SQL Reference manual. This text is subject to change. CREATE TABLE Naming convention for implicitly created XML objects: Implicitly created XML table space names will be Xyyynnnn, where yyy is derived from the first three bytes of the base table name (if the name is shorter than 3, yyy is padded | with X ). nnnn is a numeric string that will start at 0000 and be incremented by 1 until a unique number is found.). UK77130 HOLD DATA END UK77170 HOLD DATA START 1++ HOLD(UK77170) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12079) COMMENT (See PM40724 APAR/PTF text for additional information about why a REBIND is necessary. APAR PM38326 is the necessary pre-conditioning apar for this APAR. PM40724 corrects the problem of an SQLCODE -171 issued for a query that references the STRIP built-in function containing a single multi-byte mixed data strip-character. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK77170) SYS FMID(HDBAA10) REASON(MULTSYS) DATE(12079) COMMENT (PM40724 is an APAR that includes changes to support functional extensions to the existing built-in functions and to introduce a new built-in function that will be enabled once this APAR is applied in DB2 NFM (New Function Mode). Earlier, pre-conditioning APAR PM38326 was delivered. In a data sharing group, the pre-conditioning APAR should be applied to all members before applying this enabling APAR to any member. The code change is enabled once this enabling APAR is applied. The functional extensions to the following existing built-in functions are enabled by APAR PM40724. 1. LTRIM 2. RTRIM 3. REPLACE 4. ROUND 5. STRIP 6. TRUNCATE The new built-in function called, TRIM, is introduced by APAR PM40724. This APAR also corrects the problem of an SQLCODE -171 issued for a query that references the STRIP built-in function containing a single multi-byte mixed data strip-character.). ++ HOLD(UK77170) SYS FMID(HDBAA10) REASON(DOC) DATE(12079) COMMENT (See PM40724 APAR/PTF text for additional information. PM40724 introduces the following changes to externals: LTRIM The LTRIM function removes bytes from the beginning of a string expression based on the content of a trim expression. >>--LTRIM--(--string-expression---+--------------------+-)->< '-,--trim-expression-' 1 The LTRIM function removes all of the characters that are contained in trim-expression from the beginning of string-expression. The search is done by comparing the binary representation of each character (which consists of one or more bytes) in trim-expression to the bytes at the beginning of string-expression. If the string-expression is defined as FOR BIT DATA, the search is done by comparing each byte in trim-expression to the byte at the beginning of string-expression. string-expression An expression that specifies the source string. The argument must be an expression that returns a value that is a built-in string data type that is not a LOB, or a numeric data type. If the value is not a string data type, it is implicitly cast to VARCHAR before the function is evaluated. If string-expression is not FOR BIT DATA, trim-expression must not be FOR BIT DATA. trim-expression An expression that specifies the characters to remove from the beginning of string-expression. The expression must return a value that is a built-in string data type that is not a LOB, or a numeric data type. If the value is not a string data type, it is implicitly cast to VARCHAR before the function is evaluated. The default for trim-expression depends on the data type of string-expression: A DBCS blank if string-expression is a DBCS graphic string. For ASCII, the CCSID determines the hex value that represents a DBCS blank. For example, for Japanese (CCSID 301), X'8140' represents a DBCS while for Simplified Chinese, X'A1A1' represents a DBCS blank. For EBCDIC, X'4040' represents a DBCS blank. A UTF-16 or UCS-2 blank (X'0020') if string-expression is a Unicode graphic string. A value of X'00' if string-expression is a binary string. Otherwise, a single byte blank. For EBCDIC, X'40' represents a blank. If not EBCDIC, X'20' represents a blank. string-expression and trim-expression must have compatible data types. If string-expression and trim-expression have different CCSID sets, trim-expression is converted to the CCSID of string-expression. The result of the function depends on the data type of string-expression. 1 VARCHAR if string-expression is a character string. If string-expression is defined as FOR BIT DATA the result is FOR BIT DATA. VARGRAPHIC if string-expression is a graphic string. VARBINARY if string-expression is a binary string. The length attribute of the result is the same as the length attribute of string-expression. The actual length of the result for a character or binary string is the length of string-expression minus the number of bytes that are removed. The actual length of the result for a graphic string is the length (in the number of double byte characters) of string-expression minus the number of double byte characters removed. If all of the characters are removed, the result is an empty string (the length is zero). If any argument can be null, the result can be null; if any argument is null, the result is the null value. The CCSID of the result is the same as that of string-expression Refer to LTRIM section in the DB2 10 for z/OS SQL Reference for more information. RTRIM The RTRIM function removes bytes from the end of a string expression based on the content of a trim expression. >>--RTRIM--(--string-expression---+--------------------+-)->< '-,--trim-expression-' The RTRIM function removes all of the characters contained in trim-expression from the end of string-expression. The search is done by comparing the binary representation of each character (which consists of one or more bytes) in trim-expression to the bytes at the end of string-expression. If the string-expression is defined as FOR BIT DATA, the search is done by comparing each byte in trim-expression to the byte at the end of string-expression. string-expression An expression that specifies the source string. The argument must be an expression that returns a value that is a built-in string data type that is not a LOB, or a numeric data type. If the value is not a string data type, it is implicitly cast to VARCHAR before the function is evaluated. If string-expression is not FOR BIT DATA, trim-expression must 1 not be FOR BIT DATA. trim-expression An expression that specifies the characters to remove from the end of string-expression. The expression must return a value that is a built-in string data type that is not a LOB, or a numeric data type. If the value is not a string data type, it is implicitly cast to VARCHAR before the function is evaluated. The default for trim-expression depends on the data type of string-expression: A DBCS blank if string-expression is a DBCS graphic string. For ASCII, the CCSID determines the hex value that represents a DBCS blank. For example, for Japanese (CCSID 301), X'8140' represents a DBCS while for Simplified Chinese, X'A1A1' represents a DBCS blank. For EBCDIC, X'4040' represents a DBCS blank. A UTF-16 or UCS-2 blank (X'0020') if string-expression is a Unicode graphic string. A value of X'00' if string-expression is a binary string. Otherwise, a single byte blank. For EBCDIC, X'40' represents a blank. When not EBCDIC, X'20' represents a blank. string-expression and trim-expression must have compatible data types. If string-expression and trim-expression have different CCSID sets, trim-expression is converted to the CCSID of string-expression. The result of the function depends on the data type of string-expression. VARCHAR if string-expression is a character string. If string-expression is defined as FOR BIT DATA the result is FOR BIT DATA. VARGRAPHIC if string-expression is a graphic string. VARBINARY if string-expression is a binary string. The length attribute of the result is the same as the length attribute of string-expression. The actual length of the result for a character or binary string is the length of string-expression minus the number of bytes removed. The actual length of the result for a graphic string is the length (in number of double byte characters) of string-expression minus the number of double byte characters removed. If all of the characters are removed, the result is an empty string (the length is zero). 1 If any argument can be null, the result can be null; if any argument is null, the result is the null value. The CCSID of the result is the same as that of string-expression Refer to RTRIM section in the DB2 10 for z/OS SQL Reference for more information. REPLACE >>-REPLACE-(-source-string-,-search-string-+-----------------+-> ',-replace-string-' >-)->< replace-string An expression that specifies the replacement string. The expression must return a value that is a built-in character string, graphic string, or binary string data type that is not a LOB. The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type. If replace-string is not specified or is an empty string, nothing replaces the string that is removed from the source string. Refer to REPLACE section in the DB2 10 for z/OS SQL Reference for more information. ROUND .-,--0-------------------. >>-ROUND-(--numeric-expression-1-+------------------------+-)->< '-,-numeric-expression-2-' Refer to ROUND section in the DB2 10 for z/OS SQL Reference for more information. TRUNCATE or TRUNC .-,--0------------------. >>-+TRUNCATE-+-(-numeric-expression1-+-----------------------+-> '-TRUNC---' '-,-numeric-expression2-' >-)->< Refer to TRUNCATE section in the DB2 10 for z/OS SQL Reference for more information. STRIP 1 The STRIP function is similar to the TRIM scalar function. Refer to TRIM section in the DB2 10 for z/OS SQL Reference for more information. TRIM The TRIM function removes bytes from the beginning, from the end, or from both the beginning and end of a string expression. >>--TRIM--(--+----------------------------------------+--> | .-BOTH-----. | '-+----------+--+----------------+--FROM-' |-B--------| '-trim-constant--' |-LEADING--| |-L--------| |-TRAILING-| '-T--------' >--string-expression---)-------------------------------->< The schema is SYSIBM. The first argument, if specified, indicates whether characters are removed from the end or the beginning of the string. If the first argument is not specified, the characters are removed from both the end and the beginning of the string. trim-constant Specifies a constant that indicates the binary, SBCS, or DBCS character that is to be removed. If string-expression is a character string, trim-constant must be an SBCS or DBCS single-character (2 bytes) constant. If string-expression is a binary string, trim-constant must be a single-byte binary string constant. If string-expression is a DBCS graphic or DBCS-only string, trim-constant must be a graphic constant that consists of a single DBCS character. The default for trim-constant depends on the data type of string-expression: A DBCS blank if string-expression is a DBCS graphic string. For ASCII, the CCSID determines the hex value that represents a DBCS blank. For example, for Japanese (CCSID 301), X'8140' represents a DBCS while for Simplified Chinese, X'A1A1' represents a DBCS blank. For EBCDIC, X'4040' represents a DBCS blank. A UTF-16 or UCS-2 blank (X'0020') if string-expression is a Unicode graphic string. A value of X'00' if string-expression is a binary string. Otherwise, a single byte blank. For EBCDIC, X'40' represents 1 a blank. When not EBCDIC, X'20' represents a blank. string-expression An expression that returns a value that is a built-in character string data type, graphic data type, binary string data type, or numeric data type. string-expression must not be a LOB. If string-expression is numeric, it is cast to a character string before the function is evaluated. For more information about converting numeric to a character string, see VARCHAR. string-expression and trim-expression must have compatible data types. The data type of the result depends on the data type of string-expression: If string-expression is a character string data type, the result is VARCHAR. If string-expression is defined as FOR BIT DATA the result is FOR BIT DATA. If string-expression is a graphic string data type, the result is VARGRAPHIC. If string-expression is a binary string data type, the result is VARBINARY. The length attribute of the result is the same as the length attribute string-expression. The actual length of the result is the length of string-expression minus the number of characters removed. If all of the characters are removed, the result is an empty string. If string-expression can be null, the result can be null; if string-expression is null, the result is the null value. The CCSID of the result is the same as that of string-expression Example: Assume the host variable HELLO of type CHAR(9) has a value of 'Hello '. SELECT TRIM(:HELLO), TRIM(TRAILING FROM :HELLO) FROM SYSIBM.SYSDUMMY1 Results in 'Hello' and ' Hello' respectively. Example: Assume the host variable BALANCE of type CHAR(9) has a value of '000345.50'. SELECT TRIM(L '0' FROM :BALANCE) FROM SYSIBM.SYSDUMMY1 Results in '345.50' Refer to TRIM section in the DB2 10 for z/OS SQL Reference.). 1++ HOLD(UK77170) SYS FMID(HDBAA10) REASON(ACTION) DATE(12079) COMMENT (This PTF corrects the problem of an SQLCODE -171 issued for a query that references the STRIP built-in function containing a single multi-byte mixed data strip-character. If after applying both apars PM38326 and PM40724, the failing query still returns an SQLCODE -171 or for a static query a rebind does not resolve the problem, one or more of the following actions may need to be performed depending on the makeup of the query. 1. Views. Regenerate views using the ALTER VIEW REGENERATE statement. 2. Materialized query tables (MQT). Drop and recreate the MQTs. 3. SQL table UDFs. Drop and recreate the UDFs. 4. SQL scalar UDFs. Regenerate functions using the ALTER FUNCTION REGENERATE. statement. 5. SQL native procedures. Regenerate procedures using the ALTER PROCEDURE REGENERATE statement. 6. Row permissions and column masks. Regenerate using the ALTER PERMISSION REGENERATE or ALTER MASK REGENERATE statement. 7. Indexes with key expressions. Regenerate indexes using the ALTER INDEX REGENERATE statement.). UK77170 HOLD DATA END UK77227 HOLD DATA START UK77227 HOLD DATA END UK77248 HOLD DATA START ++ HOLD(UK77248) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12085) COMMENT (***Action for PM56084: See PM56084 APAR/PTF text for additional information about 1 why a REBIND is necessary. Please note that the second problem addressed by PM56084 is required to have the static application rebound. Incorrect output can occur when a not NULL XML column is fetched. DB2 does not set the nullability information properly at bind time which leads to an incorrect value written for the NULL byte when retrieving a not NULL XML column for the JCC Type 2 Driver with Limited Block Fetch. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK69995) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12085) COMMENT (***Action for PM38688: See PM38688 APAR/PTF text for additional information about why a REBIND is necessary. PM38688 corrects a problem of incorrect out when query contains EXCEPT and UNION To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK77248 HOLD DATA END UK77298 HOLD DATA START UK77298 HOLD DATA END UK77336 HOLD DATA START UK77336 HOLD DATA END UK77343 HOLD DATA START ++ HOLD(UK77343) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12088) COMMENT (***Action for PM56355: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM56355 corrects a problem that DB2 may favor regular index scan over range list index scan while range list index scan is a more efficient access method for a query with data-dependent pagination. To make this fix effective for a static application, it must be 1 rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK76457) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12088) COMMENT (***Action for PM55976: A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM55976 corrects a problem in which the most appropriate index only access may not be selected. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK75265) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12088) COMMENT (***Action for PM52549: See PM52549 APAR/PTF text for additional information about why a REBIND is necessary. PM52549 corrects a problem of SQLCODE=-510 when doing a BIND/REBIND on a FOR UPDATE cursor and IN-list direct table access is attempted. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK77343 HOLD DATA END UK77373 HOLD DATA START UK77373 HOLD DATA END UK77375 HOLD DATA START UK77375 HOLD DATA END UK77398 HOLD DATA START ++ HOLD(UK77398) SYS FMID(HDBAA10) REASON(DOC) DATE(12087) COMMENT (***Documentation for PM57643: See PM57643 APAR/PTF text for additional information. The following corrections are to be added to the DB2 for z/OS Messages manual for the DSNX880I message. . Under Explanation, the first occurrence of "sql-code" is corrected to be "return-code". . Under Explanation, following the list of message tokens, the following paragraph is added: If the operation is CONNECT and the return-code is 8, the 1 location name in the row that defines the accelerator in the SYSACCEL.SYSACCELERATORS table is incorrectly set to the local DB2 location. . Under System programmer response, the following paragraph is added: If the operation is CONNECT and the return-code is 8, ensure that the LOCATION column of the row that defines the accelerator in SYSACCEL.SYSACCELERATORS is a valid accelerator location name.). UK77398 HOLD DATA END UK77407 HOLD DATA START UK77407 HOLD DATA END UK77452 HOLD DATA START UK77452 HOLD DATA END UK77461 HOLD DATA START UK77461 HOLD DATA END UK77462 HOLD DATA START UK77462 HOLD DATA END UK77494 HOLD DATA START UK77494 HOLD DATA END UK77496 HOLD DATA START UK77496 HOLD DATA END UK77501 HOLD DATA START UK77501 HOLD DATA END UK77512 HOLD DATA START UK77512 HOLD DATA END UK77517 HOLD DATA START UK77517 HOLD DATA END UK77552 HOLD DATA START ++ HOLD(UK77552) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12091) COMMENT (***Action for PM59833: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. 1 PM59833 corrects a problem that DB2 may select an inefficient access path for a query with a subquery having multiple-table join when subsystem parameter OPTXQB is ON. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK76589) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12091) COMMENT (***Action for PM55941: See PM55941 APAR/PTF text for additional information about why a REBIND is necessary. PM55941 corrects a problem of a query with OPTIMIZE FOR n ROWS or FETCH FIRST n ROWS ONLY where DB2 chooses the non-correlated subquery access path instead of the correlated subquery access path. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK77552 HOLD DATA END UK77557 HOLD DATA START UK77557 HOLD DATA END UK77559 HOLD DATA START UK77559 HOLD DATA END UK77567 HOLD DATA START ++ HOLD(UK77567) SYS FMID(HDBAA10) REASON(ACTION) DATE(12093) COMMENT (***Action for PM58749 (DB2 10 for z/OS) This PTF modifies the stored procedure SYSPROC.ADMIN_COMMAND_UNIX to execute the specified z/OS UNIX System Services command in the home directory of the specified user, instead of the home directory of a user who happened to share the same UID as the specified user. Therefore, for users that called ADMIN_COMMAND_UNIX specifying a user with a unique UID, the outcome of the command is the same as before this PTF is applied. However, for users that called ADMIN_COMMAND_UNIX specifying a user with shared UIDs, the command outcome depends on whether the home directory of the shared UID is also the home directory of the specified user: o If the home directory of the shared UID is also the home directory of the specified user, the command outcome is 1 the same as before this PTF is applied. o Otherwise, the command is executed under the home directory of the specified user, instead of the home directory of the shared UID. This PTF also modifies the stored procedure SYSPROC.ADMIN_COMMAND_UNIX to allow the user to specify a default home directory in the input parameter OUTPUT_LAYOUT. Instead of aborting when the home directory of the specified user does not exist or the specified user does not have a home directory, ADMIN_COMMAND_UNIX will execute the specified command in the default home directory specified. If this happens, ADMIN_COMMAND_UNIX will return RETURN_CODE = 4 to warn the user that the default home directory is used. See the PTF cover letter for more information.). UK77567 HOLD DATA END UK77628 HOLD DATA START ++ HOLD(UK77628) SYS FMID(HDBAA10) REASON(DOC) DATE(12096) COMMENT (PM58206 introduces the following changes to externals: Changed option for utility TEMPLATE: NBRSECND integer Specifies the division of secondary space allocations. After the primary space is allocated, an amount of space equal to the estimated required space is divided into the specified | number of secondary allocations. Individual utilities may | request larger secondary extents to compensate for localized | uncertainty in the space estimations. The integer value must be in the range from 1 to 10. The default value is 10. Changed text for utility TEMPLATE: Extent allocation for disk data sets By default, for data sets on disk, 100 percent of the required space that is estimated by DB2 is allocated as a primary quantity. If this amount of space is typically not available on a single volume, specify the PCTPRIME option with a value lower than 100. Alternatively, if you want the upper limit of the primary quantity based on size instead of percentage, use the MAXPRIME option. After the primary space is allocated, a secondary quantity that is equal to the estimated required space is divided into | the specified number of secondary extents. Individual | utilities may request larger secondary extents to compensate | for localized uncertainty in the space estimations. This number is identified by the NBRSECND option. If you specify either PCTPRIME or MAXPRIME, any secondary allocation requests are limited to the size of the primary allocation.). ++ HOLD(UK77628) SYS FMID(HDBAA10) REASON(ACTION) DATE(12096) COMMENT (*** Action for PM58206 *** The UNLOAD utility estimates the space requirement for the 1 SYSREC data set based on the statistics from catalog tables. A RUNSTATS against the target table space is recommended prior to the UNLOAD to ensure the accurate estimation for space requirement.). UK77628 HOLD DATA END UK77652 HOLD DATA START UK77652 HOLD DATA END UK77691 HOLD DATA START UK77691 HOLD DATA END UK77693 HOLD DATA START ++ HOLD(UK74927) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12095) COMMENT (See PM52694 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. PM52694 fixes a query costing problem when the query has outer join and FETCH FIRST n ROWS ONLY or OPTIMIZE FOR n ROWS. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK77693 HOLD DATA END UK77697 HOLD DATA START UK77697 HOLD DATA END UK77704 HOLD DATA START UK77704 HOLD DATA END UK77705 HOLD DATA START UK77705 HOLD DATA END UK77712 HOLD DATA START ++ HOLD(UK77712) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12095) COMMENT (***Action for PM59265: See PM59265 APAR/PTF text for additional information about why a REBIND is necessary. PM59265 corrects a problem of ABEND0C4 RC00000004 IN DSNXRBND OFFSET189A To make this fix effective for a static application, it must be 1 rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK77712 HOLD DATA END UK77739 HOLD DATA START UK77739 HOLD DATA END UK77742 HOLD DATA START UK77742 HOLD DATA END UK77745 HOLD DATA START ++ HOLD(UK77745) SYS FMID(HDBAA10) REASON(ACTION) DATE(12102) COMMENT (*** Action for PM59280 *** This fix corrects a SQLCODE104 symptom observed at the server site (site2), but to correct the problem this fix must be applied to the DB2 for z/OS requester site (site1) where alias resolution is enabled. Review the PTF cover letter for further explanation of the requester (site1) applications that are affected.). UK77745 HOLD DATA END UK77746 HOLD DATA START ++ HOLD(UK73180) SYS FMID(HDBAA10) REASON(ACTION) DATE(12097) COMMENT (***Action for PM37057 (V10): This APAR/PTF provides additional enhancements to the DB2 10 for z/OS support for client access using a digital certificate. DB2 10 for z/OS remote connection authentication processing has been enhanced to ensure remote client's that accesses DB2 using a digital certificate are properly authenticated when the remote client user ID is different from the certificate user ID. To implement client access to DB2 using a digital certificate, users need to define a trusted context at the DB2 server where the system authorization ID is set to the value of the user ID associated with the client certificate. The trusted context would need to include all the user IDs that are allowed to use it (such as, remote client user IDs or the mapped user IDs if z/OS identity name filtering is configured). Example: A client certificate is registered to RACF and it is associated with user ID, CERTID01. A remote client application is configured to access DB2 using a digital certificate and its user ID provided to DB2 is USER01. To define the trusted context at the DB2 server, users might consider the following sample DDL: CREATE TRUSTED CONTEXT CTXT1 BASED UPON CONNECTION USING CERTID01 NO DEFAULT ROLE ENABLE 1 ATTRIBUTES(ADDRESS '1.2.3.4') WITH USE FOR USER01; See PM37057 APAR/PTF text for additional information.). UK77746 HOLD DATA END UK77758 HOLD DATA START ++ HOLD(UK77758) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12104) COMMENT (***Action for PM56424: A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM56424 allows DB2 to more accurately estimate atomic predicate filter factors when multiple local predicates exist on the same column. PM56424 also resolves a problem in which the null frequency was not taken into account for not equal and NOT IN predicates. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK77758 HOLD DATA END UK77770 HOLD DATA START ++ HOLD(UK77770) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12106) COMMENT (***Action for PM58932: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM58932 corrects a problem that DB2 may choose an inefficient join sequence for a query joining 3 or more tables. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK68201) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12106) COMMENT (***Action for PM34814: See PM34814 APAR/PTF text for additional information about why a REBIND is necessary. PM34814 corrects a problem that DB2 may select an inefficient join sequence for a query if a declared global temporary table is involved or statistics on join columns are not collected. To make this fix effective for a static application, it must be 1 rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK77770 HOLD DATA END UK77786 HOLD DATA START UK77786 HOLD DATA END UK77806 HOLD DATA START UK77806 HOLD DATA END UK77815 HOLD DATA START UK77815 HOLD DATA END UK77831 HOLD DATA START UK77831 HOLD DATA END UK77857 HOLD DATA START UK77857 HOLD DATA END UK77865 HOLD DATA START UK77865 HOLD DATA END UK77874 HOLD DATA START ++ HOLD(UK75434) SYS FMID(HDBAA10) REASON(ACTION) DATE(12102) COMMENT (Since the problem that occurred could leave incorrect RELEASE values in SYSIBM.SYSPACKAGE table, and the PTF only corrects problem in future rebinds, if there are packages that have been rebound prior to applying this PTF, and show a blank value in SYSPACKAGE.RELEASE column other than the intended RELEASE value, a manual REBIND with the correct RELEASE OPTION specified is required.). UK77874 HOLD DATA END UK77877 HOLD DATA START UK77877 HOLD DATA END UK77883 HOLD DATA START ++ HOLD(UK77883) SYS FMID(HDBAA10) REASON(ACTION) DATE(12108) COMMENT (***Action for PM59506 (V10) (1) A DSNTEP2 or DSNTEP4 job that uses the parameter SQLTERM to specify an invalid terminator character will need to be modified to specify a valid terminator character. Any character is valid except a blank, comma, single or 1 double quote, underscore, or parenthesis. (2) This PTF updates the source code for DSNTEP2 and DSNTEP4 plus the shipped object decks (DSNTEP2L and DSNTEP4L) and shipped DBRMs (DSN@EP2L and DSN@EP4L). These changes do not become effective until you rebuild the load modules and rebind the plans. * On DB2 V10 systems running in conversion mode (CM) or enabling-new-function mode (ENFM), no further action is required after applying this PTF. The updates will be used when you run the V10 IVP jobs after DB2 V10 enters new-function mode. * On DB2 V10 systems in new-function mode, proceed as follows: (a) If you use DSNTEP2 or DSNTEP4 (or both) as supplied by DB2, run IVP job DSNTEJ1L to relink the load modules from the shipped object modules and bind the plans from the shipped DBRMs. (b) If you use a modified version of DSNTEP2 or DSNTEP4 (or both) redo your modifications and then run IVP job DSNTEJ1P to recompile and link-edit the load modules and bind the plans for DSNTEP2 and DSNTEP4.). UK77883 HOLD DATA END UK77886 HOLD DATA START UK77886 HOLD DATA END UK77888 HOLD DATA START UK77888 HOLD DATA END UK77893 HOLD DATA START UK77893 HOLD DATA END UK77917 HOLD DATA START ++ HOLD(UK76819) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12103) COMMENT (***Action for PM55938: See PM55938 APAR/PTF text for additional information about why a REBIND is necessary. PM55938 corrects a problem of choosing an inefficient access path instead of a DPSI index access path for a query with an EXISTS subquery. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK77917 HOLD DATA END 1 UK77920 HOLD DATA START ++ HOLD(UK77920) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12104) COMMENT (***Action for PM52237 See PM52237 APAR/PTF text for additional information about why a REBIND is necessary. Apar PM52237 reduces the XML storage usage so as not to exceed the specified zparm limit. As a result, this will reduce the chance of receiving an SQLCODE904 (or SQLCODE -904), a Resource Unavailable condition with reason code rc00D50001 or rc00D50002, which means excessive storage used for XMLTABLE. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK77920 HOLD DATA END UK77951 HOLD DATA START ++ HOLD(UK72373) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12108) COMMENT (***Action for PM39078: See PM39078 APAR/PTF text for additional information about why a REBIND is necessary. PM39078 corrects a problem of missing warning message when a query which references an XML column that does not support multiple XML versions and it is bound with the USE CURRENTLY COMMITTED option. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK72373) SYS FMID(HDBAA10) REASON(DOC) DATE(12108) COMMENT (Existing warning SQLCODE +20002 is changed by this APAR/PTF. The change will be documented in the DB2 Codes, SQL Reference, Command Reference manuals respectively. The texts for SQLCODE +20002 are: +20002 THE clause SPECIFICATION IS IGNORED FOR OBJECT object-name Explanation This message is issued in response to a clause specification that was ignored on a query statement. System action The statement is processed. 1 User response To stop receiving this message, avoid ACCESSCONCURRENTRESOLUTION(UseCurrentlyCommitted) bind option for a plan or package accessing non-versioned XML, or avoid a statement (accessing non-versioned XML) prepared with "Use Currently Committed" attribute. User response needs real action: If an XML column does not support multiple XML versions, you can convert to an XML column that supports multiple XML versions by following these steps: 1. Unload the data from the table that contains the XML columns. 2. Drop the table. 3. Create the table in a universal table space. The new table supports multiple XML versions. 4. Load the data into the table. SQLSTATE 01624). UK77951 HOLD DATA END UK77953 HOLD DATA START UK77953 HOLD DATA END UK77981 HOLD DATA START UK77981 HOLD DATA END UK77987 HOLD DATA START UK77987 HOLD DATA END UK77992 HOLD DATA START UK77992 HOLD DATA END UK77998 HOLD DATA START UK77998 HOLD DATA END UK78029 HOLD DATA START UK78029 HOLD DATA END UK78034 HOLD DATA START UK78034 HOLD DATA END UK78035 HOLD DATA START ++ HOLD(UK78035) SYS FMID(HDBAA10) REASON(ACTION) DATE(12108) COMMENT (BEFORE APPLYING THIS PTF, DYNAMIC SQLS COULD GET SQLCODE -905 1 OR BE PROCESSED WITHOUT SQLCODE -905 BASED ON THE ASUTIME FROM THE ROW WITH 'PUBLIC' LUNAME EVEN WHEN A MORE QUALIFIED ROW EXISTS IN DSNRLSTxx TABLE. AFTER APPLYING THIS PTF, DYNAMIC SQLS WOULD GET SQLCODE -905 OR BE PROCESSED WITHOUT SQLCODE -905 BASED ON THE ASUTIME FROM THE ROW WITH THE MOST QUALIFIED LUNAME COLUMN VALUE IN DSNRLSTxx TABLE.). UK78035 HOLD DATA END UK78046 HOLD DATA START UK78046 HOLD DATA END UK78076 HOLD DATA START UK78076 HOLD DATA END UK78080 HOLD DATA START ++ HOLD(UK73880) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12109) COMMENT (***Action for PM47092: See PM47092 APAR/PTF text for additional information about why a REBIND is necessary. PM47092 corrects a problem of an incorrect output when an SQL statement satisfies all of the following conditions: 1. a BETWEEN predicate is referenced in the definition of one MQT; 2. an IN predicate or multiple OR predicates are referenced in in one SQL statement, and the value of some expressions from IN predicate or OR predicate exceed the value of low bound or high bound from the BETWEEN predicate in #1; 3. the SQL statement in #2 is rewritten to the MQT in #1 by DB2. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK78080 HOLD DATA END UK78114 HOLD DATA START UK78114 HOLD DATA END UK78127 HOLD DATA START UK78127 HOLD DATA END UK78133 HOLD DATA START UK78133 HOLD DATA END 1UK78148 HOLD DATA START ++ HOLD(UK78148) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12111) COMMENT (***Action for PM59659: See PM59659 APAR/PTF text for additional information about why a REBIND is necessary. PM59659 corrects a problem of ABEND04E RC00C90101 at DSNXIDM .DSNXOTFLA:5009 (in DB2 10) , ABEND04E RC00C900D0 at DSNXGRDS.DSNXROHB+9FF6(in DB2 10) or unexpected SQLCODE -404 (in DB2 9) could occur when a SQL statement references a LOB datatype and a different CCSID non LOB datatype in an UNION ALL. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK69753) SYS FMID(HDBAA10) REASON(ACTION) DATE(12111) COMMENT (This PTF fixes the problem of incorrect information could be recorded in the catalog SYSCOLUMNS for an ALTER statement that satisfies the following conditions: 1. The ALTER statement is used to modify the data type of the base table columns. 2. This base table column is used inside a view that contains UNION / UNION ALL. To make this fix effective, the view that satisfies the conditions above must be drop and recreate or regenerate with the ALTER VIEW REGENERATE statement.). UK78148 HOLD DATA END UK78170 HOLD DATA START ++ HOLD(UK76577) SYS FMID(HDBAA10) REASON(ACTION) DATE(12111) COMMENT (Please note. If you are using DATE exits, this concerns you. If you are using a DATE exit, specifically an EBCDIC, ASCII, or UNICODE DATE exit (DSNXVDTX, DSNXVDTA, or DSNXVDTU), please make sure it is functioning properly. If it is, a query referencing the TIMESTAMP built-in function should work correctly and no action is necessary. If however the exit is not working properly and the query referencing the TIMESTAMP built-in function worked in DB2 9 but is now not working in DB2 10 (i.e. it does not return the expected result), then please ensure that the exit is coded and working properly.). ++ HOLD(UK74371) SYS FMID(HDBAA10) REASON(ACTION) DATE(12111) COMMENT (The following behavior difference may be noticed with the application of this apar. Please note that this apar corrects an instance where an incorrect SQLCODE180 could be issued. Instead, an SQLCODE181 should be issued. So, any static applications that check for an SQLCODE180 (or -180) should also be checking for an SQLCODE181 1 (or -181).). UK78170 HOLD DATA END UK78174 HOLD DATA START UK78174 HOLD DATA END UK78177 HOLD DATA START UK78177 HOLD DATA END UK78180 HOLD DATA START ++ HOLD(UK78180) SYS FMID(HDBAA10) REASON(DOC) DATE(12117) COMMENT (PM58294 introduces the following changes to the DB2 10 for z/OS Utility Guide and Reference: Changed option for utility REORG: DISCARD Specifies that records that meet the specified WHEN conditions are to be discarded during REORG TABLESPACE UNLOAD CONTINUE or UNLOAD PAUSE. If you specify DISCARDDN or a SYSDISC DD statement in the JCL, discarded records are saved in the | associated data set. Otherwise, the utility discards records | without saving them in a data set. You can specify any SHRLEVEL option with DISCARD; however, if you specify SHRLEVEL CHANGE, modifications that are made during the reorganization to data rows that match the discard criteria are not permitted. In this case, REORG TABLESPACE terminates with an error. If you specify DISCARD, rows are decompressed and edit routines are decoded. If you also specify DISCARD to a file, rows are decoded by field procedure, and the following columns are converted to DB2 external format: (1)SMALLINT (2)INTEGER (3)FLOAT (4)DECIMAL (5)TIME (6)TIMESTAMP Otherwise, edit routines or field procedures are bypassed on both the UNLOAD and RELOAD phases for table spaces. Validation procedures are not invoked during either phase. Do not specify DISCARD with the UNLOAD EXTERNAL or UNLOAD ONLY option. | You cannot specify DISCARD for a base table with LOB columns | unless the discard records are less than 32k or you want to | discard records without saving them in a data set. You cannot specify DISCARD for a base table with XML columns or for an XML table space. 1 REORG TABLESPACE DISCARD cannot be specified on a system-maintained temporal table space when versioning is active. When versioning is active and DISCARD is specified, REORG TABLESPACE fails with message DSNU076I and return code 8.). ++ HOLD(UK78180) SYS FMID(HDBAA10) REASON(ACTION) DATE(12117) COMMENT (The PTF for APAR PM58294 has modified CSECTs in both the DSNUT101 and DSNUTILA load modules, affecting the utility batch and DB2 DBM1 address spaces. After the PTF has been applied, it will become active in the utility batch address space immediately upon job submission, while it will not take effect in the DB2 DBM1 address space until DB2 is stopped and started. The PTF does not have to be applied to all members in a data sharing environment simultaneously, and may be staged across each member. However, until the fix is active in both the utility batch and DB2 DBM1 address spaces, the problem the PTF addresses will not be resolved.). UK78180 HOLD DATA END UK78184 HOLD DATA START ++ HOLD(UK78184) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12111) COMMENT (See PM59276 APAR/PTF text for additional information about why a REBIND is necessary. APAR PM59276 fixes a REOPT AUTO problem that may not save the bound application correctly. As a result, later at execution time, certain fields in the structure of REOPT AUTO function contain bad values. DB2 will get ABEND when those bad fields in REOPT AUTO structure were referenced. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK78184 HOLD DATA END UK78201 HOLD DATA START ++ HOLD(UK78201) SYS FMID(HDBAA10) REASON(ACTION) DATE(12114) COMMENT (This apar does not correct rows that where inserted into the catalog for the directory objects before the apar was applied. To see if you have rows that need to be updated run the query: SELECT NAME,CREATEDTS FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = 'SYSIBM' AND TBNAME = 'SYSLGRNX' AND NAME = 'LGRDBID' AND CREATEDTS NOT IN('0001-01-01-00.00.00.000000'); 1 If this query returns rows you have rows that need to be corrected by running the utility job CATMAINT UPDATE UNLDDN PM55333 This job will invalidate packages that are dependent on the directory tables, delete the catalog rows and then insert the correct rows. If the following query returns 0 rows SELECT * FROM SYSIBM.SYSDATABASE WHERE DBID=1; The rows have not been added and they can be added using the same job.). UK78201 HOLD DATA END UK78204 HOLD DATA START UK78204 HOLD DATA END UK78206 HOLD DATA START UK78206 HOLD DATA END UK78213 HOLD DATA START UK78213 HOLD DATA END UK78215 HOLD DATA START UK78215 HOLD DATA END UK78230 HOLD DATA START ++ HOLD(UK78230) SYS FMID(JDBAA1K) REASON(ACTION) DATE(12115) COMMENT (The PTF for PM60499 is a companion fix and requires the fix for APAR PM55051 for FMID HDBAA10 to be applied.). UK78230 HOLD DATA END UK78234 HOLD DATA START UK78234 HOLD DATA END UK78235 HOLD DATA START UK78235 HOLD DATA END UK78243 HOLD DATA START UK78243 HOLD DATA END UK78247 HOLD DATA START 1UK78247 HOLD DATA END UK78263 HOLD DATA START UK78263 HOLD DATA END UK78270 HOLD DATA START ++ HOLD(UK78270) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12116) COMMENT (***Action for PM57955: See PM57955 APAR/PTF text for additional information about why a REBIND is necessary. PM57955 corrects a problem of ABEND04E RC00E70005 in DSNXORGL P130 when query with range list access (ACCESSTYPE = 'NR' in PLAN_TABLE) participated in parallel sort-merge join group. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK78270 HOLD DATA END UK78271 HOLD DATA START ++ HOLD(UK73883) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12116) COMMENT (***Action for PM47894: See PM47894 APAR/PTF text for additional information about why a REBIND is necessary. PM47894 corrects a problem that an incorrout may occur when user's SQL statement satisfies all of following conditions: 1, The SQL statement contains a BETWEEN predicate which references a CASE expression in its low or high operator. 2, The CASE expression references a scalar fullselect in its searched-when clause. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK78271 HOLD DATA END UK78286 HOLD DATA START UK78286 HOLD DATA END 1UK78310 HOLD DATA START UK78310 HOLD DATA END UK78338 HOLD DATA START UK78338 HOLD DATA END UK78343 HOLD DATA START UK78343 HOLD DATA END UK78358 HOLD DATA START UK78358 HOLD DATA END UK78360 HOLD DATA START UK78360 HOLD DATA END UK78396 HOLD DATA START UK78396 HOLD DATA END UK78401 HOLD DATA START ++ HOLD(UK78401) SYS FMID(HDBAA10) REASON(DOC) DATE(12121) COMMENT (PM52083 introduces the following changes to externals and the approximate text will be added to the V10 DB2 SQL Codes. This text is subject to change. Explanation added to SQLCODE -107: THE NAME name IS TOO LONG. MAXIMUM ALLOWABLE SIZE IS size Explanation: In V9 CM and V10 CM8, WLM ENVIRONMENT names must not exceed 18 EBCDIC bytes. Information about this changed message will be included in the Information Management Software for z/OS Solutions Information Center (http://publib.boulder.ibm.com/infocenter/imzic) at a later date.). UK78401 HOLD DATA END UK78421 HOLD DATA START ++ HOLD(UK78421) SYS FMID(J2AG210) REASON(ACTION) DATE(12150) COMMENT (This apar provides two new sample functions. 1. Function DB2GSE.IDXKEY2DG Function is used to retrieve the current grid cells from the starting point. 2. Function DB2GSE.GETGRIDMBR 1 Function is used to retrieve the x and y cells and the MBR values. For existing users, to be able to use these functions, please submit the last two steps (CREATE02 and BINDSK19) of the DSN5SENB job. For new users, please follow the instructions contained in the publication "IBM Spatial Support for DB2 z/OS" to enable Spatial functionality. The warning message GIM43401W can be received when applying this apar. It can be ignored.). ++ HOLD(UK75587) SYS FMID(J2AG210) REASON(ACTION) DATE(12150) COMMENT (This APAR is a replacement for apar PM47333, ptf UK72946. This apar provides the function to retrieve the current and surrounding grid cells from a starting point. For existing users, if they would like to use this function, please submit the last two steps (CREATE01 and BINDSK19) of the DSN5SENB job. For new users, please follow the instructions contained in the publication "IBM Spatial Support for DB2 z/OS" to enable Spatial functionality.). ++ HOLD(UK72946) SYS FMID(J2AG210) REASON(ACTION) DATE(12150) COMMENT (This apar provides the functions to retrieve the current and surrounding grid cells from a starting point. For the existing users, if they like to use this function then submit 2 last steps: CREATE01 and BINDSK19 of DSN5SENB job. For the new users should follow the instructions contained in the book IBM Spatial Support for DB2 z/OS to enable Spatial functionality.). ++ HOLD(UK78421) SYS FMID(J2AG210) REASON(DELETE) DATE(12150) COMMENT (++MOD TO BE REMOVED FROM USER'S MVS SYSTEM). UK78421 HOLD DATA END UK78426 HOLD DATA START UK78426 HOLD DATA END UK78452 HOLD DATA START UK78452 HOLD DATA END UK78455 HOLD DATA START UK78455 HOLD DATA END 1 UK78467 HOLD DATA START UK78467 HOLD DATA END UK78472 HOLD DATA START UK78472 HOLD DATA END UK78476 HOLD DATA START UK78476 HOLD DATA END UK78479 HOLD DATA START ++ HOLD(UK67894) SYS FMID(HDBAA10) REASON(ACTION) DATE(12124) COMMENT (After applying the PTF for PM35195, DISPLAY GROUP's MODE output will only consider ACTIVE and FAILED members when determining whether a data sharing group is in coexistence. If there are any ACTIVE or FAILED members with a DB2 release level different from the level of the member processing the DISPLAY GROUP command, then a MODE of X will be displayed. Applications that read the MODE output for DISPLAY GROUP may need to change as a result of the new behavior above. For more information, refer to the closing text for PM35195.). ++ HOLD(UK67873) SYS FMID(HDBAA10) REASON(ACTION) DATE(12124) COMMENT (Under certain conditions, DB2 may output '***' for a member's DB2 LVL in the DISPLAY GROUP command output. After applying the PTF for PM34600, such members will be ignored when considering the data sharing group members' levels to determine whether the data sharing group is in coexistence. Accordingly, the MODE output may change to no longer display X in data sharing groups where such members exist. Applications that use the MODE output may need to be changed as a result. For more information, please refer to the closing text.). UK78479 HOLD DATA END UK78514 HOLD DATA START ++ HOLD(UK78514) SYS FMID(HDBAA10) REASON(ACTION) DATE(12125) COMMENT (***Action for PM53243 (V10): For users who are interested in exploiting the functions provided via this PTF, the following actions need to be considered: * For a CALL statement to a DB2 for z/OS Stored Procedure, the Stored Procedure name can be identified via a literal or by using a host variable or parameter marker. When using a literal for the stored procedure name, and in order to 1 benefit from the enhancement that provides a valid routine ID in various IFCID records, the packages that contain the CALL statement must be rebound after this PTF is applied. * For an SQL statement that invokes a DB2 for z/OS User-Defined Function, and in order to benefit from the enhancement to provide a valid routine ID in various IFCID records, the packages that contain the SQL statement must be rebound after this PTF is applied. * The mapping of IFCID233 remains compatible with prior versions and no immediate change is required. However, applications that parse this record will need to be changed in order to exploit the new fields.). UK78514 HOLD DATA END UK78542 HOLD DATA START UK78542 HOLD DATA END UK78558 HOLD DATA START UK78558 HOLD DATA END UK78575 HOLD DATA START UK78575 HOLD DATA END UK78578 HOLD DATA START UK78578 HOLD DATA END UK78581 HOLD DATA START ++ HOLD(UK75209) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12129) COMMENT (***Action for PM51654: See PM51654 APAR/PTF text for additional information about why a REBIND is necessary. PM51654 corrects a problem when incorrect output could happen for a complex SQL statement or view with an outer join and a correlated sub-query. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK73620) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12129) COMMENT (***Action for PM47058: See PM47058 APAR/PTF text for additional information about why a REBIND is necessary. PM47058 corrects a problem of incorrect output when an SQL statement satisfies all of the following conditions: 1 1. table expression with sideway reference is defined in this SQL statement; 2. LEFT OUTER JOIN is involved in this SQL statement; 3. CPU parallelism setting is enabled. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK71528) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12129) COMMENT (***Action for PM41296: See PM41296 APAR/PTF text for additional information about why a REBIND is necessary. PM41296 corrects a problem of an SQL statement which defines a table expression one the preserved side of LEFT OUTER JOIN, and a CASE statement or non-column expression is defined in the SELECT list of the table expression and is referenced in the SQL statement. Performance may regress when a SQL statement satisfies all of the following conditions: 1. OUTER JOIN is contained, and the preserved side is defined with table expression; 2. a CASE statement or non-column expression is defined in the SELECT list of the above table expression; 3. a table expression column which corresponds to the CASE statement or non-column expression from condition #2 is referenced in predicate of the SQL statement. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK78581) SYS FMID(HDBAA10) REASON(MULTSYS) DATE(12129) COMMENT (***Action for PM55928: PM55928 is a pre-conditioning APAR that includes changes to support a new function that will be enabled via another APAR PM56631. At this time, all of the changes are transparent to the user. In a data sharing group, this pre-conditioning APAR should be applied to all members before the later enabling APAR PM56631 is applied to any member. The new function is not enabled until the enabling APAR PM56631 is applied. If the new function will not be used, then there is no need to take precautions for rolling through the pre-conditioning PTF ahead of the enabling PTF. Instead, both pre-conditioning PTF and enabling PTF can be rolled through together. In addition, because the new function can only be used in DB2 10 New Function Mode (NFM), both pre-conditioning PTF and enabling 1 PTF can be rolled through together on DB2 10 CM subsystems.). UK78581 HOLD DATA END UK78584 HOLD DATA START ++ HOLD(UK78584) SYS FMID(HDBAA10) REASON(ACTION) DATE(12129) COMMENT (***Action for PM60806 TYPE: ACT PM60806 corrects a problem of missing SQLCODE -312 for a CREATE FUNCTION statement satisfied the following conditions, 1. It is a create sql scalar function, 2. SQLCODE/SQLSTATE is not defined as an SQL variable, 3. SQLCODE/SQLSTATE is referenced in a RETURN statement in function SQL routine body. 4. No table is referenced in the RETURN statement or no column in the referenced table with name as SQLCODE or SQLSTATE. After the application of this PTF, ALTER FUNCTION REGENERATE is required for each SQL scalar function that is affected. Review the PTF cover letter to determine which, if any, SQL scalar function could be affected by this change.). UK78584 HOLD DATA END UK78604 HOLD DATA START UK78604 HOLD DATA END UK78606 HOLD DATA START UK78606 HOLD DATA END UK78632 HOLD DATA START ++ HOLD(UK78632) SYS FMID(HDBAA10) REASON(ACTION) DATE(12130) COMMENT (The PTF for APAR PM57632 has modified CSECTs in both the DSNUT101 and DSNUTILA load modules, affecting the utility batch and DB2 DBM1 address spaces. After the PTF has been applied, it will become active in the utility batch address space immediately upon job submission, while it will not take effect in the DB2 DBM1 address space until DB2 is stopped and started. The PTF does not have to be applied to all members in a data sharing environment simultaneously, and may be staged across each member. However, until the fix is active in both the utility batch and DB2 DBM1 address spaces, the function the PTF addresses will not be enabled. The fix for APAR PM57632 is a companion fix. It requires the fix for APAR PM60438, for FMID JDBAA1K to be applied.). UK78632 HOLD DATA END 1UK78633 HOLD DATA START ++ HOLD(UK78633) SYS FMID(JDBAA1K) REASON(ACTION) DATE(12130) COMMENT (The fix for APAR PM60438 is a companion fix. It requires the fix for APAR PM57632, for FMID HDBAA10 to be applied.). ++ HOLD(UK71420) SYS FMID(JDBAA1K) REASON(ACTION) DATE(12130) COMMENT (The fix for APAR PM42560 is a companion fix. It requires the fix for APAR PM27962, for FMID HDBAA10 to be applied.). UK78633 HOLD DATA END UK78649 HOLD DATA START UK78649 HOLD DATA END UK78692 HOLD DATA START UK78692 HOLD DATA END UK78700 HOLD DATA START ++ HOLD(UK78700) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12132) COMMENT (***Action for PM60533: See PM60533 APAR/PTF text for additional information about why a REBIND is recommended. After you apply this PTF, some applications might experience performance degradation. To improve the performance of those applications, rebind their plans and packages. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK78700 HOLD DATA END UK78726 HOLD DATA START UK78726 HOLD DATA END UK78731 HOLD DATA START UK78731 HOLD DATA END UK78737 HOLD DATA START ++ HOLD(UK78737) SYS FMID(HDBAA10) REASON(ACTION) DATE(12135) COMMENT (***Action for PM58842 (V10): See PTF cover letter for additional information. This PTF updated the following Database Metadata stored procedures: SYSIBM.SQLCOLPRIVILEGES (DSNACPRU) SYSIBM.SQLCOLUMNS (DSNACOLU) 1 SYSIBM.SQLFUNCTIONCOLS (DSNAFCOU) SYSIBM.SQLFUNCTIONS (DSNAFUNU) SYSIBM.SQLPRIMARYKEYS (DSNAPRKU) SYSIBM.SQLPROCEDURECOLS (DSNAPCOU) SYSIBM.SQLPROCEDURES (DSNAPRCU) SYSIBM.SQLTABLEPRIVILEGES (DSNATBPU) SYSIBM.SQLTABLES (DSNATBLU) *************************************************************** Actions for customers who are already using DB2 V10: *************************************************************** If you have already installed or migrated to DB2 V10, you need to take the following actions after applying this PTF: (1) Run installation job DSNTIJRT to rebind DBRM DSNACPRU, DSNACOLU, DSNAFCOU, DSNAFUNU, DSNAPRKU, DSNAPCOU, DSNAPRCU, DSNATBPU and DSNATBLU. (2) Refresh the WLM environment Procedures for these actions are given below. ---------------------------------------------------------------- (1) Run installation job DSNTIJRT to rebind DBRM DSNACPRU, DSNACOLU, DSNAFCOU, DSNAFUNU, DSNAPRKU, DSNAPCOU, DSNAPRCU, DSNATBPU and DSNATBLU. ==> This action is required for all V10 customers Run your customized copy of job DSNTIJRT with MODE(INSTALL) to bind the DBRMs DSNACPRU, DSNACOLU, DSNAFCOU, DSNAFUNU, DSNAPRKU, DSNAPCOU, DSNAPRCU, DSNATBPU and DSNATBLU. If you have previously run DSNTIJRT, rerunning with MODE(INSTALL) will cause it to detect and correct only missing and downlevel SQL objects and packages for DB2-supplied routines. Note: Use MODE(INSTALL-PREVIEW) to obtain a report of any changes without processing them. The PREVIEW option will also generate and output a JCL job to the JCLOUT DD that contains any SQL and bind statements to be processed. After reviewing the changes, either rerun DSNTIJRT without the PREVIEW option or customize and run the generated job. (2) Refresh the WLM environment Issue the following VARY MVS command to refresh the WLM application environment VARY WLM,APPLENV=,REFRESH where is the name of the WLM application environment in which the Database Metadata stored procedures SYSIBM.SQLCOLPRIVILEGES, SYSIBM.SQLCOLUMNS SYSIBM.SQLFUNCTIONCOLS, SYSIBM.SQLFUNCTIONS, 1 SYSIBM.SQLPRIMARYKEYS, SYSIBM.SQLPROCEDURECOLS, SYSIBM.SQLPROCEDURES, SYSIBM.SQLTABLEPRIVILEGES and SYSIBM.SQLTABLES are running.). UK78737 HOLD DATA END UK78756 HOLD DATA START UK78756 HOLD DATA END UK78762 HOLD DATA START UK78762 HOLD DATA END UK78763 HOLD DATA START ++ HOLD(UK78763) SYS FMID(HDBAA10) REASON(ACTION) DATE(12137) COMMENT (*************************************************************** Actions for customers who are already using DB2 10: *************************************************************** Run installation job DSNTIJRT to bind modified stored procedures packages *********************************************************** * This action is required for all V10 customers *********************************************************** Run your customized copy of job DSNTIJRT with MODE(INSTALL) to bind the stored procedure SYSPROC.ADMIN_UTL_MONITOR. If you have previously run DSNTIJRT, rerunning with MODE(INSTALL) will cause it to detect and correct only missing and downlevel SQL objects and packages for DB2-supplied routines. Note: Use MODE(INSTALL-PREVIEW) to obtain a report of any changes without processing them. The PREVIEW option will also generate and output a JCL job to the JCLOUT DD that contains any SQL and bind statements to be processed. After reviewing the changes, either rerun DSNTIJRT without the PREVIEW option or customize and run the generated job.). ++ HOLD(UK75581) SYS FMID(HDBAA10) REASON(ACTION) DATE(12137) COMMENT (The PTF for PM54759 fixes the stored procedure SYSPROC.ADMIN_UTL_MONITOR so that the task it adds to the DB2 scheduler task list to execute the stored procedure SYSPROC.ADMIN_UTL_EXECUTE can be executed by the DB2 scheduler when DECIMAL=COMMA is enabled in DSNHDECP. See the PTF cover letter for more information. *************************************************************** Actions for DB2 10 for z/OS customers who are using the DB2-supplied stored procedures SYSPROC.ADMIN_UTL_MONITOR and SYSPROC.ADMIN_UTL_EXECUTE in conjunction with the DB2 administrative task scheduler *************************************************************** 1 After applying this PTF, you need to remove the task 'DB2 AUTO PROCEDURE EXECUTE' from the DB2 scheduler task list. This action is detailed below: (1) Ensure that the DB2 scheduler is up and running. If scheduler is not running, execute the following console command: START DSNADMT where DSNADMT is the name of the scheduler. (2) Invoke the user-defined function ADMIN_TASK_LIST to determine if the task 'DB2 AUTO PROCEDURE EXECUTE' is in the DB2 scheduler task list, by issuing the following SQL statement: SELECT TASK_NAME FROM TABLE (DSNADM.ADMIN_TASK_LIST()) AS T WHERE TASK_NAME = 'DB2 AUTO PROCEDURE EXECUTE'; A JOB example for this action is given below in the JOB EXAMPLES section. When no such task exists proceed to step (4). Otherwise, proceed to step (3), below. (3) Remove task 'DB2 AUTO PROCEDURE EXECUTE' from the DB2 scheduler task list by invoking the DB2-supplied stored procedure SYSPROC.ADMIN_TASK_REMOVE: CALL SYSPROC.ADMIN_TASK_REMOVE (task-name, return-code, message) with the input parameter 'task-name' set to 'DB2 AUTO PROCEDURE EXECUTE'. 'return-code' and 'message' are output parameters. A JOB example of using DB2 REXX Language Support for this action is given below in the JOB EXAMPLES section. (4) If the DB2 scheduler was started in step (1), stop the scheduler by executing the following console command: MODIFY DSNADMT,APPL=SHUTDOWN where DSNADMT is the name of the scheduler. *************************************************************** JOB EXAMPLES *************************************************************** Prior to running any example, customize it for your system by adding a valid job card and modifying the examples as indicated below. 1 *************************************************************** STEP (2) EXAMPLE QUERY DSNADM.ADMIN_TASK_LIST Required actions: 1. In lines 1-3,14 replace 'DSN!!0' with the prefix of the target library for DB2 V10 2. In line 12 replace '!DSN!' with your DB2 subsystem name 3. In line 13 replace 'DSNTEP!!' with the plan name for DSNTEP2 on your DB2 *************************************************************** //JOBLIB DD DSN=DSN!!0.SDSNEXIT,DISP=SHR // DD DSN=DSN!!0.SDSNLOAD,DISP=SHR // DD DSN=DSN!!0.RUNLIB.LOAD,DISP=SHR //DSNTEP2 EXEC PGM=IKJEFT01,COND=(4,LT),DYNAMNBR=20 //SYSTSPRT DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSTERM DD SYSOUT=* //CEEDUMP DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSOUT DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(!DSN!) RUN PROGRAM(DSNTEP2) PLAN(DSNTEP!!) - LIB('DSN!!0.RUNLIB.LOAD') PARMS('/ALIGN(MID)') END //SYSIN DD * SELECT TASK_NAME FROM TABLE (DSNADM.ADMIN_TASK_LIST()) AS T WHERE TASK_NAME = 'DB2 AUTO PROCEDURE EXECUTE' *************************************************************** STEP (3) EXAMPLE CALL SYSPROC.ADMIN_TASK_REMOVE Required actions: 1. In lines 1-3 replace 'DSN!!0' with the prefix of the target library for DB2 V10 2. In line 10 replace '#' with a forward slash '/'. Closing comment sequence is not allowed within ++HOLD text. 3. In line 11 replace '!DSN!' with your DB2 subsystem name *************************************************************** //JOBLIB DD DSN=DSN!!0.SDSNEXIT,DISP=SHR // DD DSN=DSN!!0.SDSNLOAD,DISP=SHR // DD DSN=DSN!!0.RUNLIB.LOAD,DISP=SHR //REXX EXEC PGM=IEBUPDTE,PARM='NEW' //SYSPRINT DD SYSOUT=* //SYSUT2 DD DISP=(,PASS),UNIT=SYSDA,SPACE=(TRK,(2,1,1)), // DSORG=PO,BLKSIZE=0,RECFM=FB,LRECL=80 //SYSIN DD * ./ ADD NAME=CALLSP /* REXX *# SSID = !DSN! TASKNAME = 'DB2 AUTO PROCEDURE EXECUTE' RETCODE = 12 MESSAGE = COPIES(' ',1332) "SUBCOM DSNREXX" 1 IF RC <> 0 THEN S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX') ADDRESS DSNREXX "CONNECT" SSID IF SQLCODE = 0 THEN DO SAY 'CONNECTED TO ' SSID ADDRESS DSNREXX "EXECSQL CALL ", "SYSPROC.ADMIN_TASK_REMOVE(", ":TASKNAME, :RETCODE, :MESSAGE)" IF SQLCODE <> 0 THEN SAY 'FAILURE DURING SP CALL SQLCODE =' SQLCODE SQLERRMC ELSE DO IF RETCODE <> 0 THEN SAY 'FAILURE RETURNED FROM SP.' RETCODE ' ' MESSAGE ELSE SAY 'TASK WAS REMOVED' END ADDRESS DSNREXX "DISCONNECT" END ELSE SAY 'CONNECTION FAILED' S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX') EXIT RETCODE /* //CALL EXEC PGM=IKJEFT01,COND=(4,LT),DYNAMNBR=20, // PARM='%CALLSP' //SYSPROC DD DSN=*.REXX.SYSUT2,DISP=(OLD,DELETE) //SYSTSPRT DD SYSOUT=* //SYSTSIN DD DUMMY *************************************************************** END OF JOB EXAMPLES ***************************************************************). UK78763 HOLD DATA END UK78774 HOLD DATA START UK78774 HOLD DATA END UK78779 HOLD DATA START UK78779 HOLD DATA END UK78783 HOLD DATA START ++ HOLD(UK78783) SYS FMID(HIZAA10) REASON(ACTION) DATE(12138) COMMENT (This PTF requires either an ERLY code refresh or a re-IPL of zOS. To refresh, enter the following commands after applying 1 the PTF: MODIFY LLA,REFRESH -STOP DB2 ... (only if the DB2 subsystem is started) -REFRESH DB2,EARLY -START DB2,PARM=... These commands are not necessary if you re-IPL after applying the PTF.). ++ HOLD(UK77171) SYS FMID(HIZAA10) REASON(ACTION) DATE(12138) COMMENT (This PTF requires either an ERLY code refresh or a re-IPL of zOS. To refresh, enter the following commands after applying the PTF: MODIFY LLA,REFRESH -STOP DB2 ... (only if the DB2 subsystem is started) -REFRESH DB2,EARLY -START DB2,PARM=... These commands are not necessary if you re-IPL after applying the PTF. This PTF requires either an ERLY code refresh or a re-IPL of zOS. To refresh, enter the following commands after applying the PTF: MODIFY LLA,REFRESH -STOP DB2 ... (only if the DB2 subsystem is started) -REFRESH DB2,EARLY -START DB2,PARM=... These commands are not necessary if you re-IPL after applying the PTF.). ++ HOLD(UK75933) SYS FMID(HIZAA10) REASON(ACTION) DATE(12138) COMMENT (In order for this PTF to be fully effective, an IPL with CLPA or REFRESH DB2,EARLY is required. The DB2 member should be stopped before issuing the REFRESH DB2, EARLY command.). UK78783 HOLD DATA END UK78787 HOLD DATA START UK78787 HOLD DATA END UK78806 HOLD DATA START UK78806 HOLD DATA END UK78868 HOLD DATA START UK78868 HOLD DATA END 1 UK78870 HOLD DATA START ++ HOLD(UK65858) SYS FMID(HDBAA10) REASON(ACTION) DATE(12139) COMMENT (A new option to be specified on the parameter to DSNX9WLM on the WLM startup JCL is introduced so user can specify a minimum number of address spaces to be kept for that particular environment. If this parameter is not specified, existing behavior prior to this change applies and WLM controls how many WLM-SPAS to keep. This new option will be implemented as new parameter to DSNX9WLM to the WLM start up JCL. For example: //DSN8WLMP PROC DB2SSN=DSN,NUMTCB=5,APPLENV=WLMTPSMP,MNSPAS=3 ... //WLMTPSMP EXEC PGM=DSNX9WLM,TIME=1440, // PARM='&DB2SSN,&NUMTCB,&APPLENV,&MNSPAS', // REGION=0M In the above example, a minimum of 3 WLM-SPAS will be started each with NUMTCB = 5. WLM will start the minimum number of address spaces on the first stored procedure request on that WLM environment. The valid number specified for the values of MNSPAS is between 0 to 50. A value of 0 means no minimum is specified and old behavior will apply. If the value of MNSPAS is invalid, message DSNX981E will be issued. If the new parameter is not specified, there will be no impact to the existing user base.). UK78870 HOLD DATA END UK78929 HOLD DATA START UK78929 HOLD DATA END UK78996 HOLD DATA START UK78996 HOLD DATA END UK78998 HOLD DATA START UK78998 HOLD DATA END UK79004 HOLD DATA START UK79004 HOLD DATA END UK79005 HOLD DATA START UK79005 HOLD DATA END UK79007 HOLD DATA START UK79007 HOLD DATA END 1 UK79008 HOLD DATA START UK79008 HOLD DATA END UK79013 HOLD DATA START ++ HOLD(UK79013) SYS FMID(HDBAA10) REASON(ACTION) DATE(12144) COMMENT (The PTF for APAR PM64065 has modified CSECTs in both the DSNUT101 and DSNUTILA load modules, affecting the utility batch and DB2 DBM1 address spaces. After the PTF has been applied, it will become active in the utility batch address space immediately upon job submission, while it will not take effect in the DB2 DBM1 address space until DB2 is stopped and started. The PTF does not have to be applied to all members in a data sharing environment simultaneously, and may be staged across each member. However, until the fix is active in both the utility batch and DB2 DBM1 address spaces, the problem the PTF addresses will not be resolved.). ++ HOLD(UK69216) SYS FMID(HDBAA10) REASON(ACTION) DATE(12144) COMMENT ( After applying the fix for PM31180 in DB2 10 for z/OS, FLASHCOPY IMAGE COPIES of partitioned objects with more than one partition or of linear objects containing more than one piece will insert a string of the form database-name.space-name into the DSNAME (data set name) column of the SYSCOPY record it writes for the FLASHCOPY record with DSNUM 0, similar to SYSCOPY records that are currently written for various types of non-backup activity. This is done to prevent invalid data set names from being written in these records. Users or automated programs sensitive to the contents of SYSIBM.SYSCOPY are advised.). UK79013 HOLD DATA END UK79015 HOLD DATA START UK79015 HOLD DATA END UK79017 HOLD DATA START UK79017 HOLD DATA END UK79050 HOLD DATA START UK79050 HOLD DATA END UK79062 HOLD DATA START ++ HOLD(UK79062) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12145) COMMENT (***Action for PM62259: See PM62259 APAR/PTF text for additional information about 1 why a REBIND is necessary. PM62259 corrects a problem of incorrect output when a query contains OLAP specifications and a subquery predicate. This APAR also corrects ABENDOC4 RC00000038 DSNXOWIN+104A when a query contains multiple OLAP specifications other than RANK, DENSE_RANK, and ROW_NUMBER. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK79062 HOLD DATA END UK79064 HOLD DATA START UK79064 HOLD DATA END UK79117 HOLD DATA START UK79117 HOLD DATA END UK79120 HOLD DATA START UK79120 HOLD DATA END UK79161 HOLD DATA START UK79161 HOLD DATA END UK79174 HOLD DATA START UK79174 HOLD DATA END UK79230 HOLD DATA START ++ HOLD(UK79230) SYS FMID(HDBAA10) REASON(ACTION) DATE(12152) COMMENT (See PM48742 APAR/PTF text in the PTF cover letter for additional information about this APAR. This APAR fixes two problems - one is incorrect index entries created for an XML value index with key type SQL DECFLOAT; another is incorrectly issuing SQLCODE -16061 when performing general comparison of invalid format of special values to numeric type in XPath predicates when no matching index exists. To make this fix effective for an application, users need to check if there is any XML value index with key type SQL DECFLOAT in use. If so, users need to run CHECK INDEX on such index after the application of this PTF to see it reports any data/index mismatch. REBUILD INDEX needs to be done for index with mismatch issue. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK79230 HOLD DATA END 1 UK79236 HOLD DATA START UK79236 HOLD DATA END UK79241 HOLD DATA START ++ HOLD(UK79241) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12152) COMMENT (***Action for PM61565: See PM61565 APAR/PTF text for additional information about why a REBIND is necessary. PM61565 corrects a problem of incorrect output when an SQL statement satisfies all of the following conditions: 1. SQL contains 'simple-when' CASE expression; 2. the column which is under CASE clause is defined with field procedure; 3. constants are under WHEN clause. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK79241 HOLD DATA END UK79243 HOLD DATA START ++ HOLD(UK79243) SYS FMID(HDBAA10) REASON(MULTSYS) DATE(12152) COMMENT (***Action for PM56631: PM56631 is the enabling APAR that introduces the new function support for PACK/UNPACK. Earlier, a pre-conditioning APAR PM55928 was delivered that will be needed. In a data sharing group, the PM55928 pre-conditioning APAR should be applied to all members before applying this ENABLING APAR to any member. The new function will be enabled once this ENABLING APAR is applied. If the new function will not be used, then there is no need to take precautions for rolling through the pre-conditioning PTF ahead of the enabling PTF. Instead, both PTFs can be rolled through together. In addition, because the new function can only be used in DB2 10 New Function Mode (NFM), both pre-conditioning PTF and enabling PTF can be rolled through together on DB2 10 CM subsystems.). UK79243 HOLD DATA END UK79272 HOLD DATA START UK79272 HOLD DATA END UK79286 HOLD DATA START 1UK79286 HOLD DATA END UK79291 HOLD DATA START ++ HOLD(UK79291) SYS FMID(HIZAA10) REASON(ACTION) DATE(12153) COMMENT (This PTF requires either an ERLY code refresh or a re-IPL of zOS. To refresh, enter the following commands after applying the PTF: MODIFY LLA,REFRESH -STOP DB2 ... (only if the DB2 subsystem is started) -REFRESH DB2,EARLY -START DB2,PARM=... These commands are not necessary if you re-IPL after applying the PTF.). ++ HOLD(UK77481) SYS FMID(HIZAA10) REASON(ACTION) DATE(12153) COMMENT (This PTF requires either an ERLY code refresh or a re-IPL of zOS. To refresh, enter the following commands after applying the PTF: MODIFY LLA,REFRESH -STOP DB2 ... (only if the DB2 subsystem is started) -REFRESH DB2,EARLY -START DB2,PARM=... These commands are not necessary if you re-IPL after applying the PTF.). ++ HOLD(UK78499) SYS FMID(HIZAA10) REASON(ACTION) DATE(12153) COMMENT (***Action for PM57744: This PTF requires either an EARLY code refresh or a re-IPL of zOS. To refresh, enter the following commands after applying the PTF: MODIFY LLA,REFRESH -STOP DB2 ... (only if the DB2 subsystem is started) -REFRESH DB2,EARLY -START DB2,PARM=... These commands are not necessary if you re-IPL after applying the PTF.). UK79291 HOLD DATA END UK79295 HOLD DATA START UK79295 HOLD DATA END 1 UK79299 HOLD DATA START UK79299 HOLD DATA END UK79316 HOLD DATA START ++ HOLD(UK74411) SYS FMID(HDBAA10) REASON(ACTION) DATE(12156) COMMENT (***Action for PM56722 (V10) This PTF modifies program DSNTRIN to install and configure the SYSPROC.ADMIN_INFO_SQL stored procedure in all modes of DB2 10 for z/OS. Previously, ADMIN_INFO_SQL (which is useful for gathering DB2 diagnostic and tuning data) was not installed in conversion mode after migration from DB2 UDB for z/OS Version 8. If you migrated to DB2 V10 from DB2 V8 and DB2 is not yet in new-function mode then after applying this PTF you need to take the following action: Run your copy of job DSNTIJRT with MODE(INSTALL) to install and configure SYSPROC.ADMIN_INFO_SQL. If you have previously run DSNTIJRT, rerunning with MODE(INSTALL) will cause it to detect and correct only missing and downlevel SQL objects and packages for DB2-supplied routines. Note: Use MODE(INSTALL-PREVIEW) to obtain a report of any changes without processing them. The PREVIEW option will also generate and output a JCL job to the JCLOUT DD that contains any SQL and bind statements to be processed. After reviewing the changes, either rerun DSNTIJRT without the PREVIEW option or customize and run the generated job.). ++ HOLD(UK79316) SYS FMID(HDBAA10) REASON(ACTION) DATE(12156) COMMENT (This PTF provides two new features: -- The Omnifind Text Search is moving to a new release, IBM Text Search for DB2 z/OS (IBMTS). -- A new stored procedure, SYSPROC.SYSTS_ALTER, can be used to modify the options of an existing IBM Text Search index. If you have already installed or migrated to DB2 10 for z/OS, you need to perform the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Copy updated DB2 installation panels to alternate libraries (3) Update your customized copy of job DSNTIJRT (4) Update private copies of the DSNTIDxx CLIST input member (5) Run installation job DSNTIJRT to install new objects (6) Run installation job DSNTIJRV to validate new objects 1 Detailed guidance for these actions is given further below. If you currently use or want to use IBM Text Search for DB2 for z/OS, you need to perform these additional actions: (I) Download the most current version of IBMTS (II) New IBMTS users: Install the IBMTS server (III) Existing users: Migrate to IBMTS from OmniFind server (IV) Add the new stored procedure SYSPROC.SYSTS_ALTER. (V) Where to find more information Detailed guidance for these actions is given further below. ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| Actions for all customers ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members *********************************************************** * This action is required for all V10 customers *********************************************************** This PTF modifies CLIST members DSNTINST, DSNTINSV, and DSNTINS1 in the prefix.SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your private copies of these CLISTs. You may also want to move them to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. ---------------------------------------------------------------- (2) Copy updated DB2 installation panels to alternate libraries *********************************************************** * This action is required for all V10 customers who maintain * copies of the DB2 installation panels outside of SMP/E: *********************************************************** This PTF modifies the DB2 installation panels DSNTIPRA and DSNTIPRJ in the prefix.SDSNSPFP target library. If you keep the DB2 installation panels in a different library then after applying this PTF, you need to copy the updated panels to that library. ---------------------------------------------------------------- (3) Update customized copies of installation job DSNTIJRT *********************************************************** * This action is required for all V10 customers *********************************************************** This PTF modifies installation job DSNTIJRT in the prefix.SDSNSAMP target library. After you apply this PTF, you need to add the following to the list of configuration control statements under the CFIGIN DD in your customized copy of DSNTIJRT: SYSPROC.SYSTS_ALTER WLMENV(DSNWLM_GENERAL) GRANTTO(PUBLIC) Notes: (a) Always leave a blank line between each configuration 1 control statement (b) Inspect each of the new configuration control statement and if required: - Change the WLMENV parameter settings to the names of the WLM environment you use to run these routines at your site - Change the GRANTTO parameter setting to the authorization IDs that need access to the routines - Optionally add the PKGOWNER option ---------------------------------------------------------------- (4) Update private copies of the DSNTIDxx CLIST input member *********************************************************** * This action is required for all V10 customers *********************************************************** This PTF adds entries to store values entered into new fields on installation panel DSNTIPRL: - RTJ09ACL: GRANT EXECUTE setting for SYSPROC.SYSTS_ALTER - RTJ09PKO: PACKAGE OWNER setting for SYSPROC.SYSTS_ALTER - RTJ09WLM: WLM ENV NAME setting for SYSPROC.SYSTS_ALTER You need to make the same changes to all private copies of your CLIST output (DSNTIDxx) member. In each such copy, add the following lines: RTJ09ACL CHAR M NONE NONE PUBLIC RTJ09PKO CHAR M NONE NONE NULL RTJ09WLM CHAR M NONE NONE DSNWLM_GENERAL The values shown in the 6th column are the DB2-supplied default settings. If you specified different settings in action (3) above, change the settings in column 6 accordingly. ---------------------------------------------------------------- (5) Run installation job DSNTIJRT to install new objects *********************************************************** * This action is optional for V10 customers *********************************************************** After you have completed the above actions, run your updated copy of job DSNTIJRT to install and grant access to the new routine, SYSPROC.SYSTS_ALTER, and to rebind the packages for the IBM Text Search routines. ---------------------------------------------------------------- (6) Run installation job DSNTIJRV to validate new objects *********************************************************** * This action is optional for V10 customers *********************************************************** After you have completed the above actions, run your customized copy of job DSNTIJRV to validate installation and configuration of SYSPROC.SYSTS_ALTER. ---------------------------------------------------------------- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 1 Additional actions for Text Search customers ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| The users need to follow the instructions in the "IBM Text Search for DB2 for z/OS" book to define the Text Search stored procedures and enable Text Search. The following information will refer to the "IBM Text Search for DB2 for z/OS" book. ---------------------------------------------------------------- (I) Download the most current version of IBMTS *********************************************************** * This action is required for Text Search customers *********************************************************** Download the most current version which is FIX R1L5 from the following Web site: http://www.ibm.com/software/data/db2/zos/downloads/oftss.html On this Web site, download the zip file and the readme file for IBMTS Text Search for DB2 for z/OS. The following shows the names of the zip files of IBMTS Text Search Servers: Linux32 - TextSearchServer_setuplinux32.zip Linux64 - TextSearchServer_setuplinux64.zip Window32 - TextSearchServer_setupwin32.zip Window64 - TextSearchServer_setupwin64.zip zLinux - TextSearchServer_setupzlinux.zip For Window32/64: right click the zip then choose extract option For Linux32/Linux64/zlinux: chmod 755 TextSearchServer_setupxxxx.zip unzip TextSearchServer_setupxxxx.zip To install or update the IBMTS server, please refer to the following publications: --readme.html: document that accompanies the installation file. --"Installation, Administration, and Reference": the product documentation for the IBM Text Search for DB2 z/OS. ---------------------------------------------------------------- (II) New IBMTS users: Install the IBMTS server *********************************************************** * This action is required for Text Search customers *********************************************************** To install the IBMTS server, the user needs to follow: Chapter 4. Installing and configuring text search functions 1 --Installing IBM Text Search for DB2 z/OS. (for the new installation). or --Installing fixes on top of the current server. (install IBMTS server on the existing server). ---------------------------------------------------------------- (III) Existing users: Migrate to IBMTS from OmniFind server *********************************************************** * This action is required for Text Search customers *********************************************************** For existing users to migrate IBMTS from OmniFind server they need to follow: Chapter 4. Installing and configuring text search functions --Migrating to IBM Text Search for DB2 for z/OS from OmniFind Text Search. ---------------------------------------------------------------- (IV) Add the new stored procedure SYSPROC.SYSTS_ALTER. *********************************************************** * This action is required for Text Search customers *********************************************************** Refer to steps (5) and (6) of the section above titled "Actions for all customers" ---------------------------------------------------------------- (V) Where to find more information ---------------------------------------------------------------- The user can look for more information for the new SYSTS_ALTER stored procedure at: Chapter 5. Administration stored procedures for text search SYSPROC.SYSTS_ALTER.). ++ HOLD(UK67507) SYS FMID(HDBAA10) REASON(ACTION) DATE(12156) COMMENT (Please note that the following ACTIONS need to be performed with respect to this apar: 1. Run your copy of job DSNTIJRT with MODE(INSTALL) to bind, and grant access to the Text Search stored procedures. 2. Refresh WLM environment defined for Text Search stored procedures: v wlm,applenv=wlmname,refresh 3. Refresh DB2 to make sure it will pick up the new Text Search modules. 4. This apar introduces the text search server level R2L11. To update the text search server, download the most current version which is FIX R2L11, from the following Web site: http://www.ibm.com/software/data/db2/zos/downloads/oftss.html From this Web site, click Download OmniFind Text Search 1 Server for DB2 for z/OS. The following shows the names of the files that start the silent installation or update of a Text Search server. 1. Linux - TS_fixR2L11linux_i586.bin 2. zLinux - TS_fixR2L11zlinux.bin 3. Windows - TS_fixR2L11win32.exe To install or update the server, please reference the following publications: "Instructions for installing": readme.html document that accompanies the installation file. "Installation, Administration, and Reference": The product documentation for the IBM Text Search for DB2 z/OS and the IBM OmniFind Text Search Server. 5. This apar provides the fix related to the backup index table. After applying this APAR the users should run SYSTS_UPDATE with ALLROWS options for all the indexes aready created with option UPDATEWITHBACKUP = 1 to make sure the backup index table refreshed with the full collection. Note that UPDATEWITHBACKUP = 1 is default option for SYSTS_CREATE, there for all most the indexes are created with this option. The users can use the following SQL statement to look for the indexes created before applying this APAR with UPDATEWITHBACKUP = 1 select a.indexid, indexschema, indexname, parameter, value from sysibmts.systextindexes a, sysibmts.systextconfiguration where a.indexid = b.indexid and parameter = 'UPDATEWITHBACKUP' and value = '1' Assume that the index SCHEMA1.IVC2 has UPDATEWITHBACKUP = 1 then the users need to run SYSTS_UPDATE with option ALLROWS for this index: CALL SYSPROC.SYSTS_UPDATE('SCHEMA1','IVC2','ALLROWS')). UK79316 HOLD DATA END UK79324 HOLD DATA START ++ HOLD(UK79324) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12156) COMMENT (***Action for PM61658: See PM61658 APAR/PTF text for additional information about why a REBIND is necessary. 1 PM61658 corrects a problem of over-estimated correlated subquery cardinality when global query optimization is enabled (OPTXQB=ON) and there are materialized views involved in the join between the parent query block and the correlated subquery. This may result in an inefficient access path. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK71709) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12156) COMMENT (***Action for PM44942: See PM44942 APAR/PTF text for additional information about why a REBIND is necessary. PM44942 corrects a problem of a poorly performing access path caused by incorrect filter factor estimation for a compound predicate when frequency or histogram statistics exist, or column cardinality is 1 or 2. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK69998) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12156) COMMENT (***Action for PM32384: See PM32384 APAR/PTF text for additional information about why a REBIND is necessary. PM32384 corrects a problem of inaccurate index filter factor estimate when multi-column cardinality exists in some cases which may result in an inefficient access path. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK79324 HOLD DATA END UK79327 HOLD DATA START ++ HOLD(UK79327) SYS FMID(H2AF210) REASON(ACTION) DATE(12156) COMMENT (This PTF should be applied only by customers that ordered and installed DB2 Accessories Suite Version 2 Release 2, as it replaces member DSN5NOT1 in the SDSNNOTC target library, and this notices file should reflect the the components/functions received with your order/maintenance of DB2 Accessories Suite. Do not apply this PTF unless you are a licensed customer of 1 DB2 Accessories Suite Version 2 Release 2.). UK79327 HOLD DATA END UK79348 HOLD DATA START UK79348 HOLD DATA END UK79353 HOLD DATA START UK79353 HOLD DATA END UK79364 HOLD DATA START UK79364 HOLD DATA END UK79368 HOLD DATA START UK79368 HOLD DATA END UK79373 HOLD DATA START ++ HOLD(UK79373) SYS FMID(HDBAA10) REASON(ACTION) DATE(12158) COMMENT (See PM63986 APAR/PTF text in the PTF cover letter for additional information about this APAR. PM63986 fixes the problem that the XML index key entry might not be inserted for some XML document. This may cause incorrect output when the index is used. To make this fix effective for the existing XML index, please run CHECK INDEX on the XML index after application of this PTF and rebuild the XML index if there is any data/index mismatch.). UK79373 HOLD DATA END UK79376 HOLD DATA START UK79376 HOLD DATA END UK79381 HOLD DATA START ++ HOLD(UK70647) SYS FMID(HDBAA10) REASON(DOC) DATE(12158) COMMENT (The DB2 Administration Guide will be updated to state that IMS transactions can exploit RACF authorization of DB2 objects if the following tasks are completed. - IMS must be configured to use APPC/OTMA security full or use the Build Security Environment DFSBSEX0 security exit to return RC04 in register 15 to tell IMS to create the ACEE in the dependent region. - The RACF DB2 security exit DSNX@XAC must be installed. - DB2 resources accessed by IMS transactions must be defined in a RACF profile and RACF permits must be created for IMS authorization IDs. The DB2 Administration Guide, Appendix A 'Consideration for the 1 access control authorization routine. When DB2 cannot provide an ACEE' will have the following sentence added - An ACEE is available to DB2 for an IMS transaction if IMS has been configured to use APPC/OTMA security full or the DFSBSEX0 security exit has been configured to return RC04 in register 15 to tell IMS to create the ACEE in the dependent region. The RACF Access Control Module Guide, Chapter 10 section 'When DB2 cannot provide an ACEE' contains this restriction. 1. The ACEE address is not passed for IMS transactions. This restriction will be changed to say - The ACEE may not be available for IMS transactions unless IMS has been configured to use APPC/OTMA security full or the DFSBSEX0 security exit has been configured to return RC04 in register 15 to tell IMS to create the ACEE in the dependent region.). UK79381 HOLD DATA END UK79382 HOLD DATA START UK79382 HOLD DATA END UK79393 HOLD DATA START UK79393 HOLD DATA END UK79404 HOLD DATA START ++ HOLD(UK79404) SYS FMID(HDBAA10) REASON(ACTION) DATE(12159) COMMENT (***Action for PM60170 (V10): This PTF adds an online-changeable subsystem parameter in DSN6SPRM called QUERY_ACCEL_OPTIONS that can be used to specify additional types of SQL queries to be included in query offloading. Valid options for QUERY_ACCEL_OPTIONS are: * NONE: Query offloading is restricted to the standard SQL statements ==> This is the default setting. * 1 : The queries that include data encoded by multi-byte character set EBCDIC encoding scheme are not blocked from executing on IDAA although IDAA encodes the same data in the UTF-8 Unicode encoding scheme. Note that EBCDIC and Unicode implement different collating sequences. The collating sequence for Unicode is numeric, uppercase characters, and then lower case characters (1, 2, 3, ..., A, B, C, ..., a, b, c, ...). In EBCDIC, the collating sequence is lower case, upper case, and then numeric (a, b, c, ..., A, B, C, ..., 1, 2, 3, ...). There are also differences in collating for the national characters. This affects both data ordering and the results from range predicates. Therefore, if the tables include 1 character columns where more than one of these groups can be found in the column values and the SQL statements include range predicates or ordering on these columns, a query executed in DB2 may return a different result set than the same query executed in IDAA. This PTF also adds DDL for creating the DSN_QUERYINFO_TABLE explain table and its containing table spaces and indexes to installation job DSNTIJSG and sample part DSNTESC. This table contains information about the eligibility of query blocks for automatic query rewrite, information about the materialized query tables that are considered for eligible query blocks, reasons why ineligible query blocks are not eligible, and information about acceleration of query blocks. If you have already installed or migrated to this version of DB2 you need to take the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Update your customized copy of job DSNTIJUZ (3) Update private copies of the DSNTIDxx CLIST input member (4) Update your customized copy of installation job DSNTIJSG (5) Create the SYSIBM.DSN_QUERYINFO_TABLE explain table (6) Update your customized copy of sample DDL part DSNTESC (7) Create the sample DSN_QUERYINFO_TABLE explain table Detailed guidance for these actions follows: ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members ---------------------------------------------------------------- ==> This action is recommended for all customers This PTF modifies CLIST members DSNTINST and DSNTINS3 in the SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your copies of these CLISTs. You may also want to move them to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. ---------------------------------------------------------------- (2) Update your customized copy of job DSNTIJUZ ---------------------------------------------------------------- ==> This action is required for all customers This PTF modifies DB2 installation job DSNTIJUZ in the SDSNSAMP target library. After applying this PTF, you need to update your customized copy of this job as follows: * Add the keyword parameter QUERY_ACCEL_OPTIONS=, where is either NONE or 1 to the invocation of the DSN6SPRM macro. Make sure to add a continuation character in column 72 if needed. If you omit adding QUERY_ACCEL_OPTIONS here, the value will be set to the default of NONE when you assemble the DSNZPxxx module. * Run the first two steps of the DSNTIJUZ job you modified. * After the job completes, you must either use the -SET SYSPARM command or stop and restart DB2 for the change to 1 take effect. ---------------------------------------------------------------- (3) Update private copies of the DSNTIDxx CLIST input member ---------------------------------------------------------------- ==> This action is required for all customers This PTF adds an entry for QUERY_ACCEL_OPTIONS to the CLIST default input members in the SDSNSAMP target library. You need to add these entries to all private copies of your CLIST output DSNTIDxx member. In each such copy, add the following line: QUERY_ACCEL_OPTIONS CHAR M NONE NONE Change to the value specified for QUERY_ACCEL_OPTIONS in step (2), above. If you do not add QUERY_ACCEL_OPTIONS to a DSNTIDxx member, the DB2 installation CLIST will assume QUERY_ACCEL_OPTIONS=NONE when run with that member as input. ---------------------------------------------------------------- (4) Update your customized copy of installation job DSNTIJSG ---------------------------------------------------------------- ==> This action is required for all customers Edit your customized copy of DSNTIJSG and locate job step DSNTIJQ. Append the following DDL to the existing in-stream input under the CREATE DD: CREATE LOB TABLESPACE DSNOQIL1 IN DSNOPTDB BUFFERPOOL BP8K0 USING STOGROUP DSNOPTSG; CREATE LOB TABLESPACE DSNOQIL2 IN DSNOPTDB BUFFERPOOL BP8K0 USING STOGROUP DSNOPTSG; CREATE TABLE SYSIBM.DSN_QUERYINFO_TABLE ( "QUERYNO" INTEGER NOT NULL WITH DEFAULT , "QBLOCKNO" SMALLINT NOT NULL WITH DEFAULT , "QINAME1" VARCHAR(128) NOT NULL WITH DEFAULT , "QINAME2" VARCHAR(128) NOT NULL WITH DEFAULT , "APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT , "PROGNAME" VARCHAR(128) NOT NULL WITH DEFAULT , "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT , "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT , "GROUP_MEMBER" VARCHAR(24) NOT NULL 1 WITH DEFAULT , "SECTNOI" INTEGER NOT NULL WITH DEFAULT , "SEQNO" INTEGER NOT NULL WITH DEFAULT , "EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT , "TYPE" CHAR(8) NOT NULL WITH DEFAULT , "REASON_CODE" SMALLINT NOT NULL WITH DEFAULT , "QI_DATA" CLOB(2M) NOT NULL WITH DEFAULT , "SERVICE_INFO" BLOB(2M) NOT NULL WITH DEFAULT , "QB_INFO_ROWID" ROWID NOT NULL GENERATED ALWAYS ) IN DSNOPTDB.DSNOPTTS CCSID UNICODE; CREATE AUX TABLE SYSIBM.DSN_QUERYINFO_AUX IN DSNOPTDB.DSNOQIL1 STORES SYSIBM.DSN_QUERYINFO_TABLE COLUMN "QI_DATA"; CREATE INDEX SYSIBM.DSN_QUERYINFO_AUXINX ON SYSIBM.DSN_QUERYINFO_AUX; CREATE AUX TABLE SYSIBM.DSN_QUERYINFO_AUX2 IN DSNOPTDB.DSNOQIL2 STORES SYSIBM.DSN_QUERYINFO_TABLE COLUMN "SERVICE_INFO"; CREATE INDEX SYSIBM.DSN_QUERYINFO_AUXINX2 ON SYSIBM.DSN_QUERYINFO_AUX2; ---------------------------------------------------------------- (5) Create the SYSIBM.DSN_QUERYINFO_TABLE explain table ---------------------------------------------------------------- ==> This action is required for all customers Use SPUFI or similar SQL processor to process the SQL statements listed in the previous step. ---------------------------------------------------------------- (6) Update your customized copy of sample DDL part DSNTESC ---------------------------------------------------------------- ==> This action is required for all customers who maintain a customized copy of job DSNTESC (a) Edit your customized copy of job DSNTESC (b) Append the following to the list of DROP TABLESPACE statements: --DROP TABLESPACE DSN8D10A.DSN8S10Q; --DROP TABLESPACE DSN8D10A.DSN8L10Q; --DROP TABLESPACE DSN8D10A.DSN8L10R; (c) Append the following to the list of CREATE TABLE SPACE 1 statements: CREATE TABLESPACE DSN8S10Q IN DSN8D10A BUFFERPOOL BP0 CCSID UNICODE USING STOGROUP DSN8G100 PRIQTY 30 SEGSIZE 16 CLOSE NO; CREATE LOB TABLESPACE DSN8L10Q IN DSN8D10A IN DSN8D10A BUFFERPOOL BP8K0 USING STOGROUP DSN8G100; CREATE LOB TABLESPACE DSN8L10R IN DSN8D10A BUFFERPOOL BP8K0 USING STOGROUP DSN8G100; (d) Add the following after the CREATE TABLE statement for DSN_KEYTGTDIST_TABLE: CREATE TABLE DSN81010.DSN_QUERYINFO_TABLE ( "QUERYNO" INTEGER NOT NULL WITH DEFAULT , "QBLOCKNO" SMALLINT NOT NULL WITH DEFAULT , "QINAME1" VARCHAR(128) NOT NULL WITH DEFAULT , "QINAME2" VARCHAR(128) NOT NULL WITH DEFAULT , "APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT , "PROGNAME" VARCHAR(128) NOT NULL WITH DEFAULT , "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT , "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT , "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT , "SECTNOI" INTEGER NOT NULL WITH DEFAULT , "SEQNO" INTEGER NOT NULL WITH DEFAULT , "EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT , "TYPE" CHAR(8) NOT NULL WITH DEFAULT , "REASON_CODE" SMALLINT NOT NULL WITH DEFAULT , "QI_DATA" CLOB(2M) NOT NULL WITH DEFAULT , "SERVICE_INFO" BLOB(2M) NOT NULL WITH DEFAULT 1 , "QB_INFO_ROWID" ROWID NOT NULL GENERATED ALWAYS ) IN DSN8D10A.DSN8S10Q CCSID UNICODE; CREATE AUX TABLE DSN81010.DSN_QUERYINFO_AUX IN DSN8D10A.DSN8L10Q STORES DSN81010.DSN_QUERYINFO_TABLE COLUMN "QI_DATA"; CREATE INDEX DSN81010.DSN_QUERYINFO_AUXINX ON DSN81010.DSN_QUERYINFO_AUX; CREATE AUX TABLE DSN81010.DSN_QUERYINFO_AUX2 IN DSN8D10A.DSN8L10R STORES DSN81010.DSN_QUERYINFO_TABLE COLUMN "SERVICE_INFO"; CREATE INDEX DSN81010.DSN_QUERYINFO_AUXINX2 ON DSN81010.DSN_QUERYINFO_AUX2; ---------------------------------------------------------------- (7) Create the sample DSN_QUERYINFO_TABLE explain table ---------------------------------------------------------------- ==> This action is optional Use SPUFI or similar SQL processor to process the SQL statements listed in the previous step. End of ***Action for PM60170 (V10).). UK79404 HOLD DATA END UK79406 HOLD DATA START UK79406 HOLD DATA END UK79431 HOLD DATA START ++ HOLD(UK79431) SYS FMID(HDBAA10) REASON(ACTION) DATE(12160) COMMENT (++HOLD COMMENT FOR RELEASE A10, TYPE ACT: This PTF modifies DSNADMSS, the external module for SYSPROC.ADMIN_INFO_SQL, a DB2-supplied routine that can be used to collect DB2 environmental service data. It also modifies DSNADMSB, a program that can be used to call SYSPROC.ADMIN_INFO_SQL. In order to take advantage of new collection features incorporated in this version, please ensure that DSN_VIEWREF_TABLE is created before doing static binds or dynamic explains. Also, make sure APAR PM63211 is applied. If you have already installed or migrated to DB2 V10, you need to take the following actions after applying this PTF: --------------------------------------------------------------- (1) Run installation job DSNTIJRT to rebind DBRMs DSNADMSS and DSNADMSB --------------------------------------------------------------- ==> This action is required for all V10 customers 1 Run your copy of job DSNTIJRT with MODE(INSTALL) to install, bind, and grant access to the DB2 supplied stored procedures. If you have previously run DSNTIJRT, rerunning with MODE(INSTALL) will cause it to detect and correct only missing and downlevel SQL objects and packages for DB2-supplied routines. Note: Use MODE(INSTALL-PREVIEW) to obtain a report of any changes without processing them. The PREVIEW option will also generate and output a JCL job to the JCLOUT DD that contains any SQL and bind statements to be processed. After reviewing the changes, either rerun DSNTIJRT without the PREVIEW option or customize and run the generated job. --------------------------------------------------------------- (2) Refresh the WLM environment --------------------------------------------------------------- ==> All DB2 V10 customers who use the SYSPROC.ADMIN_INFO_SQL stored procedure need to take this action after applying the PTF Issue the following VARY MVS command from the console to refresh the WLM application environment: VARY WLM,APPLENV=!wlmenv!,REFRESH where !wlmenv! is the name of the WLM application environment for running the SYSPROC.ADMIN_INFO_SQL stored procedure.). UK79431 HOLD DATA END UK79433 HOLD DATA START ++ HOLD(UK79433) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12160) COMMENT (***Action for PM63211: See PM63211 APAR/PTF text for additional information about why a REBIND is necessary. PM63211 corrects a problem with inserts into the DSN_VIEWREF_TABLE. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK79433 HOLD DATA END UK79447 HOLD DATA START ++ HOLD(UK78241) SYS FMID(HDBAA10) REASON(DOC) DATE(12160) COMMENT 1 (APAR PM58177 has enabled REORG TABLESPACE SHRLEVEL CHANGE to accept a user defined mapping table created in a partition-by-growth ( PBG ) table space. The Utility Guide and Reference has been updated with the following information, under the REORG TABLESPACE utility in section titled 'Mapping table and SHRLEVEL CHANGE' : Before running REORG TABLESPACE with SHRLEVEL CHANGE on a table space with non-LOB data, you must create a mapping table and index for the table space. The table space that | contains the mapping table must be either classic segmented | or a partition-by-growth table space, and cannot be the table space to be reorganized.). ++ HOLD(UK76053) SYS FMID(HDBAA10) REASON(DOC) DATE(12160) COMMENT (See PM47356 APAR/PTF text for additional information. This APAR/PTF modifies the DB2 Utility Guide and Reference and the Messages manual. The change will be documented in the DB2 Utility Guide and Reference and Messages, and in the Information Management Software for z/OS Solutions Information Center. The change is as follows. In the Utility Guide and Reference, under REORG TABLESPACE, Option descriptions, SORTDATA option: SORTDATA NO Specifies that the data is to be unloaded in the order of the clustering index. SORTDATA NO cannot be specified | with SHRLEVEL CHANGE or if the table space is in REORG- | PENDING. Specify SORTDATA NO if one of the following conditions is true: * The data is in or near perfect clustering order, the | table space is not in REORP, and the REORG utility is used to reclaim space from dropped tables. * The data is very large, an insufficient amount of disk | space is available for sorting, and the table space is not in REORP. In the Messages manual, under DSNU messages: DSNU070I csect-name KEYWORD OR OPERAND x INVALID WITH y Explanation: The use of a keyword or operand is | inconsistent with another keyword, operand or condition. | x and y The keywords, operands or conditions that cannot be used together. System action: The control statement in error is 1 rejected and processing is halted. User response: Correct the control statement in error and resubmit the job. | Problem determination: The REORG TABLESPACE | utility issues this message when the keywords, operands or | conditions are not allowed to be used in combination.). ++ HOLD(UK77573) SYS FMID(HDBAA10) REASON(ACTION) DATE(12160) COMMENT (***Action for PM59605 This PTF modifies the DB2 installation CLIST member DSNTINST to fix a bug that causes it to ignore the NEWFUN setting specified in the DSNTIDxx input member when the CLIST is run in UPDATE mode. This PTF modifies DSNTINST in the SDSNCLST target library only. If you have already installed or migrated to this version of DB2 then after applying this PTF, you need to redo any record format changes and reapply any tailoring you have done to your copies of this CLIST. You may also want to move it to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside.). ++ HOLD(UK78229) SYS FMID(HDBAA10) REASON(ACTION) DATE(12160) COMMENT (The PTF for APAR PM55051 has modified CSECTs in both the DSNUT101 and DSNUTILA load modules, affecting the utility batch and DB2 DBM1 address spaces. After the PTF has been applied, it will become active in the utility batch address space immediately upon job submission, while it will not take effect in the DB2 DBM1 address space until DB2 is stopped and started. The PTF does not have to be applied to all members in a data sharing environment simultaneously, and may be staged across each member. However, until the fix is active in both the utility batch and DB2 DBM1 address spaces, the function the PTF provides will not be available. PM55051 provides support for REORG TABLESPACE PART SORT NPSIs (Non-partitioned Secondary Indexes). This is a performance enhancement to part-level REORG involving NPSIs intended to improve elapsed time. Since the removal of the BUILD2 phase for partition-level REORG in DB2 9, the performance of REORG was degraded in some cases due to the cost of building shadow NPSIs. Shadow NPSIs are populated initially with keys of parts which are not in the scope of the REORG during the UNLOAD phase. Then keys from parts within the scope of the REORG are sorted and inserted into the shadow NPSI during the SORT and REBUILD phases, respectively. Significant performance improvement can be achieved by sorting all keys of the NPSI in the same sort operation and rebuilding 1 the index from the entire set of sorted keys. This APAR will add functionality to improve performance while leaving the old behavior intact and allow the customer to control the behavior through the installation panel and a keyword. This APAR modifies the processing of non-partitioned secondary indexes for REORG TABLESPACE PART SHRLEVEL CHANGE/REFERENCE when NPSIs are defined on the table space. Processing of NPSIs in this case will now be done in one of two ways as documented in the UNLOAD phase section of Execution Phase of REORG TABLESPACE in the Utility Guide and Reference. 1. During UNLOAD, one or more subtasks unload NPSI keys from parts not within the scope of the REORG and build the shadow NPSI. Keys from parts within the scope of the REORG are generated from the reorganized data rows, sorted, and inserted in the shadow index. 2. During UNLOAD, one or more subtasks processes NPSI keys from parts not within the scope of the REORG. These keys are routed to a sort process to be sorted with the keys from parts within the scope of the REORG. The shadow NPSI is built from this sorted set of keys. A new message is issued when all keys of an NPSI are sorted during a part-level REORG TABLESPACE. A new keyword is added on REORG to control the sorting of all NPSI keys during a part-level REORG TABLESPACE. The zparm controlling the sorting of all NPSI keys during a part-level REORG is extended to allow more options. ---------------------------------------------------------------- Changes to Messages and Codes DSNU1242I csect-name ALL KEYS OF A NON-PARTITIONED SECONDARY INDEX WILL BE SORTED Explanation During a part-level REORG all keys of a non-partitioned secondary index will be sorted before rebuilding the index. This is controlled by the keyword SORTNPSI and the zparm REORG_PART_SORT_NPSI. When SORTNPSI is specified as YES or AUTO all keys may be sorted. When SORTNPSI is not specified, and REORG_PART_SORT_NPSI is set to YES or AUTO, all keys may be sorted. System action Processing is continues. Severity 0 (informational) ---------------------------------------------------------------- Changes to Utility Guide and Reference A new keyword SORTNPSI is added to control the method of building an NPSI during REORG TABLESPACE PART. Options of AUTO, 1 YES, and NO are available on the keyword. A default is not applicable in this case because the value will be determined by zparm REORG_PART_SORT_NPSI if the keyword is not specified. .-FORCE--NONE----. (3) >--+----------------+--------+-----------------+---------------> +-FORCE--READERS-+ +-SORTNPSI AUTO---+ '-FORCE--ALL-----' '-SORTNPSI YES----' '-SORTNPSI NO-----' 3. The default for SORTNPSI is the value of the REORG_PART_SORT_NPSI subsystem parameter. SORTNPSI Specifies when REORG TABLESPACE PART should sort all keys of a non-partitioned secondary index. This keyword is ignored for a REORG that is not partition-level or a REORG with no non-partitioned secondary indexes. If SORTNPSI is not specified the value will be determined by zparm REORG_PART_SORT_NPSI. The benefit of sorting all keys of a non-partitioned secondary index increases as the ratio of data reorganized to total data in the table space increases. AUTO Specifies that if sorting all keys of the non-partitioned secondary indexes improves the elapsed time and CPU performance, all keys are sorted. YES Specifies that if sorting all keys of the non-partitioned secondary indexes improves the elapsed time, all keys are sorted. NO Specifies that only keys of the non-partitioned secondary indexes that are in the scope of the REORG are sorted. The default value is the value of subsystem parameter REORG_PART_SORT_NPSI. ---------------------------------------------------------------- Changes to DB2 Installation This APAR modifies the DSN6SPRM.REORG_PART_SORT_NPSI subsystem parameter. REORG_PART_SORT_NPSI specifies the default method of building an NPSI during REORG TABLESPACE PART when the SORTNPSI keyword is not provided in the utility control statement. Valid settings for REORG_PART_SORT_NPSI are AUTO, NO, and YES. The default setting is NO. REORG_PART_SORT_NPSI is online changeable and has member scope in data sharing. 1 DSN6SPRM: The REORG_PART_SORT_NPSI parameter is added with a range of AUTO, NO, and YES. The default setting is NO. DSNTIJUZ: The REORG_PART_SORT_NPSI parameter is added under the DSN6SPRM invocation with a default setting of NO. ---------------------------------------------------------------- Changes to DB2 instrumentation DSNDQWPZ: Field QWP4RPSNE is modified to trace the REORG_PART_SORT_NPSI internal setting: . . . QWP4RPSN DS CL1 REORG_PART_SORT_NPSI * * A=AUTO, N=NO, Y=YES . . . The IFCID 106 formatter stored procedures, SYSPROC.DSNWZP and SYSPROC.ADMIN_INFO_SYSPARM are updated to report the REORG_PART_SORT_NPSI setting. ---------------------------------------------------------------- This PTF adds an online-changeable subsystem parameter in DSN6SPRM called REORG_PART_SORT_NPSI that can be used to specify the default method of building a non-partitioned secondary index (NPSI) during REORG TABLESPACE PART. This setting will be used when the SORTNPSI keyword is not provided in the utility control statement. The SORTNPSI keyword specifies whether REORG TABLESPACE PART should decide to sort all keys of a NPSI and how to make that decision. The setting is ignored for a REORG which is not part-level or a REORG with no NPSIs. Valid settings for this parameter are: * AUTO: Specifies that if sorting all keys of the NPSI(s) improves the elapsed time and CPU performance, all keys are sorted. * YES : Specifies that if sorting all keys of the NPSI(s) improves the elapsed time, all keys are sorted. * NO : Specifies that only keys of the NPSI(s) that are in the scope of the REORG are sorted. => This is the default setting If you have already installed or migrated to this version of DB2 you need to take the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Update your customized copy of job DSNTIJUZ (3) Update private copies of the DSNTIDxx CLIST input member Detailed guidance for these actions follows: ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members ---------------------------------------------------------------- ==> This action is recommended for all customers This PTF modifies CLIST member DSNTINST in the SDSNCLST target library only. You need to redo any record format 1 changes and reapply any tailoring you have done to your copies of this CLIST. You may also want to move it to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. ---------------------------------------------------------------- (2) Update your customized copy of job DSNTIJUZ ---------------------------------------------------------------- ==> This action is required for all customers This PTF modifies DB2 installation job DSNTIJUZ in the SDSNSAMP target library. After applying this PTF, you need to update your customized copy of this job as follows: * Add the keyword parameter REORG_PART_SORT_NPSI=, where is AUTO, NO, or YES, to the invocation of the DSN6SPRM macro. Make sure to add a continuation character in column 72 if needed. If you omit adding REORG_PART_SORT_NPSI here, the value will be set to the default of NO when you assemble the DSNZPxxx module. * Run the first two steps of the DSNTIJUZ job you modified. * After the job completes, you must either use the -SET SYSPARM command or stop and restart DB2 for the change to take effect. ---------------------------------------------------------------- (3) Update private copies of the DSNTIDxx CLIST input member ---------------------------------------------------------------- ==> This action is required for all customers This PTF adds an entry for REORG_PART_SORT_NPSI to the CLIST default input members in the SDSNSAMP target library. You need to add these entries to all private copies of your CLIST output DSNTIDxx member. In each such copy, add the following line: REORG_PART_SORT_NPSI CHARR M AUTO YES$ Change to the value you specified for REORG_PART_SORT_NPSI in step (2), above. If you do not add REORG_PART_SORT_NPSI to a DSNTIDxx member, the DB2 installation CLIST will assume REORG_PART_SORT_NPSI=NO when run with that member as input.). ++ HOLD(UK76492) SYS FMID(HDBAA10) REASON(ACTION) DATE(12160) COMMENT (APAR PM54727 has corrected an issue where REORG REBALANCE on a partitioned table space with an editproc defined can result in corrupted limit key values in the DB2 catalog and directory. In the event that the above limit key corruption has occurred, user can repair the catalog/directory by manually altering the limit keys to the correct value on the affected table space / partitions, and then run a REORG TABLESPACE utility to remove the restrictive REORP status.). ++ HOLD(UK78208) SYS FMID(HDBAA10) REASON(ACTION) DATE(12160) COMMENT (Action for PM53254: 1 The PTF of PM53254 adds a new system parameter called REORG_IGNORE_FREESPACE, which specifies whether the REORG TABLESPACE utility should ignore the PCTFREE and FREEPAGE values when reloading data rows into a Partition By Growth (PBG) table space in the following circumstances: - When reorganizing a subset of the PBG partition(s) - When the table in the PBG table space has LOB column(s) Valid settings for REORG_IGNORE_FREESPACE are NO and YES. The default is NO, meaning that the PCTFREE and FREEPAGE values will be honored by the REORG TABLESPACE utility. To incorporate this fix the DB2 initialization parameter module, DSNZPARM, will need to be reassembled and re-linked by running the DB2 install clist DSNTINST using UPDATE mode. Insure that the output sample library name specified in panel DSNTIPT is not the same as an existing library name from a previous execution of the DSNTINST clist as the clist processing will delete all members delete all members of the data set name specified. Note: Customers who receive return code 8 and message "ASMA939U Unable to load external function module - DSNTIXL" when applying this PTF need to refer to Info APAR II13979. If you have already installed or migrated to this release of DB2, you need to take the following actions after applying this PTF: (1) Optional: Update your DB2 subsystem parameter (DSNZPxxx) modules: * Add the keyword parameter REORG_IGNORE_FREESPACE=, where is either NO or YES (NO is recommended) to the invocation of the DSN6SPRM macro in your customized version of the installation job DSNTIJUZ. Make sure to add a continuation character in column 72 if needed. If you omit adding REORG_IGNORE_FREESPACE here, the value will be set to the default of NO when you assemble the DSNZPxxx module. * Run the first two steps of the DSNTIJUZ job you modified, to assemble and link the load module. * After the job completes, you must either use the SET SYSPARM command or stop and start DB2 for the change to take effect. (2) Required: Update your private copies of the CLIST defaults (DSNTIDxx) member: This PTF adds an entry for REORG_IGNORE_FREESPACE to the CLIST default input member, DSNTIDXA, in the SDSNSAMP target library. You need to add this entry to all private copies of your CLIST output (DSNTIDxx) member for DB2. In each such copy, add the following line: REORG_IGNORE_FREESPACE CHAR M NO YES 1 where is the value you specified for REORG_IGNORE_FREESPACE when you modified DSNTIJUZ in step (1), above. Specify NO for if you bypassed step (1). (3) Required: Update customized copies of installation CLIST member DSNTINST: This PTF modifies CLIST member DSNTINST in the SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your copies of this CLIST. You may also want to move it to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside.). ++ HOLD(UK74936) SYS FMID(HDBAA10) REASON(ACTION) DATE(12160) COMMENT (*** Action for PM47659 *** This APAR has changed the UNLOAD utility to utilize the RUNSTATS NPAGES statistic from SYSIBM.SYSTABLES to estimate the space needed for the SYSREC data set. A RUNSTATS against the target table space is recommended prior to the UNLOAD to ensure the accurate estimation for space requirement.). ++ HOLD(UK76053) SYS FMID(HDBAA10) REASON(ACTION) DATE(12160) COMMENT (The PTF for APAR PM47356 has modified the REORG TABLESPACE utility to issue the following message and end with RC8 when REORG TABLESPACE SORTDATA NO is run against a non-Partition- By-Growth (non-PBG) partitioned table space in REORG-PENDING (REORP). A REORG TABLESPACE SORTDATA YES is required to remove the REORP state. MSGDSNU070I - KEYWORD OR OPERAND 'SORTDATA NO' INVALID WITH 'TABLE SPACE IN REORG PENDING'). UK79447 HOLD DATA END UK79502 HOLD DATA START UK79502 HOLD DATA END UK79505 HOLD DATA START UK79505 HOLD DATA END UK79551 HOLD DATA START UK79551 HOLD DATA END UK79559 HOLD DATA START ++ HOLD(UK79559) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12165) COMMENT (***Action for PM61906: See PM61906 APAR/PTF text for additional information about why a REBIND is necessary. PM61906 corrects a problem of ABEND04E at DSNXESX2 when running 1 query with COUNT ( DISTINCT (COL) ) and parallelism enabled. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK79559 HOLD DATA END UK79619 HOLD DATA START ++ HOLD(UK79619) SYS FMID(HDBAA10) REASON(DOC) DATE(12167) COMMENT (PM64166 introduces the following changes to externals: Changed option for utility UNLOAD: MAXERR integer Specifies the maximum number of records in error that are to be allowed; the unloading process terminates when this value is reached. integer Specifies the number of records in error that are allowed. When the error count reaches this number, the UNLOAD utility issues message DSNU1219 and terminates with return code 8. The default value is 1, which indicates that UNLOAD stops when the first error is encountered. If you specify 0 or any negative number, execution continues regardless of the number of records that are in error. If multiple table spaces are being processed, the number of records in error is counted for each table space. If the LIST option is used, you can add OPTION utility control statement (EVENT option with ITEMERROR) before the UNLOAD statement to specify that the table space in error is to be skipped and the subsequent table spaces are to be processed. | MAXERR option would be ignored when getting message DSNU283I | with SQLCODE -452 REASON CODE 7 during UNLOAD LOB or XML data | with FRV, and the UNLOAD would be terminated with | return code 8.). UK79619 HOLD DATA END UK79633 HOLD DATA START UK79633 HOLD DATA END UK79635 HOLD DATA START UK79635 HOLD DATA END UK79652 HOLD DATA START UK79652 HOLD DATA END 1 UK79654 HOLD DATA START UK79654 HOLD DATA END UK79681 HOLD DATA START UK79681 HOLD DATA END UK79701 HOLD DATA START UK79701 HOLD DATA END UK79706 HOLD DATA START UK79706 HOLD DATA END UK79707 HOLD DATA START UK79707 HOLD DATA END UK79718 HOLD DATA START UK79718 HOLD DATA END UK79719 HOLD DATA START UK79719 HOLD DATA END UK79723 HOLD DATA START UK79723 HOLD DATA END UK79729 HOLD DATA START UK79729 HOLD DATA END UK79738 HOLD DATA START ++ HOLD(UK79738) SYS FMID(HDBAA10) REASON(ACTION) DATE(12173) COMMENT (***Action for PM41083 (V10): See PTF cover letter for additional information. This PTF adds a new DB2-supplied stored procedure called SYSIBM.SQLPSEUDOCOLUMNS that returns a result set having a description of the pseudo or hidden columns available in a given table within a specified catalog and schema. This PTF also adds a related created global temporary table (CGTT) called SYSIBM.SQTPSEUDOCOLUMNS. This PTF modifies an existing CGTT called SYSIBM.SQTCOLUMNS: It changes the REMARKS column from VARCHAR(254) to VARCHAR(762) and adds a new column, IS_GENERATEDCOLUMN of VARCHAR(128). Finally, this PTF updates the following DB2-supplied Database Metadata stored procedures: SYSIBM.SQLCOLUMNS (DSNACOLU) 1 SYSIBM.SQLPROCEDURES (DSNAPRCU) SYSIBM.SQLTABLES (DSNATBLU) **************************************************************** Actions for customers who are already using DB2 V10: **************************************************************** If you have already installed or migrated to V10, the following actions are required after you apply this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Copy updated DB2 installation panels to alternate libraries (3) Update customized copies of installation job DSNTIJRT (4) Update private copies of the DSNTIDxx CLIST input member (5) Run installation job DSNTIJRT to install new objects and to rebind the DBRM DSNACOLU, DSNAPRCU and DSNATBLU (6) Run installation job DSNTIJRV to validate new objects (7) Refresh the WLM environment These actions are detailed below: ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members ************************************************************ * This action is required for all V10 customers ************************************************************ This PTF modifies CLIST members DSNTINST, DSNTINSV, and DSNTINS1 in the prefix.SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your private copies of these CLISTs. You may also want to move them to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. ---------------------------------------------------------------- (2) Copy updated DB2 installation panels to alternate libraries ************************************************************ * This action is required for all V10 customers who maintain * copies of the DB2 installation panels outside of SMP/E: ************************************************************ This PTF modifies the DB2 installation panels DSNTIPRA and DSNTIPRK in the prefix.SDSNSPFP target library. If you keep the DB2 installation panels in a different library then after applying this PTF, you need to copy the updated panels to that library. ---------------------------------------------------------------- (3) Update customized copies of installation job DSNTIJRT ************************************************************ * This action is required for all V10 customers ************************************************************ This PTF modifies installation job DSNTIJRT in the prefix.SDSNSAMP target library. After you apply this PTF, you need to add the following to the list of configuration control statements under the CFIGIN DD in your customized copy of DSNTIJRT: SYSIBM.SQLPSEUDOCOLUMNS WLMENV(DSNWLM_GENERAL) GRANTTO(PUBLIC) 1 Notes: (a) Always leave a blank line between each configuration control statement (b) Inspect each of the new configuration control statement and if required: - Change the WLMENV parameter settings to the names of the WLM environment you use to run these routines at your site - Change the GRANTTO parameter setting to the authorization IDs that need access to the routines - Optionally add the PKGOWNER option ---------------------------------------------------------------- (4) Update private copies of the DSNTIDxx CLIST input member ************************************************************ * This action is required for all V10 customers ************************************************************ This PTF adds entries to store values entered into new fields on installation panel DSNTIPRK: - RTK16ACL: GRANT EXECUTE setting for SYSIBM.SQLPSEUDOCOLUMNS - RTK16PKO: PACKAGE OWNER setting for SYSIBM.SQLPSEUDOCOLUMNS - RTK16WLM: WLM ENV NAME setting for SYSIBM.SQLPSEUDOCOLUMNS You need to make the same changes to all private copies of your CLIST output (DSNTIDxx) member. In each such copy, add the following lines: RTK16ACL CHAR M NONE NONE PUBLIC RTK16PKO CHAR M NONE NONE NULL RTK16WLM CHAR M NONE NONE DSNWLM_GENERAL The values shown in the 6th column are the DB2-supplied default settings. If you specified different settings in action (3) above, change the settings in column 6 accordingly. ---------------------------------------------------------------- (5) Run installation job DSNTIJRT to install new objects and to rebind the DBRM DSNACOLU, DSNAPRCU and DSNATBLU. ************************************************************ * This action is required for all V10 customers ************************************************************ After you have completed the above actions, run your updated copy of job DSNTIJRT with MODE(INSTALL) to install and grant access to the new routine, SYSIBM.SQLPSEUDOCOLUMNS, and the new created global temporary table SYSIBM.SQTPSEUDOCOLUMNS, and to modify the existing CGTT, SYSIBM.SQTCOLUMNS, and to rebind the DBRM DSNACOLU, DSNAPRCU and DSNATBLU. If you have previously run DSNTIJRT, rerunning with MODE(INSTALL) will cause it to detect and correct only missing and downlevel SQL objects and packages for DB2-supplied routines. 1 Note: Use MODE(INSTALL-PREVIEW) to obtain a report of any changes without processing them. The PREVIEW option will also generate and output a JCL job to the JCLOUT DD that contains any SQL and bind statements to be processed. After reviewing the changes, either rerun DSNTIJRT without the PREVIEW option or customize and run the generated job. ---------------------------------------------------------------- (6) Run installation job DSNTIJRV to validate new objects ************************************************************ * This action is required for all V10 customers ************************************************************ After you have completed the above actions, run your customized copy of job DSNTIJRV to validate installation and configuration of SYSIBM.SQLPSEUDOCOLUMNS. ---------------------------------------------------------------- (7) Refresh the WLM environment ************************************************************ * This action is required for all V10 customers ************************************************************ Issue the following VARY MVS command to refresh the WLM application environment VARY WLM,APPLENV=,REFRESH where is the name of the WLM application environment in which the Database Metadata stored procedures SYSIBM.SQLCOLUMNS, SYSIBM.SQLPROCEDURES, SYSIBM.SQLPSEUDOCOLUMNS and SYSIBM.SQLTABLES are running.). UK79738 HOLD DATA END UK79761 HOLD DATA START UK79761 HOLD DATA END UK79770 HOLD DATA START ++ HOLD(UK79770) SYS FMID(HDBAA10) REASON(DOC) DATE(12176) COMMENT (PM63979 introduces the following changes to the Messages publication: Description change for message DSNU1140I: DSNU1140I csect-name MESSAGE LIMIT OF max-messages HAS BEEN REACHED FOR message-number MESSAGES. NO MORE WILL BE ISSUED FOR THIS UTILITY Explanation The number of instances of the indicated message issued during a utility operation has exceeded the maximum number permitted. csect-name The name of the control section that issued the message. max-messages The maximum number of messages that are issued before this message is issued. This value is controlled by the utility that issues message DSNU1140I. 1 message-number The message number that has reached the limit and is now suppressed. | Example 1: The LOAD utility issues message DSNU1140I after 2000 instances of any of the following messages: DSNU313I, DSNU314I, DSNU1103I, or DSNU1104I. Any rows that would have triggered the suppressed message are still discarded. | Example 2: The UNLOAD FROMCOPY/FROMCOPYDDN issues message | DSNU1140I after 2000 instances of message DSNU1239I, when | MAXERR is specified to greater than 2000 or 0. Any rows that | would have triggered the suppressed message are still not | unloaded. System action Processing continues. No additional instances of message-number messages are issued. Only the issuance of the message is suppressed. Any action that is associated with the suppressed message number still occurs. User response No action is required. Severity 0). ++ HOLD(UK79770) SYS FMID(HDBAA10) REASON(ACTION) DATE(12176) COMMENT (The PTF for APAR PM63979 has modified CSECTs in both the DSNUT101 and DSNUTILA load modules, affecting the utility batch and DB2 DBM1 address spaces. After the PTF has been applied, it will become active in the utility batch address space immediately upon job submission, while it will not take effect in the DB2 DBM1 address space until DB2 is stopped and started. To make this fix effective for a static application, it must be The PTF does not have to be applied to all members in a data sharing environment simultaneously, and may be staged across each member. However, until the fix is active in both the utility batch and DB2 DBM1 address spaces, the problem the PTF addresses will not be resolved.). ++ HOLD(UK75124) SYS FMID(HDBAA10) REASON(ACTION) DATE(12176) COMMENT (The PTF for APAR PM50005 has modified CSECTs in both the DSNUT101 and DSNUTILA load modules, affecting the utility batch and DB2 DBM1 address spaces. After the PTF has been applied, it will become active in the utility batch address space immediately upon job submission, while it will not take effect in the DB2 DBM1 address space until DB2 is stopped and started. The PTF does not have to be applied to all members in a data sharing environment simultaneously, and may be staged across each member. However, until the fix is active in both the 1 utility batch and DB2 DBM1 address spaces, the problem the PTF addresses will not be resolved. ***Action for PM50006: It should be noted that the removal of these UNLOAD restrictions will now allow clone and/or base table data to be UNLOADed using image copy data sets from the other table in the clone relationship as follows: 1 - UNLOAD from base table using a clone table image copy. 2 - UNLOAD from clone table using a base table image copy. And in these cases the MSGDSNU1227I and MSGDSNU1408I messages will no longer be issued and the return codes will be zero with this APAR fix.). UK79770 HOLD DATA END UK79786 HOLD DATA START UK79786 HOLD DATA END UK79794 HOLD DATA START UK79794 HOLD DATA END UK79815 HOLD DATA START UK79815 HOLD DATA END UK79821 HOLD DATA START ++ HOLD(UK79821) SYS FMID(HDBAA10) REASON(ACTION) DATE(12178) COMMENT (++HOLD ACTION FOR PM64354: The LOAD utility has been changed to build a unique compression dictionary when loading at the table space level on a COMPRESS YES classic or partition by range table space with 254 or less partitions for SHRLEVEL NONE LOADs. This change will result in LOAD requiring more above the bar virtual storage to support a unique compression dictionary for each partition. For range partitioned table spaces with 254 partitions this would result in about an extra 254MB of virtual storage above the bar. While this is a very small percentage of above the bar storage, it can become a problem on lpars with an inadequate amount of real storage to fulfill the above the bar virtual storage request.). UK79821 HOLD DATA END UK79825 HOLD DATA START UK79825 HOLD DATA END UK79829 HOLD DATA START 1++ HOLD(UK79829) SYS FMID(HDBAA10) REASON(ACTION) DATE(12178) COMMENT (***Action for PM63574 (V10) This PTF modifies the DB2 installation CLIST to support changes by IRLM 2.3 PTF UK77877 (fixes APAR PM58723) which increased the maximum size for the IRLM LTE parameter from 1024 MB to 2048 MB. Before applying this PTF, verify that you have already applied PTF UK77877 to your DB2 V10 system. If you have already installed or migrated to DB2 V10 then after applying this PTF, you need to take the following actions: (1) Copy updated DB2 installation panels to alternate libraries (2) Update private copies of the DSNTIDxx CLIST input member Detailed guidance for these actions follows: ---------------------------------------------------------------- (1) Copy updated DB2 installation panels to alternate libraries ---------------------------------------------------------------- ==> This action is required for all V10 customers who maintain copies of the DB2 installation panels outside of SMP/E: This PTF modifies DB2 installation panel DSNTIPJ in the prefix.SDSNSPFP target library. If you keep the DB2 installation panels in a different library then after applying this PTF, you need to copy the updated DSNTIPJ panel to that library. ---------------------------------------------------------------- (2) Update private copies of the DSNTIDxx CLIST input member ---------------------------------------------------------------- ==> This action is required for all V10 customers This PTF modifies IRLMLTE, the entry for the IRLM LTE parameter in DB2 CLIST default input members, DSNTIDXA and DSNTIDXB, in the SDSNSAMP target library. You need to update this entry in all private copies of your CLIST output (DSNTIDxx) member. In each such copy, locate the entry for IRLMLTE and change the fifth field from 1024 to 2048 IRLMLTE NUM M 0 1024 x | | | +- local setting +- change for IRLM LTE this to 2048). UK79829 HOLD DATA END UK79832 HOLD DATA START UK79832 HOLD DATA END UK79834 HOLD DATA START ++ HOLD(UK79834) SYS FMID(HDBAA10) REASON(ACTION) DATE(12178) COMMENT 1 (With the application of the PTF for PM63835, because the STATUS column in SYSIBM.SYSTABLESPACE will now be correct when loading a partitioned table space with RI, it is possible that a CHECK DATA PART or a CHECK DATA SCOPE PENDING utility run on a child table space after being loaded might take longer to complete. This would be due to the utility reading the now correct STATUS column and deciding that all partitions for that child table space needed to be checked rather than just specfic or specified partitions.). UK79834 HOLD DATA END UK79844 HOLD DATA START UK79844 HOLD DATA END UK79870 HOLD DATA START UK79870 HOLD DATA END UK79871 HOLD DATA START ++ HOLD(UK79871) SYS FMID(HDBAA10) REASON(ACTION) DATE(12179) COMMENT (The PTF for APAR PM62786 has modified CSECTs in both the DSNUT101 and DSN3AMGP load modules, affecting the utility batch and DB2 MSTR address spaces. After the PTF has been applied, it will become active in the utility batch address space immediately upon job submission, while it will not take effect in the DB2 MSTR address space until DB2 is stopped and started. The PTF does not have to be applied to all members in a data sharing environment simultaneously, and may be staged across each member. However, until the fix is active in both the utility batch and DB2 MSTR address spaces, the problem the PTF addresses will not be resolved.). ++ HOLD(UK62466) SYS FMID(HDBAA10) REASON(ACTION) DATE(12179) COMMENT (The PTF for APAR PM25135 has modified CSECTs in both the DSNUT101 and DSN3AMGP load modules, affecting the utility batch and DB2 MSTR address spaces. After the PTF has been applied, it will become active in the utility batch address space immediately upon job submission, while it will not take effect in the DB2 MSTR address space until DB2 is stopped and started. The PTF does not have to be applied to all members in a data sharing environment simultaneously, and may be staged across each member. However, until the fix is active in both the utility batch and DB2 MSTR address spaces, the problem the PTF addresses will not be resolved.). UK79871 HOLD DATA END UK79881 HOLD DATA START UK79881 HOLD DATA END 1UK79896 HOLD DATA START ++ HOLD(UK79896) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12180) COMMENT (***Action for PM62928: See PM62928 APAR/PTF text for additional information about why a REBIND is necessary. PM62928 corrects a problem where various abends can occur for a query containing a table expression or view with duplicate columns on the select list when run in parallel. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK77033) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12180) COMMENT (See PM56548 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. APAR PM56548 corrects a problem that can cause an abend for a DELETE statement when it contains more than one subquery predicates and more than one subquery to join transformation occurs. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK76048) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12180) COMMENT (See PM53481 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. APAR PM53481 corrects problems that can cause an incorrect sort order for a query with group by case expression and order by accending on the same column. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK66301) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12180) COMMENT (See PM30196 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. APAR PM30196 corrects problems that can cause an incorrect output for a query using a set function like COUNT with DISTINCT on a DECFLOAT column. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). 1UK79896 HOLD DATA END UK79898 HOLD DATA START UK79898 HOLD DATA END UK79980 HOLD DATA START UK79980 HOLD DATA END UK79982 HOLD DATA START UK79982 HOLD DATA END UK80003 HOLD DATA START UK80003 HOLD DATA END UK80018 HOLD DATA START ++ HOLD(UK80018) SYS FMID(HDBAA10) REASON(ACTION) DATE(12185) COMMENT (PM62607 has modified REPAIR VERSIONS TABLESPACE to perform a DRAIN of all readers and writers on the table space. REPAIR VERSIONS updates the catalog and directory with version information for the table space when you have moved the object from one system to another using DSN1COPY with the OBIDXLAT option. REPAIR VERSIONS should be executed before any access to the data and should therefore block all readers and writers during the REPAIR operation.). UK80018 HOLD DATA END UK80020 HOLD DATA START UK80020 HOLD DATA END UK80030 HOLD DATA START UK80030 HOLD DATA END UK80032 HOLD DATA START UK80032 HOLD DATA END UK80068 HOLD DATA START ++ HOLD(UK80068) SYS FMID(HDBAA10) REASON(ACTION) DATE(12187) COMMENT (This DB2 PTF requires z/OS XML System Services PTF UA63422 or PTF UA65591. If UA63422 or PTF UA65591 is not applied, DB2 will convert the binary XML data to textual XML before executing XML schema validation.). UK80068 HOLD DATA END 1UK80106 HOLD DATA START UK80106 HOLD DATA END UK80113 HOLD DATA START ++ HOLD(UK80113) SYS FMID(HDBAA10) REASON(DOC) DATE(12188) COMMENT (With the PTF for PM52724, a new DB2 message, DSNU1544I, will be issued during the RECOVER utility with the BACKOUT YES option if the recovery cannot proceed: .. DSNU1544I csect RECOVER BACKOUT YES CANNOT PROCEED ON obj-type obj-qual.obj-nm DUE TO SQL MASS DELETE OR OTHER TABLE ACTIVITY .. Explanation: BACKOUT YES was specified for the RECOVER utility, but it cannot proceed because a prior SQL mass DELETE, TRUNCATE TABLE, or DROP TABLE occurred on the object. The deleted data for these activities are not logged, hence cannot be backed out. The recovery is prohibited when this kind of activity has occurred between the recovery point (specified by TOLOGPOINT or TORBA) and the current time. .. System action: The utility terminates processing. .. User response: Remove the BACKOUT YES option from the RECOVER utility statement and resubmit the job. This will recover the list of objects by restoring a backup then applying the logs forward to the recovery point. .. Severity: 8 (error) .. See the closing text of PM52724 for details.). ++ HOLD(UK80113) SYS FMID(HDBAA10) REASON(ACTION) DATE(12188) COMMENT (After the application of the PTF for APAR PM52724, the SYSIBM.SYSCOPY record with ICTYPE='L' and STYPE='M' will no longer be inserted for SQL mass delete, TRUNCATE TABLE, DROP TABLE, or ALTER TABLE ROTATE PARTITION on segmented or universal table spaces (UTS). .. A new DB2 message, DSNU1544I, will now be issued during the RECOVER utility with the BACKOUT YES option if the recovery cannot proceed: .. DSNU1544I csect RECOVER BACKOUT YES CANNOT PROCEED ON obj-type obj-qual.obj-nm DUE TO SQL MASS DELETE OR OTHER TABLE ACTIVITY .. Explanation: BACKOUT YES was specified for the RECOVER utility, but it cannot proceed because a prior SQL mass DELETE, TRUNCATE TABLE, or DROP TABLE occurred on the object. The deleted data for these 1 activities are not logged, hence cannot be backed out. The recovery is prohibited when this kind of activity has occurred between the recovery point (specified by TOLOGPOINT or TORBA) and the current time. .. System action: The utility terminates processing. .. User response: Remove the BACKOUT YES option from the RECOVER utility statement and resubmit the job. This will recover the list of objects by restoring a backup then applying the logs forward to the recovery point. .. Severity: 8 (error) .. See the closing text of PM52724 for details.). UK80113 HOLD DATA END UK80114 HOLD DATA START UK80114 HOLD DATA END UK80132 HOLD DATA START ++ HOLD(UK80132) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12190) COMMENT (This PTF corrects an abend problem that occurs when DB2 processes a parallel query with INLIST containing host variables The execution structures are not built in a correct order which causes the abend. To make this fix effective for static applications, you need to rebind the affected applications after you apply this PTF.). ++ HOLD(UK79655) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12190) COMMENT (***Action for PM56830: See PM56830 APAR/PTF text for additional information about why a REBIND is necessary. PM56830 corrects a problem of ABEND04E RC00C90101 CSECT DSNIWSET when running SQL statement containing recursive CTEs. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK80132 HOLD DATA END UK80166 HOLD DATA START UK80166 HOLD DATA END UK80178 HOLD DATA START 1UK80178 HOLD DATA END UK80179 HOLD DATA START UK80179 HOLD DATA END UK80180 HOLD DATA START UK80180 HOLD DATA END UK80192 HOLD DATA START UK80192 HOLD DATA END UK80230 HOLD DATA START ++ HOLD(UK80230) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12193) COMMENT (***Action for PM64111: See PM64111 APAR/PTF text for additional information about why a REBIND is necessary. PM64111 corrects a problem where SQLCODE -161 is issued incorrectly when UPDATE SYMMETRIC VIEW is used with IN-list access. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK80230 HOLD DATA END UK80234 HOLD DATA START UK80234 HOLD DATA END UK80236 HOLD DATA START UK80236 HOLD DATA END UK80242 HOLD DATA START UK80242 HOLD DATA END UK80259 HOLD DATA START UK80259 HOLD DATA END UK80263 HOLD DATA START UK80263 HOLD DATA END UK80266 HOLD DATA START ++ HOLD(UK80266) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12194) 1 COMMENT (***Action for PM63687: See PM63687 APAR/PTF text for additional information about why a REBIND is necessary. PM63687 corrects a problem of ABEND0C4-00000004 in dsnxripe +1DEA when a correlated subquery contains an IN-LIST table ( PLAN_TABLE ACCESSTYPE = 'IN') and the parent query block of the correlated subquery is running in parallel. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK80266 HOLD DATA END UK80291 HOLD DATA START UK80291 HOLD DATA END UK80310 HOLD DATA START UK80310 HOLD DATA END UK80324 HOLD DATA START ++ HOLD(UK80324) SYS FMID(HDREA10) REASON(EXIT) DATE(12198) COMMENT (Installation of this PTF requires that you: 1. Verify that the exit options in the exit are set to values that are appropriate for your installation. 2. Assemble and link-edit the exit and place the load module into the APF-authorized DB2 exit load library. After you complete these steps, the RACF access control module will be initialized the next time the DB2 subsystem is started. Further information may be found in "RACF Access Control Module Guide" (SC19-2982).). ++ HOLD(UK65253) SYS FMID(HDREA10) REASON(EXIT) DATE(12198) COMMENT (Installation of this PTF requires that you: 1. Verify that the exit options in the exit are set to values that are appropriate for your installation. 2. Assemble and link-edit the exit and place the load module into the APF-authorized DB2 exit load library. 1 After you complete these steps, the RACF access control module will be initialized the next time the DB2 subsystem is started. Further information may be found in "RACF Access Control Module Guide" (SC19-2982).). ++ HOLD(UK64306) SYS FMID(HDREA10) REASON(EXIT) DATE(12198) COMMENT (Installation of this PTF requires that you: 1. Verify that the exit options in the exit are set to values that are appropriate for your installation. 2. Assemble and link-edit the exit and place the load module into the APF-authorized DB2 exit load library. After you complete these steps, the RACF access control module will be initialized the next time the DB2 subsystem is started. Further information may be found in "RACF Access Control Module Guide" (SC19-2982).). ++ HOLD(UK80324) SYS FMID(HDREA10) REASON(DOC) DATE(12198) COMMENT (The "RACF Access Control Module Guide" (SC19-2982-01) is updated with the changes below. Chapter 14: RACF authorization checking reference - In the "Package Privileges" section, update SUBPKAUTK. XAPLPRIV value: SUBPKAUTK Privcode 229 (x'E5') The user must have sufficient authority to: ------------------------------------------------------- | One of these resources: | In class: | ------------------------------------------------------- |DB2-subsystem.collection-ID.PACKADM | DSNADM | |The user has authority to collection-ID.| | | | | |DB2-subsystem.ACCESSCTRL | DSNADM | | | | |DB2-subsystem.SYSCTRL | DSNADM | |Bypass if Separate Security = Yes | | | | | |DB2-subsystem.SYSADM | DSNADM | |Bypass if Separate Security = Yes | | | | | |DB2-subsystem.SECADM | DSNADM | ------------------------------------------------------- 1 - In the "System Privileges" section, add ACNTLAUTU. XAPLPRIV value: ACNTLAUTU Privcode 289 (x'121') The user must have sufficient authority to: ------------------------------------------------------- | One of these resources: | In class: | ------------------------------------------------------- |DB2-subsystem.ACCESSCTRL | DSNADM | | | | |DB2-subsystem.SYSCTRL | DSNADM | |Bypass if Separate Security = Yes | | | | | |DB2-subsystem.SYSADM | DSNADM | |Bypass if Separate Security = Yes | | | | | |DB2-subsystem.SECADM | DSNADM | -------------------------------------------------------). ++ HOLD(UK65253) SYS FMID(HDREA10) REASON(DOC) DATE(12198) COMMENT (The "RACF Access Control Module Guide" (SC19-2982-01) is updated with the changes below. Chapter 14: RACF authorization checking reference - In the "System privileges" section, the system DBADM authority is removed from the STOPALL (CHKSUBSU) and the "CANCEL | START |STOP DDF" (CHKDDFU, CHKSTARTU, CHKSTOPU) privilege set checks. - In the "Buffer Pool Privileges" section, the system DBADM authority is removed from the USE (USEAUTB) privilege. - In the "Storage Group Privileges" section, the system DBADM authority is removed from the USE (USEAUTS) privilege.). ++ HOLD(UK64306) SYS FMID(HDREA10) REASON(DOC) DATE(12198) COMMENT (The "RACF Access Control Module Guide" (SC19-2982) is updated with the changes below. Chapter 5: Protecting DB2 Objects - table-qualifier.table-name.view-qualifier.view-name is added to the table "DB2 object name qualifiers for RACF resources" for views. Chapter 10: Special Considerations - A new section "Authorization checking for operations on views" is added. For most operations on views, the RACF access control module checks for authorization on the view. Authorization checking for INSERT, 1 DELETE, and UPDATE are different because the operations on views can affect the underlying tables for the views. In general, three types of views can be defined: - Updatable view: For example, a view that is defined with simple column references in the SELECT list of the view definition, and a single table in the FROM clause of the view definition. An INSERT, DELETE, or UPDATE operation to the view is reflected to the underlying table. - Read-only view: For example, a view created from multiple tables. The INSERT, DELETE, and UPDATE operations fail for these views. - INSTEAD OF trigger view: The privilege is checked on the view, but the SQL in the trigger package can update the underlying table or tables. For INSERT, DELETE, and UPDATE operations on updatable views, the RACF access control module checks for authorization to resource name which includes both the underlying table information (qualifier and name) and view information (qualifier and name) and not to the view itself. For INSERT, DELETE, and UPDATE operations on read-only and INSTEAD OF trigger views, the RACF access control module checks for authorization on the view. If a view is created on another view, during view creation the RACF access control module does authorization checks for INSERT, DELETE, and UPDATE. These checks are done on the base view. Chapter 14: The DELETE, INSERT, and UPDATE privilege for views is updated: - If the view is updatable (for example, a view created from a single table), then the ownership check is done on the base table only if SETR MLS is not in effect. - For a DELETE (DELETAUT) operation on an updateable view, the first resource name which is checked is now: - db2-subsystem.table-qualifier.table-name.view-qualifier. view-name.DELETE 1 - For an INSERT (INSRTAUT) operation on an updateable view, the first resource name which is checked is now: - db2-subsystem.table-qualifier.table-name.view-qualifier. view-name.INSERT - For an UPDATE (UPDTEAUT) operation on an updateable view, the first and second resource names which are checked are now: - db2-subsystem.table-qualifier.table-name.view-qualifier. view-name.UPDATE - db2-subsystem.table-qualifier.table-name.column-name. view-qualifier.view-name.UPDATE For all of these resource names, the view and table information is truncated according to these rules: - table-qualifier and view-qualifier are truncated at 32 characters - Table-name and view-name are truncated at 64 characters.). ++ HOLD(UK64306) SYS FMID(HDREA10) REASON(ACTION) DATE(12198) COMMENT (The application of this PTF changes the RACF resources which are checked when a view is used in an INSERT, UPDATE, or DELETE operation. Application plans and packages that were bound with the RACF Access Control Module controlling access to views which are used for INSERT, UPDATE, or DELETE must be rebound. Details on the actions required can be found at: ftp://public.dhe.ibm.com/eserver/zseries/zos/racf/pdf /pm25823_act.pdf). UK80324 HOLD DATA END UK80349 HOLD DATA START UK80349 HOLD DATA END UK80359 HOLD DATA START UK80359 HOLD DATA END UK80363 HOLD DATA START UK80363 HOLD DATA END UK80364 HOLD DATA START ++ HOLD(UK62817) SYS FMID(HDBAA10) REASON(ACTION) DATE(12199) COMMENT (In version 10 coexistence mode, all active version 8 and 1 version 9 members MUST apply this APAR before any version 10 member with this APAR applied is restarted. If not applied properly, -DISPLAY THREAD SCOPE(GROUP) commands will not return valid data from remote version 8 and version 9 members which may result in a large amount of invalid output on the version 10 member. In addition, an ABEND0C4 in CSECT DSNVDTT may be encountered on the version 10 member.). UK80364 HOLD DATA END UK80366 HOLD DATA START UK80366 HOLD DATA END UK80384 HOLD DATA START UK80384 HOLD DATA END UK80387 HOLD DATA START UK80387 HOLD DATA END UK80389 HOLD DATA START UK80389 HOLD DATA END UK80407 HOLD DATA START UK80407 HOLD DATA END UK80412 HOLD DATA START ++ HOLD(UK80412) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12201) COMMENT (See PM65883 APAR/PTF text for additional information about why a REBIND is necessary. PM65883 prevents abend which might occur when a query involves push down predicate. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK80175) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12201) COMMENT (See PM63440 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. APAR PM63440 corrects problems that can cause abend for a query with range list index scan. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK80412 HOLD DATA END 1 UK80413 HOLD DATA START UK80413 HOLD DATA END UK80459 HOLD DATA START UK80459 HOLD DATA END UK80464 HOLD DATA START UK80464 HOLD DATA END UK80472 HOLD DATA START UK80472 HOLD DATA END UK80474 HOLD DATA START UK80474 HOLD DATA END UK80506 HOLD DATA START UK80506 HOLD DATA END UK80559 HOLD DATA START UK80559 HOLD DATA END UK80577 HOLD DATA START UK80577 HOLD DATA END UK80612 HOLD DATA START UK80612 HOLD DATA END UK80613 HOLD DATA START UK80613 HOLD DATA END UK80614 HOLD DATA START UK80614 HOLD DATA END UK80615 HOLD DATA START ++ HOLD(UK80615) SYS FMID(HDBAA10) REASON(ACTION) DATE(12209) COMMENT (***Action for PM65113 (V10) This PTF modifies the DB2 installation CLIST and job DSNTIJSG to prevent job step job DSNTIJQ from failing due to SQLCODE -204 when DSNTIJSG is customized in MIGRATE mode. If you have already installed or migrated to DB2 V10, you need to take the following actions after applying this PTF: 1 (1) Update customized copies of DB2 installation CLIST members (2) Update customized copies of job DSNTIJSG These actions are detailed below ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members *********************************************************** * This action is required for all V10 customers *********************************************************** This PTF modifies CLIST members DSNTINST in the prefix.SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your private copies of this CLIST. You may also want to move it to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. (2) Update customized copies of job DSNTIJSG *********************************************************** * This action is required only if you ran the DB2 V10 * installation CLIST in MIGRATE mode *********************************************************** This PTF modifies DSNTIJSG in the prefix.SDSNSAMP library only. After applying it, edit your customized copy of DSNTIJSG and update job step DSNTIJQ as follows: (a) Locate and change these lines: //SYSIN DD DDNAME=DUMMY //* DD DDNAME=CLEANUP //* DD DDNAME=CREATE // DD DDNAME=CREATENT //DUMMY DD * to: //SYSIN DD DDNAME=DUMMY //* DD DDNAME=CLEANUP // DD DDNAME=CREATE //* DD DDNAME=CREATEPT // DD DDNAME=CREATENT //DUMMY DD * (b) Locate and change these lines: CREATE TABLESPACE DSNOPTTS IN DSNOPTDB LOCKSIZE ROW LOCKMAX SYSTEM CLOSE NO CCSID UNICODE USING STOGROUP DSNOPTSG; CREATE TABLESPACE DSNOPTT8 IN DSNOPTDB BUFFERPOOL BP8K0 LOCKSIZE ROW LOCKMAX SYSTEM CLOSE NO CCSID UNICODE USING STOGROUP DSNOPTSG; to: CREATE TABLESPACE DSNOPTT8 1 IN DSNOPTDB BUFFERPOOL BP8K0 LOCKSIZE ROW LOCKMAX SYSTEM CLOSE NO CCSID UNICODE USING STOGROUP DSNOPTSG; //CREATEPT DD * CREATE TABLESPACE DSNOPTTS IN DSNOPTDB LOCKSIZE ROW LOCKMAX SYSTEM CLOSE NO CCSID UNICODE USING STOGROUP DSNOPTSG; (Notice that the order of the two CREATE TABLESPACE statements has been reversed). (c) Save your changes). UK80615 HOLD DATA END UK80616 HOLD DATA START UK80616 HOLD DATA END UK80665 HOLD DATA START UK80665 HOLD DATA END UK80675 HOLD DATA START UK80675 HOLD DATA END UK80677 HOLD DATA START UK80677 HOLD DATA END UK80684 HOLD DATA START UK80684 HOLD DATA END UK80686 HOLD DATA START UK80686 HOLD DATA END UK80701 HOLD DATA START ++ HOLD(UK80701) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12213) COMMENT (***Action for PM66948: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM66948 corrects a problem of inaccurate filter factor estimation for a predicate if the predicate references a 1 nullable column on which histogram statistics exist. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK77642) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12213) COMMENT (A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM49539 corrects an ABEND 0CF when incorrect internal values may be used to recalculate the filter factor of the related RANGE or BETWEEN predicates at execution time. The incorrect internal values may be saved by DB2 at BIND/PREPARE time if the package is bound with REOPT(AUTO), and one or more queries in the package contain RANGE or BETWEEN predicate(s). To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK80701 HOLD DATA END UK80772 HOLD DATA START UK80772 HOLD DATA END UK80780 HOLD DATA START UK80780 HOLD DATA END UK80787 HOLD DATA START UK80787 HOLD DATA END UK80791 HOLD DATA START ++ HOLD(UK80791) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12215) COMMENT (See PM65876 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. APAR PM65876 corrects problems that may cause a storage abend with over 510 columns put into a sort workfile. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK80176) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12215) COMMENT (See PM65289 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. APAR PM65289 corrects problems that can cause abend for Hybrid 1 join without cluster index, and lob column(s) is selected out. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK77479) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12215) COMMENT (***Action for PM57118: See PM57118 APAR/PTF text for additional information about why a REBIND is necessary. PM57118 corrects a problem of ABEND0C4 in DSNXOSRD may happen when running query with hybrid join on multiple tables. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK63992) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12215) COMMENT (***Action for PM27253: See PM27253 APAR/PTF text for additional information about why a REBIND is necessary. PM27253 corrects a problem of an incorrect output for a query which contains a sideway reference predicate in a table expression and the table expression may contain UNION/ UNION ALL/ EXCEPT/EXCEPT ALL/INTERSECT/INTERSECT ALL in it running in parallel. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK63532) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12215) COMMENT (***Action for PM25378: See PM25378 APAR/PTF text for additional information about why a REBIND is necessary. PM25378 corrects a problem of A query may get incorrout (no row found) when there is sideway reference predicate in one leg of UNION/UNION ALL/EXCEPT/EXCEPT ALL/INTERSECT/INTERSECT ALL with parallelism enabled. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK80791 HOLD DATA END 1UK80819 HOLD DATA START UK80819 HOLD DATA END UK80820 HOLD DATA START UK80820 HOLD DATA END UK80823 HOLD DATA START UK80823 HOLD DATA END UK80828 HOLD DATA START UK80828 HOLD DATA END UK80838 HOLD DATA START ++ HOLD(UK80838) SYS FMID(HDBAA10) REASON(ACTION) DATE(12219) COMMENT (PM66216 corrects a problem that ABEND04E RC00E20001 or RC00C90101 may happen when executing a native SQL procedure that contains a graphic string SQL statement used in a statement PREPARE or EXECUTE IMMEDIATE. After the application of this PTF, ALTER PROCEDURE REGENERATE is required for each native SQL procedure that is affected. Review the PTF cover letter to determine which, if any, native SQL procedure could be affected by this change.). UK80838 HOLD DATA END UK80858 HOLD DATA START UK80858 HOLD DATA END UK80881 HOLD DATA START UK80881 HOLD DATA END UK80900 HOLD DATA START UK80900 HOLD DATA END UK80924 HOLD DATA START UK80924 HOLD DATA END UK80926 HOLD DATA START UK80926 HOLD DATA END UK80931 HOLD DATA START UK80931 HOLD DATA END UK80983 HOLD DATA START 1UK80983 HOLD DATA END UK80999 HOLD DATA START UK80999 HOLD DATA END UK81022 HOLD DATA START UK81022 HOLD DATA END UK81026 HOLD DATA START UK81026 HOLD DATA END UK81046 HOLD DATA START UK81046 HOLD DATA END UK81047 HOLD DATA START UK81047 HOLD DATA END UK81050 HOLD DATA START UK81050 HOLD DATA END UK81051 HOLD DATA START ++ HOLD(UK81051) SYS FMID(HDBAA10) REASON(ACTION) DATE(12226) COMMENT (**Action for PM67892 (V10 CM8) Process the following commands to bind the packages that updated as a result of installing this PTF: BIND PACKAGE(DSNJAR) MEMBER(DSNX9INJ) - ACTION(REPLACE) ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC) - LIBRARY('.SDSNDBRM') BIND PACKAGE(DSNJAR) MEMBER(DSNX9RPJ) - ACTION(REPLACE) ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC) - LIBRARY('.SDSNDBRM') BIND PACKAGE(DSNJAR) MEMBER(DSNX9RMJ) - ACTION(REPLACE) ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC) - LIBRARY('.SDSNDBRM') BIND PACKAGE(DSNJAR) MEMBER(DSNX9DIJ) - ACTION(REPLACE) ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC) - LIBRARY('.SDSNDBRM') BIND PACKAGE(DSNJAR) MEMBER(DSNX9DPJ) - ACTION(REPLACE) ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC) - LIBRARY('.SDSNDBRM') BIND PACKAGE(DSNJAR) MEMBER(DSNX9DRJ) - ACTION(REPLACE) ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC) - LIBRARY('.SDSNDBRM') BIND PACKAGE(DSNJAR) MEMBER(DSNX9DUJ) - ACTION(REPLACE) ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC) - LIBRARY('.SDSNDBRM') 1 BIND PACKAGE(DSNJAR) MEMBER(DSNX9AJP) - ACTION(REPLACE) ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC) - LIBRARY('.SDSNDBRM') Note: is the data set prefix of your DB2 V10 target libraries). UK81051 HOLD DATA END UK81058 HOLD DATA START UK81058 HOLD DATA END UK81065 HOLD DATA START UK81065 HOLD DATA END UK81070 HOLD DATA START UK81070 HOLD DATA END UK81071 HOLD DATA START UK81071 HOLD DATA END UK81134 HOLD DATA START UK81134 HOLD DATA END UK81135 HOLD DATA START UK81135 HOLD DATA END UK81139 HOLD DATA START UK81139 HOLD DATA END UK81146 HOLD DATA START ++ HOLD(UK81146) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12229) COMMENT (PM67304 corrects a problem that unexpected SQLCODE -104, -313 or -206 could be issued when running a native SQL procedure if both of the following conditions are satisfied. 1. The native SQL procedure contains a FOR loop or any statement that references a DGTT (declare global temp table). 2. A warning was issued during creating or regenerating the native SQL procedure. After the application of this PTF, ALTER PROCEDURE REGENERATE is required for each native SQL procedure that is affected. Review the PTF cover letter to determine which, if any, native SQL procedure could be affected by this change. The query below helps to identify the qualified procedures. While more rows may be returned because of the keywords FOR or 1 SESSION may exist elsewhere. Also the keywords FOR and SESSION are case sensitive in the LIKE predicates, so they need to change according the case they are used in the native SQL procedures. SELECT SUBSTR(SCHEMA, 1, 8), SUBSTR(NAME, 1, 18) FROM SYSIBM.SYSROUTINES WHERE TEXT LIKE '%FOR%' OR TEXT LIKE '%SESSION%' OR TEXT LIKE '%for%' OR TEXT LIKE '%session%';). ++ HOLD(UK78930) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12229) COMMENT (PM60625 corrects a problem that ABEND04E RC00E70005 at the location DSNXGRDS.DSNXRLOB:P017 could happen after rebinding and then running a native SQL procedure containing an SELECT...INTO statement that selects CLOB or DBCLOB data into CHAR or VARCHAR variables with different CCSIDs. After the application of this PTF, ALTER PROCEDURE REGENERATE is required for each native SQL procedure that is affected. Review the PTF cover letter to determine which, if any, native SQL procedure could be affected by this change.). UK81146 HOLD DATA END UK81149 HOLD DATA START UK81149 HOLD DATA END UK81171 HOLD DATA START ++ HOLD(UK81171) SYS FMID(HDBAA10) REASON(DOC) DATE(12230) COMMENT (PM69542 introduces the following changes to externals: DB2 10 for z/OS has been changed to disregard the REMOTE(location-name,...,,...) bind option when it allocates packages for an application. DB2 will continue to validate all remote connections if ENABLE(REMOTE) or DISABLE(REMOTE) is specified. If REMOTE(location-name,...,< luname>,...) is also specified, it is ignored during package allocation. Information about this change will be included in the Information Management Software for z/OS Solutions Information Center (http://publib.boulder.ibm.com/infocenter/imzic) at a later date.). UK81171 HOLD DATA END UK81177 HOLD DATA START UK81177 HOLD DATA END 1UK81186 HOLD DATA START UK81186 HOLD DATA END UK81195 HOLD DATA START UK81195 HOLD DATA END UK81203 HOLD DATA START UK81203 HOLD DATA END UK81264 HOLD DATA START ++ HOLD(UK81264) SYS FMID(HDBAA10) REASON(ACTION) DATE(12235) COMMENT (***Action for PM66043 (V10) This PTF modifies V10 premigration checkout job DSNTIJPM. If you maintain a customized copy of this job then after applying this PTF, you need to refresh it as follows: - Edit your customized copy of DSNTIJPM - Delete all lines and copy in the DSNTIJPM member from the prefix.SDSNSAMP library - Follow the directions in the job prolog to customize it for your DB2 - If you are preparing to migrate to DB2 V10, run the updated customized job to check for conditions that may affect your migration.). ++ HOLD(UK76610) SYS FMID(HDBAA10) REASON(ACTION) DATE(12235) COMMENT (***Action for PM54699 (V10) This PTF modifies V10 premigration checkout job DSNTIJPM. If you maintain a customized copy of this job then after applying this PTF, you need to refresh it as follows: - Edit your customized copy of DSNTIJPM - Delete all lines and copy in the DSNTIJPM member from the prefix.SDSNSAMP library - Follow the directions in the job prolog to customize it for your DB2 - If you are preparing to migrate to DB2 V10, run the updated customized job to check for conditions that may affect your migration.). UK81264 HOLD DATA END UK81296 HOLD DATA START UK81296 HOLD DATA END UK81299 HOLD DATA START UK81299 HOLD DATA END UK81302 HOLD DATA START UK81302 HOLD DATA END 1 UK81304 HOLD DATA START UK81304 HOLD DATA END UK81306 HOLD DATA START UK81306 HOLD DATA END UK81314 HOLD DATA START UK81314 HOLD DATA END UK81331 HOLD DATA START UK81331 HOLD DATA END UK81332 HOLD DATA START ++ HOLD(UK81332) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12240) COMMENT (***Action for PM66953: See PM66953 APAR/PTF text for additional information about why a REBIND is necessary. PM66953 corrects a problem of ABEND04E 00C90101 AT DSNIDM.DSNIIMSI:5014 may occur for an SQL statement that satisfies the following conditions: 1. The statement references to a table expression or view 2. The statement contains a predicate that can be push down into the table expression or materialized view. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK81332 HOLD DATA END UK81340 HOLD DATA START ++ HOLD(UK81340) SYS FMID(HDBAA10) REASON(ACTION) DATE(12240) COMMENT (Any automated operations or applications that monitor space use in Workfile database may need to be aware of the table space allocation type-related adjustment that this PTF provides with regard to the workfile table space selection approach. Please see PM65767 APAR/PTF text for additional information.). UK81340 HOLD DATA END UK81341 HOLD DATA START UK81341 HOLD DATA END 1UK81367 HOLD DATA START ++ HOLD(UK81367) SYS FMID(HDBAA10) REASON(DOC) DATE(12241) COMMENT (The fix for APAR PM53502 added a new function to REPAIR LOCATE DELETE. DB2 for z/OS Utility Guide and Reference is updated to add a new optional keyword, DATAONLY, to the delete statement of REPAIR LOCATE. delete statement: >>-DELETE---+----------+------------------------------------>< |    |     '-DATAONLY-' DATAONLY Specifies that REPAIR should only DELETE the data record specified by the REPAIR LOCATE RID statement. DATAONLY can only be specified when locating a single row with RID. After using this option, if the the table has indexes or contains LOB or XML columns, the user needs to ensure the data is consistent with the other associated objects. A new message, DSNU677I is added to DB2 Messages manual: DSNU677I csect-name DELETE OPERATION CANNOT DELETE RELATED INDEX ENTRIES OR OTHER ASSOCIATED OBJECTS Explanation: A REPAIR control statement specified a DELETE operation for row in a table that has indexes, LOB columns, XML columns, or referential integrity constraints. REPAIR could only delete the table row. Severity: 4 (warning) System Action: The data record that is specified in the LOCATE block is deleted. However, any related index entries or associated objects are not deleted. System Programmer Response: Run the appropriate CHECK utilities to ensure that the data is consistent with the other associated objects, and follow the recommended actions.). ++ HOLD(UK81367) SYS FMID(HDBAA10) REASON(ACTION) DATE(12241) COMMENT (The PTF for APAR PM53502 has modified CSECTs in both the DSNUT910/DSNUT101 and DSNUTILA load modules, affecting the utility batch and DB2 DBM1 address spaces. After the PTF has been applied, it will become active in the utility batch address space immediately upon job submission, while it will not take effect in the DB2 DBM1 address space until DB2 is stopped and started. 1 The PTF does not have to be applied to all members in a data sharing environment simultaneously, and may be staged across each member. However, until the fix is active in both the utility batch and DB2 DBM1 address spaces, the problem the PTF addresses will not be resolved.). UK81367 HOLD DATA END UK81373 HOLD DATA START UK81373 HOLD DATA END UK81377 HOLD DATA START UK81377 HOLD DATA END UK81384 HOLD DATA START ++ HOLD(UK81384) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12241) COMMENT (***Action for PM69269: See PM69269 APAR/PTF text for additional information about why a REBIND is necessary. PM69269 corrects a problem of allowing page range in some cases when using optimization hints where page range would not be allowed if hints were not used. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK80981) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12241) COMMENT (***Action for PM65757: See PM65757 APAR/PTF text for additional information about why a REBIND is necessary. PM65757 corrects a problem that an ABEND04E RC00E70005 in DSNXOBM P030 may occur or incorrect output may be produced if an optimization hint is used. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK81384 HOLD DATA END UK81388 HOLD DATA START UK81388 HOLD DATA END UK81415 HOLD DATA START UK81415 HOLD DATA END 1 UK81419 HOLD DATA START UK81419 HOLD DATA END UK81424 HOLD DATA START UK81424 HOLD DATA END UK81425 HOLD DATA START ++ HOLD(UK81425) SYS FMID(HDBAA10) REASON(ACTION) DATE(12243) COMMENT (Prior to the application of this PTF, on the 'CONNECT TO USER USING ' SQL statement, lower case characters in the userid and password in Japanese Katakana CCSIDs (290, 930, 5026, 1390 or 8482) were sent to System Authorization Facility (SAF) interface unchanged. With the application of this PTF, the lower case characters will be folded to upper case before the ID and password are given to the SAF interface.). UK81425 HOLD DATA END UK81434 HOLD DATA START ++ HOLD(UK81434) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12243) COMMENT (See PM67529 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. APAR PM67529 corrects problems that can cause abends for scrollable cursor with reduced outer join and FIELDPROC. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK81434 HOLD DATA END UK81436 HOLD DATA START UK81436 HOLD DATA END UK81439 HOLD DATA START UK81439 HOLD DATA END UK81450 HOLD DATA START ++ HOLD(UK81450) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12244) COMMENT (***Action for PM68086: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. 1 PM68086 corrects a problem in which compound filter factor may be overestimated, potentially resulting in a suboptimal access path and poor performance. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK81450 HOLD DATA END UK81451 HOLD DATA START UK81451 HOLD DATA END UK81454 HOLD DATA START UK81454 HOLD DATA END UK81460 HOLD DATA START UK81460 HOLD DATA END UK81462 HOLD DATA START UK81462 HOLD DATA END UK81466 HOLD DATA START UK81466 HOLD DATA END UK81467 HOLD DATA START UK81467 HOLD DATA END UK81472 HOLD DATA START UK81472 HOLD DATA END UK81474 HOLD DATA START ++ HOLD(UK81474) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12244) COMMENT (***Action for PM69859: See PM69859 APAR/PTF text for additional information about why a REBIND is necessary. PM69859 corrects a problem of ABEND0C4 RC04 IN DSNIWNRF+1EBE when query contains a GROUP BY clause within a table expression or materialized view and runs in parallel. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK81474 HOLD DATA END 1 UK81511 HOLD DATA START UK81511 HOLD DATA END UK81517 HOLD DATA START ++ HOLD(UK71313) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12248) COMMENT (***Action for PM45048: See PM45048 APAR/PTF text for additional information about why a REBIND is necessary. PM45048 corrects a problem of a wrong filter factor for the DISTINCT FROM predicate if COLCARDF of the column is 1. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK65982) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12248) COMMENT (***Action for PM31741: See PM31741 APAR/PTF text for additional information about why a REBIND is necessary. PM31741 corrects a problem that DB2 may intermittently produce an inaccurate filter factor of an equal join predicate when the columns on both sides have histogram statistics. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK81517 HOLD DATA END UK81520 HOLD DATA START UK81520 HOLD DATA END UK81537 HOLD DATA START UK81537 HOLD DATA END UK81542 HOLD DATA START UK81542 HOLD DATA END UK81543 HOLD DATA START UK81543 HOLD DATA END UK81546 HOLD DATA START 1UK81546 HOLD DATA END UK81581 HOLD DATA START ++ HOLD(UK81581) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12250) COMMENT (***Action for PM66095: See PM66095 APAR/PTF text for additional information about why a REBIND is necessary. PM66095 adds BIF_COMPATIBILITY support for VARCHAR(decimal) function, the CAST(decimal AS CHAR(n)) and CAST(decimal AS VARCHAR(n)) specifications. If V9 results are desired for these expressions then a rebind must be done if a package meets the following criteria: 1- The package has been bound on V10 2- The package contains static SQL using VARCHAR(decimal) or CAST(decimal AS CHAR(n)) or CAST(decimal AS VARCHAR(n)) To make this fix effective for a static application, it must be rebound after application of this PTF. The BIF_COMPATIBILITY system parameter should be set to V9_DECIMAL_VARCHAR before rebinding. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK79394) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12250) COMMENT (***Action for PM62940: See PM62940 APAR/PTF text for additional information about why a REBIND is necessary. PM62940 corrects a problem of incorrectly issued SQLCODE 445 for an INSERT statement whose source column is a table expression or view column. This column is CAST and derived from the NULLIF function which has an empty string as the argument. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK77128) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12250) COMMENT (***Action for PM55452: See PM55452 APAR/PTF text for additional information about why a REBIND is necessary. PM55452 corrects a problem that ABEND0C7 RC00000000 at the location DSNXGRDS.DSNXRFN OFFSET80DE (OFFSET80BE, or OFFSET85FE) could happen when an static sensitive scrollable for update cursor is defined for a SELECT statement where an OLAP function is used. 1 To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK81581) SYS FMID(HDBAA10) REASON(ACTION) DATE(12250) COMMENT (***Actions for PM66095 This PTF adds a new setting to DB2 subsystem parameter BIF_COMPATIBILITY. V9_DECIMAL_VARCHAR ---------------------------------------------------------------- DB2 Version 10 introduced an incompatible change in the formatting of decimal data by the CHAR, VARCHAR, CAST( ... AS CHAR) and CAST( ... AS VARCHAR) built-in functions (BIFs). Beginning in V10, when the input data is decimal, these functions no longer return: * leading zeros * the trailing decimal point character * leading blanks for positive decimal values (CHAR only) APAR PM29124 provided relief for the CHAR function incompatibility and provided a DB2 subsystem parameter, BIF_COMPATIBILITY with options V9 and CURRENT, to enable and disable it. This PTF increases the scope of DB2 subsystem parameter BIF_COMPATIBILITY to include VARCHAR, CAST( ... AS CHAR) and CAST( ... AS VARCHAR) BIFs. It also adds a third option, V9_DECIMAL_VARCHAR. These settings allow you to specify whether: * The current-release format should be returned by all of these functions (BIF_COMPATIBILITY=CURRENT) * The V9 format should be returned by all of these functions (BIF_COMPATIBILITY=V9_DECIMAL_VARCHAR) * The current release format should be used by all of these functions except the CHAR function which should return the V9 format (BIF_COMPATIBILITY=V9) +-------------------------------------------------+ | BIF_- | Example inputs and results (1) | | COMPAT- |---------------------------------------| | IBILITY | CHAR(000.1) | CHAR(1000.) | CHAR(1.1) | |---------|-------------|-------------|-----------| | CURRENT | '.1' | '1000' | '1.1' | |---------|-------------|-------------|-----------| | V9 | ' 000.1' | ' 1000.' | ' 1.1' | |---------|-------------|-------------|-----------| | V9_DEC- | | | | | IMAL_- | ' 000.1' | ' 1000.' | ' 1.1' | | VARCHAR | | | | +-------------------------------------------------+ +-------------------------------------------------+ | BIF_- | Example inputs and results (2) | 1 | COMPAT- |---------------------------------------| | IBILITY | VARCHAR('00.10') | VARCHAR('1.') | |---------|--------------------|------------------| | CURRENT | '.10' | '1' | |---------|--------------------|------------------| | V9 | '.10' | '1' | |---------|--------------------|------------------| | V9_DEC- | | | | IMAL_- | '0.10' | '1.' | | VARCHAR | | | +-------------------------------------------------+ For DB2 data sharing it is recommended but not required that all members use the same setting. BIF_COMPATIBILITY is externalized on installation panel DSNTIPX as BIF COMPATIBILITY. If you have already installed or migrated to this version of DB2 you need to take the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Copy updated DB2 installation panels to alternate libraries (3) Update your customized copy of job DSNTIJUZ (4) Update private copies of the DSNTIDxx CLIST input member (5) Modify and rebind any applications that use the VARCHAR(decimal), CAST( decimal AS CHAR) and CAST( decimal AS VARCHAR) Detailed guidance for these actions follows: ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members ---------------------------------------------------------------- ==> This action is required for all customers This PTF modifies CLIST member DSNTINST in the SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your copies of this CLIST. You may also want to move it to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. ---------------------------------------------------------------- (2) Copy updated DB2 installation panels to alternate libraries ---------------------------------------------------------------- ==> This action is required for all V10 customers who maintain copies of the DB2 installation panels outside of SMP/E This PTF modifies DB2 installation panels DSNTIPX in the SDSNSPFP target library. If you keep the DB2 installation panels in a different library then after applying this PTF, you need to copy the updated DSNTIP8 panel to that library. 1 ---------------------------------------------------------------- (3) Update your customized copy of job DSNTIJUZ ---------------------------------------------------------------- ==> This action is required for all customers This PTF modifies DB2 installation job DSNTIJUZ in the SDSNSAMP target library. After applying this PTF, you need to update your customized copy of this job as follows: * Add or update the keyword parameter BIF_COMPATIBILITY= (where is CURRENT, V9, or V9_DECIMAL_VARCHAR), to the invocation of the DSN6SPRM macro in your customized copy of installation job DSNTIJUZ. Make sure to add a continuation character in column 72 if needed. If you omit adding BIF_COMPATIBILITY here, the value will be set to the default of CURRENT when you assemble the DSNZPxxx module. * Run the first two steps of the DSNTIJUZ job you modified. * After the job completes, you must either use the -SET SYSPARM command or stop and restart DB2 for the change to take effect. ---------------------------------------------------------------- (4) Update private copies of the DSNTIDxx CLIST input member ---------------------------------------------------------------- ==> This action is required for all customers This PTF adds an entry for BIF_COMPATIBILITY to the CLIST default input members, DSNTIDXA and DSNTIDXB, in the SDSNSAMP target library. You need to add this entry to all private copies of your CLIST output DSNTIDxx member. In each such copy, add the following line: BIF_COMPATIBILITY CHAR G NONE NONE Change to the value you specified for BIF_COMPATIBILITY in step (3), above. ---------------------------------------------------------------- (5) Application and object changes may be needed when applying this PTF. The actions depend on the value of the BIF_COMPATIBILITY system parameter. Please read the applicable section below. ----------------------------------------------------------------). ++ HOLD(UK79759) SYS FMID(HDBAA10) REASON(ACTION) DATE(12250) COMMENT (***Action for PM63892 (V10) This PTF fixes a bug in the DB2 installation CLIST that can cause it to store more than one entry for subsystem parameter DSN6FAC.DDF_COMPATIBILITY to the CLIST output (DSNTIDxx) member. This PTF also fixes the installation CLIST not to force 1 EN_PJSJ=ON in job DSNTIJUZ when the CLIST is run in MIGRATE mode with an INPUT MEMBER NAME of DSNTIDXA or DSNTIDXB. If you have already installed or migrated to DB2 V10, you need to take the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Update private copies of the DSNTIDxx CLIST input member These actions are detailed below ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members *********************************************************** * This action is required for all V10 customers *********************************************************** This PTF modifies CLIST members DSNTINST in the prefix.SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your private copies of this CLIST. You may also want to move it to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. ---------------------------------------------------------------- (2) Update private copies of the DSNTIDxx CLIST input member *********************************************************** * This action is required for all V10 customers *********************************************************** This PTF fixes a bug in the DB2 installation CLIST that can cause it to store more than one entry for subsystem parameter DSN6FAC.DDF_COMPATIBILITY to the CLIST output (DSNTIDxx) member. This PTF also fixes the installation CLIST not to force EN_PJSJ=ON in job DSNTIJUZ when the CLIST is run in MIGRATE mode with an INPUT MEMBER NAME of DSNTIDXA or DSNTIDXB. You need to verify that private copies of your DSNTIDxx member contain at most one entry for DDF_COMPATIBILITY. Delete any duplicate entries, then verify that in the remaining entry, the last (rightmost) field contains the setting you use for DDF_COMPATIBILITY (either NONE or SP_PARMS_NJV). If you run DB2 with the DSN6SPRM.EN_PJSJ parameter set to OFF, you also need to verify that the entry for EB_PJSJ is correct in your DSNTIDxx member. If the setting (which is stored in the 6th field of the entry) is ON, you need to change it to OFF as shown below: EN_PJSJ CHAR M OFF ON OFF | EN_PJSJ setting --+). ++ HOLD(UK79550) SYS FMID(HDBAA10) REASON(ACTION) DATE(12250) COMMENT (***Action for PM63753 (V10) This PTF fixes a bug in DB2 subsystem parameter macro DSN6SPRM that causes it to increase SRTPOOL parameter settings of less 1 than 10000 to 10000. SRTPOOL, which specifies the amount of storage in kilobytes that is needed for the DB2 sort pool, can range from 240 to 128000. When this PTF is applied, SRTPOOL settings between 240 and 9999 will again be honored when you assemble the DB2 subsystem parameter (DSNZPxxx) module. If you have already installed or migrated to DB2 10 then, after applying this PTF, review your current setting for the SRTPOOL parameter. If that setting is less than 10000, be aware that the amount of storage available for the DB2 sort pool will be reduced from 10000 kilobytes to the specified setting (in kilobytes) the next time you assemble the DB2 subsystem parameter (DSNZPxxx) module.). UK81581 HOLD DATA END UK81612 HOLD DATA START ++ HOLD(UK81612) SYS FMID(HDBAA10) REASON(ACTION) DATE(12251) COMMENT (Users need to process the following commands to bind the packages that are updated as a result of installing this PTF: BIND PACKAGE(SYSXSR) MEMBER(DSNNVXCM) - ACTION(REPLACE) ISO(CS) CURRENTDATA(YES) ENCODING(UNICODE) - LIBRARY('.SDSNDBRM') BIND PACKAGE(SYSXSR) MEMBER(DSNNVXCV) - ACTION(REPLACE) ISO(CS) CURRENTDATA(YES) ENCODING(UNICODE) - LIBRARY('.SDSNDBRM') Note: is the data set prefix of your DB2 10 target libraries). UK81612 HOLD DATA END UK81618 HOLD DATA START UK81618 HOLD DATA END UK81641 HOLD DATA START UK81641 HOLD DATA END UK81668 HOLD DATA START UK81668 HOLD DATA END UK81677 HOLD DATA START UK81677 HOLD DATA END UK81679 HOLD DATA START UK81679 HOLD DATA END UK81685 HOLD DATA START 1UK81685 HOLD DATA END UK81688 HOLD DATA START ++ HOLD(UK81064) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12254) COMMENT (***Action for PM66232: See PM66232 APAR/PTF text for additional information about why a REBIND is necessary. PM66232 corrects a problem of incorrect output for an SQL statement that references to a CTE with ORDER BY clause and the order by column is not on the SELECT list of the CTE. The CTE contains a subquuery. To make this fix effective for a static application, it must be rebound after application of this PTF. Views or MQTs with the above symptom need to be dropped and recreated. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK79599) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12254) COMMENT (PM63969 corrects a problem where malformed text may appear in the TEXT column of DSN_PREDICAT_TABLE and/or NODE_DATA column of DSN_QUERY_TABLE. A rebind is necessary for static applications to pick up the correct text. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK81688 HOLD DATA END UK81697 HOLD DATA START UK81697 HOLD DATA END UK81719 HOLD DATA START UK81719 HOLD DATA END UK81720 HOLD DATA START UK81720 HOLD DATA END UK81723 HOLD DATA START UK81723 HOLD DATA END UK81725 HOLD DATA START UK81725 HOLD DATA END 1 UK81726 HOLD DATA START UK81726 HOLD DATA END UK81728 HOLD DATA START UK81728 HOLD DATA END UK81730 HOLD DATA START UK81730 HOLD DATA END UK81732 HOLD DATA START ++ HOLD(UK70649) SYS FMID(HDBAA10) REASON(ACTION) DATE(12255) COMMENT (***Action for PM42922: See PM37611 APAR/PTF text for additional information. PM42922 is a pre-conditioning APAR that includes changes to support a new function that will be enabled via PM37611. At this time, all of the changes are transparent to the user. In a data sharing group, this pre-conditioning APAR should be applied to all members before the enabling APAR, PM37611, is applied. The new function is not enabled until PM37611 is PM37611 removes the restriction and allows a timestamp with time zone column in a partitioning key as long as it is the last column in the partitioning key.). UK81732 HOLD DATA END UK81737 HOLD DATA START UK81737 HOLD DATA END UK81757 HOLD DATA START UK81757 HOLD DATA END UK81759 HOLD DATA START UK81759 HOLD DATA END UK81763 HOLD DATA START UK81763 HOLD DATA END UK81765 HOLD DATA START ++ HOLD(UK81765) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12256) COMMENT (***Action for PM69139: See PM69139 APAR/PTF text for additional information about why a REBIND is necessary. 1 PM69139 corrects a problem of inaccurate compound filter factor of two range predicates on the same column that may result in an inefficient access path. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK79977) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12256) COMMENT (***Action for PM64315: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM64315 corrects a problem in which the filter factor for a compound predicate containing an OR predicate could be overestimated. The incorrect compound filter factor could result in an inefficient access path and resulting poor query performance. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK79324) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12256) COMMENT (***Action for PM61658: See PM61658 APAR/PTF text for additional information about why a REBIND is necessary. PM61658 corrects a problem of over-estimated correlated subquery cardinality when global query optimization is enabled (OPTXQB=ON) and there are materialized views involved in the join between the parent query block and the correlated subquery. This may result in an inefficient access path. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK79618) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12256) COMMENT (***Action for PM56435: A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM56435 corrects a problem in which DB2 could compute an incorrect compound filter factor. This could lead to an inefficient access path and poor query performance. To make this fix effective for a static application, it must be 1 rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK71709) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12256) COMMENT (***Action for PM44942: See PM44942 APAR/PTF text for additional information about why a REBIND is necessary. PM44942 corrects a problem of a poorly performing access path caused by incorrect filter factor estimation for a compound predicate when frequency or histogram statistics exist, or column cardinality is 1 or 2. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK69998) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12256) COMMENT (***Action for PM32384: See PM32384 APAR/PTF text for additional information about why a REBIND is necessary. PM32384 corrects a problem of inaccurate index filter factor estimate when multi-column cardinality exists in some cases which may result in an inefficient access path. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK81765 HOLD DATA END UK81767 HOLD DATA START ++ HOLD(UK81767) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12256) COMMENT (***Action for PM67124: See PM67124 APAR/PTF text for additional information about why a REBIND is necessary. PM67124 corrects a problem of incorrect output when an index on expression has the source column of a key-expression as one of the index keys. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK81767 HOLD DATA END 1UK81769 HOLD DATA START ++ HOLD(UK81769) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12256) COMMENT (***Action for PM62583: A rebind is necessary to rebuild package and/or plan structures based on the changes in this APAR. PM62583 corrects a problem that DB2 may improperly ignore bind/rebind option REOPT(ALWAYS) if the option APREUSE(ERROR) is specified too. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK81769 HOLD DATA END UK81770 HOLD DATA START ++ HOLD(UK81770) SYS FMID(HDBAA10) REASON(ACTION) DATE(12256) COMMENT (***Action for PM60921: This PTF adds a new option, 2, to DB2 subsystem parameter DSN6SPRM.QUERY_ACCEL_OPTIONS. This parameter is used to specify additional types of SQL queries to be included in query offloading. It previously had two options: * NONE: Query offloading is restricted to the standard SQL statements ==> This is the default setting. * 1 : The queries that include data encoded by multi-byte character set EBCDIC encoding scheme are not blocked from executing on IDAA. See APAR PM60170 for more information about this option. The new option, 2, specifies that: * 2 : For INSERT statements where a fullselect specifies the data to be inserted, the fullselect part is not blocked from executing on IDAA although the data operated on by the fullselect may not be current in IDAA. The fullselect will be considered for offload as if it were a stand-alone statement, except that DB2 will not offload the fullselect if the target table of the INSERT has a different encoding scheme than any table referenced in the fullselect. NONE remains the default setting for QUERY_ACCEL_OPTIONS and is mutually exclusive from all other settings. Options 1 and 2 can be specified individually or together. Examples: QUERY_ACCEL_OPTIONS=NONE QUERY_ACCEL_OPTIONS=1 QUERY_ACCEL_OPTIONS=2 QUERY_ACCEL_OPTIONS=(1,2) 1 If you have already installed or migrated to this version of DB2 you need to take the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Update your customized copy of job DSNTIJUZ (3) Update private copies of the DSNTIDxx CLIST input member Detailed guidance for these actions follows: ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members ---------------------------------------------------------------- ==> This action is recommended for all customers This PTF modifies CLIST member DSNTINST in the SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your copy of this CLIST. You may also want to move it to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. ---------------------------------------------------------------- (2) Update your customized copy of job DSNTIJUZ ---------------------------------------------------------------- ==> This action is optional After applying this PTF, if you want to specify that, for queries that include an INSERT from SELECT statement, the SELECT part is not blocked from executing on IDAA, you need to update your customized copy of DSNTIJUZ as follows: * Add or update the keyword parameter QUERY_ACCEL_OPTIONS= where is 2 or (1,2) to the invocation of the DSN6SPRM macro. Make sure to add a continuation character in column 72 if needed. If your DSNTIJUZ job does not include QUERY_ACCEL_OPTIONS, that parameter will be set to the default of NONE when you assemble the DSNZPxxx module. * Run the first two steps of the DSNTIJUZ job you modified. * After the job completes, you must either use the -SET SYSPARM command or stop and restart DB2 for the change to take effect. ---------------------------------------------------------------- (3) Update private copies of the DSNTIDxx CLIST input member ---------------------------------------------------------------- ==> This action is required only if you added or updated the setting for QUERY_ACCEL_OPTIONS in the previous step. In that case, you need to add or update the entry for that parameter in all private copies of your CLIST output DSNTIDxx member. In each such copy, add or modify the following line: QUERY_ACCEL_OPTIONS CHAR M NONE NONE Change to the value specified for QUERY_ACCEL_OPTIONS in step (2), above. If you do not add QUERY_ACCEL_OPTIONS to a DSNTIDxx member, the DB2 installation CLIST will assume 1 QUERY_ACCEL_OPTIONS=NONE when run with that member as input.). UK81770 HOLD DATA END UK81819 HOLD DATA START UK81819 HOLD DATA END UK81822 HOLD DATA START UK81822 HOLD DATA END UK81826 HOLD DATA START UK81826 HOLD DATA END UK81851 HOLD DATA START ++ HOLD(UK80701) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12261) COMMENT (***Action for PM66948: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM66948 corrects a problem of inaccurate filter factor estimation for a predicate if the predicate references a nullable column on which histogram statistics exist. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK80118) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12261) COMMENT (***Action for PM64871: See PM64871 APAR/PTF text for additional information about why a REBIND is necessary. PM64871 corrects a problem of inaccurate filter factor estimation of an IN-subquery predicate that may result in an inefficient access path. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK77642) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12261) COMMENT (A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM49539 corrects an ABEND 0CF when incorrect internal values may be used to recalculate the filter factor of the related RANGE or BETWEEN predicates at execution time. The incorrect internal 1 values may be saved by DB2 at BIND/PREPARE time if the package is bound with REOPT(AUTO), and one or more queries in the package contain RANGE or BETWEEN predicate(s). To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK69338) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12261) COMMENT (***Action for PM37547: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM37547 corrects a problem in which DB2 may calculate an incorrect filter factor for an after join predicate on a column defined as NOT NULL. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK67935) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12261) COMMENT (***Action for PM36392: See PM36392 APAR/PTF text for additional information about why a REBIND is necessary. PM36392 corrects a problem that the filter factor of an equal predicate may be inaccurate if histogram statistics exist on a column of that predicate. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK64003) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12261) COMMENT (***Action for PM26521: See PM26521 APAR/PTF text for additional information about why a REBIND is necessary. PM26521 corrects a problem where the filter factor of an IS NULL predicate is underestimated as 1E-9 if there is a NULL value in the column, histogram statistics exist, but frequency statistics do not exist. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK64520) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12261) COMMENT 1 (See PM23073 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. PM23073 fixes a filter factor estimation problem for a LIKE predicate with MIXED data. The filter factor estimation problem could lead to a poor access path. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK80414) SYS FMID(HDBAA10) REASON(ACTION) DATE(12261) COMMENT (***Action for PM65872: See PM65872 APAR/PTF text for additional information about why a REBIND is necessary. PM65872 corrects a problem of infinite loop that can occur when there are a very large number of predicates, and items in the SELECT list, and at least one predicate is an equal-, ANY-, or IN-subquery. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK81851 HOLD DATA END UK81878 HOLD DATA START UK81878 HOLD DATA END UK81899 HOLD DATA START ++ HOLD(UK81899) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12262) COMMENT (See PM69176 APAR/PTF text for additional information about why a REBIND is necessary. PM69176 improves the XML virtual storage use for queries or INSERT via SELECT statements that use the XMLTABLE function with an output column of XML data type. To make the storage enhancement effective for a static application, it must be rebound after application of this PTF.). UK81899 HOLD DATA END UK81900 HOLD DATA START UK81900 HOLD DATA END UK81931 HOLD DATA START UK81931 HOLD DATA END 1 UK81944 HOLD DATA START UK81944 HOLD DATA END UK81946 HOLD DATA START ++ HOLD(UK81946) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12263) COMMENT (***Action for PM69992: See PM69992 APAR/PTF text for additional information about why a REBIND is necessary. PM69992 corrects a problem of ABEND04E RC00E2000F at DSNXEFB.DSNSVSFB offset 0858 or memory overlay when an SQL statement references a constant which is resolved as DECFLOAT datatype. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK81946 HOLD DATA END UK81960 HOLD DATA START UK81960 HOLD DATA END UK82006 HOLD DATA START ++ HOLD(UK82006) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12265) COMMENT (This PTF corrects a query clean up problem that occurs when the query contains a UNION or UNION ALL operator and dynamic statement caching is enabled. In order to expose the problem, same statement needs to be invoked multiple times. To make this fix effective for static applications, you need to rebind the affected applications after you apply this PTF.). ++ HOLD(UK66233) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12265) COMMENT (***Action for PM34291: See PM34291 APAR/PTF text for additional information about why a REBIND is necessary. PM34291 corrects a problem that can result in an SQLCODE -327 on behalf of MERGE processing because of an invalid sort input. This error may follow numerous SQLCODE -254 errors. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK82006 HOLD DATA END 1 UK82010 HOLD DATA START UK82010 HOLD DATA END UK82042 HOLD DATA START ++ HOLD(UK82042) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12268) COMMENT (***Action for PM69237: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM69237 corrects a problem in which an inefficient access path may be selected for a query containing a predicate on a timestamp column. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK78587) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12268) COMMENT (***Action for PM60422: See PM60422 APAR/PTF text for additional information about why a REBIND is necessary. PM60422 corrects a problem of abend may occur for a query selected by mutiple rowset cursor and for update cursor, and also contains IN subquery predicate on the target table. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK78548) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12268) COMMENT (***Action for PM58612: A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM58612 corrects the incorrect setting of an index screening predicate which may cause an ABEND when the query uses Hybrid Join. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK82042 HOLD DATA END UK82045 HOLD DATA START 1UK82045 HOLD DATA END UK82115 HOLD DATA START ++ HOLD(UK82056) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12269) COMMENT (***Action for PM64195: See PM64195 APAR/PTF text for additional information about why a REBIND is necessary. PM64195 corrects a problem of over estimated value for column CARDF in EXPLAIN table DSN_COLDIST_TABLE. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK82115 HOLD DATA END UK82131 HOLD DATA START UK82131 HOLD DATA END UK82163 HOLD DATA START ++ HOLD(UK82163) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12271) COMMENT (***Action for PM66487: See PM66487 APAR/PTF text for additional information about why a REBIND is necessary. PM66487 corrects a problem of column value overlay or truncation while executing a static insert statement, if the column has default value of USER or CURRENT SQLID. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK79943) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12271) COMMENT (***Action for PM63849: See PM63849 APAR/PTF text for additional information about why a REBIND is necessary. PM63849 corrects a problem of column value overlay or truncation while executing a static insert statement, if the column has default value of USER or CURRENT SQLID, and the table's encoding schema is UNICODE or ASCII, and the table is in Reordered Record Format (RRF). To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover 1 letter to determine which, if any, applications could be affected by this change.). UK82163 HOLD DATA END UK82190 HOLD DATA START ++ HOLD(UK82190) SYS FMID(HIZAA10) REASON(ACTION) DATE(12271) COMMENT (In order for this PTF to be fully effective, an IPL with CLPA or REFRESH DB2,EARLY is required. The DB2 member should be stopped before issuing the REFRESH DB2, EARLY command.). ++ HOLD(UK79291) SYS FMID(HIZAA10) REASON(ACTION) DATE(12271) COMMENT (This PTF requires either an ERLY code refresh or a re-IPL of zOS. To refresh, enter the following commands after applying the PTF: MODIFY LLA,REFRESH -STOP DB2 ... (only if the DB2 subsystem is started) -REFRESH DB2,EARLY -START DB2,PARM=... These commands are not necessary if you re-IPL after applying the PTF.). ++ HOLD(UK77481) SYS FMID(HIZAA10) REASON(ACTION) DATE(12271) COMMENT (This PTF requires either an ERLY code refresh or a re-IPL of zOS. To refresh, enter the following commands after applying the PTF: MODIFY LLA,REFRESH -STOP DB2 ... (only if the DB2 subsystem is started) -REFRESH DB2,EARLY -START DB2,PARM=... These commands are not necessary if you re-IPL after applying the PTF.). ++ HOLD(UK78499) SYS FMID(HIZAA10) REASON(ACTION) DATE(12271) COMMENT (***Action for PM57744: This PTF requires either an EARLY code refresh or a re-IPL of zOS. To refresh, enter the following commands after applying the PTF: MODIFY LLA,REFRESH -STOP DB2 ... (only if the DB2 subsystem is started) -REFRESH DB2,EARLY -START DB2,PARM=... 1 These commands are not necessary if you re-IPL after applying the PTF.). UK82190 HOLD DATA END UK82218 HOLD DATA START ++ HOLD(UK81263) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12272) COMMENT (***Action for PM67731 See PM67731 APAR/PTF text for additional information about why a REBIND is necessary. PM67731 corrects a problem that can return an incorrect result for a query that contains a SCALAR FULLSELECT on a select list involving a stage 2 predicate with a column that is also in the query's GROUP BY list. To make this fix effective for a static application,it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK82218) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12272) COMMENT (***Action for PM60726: See PM60726 APAR/PTF text for additional information about why a REBIND is necessary. PM60726 corrects a problem that can result in an ABEND04E RC00E70005 at location DSNXGDT2 M111 or incorrect output for queries that contain a GROUP BY with large columns where a unique index provides the support for the GROUP BY. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK82218 HOLD DATA END UK82225 HOLD DATA START UK82225 HOLD DATA END UK82304 HOLD DATA START UK82304 HOLD DATA END UK82331 HOLD DATA START UK82331 HOLD DATA END UK82333 HOLD DATA START ++ HOLD(UK82333) SYS FMID(HDBAA10) REASON(DOC) DATE(12277) COMMENT 1 (The following text is added to DB2 for z/OS Managing Performance, Chapter 15 Setting limits for system resource usage by using the resource limit facility, Limiting resource usage by client information for middleware servers: . The values for the client information that are found at the start of a new unit-of-work are used to determine which row of the RLMT table controls the processing of dynamic requests for the duration of the unit-of-work.). UK82333 HOLD DATA END UK82335 HOLD DATA START UK82335 HOLD DATA END UK82365 HOLD DATA START UK82365 HOLD DATA END UK82379 HOLD DATA START UK82379 HOLD DATA END UK82384 HOLD DATA START UK82384 HOLD DATA END UK82408 HOLD DATA START UK82408 HOLD DATA END UK82421 HOLD DATA START UK82421 HOLD DATA END UK82457 HOLD DATA START UK82457 HOLD DATA END UK82460 HOLD DATA START UK82460 HOLD DATA END UK82494 HOLD DATA START UK82494 HOLD DATA END UK82510 HOLD DATA START UK82510 HOLD DATA END UK82535 HOLD DATA START UK82535 HOLD DATA END UK82538 HOLD DATA START 1++ HOLD(UK81147) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12284) COMMENT (***Action for PM65851: See PM65851 APAR/PTF text for additional information about why a REBIND is necessary. PM65851 corrects a problem that incorrect output or SQLCODE -180 could happen for an SQL statement that satisfies all of the following conditions: 1. It contains UNION ALL and union distribution applies to the SQL statement. 2. It contains a predicate with an SQL scalar function, and the predicate is distributed to the UNION ALL branches. 3. The scalar function has an input parameter that is referenced multiple times in the RETURN statement of the function, and the input for this parameter contains host variables or parameter markers. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK82538) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12284) COMMENT (***Action for PM54599: See PM54599 APAR/PTF text for additional information about why a REBIND is necessary. PM54599 corrects a problem that incorrect output may be returned if a predicate containing scalar full select or containing CASE expression which has a subquery, and optimizer selects it as a matching or screening predicate for an index on expression index. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK74716) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12284) COMMENT (***Action for PM52371: See PM52371 APAR/PTF text for additional information about why a REBIND is necessary. PM52371 corrects a problem of unexpected SQLCODE -161 issued for an INSERT into SYMMETRIC view statement. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK82538 HOLD DATA END 1 UK82555 HOLD DATA START UK82555 HOLD DATA END UK82575 HOLD DATA START UK82575 HOLD DATA END UK82610 HOLD DATA START UK82610 HOLD DATA END UK82637 HOLD DATA START ++ HOLD(UK82499) SYS FMID(HDBAA10) REASON(DOC) DATE(12289) COMMENT (The REORG TABLESPACE utility on DSNDB06.SYSTSSTG or DSNDB06.SYSTSVOL with the SHRLEVEL NONE option will issue MSGDSNU1152I during the UTILINIT phase if there are any user defined indexes managed by DB2 storage groups. The explanation of DSNU1152I is modified .. DSNU1152I csect-name utility-name SHRLEVEL option OF object-type object-qualifier.object-name Explanation The SHRLEVEL option was specified incorrectly for a utility. csect-name The name of the control section that issued the message. utility-name The name of the utility: REORG TABLESPACE, REORG INDEX, or REBUILD INDEX. option The SHRLEVEL option: CHANGE, REFERENCE, or NONE. The combination of option and utility-name indicates what has occurred. CHANGE If utility is REORG TABLESPACE: SHRLEVEL CHANGE was specified when REORG TABLESPACE was run on a NOT LOGGED table space, or on a LOB table space for which the base table space is a NOT LOGGED table space. If utility is REBUILD INDEX or REORG INDEX: SHRLEVEL CHANGE was specified when REBUILD INDEX or REORG INDEX was run on an index that is defined on a NOT LOGGED table space. REFERENCE SHRLEVEL REFERENCE was specified when REORG TABLESPACE with PART was run on a NOT LOGGED table space on which nonpartitioned indexes are defined. 1 NONE SHRLEVEL NONE cannot be specified for a table space with hash organization. | SHRLEVEL NONE cannot be specified with REORG TABLESPACE | against either DSNDB06.SYSTSVOL or DSNDB06.SYSTSSTG when | there are any user defined indexes which are stogroup | defined. object-type The type of object: INDEX, INDEXSPACE, or TABLESPACE. object-qualifier.object-name The name of the object, as a qualified name. System action If the utility was operating on a list of objects and OPTIONS EVENT(ITEMERROR,SKIP) was specified, then processing continues to the next object in the list. Otherwise, processing terminates. Operator response Take the appropriate action for the object-type value: NOT LOGGED TABLESPACE Remove the SHRLEVEL(CHANGE) or SHRLEVEL(REFERENCE) option from the utility statement. HASH TABLESPACE Specify either SHRLEVEL(CHANGE) or SHRLEVEL(REFERENCE) option in the utility statement. |CATALOG TABLESPACE DSNDB06.SYSTSVOL and DSNDB06.SYSTSSTG | Specify either SHRLEVEL(CHANGE) or SHRLEVEL(REFERENCE) | option in the utility statement Severity 8 (error)). UK82637 HOLD DATA END UK82643 HOLD DATA START UK82643 HOLD DATA END UK82645 HOLD DATA START UK82645 HOLD DATA END UK82672 HOLD DATA START UK82672 HOLD DATA END UK82674 HOLD DATA START UK82674 HOLD DATA END 1UK82678 HOLD DATA START ++ HOLD(UK82678) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12290) COMMENT (***Action for PM71335: See PM71335 APAR/PTF text for additional information about why a REBIND is necessary. PM71335 corrects a problem that incorrect output could happen for an SQL statement that satisfies all of the following conditions: 1. It contains multiple table expressions or views. 2. One of the table expression or view contains UNION ALL. 3. A predicate contains a special register that can be pushed down to the table expression or view. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK82678 HOLD DATA END UK82706 HOLD DATA START ++ HOLD(UK82706) SYS FMID(HDBAA10) REASON(ACTION) DATE(12291) COMMENT (This PTF does not fix any current incorrect values. After applying this fix, you may use the following sample query to see if there are any orphaned rows caused by the original error: SELECT * FROM SYSIBM.SYSROUTINES R WHERE R.ROUTINETYPE = 'F' AND R.FUNCTION_TYPE = 'S' AND R.ORIGIN = 'N' AND R.INLINE = 'N' AND NOT EXISTS ( SELECT * FROM SYSIBM.SYSPACKAGE P WHERE P.OWNER = R.SCHEMA AND P.NAME = R.NAME AND P.COLLID = R.COLLID AND P.VERSION = R.VERSION)! If this query returns any rows, please contact IBM for further instructions on how to resolve these rows.). UK82706 HOLD DATA END UK82714 HOLD DATA START ++ HOLD(UK78312) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12291) COMMENT (***Action for PM61426: A rebind is necessary for static applications to rebuild 1 access paths based on changes in this APAR. PM61426 corrects a problem in which frequency and histogram statistics will not be used for expressions when in conversion mode from DB2 9 (CM9). When index on expressions are used, this may lead to inefficient access path choices and poor query performance. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK70542) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12291) COMMENT (***Action for PM42686: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM42686 corrects a problem in which an incorrect filter factor may be selected for a predicate on a column with a COLCARDF of 1 or 2. The incorrect filter factor may result in a poor performing access path. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK82714 HOLD DATA END UK82733 HOLD DATA START UK82733 HOLD DATA END UK82752 HOLD DATA START UK82752 HOLD DATA END UK82764 HOLD DATA START ++ HOLD(UK82764) SYS FMID(HDBAA10) REASON(ACTION) DATE(12292) COMMENT (To use this new function to automatically issue a V WLM,APPLENV=applenvname,REFRESH when z/OS RRS is recycled, you must: . Add a //AUTOREFR DD DUMMY to the WLM established stored procedure startup proc that you require to be automatically refreshed. Note that the V WLM command has a Sysplex scope, so it will refresh the same WLM application environment on other MVS machines. . If a security product is configured to authorize MVS console commands, you must grant authorization for the DB2 ssnmMSTR address space to issue a MVS command. 1 . The DB2 Administration Guide has been updated with the following information: . Refreshing WLM environments for stored procedures automatically . You can enable automatic refreshes for WLM environments for stored procedures. . Procedure . To enable automatic refreshes of WLM environments for stored procedures: Add the following DD statement to the startup procedure for the WLM established stored procedure address space: //AUTOREFR DD DUMMY DB2 saves the environment name in a list of environments to refresh automatically. If the z/OS Resource Recovery Services environment is recycled, but DB2 and its associated WLM established stored procedures address spaces are not restarted, DB2 issues the following z/OS command to refresh each environment that is named in the list: VARY WLM,APPLENV=environment-name,REFRESH . Recommendation: To prevent the automatic issuing of duplicate and unnecessary VARY WLM,APPLENV=applenvname commands, limit the number of startup procedures that contain the //AUTOREFR DD statement. The WLM,APPLENV=applenvname command has a Sysplex scope, which means that it affects all servers of an application environment on all systems in the Sysplex. The result is that every DB2 subsystem issues its own WLM,APPLENV=applenvname command for each application environment that is nominated by the //AUTOREFR DD statement in its startup procedure. . Before you begin . If a security product is configured to authorize MVS console commands, you must grant authorization for the DB2 ssnmMSTR address space to issue MVS commands.). ++ HOLD(UK65858) SYS FMID(HDBAA10) REASON(ACTION) DATE(12292) COMMENT (A new option to be specified on the parameter to DSNX9WLM on the WLM startup JCL is introduced so user can specify a minimum number of address spaces to be kept for that particular environment. If this parameter is not specified, existing behavior prior to this change applies and WLM controls how many WLM-SPAS to keep. This new option will be implemented as new parameter to DSNX9WLM to the WLM start up JCL. For example: //DSN8WLMP PROC DB2SSN=DSN,NUMTCB=5,APPLENV=WLMTPSMP,MNSPAS=3 ... //WLMTPSMP EXEC PGM=DSNX9WLM,TIME=1440, 1 // PARM='&DB2SSN,&NUMTCB,&APPLENV,&MNSPAS', // REGION=0M In the above example, a minimum of 3 WLM-SPAS will be started each with NUMTCB = 5. WLM will start the minimum number of address spaces on the first stored procedure request on that WLM environment. The valid number specified for the values of MNSPAS is between 0 to 50. A value of 0 means no minimum is specified and old behavior will apply. If the value of MNSPAS is invalid, message DSNX981E will be issued. If the new parameter is not specified, there will be no impact to the existing user base.). UK82764 HOLD DATA END UK82765 HOLD DATA START ++ HOLD(UK82765) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12292) COMMENT (See PM70719 APAR/PTF text for additional information about why a REBIND is necessary. PM70719 corrects a problem of possible incorrect output for a query containing IN-lists and IN-subqueries with GROUP BY or ORDER BY. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK81312) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12292) COMMENT (***Action for PM67984: See PM67984 APAR/PTF text for additional information about why a REBIND is necessary. PM67984 corrects a problem of incorrectly ordered output from a query with an order by, group by and parallelism. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK81271) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12292) COMMENT (***Action for PM66684: See PM66684 APAR/PTF text for additional information about why a REBIND is necessary. PM66684 corrects a problem of possible incorrect output for a query using Omnifind and ORDER BY, GROUP BY, RANK, DENSE_RANK, or ROW_NUMBER. To make this fix effective for a static application, it must be 1 rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK78933) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12292) COMMENT (***Action for PM60664: See PM60664 APAR/PTF text for additional information about why a REBIND is necessary. PM60664 corrects a problem of ABEND0C4 RC38 in DSNXRSGB+0AFC on a query with GROUP BY in a subquery when the first join in the subquery is hybrid join without sort new and the sort for GROUP BY is avoided. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK76273) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12292) COMMENT (***Action for PM54468: A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM54468 corrects a problem in which an unnecessary sort was added for a query containing both ORDER BY and GROUP BY when IN list table access was chosen (ACCESSTYPE='IN'). To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK82765 HOLD DATA END UK82781 HOLD DATA START UK82781 HOLD DATA END UK82787 HOLD DATA START UK82787 HOLD DATA END UK82797 HOLD DATA START UK82797 HOLD DATA END UK82799 HOLD DATA START ++ HOLD(UK82799) SYS FMID(HDBAA10) REASON(ACTION) DATE(12296) COMMENT (See PM69433 APAR/PTF text in the PTF cover letter for additional information about this APAR. This APAR changed the datatype for ENDPOINT_URL and SOAP_ACTION 1 parameters for the DB2XML.SOAPHTTPNV and DB2XML.SOAPHTTPNC Web Services Consumer functions to VARCHAR(4096). For the DB2XML.SOAPHTTPC and DB2XML.SOAPHTTPV functions, these parameters remain as VARCHAR(256). To make the fix effective, the DB2 Web Services consumer functions need to be dropped and recreated. It is not required to drop and recreate these functions unless you want to specify ENDPOINT_URL and SOAP_ACTION settings of more than 256 single-byte characters. However, the WLM environment needs to be refreshed. In datasharing environment, the PTF needs to be applied to all members before the dropping and recreating of the UDFs. To recreate the functions, proceed as follows: (1) Drop the DB2 Web Services/SOAP routines DROP SPECIFIC FUNCTION DB2XML.SOAPHTTPNCICO RESTRICT; DROP SPECIFIC FUNCTION DB2XML.SOAPHTTPNCIVO RESTRICT; DROP SPECIFIC FUNCTION DB2XML.SOAPHTTPNVICO RESTRICT; DROP SPECIFIC FUNCTION DB2XML.SOAPHTTPNVIVO RESTRICT; (2) Run your customized copy of job DSNTIJRT with MODE(INSTALL) to recreate the DB2 Web Services/SOAP routines. Note: If you have previously run DSNTIJRT, rerunning with MODE(INSTALL) will cause it to detect and correct only missing and downlevel SQL objects and packages for DB2-supplied routines. Note: Use MODE(INSTALL-PREVIEW) to obtain a report of any changes without processing them. The PREVIEW option will also generate and output a JCL job to the JCLOUT DD that contains any SQL and bind statements to be processed. After reviewing the changes, either rerun DSNTIJRT without the PREVIEW option or customize and run the generated job. All customers who use DB2 Web Services/SOAP functions need to refresh the WLM environment for running these functions after applying this PTF.). UK82799 HOLD DATA END UK82805 HOLD DATA START ++ HOLD(UK82805) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12295) COMMENT (See PM69619 APAR/PTF text for additional information about why a REBIND is necessary. PM69619 corrects a problem that incorrect output may happen for a static SQL statement that satisfies all of the following conditions: 1 It references a table which is in PBR(partitioned by range) tablespace; 1 2 It contains a predicate on VARCHAR column which is a partition key of PBR table; 3 The option REOPT(ALWAYS) or REOPT(VARS) is specified when binding the package. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK82805 HOLD DATA END UK82853 HOLD DATA START UK82853 HOLD DATA END UK82889 HOLD DATA START UK82889 HOLD DATA END UK82964 HOLD DATA START ++ HOLD(UK82964) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12299) COMMENT (***Action for PM74279: See PM74279 APAR/PTF text for additional information about why a REBIND is necessary. PM74279 corrects a problem of ABEND0C7 at DSNXGRDS.DSNXRSBC+8086 or incorrect output could occur for an SQL statement that satisfies the following conditions: 1. contains a scalar fullselect, 2. the scalar fullselect contains an aggregate function in the select list, it involves invalid syntax. SQLCODE -122 is expected for this case. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK82964) SYS FMID(HDBAA10) REASON(ACTION) DATE(12299) COMMENT (***Action for PM74279: TYPE: ACT PM74279 corrects a problem of ABEND0C7 at DSNXGRDS.DSNXRSBC+8086 or incorrect output could occur for an SQL statement that satisfies the following conditions: 1. contains a scalar fullselect, 2. the scalar fullselect contains an aggregate function in the select list and it involves invalid syntax. SQLCODE -122 is expected for this case, but no error message is issued and the query completes successfully or abends. After the application of this PTF, ALTER PROCEDURE REGENERATE, ALTER FUNCTION REGENERATE is required for each native 1 SQL procedure or SQL scalar function that contains a scalar fullselect statement described as above.). UK82964 HOLD DATA END UK82977 HOLD DATA START UK82977 HOLD DATA END UK83061 HOLD DATA START UK83061 HOLD DATA END UK83088 HOLD DATA START UK83088 HOLD DATA END UK83093 HOLD DATA START UK83093 HOLD DATA END UK83110 HOLD DATA START UK83110 HOLD DATA END UK83114 HOLD DATA START UK83114 HOLD DATA END UK83168 HOLD DATA START ++ HOLD(UK83168) SYS FMID(HDBAA10) REASON(ACTION) DATE(12306) COMMENT (This PTF modifies DSN6SPRM.OPTIOWGT, a DB2 subsystem parameter that specifies how the DB2 optimizer should balance I/O cost and CPU cost estimation when selecting an access path. OPTIOWGT is deprecated in DB2 10, meaning that it is still supported but will be eliminated in a future release of DB2. Prior to this PTF, OPTIOWGT can be set to either ENABLE or DISABLE, where: - ENABLE indicates to use weights for I/O and CPU that better reflect improved I/O response and caching of newer hardware. In DB2 10, this is the default setting and should be used unless directed otherwise by IBM service. - DISABLE indicates to use weights for I/O and CPU that reflect I/O and CPU capabilities of older hardware. In DB2 V8 only, DISABLE is the default setting. However, in DB2 10, use of OPTIOWGT=DISABLE can result in degraded query performance and should be avoided. To ensure that customers who migrate to V10 avoid performance degradation through inadvertent use of OPTIOWGT=DISABLE, this PTF modifies OPTIOWGT as follows: * The existing settings of ENABLE and DISABLE are made equivalent. Specifying OPTIOWGT=DISABLE now has the same effect as specifying OPTIOWGT=ENABLE, which is that DB2 uses weights for I/O and CPU that better reflect improved I/O response and caching of newer hardware. The purpose of this 1 change is to ensure that your DB2 subsystem can perform optimally with the hardware that it runs on. * A new setting, OFF, is added for cases where DB2 needs to use weights for I/O and CPU that reflect I/O and CPU capabilities of older hardware. This is the behavior that was previously given by specifying OPTIOWGT=DISABLE. You should use OPTIOWGT=OFF only when advised to do so by IBM Service. If you have already installed or migrated to this version of DB2 you need to take the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Update your customized copy of job DSNTIJUZ (3) Update private copies of the DSNTIDxx CLIST input member Detailed guidance for these actions follows: ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members ---------------------------------------------------------------- ==> This action is required for all customers This PTF modifies CLIST member DSNTINST in the SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your copies of this CLIST. You may also want to move it to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. ---------------------------------------------------------------- (2) Update your customized copy of job DSNTIJUZ ---------------------------------------------------------------- ==> This action is required for all customers who use OPTIOWGT=DISABLE: This PTF modifies OPTIOWGT so that the DISABLE setting is equivalent to the ENABLE setting. If you use currently use OPTIOWGT=DISABLE, you are strongly recommended to use OPTIOWGT=ENABLE instead. Note that OPTIOWGT=ENABLE behavior will be activated on DB2 the next time you rebuild your DB2 subsystem (DSNZPxxx) module, even if you continue to use OPTIOWGT=DISABLE. Use the new OPTIOWGT=OFF setting only when specifically advised to do so by IBM Service. After applying this PTF, modify your customized copy of job DSNTIJUZ as follows: * Add or update the keyword parameter OPTIOWGT= (where is ENABLE, DISABLE, or OFF) to the invocation of the DSN6SPRM macro in your customized copy of installation job DSNTIJUZ. Make sure to add a continuation character in column 72 if needed. If you omit adding OPTIOWGT here, the value will be set to the recommended default of ENABLE when you assemble the DSNZPxxx module. * Run the first two steps of the DSNTIJUZ job you modified. 1 * After the job completes, you must either use the -SET SYSPARM command or stop and restart DB2 for the change to take effect. ---------------------------------------------------------------- (3) Update private copies of the DSNTIDxx CLIST input member ---------------------------------------------------------------- ==> This action is required for all customers This PTF modifies the entry for OPTIOWGT in the CLIST default input members, DSNTIDXA and DSNTIDXB, in the SDSNSAMP target library. You need to modify this entry in all private copies of your CLIST output DSNTIDxx member. In each such copy, change the following line: OPTIOWGT CHAR M DISABLE ENABLE to OPTIOWGT CHAR M NONE NONE Change to the value you specify for OPTIOWGT in your private copy of the DSNTIJUZ job.). UK83168 HOLD DATA END UK83171 HOLD DATA START ++ HOLD(UK83171) SYS FMID(HDBAA10) REASON(ACTION) DATE(12306) COMMENT (This PTF adds a new DB2-supplied stored procedure called SYSPROC.ADMIN_UPDATE_SYSPARM that can be used to change the value of one or more DB2 subsystem parameters. *************************************************************** Actions for customers who are already using DB2 V10: *************************************************************** If you have already installed or migrated to V10, the following actions are required after you apply this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Copy updated DB2 installation panels to alternate libraries (3) Update customized copies of installation job DSNTIJRT (4) Update private copies of the DSNTIDxx CLIST input member (5) Run installation job DSNTIJRT to install new objects (6) Create the customized DSNADMZW library member (7) Update customized copies of installation job DSNTIJMV (8) Update the DB2 REXX WLM environment address space proc (9) Refresh the WLM environment (10) Run installation job DSNTIJRV to validate new objects These actions are detailed below: ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members *********************************************************** * This action is required for all V10 customers *********************************************************** 1 This PTF modifies CLIST members DSNTINST, DSNTINSV, and DSNTINS1, DSNTINS2, and DSNTINS3 in the prefix.SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your private copies of these CLISTs. You may also want to move them to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. ---------------------------------------------------------------- (2) Copy updated DB2 installation panels to alternate libraries *********************************************************** * This action is required for all V10 customers who maintain * copies of the DB2 installation panels outside of SMP/E *********************************************************** This PTF modifies the DB2 installation panels DSNTIPRA and DSNTIPRL in the prefix.SDSNSPFP target library. If you keep the DB2 installation panels in a different library then after applying this PTF, you need to copy the updated panels to that library. ---------------------------------------------------------------- (3) Update customized copies of installation job DSNTIJRT *********************************************************** * This action is required for all V10 customers *********************************************************** This PTF modifies installation job DSNTIJRT in the prefix.SDSNSAMP target library. After you apply this PTF, you need to add the following to the list of configuration control statements under the CFIGIN DD in your customized copy of DSNTIJRT: SYSPROC.ADMIN_UPDATE_SYSPARM WLMENV(DSNWLM_REXX) GRANTTO(PUBLIC) Notes: (a) Always leave a blank line between each configuration control statement (b) Inspect each of the new configuration control statement and if required: - Change the WLMENV parameter settings to the name of the WLM environment you use to run DB2 REXX routines at your site. Specify WLMENV(DSNWLM_NOT_ENABLED) to exclude this routine from validation by job DSNTIJRV. - Change the GRANTTO parameter setting to the authorization IDs that need access to this routine. ---------------------------------------------------------------- (4) Update private copies of the DSNTIDxx CLIST input member *********************************************************** * This action is required for all V10 customers *********************************************************** This PTF adds entries to store values entered into new fields on installation panel DSNTIPRL: - RTL38ACL: GRANT EXECUTE setting for SYSPROC.ADMIN_UPDATE_SYSPARM 1 - RTL38WLM: WLM ENV NAME setting for SYSPROC.ADMIN_UPDATE_SYSPARM You need to make the same changes to all private copies of your CLIST output (DSNTIDxx) member. In each such copy, add the following lines: RTL38ACL CHAR M NONE NONE PUBLIC RTL38WLM CHAR M NONE NONE DSNWLM_REXX The values shown in the 6th column are the DB2-supplied default settings. If you specified different settings in action (3) above, change the settings in column 6 accordingly. ---------------------------------------------------------------- (5) Run installation job DSNTIJRT to install new objects *********************************************************** * This action is required for all V10 customers *********************************************************** After you have completed the above actions, run your updated copy of job DSNTIJRT to install and grant access to the new routine, SYSPROC.ADMIN_UPDATE_SYSPARM, and the new created global temporary tables SYSIBM.SYSPARM_SETTINGS and SYSIBM.UPDSYSPARM_MSG. ---------------------------------------------------------------- (6) Create the customized DSNADMZW library member *********************************************************** * All V10 customers who want to use the stored procedure * SYSPROC.ADMIN_UPDATE_SYSPARM need to perform this step. *********************************************************** This PTF adds a member called DSNADMZW to the prefix.SDSNSAMP target library. This member contains a template for specifying the names of libraries and disk devices to be allocated by SYSPROC.ADMIN_UPDATE_SYSPARM. (a) Copy this member to a partitioned data set that can be allocated by the address space proc for the WLM environment for SYSPROC.ADMIN_UPDATE_SYSPARM. This data set must be a PDS/PDSE with LRECL=80 and RECFM=FB. (b) Edit your copy and customize and change the data set names as appropriate for your system, for example by changing 'DSN!!0' to the prefix of your DB2 target libraries. Note that DSN!!0.SRCLIB.DATA must be a PDS/PDSE with LRECL=80 and RECFM=FB. (c) Create the DSN!!0.SRCLIB.DATA library if it does not exist. ---------------------------------------------------------------- (7) Update customized copies of installation job DSNTIJMV *********************************************************** * All V10 customers who want to use the stored procedure * SYSPROC.ADMIN_UPDATE_SYSPARM need to perform this step. *********************************************************** This PTF modifies installation job DSNTIJMV in the prefix.SDSNSAMP target library. After you apply this PTF, you need to modify the WLMR address space proc generated by job step DSNTIMP (where is the name of 1 the DB2 subsystem or data sharing group) to allocate the DSNADMZW library member you created in the previous step. (a) Edit your customized DSNTIJMV job and locate the WLMR address space proc. (b) Add the following statements //**** Data sets required by ADMIN_UPDATE_SYSPARM //ZPMDFLTS DD DISP=SHR, // DSN=DSN!!0.NEW.SDSNSAMP(DSNADMZW) and change DSN!!0.NEW.SDSNSAMP to the name of the data set that contains the DSNADMZW member that you created in the previous step. (c) Save your changes. ---------------------------------------------------------------- (8) Update the DB2 REXX WLM environment address space proc *********************************************************** * All V10 customers who want to use the stored procedure * SYSPROC.ADMIN_UPDATE_SYSPARM need to perform this step. *********************************************************** The address space proc for the DB2 REXX WLM environment specified for SYSPROC.ADMIN_UPDATE_SYSPARM in step (3) above requires a DD statement to allocate the DSNADMZW library member you created in step (6). (a) Edit the address space proc and add these statements: //**** Data sets required by ADMIN_UPDATE_SYSPARM //ZPMDFLTS DD DISP=SHR, // DSN=DSN!!0.NEW.SDSNSAMP(DSNADMZW) and change DSN!!0.NEW.SDSNSAMP to the name of the data set that contains the DSNADMZW member that you created in the step (6). (b) Save your changes. If you need to create an address space proc for a DB2 REXX WLM environment, use the following procedure: (a) Copy member DSN8WLMP from the prefix.SDSNSAMP library to a system proclib. (b) Edit this copy and customize it according to the instructions in the PROC prolog (c) Save your changes ---------------------------------------------------------------- (9) Refresh the WLM environment *********************************************************** * All V10 customers who want to use the stored procedure * SYSPROC.ADMIN_UPDATE_SYSPARM need to perform this step. *********************************************************** Issue the following VARY MVS command from the console to refresh the WLM application environment VARY WLM,APPLENV=,REFRESH where is the name of the WLM application environment for running the ADMIN_UPDATE_SYSPARM stored procedure. ---------------------------------------------------------------- (10) Run installation job DSNTIJRV to validate new objects *********************************************************** * All V10 customers who want to use the stored procedure 1 * SYSPROC.ADMIN_UPDATE_SYSPARM need to perform this step. *********************************************************** After you have completed the above actions, run your customized copy of job DSNTIJRV to validate installation and configuration of SYSPROC.ADMIN_UPDATE_SYSPARM. ----------------------------------------------------------------). ++ HOLD(UK82180) SYS FMID(HDBAA10) REASON(ACTION) DATE(12306) COMMENT (***Actions for PM70897 This PTF adds a new optional configuration parameter called INDEXSTOG to DSNTRIN, the program for installing and configuring DB2-supplied routines and their supporting DB2 objects. You can use INDEXSTOG to specify the name of the DB2 storage group for indexes created by DSNTRIN. DSNTRIN assumes that the specified storage group already exists. When INDEXSTOG is not given, DSNTRIN creates indexes in the SYSDEFLT storage group. After applying this PTF, you can optionally add the INDEXSTOG parameter to the DB2OPT DD of your private copy of job DSNTIJRT. The example below shows how to use INDEXSTOG to specify a storage group called IXSTOGRP: //DB2OPT DD * STOGROUP(TSSTOGRP) INDEXSTOG(IXSTOGRP) BP4K(BP0) BP8K(BP8K0) BP16K(BP16K0) BP32K(BP32K) LOBBP8K(BP8K0) LOBBP16K(BP16K0) //CFIGIN DD *). UK83171 HOLD DATA END UK83173 HOLD DATA START UK83173 HOLD DATA END UK83174 HOLD DATA START UK83174 HOLD DATA END UK83176 HOLD DATA START UK83176 HOLD DATA END UK83214 HOLD DATA START ++ HOLD(UK81821) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12307) COMMENT (***Action for PM70949: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. 1 PM70949 corrects a problem in which a tablespace scan access path may be incorrectly chosen when using an internal serviceability feature to influence access path selection with ACCESS_TYPE='INLIST'. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK81527) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12307) COMMENT (***Action for PM69949: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM69949 corrects a problem in which DB2 may choose a highly selective or index-only DPSI without page range when a more efficient non-DPSI index exists. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK80905) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12307) COMMENT (***Action for PM67292: A rebind is necessary to rebuild package and/or plan structures based on the changes in this APAR. PM67292 corrects a problem that DB2 may improperly favor range list index scan over multiple index access. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK80906) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12307) COMMENT (***Action for PM66098: See PM66098 APAR/PTF text for additional information about why a REBIND is necessary. PM66098 corrects a problem where DB2 does not select an equal unique index even though it may be the best choice, resulting in a query performance problem. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK80750) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12307) COMMENT 1 (***Action for PM62712: See PM62712 APAR/PTF text for additional information about why a REBIND is necessary. PM62712 corrects a problem of choosing an inefficient range-list access path. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK78278) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12307) COMMENT (***Action for PM61011: A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM61011 corrects a problem in which DB2 incorrectly chooses to sort instead of using reverse index scan to avoid the sort when OPTIMIZE FOR 1 ROW is specified. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK77766) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12307) COMMENT (***Action for PM59585: A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM59585 corrects a problem in which an inefficient access path is selected when multiple highly selective indexes exist that can provide the same matching coverage. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK83214 HOLD DATA END UK83215 HOLD DATA START ++ HOLD(UK83215) SYS FMID(HDBAA10) REASON(ACTION) DATE(12307) COMMENT (APAR PM64226 provides improved space reuse capability for directory LOB table spaces, specifically DSNDB01.SYSSPUXA, DSNDB01.SYSSPUXB and DSNDB01.SYSDBDXA. A secondary problem resolved by this APAR is LOB space allocation abends due to unformatted low level space map pages. This APAR will limit further space growth and prevent creation 1 of additional unformatted low level space map page within directory LOB table spaces. The REORG utility may be used to reclaim existing space and remove unformatted space map pages within these table spaces.). UK83215 HOLD DATA END UK83220 HOLD DATA START ++ HOLD(UK83220) SYS FMID(HDBAA10) REASON(ACTION) DATE(12309) COMMENT (The APAR for PM70287 allows a table space created before DB2 for z/OS V8 that has ALTER ADDed columns and that has not had a REORG and a MODIFY RECOVERY utility run to clear the ALTER flag, to be unloaded correctly with UNLOAD FORMAT INTERNAL. However, even after the application of this APAR, certain UNLOAD performance improvements cannot be taken advantage of if the REORG and MODIFY RECOVERY utilities have not been run on those table spaces. It is highly suggested that in order to take advantage of those UNLOAD performance improvements that a REORG and a MODIFY RECOVERY utility be run on table spaces that fall into the above situation.). UK83220 HOLD DATA END UK83248 HOLD DATA START UK83248 HOLD DATA END UK83279 HOLD DATA START ++ HOLD(UK82444) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12311) COMMENT (***Action for PM72671: See PM72671 APAR/PTF text for additional information about why a REBIND is necessary. PM72671 corrects a problem of an incorrect filter factor for a range predicate referencing a column whose data type is in a double-byte character set. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK83279) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12311) COMMENT (See PM69696 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. APAR PM69696 corrects problems that can cause abend for a query running with parallelism and the table has a decimal column defined as part of primary key. To make this fix effective for a static application, it must be 1 rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK79772) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12311) COMMENT (***Action for PM60914: See PM60914 APAR/PTF text for additional information about why a REBIND is necessary. PM60914 corrects a problem for: ABEND 04E-00E70005 at DSNXOINL:M100 will occur when a select statement runs in parallelism and contains a in-subquery predicate. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK79774) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12311) COMMENT (***Action for PM60536: See PM60536 APAR/PTF text for additional information about why a REBIND is necessary. PM60536 corrects two problems : 1.Incorrect no row found will occur when a query runs in I/O parallelism and involves the index on expression. 2.Abend 0C7-00000000 at DSNXRSFN+9694 will occur when a query runs in parallelism and contains set function in the select list. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK74876) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12311) COMMENT (See PM49532 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. PM49532 fixes ABEND04E RC00E72018 in DSNXSZK+2DBA or ABEND0CF RC0F in DSNXOLM1+C714 when a query is run in parallelism. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK70551) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12311) COMMENT (See PM41582 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. PM41582 fixes an incorrect output problem when a query 1 is run in parallelism with partition on the inner table (PART_INNER = 'Y' in DSN_PGROUP_TABLE). To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK83279 HOLD DATA END UK83312 HOLD DATA START ++ HOLD(UK83312) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12312) COMMENT (See PM72026 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. APAR PM72026 provides support for multiple index access on some non-Boolean term subquery predicates. APAR PM72026 adds online-changeable subsystem parameter in DSN6SPRM called SUBQ_MIDX that can be used to enable this performance improvement. The default value is DISABLE. If you have already installed or migrated to this version of DB2 you need to take the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Update your customized copy of job DSNTIJUZ (3) Update private copies of the DSNTIDxx CLIST input member (4) Rebind static applications that are affected by this change Detailed guidance for these actions follows: ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members ==> This action is required for all customers This PTF modifies CLIST member DSNTINST in the SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your copies of this CLIST. You may also want to move it to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. ---------------------------------------------------------------- (2) Update your customized copy of job DSNTIJUZ ==> This action is required for all customers This PTF modifies DB2 installation job DSNTIJUZ in the SDSNSAMP target library. After applying this PTF, you need to update your customized copy of this job as follows: * Add the keyword parameter SUBQ_MIDX=, where is DISABLE or ENABLE, to the invocation of the DSN6SPRM macro. Make sure to add a continuation character in column 72 if needed. If you omit adding SUBQ_MIDX here, the value will be set to the default of DISABLE when you 1 assemble the DSNZPxxx module. * Run the first two steps of the DSNTIJUZ job you modified. * After the job completes, you must either use the -SET SYSPARM command or stop and restart DB2 for the change to take effect. ---------------------------------------------------------------- (3) Update private copies of the DSNTIDxx CLIST input member ==> This action is required for all customers This PTF adds an entry for SUBQ_MIDX to the CLIST default input members, DSNTIDXA and DSNTIDXB, in the SDSNSAMP target library. You need to add these entries to all private copies of your CLIST output DSNTIDxx member. In each such copy, add the following lines: SUBQ_MIDX CHAR M DISABLE ENABLE Change to the value you specified for SUBQ_MIDX in step (2), above. ---------------------------------------------------------------- (4) Rebind static applications that are affected by this change ==> This action is required for all customers To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK80240) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12312) COMMENT (***Action for PM65041: See PM65041 APAR/PTF text for additional information about why a REBIND is necessary. PM65041 corrects an incorrect output problem where NOT IN predicates contain a NULL value. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK79602) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12312) COMMENT (***Action for PM60565: See PM60565 APAR/PTF text for additional information about why a REBIND is necessary. PM60565 corrects a problem of More rows may be returned when running query using parallelism on tables with null values. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover 1 letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK76189) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12312) COMMENT (***Action for PM52539: See PM52539 APAR/PTF text for additional information about why a REBIND is necessary. PM52539 corrects a problem of less rows returned when subquery containing UNION ALL in IN predicate. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK67536) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12312) COMMENT (***Action for PM33908: See PM33908 APAR/PTF text for additional information about why a REBIND is necessary. PM33908 corrects a problem of incorrect number of rows are retrieved for query containing multiple boolean term subqueries. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK83312 HOLD DATA END UK83315 HOLD DATA START UK83315 HOLD DATA END UK83318 HOLD DATA START UK83318 HOLD DATA END UK83371 HOLD DATA START UK83371 HOLD DATA END UK83402 HOLD DATA START UK83402 HOLD DATA END UK83405 HOLD DATA START UK83405 HOLD DATA END UK83424 HOLD DATA START UK83424 HOLD DATA END 1 UK83426 HOLD DATA START ++ HOLD(UK83426) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12314) COMMENT (***Action for PM74152: A rebind is necessary for static applications to rebuild internal structures used at runtime based on the changes in this APAR. PM74152 corrects a problem in which DB2 may incorrectly return no rows for a query using page range access where a range predicate exists on a partitioning column defined as CHAR(1). To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK74779) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12314) COMMENT (***Action for PM52548: See PM52548 APAR/PTF text for additional information about why a REBIND is necessary. PM52548 fixes an abend 0C4 RC00000038 DSNISPBM + 1A64 that can occur during query execution for queries containing IS NULL or LIKE predicates. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK71244) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12314) COMMENT (***Action for PM38082: See PM38082 APAR/PTF text for additional information about why a REBIND is necessary. PM38082 corrects a query performance problem involving significant loss of page range screening for queries with multi-column partitioning keys and predicates with host variables or parameter markers on those columns. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK64444) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12314) COMMENT (See PM25059 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. PM25059 corrects a problem where the page range reduction is done incorrectly. It may cause DB2 to choose a plan with 1 an extra GROUP BY / ORDER BY sort where it is not needed. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK83426 HOLD DATA END UK83436 HOLD DATA START UK83436 HOLD DATA END UK83458 HOLD DATA START ++ HOLD(UK83458) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12317) COMMENT (***Action for PM71471: See PM71471 APAR/PTF text for additional information about why a REBIND is necessary. PM71471 corrects a predicate localization problem when there is an EXISTS subquery in the CASE expression of the select list. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK83458 HOLD DATA END UK83462 HOLD DATA START ++ HOLD(UK83462) SYS FMID(HDBAA10) REASON(DOC) DATE(12317) COMMENT (New SQL code -4742 is added. The approximate description in the DB2 Codes manual for SQL code -4742 is shown below. Refer to the DB2 Codes manual for the exact description. -4742 The statement cannot be executed by DB2 or in the accelerator (REASON reason-code) Explanation: A statement cannot be executed by DB2 or by an accelerator server. The statement cannot be executed by DB2 for the following reason: . Special register CURRENT QUERY ACCELERATION is set to ALL, so the statement must be executed in the accelerator server. The statement cannot be executed by an accelerator server if any condition that is indicated by a reason code is true. reason-code A numeric value that provides additional information about why the statement cannot be executed in the accelerator server. 1 Possible values are: 1 No active accelerator server was found when the statement was executed. 2 The special register CURRENT QUERY ACCELERATION is set to NONE. 4 The query is not read-only. 6 The cursor is defined as scrollable or is a rowset- positioned cursor. 7 The query uses multiple encoding schemes. 8 The FROM clause of the query specifies a data-change-table-reference. 9 The query contains a nested table expression. 10 The query contains a recursive reference to a common table expression. 11 The query contains an unsupported expression. 12 The query references a table that has any of the following characteristics: - Is not defined in the accelerator server - Is defined in a different accelerator server from another table in the query - Is defined in the accelerator server, but is not enabled for query acceleration 13 The accelerator server that contains the tables that are referenced in the query is not started. 14 A column that is referenced in the query was altered in DB2 after the data was loaded in the accelerator server. 15 The query uses functionality that is available only in DB2 for z/OS Version 10 new-function mode or later. 17 The query is an INSERT from SELECT statement. Subsystem parameter QUERY_ACCEL_OPTIONS does not specify option 2 to enable the acceleration of INSERT from SELECT statements. System Action: The statement cannot be processed. Programmer Response: Use the reason code to determine the cause of the failure, and take the appropriate action: 1 Start the accelerator server. 2 Set special register CURRENT QUERY ACCELERATION to ENABLE, ENABLE WITH FAILBACK, ELIGIBLE, or ALL. 4 Ensure that the query meets the criteria for a read-only query. See the information on read-only cursors in "DECLARE CURSOR". 6 Remove the SCROLL clause or WITH ROWSET POSITIONING clause from the cursor declaration. 7 Ensure that all objects to which the query refers have the same encoding scheme. 8 Rewrite the FROM clause of the query so that it does not include a data-change-table-reference clause. 9 Ensure that the FROM clause of the query does not 1 contain a subquery. 10 Ensure that the FROM clause of the query does not reference a common table expression. 11 Remove the unsupported expression from the query. 12 Ensure that all tables that are referenced by the query are in the same accelerator server, and that all tables are enabled for query acceleration. 13 Start the accelerator server that contains the tables that are referenced in the query. 14 Load the altered table into the accelerator server again. 15 Ensure that the query does not use any functionality that is available in DB2 for z/OS Version 10 new-function mode or later. 17 Enable the acceleration of INSERT from SELECT statements by updating subsystem parameter QUERY_ACCEL_OPTIONS to include option 2. SQLSTATE: 560D5). ++ HOLD(UK83462) SYS FMID(HDBAA10) REASON(ACTION) DATE(12317) COMMENT (This PTF introduces two additional settings, ALL and ELIGIBLE, for the DSN6SPRM.QUERY_ACCELERATION parameter. This parameter specifies the default value for the CURRENT QUERY ACCELERATION special register. The new settings are: * ALL : Indicates that any given query will be accelerated if it is eligible for acceleration without heuristics and costing, and queries that are not eligible will not be executed in DB2, but will raise an SQL error instead. There is no failback with this option. * ELIGIBLE: Indicates that any given query will be accelerated if it is eligible for acceleration without heuristics and costing. Queries that are not eligible will be executed in DB2. There is no failback with this option. The ALL and ELIGIBLE settings join the existing settings of ENABLE, ENABLE_WITH_FAILBACK, and NONE (which is the default setting). See the DB2 Installation Guide for more information about these existing settings. If you have already installed or migrated to this version of DB2 you need to take the following actions after applying this PTF if you want to specify QUERY_ACCELERATION=ALL or ELIGIBLE: (1) Update your customized copy of job DSNTIJUZ (2) Update private copies of the DSNTIDxx CLIST input member Detailed guidance for these actions follows: ---------------------------------------------------------------- (1) Update your customized copy of job DSNTIJUZ ---------------------------------------------------------------- To modify the QUERY_ACCELERATION setting, you need to 1 update your customized copy of this job as follows: * Add or update the keyword parameter QUERY_ACCELERATION= (where is ALL, ELIGIBLE, ENABLE, ENABLE_WITH_FAILBACK, or NONE) to the invocation of the DSN6SPRM macro in your customized copy of installation job DSNTIJUZ. Make sure to add a continuation character in column 72 if needed. If you omit adding QUERY_ACCELERATION here, the value will be set to the default of NONE when you assemble the DSNZPxxx module. * Run the first two steps of the DSNTIJUZ job you modified. * After the job completes, you must either use the -SET SYSPARM command or stop and restart DB2 for the change to take effect. ---------------------------------------------------------------- (2) Update private copies of the DSNTIDxx CLIST input member ---------------------------------------------------------------- If you added or modified the QUERY_ACCELERATION parameter in step (1) above, you also need to add or update the entry for it in all private copies of your CLIST output DSNTIDxx member. In each such copy, add or update the following line: QUERY_ACCELERATION CHAR M NONE NONE Change to the value you specified for QUERY_ACCELERATION in step (1), above.). UK83462 HOLD DATA END UK83482 HOLD DATA START UK83482 HOLD DATA END UK83499 HOLD DATA START UK83499 HOLD DATA END UK83506 HOLD DATA START UK83506 HOLD DATA END UK83524 HOLD DATA START UK83524 HOLD DATA END UK83527 HOLD DATA START ++ HOLD(UK78788) SYS FMID(HDBAA10) REASON(DOC) DATE(12320) COMMENT (PM61532 modifies DB2 to issue SQLCODE -736 when CREATE TABLE is specified with OBID 1. The approximate text will be updated in the DB2 SQL Reference and Codes manual. This text is subject to change. 1 ============= SQL Reference ============= CREATE TABLE->OBID integer Identifies the OBID to be used for this table. An OBID is the identifier for an object's internal descriptor. The | integer must be greater than 1 and not identify an existing or previously used OBID of the database. If you omit OBID, DB2 generates a value. ===== Codes ===== -736 The specified OBID does not fall within the acceptable range | for OBIDs, which is 2 to 32767.). ++ HOLD(UK79834) SYS FMID(HDBAA10) REASON(ACTION) DATE(12320) COMMENT (With the application of the PTF for PM63835, because the STATUS column in SYSIBM.SYSTABLESPACE will now be correct when loading a partitioned table space with RI, it is possible that a CHECK DATA PART or a CHECK DATA SCOPE PENDING utility run on a child table space after being loaded might take longer to complete. This would be due to the utility reading the now correct STATUS column and deciding that all partitions for that child table space needed to be checked rather than just specfic or specified partitions.). ++ HOLD(UK78788) SYS FMID(HDBAA10) REASON(ACTION) DATE(12320) COMMENT (PM61532 modifies DB2 to issue SQLCODE -736 when CREATE TABLE is specified with OBID 1. Existing tables with OBID 1 should be dropped and recreated with a different OBID. A query, such as the sample query below may be used to identify tables with OBID 1. SELECT * FROM SYSIBM.SYSTABLES WHERE OBID = 1 AND ( TYPE = 'T' OR TYPE = 'M' OR TYPE = 'H' ) ;). UK83527 HOLD DATA END UK83543 HOLD DATA START ++ HOLD(UK83543) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12320) COMMENT (***Action for PM75197: See PM75197 APAR/PTF text for additional information about why a REBIND is necessary. PM75197 corrects a problem of inefficient access path for read only queries with FOR UPDATE. 1 To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK68203) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12320) COMMENT (***Action for PM32404: See PM32404 APAR/PTF text for additional information about why a REBIND is necessary. PM32404 corrects a problem of inaccurate filter factor estimation for a predicate that is generated by global optimization for an IN subquery or EXISTS subquery if there are multiple IN or EXISTS subqueries in the same query block. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK66477) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12320) COMMENT (***Action for PM29262: See PM29262 APAR/PTF text for additional information about why a REBIND is necessary. PM29262 corrects a problem of inaccurate output in DSN_DETCOST_TABLE when a materialized query block occurs. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK66093) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12320) COMMENT (***Action for PM25629: See PM25629 APAR/PTF text for additional information about why a REBIND is necessary. PM25629 resolves a case in which an inefficient join sequence could be chosen for a query block with four or fewer tables. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK67419) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12320) COMMENT (***Action for PM17234: See PM17234 APAR/PTF text for additional information about why a REBIND is necessary. PM17234 will allow more join permutations to be considered when 1 there are a high number of query blocks, but each query block contains less than or equal to four tables. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK83543 HOLD DATA END UK83547 HOLD DATA START UK83547 HOLD DATA END UK83630 HOLD DATA START UK83630 HOLD DATA END UK83632 HOLD DATA START UK83632 HOLD DATA END UK83642 HOLD DATA START UK83642 HOLD DATA END UK83648 HOLD DATA START UK83648 HOLD DATA END UK83679 HOLD DATA START UK83679 HOLD DATA END UK83718 HOLD DATA START UK83718 HOLD DATA END UK83726 HOLD DATA START UK83726 HOLD DATA END UK83731 HOLD DATA START UK83731 HOLD DATA END UK83742 HOLD DATA START ++ HOLD(UK83742) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12331) COMMENT (See PM69950 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. Apar PM69950 corrects the conditions that can lead to the occurrence of an ABEND04E RC00C90101 at location DSNOTCSO:5020 when executing a query that contains a select that references a HAVING clause with a LOB, but without a group by and set 1 function, running with parallelism. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK83742 HOLD DATA END UK83757 HOLD DATA START UK83757 HOLD DATA END UK83767 HOLD DATA START UK83767 HOLD DATA END UK83776 HOLD DATA START UK83776 HOLD DATA END UK83784 HOLD DATA START UK83784 HOLD DATA END UK83787 HOLD DATA START UK83787 HOLD DATA END UK83810 HOLD DATA START UK83810 HOLD DATA END UK83888 HOLD DATA START UK83888 HOLD DATA END UK83891 HOLD DATA START UK83891 HOLD DATA END UK83920 HOLD DATA START UK83920 HOLD DATA END UK83955 HOLD DATA START UK83955 HOLD DATA END UK83961 HOLD DATA START UK83961 HOLD DATA END UK83963 HOLD DATA START UK83963 HOLD DATA END 1 UK83995 HOLD DATA START UK83995 HOLD DATA END UK90054 HOLD DATA START UK90054 HOLD DATA END UK90078 HOLD DATA START UK90078 HOLD DATA END UK90088 HOLD DATA START UK90088 HOLD DATA END UK90094 HOLD DATA START UK90094 HOLD DATA END UK90215 HOLD DATA START ++ HOLD(UK90215) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12345) COMMENT (***Action for PM75486: See PM75486 APAR/PTF text for additional information about why a REBIND is necessary. PM75486 corrects a problem that incorrect output could happen for an SQL statement that satisfies all of the following conditions: 1. It contains a table expression or view with a set operator, and the table expression or view is materialized. 2. It contains a LIKE predicate that can be pushed down to the materialized table expression or view. 3. After push down, the LIKE predicate contains an expression, which matches to an index on expression. 4. The expressions in the different set branches return data with different nullability. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK82227) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12345) COMMENT (***Action for PM70345: See PM70345 APAR/PTF text for additional information about why a REBIND is necessary. PM70345 corrects a problem that performance regression could happen for a static SQL statement that has a non-boolean term predicate with Timestamp column. 1 To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK90215 HOLD DATA END UK90219 HOLD DATA START ++ HOLD(UK90219) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12346) COMMENT (***Action for PM73214: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM73214 corrects a problem of inaccurate cost estimation for a query when the query contains a view or table expression with UNION ALL. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK90219 HOLD DATA END UK90237 HOLD DATA START UK90237 HOLD DATA END UK90271 HOLD DATA START UK90271 HOLD DATA END UK90307 HOLD DATA START UK90307 HOLD DATA END UK90373 HOLD DATA START ++ HOLD(UK72803) SYS FMID(JDBAA17) REASON(DOC) DATE(12349) COMMENT (This PTF introduces the following changes to DB2 ODBC externals: New DB2 ODBC for z/OS initialization keywords described as follows. LIMITEDBLOCKFETCH = 0 | 1 This keyword is a performance optimization keyword that specifies whether DB2 ODBC is to attempt limited block fetch when fetching result sets from the connected data source. 0 = Limited block fetch is not used. 0 is the default. 1 = DB2 ODBC attempts to use limited block fetch. If blocking is supported at the server for the result set that is being fetched, DB2 ODBC retrieves as many rows as it can fit in a query data block in a single fetch request. 1 QUERYDATASIZE = integer This keyword specifies the amount of query data, in bytes, that DB2 returns on each FETCH operation when limited block fetch is enabled. The default value is 32767. Possible values are: 32767 294911 557055 819199 65535 327679 589823 851967 98303 360447 622591 884735 131071 393215 655359 917503 163839 425983 688127 950271 196607 458751 720895 983039 229375 491519 753663 1015807 262143 524287 786431 1048575 If you specify a value that is not a valid value, DB2 ODBC sets QUERYDATASIZE to the nearest valid value. Information about the new DB2 ODBC initialization keywords and DB2 ODBC limited block fetch will be included in the Information Management Software for z/OS Solutions Information Center (http://publib.boulder.ibm.com/infocenter/imzic) at a later date.). ++ HOLD(UK71561) SYS FMID(JDBAA17) REASON(DOC) DATE(12349) COMMENT (*** Documentation for PM25678 (Version 10) A new initialization keyword, INTERRUPT, has been added to allow you to specify the interrupt processing mode when SQLCancel() is called to cancel the processing on a statement. Complete documentation for the SQLCancel() API will be provided in the DB2 10 for z/OS ODBC Guide and Reference and in the Information Management Software for z/OS Solutions Information Center.). ++ HOLD(UK90373) SYS FMID(JDBAA17) REASON(ACTION) DATE(12349) COMMENT (*** Action for PM72999 (Version 10) This PTF includes the following updated DBRMs for the ODBC executable modules DSNAOCLI, DSNAOCLX, and DSNAO64C: DSNCLINF DSNCLIC1 DSNCLIMS DSNCLIQR DSNCLIV1 (DB2 for Linux, UNIX, and Windows servers only) After applying the PTF, all customers who use these modules need to bind a package from each of the DBRMs listed above. For example: BIND PACKAGE (.DSNAOCLI) MEMBER(DSNCLINF) ACTION(ADD) ENCODING(EBCDIC) BIND PACKAGE (.DSNAOCLI) MEMBER(DSNCLIC1) ACTION(ADD) ENCODING(EBCDIC) BIND PACKAGE (.DSNAOCLI) MEMBER(DSNCLIMS) ACTION(ADD) ENCODING(EBCDIC) BIND PACKAGE (.DSNAOCLI) MEMBER(DSNCLIQR) ACTION(ADD) ENCODING(EBCDIC) 1 BIND PACKAGE (.DSNAOCLI) MEMBER(DSNCLIV1) ACTION(ADD) ENCODING(EBCDIC) where is the location of the DBMS where the package binds and where the description of the package resides. Failure to bind the required packages could result in SQLCODE=-805 at application run time. Also, refer to the prolog of the CLI sample bind job DSNTIJCL in your SDSNSAMP data set for specific instructions on how to customize the BIND command for binding the DBRMs/packages and the application plan at your site. *** Attention: DB2 data sharing customers need to bind the package only once, after applying the PTF on the first member.). ++ HOLD(UK67822) SYS FMID(JDBAA17) REASON(ACTION) DATE(12349) COMMENT (*** Action for PM32032 (Version 10) The following database request module (DBRM) is updated by this PTF: DSNCLIQR Application of this PTF requires a BIND PACKAGE for the above package. Failure to rebind the required packages could result in SQLCODE=-805 at application run time. Refer to the sample DB2 CLI bind job DSNTIJCL in the SDSNSAMP data set for an example of how to perform the BIND command. Also, refer to the prolog of DSNTIJCL for specific instructions on how to bind each DBRM and on how to customize the DSNTIJCL job for use at your site.). ++ HOLD(UK72803) SYS FMID(JDBAA17) REASON(ACTION) DATE(12349) COMMENT (The following database request modules (DBRMs) are updated by this PTF: DSNCLINF DSNCLIC1 DSNCLIMS DSNCLIQR DSNCLIV1 (DB2 for Linux, UNIX, and Windows servers only) Application of this PTF requires a BIND PACKAGE for the above packages. Failure to rebind the required packages could result in SQLCODE=-805 at application run time. Refer to the sample DB2 CLI bind job DSNTIJCL in the SDSNSAMP data set for an example of how to perform the BIND command. Also, refer to the prolog of DSNTIJCL for specific instructions on how to bind each DBRM and on how to customize the DSNTIJCL job for use at your site. This PTF introduces two new DB2 ODBC initialization keywords LIMITEDBLOCKFETCH and QUERYDATASIZE, to enable limited block 1 fetch. Limited block fetch is a performance optimization that enables applications to improve the performance of FETCH operations when connected to a local DB2 for z/OS server. If limited block fetch is not desired, no further action is necessary other than rebinding the DBRMs listed above; otherwise please note the following. When you enable limited block fetch, the data that is returned to your application might not reflect the data that has been committed to the source table. If your application fetches data from tables that are updated by other users, or if your application uses savepoints and issues ROLLBACK TO SAVEPOINT to manage transactions, you should disable limited block fetch.). ++ HOLD(UK60517) SYS FMID(JDBAA17) REASON(ACTION) DATE(12349) COMMENT ( This PTF updates DB2 ODBC for z/OS database request module (DBRM) DSNCLIC1. After you apply this PTF, you need to run the BIND PACKAGE command to recreate the package for DSNCLIC1. Failure to bind the required package could result in SQLCODE -805 at application run time. Refer to the sample DB2 ODBC bind job DSNTIJCL in the prefix.SDSNSAMP data set for an example of how to perform the BIND command.). ++ HOLD(UK56516) SYS FMID(JDBAA17) REASON(ACTION) DATE(12349) COMMENT (The following database request modules (DBRMs) for ODBC DB2 for z/OS are updated by this PTF: DSNCLIMS DSNCLIC1 Application of this PTF requires a BIND PACKAGE for the above packages. Failure to rebind the required packages could result in SQLCODE=-805 at application run time. Refer to the sample DB2 ODBC bind job DSNTIJCL in the SDSNSAMP dataset for an example of how to perform the BIND command.). UK90373 HOLD DATA END UK90444 HOLD DATA START UK90444 HOLD DATA END UK90477 HOLD DATA START UK90477 HOLD DATA END UK90628 HOLD DATA START UK90628 HOLD DATA END 1 UK90637 HOLD DATA START UK90637 HOLD DATA END UK90642 HOLD DATA START UK90642 HOLD DATA END UK90662 HOLD DATA START UK90662 HOLD DATA END UK90663 HOLD DATA START ++ HOLD(UK83798) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12364) COMMENT (***Action for PM71904: See PM71904 APAR/PTF text for additional information about why a REBIND is necessary. PM71904 corrects a problem of poor query performance for a query with a predicate or predicates on a timestamp column inside an OR predicate. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK83458) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12364) COMMENT (***Action for PM71471: See PM71471 APAR/PTF text for additional information about why a REBIND is necessary. PM71471 corrects a predicate localization problem when there is an EXISTS subquery in the CASE expression of the select list. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK90663) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12364) COMMENT (A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM69713 corrects a problem where incorrect output can occur on a query if there are: 1. two or more predicates with IN-lists on the same column; 2. the predicates are involved in compound OR predicates; and 3. the OR predicates involve columns from more than one table. To make this fix effective for a static application, it must be 1 rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK90663 HOLD DATA END UK90674 HOLD DATA START UK90674 HOLD DATA END UK90677 HOLD DATA START UK90677 HOLD DATA END UK90699 HOLD DATA START UK90699 HOLD DATA END UK90722 HOLD DATA START UK90722 HOLD DATA END UK90725 HOLD DATA START UK90725 HOLD DATA END UK90728 HOLD DATA START ++ HOLD(UK90728) SYS FMID(HDBAA10) REASON(ACTION) DATE(13003) COMMENT (PM78210 fixes CREATE TABLE to define implicit table spaces in reordered row format(RRF) when all of the following conditions are true: o DSNZPARM SPRMRRF is set to DISABLE o The CREATE TABLE statement is specified with the BUFFERPOOL clause o The CREATE TABLE statement defines an inline LOB column This APAR does not fix existing objects. A query, such as the sample query below may be used to determine whether table spaces containing tables with inline LOB columns have partitions which are in basic row format (BRF). Affected table spaces must be dropped and recreated after this apar is applied. SELECT TPT.DBNAME, TPT.TSNAME, TPT.PARTITION FROM SYSIBM.SYSCOLUMNS COL, SYSIBM.SYSTABLES TAB, SYSIBM.SYSTABLEPART TPT WHERE ( ( COL.DATATYPEID = 404 OR COL.DATATYPEID = 408 OR COL.DATATYPEID = 412 OR COL.SOURCETYPEID = 404 OR COL.SOURCETYPEID = 408 OR COL.SOURCETYPEID = 412 ) AND COL.TBCREATOR = TAB.CREATOR AND COL.TBNAME = TAB.NAME AND TAB.DBNAME = TPT.DBNAME 1 AND TAB.TSNAME = TPT.TSNAME AND COL.LENGTH > 4 AND TPT.FORMAT = ' ' );). UK90728 HOLD DATA END UK90746 HOLD DATA START UK90746 HOLD DATA END UK90797 HOLD DATA START UK90797 HOLD DATA END UK90811 HOLD DATA START UK90811 HOLD DATA END UK90833 HOLD DATA START UK90833 HOLD DATA END UK90840 HOLD DATA START UK90840 HOLD DATA END UK90883 HOLD DATA START UK90883 HOLD DATA END UK90966 HOLD DATA START ++ HOLD(UK90966) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13015) COMMENT (***Action for PM77332: PM77332 corrects a problem of incorrout when spatial UDF is used in a query. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK90966 HOLD DATA END UK90990 HOLD DATA START ++ HOLD(UK90990) SYS FMID(HDBAA10) REASON(ACTION) DATE(13016) COMMENT (The PTF for APAR PM62002 has modified CSECTs in both the DSNUT101 and DSNUTILA load modules, affecting the utility batch and DB2 DBM1 address spaces. After the PTF has been applied, it will become active in the utility batch address space immediately upon job submission, while it will not take effect in the DB2 DBM1 address space until DB2 is stopped and started. The PTF does not have to be applied to all members in a data 1 sharing environment simultaneously, and may be staged across each member. However, until the fix is active in both the utility batch and DB2 DBM1 address spaces, the problem the PTF addresses will not be resolved.). UK90990 HOLD DATA END UK90993 HOLD DATA START ++ HOLD(UK81425) SYS FMID(HDBAA10) REASON(ACTION) DATE(13016) COMMENT (Prior to the application of this PTF, on the 'CONNECT TO USER USING ' SQL statement, lower case characters in the userid and password in Japanese Katakana CCSIDs (290, 930, 5026, 1390 or 8482) were sent to System Authorization Facility (SAF) interface unchanged. With the application of this PTF, the lower case characters will be folded to upper case before the ID and password are given to the SAF interface.). UK90993 HOLD DATA END UK90995 HOLD DATA START UK90995 HOLD DATA END UK91030 HOLD DATA START UK91030 HOLD DATA END UK91051 HOLD DATA START UK91051 HOLD DATA END UK91063 HOLD DATA START UK91063 HOLD DATA END UK91069 HOLD DATA START ++ HOLD(UK91069) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13020) COMMENT (***Action for PM78070: See PM78070 APAR/PTF text for additional information about why a REBIND is necessary. PM78070 corrects a problem of incorrect output that can happen for a query which contains UNION, EXCEPT, EXCEPT ALL or ORDER BY and whose top query block has exactly one table. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK91069 HOLD DATA END 1UK91089 HOLD DATA START UK91089 HOLD DATA END UK91121 HOLD DATA START UK91121 HOLD DATA END UK91122 HOLD DATA START UK91122 HOLD DATA END UK91142 HOLD DATA START UK91142 HOLD DATA END UK91146 HOLD DATA START UK91146 HOLD DATA END READY END