UI20718 HOLD DATA START ++ HOLD(UI20439) SYS FMID(HDBAA10) REASON(ACTION) DATE(14232) COMMENT (***Action for PI20069 (DB2 10) This PTF modifies the DB2 installation CLIST default input members, DSNTIDXA and DSNTIDXB. It corrects an error in the CLIST range checking of the settings for SIMULATED_CPU_COUNT and SIMULATED_CPU_SPEED. If you have already installed or migrated to DB2 10, you need to make the correction in your private DSNTIDxx members. Edit each such member, locate the entries for SIMULATED_CPU_COUNT and SIMULATED_CPU_SPEED, and change the fifth field to NONE. In other words, change this format: SIMULATED_CPU_COUNT CHARR G OFF 255 SIMULATED_CPU_SPEED CHARR G OFF 2147483647 to this: SIMULATED_CPU_COUNT CHARR G OFF NONE SIMULATED_CPU_SPEED CHARR G OFF NONE where is the current setting of SIMULATED_CPU_COUNT and is the current setting of SIMULATED_CPU_SPEED. Save your changes.). UI20718 HOLD DATA END UI20718 HOLD DATA START ++ HOLD(UI20718) SYS FMID(HDBAA10) REASON(ACTION) DATE(14232) COMMENT (***Action for PI07515 (DB2 10) This PTF adds two new options, DISABLE_ITTO_EXCPTN and IDNTFY_V10_PRIOR_VER, to the DSN6FAC.DDF_COMPATIBILITY subsystem parameter. - DISABLE_ITTO_EXCPTN: Disables Idle Thread Time-Out related serviceability information from being returned to remote client environments. - IDNTFY_V10_PRIOR_VER: Return information to ALL remote clients to reflect New Function support and product signature of prior version where DB2 was migrated from. DB2 essentially identifies itself as the prior version. Does not apply in NFM. Applies only in CMn / ENFM and CMn* / ENFM*. These options can be specified individually, together, or in combination with other DDF_COMPATIBILITY options. Examples: DDF_COMPATIBILITY= DDF_COMPATIBILITY=DISABLE_ITTO_EXCPTN DDF_COMPATIBILITY=IDNTFY_V10_PRIOR_VER DDF_COMPATIBILITY=(DISABLE_ITTO_EXCPTN,IDNTFY_V10_PRIOR_VER) DDF_COMPATIBILITY=(SP_PARMS_NJV,DISABLE_ITTO_EXCPTN) In DB2 data sharing, it is recommended to use the same DDF_COMPATIBILITY setting on all members of the group. The default for DDF_COMPATIBILITY is null (as shown in the first example above) meaning that DB2 10 DDF behavior is maintained with all clients. 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 the package for DSNTXAZP 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 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 want to use one or both of the new DDF_COMPATIBILITY options, DISABLE_ITTO_EXCPTN and IDNTFY_V10_PRIOR_VER. After applying this PTF, edit your customized copy of job DSNTIJUZ as follows: * Add or update the DDF_COMPATIBILITY keyword parameter in the invocation of the DSN6FAC macro and specify a setting of DISABLE_ITTO_EXCPTN, IDNTFY_V10_PRIOR_VER, or both. For example: DDF_COMPATIBILITY=DISABLE_ITTO_EXCPTN DDF_COMPATIBILITY=IDNTFY_V10_PRIOR_VER If additional options are needed, use the list format, for example: DDF_COMPATIBILITY=(DISABLE_ITTO_EXCPTN,IDNTFY_V10_PRIOR_VER) You can also use the list format to specify one or both of the new options in combination with one or more other DDF_COMPATIBILITY options. Make sure to add a continuation character in column 72 if needed. If you omit adding DDF_COMPATIBILITY here, the value will be set to the default of null 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 modifies the entry for DDF_COMPATIBILITY in the CLIST default input members in the SDSNSAMP target library You need to add or update this entry in all private copies of your CLIST output DSNTIDxx member. In each such copy, convert any existing DDF_COMPATIBILITY entry that has this format: DDF_COMPATIBILITY CHARR M NONE NONE or this format: DDF_COMPATIBILITY STR M or this format: DDF_COMPATIBILITY LSTR M DDF_COMPATIBILITY1 LSTR M to this format: DDF_COMPATIBILITY LLST M DDF_COMPATIBILITY1 LLST M DDF_COMPATIBILITY2 LLST M NULL DDF_COMPATIBILITY3 LLST M NULL DDF_COMPATIBILITY4 LLST M NULL where is NULL is the previous format was CHARR or STR. Change to the setting you specified in step (2), above. If necessary, you can continue the setting onto the DDF_COMPATIBILITYx entries by splitting it after a comma. For example: DDF_COMPATIBILITY LLST M SP_PARMS_NJV,DISABLE_IMPCAST_NJV, DDF_COMPATIBILITY1 LLST M DISABLE_IMPCAST_JV,IGNORE_TZ, DDF_COMPATIBILITY2 LLST M SP_PARMS_JV,DISABLE_ITTO_EXCPTN, DDF_COMPATIBILITY3 LLST M IDNTFY_V10_PRIOR_VER DDF_COMPATIBILITY4 LLST M NULL If you do not add entries for DDF_COMPATIBILITY to a DSNTIDxx member, the DB2 installation CLIST will assume 'DDF_COMPATIBILITY=,' when run with that member as input. --------------------------------------------------------------- (4) Update the package for DSNTXAZP --------------------------------------------------------------- ==> This action is required for all customers who use DSNTXAZP to update DB2 CLIST input (DSNTIDXX) members Submit a job that contains the following BIND statement: BIND PACKAGE(DSNTXAZP) MEMBER(DSNTXAZP) - ACTION(ADD) ENCODING(EBCDIC) - LIBRARY('prefix.SDSNDBRM') where is the high-level qualifier for the DB2 subsystem target libraries. ---------------------------------------------------------------). UI20718 HOLD DATA END UI21416 HOLD DATA START ++ HOLD(UI21416) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14260) COMMENT (***Action for PI20314: See PI20314 APAR/PTF text for additional information about why a REBIND is necessary. PI20314 adds BIF_COMPATIBILITY support for the RTRIM, LTRIM and STRIP built-in functions. 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_TRIM before rebinding. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UI21416 HOLD DATA END UI21416 HOLD DATA START ++ HOLD(UI21416) SYS FMID(HDBAA10) REASON(ACTION) DATE(14260) COMMENT (***Action for PI20314 (DB2 10) This PTF adds a new option called V9_TRIM to the DSN6SPRM BIF_COMPATIBILITY subsystem parameter. It can be used by DB2 10 customers who store mixed-byte EBCDIC data on DB2 to obtain toleration of certain errors in mixed-byte EBCDIC strings: - V9_TRIM: For decimal input, the CHAR, VARCHAR, CAST( ... AS CHAR) and CAST( ... as VARCHAR) built-in functions behave as they do when BIF_COMPATIBILITY is set to V9_DECIMAL_VARCHAR. In addition, the LTRIM, RTRIM, and STRIP built-in functions behave like DB2 V9 with respect to tolerating invalid EBCDIC mixed data strings and treatment of EBCDIC control characters X'0E0F'. For DB2 data sharing it is recommended but not required that all members use the same setting. The BIF_COMPATIBILITY parameter is externalized on installation panel DSNTIPX as BIF COMPATIBILITY. DSNTIPX has been updated to accept the new V9_TRIM 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) Copy updated DB2 installation panels to alternate libraries (2) Update your customized copy of job DSNTIJUZ (3) Update private copies of the DSNTIDxx CLIST input member (4) Modify and rebind any applications that use the LTRIM, RTRIM, or STRIP built-in functions. ---------------------------------------------------------------- (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 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. ---------------------------------------------------------------- (2) Update your customized copy of job DSNTIJUZ ---------------------------------------------------------------- ==> This action is required for all customers who want to use the new V9_TRIM option of DSN6SPRM.BIF_COMPATIBILITY: After applying this PTF, edit your customized copy of job DSNTIJUZ as follows: * Add or update the keyword parameter BIF_COMPATIBILITY=V9_TRIM 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. ---------------------------------------------------------------- (3) Update private copies of the DSNTIDxx CLIST input member ---------------------------------------------------------------- ==> This action is required for all customers who want to use the new V9_TRIM option of DSN6SPRM.BIF_COMPATIBILITY: After applying this PTF, edit all private copies of your your CLIST output DSNTIDxx member, locate the entry for BIF_COMPATIBILITY, and set the last field to V9_TRIM: BIF_COMPATIBILITY CHAR G NONE NONE V9_TRIM ---------------------------------------------------------------- (4) Application and object changes may be needed when applying this PTF. The actions depend on the value of the BIF_COMPATIBILITY system parameter. See PI20314 APAR/PTF text for additional details. ----------------------------------------------------------------). UI21416 HOLD DATA END UI21468 HOLD DATA START ++ HOLD(UI21468) SYS FMID(HDBAA10) REASON(ACTION) DATE(14265) COMMENT (After the application of the PTF for APAR PI12327, a single SYSIBM.SYSCOPY record with ICTYPE='L' and STYPE='M' will be inserted per commit scope for SQL mass DELETE, TRUNCATE TABLE, or DROP TABLE executed on a table in a segmented or universal table space (UTS). See the closing text for APAR PI12327 for more details.). UI21468 HOLD DATA END UI21494 HOLD DATA START ++ HOLD(UI21494) SYS FMID(HDBAA10) REASON(DOC) DATE(14262) COMMENT (The fix for APAR PI18635 changes RECOVER utility to allow the use of DSNUM n in the case where TOCOPY is used, the target object is a single piece of a nonpartitioned index (NPI), and the specified copy data set is a FlashCopy image copy. The DB2 Utilities Guide will be updated to reflect the changes made by this APAR and to clarify the conditions under which DSNUM n may or may not be specified for RECOVER utility. Specifically, the following footnote is added to the RECOVER utility syntax diagram for the DSNUM option that can be specified with TOCOPY: | DSNUM integer is not valid for nonpartitioned indexes | unless the data set that is specified for TOCOPY is a | FlashCopy image copy. To support the proper placement of this footnote, portions of the RECOVER utility syntax diagram are restructured. These documentation changes are listed for reference: >>-RECOVER-------------------------------------------------> >--+-+-LIST--listdef-name-----------+-list-options-spec-+--> | | .--------------------------. | | | | V .-DSNUM--ALL--------. | | | | '--obj.+---------------------+-' | | '-DSNUM--integer----' | | +-recover-options-spec-------------------------------+ '-object--PAGE--page-number--+----------+------------' '-CONTINUE-' .-LOGRANGES--YES----. >--+-------+--+--------------+--+-------------------+----->< '-CLONE-' +-LOCALSITE----+ | | '-RECOVERYSITE-' '-LOGRANGES--NO-----' ... recover-options-spec: | .-DSNUM--ALL-------------. | >>-object--+------------------------+----------------------> | | (1) | | '-DSNUM--integer---------' >--+-TOCOPY--data-set-- ... | +-TOLASTCOPY-- ... | +-TOLASTFULLCOPY-- ... | '-ERROR--RANGE-- ... Also under RECOVER utility, a portion of the explanation of the DSNUM keyword is modified as follows: Identifies a partition within a partitioned table space or a partitioned index, or identifies a data set within a nonpartitioned table space that is to be recovered. | You cannot specify a logical partition of a nonpartitioned | index. | You cannot specify a single data set of a nonpartitioned | index unless the data set that is specified for TOCOPY | is a FlashCopy image copy. However, to ensure consistency, | all data sets of a nonpartitioned index should be recovered | to the same point in time. Alternatively, the option can recover the entire table space or index space. ...). UI21494 HOLD DATA END UI21511 HOLD DATA START ++ HOLD(UI21511) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14265) COMMENT (***Action for PI22378: See PI22378 APAR/PTF text for additional information about why a REBIND is necessary. PI22378 corrects a problem ABEND04E RC00C90101 when the query contains residual join predicates. When the predicate is applied, the columns from inner table are the index keys of a 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.). UI21511 HOLD DATA END UI21565 HOLD DATA START ++ HOLD(UK64497) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14265) COMMENT (DB2 will not allow catalog index names to be different from their index space names. Applying this APAR/PTF will not change any index spaces names created incorrectly if customer already have migrated to DB2 V10. Please run the following query to identify which catalog index spaces names do not match their catalog index name SELECT NAME, CREATOR, TBNAME, INDEXSPACE, DBID, OBID FROM SYSIBM.SYSINDEXES WHERE NAME ¬= INDEXSPACE AND DBID = 6 AND (OBID NOT BETWEEN 400 AND 599 AND OBID NOT BETWEEN 900 AND 1699) If the above query returns rows, you should contact IBM support for further assistance.). UI21565 HOLD DATA END UI21712 HOLD DATA START ++ HOLD(UK96188) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14268) COMMENT (***Action for PM90902: See PM90902 APAR/PTF text for additional information about why a REBIND is necessary. PM90902 corrects a problem of a SQLCODE -180 is issued when a query contains a scalar function in the selection list which is also be referenced in ORDER BY clause running on 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.). UI21712 HOLD DATA END UI21712 HOLD DATA START ++ HOLD(UK92645) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14268) COMMENT (***Action for PM81200: See PM81200 APAR/PTF text for additional information about why a REBIND is necessary. PM81200 corrects a problem of ABEND04E RC00C90101 at DSNIBNRF:5007 when a query's selection list contains a scalar full-select item participated in arithmetic calculation 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.). UI21712 HOLD DATA END UI21712 HOLD DATA START ++ HOLD(UI21712) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14268) COMMENT (***Action for PI23177: See PI23177 APAR/PTF text for additional information about why a REBIND is necessary. PI23177 corrects a problem an ABEND04E RC00E70005 at DSNXORLI P001 may occur when a query contains a LOB column and its defined length is greater than 32767 ('7FFF'x) 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.). UI21712 HOLD DATA END UI21770 HOLD DATA START ++ HOLD(UI21122) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14270) COMMENT (***Action for PI22793: See PI22793 APAR/PTF text for additional information about why a REBIND is necessary. PI22793 corrects a problem that incorrect output could happen for an SQL statement containing a function VARCHAR_FORMAT on a FOR BIT DATA 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.). UI21770 HOLD DATA END UI21904 HOLD DATA START ++ HOLD(UI21904) SYS FMID(HDBAA10) REASON(DOC) DATE(14274) COMMENT (IFCID 22 is updated to include a new flag QW0022ER to indicate if existing field QW0022PN (plan name or package name) is displayed in Unicode instead of EBCDIC. If a CCSID translation error occurs for QW0022PN, QW0022ER will be turned on to indicate QW0022PN is in Unicode. The new field is added under flags QW0022FG: . . . QW0022FG DS XL1 FLAGS QW0022HT EQU X'80' 1 IF OPTIMIZATION HINTS WERE USED QW0022ER EQU X'40' 1 IF CONVERSION ERROR ON QW0022PN AND QW0022PN IS IN UNICODE . . .). UI21904 HOLD DATA END UI22011 HOLD DATA START ++ HOLD(UI12294) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14279) COMMENT (***Action for PM90617: See PM90617 APAR/PTF text for additional information. Post-apply actions after applying the fix for APAR PM90617: (a)STOP PROC(SYSPROC.DSNACCOX) to suspend DSNACCOX activity (b)Rebind package for DSNACCOX. An example on how to do this is BIND PACKAGE(DSNACCOX) MEMBER(DSNACCOX) - ACTION(ADD) ISOLATION(UR) ENCODING(EBCDIC) - LIBRARY('DSNA10.SDSNDBRM') (c)START PROC(SYSPROC.DSNACCOX) to resume DSNACCOX activity). UI22011 HOLD DATA END UI22011 HOLD DATA START ++ HOLD(UK97958) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14279) COMMENT ( ***Action for PM88775: See PM88775 APAR/PTF text for additional information. Post-apply actions after applying the fix for APAR PM88775: (a) STOP PROC(SYSPROC.DSNACCOX) to suspend DSNACCOX activity (b) Rebind package for DSNACCOX. An example on how to do this is BIND PACKAGE(DSNACCOX) MEMBER(DSNACCOX) - ACTION(ADD) ISOLATION(UR) ENCODING(EBCDIC) - LIBRARY('DSNA10.SDSNDBRM') (c) START PROC(SYSPROC.DSNACCOX) to resume DSNACCOX activity). UI22011 HOLD DATA END UI22011 HOLD DATA START ++ HOLD(UI15181) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14279) COMMENT (***Action for PI06664: See PI06664 APAR/PTF text for additional information. Post-apply actions after applying the fix for APAR PI06664: (a)STOP PROC(SYSPROC.DSNACCOX) to suspend DSNACCOX activity (b)Rebind package for DSNACCOX. An example on how to do this is BIND PACKAGE(DSNACCOX) MEMBER(DSNACCOX) - ACTION(ADD) ISOLATION(UR) ENCODING(EBCDIC) - LIBRARY('DSNA10.SDSNDBRM') (c)START PROC(SYSPROC.DSNACCOX) to resume DSNACCOX activity). UI22011 HOLD DATA END UI22011 HOLD DATA START ++ HOLD(UK91351) SYS FMID(HDBAA10) REASON(DOC) DATE(14279) COMMENT (PM74794 introduces the following changes to the DB2 10 for z/OS Utility Guide and Reference - Appendix B. DB2-supplied stored procedures for utility operations Section - DSNACCOX stored procedure Chapter - DSNACCOX output Table 145 - Result set row for second DSNACCOX result set | NAME VARCHAR(128) Table space name, index name, or | index space name. Index space | name is used if the row is added | as a result of checking a | restricted state and the index | name is not available at the time. Information about this changed information 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.). UI22011 HOLD DATA END UI22011 HOLD DATA START ++ HOLD(UI12294) SYS FMID(HDBAA10) REASON(ACTION) DATE(14279) COMMENT (***Action for PM90617: See PM90617 APAR/PTF text for additional information. After applying the fix for PM90617, DSNACCOX will reverse the recommendation based on percentage criterion on an object with TOTALROWS or TOTALENTRIES of zero. If TOTALROWS or TOTALENTRIES is zero, we now rely on the related absolute criteria.). UI22011 HOLD DATA END UI22011 HOLD DATA START ++ HOLD(UK91351) SYS FMID(HDBAA10) REASON(ACTION) DATE(14279) COMMENT ( ***Action for PM74794: See PM74794 APAR/PTF text for additional information. Post-apply actions after applying the fix for APAR PM74794: (a) STOP PROC(SYSPROC.DSNACCOX) to suspend DSNACCOX activity (b) Rebind package for DSNACCOX. An example on how to do this is BIND PACKAGE(DSNACCOX) MEMBER(DSNACCOX) - ACTION(REPLACE) ISOLATION(UR) ENCODING(EBCDIC) - LIBRARY('DSN!!0.SDSNDBRM') Change 'DSN!!0' to the prefix of your target library name. (c) START PROC(SYSPROC.DSNACCOX) to resume DSNACCOX activity). UI22011 HOLD DATA END UI22011 HOLD DATA START ++ HOLD(UK79842) SYS FMID(HDBAA10) REASON(ACTION) DATE(14279) COMMENT (After you apply the PTF for APAR PM61574, Non Partitioned Index is no longer recommended for reorg or runstats when chklevel 32 is specified and any of the ts partitioned is recommended for reorg or runstats.). UI22011 HOLD DATA END UI22011 HOLD DATA START ++ HOLD(UI22011) SYS FMID(HDBAA10) REASON(ACTION) DATE(14279) COMMENT ( ***Action for PI22121: See PI22121 APAR/PTF text for additional information. The package for DSNACCOX SQL statement support needs to be bound after applying the fix for APAR PI22121. An example of how to do this is: BIND PACKAGE(DSNACCOX) MEMBER(DSNACCOX) - ACTION(ADD) ISOLATION(UR) ENCODING(EBCDIC) - LIBRARY('DSN!!0.SDSNDBRM') Change 'DSN!!0' to the prefix of your target library name.). UI22011 HOLD DATA END UI22011 HOLD DATA START ++ HOLD(UI18727) SYS FMID(HDBAA10) REASON(ACTION) DATE(14279) COMMENT (The package for DSNACCOX needs to be bound after applying the fix for APAR PI15366. An example of how to do this is: BIND PACKAGE(DSNACCOX) MEMBER(DSNACCOX) - ACTION(ADD) ISOLATION(UR) ENCODING(EBCDIC) - LIBRARY('DSN!!0.SDSNDBRM') Change 'DSN!!0' to the prefix of your target library name.). UI22011 HOLD DATA END UI22011 HOLD DATA START ++ HOLD(UI15181) SYS FMID(HDBAA10) REASON(ACTION) DATE(14279) COMMENT (PLEASE NOTE: After applying the fix for APAR PI06664, DSNACCOX will reverse the recommendation based on percentage criterion on the object with TOTALROWS or TOTALENTRIES of zero. If the TOTALROWS or TOTALENTRIES is zero, we now rely on the related absolute criteria.). UI22011 HOLD DATA END UI22067 HOLD DATA START ++ HOLD(UI22067) SYS FMID(HDBAA10) REASON(DOC) DATE(14281) COMMENT (DB2 will issue a new message DSNY028I and terminate with an ABEND04E RC00E80163 if the DB2 subsystem is being started with an incompatible level of DB2 ERLY code. . DSNY028I csect-name DB2 version ERLY CODE IS NOT COMPATIBLE WITH THIS RELEASE OF DB2 . Explanation A DB2 subsystem could not start because the DB2 ERLY code was not at the correct level for the version of that DB2 subsystem. csect-name The name of the control section that issued the message. version The version of the ERLY code. . System action DB2 startup abnormally terminates with reason code 00E800163. . System programmer response To solve the problem, follow these steps: 1. Verify that you are specifying the correct prefix.SDSNLINK library in your link list. If not, correct the library name. 2. Install a version of the DB2 ERLY code that is compatible with the DB2 version that you are starting. See the corresponding DB2 Installation manual for compatibility of ERLY code levels and DB2 versions. 3. Activate the ERLY code by performing a z/OS IPL or issuing the command -REFRESH DB2,EARLY. . 00E80163 . Explanation A DB2 subsystem could not start because the DB2 ERLY code was not at the correct level for the version of that DB2 subsystem. . System action DB2 startup abnormally terminates. . System programmer response To solve the problem, follow these steps: 1. Verify that you are specifying the correct prefix.SDSNLINK library in your link list. If not, correct the library name. 2. Install a version of the DB2 ERLY code that is compatible with the DB2 version that you are starting. See the corresponding DB2 Installation manual for compatibility of ERLY code levels and DB2 versions. 3. Activate the ERLY code by performing a z/OS IPL or issuing the command -REFRESH DB2,EARLY. DB2 will issue a new message DSNY028I and terminate with an ABEND04E RC00E80163 if the DB2 subsystem is being started with an incompatible level of DB2 ERLY code. . DSNY028I csect-name DB2 version ERLY CODE IS NOT COMPATIBLE WITH THIS RELEASE OF DB2 . Explanation A DB2 subsystem could not start because the DB2 ERLY code was not at the correct level for the version of that DB2 subsystem. csect-name The name of the control section that issued the message. version The version of the ERLY code. . System action DB2 startup abnormally terminates with reason code 00E800163. . System programmer response To solve the problem, follow these steps: 1. Verify that you re specifying the correct prefix.SDSNLINK library in your link list. If not, correct the library name. 2. Install a version of the DB2 ERLY code that is compatible with the DB2 version that you are starting. See the corresponding DB2 Installation manual for compatibility of ERLY code levels and DB2 versions. 3. Activate the ERLY code by performing a z/OS IPL or issuing the command -REFRESH DB2,EARLY. . 00E80163 . Explanation A DB2 subsystem could not start because the DB2 ERLY code was not at the correct level for the version of that DB2 subsystem. . System action DB2 startup abnormally terminates. . System programmer response To solve the problem, follow these steps: 1. Verify that you re specifying the correct prefix.SDSNLINK library in your link list. If not, correct the library name. 2. Install a version of the DB2 ERLY code that is compatible with the DB2 version that you are starting. See the corresponding DB2 Installation manual for compatibility of ERLY code levels and DB2 versions. 3. Activate the ERLY code by performing a z/OS IPL or issuing the command -REFRESH DB2,EARLY.). UI22067 HOLD DATA END UI22080 HOLD DATA START ++ HOLD(UI19545) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14282) COMMENT (***Action for PM94681 (DB2 10) 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. These instructions supersede the instructions for any previous PTF on ADMIN_INFO_SQL. 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 bind DBRM DSNADMSS --------------------------------------------------------------- ==> This action is required for all V10 customers. This PTF updates programs module DSNADMSS plus the DBRM for DSNADMSS.After applying this PTF, proceed as follows to bind a package from the updated DSNADMSS DBRM. 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.). UI22080 HOLD DATA END UI22080 HOLD DATA START ++ HOLD(UI22080) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14282) COMMENT (***Action for PI25905 (DB2 10 for z/OS) This PTF modifies the DB2-supplied stored procedure SYSPROC.ADMIN_INFO_SQL and batch program DSNADMSB which are used to collect DB2 environmental service data. *************************************************************** Actions for customers who are already using DB2 10: *************************************************************** If you have already installed or migrated to V10, run your copy of the installation job DSNTIJRT with MODE(INSTALL) to bind the DSNADMSS 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.). UI22080 HOLD DATA END UI22080 HOLD DATA START ++ HOLD(UI20314) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14282) COMMENT (***Action for PI21058 (DB2 10 for z/OS) 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. 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 bind DBRM DSNADMSS --------------------------------------------------------------- ==> This action is required for all V10 customers. This PTF updates program module DSNADMSS and the DBRM for DSNADMSS. After applying this PTF, proceed as follows to bind the updated DSNADMSS DBRM. Run your copy of job DSNTIJRT with MODE(INSTALL) to bind the DSNADMSS 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.). UI22080 HOLD DATA END UI22080 HOLD DATA START ++ HOLD(UI15281) SYS FMID(HDBAA10) REASON(ACTION) DATE(14282) COMMENT (***Action for PI07646 (DB2 10) 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. These instructions supersede any previous PTF instructions. If you have already installed or migrated to DB2 V10, you need to take the following action after applying this PTF: --------------------------------------------------------------- Run installation job DSNTIJRT to bind DBRM DSNADMSS --------------------------------------------------------------- ==> This action is required for all V10 customers. This PTF updates programs module DSNADMSS plus the DBRM for DSNADMSS. After applying this PTF, proceed as follows to bind a package from the updated DSNADMSS DBRM. 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, re-running 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 re-run DSNTIJRT without the PREVIEW option or customize and run the generated job.). UI22080 HOLD DATA END UI22144 HOLD DATA START ++ HOLD(UI22144) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14285) COMMENT (***Action for PI23252: See PI23252 APAR/PTF text for additional information about why a REBIND is necessary. PI23252 corrects following problems: ABEND04E RC00E72048 at location DSNXGRDS DSNXSORI OFFSET5718 (for V10) or OFFSET6996 (for V11) could happen when executing an SQL statement that satisfies all of the following conditions. ABEND0C4 RC00000038 at location DSNXGRDS DSNXOD8 OFFSET1F3A (for V10) or OFFSET1F5A (for V11) could happen when doing EXPLAIN for an SQL statement that satisfies all of the following conditions. 1. It contains a CASE expression in the SELECT list. 2. The CASE expression contains an EXISTS subquery in the WHEN clause. 3. The EXISTS subquery contains a table expression with SELECT 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.). UI22144 HOLD DATA END UI22195 HOLD DATA START ++ HOLD(UI19129) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14287) COMMENT (***Action for PI19010: See PI19010 APAR/PTF text for additional information about why a REBIND is necessary. PI19010 corrects a problem of ABEND0C4 RC00E70005 at location DSNXRRP OFFSETM110 can occur for an SQL statement that contains an after join or during join predicate which contains a timestamp column and a string host variable. 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.). UI22195 HOLD DATA END UI22195 HOLD DATA START ++ HOLD(UI16685) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14287) COMMENT (***Action for PI11756: See PI11756 APAR/PTF text for additional information about why a REBIND is necessary. PI11756 corrects a problem of 'less rows than expected' were inserted for an offloaded IBM DB2 Analytics Accelerator 'INSERT from SELECT' stetement. 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.). UI22195 HOLD DATA END UI22195 HOLD DATA START ++ HOLD(UI22195) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14287) COMMENT (***Action for PI08409: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PI08409 corrects a problem in which an inefficient access path may be selected by DB2 when a range list access path (ACCESSTYPE='NR') would be more efficient. To make this fix effective for a static application, zparm PGRNGSCR must be set to YES and the application rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UI22195 HOLD DATA END UI22195 HOLD DATA START ++ HOLD(UI22195) SYS FMID(HDBAA10) REASON(MULTSYS) DATE(14287) COMMENT (PI08409 is an enabling APAR. In a data sharing group, pre-conditioning APAR PI16179 must be applied to all members before the enabling APAR (PI08409) is applied to any member. The code change is not enabled until the enabling APAR PI08409 is applied. PI08409 corrects a problem of performance regression for static SQL queries when an inefficient access path is selected and a more efficient range list access path exists. The problem can occur when a predicate eligible for index matching or index screening in a range list leg references a timestamp column.). UI22195 HOLD DATA END UI22195 HOLD DATA START ++ HOLD(UI22195) SYS FMID(HDBAA10) REASON(ACTION) DATE(14287) COMMENT (***Action for PI08409: ZPARM PGRNGSCR must be set to YES to enable the fix in this APAR. PI08409 corrects a problem in which an inefficient access path may be selected by DB2 when a range list access path (ACCESSTYPE='NR') would be more efficient. To make this fix effective for a static application, zparm PGRNGSCR must be set to YES and the application rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UI22195 HOLD DATA END UI22273 HOLD DATA START ++ HOLD(UI22273) SYS FMID(HDBAA10) REASON(ACTION) DATE(14290) COMMENT (See PI24860 APAR/PTF text for additional information. PI24860 has modified the REORG utility to ignore the KEEPDICTIONARY option when an ALTER TABLESPACE BUFFERPOOL change is pending and instead, REORG will build a new dictionary. This change will be documented in the Utility Guide and Reference manual, under the REORG utility, the KEEPDICTIONARY option: Possible reasons for not specifying KEEPDICTIONARY are: | If REORG is materializing a pending alter of the buffer | pool, REORG builds a new dictionary. DB2 ignores the | KEEPDICTIONARY option if REORG is materializing a | buffer pool change.). UI22273 HOLD DATA END UI22470 HOLD DATA START ++ HOLD(UK63421) SYS FMID(HDBAA10) REASON(ACTION) DATE(14297) COMMENT (After applying this PTF on DB2 10 for z/OS ========================================== The following post-apply actions are required of all current V10 customers that use the DB2 scheduler. To activate the changes in this PTF you need to take the following additional actions: (1) Stop the scheduler for administrative tasks. (2) Start the scheduler for administrative tasks. Each of these actions is detailed below. (1) Stop the scheduler for administrative tasks. If the 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) Start the scheduler for administrative tasks. Start the scheduler by executing the following console command: START DSNADMT where DSNADMT is the name of the scheduler.). UI22470 HOLD DATA END UI22470 HOLD DATA START ++ HOLD(UI22470) SYS FMID(HDBAA10) REASON(ACTION) DATE(14297) COMMENT (***Action for PI25728 (DB2 10 for z/OS) This PTF modifies the DB2 scheduler for administrative tasks so that when a triggering task is updated while it is not executing, the scheduler will not automatically schedule its triggered task for immediate execution. Post-apply actions for this PTF: -------------------------------- If you have already installed or migrated to DB2 10 for z/OS then after applying this PTF you need to take the following actions after applying this PTF: (1) Stop the DB2 scheduler for administrative tasks. (2) Start the DB2 scheduler for administrative tasks. These actions are detailed below. ---------------------------------------------------------------- (1) Stop the DB2 scheduler for administrative tasks. *********************************************************** * This action is required for all DB2 10 customers who use * the DB2 scheduler for administrative tasks, i.e., the * DSN6SPRM.ADMTPROC subsystem parameter setting is not * null (in which case it specifies the name of the * scheduler). *********************************************************** 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) Start the DB2 scheduler for administrative tasks. *********************************************************** * This action is required for all DB2 10 customers who use * the DB2 scheduler for administrative tasks, i.e., the * DSN6SPRM.ADMTPROC subsystem parameter setting is not * null (in which case it specifies the name of the * scheduler). *********************************************************** To start the DB2 scheduler again, execute the following console command: START DSNADMT where DSNADMT is the name of the scheduler.). UI22470 HOLD DATA END UI22482 HOLD DATA START ++ HOLD(UI21104) SYS FMID(HDBAA10) REASON(ACTION) DATE(14297) COMMENT (***Action for PI19131 (DB2 10) This APAR modifies DSNTRIN, the installation and configuration tool for DB2-supplied stored procedures and user-defined functions (UDFs), to correct invalid syntax in the generated create statement for the SYSTOOLS.REGSP stored procedure. It also modifies DSNTRIN to detect and alter existing instances of SYSTOOLS.REGSP that are defined with the error. If you have already installed or migrated to DB2 10, you need to run your copy of job DSNTIJRT with MODE(INSTALL) to check for and correct problems with the SYSTOOLS.REGSP stored procedure. 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.). UI22482 HOLD DATA END UI22482 HOLD DATA START ++ HOLD(UI22482) SYS FMID(HDBAA10) REASON(ACTION) DATE(14297) COMMENT (This PTF modifies Database Metadata(DBMD) stored procedure SYSIBM.SQLTables, as follows: * Support new keyword EXTENDEDTABLEINFO = 0|1. Default value is 0. * Report 'ACCEL-ONLY TABLE' in TABLE_TYPE . * 3 new columns are appended at the end of current result set. (1) TEMPORAL_TABLE_TYPE VARCHAR(11) Valid values: 'SYSTEM'|'APPLICATION'| 'BITEMPORAL'|'' (2) IS_ACCELERATED VARCHAR(3) Valid values: 'YES'|'NO' (3) ACCEL_ARCHIVE_STATUS CHAR(1) Valid values: 'A'|'B'|'C'|X'|'' It also modifies SYSIBM.SQLTABLETYPES, a table used by stored procedure SYSIBM.SQLTables, as follows: * A new column, IS_EXTENDEDTYPE, is added. It is defined as SMALLINT NOT NULL with DEFAULT 0. * A new row, for TABLE_TYPE='ACCEL-ONLY TABLE' is added. *************************************************************** Actions for customers who are already using DB2 10: *************************************************************** If you have already installed or migrated to DB2 10 then, you need to take the following actions after applying this PTF: Procedures for these actions are given below. ---------------------------------------------------------------- (1) Run installation job DSNTIJRT to alter SYSIBM.SQLTABLETYPES and add the new column, insert the new row, then bind DBRM DSNATBLU ==> This action is required for all V10 customers Run your copy of job DSNTIJRT with MODE(INSTALL) to alter SYSIBM.SQLTABLETYPES and add the new column, insert the new row, and to bind the DBRM DSNATBLU and refresh the package for DBMD stored procedure SYSIBM.SQLTABLES. Note: The bind request for DSNATBLU will return warnings if the SYSACCEL.SYSACCELERATEDTABLES does not exist on DB2. This is expected if the DB2 does not use an accelerator product. 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) REORG the DSNATPDB.DSNATPTS table space ==> This action is required for all V10 customers Alteration of SYSIBM.SQLTABLETYPES by DSNTIJRT to add the new column places the containing table space, DSNATPDB.DSNATPTS, in Advisory REORG Pending (AREO*) status. The table space is available but needs to be reorganized for optimal performance. Therefore, run the REORG TABLESPACE utility with SHRLEVEL CHANGE or SHRLEVEL REFERENCE on the entire table space. (3) 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 procedure SYSIBM.SQLTables is running.). UI22482 HOLD DATA END UI22529 HOLD DATA START ++ HOLD(UI22529) SYS FMID(HDBAA10) REASON(ACTION) DATE(14300) COMMENT (This PTF updates DSNMQHLP, the DBRM for for the DB2 MQI user-defined functions. After applying the PTF, all customers should run installation job DSNTIJRT with MODE(INSTALL) to bind a new version of package DSNMQUDF.DSNMQHLP. 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.). UI22529 HOLD DATA END UI22578 HOLD DATA START ++ HOLD(UK97603) SYS FMID(HDBAA10) REASON(MULTSYS) DATE(14301) COMMENT (***Action for PM91547: PM91547 is an APAR that includes changes to support a functional code change that will be enabled once this APAR is applied. Earlier, a pre-conditioning APAR PM69760 was delivered. In a data sharing group, this 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 following APAR enables the fix for the problem of timeout by SCA REBUILD when a concurrent mainline DBET updater happens to be in the notify exit path.). UI22578 HOLD DATA END UI22578 HOLD DATA START ++ HOLD(UK90830) SYS FMID(HDBAA10) REASON(MULTSYS) DATE(14301) COMMENT (***Action for PM74803: PM74803 is an APAR that includes changes to support a functional code change that will be enabled once this APAR is applied. Earlier, a pre-conditioning APAR PM69760 was delivered. In a data sharing group, this 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 following APAR enables the fix for the problem of timeout by SCA REBUILD when a concurrent mainline DBET updater happens to be in the notify exit path.). UI22578 HOLD DATA END UI22583 HOLD DATA START ++ HOLD(UK64872) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14301) COMMENT (See PM27871 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. APAR PM27871 corrects problems that can cause an incorrect output for sensitive static scroll cursor when order by DESC on a column that is not 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.). UI22583 HOLD DATA END UI22583 HOLD DATA START ++ HOLD(UI22583) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14301) COMMENT (See PI24528 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. Apar PI24528 corrects the conditions that can lead to the occurrence of an ABEND04E RC00E70005 at location DSNXOUWF:P010 when executing a query scrollable cursor and inline lob. 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.). UI22583 HOLD DATA END UI22693 HOLD DATA START ++ HOLD(UK70649) SYS FMID(HDBAA10) REASON(ACTION) DATE(14304) 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.). UI22693 HOLD DATA END UI22766 HOLD DATA START ++ HOLD(UI22766) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14308) COMMENT (***Action for PI27501: See PI27501 APAR/PTF text for additional information about why a REBIND is necessary. PI27501 corrects a problem for SQL query acceleration where an SQL query is incorrectly blocked from routing to an accelerator. To make this fix effective for a static application containing accelerated static SQL queries, the application 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.). UI22766 HOLD DATA END UI22816 HOLD DATA START ++ HOLD(UI22816) SYS FMID(HDBAA10) REASON(DOC) DATE(14309) COMMENT (***Documentation for PI21021: See PI21021 APAR/PTF text for additional information. The following text contains the documentation of the function provided by this APAR. The product documentation contained in the Information Management Software for z/OS Solutions Information Center will be updated at a later date. System Profile Monitoring is being changed in the following ways: * a new ATTRIBUTE1 column value, "WARNING_MESSAGE_FOR_IDLE_TIMEOUT", for a row in the DSN_PROFILE_ATTRIBUTES table is being supported when the KEYWORD column has the value "MONITOR IDLE THREADS" * a new message, DSNT773I, will be issued when a thread has exceeded the new WARNING_MESSAGE_FOR_IDLE_TIMEOUT specified threshold. * the base accounting trace record, as defined by DSNDQWAC, is being enhanced to include the PROFILEID of the system profile montoring table row (DSN_PROFILE_TABLE) which the thread was identified to exceed, whether the threshold was a warning or an exception. New ATTRIBUTE1 Column Value for DSN_PROFILE_ATTRIBUTES Table ------------------------------------------------------------ In the documentation for "Managing DB2 Threads", there is an existing subsection named "Monitoring idle threads by using profiles". In step 2c of that section, the ATTRIBUTE1 column can have values WARNING or EXCEPTION followed by either _DIAGLEVEL1 or _DIAGLEVEL2. A new entry is being added to the second part of the value as follows: type_MESSAGE_FOR_IDLE_TIMEOUT Where type can only be WARNING, DB2 will issue message, DSNT773I, to display the information about the thread which exceeded the warning threshold. DB2 will also issue a console message, DSNT771I, which contains minimal information. DB2 will only issue the DSNT773I message once for the thread if it remains in an idle state. Once a client request message is received, and a COMMIT or ROLLBACK is performed with no resources active past the end of the unit-of-work, DB2 will remove the warning against the thread. In the Table 1, "Summary of DSN_PROFILE_ATTRIBUTES values for monitoring idle threads", a new value under the column with heading, "ATTRIBUTE1 column", is being added as follows: * WARNING_MESSAGE_FOR_IDLE_TIMEOUT New Message DSNT773I -------------------- DSNT773I csect-name SERVER DISTRIBUTED AGENT WITH LUWID=luw-id=token THREAD-INFO=thread-information FOR LOCATION=location RECEIVED IDLE THREAD TIMEOUT WARNING DUE TO PROFILE ID=profile-id Explanation The information about the server thread which exceeded a "MONITOR IDLE THREADS" monitor profile WARNING condition is being displayed. csect-name The name of the control section that issued the message. luw-id=token The logical unit of work identifier (LUWID) and a six-digit token value. The token value can be used in any DB2 command that accepts a LUWID as input. thread-information Information about the thread. The information is presented in a colon-delimited list that contains the following segments: * The primary authorization ID that is associated with the thread. In many distributed configurations, the primary authorization ID that is used with DB2 is not necessarily the user's ID. * The name of the user's workstation. * The ID of the user. * The name of the application. * The statement type for the previously executed statement: dynamic or static. * The statement identifier for the previously executed statement, if available. The statement identifier can be used to identify the particular SQL statement. For static statements, the statement identifier correlates to the STMT_ID column in the SYSIBM.SYSPACKSTMT table. For dynamic statements, the statement identifier correlates to the STMT_ID column in the DSN_STATEMENT_CACHE_TABLE table. That statement might not have contributed to the current failure. * The name of the role that is associated with the thread. * The correlation token that can be used to correlate work at the remote system with work performed at the DB2 subsystem. The correlation token, if available, is enclosed in '<' and '>' characters, and contains three components, separated by periods: - A 3 to 39 character IP address. - A 1 to 8 character port address. - A 12 character unique identifier. An asterisk (*) in any segment indicates that the information is not available. location This field contains the requester's IP address profile-id The profile ID of the monitor profile (defined in DSN_PROFILE_TABLE) that detected the condition. This message might be followed by instances of message DSNT771I indicating that WARNING MONITOR PROFILE conditions have occurred. Operator response Notify the system programmer. System programmer response The message is being issued to provide information about the thread which exceeded the WARNING condition. The thread and connection remain active awaiting a subsequent requester action. If the WARNING condition being exceeded points to an application error, the information provided can be used to identify the errant application. Tracing "Monitor Idle Threads" Warning/Exception Conditions ----------------------------------------------------------- A new paragraph is being added to our documentation in the subsection named "Monitoring idle threads by using profiles" of "Managing DB2 Threads", as follows: "To understand which connection/thread exceeded a warning or exception monitor profile, the accounting trace record of the thread occurrence which experienced the condition will be updated as follows: * QWAC_PROFMON_TYPE - will be set to an 'E' for an exception or a 'W' for a warning condition detected * QWAC_PROFMON_PID - will be set to the 4-byte binary integer value of the profile ID of the monitor profile (defined in DSN_PROFILE_TABLE) that detected the condition * if QWAC_PROFMON_TYPE is neither set to an 'E' nor a 'W', then no monitor profile condition was detected for this thread/connection accounting occurrence Refer to the DSNWMSGS member of SDSNIVPD library or the DSNDQWAC member of SDSNMACS library for a complete description of the basic accounting trace fields. Since a monitor profile exception condition will result in thread level abend messages (DSNL027I/DSNL028I) being displayed, a user can also create a warning monitor profile which will result in a thread level message DSNT773I being displayed when the warning threshold is exceeded. Refer to the description of the ATTRIBUTE1 column value, "WARNING_MESSAGE_FOR_IDLE_TIMEOUT". Changes to IFCID 003 Record as described by DSNDQWAC ---------------------------------------------------- The accounting trace record (IFCID 003) as described by DSNDQWAC is being changed as follows: The current V10 DSNDWQAC contains the following in assembler: ... QWACRU EQU * DS CL8 * Reserved * DS F * Reserved * QWAC_PT_COUNT DS F * 1. For all non-rollup records = 0 * * * 2. For a parallel query rollup record * * * = number of parallel child agents * * * rolled into this record * * * 3. For a DDF/RRSAF rollup record = * * * the number of parallel query child * * * agents rolled into this record. * * * These agents are NOT counted in * * * QWACPCNT. * QWACFLGS2 DS H QWAC_RU_PARQRY EQU X'8000' * This record contains parallel * * * query rollup data. * DS CL6 QWAC_PQS EQU * DS CL12 * Reserved * QWAC_ACCEL EQU * ... DSNDQWAC in assembler is being changed as follows: ... QWACRU EQU * DS CL8 * Reserved * DS F * Reserved * QWAC_PT_COUNT DS F * 1. For all non-rollup records = 0 * * * 2. For a parallel query rollup record * * * = number of parallel child agents * * * rolled into this record * * * 3. For a DDF/RRSAF rollup record = * * * the number of parallel query child * * * agents rolled into this record. * * * These agents are NOT counted in * * * QWACPCNT. * QWACFLGS2 DS H QWAC_RU_PARQRY EQU X'8000' * This record contains parallel * * * query rollup data. * QWAC_PROFMON EQU * * Thread profile monitoring info * QWAC_PROFMON_TYPE DS CL1 * Profile monitoring type * * * (E=excp, W=warn) * DS CL1 QWAC_PROFMON_PID DS F * Profile table row id value * QWAC_PQS EQU * DS CL12 * Reserved * QWAC_ACCEL EQU * ...). UI22816 HOLD DATA END UI22920 HOLD DATA START ++ HOLD(UI22920) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14314) COMMENT (***Action for PI22697: See PI22697 APAR/PTF text for additional information about why a REBIND is necessary. PI22697 corrects a problem for SQL query acceleration when a virtual accelerator exists or when an accelerated table is archived on the accelerator. To make this fix effective for a static application containing accelerated static SQL queries, the application 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.). UI22920 HOLD DATA END UI22954 HOLD DATA START ++ HOLD(UI22954) SYS FMID(JDBAA17) REASON(ACTION) DATE(14316) COMMENT (*** Action for PI22357 (Version 10) This PTF includes the following updated DBRM for the ODBC executable modules DSNAOCLI, DSNAOCLX, and DSNAO64C: DSNCLIQR After applying the PTF, all customers who use these modules need to bind the package of the DBRM DSNCLIQR. For example: BIND PACKAGE (.DSNAOCLI) MEMBER(DSNCLIQR) ACTION(ADD) ENCODING(EBCDIC) where is the location of the DBRM 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.). UI22954 HOLD DATA END UI22984 HOLD DATA START ++ HOLD(UK75442) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14316) COMMENT (***Action for PM53253: See PM53253 APAR/PTF text for additional information. PM53253 corrects a problem of SQLCODE -805 encountered when calling a Native SQL Procedure at a site that does not have maintenance UK60802 (V9) or UK65480 (V10) applied, after the procedure was deployed in a BIND PACKAGE DEPLOY command from another site that does have maintenance UK60802 (V9) or UK65480 (V10) applied. To make this fix effective, apply this fix to all target sites specified by BIND PACKAGE DEPLOY commands. You can issue an sql ALTER PROCEDURE REGENERATE statement for the procedure that fails with SQLCODE805 to allow subsequent calls to be successful. The order of maintenance across distributed subsystems determines whether or not you will receive the SQLCODE805 error. To circumvent the error, first apply this fix (or UK60802 or UK65480) to all target sites specified by BIND PACKAGE DEPLOY commands, and then apply this fix (or UK60802 or UK65480) to the requesting sites. The order of maintenance and the issuance of the BIND PACKAGE DEPLOY command also determines whether or not you will receive the SQLCODE805 error. If this fix (or UK60802 or UK65480) is applied to all systems before any BIND PACKAGE DEPLOY commands are issued, then the subject error will not occur.). UI22984 HOLD DATA END UI22987 HOLD DATA START ++ HOLD(UI22987) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14316) COMMENT (***Action for PI25244: See PI25244 APAR/PTF text for additional information about why a REBIND is necessary. PI25244 corrects a problem that incorrect output could happen for an SQL statement containing a temporal table and a period-specification when parallelism is enabled and an index is created on the period 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.). UI22987 HOLD DATA END UI22993 HOLD DATA START ++ HOLD(UI22993) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14317) COMMENT (***Action for PI22468: A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PI22468 corrects a problem where ABEND04E RC00E72018 can occur in DSNXSORI+481E during execution time for a query meeting the following conditions: 1. The query contains GROUP BY; 2. An attempt is made to use an index to support the GROUP BY grouping; 3. The index chosen from #2 is on a different table than the table containing the columns referenced in the GROUP BY; 4. More than one GROUP BY column maps to the same column in the chosen index; and 5. Parallelism is used on the 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.). UI22993 HOLD DATA END UI23213 HOLD DATA START ++ HOLD(UI23213) SYS FMID(HDBAA10) REASON(ACTION) DATE(14323) COMMENT (The fix for APAR PI29090 requires the fix for APAR PM29188, for FMID JDBAA1K to be applied.). UI23213 HOLD DATA END UI23214 HOLD DATA START ++ HOLD(UI23214) SYS FMID(JDBAA1K) REASON(ACTION) DATE(14323) COMMENT (The fix for APAR PI29188 requires the fix for APAR PM29090, for FMID HDBAA10 to be applied.). UI23214 HOLD DATA END UI23220 HOLD DATA START ++ HOLD(UI18014) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14323) COMMENT (This PTF modifies the source code for DSNTIAUL (DB2 sample unload program). Applying the PTF updates only the DSNTIAUL source code. The updates do not affect your current DSNTIAUL load module until you reprepare and rebind it. * On DB2 10 systems running in conversion mode (CM) or enabling-new-function mode (ENFM), no action is required after applying this PTF. The updates will be used when you run the DB2 10 IVP jobs after DB2 enters new-function mode (NFM). * On DB2 10 systems in new-function mode, you need to take the following actions: (1) If you use a modified version of DSNTIAUL, redo your modifications (2) Run your customized copy of IVP job DSNTEJ2A to assemble and link-edit the load module and bind the plan for DSNTIAUL. Running this job will also verify that the DSNTIAUL was prepared and bound correctly.). UI23220 HOLD DATA END UI23220 HOLD DATA START ++ HOLD(UI23220) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14323) COMMENT (This PTF modifies the source code for DSNTIAUL (DB2 sample unload program). Applying the PTF updates only the DSNTIAUL source code. The updates do not affect your current DSNTIAUL load module until you reprepare and rebind it. * On DB2 10 systems running in conversion mode (CM) or enabling-new-function mode (ENFM), no action is required after applying this PTF. The updates will be used when you run the DB2 10 IVP jobs after DB2 enters new-function mode (NFM). * On DB2 10 systems in new-function mode, you need to take the following actions: (1) If you use a modified version of DSNTIAUL, redo your modifications (2) Run your customized copy of IVP job DSNTEJ2A to assemble and link-edit the load module and bind the plan for DSNTIAUL. Running this job will also verify that the DSNTIAUL was prepared and bound correctly.). UI23220 HOLD DATA END UI23220 HOLD DATA START ++ HOLD(UI23147) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14323) COMMENT (This PTF modifies the source code for DSNTIAUL (DB2 sample unload program). Applying the PTF updates only the DSNTIAUL source code. The updates do not affect your current DSNTIAUL load module until you reprepare and rebind it. * On DB2 10 systems running in conversion mode (CM) or enabling-new-function mode (ENFM), no action is required after applying this PTF. The updates will be used when you run the DB2 10 IVP jobs after DB2 enters new-function mode (NFM). * On DB2 10 systems in new-function mode, you need to take the following actions: (1) If you use a modified version of DSNTIAUL, redo your modifications (2) Run your customized copy of IVP job DSNTEJ2A to assemble and link-edit the load module and bind the plan for DSNTIAUL. Running this job will also verify that the DSNTIAUL was prepared and bound correctly.). UI23220 HOLD DATA END UI23245 HOLD DATA START ++ HOLD(UI23245) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14324) COMMENT (***Action for PI25743: See PI25743 APAR/PTF text for additional information about why a REBIND is necessary. PI25743 corrects a problem an ABEND04E RC00E72018 at DSNXSORI OFFSET 5E02 may occur when a sort-merge join query with merge-composite runs on parallel and the join predicate has different nullability, data type, or data length. 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.). UI23245 HOLD DATA END UI23268 HOLD DATA START ++ HOLD(UK96752) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14324) COMMENT (***Action for PM92790: See PM92790 APAR/PTF text for additional information about why a REBIND is necessary. PM92790 corrects a problem that ABEND04E RC00E70005 at the location DSNXGRDS DSNXGSFN M628 could happen or SQLCODE -180 could be incorrectly issued for an SQL statement that satisfies all of the following conditions: 1. It contains a table expression or view with SET operator. 2. The CCSIDs of the tables in the SET operation branches are different. 3. A Date or Timestamp item is in the SELECT list of one of the SET operation branches. 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.). UI23268 HOLD DATA END UI23268 HOLD DATA START ++ HOLD(UI23268) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14324) COMMENT (***Action for PI28667: See PI28667 APAR/PTF text for additional information about why a REBIND is necessary. PI28667 corrects a problem of ABEND04E RC00C90101 at location DSNIDM DSNOTFLA ERQUAL5009 when a statement satisfies all of the following list of conditions: 1. A fullselect statement contains UNION ALL. 2. One of the sub-query select list of UNION ALL references an item with CLOB or DBCLOB data type. 3. The corresponding of other sub-query select list of UNION ALL is a literal. 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.). UI23268 HOLD DATA END UI23268 HOLD DATA START ++ HOLD(UI21715) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14324) COMMENT (***Action for PI24608: See PI24608 APAR/PTF text for additional information about why a REBIND is necessary. PI24608 corrects a problem of ABEND04E RC00C900D0 at location DSNXGRDS DSNXROHB OFFSETCD80 when a statement satisfies all of the following list of conditions: 1. A fullselect statement contains UNION ALL. 2. One of the sub-query select list of UNION ALL references a column with BLOB data type. 3. The corresponding of other sub-query select list of UNION ALL is BINARY or VARBINARY data type. 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.). UI23268 HOLD DATA END UI23333 HOLD DATA START ++ HOLD(UI23333) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14325) COMMENT (This PTF modifies DSNTIJAS, the job for creating the DB2 for z/OS accelerator database, DSNACCEL. It adds a new, optional job step that can be used to add RESTRICT ON DROP to the tables in DSNACCEL to prevent them from being accidentally dropped. If you have already installed or migrated to this version of DB2 and you maintain a customized copy of job DSNTIJAS then after applying this PTF, you can add the new job step to the end of your DSNTIJAS job. You can also run this job step to add RESTRICT ON DROP to the tables in your accelerator database. To update your DSNTIJAS job: (1) Copy the new job step, DSNTICR, including the comment block, from prefix.SDSNSAMP(DSNTIJAS) into your DSNTIJAS job. (2) Customize job step DSNTICR for your DB2: (A) Change the subsystem name '!DSN!' to the SSID of your DB2. (B) Change 'DSN!!0' to the prefix of the target library for DB2. (C) Change 'DSNTIA!!' to the plan name for DSNTIAD on your DB2. To add RESTRICT ON DROP to the tables in your accelerator database, uncomment job step DSNTICR and then start your DSNTIJAS job from job step DSNTICR.). UI23333 HOLD DATA END UI23379 HOLD DATA START ++ HOLD(UI23379) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14328) COMMENT (See PI20544 APAR/PTF text for additional information about why a REBIND is necessary. PI20544 corrects a problem of XML storage not being released when the query includes XMLPARSE function and its output is passed into XMLTABLE function for further processing. 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.). UI23379 HOLD DATA END UI23379 HOLD DATA START ++ HOLD(UI23379) SYS FMID(HDBAA10) REASON(MULTSYS) DATE(14328) COMMENT (PI20544 is the enabling APAR that corrects the problem where XML storage is not being released when the query includes XMLPARSE function and its output is passed into XMLTABLE function for further processing. Earlier, a pre-conditioning APAR PI19349 was delivered that is required. In a data sharing group, this pre-conditioning APAR should be applied to all members before applying this enabling APAR to any member. The fix will be enabled once this enabling APAR is applied. If the 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.). UI23379 HOLD DATA END UI23423 HOLD DATA START ++ HOLD(UK73137) SYS FMID(HDBAA10) REASON(MULTSYS) DATE(14329) COMMENT (PM44216 is an enabling APAR that includes additional support for the subsystem parameter XML_RANDOMIZE_DOCID (added by APAR PM31487). The additional support is enabled when XML_RANDOMIZE_DOCID is set to YES. Users who are not creating tables with XML columns on data-sharing systems will not be affected by PM44210/PM44216. For users who will create new tables with XML columns in a data-sharing group and intend to enable the parameter XML_RANDOMIZE_DOCID, the pre-conditioning APAR PM44210 for DB2 9 for z/OS and the pre-conditioning APAR PM31486 for DB2 10 for z/OS must be applied to all data-sharing members before enabling APAR PM44216 is applied to any member.). UI23423 HOLD DATA END UI23426 HOLD DATA START ++ HOLD(UK80523) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14329) COMMENT (***Action for PM57853: See PM57853 APAR/PTF text for additional information about why a REBIND is necessary. PM57853 corrects a problem with incorrect output (less rows found) that may occur on a query with a join if an index on the outer table is chosen where predicates exist on multiple columns of the index, and sort merge join is performed. 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.). UI23426 HOLD DATA END UI23426 HOLD DATA START ++ HOLD(UK69881) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14329) COMMENT (***Action for PM42245: See PM42245 APAR/PTF text for additional information about why a REBIND is necessary. PM42245 corrects a problem of incorrect output that can occur when a subquery is transformed into a join and sort merge join is chosen. 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.). UI23426 HOLD DATA END UI23552 HOLD DATA START ++ HOLD(UI23552) SYS FMID(HDBAA10) REASON(ACTION) DATE(14336) COMMENT (The PTF for APAR PI28671 corrects a deadlock problem that occurs when running multiple SQL mass delete jobs on multi-table table spaces concurrently. The PTF does not need to be applied to all data sharing members at once. However, it should be applied to all members where multiple SQL mass delete jobs on multi-table table spaces are run, in order to avoid deadlocks.). UI23552 HOLD DATA END UI23664 HOLD DATA START ++ HOLD(UK96886) SYS FMID(HDBAA10) REASON(ACTION) DATE(14339) COMMENT (DFSMS APAR OA41617 is required and must be installed in order to alleviate the problem reported for PM87248.). UI23664 HOLD DATA END UI23778 HOLD DATA START ++ HOLD(UI23778) SYS FMID(HIZAA10) REASON(ACTION) DATE(14344) COMMENT (This PTF updates the ERLY code. After you apply this fix, you must either re-IPL z/OS with CLPA, or issue the -REFRESH DB2 command. The procedure for issuing the REFRESH command is: 1. Issue -STOP DB2 2. Issue MODIFY LLA,REFRESH 3. Issue -REFRESH DB2,EARLY). UI23778 HOLD DATA END UI23953 HOLD DATA START ++ HOLD(UI23953) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(14350) COMMENT (***Action for PI25489: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PI25489 corrects a problem that DB2 may select an inefficient range list access plan over an efficient single index access plan for a query with OR predicates if the range list access plan matches a superset of matching columns of the single index access plan. 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.). UI23953 HOLD DATA END UI24111 HOLD DATA START ++ HOLD(UI12956) SYS FMID(HDBAA10) REASON(DOC) DATE(14357) COMMENT (PM99151 introduces the following changes to externals: The command response messages for the -DISPLAY ARCHIVE command are being changed to prevent the truncation of the archive log data set name when the data set name exceeds 42 characters in length. The archive read data lines follow the DSNJ322I header message. To avoid the possible data set name truncation, the STATUS column in the archive log data lines will be changed from 6 characters to 4 characters. For example - before the APAR change: DSNJ322I @ DISPLAY ARCHIVE REPORT FOLLOWS- COUNT TIME (TAPE UNITS) (MIN,SEC) DSNZPARM 3 0,00 CURRENT 3 0,00 =============================================== ADDR STATUS CORR-ID VOLSER DATASET_NAME BUSY SHEDDEN A00780 DSNC000.VA1A.ARCLG1.D13277..... And after the APAR change: DSNJ322I @ DISPLAY ARCHIVE REPORT FOLLOWS- COUNT TIME (TAPE UNITS) (MIN,SEC) DSNZPARM 3 0,00 CURRENT 3 0,00 =============================================== ADDR STAT CORR-ID VOLSER DATASET_NAME BUSY SHEDDEN A00780 DSNC000.VA1A.ARCLG1.D13277...... END OF DISPLAY ARCHIVE REPORT. DSN9022I @ DSNJC001 '-DIS ARC' NORMAL COMPLETION The STATUS column heading is being changed to STAT. Also, the AVAIL status is being changed to AVL and the RECALL status is being changed to RCAL. If there is automation in place that parses the -DISPLAY ARCHIVE command response messages, adjustments will have to be made to account for these changes. Information about these changed messages 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.). UI24111 HOLD DATA END UI24111 HOLD DATA START ++ HOLD(UI24111) SYS FMID(HDBAA10) REASON(DOC) DATE(14357) COMMENT (This PTF modifies message DSNJ370I output by the -DISPLAY LOG command to indicate whether or not zHyperWrite will be requested when writing the active log data sets. DSNJ370I csect-name LOG DISPLAY CURRENT COPY1 LOG = IS % FULL CURRENT COPY2 LOG = IS % FULL H/W RBA = H/O RBA = FULL LOGS TO OFFLOAD = nn OF mm, OFFLOAD TASK IS SOFTWARE ACCELERATION IS The last line of the DSNJ370I message is new with this PTF. The rest of the message is unchanged. Values for are ENABLED - zHyperWrite will be requested for active log writes DISABLED - zHyperWrite will not be used for active log writes If zHyperWrite is requested, but the active log resides on a volume that does not support zHyperWrite, the zHyperWrite option is ignored and the log write is done as though zHyperWrite was not requested. This PTF adds a new DB2 subsystem parameter in DSN6LOGP called REMOTE_COPY_SW_ACCEL that can be used to specify whether DB2 should use software to control the remote copy process for active log output in PPRC environments. Valid settings are: * DISABLE: Means that the hardware PPRC function is used if available; this setting is appropriate for non-PPRC environments. ==> This is the default setting * ENABLE : Means that host software is used; this option can improve overall DB2 performance in PPRC environments at the expense of some additional SRB CPU time in the xxxxMSTR address space. z/OS Version 2 Release 1 or later is required to support this function. The setting of REMOTE_COPY_SW_ACCEL is changeable via the -SET SYSPARM command. To use zHyperWrite, the following z/OS APARs are required: OA41906 OA43661 OA43662 OA43663 OA44973 OA45125 OA43453 OA44240 OA45662 If these APARs are not installed, the setting of REMOTE_COPY_SW_ACCEL has no effect. DB2 will function as if DISABLE was specified. --------------------------------------------------------------- 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 the package for DSNTXAZP 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 REMOTE_COPY_SW_ACCEL= (where is DISABLE or ENABLE) to the invocation of the DSN6LOGP macro. Make sure to add a continuation character in column 72 if needed. If you omit adding REMOTE_COPY_SW_ACCEL here, the value will be set to the default of DISABLE 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 REMOTE_COPY_SW_ACCEL 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. In each such copy, add the following line: REMOTE_COPY_SW_ACCEL CHAR M DISABLE ENABLE Change to the value you specified for REMOTE_COPY_SW_ACCEL in step (2), above. --------------------------------------------------------------- (4) Update the package for DSNTXAZP ==> This action is required for all customers who use DSNTXAZP to update DB2 CLIST input (DSNTIDXX) members Submit a job that contains the following BIND statement: BIND PACKAGE(DSNTXAZP) MEMBER(DSNTXAZP) - ACTION(ADD) ENCODING(EBCDIC) - LIBRARY('prefix.SDSNDBRM') where is the high-level qualifier for the DB2 subsystem target libraries. ----------------------------------------------------------). UI24111 HOLD DATA END UI24111 HOLD DATA START ++ HOLD(UI24111) SYS FMID(HDBAA10) REASON(ACTION) DATE(14357) COMMENT (To use zHyperWrite for active log data set writes, several actions are required: - Set the new REMOTE_COPY_SW_ACCEL ZPARM to ENABLE - Apply the z/OS maintenance listed in the ++HOLD DOC - Install microcode level 7.4 on DS8870 DASD subsystems where active log data sets reside.). UI24111 HOLD DATA END UI24155 HOLD DATA START ++ HOLD(UI24155) SYS FMID(HDBAA10) REASON(ACTION) DATE(14363) COMMENT (***Action for PI31086 This PTF modifies DSNTWR, the external module for the SYSPROC.WLM_REFRESH stored procedure. It fixes a bug that can cause SYSPROC.WLM_REFRESH to fail with abend S72A reason code 10 when called with a NULL or blank SSID parameter. Post-apply actions for this PTF: ------------------------------- If you have already installed or migrated to this version of DB2, you need to take the following actions after applying this PTF: (1) Rebuild or delete any private copies of module DSNTWR (2) Bind the package for DSNTWR ---------------------------------------------------------------- (1) Rebuild or delete any private copies of module DSNTWR *********************************************************** * This action is required for all customers who maintain * a private copy of DSNTWR, the external module for * WLM_REFRESH *********************************************************** This PTF updates DSNTWR, the DB2-supplied load module for WLM_REFRESH, in the prefix.SDSNLOAD library. It also updates DSNTWRS, the sample source code for DSNTWR, in the prefix.SDSNSAMP library. If you maintain a private copy of module DSNTWR, you either need to delete it in order to use the DB2-supplied DSNTWR module instead or you need to update it to include the changes introduced by this PTF. To continue maintaining your own DSNTWR module, proceed as follows: (a) Compare your source code to that in member DSNTWRS of the prefix.SDSNSAMP library to identify necessary code changes. (b) After completing your code changes, use job steps PH06WS01 and PH06WS02 of your customized copy of job DSNTEJ6W to prepare an updated DSNTWR module and to bind a package from the updated DBRM. ---------------------------------------------------------------- (2) Bind the package for DSNTWR *********************************************************** * This action is required for all customers who use the * DB2-supplied DSNTWR module *********************************************************** This PTF updates DSNTWR, the external module for WLM_REFRESH plus the DBRM for DSNTWR. After applying this PTF, proceed as follows to bind a package from the updated DSNTWR DBRM. 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, re-running 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 re-run DSNTIJRT without the PREVIEW option or customize and run the generated job.). UI24155 HOLD DATA END UI24164 HOLD DATA START ++ HOLD(UI24164) SYS FMID(HDBAA10) REASON(ACTION) DATE(14363) COMMENT (Applying APAR PI27903 will not remove the existing extra record from SYSIBM.SYSCOPY catalog table. If needed, after applying APAR PI27903, the user could either 1. DROP the affected tables, then recreate them to remove the extra SYSCOPY record. 2. OR execute a utility MODIFY RECOVERY job to remove it: MODIFY RECOVERY TABLESPACE database-name.table-space-name DELETE AGE(*) Then execute a utility REPAIR DBD REBUILD job to fix the DBD error: REPAIR DBD REBUILD DATABASE database-name Please note, if user elects the option 2, after the MODIFY RECOVERY TABLESPACE job executed, the extra SYSCOPY record will be removed. However, the REPAIR DBD DIAGNOSE/TEST job still reports error. In order to fix it, users needs to execute the REPAIR DBD REBUILD job to correct it.). UI24164 HOLD DATA END UI24205 HOLD DATA START ++ HOLD(UI24205) SYS FMID(HDBAA10) REASON(ACTION) DATE(15005) COMMENT (APAR PI26452 has modified the UNLOAD SPANNED YES utility to ignore the Large Block Interface for output data sets. The data set will use system determined block size (SDB) and the records will continue to be written in the variable blocked spanned format. The Utility Guide and Reference manual has been updated to document that UNLOAD SPANNED YES ignores the Large Block Interface. It will be added to the following sections: Data sets that online utilities use UNLOAD utility under the SPANNED keyword UNLOAD utility under Unloading data in spanned record format). UI24205 HOLD DATA END UI24305 HOLD DATA START ++ HOLD(UI24305) SYS FMID(HDBAA10) REASON(ACTION) DATE(15009) COMMENT (The PTF for APAR PI25214 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. The fix for APAR PI25214 requires the fix for APAR PI26321, for FMID JDBAA1K to be applied.). UI24305 HOLD DATA END UI24307 HOLD DATA START ++ HOLD(UK78633) SYS FMID(JDBAA1K) REASON(ACTION) DATE(15009) COMMENT (The fix for APAR PM60438 is a companion fix. It requires the fix for APAR PM57632, for FMID HDBAA10 to be applied.). UI24307 HOLD DATA END UI24307 HOLD DATA START ++ HOLD(UK71420) SYS FMID(JDBAA1K) REASON(ACTION) DATE(15009) COMMENT (The fix for APAR PM42560 is a companion fix. It requires the fix for APAR PM27962, for FMID HDBAA10 to be applied.). UI24307 HOLD DATA END UI24332 HOLD DATA START ++ HOLD(UI24332) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15012) COMMENT (See PI27007 APAR/PTF text for additional information about why a REBIND is necessary. PI27007 implements the code to clean up the XML storage token after the query finishes the processing. So the DSNXMLSM M098 ABEND will not occur when the same query is called again. 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.). UI24332 HOLD DATA END UI24359 HOLD DATA START ++ HOLD(UI24359) SYS FMID(HDBAA10) REASON(MULTSYS) DATE(15013) COMMENT (PI22531 is an APAR that includes changes to support a functional code change that will be enabled once this APAR is applied. Earlier, a pre-conditioning APAR PI21188 was delivered. In a data sharing group, this 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 following APAR enables the support for Dynamic Statement Cache for accelerated dynamic queries.). UI24359 HOLD DATA END UI24359 HOLD DATA START ++ HOLD(UI24359) SYS FMID(HDBAA10) REASON(ACTION) DATE(15013) COMMENT (This PTF adds the following columns to the DB2 10 DSN_STATEMENT_CACHE_TABLE: "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT "ACCELERATED" CHAR(10) NOT NULL WITH DEFAULT 'NO' CHECK("ACCELERATED" IN('ACCEL_ONLY', 'NO', 'NEVER', 'YES')) "STAT_ACC_ELAP" BIGINT NOT NULL WITH DEFAULT "STAT_ACC_CPU" BIGINT NOT NULL WITH DEFAULT "STAT_ACC_ROW" BIGINT NOT NULL WITH DEFAULT "STAT_ACC_BYTE" BIGINT NOT NULL WITH DEFAULT "STAT_ACC_1ROW" BIGINT NOT NULL WITH DEFAULT "STAT_ACC_DB2" BIGINT NOT NULL WITH DEFAULT "STAT_ACC_EXEC" BIGINT NOT NULL WITH DEFAULT "STAT_ACC_WAIT" BIGINT NOT NULL WITH DEFAULT "ACCEL_OFFLOAD_ELIGIBLE" CHAR(1) NOT NULL WITH DEFAULT 'N' "ACCELERATOR_NAME" VARCHAR(128) WITH DEFAULT NULL No action is required after applying this PTF to a DB2 subsystem or data sharing group that is currently in DB2 10 conversion mode. After DB2 enters new-function mode, follow directions in the DB2 10 Installation Guide for migrating explain tables to DB2 10 format. If you have installed or migrated to DB2 10 new-function mode, you need to take the following actions after applying this PTF: (1) Update your existing DSN_STATEMENT_CACHE_TABLE tables to the new format (2) Update your customized copy of sample DDL member DSNTESC Detailed guidance follows: ---------------------------------------------------------------- (1) Update your existing DSN_STATEMENT_CACHE_TABLE tables to the new format ---------------------------------------------------------------- => This action is required for all customers who have created one or more DSN_STATEMENT_CACHE_TABLE tables: (1) Use the following query to identify creators of existing DSN_STATEMENT_CACHE_TABLE tables that are in the old DB2 10 format: SELECT TBCREATOR FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = 'DSN_STATEMENT_CACHE_TABLE' GROUP BY TBCREATOR, TBNAME HAVING( MAX(COLNO) < 68 ); (2) Use job DSNTIJXA to update the DSN_STATEMENT_CACHE_TABLE for each creator identified by the query. Alternatively, you can process the following statements to add the new columns: ALTER TABLE prefix.DSN_STATEMENT_CACHE_TABLE ADD COLUMN "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT; ALTER TABLE prefix.DSN_STATEMENT_CACHE_TABLE ADD COLUMN "ACCELERATED" CHAR(10) NOT NULL WITH DEFAULT 'NO' CHECK("ACCELERATED" IN('ACCEL_ONLY', 'NO', 'NEVER', 'YES')); ALTER TABLE prefix.DSN_STATEMENT_CACHE_TABLE ADD COLUMN "STAT_ACC_ELAP" BIGINT NOT NULL WITH DEFAULT; ALTER TABLE prefix.DSN_STATEMENT_CACHE_TABLE ADD COLUMN "STAT_ACC_CPU" BIGINT NOT NULL WITH DEFAULT; ALTER TABLE prefix.DSN_STATEMENT_CACHE_TABLE ADD COLUMN "STAT_ACC_ROW" BIGINT NOT NULL WITH DEFAULT; ALTER TABLE prefix.DSN_STATEMENT_CACHE_TABLE ADD COLUMN "STAT_ACC_BYTE" BIGINT NOT NULL WITH DEFAULT; ALTER TABLE prefix.DSN_STATEMENT_CACHE_TABLE ADD COLUMN "STAT_ACC_1ROW" BIGINT NOT NULL WITH DEFAULT; ALTER TABLE prefix.DSN_STATEMENT_CACHE_TABLE ADD COLUMN "STAT_ACC_DB2" BIGINT NOT NULL WITH DEFAULT; ALTER TABLE prefix.DSN_STATEMENT_CACHE_TABLE ADD COLUMN "STAT_ACC_EXEC" BIGINT NOT NULL WITH DEFAULT; ALTER TABLE prefix.DSN_STATEMENT_CACHE_TABLE ADD COLUMN "STAT_ACC_WAIT" BIGINT NOT NULL WITH DEFAULT; ALTER TABLE prefix.DSN_STATEMENT_CACHE_TABLE ADD COLUMN "ACCEL_OFFLOAD_ELIGIBLE" CHAR(1) NOT NULL WITH DEFAULT 'N'; ALTER TABLE prefix.DSN_STATEMENT_CACHE_TABLE ADD COLUMN "ACCELERATOR_NAME" VARCHAR(128) WITH DEFAULT NULL; Note: Altering the table will cause the containing table space to be placed in advisory REORG pending state. REORG the table space to clear this state. ---------------------------------------------------------------- (2) Update your customized copy of sample DDL member DSNTESC ---------------------------------------------------------------- => This action is required for all customers who maintain customized copies of DSNTESC Edit DSNTESC, locate the CREATE TABLE statement for DSN_STATEMENT_CACHE_TABLE, and proceed as follows: (a) Add a comma to the end of the specification for the STAT_SUS_LOG column (b) Add the following column specifications after the one for STAT_SUS_LOG: "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT, "ACCELERATED" CHAR(10) NOT NULL WITH DEFAULT 'NO', CHECK("ACCELERATED" IN('ACCEL_ONLY', 'NO', 'NEVER', 'YES')), "STAT_ACC_ELAP" BIGINT NOT NULL WITH DEFAULT, "STAT_ACC_CPU" BIGINT NOT NULL WITH DEFAULT, "STAT_ACC_ROW" BIGINT NOT NULL WITH DEFAULT, "STAT_ACC_BYTE" BIGINT NOT NULL WITH DEFAULT, "STAT_ACC_1ROW" BIGINT NOT NULL WITH DEFAULT, "STAT_ACC_DB2" BIGINT NOT NULL WITH DEFAULT, "STAT_ACC_EXEC" BIGINT NOT NULL WITH DEFAULT, "STAT_ACC_WAIT" BIGINT NOT NULL WITH DEFAULT, "ACCEL_OFFLOAD_ELIGIBLE" CHAR(1) NOT NULL WITH DEFAULT 'N', "ACCELERATOR_NAME" VARCHAR(128) WITH DEFAULT NULL (c) Save your changes). UI24359 HOLD DATA END UI24366 HOLD DATA START ++ HOLD(UI24366) SYS FMID(HDBAA10) REASON(ACTION) DATE(15013) COMMENT (The PTF for APAR PI27495 has modified CSECTs in both the DSNUT110 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.). UI24366 HOLD DATA END UI24371 HOLD DATA START ++ HOLD(UI24371) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15013) COMMENT (When selecting columns from a partitioned table space (Universal table space and classic table space) using direct row access with a ROWID column in the predicate , optimized code to perform the select will be bypassed due to an error. This could lead to performance degradation or incorrect output. To rectify, rebind the plan or package.). UI24371 HOLD DATA END UI24530 HOLD DATA START ++ HOLD(UK95379) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15020) COMMENT (***Action for PM88698: See PM88698 APAR/PTF text for additional information about why a REBIND is necessary. PM88698 corrects a problem that incorrect output could happen for an SQL statement that contains special registers in GRPOUP BY and 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.). UI24530 HOLD DATA END UI24530 HOLD DATA START ++ HOLD(UI15888) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15020) COMMENT (***Action for PI08763: See PI08763 APAR/PTF text for additional information about why a REBIND is necessary. PI08763 corrects a problem of incorrect output for an SQL statement that contains OUTER JOIN and subquery predicate, where the subquery predicate contains a table expression or view 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.). UI24530 HOLD DATA END UI24530 HOLD DATA START ++ HOLD(UI15912) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15020) COMMENT (***Action for PI08206: See PI08206 APAR/PTF text for additional information about why a REBIND is necessary. PI08206 corrects the following problems: An ABEND0C4 RC00000038 at location DSNXGRDS DSNXOCT OFFSET0540 or ABEND0C4 RC00000004 at location DSNXGRDS DSNXRBND OFFSET0814 may occur when an SQL statement satisfies following conditions: 1. a statement contains nested UNION/UNION ALL with ORDER BY clause. 2. some sort-keys in the ORDER BY clause is not referenced in the select list. Incorrect output may occur when a SQL statement contains nested UNION/UNION ALL with FETCH FIRST 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.). UI24530 HOLD DATA END UI24541 HOLD DATA START ++ HOLD(UI24541) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15021) COMMENT (***Action for PI30113: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PI30113 corrects a problem of an incorrect filter factor for an XMLEXISTS predicate with multiple range conditions on the same element. The incorrect filter factor could lead to an inefficient access path 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.). UI24541 HOLD DATA END UI24603 HOLD DATA START ++ HOLD(UI24603) SYS FMID(HDBAA10) REASON(ACTION) DATE(15022) COMMENT (***Action for PI29024: See PI29024 APAR/PTF text for additional information about why an ACTION is necessary. PI29024 corrects a problem which SQLCODE -104 is not issued as that expected when a column is qualified in CREATE INDEX statement. To make this fix effective for an INDEX creation, the object must be dropped and recreated after application of this PTF. Review the letter to determine which, if any, applications could be affected by this change.). UI24603 HOLD DATA END UI24647 HOLD DATA START ++ HOLD(UK70649) SYS FMID(HDBAA10) REASON(ACTION) DATE(15023) 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.). UI24647 HOLD DATA END UI24713 HOLD DATA START ++ HOLD(UI24713) SYS FMID(HDBAA10) REASON(DOC) DATE(15027) COMMENT (This new function APAR now enables Accelerator Statement Level Monitoring support in IFCID 316 and 401. This APAR has functional dependency on the IBM DB2 Analytics Accelerator V4 or higher. Without the server support, no statement level monitoring metric will be reported. This APAR enables DB2 to report SQL statement's eligibility to be executed on the accelerators and to report SQL Statement Level Monitoring statistics for queries that are accelerated. The statement's eligibility and statistics will be reported in the IFCID 316, 401 trace records and the DSN_STATEMENT_CACHE_TABLE. The DSN_STATEMENT_CACHE_TABLE changes: 1. The ACCEL_OFFLOAD_ELIGIBLE column will be set to 'Y' when DB2 determines a dynamic cached query is eligible to be executed on the IBM DB2 Analytics Accelerator, when zparm or special register for accelerator modeling is enabled. 'N' is the default value. The statement is not eligible for acceleration. 2. The following columns in DSN_STATEMENT_CACHE_TABLE are filled with execution statistics as the dynamic cached statement is actually accelerated (column ACCELERATED is 'YES'). These columns' description is listed below: Column Data Type Description ----------------------------------------------------------- STAT_ACC_ELAP BIGINT Accumulated accelerator Elapsed time ----------------------------------------------------------- STAT_ACC_CPU BIGINT Accumulated accelerator CPU time ----------------------------------------------------------- STAT_ACC_ROW BIGINT Accumulated # of rows returned from accelerator ----------------------------------------------------------- STAT_ACC_BYTE BIGINT Accumulated # of bytes returned from accelerator ----------------------------------------------------------- STAT_ACC_1ROW BIGINT Time waited for 1st row of query result to be available from accelerator ----------------------------------------------------------- STAT_ACC_DB2 BIGINT Total time accelerator waited for DB2 to request query results ----------------------------------------------------------- STAT_ACC_EXEC BIGINT Accumulated accelerator Execution time ----------------------------------------------------------- STAT_ACC_WAIT BIGINT Accumulated accelerator queue Wait time ----------------------------------------------------------- 3. The ACCELERATOR_NAME column contains the concatenated name(s) of accelerator(s) where the query is accelerated. Instrumentation changes: 1. IFCID 316 is changed to report when a cached statement is eligible for execution on an accelerator. When a cached statement is accelerated, this trace record also contains statistics of the dynamic statement's acceleration in QW0316's R20 section per accelerator. The 316 changes are reflected in the DSNDQW04 copy file and related DB2 documentation on IFCID 316. 2. IFCID 401 is changed to report when a static statement is eligible for execution on an accelerator. When a statement is accelerated, this trace record also contains statistics of the static statement's acceleration in QW0401's R20 section per accelerator. The 401 changes are reflected in the DSNDQW05 copy file and related DB2 documentation on IFCID 401.). UI24713 HOLD DATA END UI24713 HOLD DATA START ++ HOLD(UI14233) SYS FMID(HDBAA10) REASON(ACTION) DATE(15027) COMMENT (The package name and collection ID fields of IFCID 360 will now be written out as EBCDIC if the zparm UIFCIDS is set to NO, and UNICODE if the zparm is YES. Please note that existing applications that read these fields may need to be changed appropriately.). UI24713 HOLD DATA END UI24723 HOLD DATA START ++ HOLD(UI24723) SYS FMID(HDBAA10) REASON(ACTION) DATE(15028) COMMENT (See PI30645 APAR/PTF text for additional information about why an ACTION is necessary. PI30645 corrects a problem of incorrect output when running a native SQL procedure which contains an SQL statement that references a VARGRAPHIC type SQL variable multiple times. 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 procedures could be affected by this change.). UI24723 HOLD DATA END UI24723 HOLD DATA START ++ HOLD(UI17182) SYS FMID(HDBAA10) REASON(ACTION) DATE(15028) COMMENT (PI11454 corrects a problem that incorrect statement number and section number could be generated when a native SQL procedure or non-inline SQL scalar function satisfies following conditions: 1. CREATE PROCEDURE/FUNCTION statement contains at least 2 assignment-statements that references user-defined function (UDF) or built-in function(BIF). 2. One of the assignment-statement references a UDF which does not exist during bind time. 3. One of the assignment-statement references a BIF. After the application of this PTF, ALTER PROCEDURE/FUNCTION REGENERATE is required for each native SQL procedure/SQL scalar function that is affected. Review the PTF cover letter to determine which, if any, native SQL procedure and SQL scalar function could be affected by this change.). UI24723 HOLD DATA END UI24725 HOLD DATA START ++ HOLD(UI24725) SYS FMID(HDBAA10) REASON(ACTION) DATE(15028) COMMENT (***Action for PI31554 (DB2 10 for z/OS) This PTF modifies the DB2-supplied stored procedure SYSPROC.ADMIN_INFO_SYSPARM to properly initialize storage for the procedure result set columns. *************************************************************** Actions for customers who are already using DB2 10: *************************************************************** If you have already installed or migrated to V10, run your copy of the installation job DSNTIJRT with MODE(INSTALL) to bind the ADMIN_INFO_SYSPARM 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.). UI24725 HOLD DATA END UI24889 HOLD DATA START ++ HOLD(UI24889) SYS FMID(HDBAA10) REASON(ACTION) DATE(15033) COMMENT (***Action for PI30243 This PTF modifies DSNTXAZP, a program that can be used to refresh a DB2 installation CLIST input (DSNTIDxx) member with DB2's current subsystem parameter, DSNHDECP parameter, and buffer pool settings. If you have already installed or migrated to this version of DB2 and you use DSNTXAZP then you need to update the package: Submit a job that contains the following BIND statement: BIND PACKAGE(DSNTXAZP) MEMBER(DSNTXAZP) - ACTION(ADD) ENCODING(EBCDIC) - LIBRARY('prefix.SDSNDBRM') where is the high-level qualifier for the DB2 subsystem target libraries.). UI24889 HOLD DATA END UI24889 HOLD DATA START ++ HOLD(UI24189) SYS FMID(HDBAA10) REASON(ACTION) DATE(15033) COMMENT (***Action for PI29286 (DB2 10) This PTF modifies DSNTXAZP, the DSNTIDxx refresh tool to fix an error that causes the wrong MAX VALUE setting to be assigned to the DSNTIDxx entries for SIMULATED_CPU_COUNT and SIMULATED_CPU_SPEED. 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 private copies of the DSNTIDxx CLIST input member (2) Update the package for DSNTXAZP Detailed guidance for these actions follows: ---------------------------------------------------------------- (1) Update private copies of the DSNTIDxx CLIST input member --------------------------------------------------------------- ==> This action is required for all customers who use DSNTXAZP You need to verify that your DSNTIDxx members have the appropriate MAX VALUE settings for SIMULATED_CPU_COUNT and SIMULATED_CPU_SPEED. For each private DSNTIDxx member, proceed as follows: (a) Edit the member (b) Locate the entry for SIMULATED_CPU_COUNT. If the fifth field (MAX VALUE) specifies 255, change it to NONE. In other words, change this format: SIMULATED_CPU_COUNT CHARR G OFF 255 to this: SIMULATED_CPU_COUNT CHARR G OFF NONE where is the current setting of SIMULATED_CPU_COUNT. (c) Locate the entry for SIMULATED_CPU_SPEED. If the fifth field (MAX VALUE) specifies 2147483647, change it to NONE. In other words, change this format: SIMULATED_CPU_SPEED CHARR G OFF 2147483647 to this: SIMULATED_CPU_SPEED CHARR G OFF NONE where is the current setting of SIMULATED_CPU_SPEED. (d) Save any changes you made and proceed to your next DSNTIDxx member Repeat steps (a) - (d) for each DSNTIDxx member. --------------------------------------------------------------- (2) Update the package for DSNTXAZP --------------------------------------------------------------- ==> This action is required for all customers who use DSNTXAZP to update DB2 CLIST input (DSNTIDXX) members Submit a job that contains the following BIND statement: BIND PACKAGE(DSNTXAZP) MEMBER(DSNTXAZP) - ACTION(ADD) ENCODING(EBCDIC) - LIBRARY('prefix.SDSNDBRM') where is the high-level qualifier for the DB2 subsystem target libraries. ---------------------------------------------------------------). UI24889 HOLD DATA END UI24966 HOLD DATA START ++ HOLD(UI24966) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15035) COMMENT (***Action for PI28688: See PI28688 APAR/PTF text for additional information about why a REBIND is necessary. PI28688 corrects a problem that DB2 may add an unnecessary sort to the access path when the query has left outer join or right outer join. Also, the preserved side is a table expression with an ORDER BY clause, GROUP BY clause, or 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.). UI24966 HOLD DATA END UI24966 HOLD DATA START ++ HOLD(UI24547) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15035) COMMENT (***Action for PI27160: See PI27160 APAR/PTF text for additional information about why a REBIND is necessary. PI27160 corrects a problem about filter factor when the query contains table with unique index on expression which has only 1 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.). UI24966 HOLD DATA END UI24968 HOLD DATA START ++ HOLD(UK95210) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15035) COMMENT (***Action for PM87166: See PM87166 APAR/PTF text for additional information about why a REBIND is necessary. PM87166 corrects a problem of Incorrect output may occur with UK92607 (V10) or UK92608 (V9). The non correlated SQL statement that uses constant <> constant predicate where the constants are integer, bigint or binary and the length of the constants are different may receive incorrect output. 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.). UI24968 HOLD DATA END UI24968 HOLD DATA START ++ HOLD(UK92607) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15035) COMMENT (***Action for PM80911: See PM80911 APAR/PTF text for additional information about why a REBIND is necessary. PM80911 enhances the performance of a non correlated SQL statements with predicates involving constant comparisons. The performance of a non correlated SELECT statement can be improved if it has a predicate that always evaluates to false because it is a not equal comparison between two constants that have the same values. The query can be executed without accessing the tables in the FROM clause because the predicate is always evaluated to false 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.). UI24968 HOLD DATA END UI24968 HOLD DATA START ++ HOLD(UK90311) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15035) COMMENT (***Action for PM76608: See PM76608 APAR/PTF text for additional information about why a REBIND is necessary. PM76608 corrects a problem that incorrect output could happen for an SQL statement that contains FETCH FIRST n ROWS ONLY in a table expression, on which there is a predicate. The access path of the SQL statement maybe changed after applying PM76608, as predicates will not be push down to the table expression that contains FETCH FIRST n ROWS ONLY. 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.). UI24968 HOLD DATA END UI25035 HOLD DATA START ++ HOLD(UI25035) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15040) COMMENT (***Action for PI25969: A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PI25969 corrects a problem of possible incorrect output for a query containing GROUP BY on a SUBSTR of a column which requires a cast. 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.). UI25035 HOLD DATA END UI25066 HOLD DATA START ++ HOLD(UI24889) SYS FMID(HDBAA10) REASON(ACTION) DATE(15041) COMMENT (***Action for PI30243 This PTF modifies DSNTXAZP, a program that can be used to refresh a DB2 installation CLIST input (DSNTIDxx) member with DB2's current subsystem parameter, DSNHDECP parameter, and buffer pool settings. If you have already installed or migrated to this version of DB2 and you use DSNTXAZP then you need to update the package: Submit a job that contains the following BIND statement: BIND PACKAGE(DSNTXAZP) MEMBER(DSNTXAZP) - ACTION(ADD) ENCODING(EBCDIC) - LIBRARY('prefix.SDSNDBRM') where is the high-level qualifier for the DB2 subsystem target libraries.). UI25066 HOLD DATA END UI25066 HOLD DATA START ++ HOLD(UI25066) SYS FMID(HDBAA10) REASON(ACTION) DATE(15041) COMMENT (PI29707 introduces the following changes to externals: New option for TEMPLATE utility: TIME Specifies time used in expansion of date and time DSN variables. The default TIME value is determined by the TEMPLATE_TIME subsystem parameter. Tip: Set all DB2 data sharing members to the same value. LOCAL Use local time at the DB2 server in the expansion of date and time in DSN variables. UTC Use Coordinated Universal Time (UTC) in the expansion of date and time in DSN variables. New subsystem parameter for DSN6SPRM: TEMPLATE_TIME Specifies the default setting for the TIME option of the DB2 TEMPLATE utility, which is used when the TEMPLATE statement does not specify the TIME option. Valid settings are: * LOCAL: Means that the local time is used. * UTC : Means that Coordinated Universal Time is used. ===> This is the default setting Information about these changes will be included in the DB2 for z/OS collection in the IBM Knowledge Center (http://www-01.ibm.com/support/knowledgecenter/ SSEPEK/db2z_prodhome.html) at a later date. If you have already installed or migrated to DB2 10, 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 the package for DSNTXAZP 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 TEMPLATE_TIME= (where is UTC or LOCAL) to the invocation of the DSN6SPRM macro. Make sure to add a continuation character in column 72 if needed. If you omit adding TEMPLATE_TIME here, the value will be set to the default of UTC 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 TEMPLATE_TIME 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. In each such copy, add the following line: TEMPLATE_TIME CHAR G LOCAL UTC Change to the value you specified for TEMPLATE_TIME in step (2), above. --------------------------------------------------------------- (4) Update the package for DSNTXAZP ==> This action is required for all customers who use DSNTXAZP to update DB2 CLIST input (DSNTIDXX) members Submit a job that contains the following BIND statement: BIND PACKAGE(DSNTXAZP) MEMBER(DSNTXAZP) - ACTION(ADD) ENCODING(EBCDIC) - LIBRARY('prefix.SDSNDBRM') where is the high-level qualifier for the DB2 subsystem target libraries. ---------------------------------------------------------------). UI25066 HOLD DATA END UI25066 HOLD DATA START ++ HOLD(UI24189) SYS FMID(HDBAA10) REASON(ACTION) DATE(15041) COMMENT (***Action for PI29286 (DB2 10) This PTF modifies DSNTXAZP, the DSNTIDxx refresh tool to fix an error that causes the wrong MAX VALUE setting to be assigned to the DSNTIDxx entries for SIMULATED_CPU_COUNT and SIMULATED_CPU_SPEED. 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 private copies of the DSNTIDxx CLIST input member (2) Update the package for DSNTXAZP Detailed guidance for these actions follows: ---------------------------------------------------------------- (1) Update private copies of the DSNTIDxx CLIST input member --------------------------------------------------------------- ==> This action is required for all customers who use DSNTXAZP You need to verify that your DSNTIDxx members have the appropriate MAX VALUE settings for SIMULATED_CPU_COUNT and SIMULATED_CPU_SPEED. For each private DSNTIDxx member, proceed as follows: (a) Edit the member (b) Locate the entry for SIMULATED_CPU_COUNT. If the fifth field (MAX VALUE) specifies 255, change it to NONE. In other words, change this format: SIMULATED_CPU_COUNT CHARR G OFF 255 to this: SIMULATED_CPU_COUNT CHARR G OFF NONE where is the current setting of SIMULATED_CPU_COUNT. (c) Locate the entry for SIMULATED_CPU_SPEED. If the fifth field (MAX VALUE) specifies 2147483647, change it to NONE. In other words, change this format: SIMULATED_CPU_SPEED CHARR G OFF 2147483647 to this: SIMULATED_CPU_SPEED CHARR G OFF NONE where is the current setting of SIMULATED_CPU_SPEED. (d) Save any changes you made and proceed to your next DSNTIDxx member Repeat steps (a) - (d) for each DSNTIDxx member. --------------------------------------------------------------- (2) Update the package for DSNTXAZP --------------------------------------------------------------- ==> This action is required for all customers who use DSNTXAZP to update DB2 CLIST input (DSNTIDXX) members Submit a job that contains the following BIND statement: BIND PACKAGE(DSNTXAZP) MEMBER(DSNTXAZP) - ACTION(ADD) ENCODING(EBCDIC) - LIBRARY('prefix.SDSNDBRM') where is the high-level qualifier for the DB2 subsystem target libraries. ---------------------------------------------------------------). UI25066 HOLD DATA END UI25097 HOLD DATA START ++ HOLD(UI19545) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15042) COMMENT (***Action for PM94681 (DB2 10) 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. These instructions supersede the instructions for any previous PTF on ADMIN_INFO_SQL. 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 bind DBRM DSNADMSS --------------------------------------------------------------- ==> This action is required for all V10 customers. This PTF updates programs module DSNADMSS plus the DBRM for DSNADMSS.After applying this PTF, proceed as follows to bind a package from the updated DSNADMSS DBRM. 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.). UI25097 HOLD DATA END UI25097 HOLD DATA START ++ HOLD(UI25097) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15042) COMMENT (***Action for PI31854 (DB2 10 for z/OS) This PTF modifies the DB2-supplied stored procedure SYSPROC.ADMIN_INFO_SQL and batch program DSNADMSB which are used to collect DB2 environmental service data. *************************************************************** Actions for customers who are already using DB2 10: *************************************************************** If you have already installed or migrated to V10, run your copy of the installation job DSNTIJRT with MODE(INSTALL) to bind the DSNADMSS 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.). UI25097 HOLD DATA END UI25097 HOLD DATA START ++ HOLD(UI22080) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15042) COMMENT (***Action for PI25905 (DB2 10 for z/OS) This PTF modifies the DB2-supplied stored procedure SYSPROC.ADMIN_INFO_SQL and batch program DSNADMSB which are used to collect DB2 environmental service data. *************************************************************** Actions for customers who are already using DB2 10: *************************************************************** If you have already installed or migrated to V10, run your copy of the installation job DSNTIJRT with MODE(INSTALL) to bind the DSNADMSS 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.). UI25097 HOLD DATA END UI25097 HOLD DATA START ++ HOLD(UI20314) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15042) COMMENT (***Action for PI21058 (DB2 10 for z/OS) 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. 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 bind DBRM DSNADMSS --------------------------------------------------------------- ==> This action is required for all V10 customers. This PTF updates program module DSNADMSS and the DBRM for DSNADMSS. After applying this PTF, proceed as follows to bind the updated DSNADMSS DBRM. Run your copy of job DSNTIJRT with MODE(INSTALL) to bind the DSNADMSS 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.). UI25097 HOLD DATA END UI25097 HOLD DATA START ++ HOLD(UI25095) SYS FMID(HDBAA10) REASON(ACTION) DATE(15042) COMMENT (***Action for PI30399 (DB2 10 for z/OS) This PTF modifies the DB2-supplied stored procedure SYSPROC.ADMIN_INFO_SQL and batch program DSNADMSB which are used to collect DB2 environmental service data. *************************************************************** Actions for customers who are already using DB2 10: *************************************************************** If you have already installed or migrated to DB2 10, run your copy of the installation job DSNTIJRT with MODE(INSTALL) to bind the DSNADMSS 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.). UI25097 HOLD DATA END UI25097 HOLD DATA START ++ HOLD(UI15281) SYS FMID(HDBAA10) REASON(ACTION) DATE(15042) COMMENT (***Action for PI07646 (DB2 10) 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. These instructions supersede any previous PTF instructions. If you have already installed or migrated to DB2 V10, you need to take the following action after applying this PTF: --------------------------------------------------------------- Run installation job DSNTIJRT to bind DBRM DSNADMSS --------------------------------------------------------------- ==> This action is required for all V10 customers. This PTF updates programs module DSNADMSS plus the DBRM for DSNADMSS. After applying this PTF, proceed as follows to bind a package from the updated DSNADMSS DBRM. 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, re-running 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 re-run DSNTIJRT without the PREVIEW option or customize and run the generated job.). UI25097 HOLD DATA END UI25153 HOLD DATA START ++ HOLD(UI25153) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15044) COMMENT (***Action for PI32837: See PI32837 APAR/PTF text for additional information about why a REBIND is necessary. PI32837 corrects a bind time problem that can cause an incorrect result set to be returned for queries that contain the CORRELATION built-in aggregate 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.). UI25153 HOLD DATA END UI25213 HOLD DATA START ++ HOLD(UI25213) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15048) COMMENT (***Action for PI32543: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PI32543 corrects a problem in which DB2 may overestimate the cost of a non-correlated EXISTS subquery. This may lead to an inefficient access path 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.). UI25213 HOLD DATA END UI25236 HOLD DATA START ++ HOLD(UI22717) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15048) COMMENT (***Action for PI15740: A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PI15740 corrects a problem where incorrect output can occur for a query which uses a range list access path and contains multiple range predicates on the same column in the same direction. 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.). UI25236 HOLD DATA END UI25417 HOLD DATA START ++ HOLD(UI25417) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15055) COMMENT (See PI32067 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. Apar PI32067 corrects the conditions that can lead to the occurrence of an ABEND04E RC00C90101 at DSNICMAT ERQUAL5006 for select using scrollable sensitive cursor with 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.). UI25417 HOLD DATA END UI25546 HOLD DATA START ++ HOLD(UI25546) SYS FMID(HIZAA10) REASON(ACTION) DATE(15058) COMMENT (This PTF updates the ERLY code. After you apply this fix, you must either re-IPL z/OS with CLPA, or issue the -REFRESH DB2 command. The procedure for issuing the REFRESH command is: 1. Issue -STOP DB2 2. Issue MODIFY LLA,REFRESH 3. Issue -REFRESH DB2,EARLY). UI25546 HOLD DATA END UI25546 HOLD DATA START ++ HOLD(UI24916) SYS FMID(HIZAA10) REASON(ACTION) DATE(15058) COMMENT (This PTF updates the ERLY code. After you apply this fix, you must either re-IPL z/OS with CLPA, or issue the -REFRESH DB2 command. The procedure for issuing the REFRESH command is: 1. Issue -STOP DB2 2. Issue MODIFY LLA,REFRESH 3. Issue -REFRESH DB2,EARLY). UI25546 HOLD DATA END UI25546 HOLD DATA START ++ HOLD(UI21178) SYS FMID(HIZAA10) REASON(ACTION) DATE(15058) 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.). UI25546 HOLD DATA END UI25546 HOLD DATA START ++ HOLD(UI18945) SYS FMID(HIZAA10) REASON(ACTION) DATE(15058) COMMENT (***Action for PI18566: 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.). UI25546 HOLD DATA END UI25546 HOLD DATA START ++ HOLD(UI18548) SYS FMID(HIZAA10) REASON(ACTION) DATE(15058) COMMENT (This PTF updates the ERLY code. After you apply this fix, you must either re-IPL z/OS with CLPA, or issue the -REFRESH DB2 command. The procedure for issuing the REFRESH command is: 1. Issue -STOP DB2 2. Issue MODIFY LLA,REFRESH 3. Issue -REFRESH DB2,EARLY). UI25546 HOLD DATA END UI25563 HOLD DATA START ++ HOLD(UI25563) SYS FMID(HDBAA10) REASON(DOC) DATE(15061) COMMENT (***Documentation for PI31597: See PI31597 APAR/PTF text for additional information. Documentation changes are required to describe the function provided by this APAR. The product documentation contained in the DB2 for z/OS Knowledge Center will be updated at a later date. Changes to the DB2 10 and DB2 11 Messages Manuals. --------------------------------------------------------------- The explanation text of messages DSNL004I and DSNL302I is changed: * The description of the BNDOPT value of the PKGREL DDF processing option is changed to have an additional paragraph as follows: When a connection which has used a high performance DBAT terminates, the high performance DBAT will also be deallocated. * A description of a new PKGREL DDF processing option value, BNDPOOL, is added as follows: BNDPOOL DDF processing for the BNDPOOL option value will be the same as the BNDOPT option value with the following exception: when a connection which has used a high performance DBAT terminates, the high performance DBAT will be pooled. The pooled DBAT can then be used by any other connection which presents a request to this DB2 subsystem. Changes to the DB2 10 and DB2 11 Command Reference manuals. --------------------------------------------------------------- The description of the PKGREL option of the MODIFY DDF command is replaced with the following: PKGREL Specifies whether DB2 honors the bind options of packages that are used for remote client processing. The PKGREL option also controls the disposition of high performance DBATs when a client connection which has used a high performance DBAT has terminated. BNDOPT The rules of the RELEASE bind option that was specified when the package was bound are applied to any package that is used for remote client processing. The high performance DBAT used by a terminating client connection will be deallocated. BNDOPT is the default value of the MODIFY DDF PKGREL command. BNDPOOL The rules of the RELEASE bind option that was specified when the package was bound are applied to any package that is used for remote client processing. The high performance DBAT used by terminating a client connection will be pooled. COMMIT The rules of the RELEASE(COMMIT) bind option are applied to any package that is used for remote client processing. COMMIT is the default value when the CMTSTAT subsystem parameter is set to ACTIVE. If the MODIFY DDF PKGREL command had never been issued, then COMMIT is the default value. No high performance DBATs exist while the PKGREL option is set to COMMIT. The usages notes of the -MODIFY DDF command is changed as follows: When to use PKGREL options: You can specify that DDF uses the PKGREL(BNDOPT) or PKGREL(BNDPOOL) option during normal production operating hours. For environments where client configurations control the use of packages which were bound with the RELEASE(DEALLOCATE) bind option, BNDOPT will be the preferred value of the PKGREL option. For environments where any client is capable of using packages bound with the RELEASE(DEALLOCATE) bind option, BNDPOOL will be the preferred value of the PKGREL option. Either option value offers improved performance by reducing the amount of CPU costs for allocating and deallocating packages. However, packages that run under the rules of the RELEASE(DEALLOCATE) bind option are likely to remain allocated and prevent maintenance activities such as objects modifications and bind operations. Consequently, you can use the PKGREL(COMMIT) option during routine and emergency maintenance periods.). UI25563 HOLD DATA END UI25563 HOLD DATA START ++ HOLD(UI25563) SYS FMID(HDBAA10) REASON(ACTION) DATE(15061) COMMENT (***Action for PI31597: For users which operate DDF with PKGREL(BNDOPT) option, as shown by a -DISPLAY DDF command, this APAR may be of interest to you. High Performance DBAT processing is activated when the PKGREL value is set to BNDOPT. APAR PI20352 changed the behavior of High Performance DBAT processing. This APAR, PI31597, will restore the prior (to PI20352) behavior of High Performance DBAT processing. For those users which desire the High Performance DBAT processing behavior to be as provided by APAR PI20352, a -MODIFY DFF PKGREL(BNDPOOL) command can now be issued. See PTF cover letter for more information.). UI25563 HOLD DATA END UI25614 HOLD DATA START ++ HOLD(UK70914) SYS FMID(HDBAA10) REASON(ACTION) DATE(15062) COMMENT (***Action for PM41805: See PM41805 APAR/PTF text for additional information about why further ACTION might be necessary. PM41805 corrects a problem that can occur when DATETIME arithmetic returns a NULL value. However, DB2 does not zero out the data part of the result and can leave it containing "bad" data. Eventually, this can cause an incorrect result to be returned. For the recreate example described in the apar PM41805 text, if the incorrect data already exists in the database, the following simple example (steps) might be helpful in correcting it. 1. Drop the index which has the LAST_DATE column as one of the index keys. DROP INDEX I1; COMMIT; 2. Apply the PTF for apar PM41805 and recycle DB2. 3. Rerun the UPDATE statement which causes the incorrect result. UPDATE T1 SET LAST_DATE = LAST_DATE - ((DAYS('07/20/2010') - DAYS('05/28/2009'))DAYS); 4. Recreate the dropped index. CREATE INDEX I1 ON T1 (IDN ASC, LAST_DATE ASC); COMMIT; 5. Execute the SELECT statement (again) which uses index access and references the LAST_DATE column as a stage 1 predicate. SELECT * FROM T1 WHERE IDN = 2 AND LAST_DATE IS NULL; After the application of apar PM41805 and re-running the UPDATE statement in Step 3., a correct NULL value will correct/replace the "bad" null value in the table. Once the same index is recreated, and the LAST_DATE column is referenced as a stage 1 predicate in the SELECT query, a correct result will be returned.). UI25614 HOLD DATA END UI25616 HOLD DATA START ++ HOLD(UI25616) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15063) COMMENT (***Action for PI33252: See PI33252 APAR/PTF text for additional information about why a REBIND is necessary. PI33252 corrects a problem that incorrect output could happen for a static cursor UPDATE SQL statement whose right hand side of the SET clause is an expression containing a correlated scalar-fullselect. 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.). UI25616 HOLD DATA END UI25778 HOLD DATA START ++ HOLD(UI24478) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15069) COMMENT (***Action for PI28863: See PI28863 APAR/PTF text for additional information about why a REBIND is necessary. PI28863 corrects a problem of SQLCODE504 from DSNXEPP is received during a remote rebind for an UPDATE WHERE CURRENT OF statement with an invalid cursor and the statement was bound successfully with SQLERROR(CONTINUE). 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.). UI25778 HOLD DATA END UI25778 HOLD DATA START ++ HOLD(UI25016) SYS FMID(HDBAA10) REASON(ACTION) DATE(15069) COMMENT (***Action for PI28996: PI28996 APAR/PTF is only for DB2 query acceleration to an accelerator. After the PTF for PI28996 is applied, when using QUERY ACCELERATION ELIGIBLE or ALL behaviors for certain queries that have never before run 'only' in DB2, DB2 may now validly fail those queries with existing -SQLCODEs during PREPARE or bind time if those queries fail the existing DB2 semantic error checking. Prior to this APAR, the 'full' DB2 semantic error checking was not done for accelerated queries when using QUERY ACCELERATION ELIGIBLE or ALL behaviors. Please see APAR/PTF closing text for more information.). UI25778 HOLD DATA END UI25837 HOLD DATA START ++ HOLD(UI25072) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15070) COMMENT (***Action for PI31373: See PI31373 APAR/PTF text for additional information about why a REBIND is necessary. PI31373 corrects a problem of SQLCODE -302, when all of following conditions are satisfied: 1. A host structure and an extended indicator array are defined in host programming language; 2. one of the fields in the above host structure is defined with DECIMAL type; 3. both of the above structures are used in an SQL statement; 4. the table referenced in the above SQL statement is a remote table and locates on a federation server. 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.). UI25837 HOLD DATA END UI25853 HOLD DATA START ++ HOLD(UI12045) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15070) COMMENT (***Action for PM95113: See PM95113 APAR/PTF text for additional information about why a REBIND is necessary. PM95113 improves the performance of query that contains a correlated IN or EXISTS subquery predicate with a field procedure on the correlated predicate 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.). UI25853 HOLD DATA END UI25853 HOLD DATA START ++ HOLD(UK80840) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15070) COMMENT (***Action for PM67966: See PM67966 APAR/PTF text for additional information about why a REBIND is necessary. PM67966 corrects a problem that can result in an incorrect result set, more rows than expected, being returned for a query that contains a correlated subquery for an EXISTS predicate, and there is a unique index on expression defined on the table that is referenced in the correlated 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.). UI25853 HOLD DATA END UI25853 HOLD DATA START ++ HOLD(UI25853) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15070) COMMENT (***Action for PI33994: See PI33994 APAR/PTF text for additional information about why a REBIND is necessary. PI33994 corrects a problem of ABEND0C4 RC00000038 at location DSNXGRDS DSNXOSCR OFFSET345E or ABEND04E RC00C90101 at location DSNIDM DSNICUBC OFFSET5015 when a SQL statement contains SENSITIVE STATIC SCROLL CURSOR and EXISTS or IN predicate with correlated 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.). UI25853 HOLD DATA END UI25960 HOLD DATA START ++ HOLD(UI25960) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15074) COMMENT (***Action for PI31707: See PI31707 APAR/PTF text for additional information about why a REBIND is necessary. PI31707 corrects a problem that DB2 may give incorrect output for a query with a table function where there is only 1 limited key on the partition table and there is an equal predicate between the partition limit key and a table function parameter. 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.). UI25960 HOLD DATA END UI25960 HOLD DATA START ++ HOLD(UI22684) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15074) COMMENT (***Action for PI26227: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PI26227 corrects a problem in which incorrect output may be generated for a query with an IS NULL predicate on a partitioning 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.). UI25960 HOLD DATA END UI25987 HOLD DATA START ++ HOLD(UI20621) SYS FMID(HDBAA10) REASON(ACTION) DATE(15076) COMMENT (PI19842 corrects a problem of ABEND04E RC00E70005 at location DSNXGRDS DSNXSTRC M777 or other unexpected ABEND0C4 or ABEND04E or unexpected SQLCODE -104 during rebind when a compiled SQL scalar function contains a RETURN statement which returns an expression with special register CURRENT SERVER, CURRENT PACKAGESET or CURRENT PACKAGE PATH. 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, compiled SQL scalar function could be affected by this change.). UI25987 HOLD DATA END UI26059 HOLD DATA START ++ HOLD(UK71432) SYS FMID(HDBAA10) REASON(ACTION) DATE(15077) COMMENT (The DB2 10 for z/OS PTF for APAR PM37479 has modified the REORG utility to use the maximum record length for tables that are compressed and defined with MAXROWS 1 and the KEEPDICTIONARY keyword is NOT specified on the utility statement. This change will usually require additional sort work space for the data sort.). UI26059 HOLD DATA END UI26201 HOLD DATA START ++ HOLD(UK78526) SYS FMID(JDBAA12) REASON(ACTION) DATE(15082) COMMENT (***Action for PM58951 ____________________________________________________________ This PTF contains a fix for a defect in the SQLJ translator. Incorrect code was generated for an SQL SELECT INTO statement using a common table expression specified with the "WITH" keyword. To make this fix effective for any impacted SQLJ program, after applying the APAR, you must re-translate the SQLJ program, then you must run the SQLJ Binder (db2sqljbind) again on the affected profiles and rebind the profiles. See PM58951 APAR/PTF text in the PTF cover letter for further information about this problem. Reference entry with (wsdbu01043533) identifier.). UI26201 HOLD DATA END UI26208 HOLD DATA START ++ HOLD(UK78529) SYS FMID(JDBAA12) REASON(ACTION) DATE(15082) COMMENT (***Action for PM58952 ____________________________________________________________ This PTF contains a fix for a defect in the SQLJ translator. Incorrect code was generated for an SQL SELECT INTO statement using a common table expression specified with the "WITH" keyword. To make this fix effective for any impacted SQLJ program, after applying the APAR, you must re-translate the SQLJ program, then you must run the SQLJ Binder (db2sqljbind) again on the affected profiles and rebind the profiles. See PM58952 APAR/PTF text in the PTF cover letter for further information about this problem. Reference entry with (wsdbu01043533) identifier.). UI26208 HOLD DATA END UI26240 HOLD DATA START ++ HOLD(UI26240) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15083) COMMENT (See Pi34186 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. APAR Pi34186 corrects a problem that can cause poor performance for query contains LIKE predicate on unicode 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.). UI26240 HOLD DATA END UI26243 HOLD DATA START ++ HOLD(UI13992) SYS FMID(HDBAA10) REASON(DOC) DATE(15083) COMMENT (PM95281 introduces the following changes to externals: Changed messages or codes: For SQLCODE -20523, the following explanation is updated under reason code 1: Explanation: .... 1 The table must not be any of the following tables: * System-period temporal table. The table was defined as a system-period temporal table by a previous statement, or the current statement defines the table as a system period temporal table. Changed option for SQL statement ALTER TABLE: Under ALTER TABLE USE HISTORY TABLE history-table-name, the following restriction is updated: ... history-table name must identify a table that exists at the current server and must not identify one of the following tables: ... - a system-period temporal table. The table was defined as a system-period temporal table by a previous statement, or the current statement defines the table as a system-period temporal table (SQLSTATE 428HX, SQLCODE -20523 with reason code 1).). UI26243 HOLD DATA END UI26243 HOLD DATA START ++ HOLD(UI13992) SYS FMID(HDBAA10) REASON(ACTION) DATE(15083) COMMENT (This PTF corrected a problem where DB2 did not restrict the use of versioning table properly. This allowed invalid tables to be used as the history table. Applying the PTF however does not fix existing problems and user will need to take the following action to correct the problem on their system. Please use a query to identify any tables that use themselves as their own history table. The following is a sample query: SELECT CREATOR, NAME, TYPE, DBNAME, TSNAME, DBID, OBID, COLCOUNT, CREATEDTS, ALTEREDTS, RELCREATED, VERSIONING_SCHEMA, VERSIONING_TABLE FROM SYSIBM.SYSTABLES WHERE CREATOR = VERSIONING_SCHEMA AND NAME = VERSIONING_TABLE; After the tables are identified, please run an ALTER TABLE DROP VERSIONING statement on each of the tables to correct the self referencing problem. However the data of the affected tables can still be down level and incorrect. User will need to examine the data to see if any other correction is necessary.). UI26243 HOLD DATA END UI26306 HOLD DATA START ++ HOLD(UI24527) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15085) COMMENT (See PI29102 APAR/PTF text for additional information about why a REBIND is necessary. PI29102 corrects a problem for SQL query acceleration where the query to be offloaded is a self-referencing INSERT with sub-select. To make this fix effective for a static application containing accelerated static SQL queries, the application 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.). UI26306 HOLD DATA END UI26306 HOLD DATA START ++ HOLD(UI15443) SYS FMID(HDBAA10) REASON(ACTION) DATE(15085) COMMENT (*** Action for PI10774 *** Users should be aware that there MAY be operational issues as a result of this change because more remote application environments will have to stay active, after rollback processing, IF the application executes SQL that can be off-loaded to an accelerator. Basically, the operational impact of this change occurs as a result of requiring an increased number of DB2 threads (DBATs) and remote client system network connections (to the DB2 server) in order to service the same amount of remote application work. Possible symptoms may include: . Increased storage utilization symptoms due to additional threads (DBATs) that may now be required. . Performance degradation, perhaps due to the queuing that may occur as a result of reaching or approaching the MAXDBAT resource limit. . High rate of unexpected "Idle Thread Time-out" conditions (message DSNL030I with reason code 00D3003B) that may result in unexpected remote application failures. . Remote client "connection concentrator" environments may also be adversely affected as a result of their inability to pool connections on a transaction (rollback) boundary. Any potential exposure to these symptoms only occurs with respect to remote applications that access DB2, as a server, and then utilize an accelerator AND issue rollback. The rollback has an effect even if it is executed after accelerator access is complete. Since exposure is based on the execution of a rollback, which is not typical, it is unlikely that users will be affected, but it is possible. After applying this change, users should monitor their system for these symptoms. The symptoms may be normal under the circumstances and measures may be necessary to address the condition.). UI26306 HOLD DATA END UI26326 HOLD DATA START ++ HOLD(UK73300) SYS FMID(HDBAA10) REASON(ACTION) DATE(15086) COMMENT (The ++HOLD and action is for DB2 users running in z/OS 1.12 or higher only. See PM46045 APAR/PTF text for additional information. The following summarizes the interactions of DB2 APARs PM17542, PM46045, and z/OS APARs OA36101, OA37361, and OA37362. It is intended to help you determine when to apply DB2 and z/OS APARs, and in what order. 1. PM17542 only - exposed to overlay and missing SMF I/O counting at address space level. MEMDSENQMGMT irrelevent. 2. PM17542 with OA36101 - exposed to missing SMF I/O counting at address space level only. MEMDSENQMGMT irrelevent. 3. PM17542 with OA36101, OA37361 & OA37362 - no longer exposed to either problem. 4. PM17542 with PM46045 - S99DASUP setting now only turned on when MEMDSENQMGMT is enabled. Exposed to overlay and missing SMF I/O counting at address space level ONLY when MEMDSENQMGMT is ENABLED. 5. PM17542 with PM46045 & OA36101 - S99DASUP setting now only turned on when MEMDSENQMGMT is enabled. Exposed to missing SMF I/O counting at address space level ONLY when MEMDSENQMGMT is ENABLED. 6. PM17542 with PM46045, OA36101, OA37361 & OA37362 - no longer exposed to either problem. Performance benefits for both features introduced with PM17542 only available when MEMDSENQMGMT is ENABLED. Instructions on setting MEMDSENQMGMT to DISABLE or ENABLE: ---------------------------------------------------------- Work with your system programmer to complete one of the following actions: - Update the ALLOCxx parmlib member to set the SYSTEM MEMDSENQMGMT value. SYSTEM MEMDSENQMGMT(DISABLE) or SYSTEM MEMDSENQMGMT(ENABLE) - Issue system command SETALLOC SETALLOC SYSTEM,MEMDSENQMGMT=DISABLE or SETALLOC SYSTEM,MEMDSENQMGMT=ENABLE Note: 1. Updating the ALLOCxx parmlib is strongly recommended as it remains effective across IPLs. You may apply the PTF and update the ALLOCxx parmlib member at different times. However the benefit of the APAR will only be available after re-IPL with ALLOCxx changes or after issuing the SETALLOC command as described above. 2. If SETALLOC SYSTEM,MEMDSENQMGMT command is used, a DB2 restart is required to make the change effective. 3. System command D ALLOC,OPTIONS can be used to display the status of MEMDSENQMGMT in z/OS. Sample DISPLAY output: IEFA003I 15.35.15 ALLOC OPTIONS SPACE PRIMARY: 4 SECONDARY: 24 : SYSTEM IEFBR14_DELMIGDS: LEGACY TAPELIB_PREF: EQUAL REMIND_INTV: 90 VERIFY_UNCAT: FAIL TEMPDSFORMAT: INCLUDELABEL MEMDSENQMGMT: ENABLE). UI26326 HOLD DATA END UI26382 HOLD DATA START ++ HOLD(UI26188) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15089) COMMENT (***Action for PI35715: PI35715 corrects a problem that incorrect output, ABEND04E RC00E70005 at the location DSNXGRDS DSNXGMVE M628, or other unexpected problems could happen when rebinding a native SQL procedure with the option WITH EXPLAIN and EXPLAIN tables in older format. To make this fix effective, the package of the native SQL procedure, which has been rebound with the option WITH EXPLAIN and EXPLAIN tables in older format, must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, native SQL procedure could be affected by this change.). UI26382 HOLD DATA END UI26382 HOLD DATA START ++ HOLD(UI26382) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15089) COMMENT (***Action for PI30859: See PI30859 APAR/PTF text for additional information about why a REBIND is necessary. PI30859 enables new function for execution on an accelerator. To make this new function effective for a static application containing accelerated static SQL queries, the application 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.). UI26382 HOLD DATA END UI26404 HOLD DATA START ++ HOLD(UI26404) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15090) COMMENT (See PI30376 APAR/PTF text for additional information about why a REBIND is necessary. PI30376 1. Enable offloading of a Temporal query to an accelerator. Only Business Time is supported. 2. Block offloading to an accelerator when the length of nchar/nvarchar is > 16000 in the converted query. SQLCODE -4742 with reason code = 11 will be issued. 3. Fix SQLCODE -904 "Invalid SQL Statement" for an offloading query which includes minus literal string. For example, SELECT * FROM T1 WHERE C2 IN ('-9','-1') 4. Fix abend 04E-00E70005 DSNXOMSF:P170. After the fix, SQLCODE -4742 with reason code = 11 is issued for an unsupported statement that contains MEDIAN. To make this fix effective for a static application containing accelerated static SQL queries, the application 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.). UI26404 HOLD DATA END UI26404 HOLD DATA START ++ HOLD(UI23690) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15090) COMMENT (***Action for PI29176: See PI29176 APAR/PTF text for additional information about why a REBIND is necessary. PI29176 corrects a problem for SQL query acceleration when there are errors in the DB2 modified query text that DB2 routes to an accelerator. To make this fix effective for a static application containing accelerated static SQL queries, the application 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.). UI26404 HOLD DATA END UI26404 HOLD DATA START ++ HOLD(UI22766) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15090) COMMENT (***Action for PI27501: See PI27501 APAR/PTF text for additional information about why a REBIND is necessary. PI27501 corrects a problem for SQL query acceleration where an SQL query is incorrectly blocked from routing to an accelerator. To make this fix effective for a static application containing accelerated static SQL queries, the application 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.). UI26404 HOLD DATA END UI26404 HOLD DATA START ++ HOLD(UI22157) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15090) COMMENT (***Action for PI26089: See PI26089 APAR/PTF text for additional information about why a REBIND is necessary. PI26089 corrects a problem for SQL query acceleration where there are errors in the DB2 modified query text that DB2 routes to an accelerator. To make this fix effective for a static application containing accelerated static SQL queries, the application 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.). UI26404 HOLD DATA END UI26404 HOLD DATA START ++ HOLD(UI23245) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15090) COMMENT (***Action for PI25743: See PI25743 APAR/PTF text for additional information about why a REBIND is necessary. PI25743 corrects a problem an ABEND04E RC00E72018 at DSNXSORI OFFSET 5E02 may occur when a sort-merge join query with merge-composite runs on parallel and the join predicate has different nullability, data type, or data length. 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.). UI26404 HOLD DATA END UI26404 HOLD DATA START ++ HOLD(UI21946) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15090) COMMENT (***Action for PI22945: See PI22945 APAR/PTF text for additional information about why a REBIND is necessary. PI22945 corrects a problem for SQL query acceleration where there are errors in the DB2 modified query text that DB2 routes to an accelerator. To make this fix effective for a static application containing accelerated static SQL queries, the application 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.). UI26404 HOLD DATA END UI26404 HOLD DATA START ++ HOLD(UI26378) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15090) COMMENT (***Action for PI22413: See PI22413 APAR/PTF text for additional information about why a REBIND is necessary. PI22413 enables the following new functions for execution on an accelerator: (1) DECODE (2) LTRIM and RTRIM with two arguments (3) TRIM (4) NVL To make any of the new functions effective for a static application that contains accelerated static SQL queries, the application 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.). UI26404 HOLD DATA END UI26404 HOLD DATA START ++ HOLD(UI20151) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15090) COMMENT (***Action for PI20688: See PI20688 APAR/PTF text for additional information about why a REBIND is necessary. PI20688 corrects a problem for SQL query acceleration where there are errors in the DB2 modified query text that DB2 routes to an accelerator. To make this fix effective for a static application containing accelerated static SQL queries, the application 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.). UI26404 HOLD DATA END UI26404 HOLD DATA START ++ HOLD(UI17624) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15090) COMMENT (See PI14495 APAR/PTF text for additional information about why a REBIND is necessary. PI14495 corrects a problem for SQL query acceleration where there are errors in the DB2 modified query text that DB2 routes to an IBM DB2 Analytics Accelerator. To make this fix effective for a static application containing accelerated static SQL queries, the application 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.). UI26404 HOLD DATA END UI26404 HOLD DATA START ++ HOLD(UI19207) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15090) COMMENT (***Action for PI14368: See PI14368 APAR/PTF text for additional information about why a REBIND is necessary. PI14368 corrects a problem for SQL query acceleration where there are errors in the DB2 modified query text that DB2 routes to an accelerator. To make this fix effective for a static application containing accelerated static SQL queries, the application 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.). UI26404 HOLD DATA END UI26404 HOLD DATA START ++ HOLD(UI26404) SYS FMID(HDBAA10) REASON(DOC) DATE(15090) COMMENT (*************************************************************** The following messages and codes are changed in PI30376/PI30377/PI31445: *************************************************************** The message/code text shown here is approximate and is subject to change before it is published in the DB2 manuals. (a) New messages -4747: -4747 THE CREATE OR ALTER TABLE STATEMENT FAILED BECAUSE SOME FUNCTIONALITY WAS SPECIFIED IN THE TABLE DEFINITION THAT IS NOT SUPPORTED WITH THE TABLE TYPE. UNSUPPORTED FUNCTIONALITY: functionality-keyword. ...... (b) Changed messages -159 THE STATEMENT REFERENCES object-name WHICH IDENTIFIES AN actual-type RATHER THAN AN expected-type. A CREATE MASK or CREATE PERMISSION statement must name a base table that exists on the server. The table must not be a system-period temporal table or a history table or an accelerator-only table. (c) Changed messages -270 FUNCTION NOT SUPPORTED Explanation: ...... ALTER TABLE or CREATE TABLE statements: ..... A CREATE TABLE statement must not contain a LIKE clause and an IN ACCELERATOR clause. A CREATE TABLE statement must not contain a LIKE clause that references an accelerator-only table. A CREATE TABLE statement must not contain an AS fullselect clause and an IN ACCELERATOR clause. A CREATE TABLE statement must not contain an AS fullselect clause where the fullselect references an accelerator-only table. A CREATE TABLE statement must not contain a materialized query definition and an IN ACCELERATOR clause. A CREATE or ALTER TABLE statement must not define a Unicode column in an EBCDIC table if the table is an accelerator-only table. ...... CREATE MASK statements: A CREATE MASK statement must not define a mask for an accelerator-only table. CREATE PERMISSION statements: A CREATE PERMISSION statement must not define a column permission for an accelerator-only table. DECLARE GLOBAL TEMPORARY TABLE statements The LIKE clause of a DECLARE GLOBAL TEMPORARY TABLE must not reference an accelerator-only table. TRIGGER statements A CREATE [or ALTER] TRIGGER statement must not reference an accelerator-only table in the body of the trigger. A CREATE TRIGGER statement with INSTEAD OF clause specified must not reference a view that references an accelerator-only table in the body of the trigger. (d) Changed messages -350 column-name WAS IMPLICITLY OR EXPLICITLY REFERENCED IN A CONTEXT IN WHICH IT CANNOT BE USED Explanation: ....... A LOB column cannot be: Defined in an accelerator-only table. (e) Changed messages -637 DUPLICATE keyword-name KEYWORD OR CLAUSE. ...... IN ACCELERATOR can only be specified once. (f) Changed messages -650 THE ALTER STATEMENT CANNOT BE EXECUTED, REASON reason-code 28 ALTER TABLE is not allowed when the table is an accelerator-only table. 29 ALTER TABLESPACE is not allowed when the table space contains an accelerator-only table. (g) Changed messages -669 THE OBJECT CANNOT BE EXPLICITLY DROPPED. REASON reason-code ...... 0007 The DROP TABLESPACE statement or DROP DATABASE statement attempted to drop a table space or database that contains an accelerator-only table. (h) Changed messages -750 THE SOURCE TABLE source-name CANNOT BE RENAMED OR ALTERED AS SPECIFIED Possible reasons for this error include, but are not limited to: The table is an accelerator-only table. (i) Changed messages -766 THE OBJECT OF A STATEMENT IS A TABLE FOR WHICH THE REQUESTED OPERATION IS NOT PERMITTED ...... Accelerator-only table An accelerator-only table must not be specified in any of the following statements: 1. A CREATE SYNONYM statement to define a synonym. 2. A CREATE TRIGGER statement to define a trigger for the accelerator-only table. 3. A LOCK TABLE statement. 4. A CREATE INDEX (j) Changed messages -817 THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE THE STATEMENT WILL RESULT IN A PROHIBITED DATA CHANGE UPDATE OPERATION. Explanation: The application attempted to execute an SQL statement that would result in updates to user data or to the subsystem catalog. This is prohibited for one of the following reasons: ...... A CREATE TABLE statement for an accelerator-only table was fo followed by a data change operation for an accelerator-only table. (k) Changed messages -7008 object-name NOT VALID FOR OPERATION (reason-code) 17 A CREATE TRIGGER statement was issued, but the trigger cannot be created because an INSTEAD OF trigger cannot be defined on a view that has other views dependent on it, or the view references an accelerator-only table, or an AFTER trigger cannot be defined on a table that has a clone defined. (l) Changed messages -20058 THE FULLSELECT SPECIFIED FOR MATERIALIZED QUERY TABLE table-name IS NOT VALID. The fullselect cannot contain a reference to a created global temporary table, a declared global temporary table, an accelerator-only table, or another materialized query table. (m) Changed messages -20240 INVALID SPECIFICATION OF A SECURITY LABEL COLUMN table-name.column-name REASON CODE reason-code ...... 9 A security label column cannot be defined for an accelerator-only table. (n) Changed messages -20474 PERMISSION OR MASK CANNOT BE CREATED FOR THE object-name OBJECT OF THE object-type TYPE. REASON CODE reason-code. ...... 17 One of the following situations occurred: ...... An accelerator-only table 39 The definition references an accelerator-only table. (o) Changed messages -4742 THE STATEMENT CANNOT BE EXECUTED BY DB2 OR IN THE ACCELERATOR (REASON reason-code) ...... The statement cannot be executed by DB2 if any of the following conditions are true: ... The statement references an accelerator-only table. ...... The statement cannot be executed by an accelerator server if any condition that is indicated by a reason code is true. ...... 22 The statement references an accelerator-only table, but the statement is not offloadable. For example: MERGE statement references an accelerator-only table. UPDATE or DELETE statement references an accelerator-only table,but the target table of the UPDATE, DELETE is a normal table: UPDATE DB2_TABLE SET .. (SELECT .. FROM ACCEL_ONLY_TABLE). INSERT, UPDATE or DELETE of a row expression with subselect: UPDATE ACCEL_ONLY_TABLE SET (C1, C2) = (SELECT C3, C4 TABLE2); ...... Programmer response: Use the reason code to determine the cause of the failure, and take the appropriate action: …..... 22 Ensure the statement does not references an accelerator-only table, or replace the accelerator-only table with a DB2 table so that the query can be executed in DB2. (p) New warning message: +4748 +4748 THE statement STATEMENT FOR OBJECT object-name WAS SUCCESSFUL ON THE DB2 SERVER. THE OBJECT MAY NOT HAVE BEEN SUCCESSFULLY PROCESSED ON THE ACCELERATOR SERVER FOR REASON reason-code ...... *************************************************************** Monitoring and Accounting Changes: *************************************************************** The following is the new System Level Statistic Counters: Q8STOPNC Total accumulated number of SQL Open statements sent to IDAA by this DB2. Q8STINSC Total accumulated number of SQL Insert statements sent to IDAA by this DB2. Q8STUPDC Total accumulated number of SQL Update statements sent to IDAA by this DB2. Q8STDELC Total accumulated number of SQL Delete statements sent to IDAA by this DB2. Q8STDRPC Total accumulated number of SQL Drop statements sent IDAA by this DB2. Q8STCRTC Total accumulated number of SQL Create statements sent IDAA by this DB2. Q8STCMTC Total accumulated number of SQL Commit statements sent to IDAA by this DB2. Q8STRBKC Total accumulated number of SQL Rollback statements sent to IDAA by this DB2. The new accounting fields (thread level): Q8ACOPNC Total accumulated number of SQL Open statements sent to IDAA by this thread. Q8ACINSC Total accumulated number of SQL Insert statements sent to IDAA by this thread. Q8ACUPDC Total accumulated number of SQL Update statements sent to IDAA by this thread. Q8ACDELC Total accumulated number of SQL Delete statements sent to IDAA by this thread. Q8ACDRPC Total accumulated number of SQL Drop statements sent to IDAA by this thread. Q8ACCRTC Total accumulated number of SQL Create statements sent to IDAA by this thread. Q8ACCMTC Total accumulated number of SQL Commit statements sent to IDAA by this thread. Q8ACRBKC Total accumulated number of SQL Rollback statements sent to IDAA by this thread. Q8ACROWI Total accumulated number of rows inserted to IDAA by this thread. Q8ACROWU Total accumulated number of rows updated on IDAA by this thread. Q8ACROWD Total accumulated number of rows deleted on IDAA by this thread. Q8ACROWC Total accumulated number of rows returned by IDAA to this thread. Statement level: For query processing, the following statement level counters are defined (see Monitoring Counters Exchanged With DB2). The semantics and values for those counters are slightly different for INSERT/UPDATE/DELETE statements: QW0316AROW - always set to 0 because DML statements return no result set. QW0316ABYT - always set to 0 because DML statements return no result set. QW0316TW1R - always set to 0 because DML statements have no such wait times. QW0316TWDB2 - always set to 0 because DML statements have no such wait times.). UI26404 HOLD DATA END UI26404 HOLD DATA START ++ HOLD(UI12631) SYS FMID(HDBAA10) REASON(ACTION) DATE(15090) COMMENT (Impacted packages must be DROP and CREATE'd or ALTER REGENERATE'd after applying this PTF. To find potentially impacted packages, this query may be used: SELECT DISTINCT PKG.COLLID, PKG.NAME, HEX(PKG.CONTOKEN) FROM SYSIBM.SYSPACKSTMT AS PKS, SYSIBM.SYSPACKAGE AS PKG WHERE LENGTH(PKS.STMT) > 3499 AND PKG.TYPE = 'N' AND PKG.COLLID = PKS.COLLID AND PKG.NAME=PKS.NAME AND PKG.CONTOKEN = PKS.CONTOKEN; Please note that the query results may identify false positives. That is, not all of the packages identified may be impacted. Alternatively, one may opt to only attempt the ALTER REGENERATE or DROP and CREATE after encountering the -104 during REBIND.). UI26404 HOLD DATA END UI26404 HOLD DATA START ++ HOLD(UK96950) SYS FMID(HDBAA10) REASON(ACTION) DATE(15090) COMMENT (The query provided in the ++HOLD of PM78210/UK90728 has been updated to exclude views. 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 PM78210 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). The sample query may not take into account all customer environments and scenarios. The query output should be analyzed to verify the table spaces returned are in BRF and contain tables with inline LOB columns If so, the table spaces must be dropped and recreated after PM78210 has been 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 AND TAB.TSNAME = TPT.TSNAME AND COL.LENGTH > 4 AND TPT.FORMAT = ' ' AND TAB.TYPE <> 'V' );). UI26404 HOLD DATA END UI26404 HOLD DATA START ++ HOLD(UK90728) SYS FMID(HDBAA10) REASON(ACTION) DATE(15090) 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 AND TAB.TSNAME = TPT.TSNAME AND COL.LENGTH > 4 AND TPT.FORMAT = ' ' );). UI26404 HOLD DATA END UI26404 HOLD DATA START ++ HOLD(UI17690) SYS FMID(HDBAA10) REASON(ACTION) DATE(15090) COMMENT (***Action for PI16184: See PI16184 APAR/PTF text for additional information about why a REBIND is necessary. PI16184 corrects a problem for SQL query acceleration where there are errors in the DB2 modified query text that DB2 routes to an IBM DB2 Analytics Accelerator. To make this fix effective for a static application containing accelerated static SQL queries, the application 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.). UI26404 HOLD DATA END UI26406 HOLD DATA START ++ HOLD(UI19105) SYS FMID(HDBAA10) REASON(DOC) DATE(15090) COMMENT (*** Documentation for PI17163 *** The following new reason code is added to the DB2 Codes manual. 00D34451 Explanation: A remote application connected to a DB2 server and issued an SQL statement that contains a three-part name or alias that refers to a table or view at a remote location. However, the remote reference actually resolves to the local DB2 server location. This situation can occur when the SQL statement refers to a location alias but the package for the application was bound before the location alias was defined. System action: The SQL statement is rejected. User response: Bind the application package at the server after the location alias has been defined. For a data sharing group, ensure that the location alias is defined on all members. Problem determination: Use the -DISPLAY DDF command to determine the location aliases for the server. Ensure that any location alias references in the application exist at the server when the package is bound.). UI26406 HOLD DATA END UI26665 HOLD DATA START ++ HOLD(UI26665) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15099) COMMENT (***Action for PI34271: See PI34271 APAR/PTF text for additional information about why a REBIND is necessary. PI34271 corrects a problem that incorrect output could happen for an SQL statement that satisfies all of the following conditions: 1. It contains an UNION, EXCEPT or INTERSECT in the top level of the statement. 2. A column in the select list contains a field procedure. 3. The field-decoding length is bigger than the column length. 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.). UI26665 HOLD DATA END UI26764 HOLD DATA START ++ HOLD(UI26764) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15105) COMMENT (***Action for PI29894: See PI29894 APAR/PTF text for additional information about why a REBIND is necessary. PI29894 corrects a problem for SQL query acceleration where there are errors when routed to an accelerator. To make this fix effective for a static application containing accelerated static SQL queries, the application 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.). UI26764 HOLD DATA END UI26785 HOLD DATA START ++ HOLD(UI26785) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15105) COMMENT (***Action for PI32931: See PI32931 APAR/PTF text for additional information about why a REBIND is necessary. PI32931 corrects a problem of an ABEND04E RC00E70005 in DSNXRDOU:P011 or DSNXRDEC:P005. 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.). UI26785 HOLD DATA END UI26847 HOLD DATA START ++ HOLD(UK77712) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15109) 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 rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UI26847 HOLD DATA END UI26847 HOLD DATA START ++ HOLD(UK72345) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15109) COMMENT (***Action for PM43934: See PM43934 APAR/PTF text for additional information about why a REBIND is necessary. PM43934 corrects a problem that can lead to an ABEND04E RC00E70005 at location DSNXESX2:P404. 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.). UI26847 HOLD DATA END UI26847 HOLD DATA START ++ HOLD(UK66763) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15109) COMMENT (***Action for PM28718: See PM28718 APAR/PTF text for additional information about why a REBIND is necessary. PM28718 corrects a problem of incorrect output when running a query which SELECT from MERGE when the UPDATE clause is in row expression format and the MERGE contains an INCLUDE 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.). UI26847 HOLD DATA END UI26847 HOLD DATA START ++ HOLD(UI26847) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15109) COMMENT (***Action for PI32550: See PI32550 APAR/PTF text for additional information about why a REBIND is necessary. PI32550 corrects a problem for a query that contains a column define with a field procedure(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.). UI26847 HOLD DATA END UI26847 HOLD DATA START ++ HOLD(UI22950) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15109) COMMENT (***Action for PI26285: See PI26285 APAR/PTF text for additional information about why a REBIND is necessary. This apar corrects a problem that can cause DB2 to return an incorrect result, more rows than expected. The problem can occur for a query that includes a large indexable IN-List with many NULL elements that DB2 does not process properly. 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.). UI26847 HOLD DATA END UI27070 HOLD DATA START ++ HOLD(UI27070) SYS FMID(HDBAA10) REASON(ACTION) DATE(15116) COMMENT (The PTF for APAR PI33465 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.). UI27070 HOLD DATA END UI27120 HOLD DATA START ++ HOLD(UI27120) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15117) COMMENT (***Action for PI35773: A rebind is necessary for static applications to exercise the code changes implemented by this APAR. PI35773 corrects a problem of ABEND04E RC00E70005 AT DSNXORGL P110 when using a TEMPORAL table with RANGE LIST ACCESS TYPE. 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.). UI27120 HOLD DATA END UI27208 HOLD DATA START ++ HOLD(UI27208) SYS FMID(HDBAA10) REASON(ACTION) DATE(15120) COMMENT (Run the following query: SELECT SUBSTR(R.SCHEMA||'.'||R.NAME,1,36) TRIGGER , SUBSTR(R.TBOWNER||'.'||R.TBNAME,1,36) TABLE , SUBSTR(R.OWNER,1,10) OWNER FROM SYSIBM.SYSTRIGGERS R WHERE NOT EXISTS(SELECT 1 FROM SYSIBM.SYSTABAUTH TA WHERE R.TBNAME = TA.TTNAME AND R.TBOWNER = TA.TCREATOR AND ( (R.OWNER = TA.GRANTEE AND R.OWNERTYPE = TA.GRANTEETYPE) OR (TA.GRANTEETYPE = ' ' AND TA.GRANTEE = 'PUBLIC') ) AND TA.SELECTAUTH <> ' ' ) AND NOT EXISTS(SELECT 1 FROM SYSIBM.SYSUSERAUTH UA WHERE ( (R.OWNERTYPE = UA.GRANTEETYPE AND R.OWNER = UA.GRANTEE) OR (UA.GRANTEETYPE = ' ' AND UA.GRANTEE = 'PUBLIC') ) AND (UA.SYSADMAUTH <> ' ' OR UA.DATAACCESSAUTH <> ' ' ) ) AND NOT EXISTS(SELECT 1 FROM SYSIBM.SYSDBAUTH DA , SYSIBM.SYSTABLES T WHERE R.TBNAME = T.NAME AND R.TBOWNER = T.CREATOR AND T.DBNAME = DA.NAME AND ( (R.OWNERTYPE = DA.GRANTEETYPE AND R.OWNER = DA.GRANTEE) OR (DA.GRANTEETYPE = ' ' AND DA.GRANTEE = 'PUBLIC') ) AND DA.DBADMAUTH <> ' ' ) AND XMLEXISTS( 'if (fn:matches($s,"REFERENCING +OLD ","i")) then(1) else if (fn:matches($s,"REFERENCING +NEW ","i")) then(1) else if (fn:matches($s,"REFERENCING +OLD_TABLE ","i")) then(1) else if (fn:matches($s,"REFERENCING +NEW_TABLE ","i")) then(1) else ()' PASSING XMLCAST(R.STATEMENT AS XML) as "s"); To make this fix effective, after application of this PTF, for each qualified trigger from the above query, issue REBIND TRIGGER PACKAGE command without the EXPLAIN(ONLY) specification to its trigger package.). UI27208 HOLD DATA END UI27526 HOLD DATA START ++ HOLD(UI27526) SYS FMID(HDBAA10) REASON(ACTION) DATE(15131) COMMENT (DB2 is modified to make all new catalog indexes DB2-managed and be created in the DB2 catalog data space. Applying this PTF does not convert existing user-created catalog indexes. In DB2 10 enabling new-function mode and higher, these indexes must be dropped and recreated for them to function correctly. Please refer to APAR PI11316 for additional details. This PTF modifies the DB2 10 premigration check-out 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 10, run the updated customized job to check for conditions that might affect your migration. This PTF also modifies the DB2 11 premigration check-out job, DSNTIJPB. 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 DSNTIJPB - Delete all lines and copy in the DSNTIJPB 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 11, run the updated customized job to check for conditions that might affect your migration.). UI27526 HOLD DATA END UI27781 HOLD DATA START ++ HOLD(UI27781) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15139) COMMENT (***Action for PI39512: A rebind is necessary for static applications to exercise the code changes implemented by this APAR. PI39512 corrects a problem that can occur when modifying a temporal table via an UPDATE or DELETE operation when using 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.). UI27781 HOLD DATA END UI28096 HOLD DATA START ++ HOLD(UI28096) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15151) COMMENT (***Action for PI38584: A rebind is necessary for static applications to exercise the code changes implemented by this APAR. PI38584 corrects the following problems in V10: An ABEND0C7 RC00000000 at location DSNXGRDS DSNXRDTF OFFSET4192 can occur when executing a compiled SQL scalar function or native SQL procedure containing a Datetime arithmetic expression with a subtraction operator. An unexpected SQLCODE -407 can be issued when executing an SQL statement containing a Datetime arithmetic expression with a subtraction operator. Also, an incorrect result can be returned when executing an SQL statement containing a basic predicate or BETWEEN 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.). UI28096 HOLD DATA END UI28096 HOLD DATA START ++ HOLD(UI28096) SYS FMID(HDBAA10) REASON(ACTION) DATE(15151) COMMENT (PI38584 corrects the following problems in V10: An ABEND0C7 RC00000000 at location DSNXGRDS DSNXRDTF OFFSET4192 can occur when executing a compiled SQL scalar function or native SQL procedure containing a Datetime arithmetic expression with a subtraction operator. An unexpected SQLCODE -407 can be issued when executing an SQL statement containing a Datetime arithmetic expression with a subtraction operator. Also, an incorrect result can be returned when executing an SQL statement containing a basic predicate or BETWEEN predicate. To make the fix effective for a native SQL PL, ALTER PROCEDURE/ FUNCTION REGENERATE statement can be used to regenerate the SQL PL.). UI28096 HOLD DATA END UI28171 HOLD DATA START ++ HOLD(UI28171) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15154) COMMENT (--------------------------------------------------------------- (1) Run installation job DSNTIJRT to rebind DBRM DSNAEXP --------------------------------------------------------------- ==> This action is required for all DB2 V10 customers 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 DSNAEXP 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.DSNAEXP stored procedure.). UI28171 HOLD DATA END UI28171 HOLD DATA START ++ HOLD(UK66001) SYS FMID(HDBAA10) REASON(ACTION) DATE(15154) COMMENT (--------------------------------------------------------------- (1) Run installation job DSNTIJRT to rebind DBRM DSNAEXP --------------------------------------------------------------- ==> This action is required for all V10 customers 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 DSNAEXP 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.DSNAEXP stored procedure. ----------------------------------------------------------------). UI28171 HOLD DATA END UI28171 HOLD DATA START ++ HOLD(UK61994) SYS FMID(HDBAA10) REASON(ACTION) DATE(15154) COMMENT (--------------------------------------------------------------- (1) Run installation job DSNTIJRT to rebind DBRM DSNAEXP --------------------------------------------------------------- ==> This action is required for all DB2 V10 customers 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 DSNAEXP 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.DSNAEXP stored procedure.). UI28171 HOLD DATA END UI28270 HOLD DATA START ++ HOLD(UI28270) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15156) COMMENT (***Action for PI30015: A rebind is necessary for static applications to exercise the code changes implemented by this APAR. PI30015 corrects a problem of incorrect output may occur for an SQL statement using multiple outer joins with RIGHT JOIN or FULL OUTER 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.). UI28270 HOLD DATA END UI29293 HOLD DATA START ++ HOLD(UI19159) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15191) COMMENT (***Action for PI17479: See PI17479 APAR/PTF text for additional information about why a REBIND is necessary. PI17479 corrects a problem that incorrect output could happen for an SQL statement with a scalar-fullselect containing a table expression or view with UNION ALL, when the SELECT item of the scalar-fullselect is 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.). UI29293 HOLD DATA END UI29293 HOLD DATA START ++ HOLD(UI17994) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15191) COMMENT (***Action for PI13861: See PI13861 APAR/PTF text for additional information about why a REBIND is necessary. PI13861 corrects a problem of ABEND04E RC00E70005 at location DSNXGRDS DSNXSINE M103 or DSNXSZK M103 when a SELECT statement references SYSIBM.SYSDUMMY1, SYSIBM.SYSDUMMYE, SYSIBM.SYSDUMMYA, or SYSIBM.SYSDUMMU tables and contains OLAP specification. 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.). UI29293 HOLD DATA END UI29293 HOLD DATA START ++ HOLD(UI17944) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(15191) COMMENT (***Action for PI09729: See PI09729 APAR/PTF text for additional information about why a REBIND is necessary. PI09729 corrects a problem that ABEND04E RC00E70005 at the location DSNXGRDS DSNXRDEC P005, DSNXRSBC P041, DSNXRINT P026, DSNXRTIM P016 or DSNXRDOU P011 could happen for an SQL statement satisfying all of the following conditions: 1. It contains a table expression or view with UNION ALL. 2. It contains a predicate with a host variable or parameter marker. 3. The predicate is on a column from the table expression or view. 4. The columns of the predicate in the UNION branches have different nullabilties. 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.). UI29293 HOLD DATA END ****************************** * HOLDDATA REASON ID SUMMARY * ****************************** REASON ID TOTALS --------- ------ ACTION 72 DB2BIND 94 DOC 12 MULTSYS 6 ______________________________ READY END