1ACF0C038 ACF2 LOGONID ATTRIBUTES HAVE REPLACED DEFAULT USER ATTRIBUTES READY %HOLDDATA WDB2I.R.DB2101S.SMPPTS IKJ56247I FILE LNKLST NOT FREED, IS NOT ALLOCATED UK79840 HOLD DATA START ++ HOLD(UK79840) SYS FMID(HDBAA10) REASON(DOC) DATE(12178) COMMENT (Since the -DISPLAY DATABASE command could have displayed INCORROUT STATUS field values for the final summary line of partitioned objects any programs or processes that use that field need to understand that the only valid or relevant STATUS field values for the entire final summary line of partitioned object are: blank, DBETE or DBETE,RECP The DSNT397I message was updated to describe the final summary line for partitioned objects and their possible valid or relevant STATUS field values: blank, DBETE or DBETE,RECP.). ++ HOLD(UK66610) SYS FMID(HDBAA10) REASON(DOC) DATE(12178) COMMENT (The fix for APAR PM25652, introduces a new special option, RRTHashOvrFlwRatio. The Utility Guide and Reference and The Application Programming Guide documentation on DSNACCOX will have a new description of RRTHashOvrFlwRatio as follows: Syntax: -|---+---' '--------------+---|- +-RRTHashOvrFlwRatio-+ '- -1 ---------------' Description: RRTHashOvrFlwRatio Specifies a criterion for recommending that the REORG utility is run on a table space. If the following conditions are true, DSNACCOX recommends running REORG: - The hash access overflow index is being used for access. - The number of hash access overflow index entries divided by the total number of rows (expressed as a percentage) is greater than RRTHashOvrFlwRatio. RRTHashOvrFlwRatio is an input parameter of type CHAR(4). The default value is ' 15'. A plus sign (+) preceding or following the value, or a plus sign by itself indicates that the DSNACCOX stored procedure returns the calculated ratio value in the result set. A negative value indicates that the criterion is not used. The ratio of hash access overflow index entries to the total number of rows is returned in column RRTHashOvrFlwRatPCT of the result set. Figure 4. DSNACCOX formula for recommending a REORG on a table space has an additional criteria: (((QueryType='REORG' OR QueryType='ALL') AND 1 (ObjectType='TS' OR ObjectType='ALL')) AND (REORGLASTTIME IS NULL AND LOADRLASTTIME IS NULL) OR (NACTIVE IS NULL OR NACTIVE > 5) AND ((((REORGINSERTS x 100) / TOTALROWS > RRTInsertsPct) AND REORGINSERTS > RRTInsertsAbs) OR (((REORGDELETE x 100) / TOTALROWS > RRTDeletesPct) AND REORGDELETE > RRTDeleteAbs) OR (REORGCLUSTERSENS > 0 AND (REORGUNCLUSTINS x 100) / TOTALROWS > RRTUnclustInsPct) OR ((REORGDISORGLOB x 100) / TOTALROWS > RRTDisorgLOBPct) OR | (Object is not using hash organization and ((SPACE x 1024) / RRTDataSpaceRat)) OR ((REORGNEARINDREF+REORGFARINDREF) x 100) / TOTALROWS > RRTIndRefLimit) OR REORGMASSDELETE > RRTMassDelLimit OR EXTENTS > ExtentLimit) OR | ((QueryType='REORG' OR QueryType='ALL') AND | ObjectType='ALL' * AND | Overflow index hash access is used**, AND | ((overflow index TOTALENTRY x 100) / TOTALROWS > RRTHashOvrFlwRatio)) OR ((QueryType='RESTRICT' OR QueryType='ALL') AND (ObjectType='TS' OR ObjectType='ALL') AND The table space is in advisory or informational reorg pending status) |Note: | * ObjectType='ALL' must be specified to use this criteria. | for this criterion to be used. If ObjectType='TS' or | ObjectType='IX' is specified, and '+' is specified for | RRTHashOvrFlwRatio, an error message is issued. If | ObjectType='TS' or ObjectType='IX' is specified, and | '+' is not specified for RRTHashOvrFlwRatio, this | criterion is not used. | ** The overflow index is used when SYSINDEXES.HASH ='Y' AND | SYSINDEXSPACESTATS.REORGINDEXACCESS > 0. Add the following table entry to table for the "DSNACCOX OUTPUT" section. Result set row for second DSNACCOX result set ---------------------------------------------------------------- |Column name | Data type |Description | ---------------------------------------------------------------- |RRTHashOvrFlwRatPCT| Double |This is an optional column, | | | |and it is being returned when | | | |there is a plus (+) sign in | | | |the RRTHashOverFlwRatio. | | | | | | | |If OBJECTTYPE is ALL and REORG| | | |is YES, the ratio of Hash | | | |Access overflow index entries | | | |to the total number of rows, | | | |expressed as a percentage. | | | |Otherwise null. | 1 | | | | | | |If the ratio of Hash Access | | | |overflow index entries to the | | | |total number of rows does not | | | |exceed the value specified for| | | |RRTHashOvrFlwRatio or meet the| | | |criteria requirement, this | | | |value is null. | ----------------------------------------------------------------). ++ HOLD(UK65635) SYS FMID(HDBAA10) REASON(ACTION) DATE(12178) COMMENT ( ***Action for PM28652: See PM28652 APAR/PTF text for additional information. <<<<<<> Post-apply actions after applying the fix for APAR PM28652: (a) STOP PROC(SYSPROC.DSNACCOR) to suspend DSNACCOR activity (b) Rebind package for DSNACCOR. An example on how to do this is BIND PACKAGE(DSNACCOR) MEMBER(DSNACCOR) - 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.DSNACCOR) to resume DSNACCOR activity). ++ HOLD(UK66610) SYS FMID(HDBAA10) REASON(ACTION) DATE(12178) COMMENT (Post-apply actions after applying the fix for APAR PM25652: (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). UK79840 HOLD DATA END UK81128 HOLD DATA START ++ HOLD(UK81128) SYS FMID(HDBAA10) REASON(ACTION) DATE(12229) COMMENT (This PTF modifies the DB2 scheduler for administrative tasks so that a user can add a scheduler task using a PassTicket without the user ID under which the task execution is performed incurring a password violation. The following post-apply actions are required of all current DB2 10 for z/OS customers to activate the changes in this PTF: (1) Stop the DB2 scheduler for administrative tasks. (2) Start the DB2 scheduler for administrative tasks. Each of these actions is detailed below. (1) If the DB2 scheduler is running, stop it first by executing the following console command: MODIFY DSNADMT,APPL=SHUTDOWN where DSNADMT is the name of the scheduler. (2) To start the DB2 scheduler again, execute the following console command: 1 START DSNADMT where DSNADMT is the name of the scheduler.). UK81128 HOLD DATA END UK81181 HOLD DATA START UK81181 HOLD DATA END UK81198 HOLD DATA START UK81198 HOLD DATA END UK81336 HOLD DATA START UK81336 HOLD DATA END UK81866 HOLD DATA START ++ HOLD(UK70649) SYS FMID(HDBAA10) REASON(ACTION) DATE(12261) 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.). UK81866 HOLD DATA END UK81876 HOLD DATA START ++ HOLD(UK78035) SYS FMID(HDBAA10) REASON(ACTION) DATE(12261) COMMENT (BEFORE APPLYING THIS PTF, DYNAMIC SQLS COULD GET SQLCODE -905 OR BE PROCESSED WITHOUT SQLCODE -905 BASED ON THE ASUTIME FROM THE ROW WITH 'PUBLIC' LUNAME EVEN WHEN A MORE QUALIFIED ROW EXISTS IN DSNRLSTxx TABLE. AFTER APPLYING THIS PTF, DYNAMIC SQLS WOULD GET SQLCODE -905 OR BE PROCESSED WITHOUT SQLCODE -905 BASED ON THE ASUTIME FROM THE ROW WITH THE MOST QUALIFIED LUNAME COLUMN VALUE IN DSNRLSTxx TABLE.). UK81876 HOLD DATA END UK81892 HOLD DATA START UK81892 HOLD DATA END UK81895 HOLD DATA START 1UK81895 HOLD DATA END UK81897 HOLD DATA START UK81897 HOLD DATA END UK81929 HOLD DATA START UK81929 HOLD DATA END UK81974 HOLD DATA START UK81974 HOLD DATA END UK82110 HOLD DATA START UK82110 HOLD DATA END UK82112 HOLD DATA START ++ HOLD(UK82112) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12269) COMMENT (See PK71143 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. APAR PK71143 corrects a problem that can cause RS00C90096 (number of page locks reached numlkus install limit) being issued for an update/delete/merge statement. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK82112 HOLD DATA END UK82113 HOLD DATA START UK82113 HOLD DATA END UK82167 HOLD DATA START UK82167 HOLD DATA END UK82179 HOLD DATA START UK82179 HOLD DATA END UK82182 HOLD DATA START UK82182 HOLD DATA END UK82223 HOLD DATA START UK82223 HOLD DATA END 1UK82265 HOLD DATA START ++ HOLD(UK82265) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12275) COMMENT (See PM70395 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. Apar PM70395 corrects a problem that can cause an ABEND04E rc00E70005 (DSNXGINB M621 or DSNXRFF M932) or an ABEND0C4 rc4 (DSNIRNXT +308) for queries that contain an INLIST predicate that includes hostvars. 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.). UK82265 HOLD DATA END UK82322 HOLD DATA START ++ HOLD(UK82322) SYS FMID(HDBAA10) REASON(DOC) DATE(12277) COMMENT (PM63884 adds a documentation change in Utility Guide and Reference like the following: In Chapter 28. RESTORE SYSTEM,Utility Guide and Reference V10. Under the section "Authorization required": This RACF authority is required, because the RESTORE SYSTEM utility invokes DFSMSdss when tape is the input | and z/OS level is 1.11(or lower). However, when you | restore database copy pools from tape when z/OS level is | 1.12(or higher) or from a FlashCopy on disk, the RESTORE SYSTEM utility invokes DFSMShsm, which does not require Operations or DASDVOL authority. Chapter 28. RESTORE SYSTEM Under the section "How to determine which system-level backups DB2 restores": | The RESTORE SYSTEM utility invokes DFSMShsm to restore the database copy pool volumes from a system-level backup on | tape when running z/OS 1.12 or above. Otherwise, it invokes | DFSMSdss.). ++ HOLD(UK82322) SYS FMID(HDBAA10) REASON(ACTION) DATE(12277) COMMENT (After applying the fix for PM63384, users are advised that the job output of RESTORE SYSTEM utility from tape might not include DFSMSdss messages in cases where DFSMShsm is now called instead. This PTF has changed RESTORE SYSTEM to call DFSMShsm to invoke DFSMSdss in its own address space (cross memory mode, XM) instead of DFSMShsm's address space when z/OS level is 1.12 or later. THE FOLLOWING IMPORTANT ACTION IS REQUIRED: APPLY HSM APAR OA40046 BEFORE OR AT THE SAME TIME PM63384 IS APPLIED. FAILURE TO DO SO CAN RESULT IN A FAILURE OF RESTORE 1 SYSTEM IF PM63384 IS APPLIED AND ACTIVE ON DB2 BUT OA40046 IS NOT APPLIED AND ACTIVE ON HSM.). UK82322 HOLD DATA END UK82324 HOLD DATA START UK82324 HOLD DATA END UK82353 HOLD DATA START UK82353 HOLD DATA END UK82387 HOLD DATA START UK82387 HOLD DATA END UK82411 HOLD DATA START UK82411 HOLD DATA END UK82453 HOLD DATA START ++ HOLD(UK70912) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12283) COMMENT (See PM44087 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. APAR PM44087 corrects problems that can cause incorrect output for XMLEXIST when indexx access is chosen for XML value 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.). UK82453 HOLD DATA END UK82533 HOLD DATA START UK82533 HOLD DATA END UK82563 HOLD DATA START UK82563 HOLD DATA END UK82612 HOLD DATA START UK82612 HOLD DATA END UK82618 HOLD DATA START ++ HOLD(UK82618) SYS FMID(HDBAA10) REASON(ACTION) DATE(12286) COMMENT (**Action for PM70983 (V10): See PTF cover letter for additional information. This PTF modifies DB2 supplied Database Metadata stored 1 procedures SYSIBM.SQLCOLPRIVILEGES, SYSIBM.SQLCOLUMNS, SYSIBM.SQLTABLEPRIVILEGES and SYSIBM.SQLTABLES to fix the SQLCODE -104 problem when SCHEMALIST value length exceed 128 bytes. *************************************************************** Actions for customers who are already using DB2 V10: *************************************************************** If you have already installed or migrated to DB2 V10, you need to take the following actions after applying this PTF: (1) Run installation job DSNTIJRT to rebind DBRM DSNACOLU, DSNACPRU, DSNATBLU and DSNATBPU (2) Refresh the WLM environment Procedures for these actions are given below. ---------------------------------------------------------------- (1) Run installation job DSNTIJRT to rebind DBRM DSNACOLU, DSNACPRU, DSNATBLU and DSNATBPU ==> This action is required for all V10 customers Run your customized copy of job DSNTIJRT with MODE(INSTALL) to bind the DBRMs DSNACOLU, DSNACPRU, DSNATBLU and DSNATBPU If you have previously run DSNTIJRT, rerunning with MODE(INSTALL) will cause it to detect and correct only missing and downlevel SQL objects and packages for DB2-supplied routines. Note: Use MODE(INSTALL-PREVIEW) to obtain a report of any changes without processing them. The PREVIEW option will also generate and output a JCL job to the JCLOUT DD that contains any SQL and bind statements to be processed. After reviewing the changes, either rerun DSNTIJRT without the PREVIEW option or customize and run the generated job. (2) Refresh the WLM environment Issue the following VARY MVS command to refresh the WLM application environment VARY WLM,APPLENV=,REFRESH where is the name of the WLM application environment in which the Database Metadata stored procedures SYSIBM.SQLCOLPRIVILEGES, SYSIBM.SQLCOLUMNS, SYSIBM.SQLTABLEPRIVILEGES and SYSIBM.SQLTABLES are running). UK82618 HOLD DATA END UK82624 HOLD DATA START UK82624 HOLD DATA END 1 UK82625 HOLD DATA START UK82625 HOLD DATA END UK82640 HOLD DATA START UK82640 HOLD DATA END UK82671 HOLD DATA START UK82671 HOLD DATA END UK82676 HOLD DATA START UK82676 HOLD DATA END UK82696 HOLD DATA START UK82696 HOLD DATA END UK82699 HOLD DATA START ++ HOLD(UK78418) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12291) COMMENT (***Action for PM58322: See PM58322 APAR/PTF text for additional information about why a REBIND is necessary. PM58322 corrects a problem of missing a negative SQLCODE on the insert or delete operation in the statements such as INSERT, DELETE, MERGE, or SELECT FROM those where the object table references a view and the view definition references a DB2 directory table such as SYSLGRNX. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK82699) SYS FMID(HDBAA10) REASON(ACTION) DATE(12291) COMMENT (***Action for PM73083: See PM73083 APAR/PTF text for additional information about why a REBIND is necessary. PM73083 corrects a problem of ABND04E at DSNIDM.DSNIRNXT:5024 or ABND04E at DSNNXML.DSNNOGET:M720 may occur for an SQL statement that references to a DB2_GENERATED_DOCID_FOR_XML column inside a view and a non DEFAULT value is inserted into this DB2_GENERATED_DOCID_FOR_XML column inside a view. View contains the DB2_GENERATED_DOCID_FOR_XML and a non default value is inserted into this column must be dropped and recreated. 1 To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK82699 HOLD DATA END UK82701 HOLD DATA START UK82701 HOLD DATA END UK82729 HOLD DATA START UK82729 HOLD DATA END UK82731 HOLD DATA START ++ HOLD(UK82706) SYS FMID(HDBAA10) REASON(ACTION) DATE(12291) COMMENT (This PTF does not fix any current incorrect values. After applying this fix, you may use the following sample query to see if there are any orphaned rows caused by the original error: SELECT * FROM SYSIBM.SYSROUTINES R WHERE R.ROUTINETYPE = 'F' AND R.FUNCTION_TYPE = 'S' AND R.ORIGIN = 'N' AND R.INLINE = 'N' AND NOT EXISTS ( SELECT * FROM SYSIBM.SYSPACKAGE P WHERE P.OWNER = R.SCHEMA AND P.NAME = R.NAME AND P.COLLID = R.COLLID AND P.VERSION = R.VERSION)! If this query returns any rows, please contact IBM for further instructions on how to resolve these rows.). UK82731 HOLD DATA END UK82732 HOLD DATA START UK82732 HOLD DATA END UK82737 HOLD DATA START UK82737 HOLD DATA END UK82739 HOLD DATA START UK82739 HOLD DATA END UK82783 HOLD DATA START UK82783 HOLD DATA END 1 UK82789 HOLD DATA START UK82789 HOLD DATA END UK82795 HOLD DATA START UK82795 HOLD DATA END UK82830 HOLD DATA START UK82830 HOLD DATA END UK82841 HOLD DATA START UK82841 HOLD DATA END UK82873 HOLD DATA START UK82873 HOLD DATA END UK82887 HOLD DATA START UK82887 HOLD DATA END UK82904 HOLD DATA START UK82904 HOLD DATA END UK82906 HOLD DATA START UK82906 HOLD DATA END UK83082 HOLD DATA START ++ HOLD(UK83082) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12304) COMMENT (***Action for PM71068: See PM71068 APAR/PTF text for additional information about why a REBIND is necessary. PM71068 corrects a problem of ABEND04E RC00C90101 DSNIBNRF 5003 or ERQUAL5003 that may occur for an SQL statement that satisfies the following conditions: 1. The SQL statement references to a recursive common table expression. 2. The common table expression contains a UNION ALL and outer join. 3. The select list of the one branch of the UNION ALL references to a not null column which came from the preserving side table of the outer join. The select list of the other branch references to a not null value. 4. The values on both select list have different types or length To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover 1 letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK82118) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12304) COMMENT (***Action for PM69519: See PM69519 APAR/PTF text for additional information about why a REBIND is necessary. PM69519 corrects a problem that ABEND04E RC00E70005 at the location DSNXGRDS.DSNXORLI:M111 may happen for an SQL statement that ORDER BY any empty string constant and parallelism is enabled. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK78148) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12304) COMMENT (***Action for PM59659: See PM59659 APAR/PTF text for additional information about why a REBIND is necessary. PM59659 corrects a problem of ABEND04E RC00C90101 at DSNXIDM .DSNXOTFLA:5009 (in DB2 10) , ABEND04E RC00C900D0 at DSNXGRDS.DSNXROHB+9FF6(in DB2 10) or unexpected SQLCODE -404 (in DB2 9) could occur when a SQL statement references a LOB datatype and a different CCSID non LOB datatype in an UNION ALL. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK69753) SYS FMID(HDBAA10) REASON(ACTION) DATE(12304) COMMENT (This PTF fixes the problem of incorrect information could be recorded in the catalog SYSCOLUMNS for an ALTER statement that satisfies the following conditions: 1. The ALTER statement is used to modify the data type of the base table columns. 2. This base table column is used inside a view that contains UNION / UNION ALL. To make this fix effective, the view that satisfies the conditions above must be drop and recreate or regenerate with the ALTER VIEW REGENERATE statement.). UK83082 HOLD DATA END UK83085 HOLD DATA START UK83085 HOLD DATA END UK83112 HOLD DATA START UK83112 HOLD DATA END 1 UK83238 HOLD DATA START UK83238 HOLD DATA END UK83265 HOLD DATA START UK83265 HOLD DATA END UK83266 HOLD DATA START ++ HOLD(UK83266) SYS FMID(HDBAA10) REASON(ACTION) DATE(12310) COMMENT (***Action for PM70630 (V10) This PTF corrects the following errors: (1) A bug that can cause the DB2 installation CLIST to halt during edit of job DSNTIJMV (2) A bug that can cause the CLIST to report insufficient data set size when a large amount of storage is specified for the DB2 work file database. (3) Missing and extraneous REBUILD INDEX statements in job DSNTIJRI. If you have already installed or migrated to DB2 V10, you need to take the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Optionally update customized copies of job DSNTIJRI Detail for these actions follows: ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members ---------------------------------------------------------------- ==> This action is required for all V10 customers This PTF modifies CLIST members DSNTCALC, DSNTINS1, and DSNTINS2 in the SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your copies of these CLISTs. You may also want to move them to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. ---------------------------------------------------------------- (2) Optionally update customized copies of job DSNTIJRI ---------------------------------------------------------------- ==> This action is recommended for V10 customers who maintain a customized copy of job DSNTIJRI The DSNTIJRI job can be used to rebuild indexes on DB2 catalog tables that are affected by DB2 catalog tailoring (job DSNTIJEN) during migration to V10 conversion mode. This PTF adds a missing REBUILD INDEX statement to the DSNTIJRI job in the SDSNSAMP target library only. If you maintain a private copy of this job, you need to add the following statement under the SYSIN DD of job step DSNRI1: 1 REBUILD INDEX ("SYSIBM"."DSNXRX01") SORTDEVT(SYSDA) SORTNUM(5) If you migrated to V10 from V9, you can optionally remove the other REBUILD INDEX statements.). UK83266 HOLD DATA END UK83268 HOLD DATA START UK83268 HOLD DATA END UK83355 HOLD DATA START UK83355 HOLD DATA END UK83359 HOLD DATA START UK83359 HOLD DATA END UK83407 HOLD DATA START UK83407 HOLD DATA END UK83409 HOLD DATA START ++ HOLD(UK78529) SYS FMID(JDBAA12) REASON(ACTION) DATE(12314) 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.). UK83409 HOLD DATA END UK83411 HOLD DATA START ++ HOLD(UK78526) SYS FMID(JDBAA12) REASON(ACTION) DATE(12314) 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) 1 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.). UK83411 HOLD DATA END UK83427 HOLD DATA START UK83427 HOLD DATA END UK83430 HOLD DATA START UK83430 HOLD DATA END UK83431 HOLD DATA START UK83431 HOLD DATA END UK83440 HOLD DATA START UK83440 HOLD DATA END UK83463 HOLD DATA START ++ HOLD(UK83463) SYS FMID(HDBAA10) REASON(DOC) DATE(12317) COMMENT (SQL code -4742 is updated to describe when this code can be issued if the new special register CURRENT GET_ACCEL_ARCHIVE is set to YES. The approximate description containing the new text of SQL code -4742 in the DB2 Codes manual is shown below. Refer to the DB2 Codes manual for the exact description. -4742 The statement cannot be executed by DB2 or in the accelerator (REASON reason-code) Explanation: A statement cannot be executed by DB2 or by an accelerator server. | The statement cannot be executed by DB2 if any of the | following conditions are true: | . Special register CURRENT GET_ACCEL_ARCHIVE is set to YES, | and the statement needs to access the data that is stored | only in the accelerator server. . Special register CURRENT QUERY ACCELERATION is set to ALL, so the statement must be executed in the accelerator server. The statement cannot be executed by an accelerator server if any condition that is indicated by a reason code is true. . . .). ++ HOLD(UK77398) SYS FMID(HDBAA10) REASON(DOC) DATE(12317) COMMENT (***Documentation for PM57643: See PM57643 APAR/PTF text for additional information. 1 The following corrections are to be added to the DB2 for z/OS Messages manual for the DSNX880I message. . Under Explanation, the first occurrence of "sql-code" is corrected to be "return-code". . Under Explanation, following the list of message tokens, the following paragraph is added: If the operation is CONNECT and the return-code is 8, the location name in the row that defines the accelerator in the SYSACCEL.SYSACCELERATORS table is incorrectly set to the local DB2 location. . Under System programmer response, the following paragraph is added: If the operation is CONNECT and the return-code is 8, ensure that the LOCATION column of the row that defines the accelerator in SYSACCEL.SYSACCELERATORS is a valid accelerator location name.). ++ HOLD(UK83463) SYS FMID(HDBAA10) REASON(ACTION) DATE(12317) COMMENT (***Action for PM72274: This PTF adds a new DB2 subsystem parameter, DSN6SPRM.GET_ACCEL_ARCHIVE. This parameter specifies the default setting of the CURRENT GET_ACCEL_ARCHIVE special register. This is the value used when this special register has not been set by means of the SET CURRENT GET_ACCEL_ARCHIVE statement. Valid options are: * NO : Specifies that when a table is archived in IBM DB2 Analytics Accelerator, the table reference does not include the accelerator archived data. ====> This is the default setting * YES: Specifies that when a table is archived in IBM DB2 Analytics Accelerator, the table reference includes the archived data. This PTF also adds a new option, 3, to an existing DB2 subsystem parameter DSN6SPRM.QUERY_ACCEL_OPTIONS. This parameter is used to specify additional types of SQL queries to be included in query offloading. It previously had three options: * NONE: Query offloading is restricted to the standard SQL statements ==> This is the default setting. * 1 : The queries that include data encoded by multi-byte character set EBCDIC encoding scheme are not blocked from executing on IBM DB2 Analytics Accelerator. See APAR PM60170 for more information about this option. * 2 : For INSERT statements where a fullselect specifies the data to be inserted, the fullselect part is not blocked from executing on IBM DB2 Analytics Accelerator although the data operated on by the fullselect may not be current in the accelerator. The fullselect will be considered for offload as if it were a stand-alone statement, except that DB2 will not offload the fullselect if the target 1 table of the INSERT has a different encoding scheme than any tables referenced in the fullselect. The new option, 3, specifies that: * 3 : The queries that include DB2 byte-based string functions on data encoded by multi-byte character sets encoding schemes (like Unicode) are not blocked from executing on IBM DB2 Analytics Accelerator although it supports only character-based string functions. If the data on which the string function is specified contains only single-byte characters, executing the function on IBM DB2 Analytics Accelerator will return the same result as executing the function on DB2 irrespective of what encoding scheme is used for the data. However, if the data contains multi-byte characters, the results will not be the same. NONE remains the default setting for QUERY_ACCEL_OPTIONS and is mutually exclusive from all other settings. Options 1, 2, and 3 can be specified individually or together. Examples: QUERY_ACCEL_OPTIONS=NONE QUERY_ACCEL_OPTIONS=1 QUERY_ACCEL_OPTIONS=3 QUERY_ACCEL_OPTIONS=(1,2) QUERY_ACCEL_OPTIONS=(1,3) QUERY_ACCEL_OPTIONS=(2,3) This PTF also adds support for a new column, ARCHIVE, to SYSACCEL.SYSACCELERATEDTABLES, a table in the accelerator database. It also changes the lock size of the containing table space, SYSACCEL, from PAGE to ROW. DSNTIJAS, the sample job for creating these objects, is modified accordingly. If you have already installed or migrated to this version of DB2 you need to take the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Update your customized copy of job DSNTIJUZ (3) Update private copies of the DSNTIDxx CLIST input member (4) Update your customized copy of job DSNTIJAS (5) Update your accelerator database Detailed guidance for these actions follows: ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members ---------------------------------------------------------------- ==> This action is required 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 1 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 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 GET_ACCEL_ARCHIVE=, where is NO or YES, to the invocation of the DSN6SPRM macro. Make sure to add a continuation character in column 72 if needed. If your DSNTIJUZ job does not include GET_ACCEL_ARCHIVE, that parameter will be set to the default of NO when you assemble the DSNZPxxx module. * To specify that queries that include DB2 byte-based string functions on data encoded by multi-byte character sets encoding schemes (like Unicode) are not to be blocked from executing on the accelerator, add or update the keyword parameter QUERY_ACCEL_OPTIONS= where is 3 or a list form such as (1,2,3) to the invocation of the DSN6SPRM macro. Make sure to add a continuation character in column 72 if needed. If your DSNTIJUZ job does not include QUERY_ACCEL_OPTIONS, that parameter will be set to the default of NONE when you assemble the DSNZPxxx module. * Run the first two steps of the DSNTIJUZ job you modified. * After the job completes, you must either use the -SET SYSPARM command or stop and restart DB2 for the change to take effect. ---------------------------------------------------------------- (3) Update private copies of the DSNTIDxx CLIST input member ---------------------------------------------------------------- ==> This action is required This PTF adds an entry for GET_ACCEL_ARCHIVE to the CLIST default input members, DSNTIDXA and DSNTIDXB, in the SDSNSAMP target library. You need to add this entry to all private copies of your CLIST output DSNTIDxx member. In each such copy, add the following line: GET_ACCEL_ARCHIVE CHAR M NONE NONE Change to the value specified for GET_ACCEL_ARCHIVE in step (2), above. If you added or modified the setting for QUERY_ACCEL_OPTIONS, you need to add an entry for it to all private copies of your CLIST output DSNTIDxx member. In each such copy, add the following line: 1 QUERY_ACCEL_OPTIONS CHAR M NONE NONE Change to the value you specified for QUERY_ACCEL_OPTIONS in step (2), above. ---------------------------------------------------------------- (4) Update your customized copy of job DSNTIJAS ---------------------------------------------------------------- ==> This action is required if you maintain a customized copy of DSNTIJAS This PTF modifies installation job DSNTIJAS in the SDSNSAMP target library only. After applying this PTF, you need to update job step DSNTIAC of your customized copy of this job as follows: (a) In the create statement for table space SYSACCEL, change LOCKSIZE from PAGE to ROW. (b) In the create statement for table SYSACCEL.SYSACCELERATEDTABLES, add a line for column ARCHIVE after the line for column SUPPORTLEVEL: , "ARCHIVE" CHAR(1) NOT NULL WITH DEFAULT ---------------------------------------------------------------- (5) Update your accelerator database ---------------------------------------------------------------- ==> This action is required if you have created the accelerator database, DSNACCEL. After applying this PTF, process the following SQL using SPUFI or a similar tool to update the SYSACCEL table space and the SYSACCEL.SYSACCELERATEDTABLES table: ALTER TABLESPACE DSNACCEL.SYSACCEL LOCKSIZE ROW; ALTER TABLE SYSACCEL.SYSACCELERATEDTABLES ADD COLUMN "ARCHIVE" CHAR(1) NOT NULL WITH DEFAULT; The ALTER operation will place the DSNACCEL.SYSACCEL table space where the SYSACCEL.SYSACCELERATEDTABLES table resides in AREO* state (e.g. REORG-pending state). Perform REORG utility on DSNACCEL.SYSACCEL table space to remove it from REORG-pending state. Then re-issue the START ACCELERATOR command for DB2 to validate the new added column. If an accelerator is already started, you may receive an expected message DSNX823I indicating an accelerator is already started. If you are falling back from DB2 10 to DB2 9 or running with release coexistence between DB2 10 and DB2 9, note that the new features added by this APAR will not work on DB2 9. New syntax will receive an SQL error on DB2 9.). ++ HOLD(UK80838) SYS FMID(HDBAA10) REASON(ACTION) DATE(12317) 1 COMMENT (PM66216 corrects a problem that ABEND04E RC00E20001 or RC00C90101 may happen when executing a native SQL procedure that contains a graphic string SQL statement used in a statement PREPARE or EXECUTE IMMEDIATE. After the application of this PTF, ALTER PROCEDURE REGENERATE is required for each native SQL procedure that is affected. Review the PTF cover letter to determine which, if any, native SQL procedure could be affected by this change.). ++ HOLD(UK78584) SYS FMID(HDBAA10) REASON(ACTION) DATE(12317) COMMENT (***Action for PM60806 TYPE: ACT PM60806 corrects a problem of missing SQLCODE -312 for a CREATE FUNCTION statement satisfied the following conditions, 1. It is a create sql scalar function, 2. SQLCODE/SQLSTATE is not defined as an SQL variable, 3. SQLCODE/SQLSTATE is referenced in a RETURN statement in function SQL routine body. 4. No table is referenced in the RETURN statement or no column in the referenced table with name as SQLCODE or SQLSTATE. After the application of this PTF, ALTER FUNCTION REGENERATE is required for each SQL scalar function that is affected. Review the PTF cover letter to determine which, if any, SQL scalar function could be affected by this change.). ++ HOLD(UK83463) SYS FMID(HDBAA10) REASON(AO) DATE(12317) COMMENT (Fields in the Q8ST will be marked '(S)' (serviceability) that were previously externalized. These fields will no longer be formally supported so applications relying on their content should be changed. The following fields in Q8ST will be marked '(S)': Q8STACTV Current active requests (S) Q8STMAXA Maximum active requests (S) Q8STAVGQ03 Avg query queue length in last 3 hrs(S) Q8STMAXQ Highest query queue length so far (S) Q8STCCPU Average cpu utilization on (S) accelerator coordinator node Q8STSKEW Data skew (S) Q8STWCPU Average cpu utilization on (S) accelerator worker nodes Q8STWNOD Number of active worker nodes (S) Q8STAVGQ24 Avg query queue length in last 24hrs(S) Q8STQUEW Avg wait time in accelerator queue (S) Q8STQUEM Max wait time in accelerator queue (S) Q8STMIPS Accelerator proccessing capacity (S) in MIPS Q8STCORS Total number of proccessors in the (S) accelerator Q8STSREQ Total number of successful query (S) requests since accelerator started Q8STFREQ Total number of failed query (S) 1 requests since accelerator started Q8STFINV Number of failed query requests (S) due to invalid accelerator state Q8STDSKA Disk storage (MB) available (S) Q8STDSKU Disk storage (MB) in-use (S) Q8STDSKB Disk storage (MB) in-use for DB (S) Q8STCPMU Physical memory (MB) average usage (S) on accelerator coordinator node Q8STNMDS Number of data slices (S) Q8STWPMU Physical memory (MB) average usage (S) on accelerator worker nodes Q8STWSMA Shared memory (MB) data available (S) on accelerator worker nodes Q8STWSMU Shared memory (MB) data average (S) usage on accelerator worker nodes Q8STWSMM Maximum shared (MB) memory data in (S) use on accelerator worker nodes). UK83463 HOLD DATA END UK83480 HOLD DATA START UK83480 HOLD DATA END UK83490 HOLD DATA START UK83490 HOLD DATA END UK83497 HOLD DATA START UK83497 HOLD DATA END UK83501 HOLD DATA START UK83501 HOLD DATA END UK83517 HOLD DATA START UK83517 HOLD DATA END UK83526 HOLD DATA START UK83526 HOLD DATA END UK83553 HOLD DATA START ++ HOLD(UK83553) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12320) COMMENT (See PM72976 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. Apar PM72976 corrects a problem that can cause an ABEND04E RC00C90101 at DSNIDM.DSNONLLE:5003 when running a query that contains an OLAP function and a group by clause that references a LOB column. To make this fix effective for a static application, it must be 1 rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK83553 HOLD DATA END UK83569 HOLD DATA START UK83569 HOLD DATA END UK83605 HOLD DATA START ++ HOLD(UK83605) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12321) COMMENT (***Action for PM73526: See PM73526 APAR/PTF text for additional information about why a REBIND is necessary. PM73526 corrects a problem of an ABEND0C7 RC0 at DSNXRSBC +8086 or unexpected SQLCODE -406 could occur when an SQL statement satisfies the following conditions: 1. It contains a DATETIME arithmetic expression in SELECT LIST, 2. It contains an order by clause which references the DATETIME arithmetic expression result in its sort key expression. 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.). UK83605 HOLD DATA END UK83623 HOLD DATA START ++ HOLD(UK72373) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12321) COMMENT (***Action for PM39078: See PM39078 APAR/PTF text for additional information about why a REBIND is necessary. PM39078 corrects a problem of missing warning message when a query which references an XML column that does not support multiple XML versions and it is bound with the USE CURRENTLY COMMITTED option. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK72373) SYS FMID(HDBAA10) REASON(DOC) DATE(12321) COMMENT (Existing warning SQLCODE +20002 is changed by this APAR/PTF. The change will be documented in the DB2 Codes, SQL Reference, Command Reference manuals respectively. The texts for SQLCODE +20002 are: 1 +20002 THE clause SPECIFICATION IS IGNORED FOR OBJECT object-name Explanation This message is issued in response to a clause specification that was ignored on a query statement. System action The statement is processed. User response To stop receiving this message, avoid ACCESSCONCURRENTRESOLUTION(UseCurrentlyCommitted) bind option for a plan or package accessing non-versioned XML, or avoid a statement (accessing non-versioned XML) prepared with "Use Currently Committed" attribute. User response needs real action: If an XML column does not support multiple XML versions, you can convert to an XML column that supports multiple XML versions by following these steps: 1. Unload the data from the table that contains the XML columns. 2. Drop the table. 3. Create the table in a universal table space. The new table supports multiple XML versions. 4. Load the data into the table. SQLSTATE 01624). UK83623 HOLD DATA END UK83629 HOLD DATA START ++ HOLD(UK83629) SYS FMID(HDBAA10) REASON(DOC) DATE(12321) COMMENT (See PM70090 APAR/PTF text in the PTF cover letter for additional information about why a warning message may be issued on BIND/REBIND PACKAGE command. PM70090 may cause a warning message DSNT204I to be issued on BIND/REBIND PACKAGE request, if the REMOTE(location-name,...,,...) option was ignored. This is the approximate wording of the new text in the Messages manual: DSNT204I bind-type WARNING FOR object-type object-name, option IS option-status, substitute-option WILL BE USED. Explanation A BIND subcommand was issued with an invalid option. bind-type 1 The type of BIND subcommand, such as REBIND. object-type The type of object, such as PLAN. object-name The name of the application plan. option The unsupported BIND option. option-status Possible values are: INVALID The BIND option is not valid in the context where it is used. NOT USED The BIND option syntax is valid, but the option has no effect. substitute-option The BIND option that was used instead of option. This message can be caused by the use of a deprecated option. System action The bind process continues, using the option that is indicated by substitute-option. Operator response No action is required. Severity 4 (warning) This is an example of the new message if the REMOTE(location-name,...,,...) option was ignored. DSNT204I bind-type WARNING FOR PACKAGE object-name,  REMOTE option IS NOT USED, ENABLE(REMOTE) WILL BE USED. Review the PTF cover letter to determine which, if any, packages could be affected by this change.). UK83629 HOLD DATA END UK83646 HOLD DATA START ++ HOLD(UK83646) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12324) COMMENT (***Action for PM73328: See PM73328 APAR/PTF text for additional information about why a REBIND is necessary. PM73328 corrects a problem of selecting Index Scan access using a less filtering index over INLIST access using a more filtering index, which may result in an inefficient access 1 path. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK83646 HOLD DATA END UK83649 HOLD DATA START UK83649 HOLD DATA END UK83672 HOLD DATA START UK83672 HOLD DATA END UK83675 HOLD DATA START UK83675 HOLD DATA END UK83682 HOLD DATA START ++ HOLD(UK83682) SYS FMID(HDBAA10) REASON(ACTION) DATE(12325) COMMENT (APAR PM75275 corrects a problem with RUNSTATS updating SYSIBM.SYSINDEXES and SYSIBM.SYSINDEXSTATS catalog tables with a value of zero for column DATAREPEATFACTORF for non-empty partitions. After applying this APAR, RUNSTATS will update DATAREPEATFACTORF with a non-zero value when applicable, and a zero value if the partition or table is empty. A documention change is needed in the SQL REFERENCE for catalog tables SYSINDEXES, SYSINDEXES_HIST, SYSINDEXSTATS and SYSINDEXSTATS_HIST The description for DATAREPEATFACTORF will read as follows: The anticipated number of data pages that will be touched following an index key order. This statistic is only collected when the STATCLUS subsystem parameter is set to ENHANCED. This number is -1 if statistics have not been collected. This is an updatable column. APAR PM75275 corrects a problem with RUNSTATS updating SYSIBM.SYSINDEXES and SYSIBM.SYSINDEXSTATS catalog tables with a value of zero for column DATAREPEATFACTORF for non-empty partitions. After applying this APAR, RUNSTATS will update DATAREPEATFACTORF with a non-zero value when applicable, and a zero value if the partition or table is empty. A documention change is needed in the SQL REFERENCE for catalog tables SYSINDEXES, SYSINDEXES_HIST, SYSINDEXSTATS and SYSINDEXSTATS_HIST The description for DATAREPEATFACTORF will read as follows: 1 The anticipated number of data pages that will be touched following an index key order. This statistic is only collected when the STATCLUS subsystem parameter is set to ENHANCED. This number is -1 if statistics have not been collected. This is an updatable column.). UK83682 HOLD DATA END UK83700 HOLD DATA START UK83700 HOLD DATA END UK83708 HOLD DATA START UK83708 HOLD DATA END UK83727 HOLD DATA START UK83727 HOLD DATA END UK83733 HOLD DATA START UK83733 HOLD DATA END UK83736 HOLD DATA START UK83736 HOLD DATA END UK83737 HOLD DATA START UK83737 HOLD DATA END UK83750 HOLD DATA START ++ HOLD(UK83750) SYS FMID(HDBAA10) REASON(DOC) DATE(12331) COMMENT (Changed option for BIND QUERY in Command Reference: Syntax >>-BIND QUERY--+-------------------+--------------------------> | .-NO--. | '-LOOKUP(-+-YES-+-)-' | >--+---------------------------------+--------------------->< | '-EXPLAININPUTSCHEMA(schema-name)-' Option description LOOKUP | Specifies whether to check catalog tables for existing | statement-level access path or optimization parameter hints | that match rows in DSN_USERQUERY_TABLE. LOOKUP(NO) is the | default value. When LOOKUP(YES) is in effect, new rows are | not inserted into the catalog tables and new hints are not | created. When matching rows are found in the catalog tables, | DB2 inserts the value of the SYSQUERY.QUERYID column into | the DSN_USERQUERY_TABLE.QUERYID column of the matching row. 1 (NO) DB2 reads the information from the DSN_USERQUERY_TABLE and certain EXPLAIN tables and inserts the data into certain catalog tables as appropriate to create the specified statement-level optimization hints. NO is the default value. Depending on the values that are specified in DSN_USERQUERY_TABLE, rows might be read from the following additional input tables: schema.PLAN_TABLE | Where schema is the authorization ID of the issuer of the | BIND QUERY command. Depending on the values that are specified in the input tables, data might be inserted in the following catalog tables: SYSIBM.SYSQUERY SYSIBM.SYSQUERYPLAN SYSIBM.SYSQUERYOPTS The catalog table rows create the following types of statement-level optimization hints: Access path hints Optimization parameter hints DB2 issues the following messages to indicate the results of BIND QUERY operation: | A DSNT280I message for each DSN_USERQUERY_TABLE row that is | inserted successfully into the catalog tables. | A DSNT281I message for each DSN_USERQUERY_TABLE row that is | not successfully inserted into the catalog tables. | A single DSNT290I message if some rows were inserted into | catalog tables successfully or a DSNT291I message if no | rows were inserted successfully. (YES) | DB2 reads information from the DSN_USERQUERY_TABLE and looks | for the matching rows in the following catalog tables: SYSIBM.SYSQUERY SYSIBM.SYSQUERYPLAN | SYSIBM.SYSQUERYOPTS The catalog tables are not populated with new values and no new hints are created. When matching rows exist in the catalog tables, DB2 inserts the value of the | SYSQUERY.QUERYID column into the DSN_USERQUERY_TABLE.QUERYID | column of the matching row. | New rows are not inserted into the catalog tables when | LOOKUP(YES) is specified. Instead, DB2 issues messages to 1 | indicate whether existing hints where identified: | A DSNT280I message for each row in the DSN_USERQUERY_TABLE | that has a valid matching row in the catalog tables. | A DSNT281I message for each row in the DSN_USERQUERY_TABLE | that does not have valid matching rows in the catalog | tables. | A single DSNT290I message if some matching rows were found or | a DSNT291I message if no matches were found. |EXPLAININPUTSCHEMA |Specifies the schema of the EXPLAIN tables to be used for |input during BIND QUERY processing. This option enables you |to create separate EXPLAIN tables to be used only as input to |the BIND QUERY command. By creating separate input tables, |you can eliminate the need to remove unneeded rows that might |interfere with BIND QUERY process from the EXPLAIN output |tables. When the EXPLAININPUTSCHEMA option is not specified, |DB2 uses the tables that are qualified by the authorization |ID of the user that issues the BIND QUERY command.). UK83750 HOLD DATA END UK83751 HOLD DATA START UK83751 HOLD DATA END UK83754 HOLD DATA START UK83754 HOLD DATA END UK83756 HOLD DATA START UK83756 HOLD DATA END UK83760 HOLD DATA START UK83760 HOLD DATA END UK83769 HOLD DATA START UK83769 HOLD DATA END UK83792 HOLD DATA START ++ HOLD(UK83792) SYS FMID(HDBAA10) REASON(ACTION) DATE(12332) COMMENT (This PTF adds a new DB2-supplied stored procedure called SYSPROC.SET_PLAN_HINT. SET_PLAN_HINT is a common SQL API stored procedure which validates and deploys hints for a single SQL statement. This PTF also adds a new optional configuration parameter called LOBBP32K to DSNTRIN, the program for installing and configuring DB2-supplied routines and their supporting DB2 objects. LOBBP32K, if specified, provides the name of the buffer pool for LOB table spaces created by DSNTRIN that use a 32-KB page 1 size. BP32K is used if this parameter is not specified. *************************************************************** Actions for customers who are already using DB2 V10: *************************************************************** If you have already installed or migrated to V10, the following actions are required after you apply this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Copy updated DB2 installation panels to alternate libraries (3) Update customized copies of installation job DSNTIJRT (4) Update private copies of the DSNTIDxx CLIST input member (5) Run installation job DSNTIJRT to install new objects (6) Run installation job DSNTIJRV to validate new objects These actions are detailed below: ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members *********************************************************** * This action is required for all V10 customers *********************************************************** This PTF modifies CLIST members DSNTINST, DSNTINSV, and DSNTINS1 in the prefix.SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your private copies of these CLISTs. You may also want to move them to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. ---------------------------------------------------------------- (2) Copy updated DB2 installation panels to alternate libraries *********************************************************** * This action is required for all V10 customers who maintain * copies of the DB2 installation panels outside of SMP/E: *********************************************************** This PTF modifies the DB2 installation panels DSNTIPRA and DSNTIPRL in the prefix.SDSNSPFP target library. If you keep the DB2 installation panels in a different library then after applying this PTF, you need to copy the updated panels to that library. ---------------------------------------------------------------- (3) Update customized copies of installation job DSNTIJRT *********************************************************** * This action is required for all V10 customers *********************************************************** This PTF modifies installation job DSNTIJRT in the prefix.SDSNSAMP target library. After you apply this PTF, you need to update your private copies of DSNTIJRT as follows: (a) Add the following to the list of configuration control statements under the CFIGIN DD: SYSPROC.SET_PLAN_HINT WLMENV(DSNWLM_GENERAL) GRANTTO(PUBLIC) 1 Notes: (i) Always leave a blank line between each configuration statement (ii) Inspect each of the new configuration control statements and if required: - Change the WLMENV parameter setting to the name of the WLM environment you use to run this routine at your site - Change the GRANTTO parameter setting to the authorization IDs that need access to this routine - Optionally add the PKGOWNER option (b) Optionally add the LOBBP32K parameter to the DB2OPT DD. The example below shows how to use LOBBP32K to specify a buffer pool for LOB table spaces created by DSNTRIN that use a 32-KB page size: //DB2OPT DD * STOGROUP(TSSTOGRP) INDEXSTOG(IXSTOGRP) BP4K(BP0) BP8K(BP8K0) BP16K(BP16K0) BP32K(BP32K) LOBBP8K(BP8K0) LOBBP16K(BP16K0) LOBBP32K(BP32K) //CFIGIN DD * ---------------------------------------------------------------- (4) Update private copies of the DSNTIDxx CLIST input member *********************************************************** * This action is required for all V10 customers *********************************************************** This PTF adds entries to store values entered into new fields on installation panel DSNTIPRL: - RTL40ACL: GRANT EXECUTE setting for SYSPROC.SET_PLAN_HINT - RTL40WLM: WLM ENV NAME setting for SYSPROC.SET_PLAN_HINT - RTL40PKO: PACKAGE OWNER setting for SYSPROC.SET_PLAN_HINT You need to make the same changes to all private copies of your CLIST output (DSNTIDxx) member. In each such copy, add the following lines: RTL40ACL CHAR M NONE NONE PUBLIC RTL40PKO CHAR M NONE NONE NULL RTL40WLM CHAR M NONE NONE DSNWLM_GENERAL The values shown in the 6th column are the DB2-supplied default settings. If you specified different settings in action (3) above, change the settings in column 6 accordingly. ---------------------------------------------------------------- (5) Run installation job DSNTIJRT to install new objects *********************************************************** * This action is required for all V10 customers 1 *********************************************************** After you have completed the above actions, run your updated copy of job DSNTIJRT to perform the following tasks: (a) install, bind, and grant access to the new routine SYSPROC.SET_PLAN_HINT (b) create table DSN8BQRY.DSN_USERQUERY_TABLE and its containing table spaces, auxiliary table, and indexes (c) create table DSN8BQRY.PLAN_TABLE and its containing table space and index 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. ---------------------------------------------------------------- (6) Run installation job DSNTIJRV to validate new objects *********************************************************** * This action is required for all V10 customers *********************************************************** After you have completed the above actions, run your customized copy of job DSNTIJRV to validate installation and configuration of SYSPROC.SET_PLAN_HINT.). UK83792 HOLD DATA END UK83795 HOLD DATA START UK83795 HOLD DATA END UK83796 HOLD DATA START UK83796 HOLD DATA END UK83809 HOLD DATA START UK83809 HOLD DATA END UK83843 HOLD DATA START UK83843 HOLD DATA END UK83864 HOLD DATA START ++ HOLD(UK65383) SYS FMID(HDBAA10) REASON(ACTION) DATE(12334) COMMENT (*** Action for PM27855 (DB2 10 for z/OS): DB2 has been changed to recognize that the presence of a held locator requires DB2 to maintain the remote application's association to the DB2 thread that owns the locator value. 1 Effectively, DB2 is changed to apply the same behavior to held LOB locators that currently exists for WITH HOLD cursors. 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 commit processing, IF the application, perhaps erroneously, has established held locators. 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: . Reduced number of IFCID 3 accounting records are written. . 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 (commit) boundary. Before applying this APAR/PTF change, users are advised to evaluate their exposure by researching their enterprise use of held LOB locators. It is difficult to evaluate the use of held LOB locators in Dynamic SQL, ODBC, or JDBC environments, but the use of static SQL HOLD LOCATOR statements can be determined by a query of the DB2 for z/OS SYSIBM.SYSPACKSTMT catalog table. SELECT * FROM SYSIBM.SYSPACKSTMT WHERE STATEMENT LIKE '%HOLD%LOCATOR%' ORDER BY COLLID,NAME; Evaluation of IFCID 0003 accounting trace information, with respect to non zero DSNDQXST.QXHOLDL values, in conjunction with IFCID 0239 package accounting information, can also be used to help identify packages that utilize held LOB locators. Erroneous or unintentional use of held LOB locators, or the packages that contain them, should be eliminated. Proper or intentional use of held LOB locators will now behave correctly but may also result in operational issues that users should be prepared to detect and accommodate. Users can also consider using the FREE LOCATOR SQL statement when the held locator is no longer needed.). UK83864 HOLD DATA END UK83879 HOLD DATA START UK83879 HOLD DATA END UK83887 HOLD DATA START UK83887 HOLD DATA END 1 UK83909 HOLD DATA START ++ HOLD(UK83909) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12335) COMMENT (***Action for PM72374: A rebind is necessary for static applications to rebuild access paths based on the changes in this PTF. PM72374 corrects a problem in which DB2 chooses an index matching on only a few (or one) column(s) when an alternate index exists with matching that is estimated to be very selective and matching columns that are a superset of the chosen index. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK81821) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12335) COMMENT (***Action for PM70949: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM70949 corrects a problem in which a tablespace scan access path may be incorrectly chosen when using an internal serviceability feature to influence access path selection with ACCESS_TYPE='INLIST'. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK81527) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12335) COMMENT (***Action for PM69949: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM69949 corrects a problem in which DB2 may choose a highly selective or index-only DPSI without page range when a more efficient non-DPSI index exists. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK80905) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12335) COMMENT (***Action for PM67292: A rebind is necessary to rebuild package and/or plan structures 1 based on the changes in this APAR. PM67292 corrects a problem that DB2 may improperly favor range list index scan over multiple index access. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK80906) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12335) COMMENT (***Action for PM66098: See PM66098 APAR/PTF text for additional information about why a REBIND is necessary. PM66098 corrects a problem where DB2 does not select an equal unique index even though it may be the best choice, resulting in a query performance problem. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK80750) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12335) COMMENT (***Action for PM62712: See PM62712 APAR/PTF text for additional information about why a REBIND is necessary. PM62712 corrects a problem of choosing an inefficient range-list access path. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK78278) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12335) COMMENT (***Action for PM61011: A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM61011 corrects a problem in which DB2 incorrectly chooses to sort instead of using reverse index scan to avoid the sort when OPTIMIZE FOR 1 ROW is specified. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK77766) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12335) COMMENT (***Action for PM59585: 1 A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM59585 corrects a problem in which an inefficient access path is selected when multiple highly selective indexes exist that can provide the same matching coverage. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK83909 HOLD DATA END UK83912 HOLD DATA START UK83912 HOLD DATA END UK83928 HOLD DATA START ++ HOLD(UK83928) SYS FMID(HDBAA10) REASON(ACTION) DATE(12337) COMMENT (The PTF for APAR PM65564 has modified CSECTs in both the DSNUT101 and DSNUTILA load modules, affecting the utility batch and DB2 DBM1 address spaces. After the PTF has been applied, it will become active in the utility batch address space immediately upon job submission, while it will not take effect in the DB2 DBM1 address space until DB2 is stopped and started. The PTF does not have to be applied to all members in a data sharing environment simultaneously, and may be staged across each member. However, until the fix is active in both the utility batch and DB2 DBM1 address spaces, the problem the PTF addresses will not be resolved.). ++ HOLD(UK79821) SYS FMID(HDBAA10) REASON(ACTION) DATE(12337) COMMENT (++HOLD ACTION FOR PM64354: The LOAD utility has been changed to build a unique compression dictionary when loading at the table space level on a COMPRESS YES classic or partition by range table space with 254 or less partitions for SHRLEVEL NONE LOADs. This change will result in LOAD requiring more above the bar virtual storage to support a unique compression dictionary for each partition. For range partitioned table spaces with 254 partitions this would result in about an extra 254MB of virtual storage above the bar. While this is a very small percentage of above the bar storage, it can become a problem on lpars with an inadequate amount of real storage to fulfill the above the bar virtual storage request.). UK83928 HOLD DATA END UK83933 HOLD DATA START ++ HOLD(UK83933) SYS FMID(HDBAA10) REASON(ACTION) DATE(12337) 1 COMMENT (The PTF for APAR PM71033 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. There is no default DISCARD DD for a LOAD with partition parallelism. A DISCARDDN ddname must be specified for a partition parallel LOAD when using the DISCARDS n keyword at the partition level. If you specify DISCARDS n keyword but omit the DISCARDDN ddname option for the partition, LOAD will issue message DSNU047I - A REQUIRED DD CARD OR TEMPLATE IS MISSING. NAME=DISCARDDN and terminate with RC8.). UK83933 HOLD DATA END UK83945 HOLD DATA START ++ HOLD(UK83945) SYS FMID(HDBAA10) REASON(ACTION) DATE(12338) COMMENT (++HOLD COMMENT FOR RELEASE A10, TYPE ACT: This PTF modifies DSNADMSS, the external module for SYSPROC.ADMIN_INFO_SQL, a DB2-supplied routine that can be used to collect DB2 environmental service data. 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 modules DSNADMSB and DSNADMSS plus the DBRM for DSNADMSS (the DBRM for DSNADMSB is not updated). 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. 1 Note: Use MODE(INSTALL-PREVIEW) to obtain a report of any changes without processing them. The PREVIEW option will also generate and output a JCL job to the JCLOUT DD that contains any SQL and bind statements to be processed. After reviewing the changes, either rerun DSNTIJRT without the PREVIEW option or customize and run the generated job. --------------------------------------------------------------- (2) Refresh the WLM environment --------------------------------------------------------------- ==> All DB2 V10 customers who use the SYSPROC.ADMIN_INFO_SQL stored procedure need to take this action after applying the PTF Issue the following VARY MVS command from the console to refresh the WLM application environment: VARY WLM,APPLENV=!wlmenv!,REFRESH where !wlmenv! is the name of the WLM application environment for running the SYSPROC.ADMIN_INFO_SQL stored procedure. --------------------------------------------------------------- (3) Update your customized copy of job DSNTEJ6I --------------------------------------------------------------- ==> All DB2 V10 customers who maintain a customized copy of DSNTEJ6I need to take this action after applying the PTF. The change affects the default input for the following parameters: Change COLLECT_COLUMN_STATS to N Change EDIT_DDL to Y These default changes are made to minimize the amount of data that is collected. Column statistics can be abundant, but are not always required for analysis. Edit mode will help create DDL in a format that can be more easily applied to a service environment.). ++ HOLD(UK82329) SYS FMID(HDBAA10) REASON(ACTION) DATE(12338) COMMENT (++HOLD COMMENT FOR RELEASE A10, TYPE ACT: This PTF modifies DSNADMSS, the external module for SYSPROC.ADMIN_INFO_SQL, a DB2-supplied routine that can be used to collect DB2 environmental service data. If you have already installed or migrated to DB2 V10, you need to take the following actions after applying this PTF: --------------------------------------------------------------- (1) Refresh the WLM environment --------------------------------------------------------------- ==> All DB2 V10 customers who use the SYSPROC.ADMIN_INFO_SQL stored procedure need to take this action after applying the PTF 1 Issue the following VARY MVS command from the console to refresh the WLM application environment: VARY WLM,APPLENV=!wlmenv!,REFRESH where !wlmenv! is the name of the WLM application environment for running the SYSPROC.ADMIN_INFO_SQL stored procedure.). ++ HOLD(UK79431) SYS FMID(HDBAA10) REASON(ACTION) DATE(12338) COMMENT (++HOLD COMMENT FOR RELEASE A10, TYPE ACT: This PTF modifies DSNADMSS, the external module for SYSPROC.ADMIN_INFO_SQL, a DB2-supplied routine that can be used to collect DB2 environmental service data. It also modifies DSNADMSB, a program that can be used to call SYSPROC.ADMIN_INFO_SQL. In order to take advantage of new collection features incorporated in this version, please ensure that DSN_VIEWREF_TABLE is created before doing static binds or dynamic explains. Also, make sure APAR PM63211 is applied. If you have already installed or migrated to DB2 V10, you need to take the following actions after applying this PTF: --------------------------------------------------------------- (1) Run installation job DSNTIJRT to rebind DBRMs DSNADMSS and DSNADMSB --------------------------------------------------------------- ==> This action is required for all V10 customers Run your copy of job DSNTIJRT with MODE(INSTALL) to install, bind, and grant access to the DB2 supplied stored procedures. If you have previously run DSNTIJRT, rerunning with MODE(INSTALL) will cause it to detect and correct only missing and downlevel SQL objects and packages for DB2-supplied routines. Note: Use MODE(INSTALL-PREVIEW) to obtain a report of any changes without processing them. The PREVIEW option will also generate and output a JCL job to the JCLOUT DD that contains any SQL and bind statements to be processed. After reviewing the changes, either rerun DSNTIJRT without the PREVIEW option or customize and run the generated job. --------------------------------------------------------------- (2) Refresh the WLM environment --------------------------------------------------------------- ==> All DB2 V10 customers who use the SYSPROC.ADMIN_INFO_SQL stored procedure need to take this action after applying the PTF Issue the following VARY MVS command from the console to refresh the WLM application environment: VARY WLM,APPLENV=!wlmenv!,REFRESH 1 where !wlmenv! is the name of the WLM application environment for running the SYSPROC.ADMIN_INFO_SQL stored procedure.). ++ HOLD(UK67425) SYS FMID(HDBAA10) REASON(ACTION) DATE(12338) COMMENT (This PTF modifies DSNADMSS, the external module for SYSPROC.ADMIN_INFO_SQL, a DB2-supplied routine that can be used to collect DB2 environmental service data. It also modifies DSNADMSB, a program that can be used to call SYSPROC.ADMIN_INFO_SQL. This PTF also adds a DCB clause to the SYSPRINT DD in job step DSNADMSB of job DSNTEJ6I. If you have already installed or migrated to DB2 V10, you need to take the following actions after applying this PTF: --------------------------------------------------------------- (1) Run installation job DSNTIJRT to rebind DBRM DSNADMSS --------------------------------------------------------------- ==> 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 SYSPROC.ADMIN_INFO_SQL stored procedure need to take this action after applying the PTF. Issue the following VARY MVS command from the console to refresh the WLM application environment: VARY WLM,APPLENV=!wlmenv!,REFRESH where !wlmenv! is the name of the WLM application environment for running the SYSPROC.ADMIN_INFO_SQL stored procedure. --------------------------------------------------------------- (3) Rebind the package for DSNADMSB --------------------------------------------------------------- ==> All DB2 V10 customers who use the DSNADMSB program to call 1 the SYSPROC.ADMIN_INFO_SQL stored procedure need to take this action after applying the PTF. Submit a job that contains the following BIND statements: BIND PACKAGE(DSNADMSB) MEM(DSNADMSB) - ACTION(REPLACE) ISOLATION(CS) ENCODING(EBCDIC) - LIBRARY('.SDSNDBRM') where is the high-level qualifier for the target DB2 subsystem. --------------------------------------------------------------- (4) Update SYSPRINT DD in job step DSNADMSB of job DSNTEJ6I --------------------------------------------------------------- ==> All DB2 V10 customers who use the job DSNTEJ6I to call DSNADMSB need to take this action after applying the PTF. If you have already installed or migrated to DB2 V10 and you maintain a customized copy of the DSNTEJ6I job, edit your copy, locate the SYSPRINT DD statement, and add DCB=(RECFM=VB,LRECL=4096). For example: //SYSPRINT DD SYSOUT=*,DCB=(RECFM=VB,LRECL=4096)). ++ HOLD(UK62150) SYS FMID(HDBAA10) REASON(ACTION) DATE(12338) COMMENT (This PTF modifies DSNADMSS, the external module for SYSPROC.ADMIN_INFO_SQL, a DB2-supplied routine that can be used to collect DB2 environmental service data. It also modifies DSNADMSB, a program that can be used to call SYSPROC.ADMIN_INFO_SQL. If you have already installed or migrated to DB2 V10, you need to take the following actions after applying this PTF: --------------------------------------------------------------- (1) Run installation job DSNTIJRT to rebind DBRM DSNADMSS --------------------------------------------------------------- ==> 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 1 the PREVIEW option or customize and run the generated job. --------------------------------------------------------------- (2) Refresh the WLM environment --------------------------------------------------------------- ==> All DB2 V10 customers who use the SYSPROC.ADMIN_INFO_SQL stored procedure need to take this action after applying the PTF Issue the following VARY MVS command from the console to refresh the WLM application environment: VARY WLM,APPLENV=!wlmenv!,REFRESH where !wlmenv! is the name of the WLM application environment for running the SYSPROC.ADMIN_INFO_SQL stored procedure. --------------------------------------------------------------- (3) Rebind the package for DSNADMSB --------------------------------------------------------------- ==> All DB2 V10 customers who use the DSNADMSB program to call the SYSPROC.ADMIN_INFO_SQL stored procedure need to take this action after applying the PTF Submit a job that contains the following BIND statements: BIND PACKAGE(DSNADMSB) MEM(DSNADMSB) - ACTION(REPLACE) ISOLATION(CS) ENCODING(EBCDIC) - LIBRARY('.SDSNDBRM') where is the high-level qualifier for the target DB2 subsystem.). UK83945 HOLD DATA END UK83965 HOLD DATA START UK83965 HOLD DATA END UK83968 HOLD DATA START UK83968 HOLD DATA END UK83981 HOLD DATA START UK83981 HOLD DATA END UK83996 HOLD DATA START ++ HOLD(UK83996) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12339) COMMENT (***Action for PM74543: See PM74543 APAR/PTF text for additional information about why a REBIND is necessary. 1 PM74543 corrects a problem of choosing a poorly performing access path for a partitioned table when queries involve partitioned tables, and EQUAL with host variables or parameter markers, and a DPSI (Data Partitioned Secondary Index). To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK83426) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12339) COMMENT (***Action for PM74152: A rebind is necessary for static applications to rebuild internal structures used at runtime based on the changes in this APAR. PM74152 corrects a problem in which DB2 may incorrectly return no rows for a query using page range access where a range predicate exists on a partitioning column defined as CHAR(1). To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK74779) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12339) COMMENT (***Action for PM52548: See PM52548 APAR/PTF text for additional information about why a REBIND is necessary. PM52548 fixes an abend 0C4 RC00000038 DSNISPBM + 1A64 that can occur during query execution for queries containing IS NULL or LIKE predicates. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK71244) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12339) COMMENT (***Action for PM38082: See PM38082 APAR/PTF text for additional information about why a REBIND is necessary. PM38082 corrects a query performance problem involving significant loss of page range screening for queries with multi-column partitioning keys and predicates with host variables or parameter markers on those columns. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover 1 letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK64444) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12339) COMMENT (See PM25059 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. PM25059 corrects a problem where the page range reduction is done incorrectly. It may cause DB2 to choose a plan with an extra GROUP BY / ORDER BY sort where it is not needed. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK83996 HOLD DATA END UK90046 HOLD DATA START UK90046 HOLD DATA END UK90047 HOLD DATA START ++ HOLD(UK90047) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12340) COMMENT (***Action for PM76278: See PM76278 APAR/PTF text for additional information about why a REBIND is necessary. PM76278 corrects a problem of ABEND04E RC00C90101 at DSNGEPLC : 500D or ABEND0C1 or ABEND0C4 at an unknown PSW due to a bad branch when running static DML workloads. 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.). UK90047 HOLD DATA END UK90071 HOLD DATA START UK90071 HOLD DATA END UK90073 HOLD DATA START UK90073 HOLD DATA END UK90076 HOLD DATA START UK90076 HOLD DATA END UK90090 HOLD DATA START ++ HOLD(UK90090) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12341) 1 COMMENT (***Action for PM75364: See PM75364 APAR/PTF text for additional information about why a REBIND is necessary. PM75364 corrects a problem of inaccurate filter factor estimate of an IS NOT DISTINCT FROM predicate that may result in an inefficient access path. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK90090 HOLD DATA END UK90099 HOLD DATA START ++ HOLD(UK90099) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12341) COMMENT (***Action for PM75775: See PM75775 APAR/PTF text for additional information about why a REBIND is necessary. PM75775 corrects a problem of poor query performance for OPTIMIZE FOR 1 ROW when no sort and no join will be performed, and there is an index or indexes which contain all the columns in the predicates. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK90099 HOLD DATA END UK90150 HOLD DATA START UK90150 HOLD DATA END UK90155 HOLD DATA START ++ HOLD(UK82805) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12342) COMMENT (See PM69619 APAR/PTF text for additional information about why a REBIND is necessary. PM69619 corrects a problem that incorrect output may happen for a static SQL statement that satisfies all of the following conditions: 1 It references a table which is in PBR(partitioned by range) tablespace; 2 It contains a predicate on VARCHAR column which is a partition key of PBR table; 3 The option REOPT(ALWAYS) or REOPT(VARS) is specified when binding the package. 1 To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK90155 HOLD DATA END UK90165 HOLD DATA START UK90165 HOLD DATA END UK90173 HOLD DATA START ++ HOLD(UK83869) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12345) COMMENT (***Action for PM74733: See PM74733 APAR/PTF text for additional information about why a REBIND is necessary. Apar PM74733 corrects an ABEND0C4 RC00000004 failure that can occur at location DSNXGRDS.DSNXRSEQ+02D4 when an SQL statement satisfies the following conditions. 1. It contains a table expression. 2. The table expression references a SEQUENCE. 3. The table expression is merged. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK83605) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12345) COMMENT (***Action for PM73526: See PM73526 APAR/PTF text for additional information about why a REBIND is necessary. PM73526 corrects a problem of an ABEND0C7 RC0 at DSNXRSBC +8086 or unexpected SQLCODE -406 could occur when an SQL statement satisfies the following conditions: 1. It contains a DATETIME arithmetic expression in SELECT LIST, 2. It contains an order by clause which references the DATETIME arithmetic expression result in its sort key expression. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK90173) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12345) COMMENT (***Action for PM72911: See PM72911 APAR/PTF text for additional information about why a REBIND is necessary. 1 PM72911 corrects a problem of incorrect output may occur for an SQL statement that references to an SQL table function that satisfies the following conditions: 1.A column is specified as an argument to an SQL table function when the body of that table function contains a materialized common table expression. 2.An argument to an SQL table function is included in the invocation of another SQL table function which contains a materialized common table expression. PM72911 restricts the usage of queries that satisfy the above conditions because of the potential incorrect output. SQLCODE -270 will be issued for queries that satisfy the above conditions. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK80910) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12345) COMMENT (***Action for PM66961: See PM66961 APAR/PTF text for additional information about why a REBIND is necessary. PM66961 corrects a problem that incorrect output may happen for an SQL statement that contains a correlated subquery with predicates like 'T2.C1 = T1.C1 OR (T2.C1 IS NULL AND T1.C1 IS NULL)', where T2.C1 or T1.C1 is a correlated column and the predicate can be reduced to NOT DISTINCT FROM predicate. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK80199) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12345) COMMENT (***Action for PM65380: See PM65380 APAR/PTF text for additional information about why a REBIND is necessary. PM65380 corrects a problem of ABEND04E RC00E70005 in DSNXGRDS.DSNXOUWF:P009 could occur for an SQL statement that contains multiple outer joins, LOB and scrollable cursor. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK80208) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12345) COMMENT 1 (***Action for PM65365: See PM65365 APAR/PTF text for additional information about why a REBIND is necessary. PM65365 corrects a problem that ABEND0C4 RC00000004 at the location DSNXGRDS.DSNXRBND OFFSET07B6 or OFFSET075A may happen for an SQL statement that contains a predicate like 'T1.C1 IS DISTINCT FROM T2.C1' under OR, and T1.C1 has different nullability from T2.C1. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK80206) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12345) COMMENT (***Action for PM64633: See PM64633 APAR/PTF text for additional information about why a REBIND is necessary. PM64633 corrects a problem that ABEND04E RC00E70005 at the location DSNXGRDS.DSNXOSL:P105 could happen for a native SQL procedure when it satisfies all of the following conditions. 1. It contains a 'SELECT aggregate_function INTO sql_variable' statement. 2. The input column to the aggregate_function has different CCSID from the sql_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.). UK90173 HOLD DATA END UK90174 HOLD DATA START ++ HOLD(UK82678) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12345) COMMENT (***Action for PM71335: See PM71335 APAR/PTF text for additional information about why a REBIND is necessary. PM71335 corrects a problem that incorrect output could happen for an SQL statement that satisfies all of the following conditions: 1. It contains multiple table expressions or views. 2. One of the table expression or view contains UNION ALL. 3. A predicate contains a special register that can be pushed down to the table expression or view. To make this fix effective for a static application, it must be 1 rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK90174) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12345) COMMENT (***Action for PM62036: See PM62036 APAR/PTF text for additional information about why a REBIND is necessary. PM62036 corrects a problem of abend ABEND0C4 at DSNIDM DSNOTCSO 0D00 may occur for an SQL statement that references a nested inline table user defined function with LOB parameter data type and varchar literal constant argument. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK90174 HOLD DATA END UK90197 HOLD DATA START UK90197 HOLD DATA END UK90208 HOLD DATA START ++ HOLD(UK90208) SYS FMID(HDBAA10) REASON(DOC) DATE(12345) COMMENT (The DB2 z/OS for v10 SQL Reference description of the CURRENT TIME ZONE special register will be changed as follows: CURRENT TIME ZONE The CURRENT TIME ZONE special register specifies a value that contains the difference between UTC and local time at the current server, if the SESSION TIME ZONE special register has not been set. The data type is DECIMAL(6,0). The difference between UTC and local time at the current server is represented by a time duration (a decimal number in which the first two digits are the number of hours, the next two digits are the number of minutes, and the last two digits are the number of seconds). The number of hours is adjusted if necessary to fit in the range between -24 and 24 exclusive. Subtracting CURRENT TIME ZONE from a local time converts that local time to UTC. CURRENT TIMEZONE can be specified as an alternative to CURRENT TIME ZONE. Please note, the sentence "The number of hours is adjusted if necessary to fit in the range between -24 and 24 exclusive." was added.). UK90208 HOLD DATA END 1 UK90212 HOLD DATA START UK90212 HOLD DATA END UK90222 HOLD DATA START ++ HOLD(UK90219) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12346) COMMENT (***Action for PM73214: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM73214 corrects a problem of inaccurate cost estimation for a query when the query contains a view or table expression with UNION ALL. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK90222) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12346) COMMENT (***Action for PM72676: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM72676 corrects a problem of inaccurate cost estimation for a query when the query contains nested non-correlated subqueries. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK90222 HOLD DATA END UK90233 HOLD DATA START ++ HOLD(UK90233) SYS FMID(HDBAA10) REASON(DOC) DATE(12346) COMMENT (PM68894 provides support to print message DSNU714I when CHECK DATA SHRLEVEL CHANGE cannot generate a REPAIR statement because the base row is compressed. REPAIR statements are generated for some inconsistencies when AUXERROR/LOBERROR/XMLERROR INVALIDATE is specified with SHRLEVEL CHANGE. DSNU714I csect-name CHECK DATA CANNOT GENERATE A REPAIR STATEMENT FOR A COMPRESSED ROW OF TABLE table_name Explanation CHECK DATA detected an inconsistency on a compressed row in table table_name. SHRLEVEL CHANGE and AUXERROR INVALIDATE, 1 LOBERROR INVALIDATE, or XMLERROR INVALIDATE was specified, but a REPAIR statement cannot be generated for a compressed row. System action The CHECK DATA utility continues processing. System programmer response See previous message to determine the inconsistency. Consider modifying the CHECK DATA utility to use SHRLEVEL REFERENCE or the REPORT option of AUXERROR, LOBERROR, or XMLERROR. Severity 4 (error)). ++ HOLD(UK90233) SYS FMID(HDBAA10) REASON(ACTION) DATE(12346) COMMENT (The PTF for APAR PM68894 has modified CSECTs in both the DSNUT101 and DSNUTILA load modules, affecting the utility batch and DB2 DBM1 address spaces. After the PTF has been applied, it will become active in the utility batch address space immediately upon job submission, while it will not take effect in the DB2 DBM1 address space until DB2 is stopped and started. The PTF does not have to be applied to all members in a data sharing environment simultaneously, and may be staged across each member. However, until the fix is active in both the utility batch and DB2 DBM1 address spaces, the function the PTF provides will not be available.). UK90233 HOLD DATA END UK90266 HOLD DATA START UK90266 HOLD DATA END UK90276 HOLD DATA START UK90276 HOLD DATA END UK90311 HOLD DATA START ++ HOLD(UK90311) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12348) 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. 1 To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK90311 HOLD DATA END UK90325 HOLD DATA START ++ HOLD(UK90325) SYS FMID(HDBAA10) REASON(DOC) DATE(12348) COMMENT (***Documentation for PM43293: See PM43293 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. New Subsystem Parameter, MAXCONQN, for DSN6FAC Macro --------------------------------------------------------------- MAXCONQN=(OFF|ON|1<=numeric-value<=19999) OFF - This is the default value for the parameter which means that no attempt will be made to close a client connection. ON - The support is enabled and the value used will be the value specified for MAXDBAT. Thus, a client connection will only be closed when MAXDBAT has been reached and MAXDBAT is also the depth of the connection request queue. However, if CONDBAT is reached first, a client connection will only be closed due to CONDBAT being reached. numeric-value - This is a positive integer less than or equal to the maximum value that can be specified for MAXDBAT. New Subsystem Parameter, MAXCONQW, for DSN6FAC Macro --------------------------------------------------------------- MAXCONQW=(OFF|ON|5<=numeric-value<=3600) OFF - This is the default value for the parameter which means that no attempt will be made to close a client connection. ON - The support is enabled and the value used will be the value specified for IDTHTOIN. numeric-value - This is a positive integer which represents a time duration in seconds which must be greater than or equal to 5 seconds and less than or equal to 3600 seconds (one hour). New Reason Codes --------------------------------------------------------------- 1 00D3003E Explanation: The connection/thread is running under KEEPDYNAMIC Refresh rules and it has been determined that the connection/thread has been actively in use for over one hour. This determination occurs only after a unit-of-work has completed and no other pending resource usages have caused the thread to stay active with its connection other than the use of KEEPDYNAMIC(YES) bound packages. This is a normal condition if the client has connected to DB2 indicating that it is either sysplex workload balancing enabled or seamless failover enabled and using packages bound with KEEPDYNAMIC(YES). System action: The connection/thread has been terminated. User response: The IBM Data Server Driver for JAVA and the IBM Data Server Driver for ODBC/CLI/.NET support the KEEPDYNAMIC Refresh rules such that when a client socket has been closed from the server, it will then attempt a reconnect back to the same server and mark all statements currently managed under the connection as now requiring a prepare. 00D3003F Explanation: The connection/thread is running under KEEPDYNAMIC Refresh rules and it has been determined that the connection/thread has been idle for over 20 minutes. This determination occurs only after a unit-of-work has completed and no other pending resource usages have caused the thread to stay active with its connection other than the use of KEEPDYNAMIC(YES) bound packages. This is a normal condition if the client has connected to DB2 indicating that it is either sysplex workload balancing enabled or seamless failover enabled and using packages bound with KEEPDYNAMIC(YES). System action: The connection/thread has been terminated. User response: The IBM Data Server Driver for JAVA and the IBM Data Server Driver for ODBC/CLI/.NET support the KEEPDYNAMIC Refresh rules such that when a client socket has been closed from the server, it will then attempt a reconnect back to the same server and mark all statements currently managed under the connection as now requiring a prepare. 00D31053 Explanation: The CONN QUEUED MAX DEPTH subsystem parameter (ZPARM MAXCONQN) has been reached and the connection waiting the longest in the connection request queue has had its TCP/IP socket closed. 1 System action: The client is no longer connected to DB2. User response: This reason code may indicate that a slowdown in the processing of requests has or is occurring. Since MAXDBAT has also been reached, actions should be taken to either increase MAXDBAT or determine the cause of the slowdown of the request processing. 00D31054 Explanation: DB2 has found that a connection request has been waiting for a period of time in excess of the CONN QUEUED MAX WAIT subsystem parameter (ZPARM MAXCONQW) and the connection has had its TCP/IP socket closed. System action: The client is no longer connected to DB2. User response: This reason code may indicate that a slowdown in the processing of requests has or is occurring. Since MAXDBAT would have also been reached, actions should be taken to either increase MAXDBAT or determine the cause of the slowdown of the request processing. New Messages to Display Client Connection Thresholds --------------------------------------------------------------- DSNL074I NUMBER OF CLIENT CONNECTIONS HAS EXCEEDED nn% OF THE MAXIMUM ALLOWED Explanation: This message will be issued when the number of increasing client connections has exceeded either 80% or 90% relative to the MAX REMOTE ACTIVE subsystem parameter (ZPARM CONDBAT). The health value reported to WLM will also be decreased to 50% (when nn is 80) or 25% (when nn is 90) of the calculated DB2 system health value. This is done in an effort to potentially allow remote client systems to direct work to other members which have more resources to process the work. This adjustment will continue until the number of client connections decreases below either the 80% or 90% threshold. The message can only be issued when the DB2 subsystem is a member of a data sharing group and DB2 was started with DDF THREADS set to INACTIVE. System action: Processing continues normally. System Programmer response: Depending on the subsystem's current CONDBAT subsystem parameter value, the display of this message could be of some concern. An investigation into why the number of connections has exceeded the indicated threshold should be undertaken. If the subsystem could handle more inbound connections, a new subsystem parameters module with a higher CONDBAT value can be brought online to alleviate the reduction of the subsystem's health value. As long as DSNL075I is displayed indicating that the number of connections has then dropped 1 below one of the thresholds, no further action should be necessary. However, if the percentage still keeps increasing, one should use the -DISPLAY LOCATION command to find out which client locations are increasing their connections to this DB2 subsystem. A determination should then be made as to whether the cause is due to normal workload growth which would require no more actions are to be taken other than continued monitoring or due to an exception condition which would require further investigation to determine the reason for the exception. DSNL075I NUMBER OF CLIENT CONNECTIONS NO LONGER EXCEEDS nn% OF THE MAXIMUM ALLOWED Explanation: This message will be issued when the number of client connections has decreased such that either the 80% or 90% threshold relative to the MAX REMOTE ACTIVE subsystem parameter (ZPARM CONDBAT) is no longer being exceeded. The health value reported to WLM will also be increased to 50% (when nn is 90) or 100% (when nn is 80) of the calculated DB2 system health value. The message can only be issued when the DB2 subsystem is a member of a data sharing group and DB2 was started with DDF THREADS set to INACTIVE. System action: Processing continues normally. System Programmer response: The display of this message indicates that conditions have improved at the subsystem. No further action is necessary. New Message to Display Configured MAXCONQN and MAXCONQW Values --------------------------------------------------------------- The new DSNL091I message, which is issued as a result of a -DISPLAY DDF command, is being added to display the currently specified MAXCONQN and MAXCONQW values. The message will only be displayed if the DB2 subsystem is a member of a data sharing group. DSNL091I MCONQN=mconqn MCONQW=mconqw Explanation: This message is issued as a result of the -DISPLAY DDF DETAIL command. This message displays some of the subsystem parameter information associated with DDF when the subsystem is a member of a data sharing group. mconqn The depth of the queue of connections (as determined by the value specified for the MAXCONQN parameter of the DSN6FAC macro) waiting for a DBAT to process the next request. When this value is reached, and MAXDBAT has also been reached, DDF will close the oldest waiting client connection. A value of 0 will only be displayed for this parameter if DB2 had been started with the CMTSTAT parameter of the DSN6FAC macro was set to ACTIVE. 1 mconqw The maximum amount of time (as determined by the value specified for the MAXCONQW parameter of the DSN6FAC macro) that a client connection will wait for a thread to process its request. DDF will attempt to close any client connection which has been waiting in excess of this time. A value of 0 will only be displayed for this parameter if DB2 had been started with the CMTSTAT parameter of the DSN6FAC macro was set to ACTIVE. System action: Processing continues normally. New Message to Display Closed Connection Counts and WLM Health --------------------------------------------------------------- The DSNL094I message, which is issued as a result of a -DISPLAY DDF DETAIL command, is being added to show the DB2 system health value as last reported to WLM and the number of client connections which were closed due to either MAXCONQN and/or MAXCONQW being exceeded. This message will only be displayed if the DB2 subsystem is a member of a data sharing group. DSNL094I WLMHEALTH=hlth CLSDCONQN=clsdn CLSDCONQW=clsdw Explanation: This message is issued as a result of the -DISPLAY DDF DETAIL command. The message will only be displayed when the subsystem is a member of a data sharing group. hlth The DB2 system health value (0 to 100) as last reported to WLM. clsdn The number of client connections that have been closed due to MAXCONQN subsystem parameter being reached since DDF was last started. clsdw The number of client connections that have been closed due to MAXCONQW subsystem parameter being reached since DDF was last started. System action: Processing continues normally. Sample -DISPLAY DDF DETAIL Output with New Messages --------------------------------------------------------------- DSNL080I -DB2A DSNLTDDF DISPLAY DDF REPORT FOLLOWS: DSNL081I STATUS=STARTD DSNL082I LOCATION LUNAME GENERICLU DSNL083I STLEC1 USIBMSY.SYEC1A USIBMSY.SYEC1GLU DSNL084I TCPPORT=446 SECPORT=0 RESPORT=5001 IPNAME=-NONE DSNL085I IPADDR=::9.30.85.32 DSNL086I SQL DOMAIN=dune.vmec.svl.ibm.com DSNL086I RESYNC DOMAIN=caladan.vmec.svl.ibm.com DSNL089I MEMBER IPADDR=::9.30.85.33 DSNL090I DT=I CONDBAT= 64 MDBAT= 16 DSNL091I MCONQN= 16 MCONQW= 0 DSNL092I ADBAT= 0 QUEDBAT= 0 INADBAT= 0 CONQUED= 0 1 DSNL093I DSCDBAT= 0 INACONN= 0 DSNL094I WLMHEALTH=100 CLSDCONQN= 0 CLSDCONQW= 1 DSNL100I LOCATION SERVER LIST: DSNL101I WT IPADDR IPADDR DSNL102I 10 ::9.30.85.34 DSNL102I 10 ::9.30.85.33 DSNL102I 10 ::9.30.85.36 DSNL102I 10 ::9.30.85.37 DSNL102I 10 ::9.30.85.38 DSNL102I 10 ::9.30.85.39 DSNL099I DSNLTDDF DISPLAY DDF REPORT COMP Instrumentation Changes --------------------------------------------------------------- The following DDF system statistical values will now be maintained: DSNDQDST (Global DDF Activity): QDSTNCQC - Number of queued client connections which have had their TCP/IP sockets closed because MAXDBAT was reached and the number of inactive connections queued, waiting for a DBAT, has also reached MAXCONQN in number. The socket close will only be attempted in a member of a data sharing group. (This is an update to an existing field description) QDSTNCQW - Number of queued client connections which have had their TCP/IP sockets closed due to exceeding the time specified by MAXCONQW while waiting for a DBAT to process their next request. The socket close will only be attempted in a member of a data sharing group. DSNDQLST (Location activity): QLSTCNVT - Number of conversations that were terminated from this site to the remote site. This is not a new value but the description will be changed to have the phrase "(meaningful at the AR)" removed. This value at a DDF server will be updated when MAXCONQN or MAXCONQW causes a connection to be teminated. This statistical value is reported for both individual locations as well as DRDA REMOTE LOCS. DSNDQWPZ (Subsystem Initialization Parameters): Fields QWP9MCONQN and QWP9MCONQW are added to trace the MAXCONQN and MAXCONQW settings. The IFCID 106 formatter stored procedures, SYSPROC.DSNWZP and SYSPROC.ADMIN_INFO_SYSPARM are updated to report the MAXCONQN and MAXCONQW settings.). ++ HOLD(UK90325) SYS FMID(HDBAA10) REASON(ACTION) DATE(12348) COMMENT (***Actions for PM43293: This PTF adds two online changeable DB2 subsystem parameters, MAXCONQN and MAXCONQW, to the DSN6FAC macro. * MAXCONQN: Maximum depth of the connection request queue of connections waiting for a DBAT to process 1 a request. OFF indicates that the queue of is limited only by CONDBAT. ON says that the queue depth will default to MAXDBAT. Minimum for a numeric value is 1 and max is MAXDBAT maximum value. The default is OFF. * MAXCONQW: Maximum time for a connection to wait for a DBAT to process its request. OFF says that the connection will wait indefinitely. ON says that the time value specified for IDTHTOIN will be used. However, if IDTHTOIN is 0, then a warning mnote will be issued stating that MAXCONT is set to OFF because IDTHTOIN is 0. The minimum numeric value will be 5 and the maximum can be 3600 (seconds). The default is OFF. If you have already installed or migrated to this version of DB2 you need to take the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Update your customized copy of job DSNTIJUZ (3) Update private copies of the DSNTIDxx CLIST input member Detailed guidance for these actions follows: ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members ---------------------------------------------------------------- ==> This action is recommended for all customers This PTF modifies CLIST member DSNTINST in the SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your 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 parameters MAXCONQN=, where is OFF, ON, or an integer from 1 to 19999, to the invocation of the DSN6FAC macro. Make sure to add a continuation character in column 72 if needed. If you do not add MAXCONQN here the value will be set to the default of OFF when you assemble the DSNZPxxx module. * Add the keyword parameters MAXCONQW=, where is OFF, ON, or an integer from 5 to 3600, to the invocation of the DSN6FAC macro. Make sure to add a continuation character in column 72 if needed. If you do not add MAXCONQW here the value will be set to the default of OFF 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 1 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 entries for MAXCONQN and MAXCONQW to the CLIST default input member in the SDSNSAMP target library. You need to add these entries to all private copies of your CLIST output DSNTIDxx member. In each such copy, add the following line: MAXCONQN CHARR M NONE NONE MAXCONQW CHARR M NONE NONE Change to the value you specified for MAXCONQN in step (2), above. Change to the value you specified for MAXCONQW in step (2), above. If you do not add MAXCONQN to a DSNTIDxx member, the DB2 installation CLIST will assume MAXCONQN=OFF when run with that member as input. If you do not add MAXCONQW to a DSNTIDxx member, the DB2 installation CLIST will assume MAXCONQW=OFF when run with that member as input.). UK90325 HOLD DATA END UK90349 HOLD DATA START UK90349 HOLD DATA END UK90366 HOLD DATA START UK90366 HOLD DATA END UK90369 HOLD DATA START ++ HOLD(UK90369) SYS FMID(HDBAA10) REASON(ACTION) DATE(12352) COMMENT (This PTF enhances DB2 XMLMODIFY function performance on XML column with XML type modifier. This enhancement requires z/OS R12 or later, or R11 PTF UA59082, or R10 PTF UA59081. *************************************************************** Actions for customers who are preparing to install or migrate to DB2 V10: *************************************************************** If you apply this PTF before installing or migrating to DB2 V10, and you use z/OS R10 or z/OS R11 then before you can use the SYSPROC.XSR_COMPLETE stored procedure, you need to have applied z/OS R11 PTF UA59082 or R10 PTF UA59081. *************************************************************** Actions for customers who are already using DB2 V10: *************************************************************** If you have already installed or migrated to DB2 V10, and you use z/OS R10 or z/OS R11 1 and you use the SYSPROC.XSR_COMPLETE stored procedure then -before- applying this PTF verify that you have applied z/OS R11 PTF UA59082 or R10 PTF UA59081. If you have already installed or migrated to DB2 V10, the following actions are required after you apply this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Update customized copies of installation job DSNTIJMV (3) Update the DB2 Java WLM environment variable file (4) Rebind the packages(SYSXSR) that are updated as a result of installing this PTF (5) Refresh the WLM environment for SYSPROC.XSR_COMPLETE (6) re-run SYSPROC.XSR_COMPLETE to compile the schema into a new version of grammar These actions are detailed below: ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members *********************************************************** * This action is required for all DB2 V10 customers *********************************************************** This PTF modifies CLIST member DSNTINS1 in the prefix.SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your private copies of this CLIST. You may also want to move it to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. ---------------------------------------------------------------- (2) Update customized copies of installation job DSNTIJMV *********************************************************** * All DB2 V10 customers who use the SYSPROC.XSR_COMPLETE * stored procedure to perform XML schema validation need to * perform this step. *********************************************************** This PTF modifies installation job DSNTIJMV in the prefix.SDSNSAMP target library. After you apply this PTF, you need to update your customized copy of DSNTIJMV and remove the gxljosrgImpl.jar from the code to generate the DB2 Java WLM environment variable file. Proceed as follows: (a) Edit your customized DSNTIJMV job and locate job step DSNTIJR (b) Remove the words "and gxljosrgImpl.jar" from the comment block that precedes this job step (c) Remove the HFS path and file name for gxljosrgImpl.jar from the CLASSPATH variable. For example, remove: :/usr/include/java_classes/gxljosrgImpl.jar Note that the path and file name may be wrapped onto more than one line. (d) Save your changes. ---------------------------------------------------------------- (3) Update the DB2 Java WLM environment variable file *********************************************************** * All DB2 V10 customers who use the SYSPROC.XSR_COMPLETE 1 * stored procedure to perform XML schema validation need to * perform this step. *********************************************************** This PTF eliminates the need to include the gxljosrgImpl.jar file in the DB2 Java WLM environment variable file. After you apply this PTF, you need to update your copy of this file and remove the HFS path and file name for gxljosrgImpl.jar from the CLASSPATH variable as discussed in step (2)(c), above. If you use the DB2 Java WLM environment variable file provided by job DSNTIJMV, the name and location of the file that contains this variable is given by the PATH setting of the SYSUT2 DD statement in job step DSNTIJR of your customized copy of job DSNTIJMV. --------------------------------------------------------------- (4) Rebind the packages that are updated as a result of installing this PTF. *********************************************************** * All DB2 V10 customers who use the SYSPROC.XSR_COMPLETE * stored procedure to perform XML schema registration need * to perform this step. *********************************************************** Users need to process the following commands to bind the packages that are updated as a result of installing this PTF BIND PACKAGE(SYSXSR) MEMBER(DSNNVXCV) - ACTION(ADD) ISO(CS) CURRENTDATA(YES) ENCODING(UNICODE) - LIBRARY('.SDSNDBRM') Note: is the data set prefix of your DB2 10 target libraries ------------------------------------------------------------- (5) Refresh the WLM environment for SYSPROC.XSR_COMPLETE *********************************************************** * All DB2 V10 customers who use the SYSPROC.XSR_COMPLETE * stored procedure to perform XML schema registration need * to perform this step. *********************************************************** Issue the following VARY MVS command from the console to refresh the WLM application environment VARY WLM,APPLENV=,REFRESH where is the name of the WLM application environment for running the SYSPROC.XSR_COMPLETE stored procedure. ------------------------------------------------------------- (6) Re-run SYSPROC.XSR_COMPLETE to compile the schemas into new version of grammar. ********************************************************** * All DB2 V10 customers who use the SYSPROC.XSR_COMPLETE s * procedure to perform XML schema registration need to * perform this step. 1 ********************************************************** Identify the schema name used in XML type modifier by executing the following statement: SELECT X.XSROBJECTNAME FROM SYSIBM.XSROBJECTS X, SYSIBM.SYSXMLTYPMSCHEMA T WHERE T.XSROBJECTID = X.XSROBJECTID;). ++ HOLD(UK65385) SYS FMID(HDBAA10) REASON(ACTION) DATE(12352) COMMENT (This APAR introduces a new environment variable JDBCSTD for the Java WLM SP environment. When set JDBCSTD=4, The Java SP environment will use JDBC 4.0 standard JCC driver. If JDBCSTD is not set or set as JDBCSTD=3, there will be no change in the behavior and JDBC 3.0 standard driver will be used. The JDBC 4.0 standard driver must be installed when running with JDBCSTD=4 set.). UK90369 HOLD DATA END UK90405 HOLD DATA START UK90405 HOLD DATA END UK90407 HOLD DATA START ++ HOLD(UK80018) SYS FMID(HDBAA10) REASON(ACTION) DATE(12351) COMMENT (PM62607 has modified REPAIR VERSIONS TABLESPACE to perform a DRAIN of all readers and writers on the table space. REPAIR VERSIONS updates the catalog and directory with version information for the table space when you have moved the object from one system to another using DSN1COPY with the OBIDXLAT option. REPAIR VERSIONS should be executed before any access to the data and should therefore block all readers and writers during the REPAIR operation.). UK90407 HOLD DATA END UK90408 HOLD DATA START UK90408 HOLD DATA END UK90447 HOLD DATA START ++ HOLD(UK90447) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12352) COMMENT (***Action for PM76452: See PM76452 APAR/PTF text for additional information about why a REBIND is necessary. PM76452 corrects a problem of ABEND0C7 RC0 at DSNXRDEC +59BC when a transitive closure predicate takes the form of column = host variable where the column is a not null decimal and a NULL value was sent in during execution time for the host variable. 1 To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK90447 HOLD DATA END UK90450 HOLD DATA START ++ HOLD(UK90450) SYS FMID(HDBAA10) REASON(ACTION) DATE(12352) COMMENT (DB2 code has been changed to drive an implicit CLOSE of any cursor whose query participates in either cpu or sysplex query parallelism that has encountered an sql error (negative sqlcode) condition from a child task. This implicit CLOSE action occurs on the OPEN or FETCH that first encounters the sql error from a child task. . As a result of this change, DB2 will now return SQLCODE -501 to applications that continue fetching rows after an sql error is encountered on a child task. This may require changes and modifications to application programs to expect this SQLCODE on FETCH requests. . Prior to this apar, it was possible for the application to continue fetching without encountering the subject abends, although the data and error information returned to the applications may not have been correct. . There is no change to the existing logic for cursors that encounter an sql error condition from the parent task.). UK90450 HOLD DATA END UK90516 HOLD DATA START ++ HOLD(UK71136) SYS FMID(HDBAA10) REASON(ACTION) DATE(12354) COMMENT (PM43179 corrects a problem of SQLCODE -171 during bind when using a file reference host variable of XML data type in an application prepared by the DB2 coprocessor. To make this fix effective for a static application, it must be recompiled after application of this PTF. If the application is a COBOL application, the following COBOL PTFs may also be required in addition to this PTF to fix the problem: UK71077 Enterprise COBOL for z/OS V3R4 H26L340 UK71078 Enterprise COBOL for z/OS V4R2 HADB420 UK71079 Enterprise COBOL for z/OS V4R1 HADB400 There is no problem applying the PTF for PM43179 without the COBOL PTF. However, the problem described in PM43179 will not be fixed for COBOL applications until the corresponding COBOL PTF is also applied.). UK90516 HOLD DATA END 1 UK90518 HOLD DATA START ++ HOLD(UK90518) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12354) COMMENT (***Action for PM74991: See PM74991 APAR/PTF text for additional information about why a REBIND is necessary. PM74991 corrects a problem of choosing a poorly performing access path for queries containing predicates on columns where frequency or histogram statistics have been collected. The predicate is a range predicate with parameter marker (or parameter markers) or host variable (or host variables), or the predicate refers to a subquery (except an EQUAL or NOT EQUAL 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.). UK90518 HOLD DATA END UK90524 HOLD DATA START ++ HOLD(UK90524) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12355) COMMENT (***Action for PM71713: A rebind is necessary to rebuild package and/or plan structures based on the changes in this APAR. PM71713 corrects a problem that the DB2 optimizer may select an inefficient access path while a multi-index access is more efficient. 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.). UK90524 HOLD DATA END UK90533 HOLD DATA START UK90533 HOLD DATA END UK90536 HOLD DATA START UK90536 HOLD DATA END UK90550 HOLD DATA START UK90550 HOLD DATA END 1UK90561 HOLD DATA START UK90561 HOLD DATA END UK90599 HOLD DATA START UK90599 HOLD DATA END UK90605 HOLD DATA START UK90605 HOLD DATA END UK90618 HOLD DATA START UK90618 HOLD DATA END UK90676 HOLD DATA START UK90676 HOLD DATA END UK90679 HOLD DATA START ++ HOLD(UK90679) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(12366) COMMENT (***Action for PM61099: See PM61099 APAR/PTF text for additional information about why a REBIND is necessary. PM61099 enhances the row and column access control rules and the following are the affected statements: 1. TRUNCATE statement To truncate a row access control activated table, the authorization ID of the TRUNCATE statement must be the owner of the subject table, or must have one of the system authorities that are currently listed in the existing privilege set, with the exception of the SYSCTRL authority: In addition, if the row access control is activated for a table, the privilege set must include at least one of the following privileges (SQLCODE -551): . Ownership of the table . DBADM authority for the database . SYSADM authority . System DBADM If the authorization ID of the statement has the required privilege, all rows in the table will be truncated even though the table is activated for the row access control. See an example in the PROBLEM SUMMARY section. 1 2. Insert or update operation in the INSERT, UPDATE, or MERGE statement When the source value of a new row references a column that has a mask, the masked value is used to derive the value for the new row. If the target table is also activated for the column access control, the authorization ID that issues the insert or update operation must be allowed by the source column mask to mask the source column value to itself (in other words, the authorization ID must be allowed by the source column mask to see the column value in order to retrieve it for the insert or update operation). If the source column mask does not allow the authorization ID to mask the column value to itself, an error will be returned at run time (SQLCODE -20478 reason code 30). SQLCODE -20478 reason code 30 is to ensure the source value of an insert or update operation is masked to the same value as the column value. See an example in the PROBLEM SUMMARY section. 3. Aggregate function with the DISTINCT keyword If DISTINCT is specified in an aggregate function, with the exception of the MAX and MIN aggregate function, the argument of the function must not reference a columnh with a column mask (SQLCODE -20478 reason code 26). See an example in the PROBLEM SUMMARY section. 4. User-defined function with the MODIFIES SQL DATA option Such a UDF must not be referenced in a row permission or column mask definition (SQLCODE -20474 reason code 4). To determine which table or column has the row or column access control, use SYSIBM.SYSTABLES.CONTROL column to identify which type of the access control has been activated for the table, and the SYSIBM.SYSCOLUMNS.CONTROL_ID column to determine which column mask is in effect, then use SYSIBM.SYSDEPENDENCIES to determine whether additional objects are affected. For the static applications, to determine which package is affected, search the SYSIBM.SYSPACKDEP on those affected objects. To make this fix effective for a static application, it must be rebound after application of this PTF. Use the above suggested ways of querying the catalog tables to determine which, if any, applications could be affected by this change.). ++ HOLD(UK90679) SYS FMID(HDBAA10) REASON(ACTION) DATE(12366) COMMENT (This PTF introduces the following enhancements to the row and 1 column access control and the following statements are affected: 1. TRUNCATE statement To truncate a row access control activated table, the authorization ID of the TRUNCATE statement must be the owner of the subject table, or must have one of the system authorities that are currently listed in the existing privilege set, with the exception of the SYSCTRL authority: In addition, if the row access control is activated for a table, the privilege set must include at least one of the following privileges (SQLCODE -551): . Ownership of the table . DBADM authority for the database . SYSADM authority . System DBADM If the authorization ID of the statement has the required privilege, all rows in the table will be truncated even though the table is activated for the row access control. See an example in the PROBLEM SUMMARY section. 2. Insert or update operation in the INSERT, UPDATE, or MERGE statement When the source value of a new row references a column that has a mask, the masked value is used to derive the value for the new row. If the target table is also activated for the column access control, the authorization ID that issues the insert or update operation must be allowed by the source column mask to mask the source column value to itself (in other words, the authorization ID must be allowed by the source column mask to see the column value in order to retrieve it for the insert or update operation). If the source column mask does not allow the authorization ID to mask the column value to itself, an error will be returned at run time (SQLCODE -20478 reason code 30). SQLCODE -20478 reason code 30 is to ensure the source value of an insert or update operation is masked to the same value as the column value. See an example in the PROBLEM SUMMARY section. 3. Aggregate function with the DISTINCT keyword If DISTINCT is specified in an aggregate function, with the exception of the MAX and MIN aggregate function, the argument of the function must not reference a column with a column mask (SQLCODE -20478 reason code 26). 1 See an example in the PROBLEM SUMMARY section. 4. User-defined function with the MODIFIES SQL DATA option Such a UDF must not be referenced in a row permission or column mask definition (SQLCODE -20474 reason code 4). To determine which table or column has the row or column access control, use SYSIBM.SYSTABLES.CONTROL column to identify which type of the access control has been activated for the table, and the SYSIBM.SYSCOLUMNS.CONTROL_ID column to determine which column mask is in effect, then use SYSIBM.SYSDEPENDENCIES to determine whether additional objects are affected. For the static applications, to determine which package is affected, search the SYSIBM.SYSPACKDEP on those affected objects. If a row permission or column mask references a user-defined function with the MODIFIES SQL DATA option, the row permission or column mask must be dropped and recreated with the proper modification.). UK90679 HOLD DATA END UK90691 HOLD DATA START UK90691 HOLD DATA END UK90705 HOLD DATA START UK90705 HOLD DATA END UK90706 HOLD DATA START UK90706 HOLD DATA END UK90730 HOLD DATA START UK90730 HOLD DATA END UK90749 HOLD DATA START UK90749 HOLD DATA END UK90752 HOLD DATA START UK90752 HOLD DATA END UK90753 HOLD DATA START UK90753 HOLD DATA END UK90757 HOLD DATA START 1UK90757 HOLD DATA END UK90758 HOLD DATA START UK90758 HOLD DATA END UK90762 HOLD DATA START ++ HOLD(UK90762) SYS FMID(HDBAA10) REASON(ACTION) DATE(13007) COMMENT (To enable the new functionality, add the following DD statement to the WLM startup JCL: //NOUNDUB DD DUMMY This indicates that in this WLM environment, if there is a USS process dubbed to a PROGRAM TYPE SUB stored procedure or UDF task, the process will not be undubbed at the end of the routine's execution. Use caution when enabling this option. This option affects all routine executions in the WLM environment. This option should only enabled for routines where there is conscious intent to keep USS processes across routine executions. The clean up of USS resources of the process such as file handles, pipes, sockets, etc. must be handled correctly by the routine. Please also note that any subsequent execution on the MVS task can be from any of the routines queued for that WLM environment. Therefore use of this option should be isolated from WLM environments where routines run that have not been verified to clean up resources appropriately.). UK90762 HOLD DATA END UK90769 HOLD DATA START UK90769 HOLD DATA END UK90798 HOLD DATA START UK90798 HOLD DATA END UK90803 HOLD DATA START ++ HOLD(UK90803) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13008) COMMENT (***Action for PM74746: See PM74746 APAR/PTF text for additional information about why a REBIND is necessary. PM74746 corrects a bind time problem that can result in an ABEND0C6-6 at location DSNXVCDF+27B or +27D for a query referencing a large complex view that has a UNION ALL and decimal arithmetic. 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 1 affected by this change.). ++ HOLD(UK90803) SYS FMID(HDBAA10) REASON(ACTION) DATE(13008) COMMENT (Please note that the following action will need to be performed along with applying the ptf for this apar. Any nested views that are created with the symptoms mentioned in the apar description will need to be dropped and recreated.). UK90803 HOLD DATA END UK90805 HOLD DATA START UK90805 HOLD DATA END UK90809 HOLD DATA START ++ HOLD(UK90809) SYS FMID(HDBAA10) REASON(ACTION) DATE(13008) COMMENT (***Action for PM73636 (V10) This PTF also adds a new option, 99, to an existing DB2 subsystem parameter DSN6SPRM.QUERY_ACCEL_OPTIONS, which is used to specify additional types of SQL queries to be included in query offloading. It previously had these four options: * NONE: Query offloading is restricted to the standard SQL statements ==> This is the default setting. * 1 : The queries that include data encoded by multi-byte character set EBCDIC encoding scheme are not blocked from executing on IDAA. See APAR PM60170 for more information about this option. * 2 : For INSERT statements where a fullselect specifies the data to be inserted, the fullselect part is not blocked from executing on IDAA although the data operated on by the fullselect may not be current in IDAA. The fullselect will be considered for offload as if it were a stand-alone statement, except that DB2 will not offload the fullselect if the target table of the INSERT has a different encoding scheme than any tables referenced in the fullselect. * 3 : The queries that include DB2 byte-based string functions on data encoded by multi-byte character sets encoding schemes (like Unicode) are not blocked from executing on IDAA although IDAA supports only character-based string functions. If the data on which the string function is specified contains only single-byte characters, executing the function on IDAA will return the same result as executing the function on DB2 irrespective of what encoding scheme is used for the data. However, if the data contains multi-byte characters, the results will not be the same. 1 The new option, 99, is for serviceability only and you should not use it except when advised to do so by IBM service. NONE remains the default setting for QUERY_ACCEL_OPTIONS and is mutually exclusive from all other settings. The other options can be specified individually or together. Examples: QUERY_ACCEL_OPTIONS=NONE QUERY_ACCEL_OPTIONS=1 QUERY_ACCEL_OPTIONS=3 QUERY_ACCEL_OPTIONS=(1,2) QUERY_ACCEL_OPTIONS=(1,3) QUERY_ACCEL_OPTIONS=(2,99) No action is required after applying this PTF unless you have been directed to use option 99 of DSN6SPRM.QUERY_ACCEL_OPTIONS. In that case, update your customized copy of job DSNTIJUZ as follows: * Add or update the keyword parameter QUERY_ACCEL_OPTIONS= where is 99 or a list form such as (1,2,3,99) to the invocation of the DSN6SPRM macro. Make sure to add a continuation character in column 72 if needed. If your DSNTIJUZ job does not include QUERY_ACCEL_OPTIONS, that parameter will be set to the default of NONE when you assemble the DSNZPxxx module. * Run the first two steps of the DSNTIJUZ job you modified. * After the job completes, you must either use the -SET SYSPARM command or stop and restart DB2 for the change to take effect. If you added or modified the setting for QUERY_ACCEL_OPTIONS, you need to add or update the entry for it in all private copies of your CLIST output DSNTIDxx member. In each such copy, add or update the following line: QUERY_ACCEL_OPTIONS CHAR M NONE NONE Change to the value you specified for QUERY_ACCEL_OPTIONS above in DSNTIJUZ.). UK90809 HOLD DATA END UK90814 HOLD DATA START UK90814 HOLD DATA END UK90816 HOLD DATA START UK90816 HOLD DATA END UK90818 HOLD DATA START UK90818 HOLD DATA END UK90830 HOLD DATA START ++ HOLD(UK90830) SYS FMID(HDBAA10) REASON(MULTSYS) DATE(13009) 1 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.). UK90830 HOLD DATA END UK90843 HOLD DATA START UK90843 HOLD DATA END UK90881 HOLD DATA START UK90881 HOLD DATA END UK90882 HOLD DATA START UK90882 HOLD DATA END UK90884 HOLD DATA START UK90884 HOLD DATA END UK90892 HOLD DATA START UK90892 HOLD DATA END UK90928 HOLD DATA START UK90928 HOLD DATA END UK90930 HOLD DATA START UK90930 HOLD DATA END UK90938 HOLD DATA START ++ HOLD(UK90938) SYS FMID(HDBAA10) REASON(DOC) DATE(13014) COMMENT (See PM71153 APAR/PTF text for additional information. PM71153 introduces the following changes to externals: SQL Reference PREPARE Notes Untyped parameter marker 1 Table. Untyped parameter marker usage in expressions (including select list, CASE, and VALUES) +------------------------------------+---------------------+ | | | | Location of untyped | Data type (or error | | parameter marker | if not supported) | | | | +------------------------------------+---------------------| | | | | A labeled duration in a datetime | DECIMAL(15,0) | | expression (the portion of a | | | labeled duration that indicates | | | the type of units cannot be a | | | parameter marker). | | | | | +------------------------------------+---------------------| | | | | A labeled duration in a datetime | DECIMAL(27,12) | | expression with a type unit of | | | SECONDS (the portion of a labeled | | | duration that indicates the type | | | of units cannot be a parameter | | | marker). | | | | | +------------------------------------+---------------------+). UK90938 HOLD DATA END UK90961 HOLD DATA START UK90961 HOLD DATA END UK90964 HOLD DATA START UK90964 HOLD DATA END UK91020 HOLD DATA START UK91020 HOLD DATA END UK91023 HOLD DATA START UK91023 HOLD DATA END UK91061 HOLD DATA START UK91061 HOLD DATA END UK91065 HOLD DATA START UK91065 HOLD DATA END UK91084 HOLD DATA START UK91084 HOLD DATA END 1 UK91144 HOLD DATA START UK91144 HOLD DATA END UK91166 HOLD DATA START UK91166 HOLD DATA END UK91168 HOLD DATA START UK91168 HOLD DATA END UK91189 HOLD DATA START UK91189 HOLD DATA END UK91203 HOLD DATA START UK91203 HOLD DATA END UK91209 HOLD DATA START ++ HOLD(UK91209) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13025) COMMENT (A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM74461 corrects a problem of ABEND0C4 RC00000004 at DSNIWNRF OFFSET515E or DSNIWNRF OFFSET1EB6 for an UPDATE or DELETE statement with a subquery. When IN-list direct table access is chosen for an UPDATE or DELETE statement, this problem can occur. IN-list table access is indicated in the ACCESSTYPE column of the PLAN_TABLE as 'IN'. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK91209 HOLD DATA END UK91210 HOLD DATA START UK91210 HOLD DATA END UK91217 HOLD DATA START UK91217 HOLD DATA END UK91220 HOLD DATA START UK91220 HOLD DATA END UK91228 HOLD DATA START UK91228 HOLD DATA END 1 UK91247 HOLD DATA START UK91247 HOLD DATA END UK91264 HOLD DATA START UK91264 HOLD DATA END UK91268 HOLD DATA START UK91268 HOLD DATA END UK91288 HOLD DATA START UK91288 HOLD DATA END UK91315 HOLD DATA START UK91315 HOLD DATA END UK91321 HOLD DATA START UK91321 HOLD DATA END UK91330 HOLD DATA START UK91330 HOLD DATA END UK91332 HOLD DATA START UK91332 HOLD DATA END UK91338 HOLD DATA START ++ HOLD(UK91338) SYS FMID(HDBAA10) REASON(ACTION) DATE(13032) COMMENT ( ***Action for PM70914 (V10) This PTF modifies V10 premigration checkout job DSNTIJPM. If you maintain a customized copy of this job then after applying this PTF, you need to refresh it as follows: - Edit your customized copy of DSNTIJPM - Delete all lines and copy in the DSNTIJPM member from the prefix.SDSNSAMP library - Follow the directions in the job prolog to customize it your DB2 - If you are preparing to migrate to DB2 V10, run the updated customized job to check for conditions that may affect your migration.). ++ HOLD(UK81264) SYS FMID(HDBAA10) REASON(ACTION) DATE(13032) COMMENT (***Action for PM66043 (V10) This PTF modifies V10 premigration checkout job DSNTIJPM. If you maintain a customized copy of this job then after applying 1 this PTF, you need to refresh it as follows: - Edit your customized copy of DSNTIJPM - Delete all lines and copy in the DSNTIJPM member from the prefix.SDSNSAMP library - Follow the directions in the job prolog to customize it for your DB2 - If you are preparing to migrate to DB2 V10, run the updated customized job to check for conditions that may affect your migration.). ++ HOLD(UK76610) SYS FMID(HDBAA10) REASON(ACTION) DATE(13032) COMMENT (***Action for PM54699 (V10) This PTF modifies V10 premigration checkout job DSNTIJPM. If you maintain a customized copy of this job then after applying this PTF, you need to refresh it as follows: - Edit your customized copy of DSNTIJPM - Delete all lines and copy in the DSNTIJPM member from the prefix.SDSNSAMP library - Follow the directions in the job prolog to customize it for your DB2 - If you are preparing to migrate to DB2 V10, run the updated customized job to check for conditions that may affect your migration.). UK91338 HOLD DATA END UK91349 HOLD DATA START UK91349 HOLD DATA END UK91351 HOLD DATA START ++ HOLD(UK91351) SYS FMID(HDBAA10) REASON(DOC) DATE(13032) 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 1 Solutions Information Center (http://publib.boulder.ibm.com/infocenter/imzic) at a later date.). ++ HOLD(UK91351) SYS FMID(HDBAA10) REASON(ACTION) DATE(13032) 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). ++ HOLD(UK79842) SYS FMID(HDBAA10) REASON(ACTION) DATE(13032) 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.). UK91351 HOLD DATA END UK91433 HOLD DATA START UK91433 HOLD DATA END UK91435 HOLD DATA START UK91435 HOLD DATA END UK91442 HOLD DATA START UK91442 HOLD DATA END UK91449 HOLD DATA START UK91449 HOLD DATA END UK91454 HOLD DATA START ++ HOLD(UK83472) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13039) COMMENT (***Action for PM71964: See PM71964 APAR/PTF text for additional information about why a REBIND is necessary. PM71964 corrects a problem of incorrect output may occur for an insert with subquery statement that satisfied the following conditions: 1. The source of the insert statement has timestamp data type. 2. The target of the insert statement is a char or varchar column. 3. There is sort involved for the INSERT statement. 1 To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK91454) SYS FMID(HDBAA10) REASON(ACTION) DATE(13039) COMMENT (***Action for PM61811 (V10) This PTF adds a new DB2-supplied stored procedure, SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY, that can be used to facilitate replication of temporal data by disabling recording of temporal history for a system-period temporal table and by allowing values to be specified for special temporal column types of row-begin, row-end, and transaction-start-ID. This stored procedure is not intended for general use. It is intended to be used by products that enable DB2 replication. If you have already installed or migrated to DB2 10 for z/OS, you need to perform the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Copy updated DB2 installation panels to alternate libraries (3) Update your customized copy of job DSNTIJRT (4) Update private copies of the DSNTIDxx CLIST input member (5) Run installation job DSNTIJRT to install new objects Detailed guidance for these actions is given further below. ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members *********************************************************** * This action is required for all V10 customers *********************************************************** This PTF modifies CLIST members DSNTINST, DSNTINSV, and DSNTINS1 in the prefix.SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your private copies of these CLISTs. You may also want to move them to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. ---------------------------------------------------------------- (2) Copy updated DB2 installation panels to alternate libraries *********************************************************** * This action is required for all V10 customers who maintain * copies of the DB2 installation panels outside of SMP/E: *********************************************************** This PTF modifies the DB2 installation panel DSNTIPRL in the prefix.SDSNSPFP target library. If you keep the DB2 installation panels in a different library then after applying this PTF, you need to copy the updated panel to that library. ---------------------------------------------------------------- (3) Update customized copies of installation job DSNTIJRT 1 *********************************************************** * This action is required for all V10 customers *********************************************************** This PTF modifies installation job DSNTIJRT in the prefix.SDSNSAMP target library. After you apply this PTF, you need to add the following to the list of configuration control statements under the CFIGIN DD in your customized copy of DSNTIJRT: SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY GRANTTO() Notes: (a) Always leave a blank line between each configuration control statement (b) Inspect the new configuration control statement and change the GRANTTO parameter setting to the authorization ID(s) that need access to the new routine. (c) Do not specify a WLMENV parameter in the configuration control statement for SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY. ---------------------------------------------------------------- (4) Update private copies of the DSNTIDxx CLIST input member *********************************************************** * This action is required for all V10 customers *********************************************************** This PTF adds an entry to store values entered into the new field on installation panel DSNTIPRL: - RTL39ACL: GRANT EXECUTE setting for SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY You need to make the same changes to all private copies of your CLIST output (DSNTIDxx) member. In each such copy, add the following lines: RTL39ACL CHAR M NONE NONE Change to the setting you specified in action (3), above. ---------------------------------------------------------------- (5) Run installation job DSNTIJRT to install new objects *********************************************************** * This action is optional for V10 customers *********************************************************** After you have completed the above actions, run your updated copy of job DSNTIJRT to install and grant access to the new routine, SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY. 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. 1 ----------------------------------------------------------------). UK91454 HOLD DATA END UK91455 HOLD DATA START ++ HOLD(UK91455) SYS FMID(HDBAA10) REASON(ACTION) DATE(13037) COMMENT (***Action for PM72982 (V10) This PTF modifies DB2 installation program DSNTRIN to create the LOB table spaces in the DB2 XML Schema Registration (DSNXSR) database as LOGGED (by default) rather than NOT LOGGED. The affected table spaces are: SYSXSRA1, SYSXSRA2, SYSXSRA3, and SYSXSRA4. Use of the LOGGED attribute facilitates rollback and recovery by ensuring that changes made to the data in these table spaces are recorded in the DB2 log. This change has no effect on existing instances of these table spaces. If you have already created the DSNXSR database, you can alter these table spaces from NOT LOGGED to LOGGED by means of the following SQL statements: ALTER TABLESPACE DSNXSR.SYSXSRA1 LOGGED; ALTER TABLESPACE DSNXSR.SYSXSRA2 LOGGED; ALTER TABLESPACE DSNXSR.SYSXSRA3 LOGGED; ALTER TABLESPACE DSNXSR.SYSXSRA4 LOGGED; Because altering the logging attribute of a table space from NOT LOGGED to LOGGED can place the table space in COPY-pending status, you might need to copy the table spaces after completing this operation.). UK91455 HOLD DATA END UK91458 HOLD DATA START UK91458 HOLD DATA END UK91474 HOLD DATA START ++ HOLD(UK91474) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13038) COMMENT (***Action for PM59899: See PM59899 APAR/PTF text for additional information about why a REBIND is necessary. PM59899 corrects an incorrect output problem for MAX or MIN olap specifications. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK76936) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13038) COMMENT (***Action for PM57492: 1 See PM57492 APAR/PTF text for additional information about why a REBIND is necessary. PM57492 corrects an incorrect output problem where the join predicate involves graphic data and there is a derived predicate for inner table of a sort merge 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.). UK91474 HOLD DATA END UK91493 HOLD DATA START UK91493 HOLD DATA END UK91515 HOLD DATA START ++ HOLD(UK91515) SYS FMID(HDBAA10) REASON(ACTION) DATE(13039) COMMENT (After applying APAR PM76271, the users of spatial indexes should complete the following steps: (1) Check if the table has any empty spatial objects. Example: Run the following statement to check if the spatial table (SPATIALTABLE) has an empty spatial object for a column with the spatial data type (SPATIALCOLUMN). SELECT COUNT(*) FROM SPATIALTABLE WHERE DB2GSE.ST_NUMPOINTS(SPATIALCOLUMN) IS NULL; If the above select returns a value greater than 0 then continue with the second step: (2) Use DSN5SCLP with option drop_idx and create_idx to drop and create a new spatial index for that table.). UK91515 HOLD DATA END UK91525 HOLD DATA START UK91525 HOLD DATA END UK91533 HOLD DATA START UK91533 HOLD DATA END UK91534 HOLD DATA START ++ HOLD(UK90099) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13039) COMMENT (***Action for PM75775: See PM75775 APAR/PTF text for additional information about why a REBIND is necessary. 1 PM75775 corrects a problem of poor query performance for OPTIMIZE FOR 1 ROW when no sort and no join will be performed, and there is an index or indexes which contain all the columns in the predicates. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK91534) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13039) COMMENT (***Action for PM74945: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM74945 corrects a problem in which DB2 may choose an access path with an inefficient index used for the inner table of a nested loop join or hybrid join. To make this fix effective for a static application, it must be rebound with new zparm OPTIOPIN set to ENABLE after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK91534) SYS FMID(HDBAA10) REASON(ACTION) DATE(13039) COMMENT (***Actions for APAR PM74945 (V10) This PTF adds an online-changeable subsystem parameter in DSN6SPRM called OPTIOPIN that controls whether DB2 is to use an enhancement to more accurately estimate the cost of accessing the inner table of a join that involved index and data access. Valid settings are: - ENABLE : DB2 uses the improved costing formula ==> This is the default setting - DISABLE: DB2 does not use the improved costing formula If you have already installed or migrated to this version of DB2 you need to take the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Update your customized copy of job DSNTIJUZ (3) Update private copies of the DSNTIDxx CLIST input member (4) Rebind static applications that are affected by this change Detailed guidance for these actions follows: ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members ==> This action is required for all customers This PTF modifies CLIST member DSNTINST in the SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your copies of this CLIST. You may also want to move it to the 1 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 OPTIOPIN=, where is DISABLE or ENABLE, to the invocation of the DSN6SPRM macro. Make sure to add a continuation character in column 72 if needed. If you omit adding OPTIOPIN here, the value will be set to the default of ENABLE 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 OPTIOPIN to the CLIST default input members, DSNTIDXA and DSNTIDXB, in the SDSNSAMP target library. You need to add these entries to all private copies of your CLIST output DSNTIDxx member. In each such copy, add the following lines: OPTIOPIN CHAR M DISABLE ENABLE Change to the value you specified for OPTIOPIN in step (2), above. End of actions for APAR PM74945 (V10)). UK91534 HOLD DATA END UK91557 HOLD DATA START UK91557 HOLD DATA END UK91560 HOLD DATA START ++ HOLD(UK90047) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13042) COMMENT (***Action for PM76278: See PM76278 APAR/PTF text for additional information about why a REBIND is necessary. PM76278 corrects a problem of ABEND04E RC00C90101 at DSNGEPLC : 500D or 1 ABEND0C1 or ABEND0C4 at an unknown PSW due to a bad branch when running static DML workloads. 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.). UK91560 HOLD DATA END UK91561 HOLD DATA START UK91561 HOLD DATA END UK91563 HOLD DATA START ++ HOLD(UK91019) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13042) COMMENT (See PM78249 APAR/PTF text for additional information about why a REBIND is necessary. PM78249 corrects a problem that ABEND0C7 RC00000000 at the location DSNXGRDS DSNXRDEC OFFSET59BC could happen for an SQL statement that satisfies all of the following conditions: 1. It contains a table expression with UNION ALL. 2. It contains a HAVING predicate with scalar function ABS, SIGN, CEILING or ROUND. 3. The scalar function contains a SUM function in the input expression. 4. The SUM function is on a DECIMAL column of the table expression. 5. The DECARTH DECP value is DEC31 or 31, and the DECIMAL column is with precision 15. 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.). UK91563 HOLD DATA END UK91565 HOLD DATA START UK91565 HOLD DATA END UK91573 HOLD DATA START UK91573 HOLD DATA END UK91575 HOLD DATA START UK91575 HOLD DATA END UK91577 HOLD DATA START UK91577 HOLD DATA END 1 UK91605 HOLD DATA START ++ HOLD(UK91605) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13043) COMMENT (***Action for PM79953: See PM79953 APAR/PTF text for additional information about why a REBIND is necessary. PM79953 corrects a problem of an inefficient access path being chosen by DB2 for a SQL statement which satisfies the following conditions: 1.it contains a VALUE COMPARE predicate or a BETWEEN predicate with an arithmetic expression, 2.the arithmetic expression has a host variable or parameter marker and integer constant as its operators, 3.matching index access is possible for 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.). ++ HOLD(UK90447) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13043) COMMENT (***Action for PM76452: See PM76452 APAR/PTF text for additional information about why a REBIND is necessary. PM76452 corrects a problem of ABEND0C7 RC0 at DSNXRDEC +59BC when a transitive closure predicate takes the form of column = host variable where the column is a not null decimal and a NULL value was sent in during execution time for the 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.). UK91605 HOLD DATA END UK91608 HOLD DATA START UK91608 HOLD DATA END UK91643 HOLD DATA START ++ HOLD(UK91643) SYS FMID(HDBAA10) REASON(ACTION) DATE(13044) COMMENT (***Action for PM67402: This PTF corrects the following two problems : 1. ABEND04E RC00E70005 at DSNXGRDS DSNXEFDA M500 on a fetch statement. 2. Update of the wrong row with a UPDATE WHERE CURRENT 1 OF statement. Both problems occur if the previous SQL executed was a FETCH from a cursor which returned an error SQLCODE. The position of a cursor is unpredicatable after an error. DB2 has been fixed to close the cursor when a negative SQLCODE is returned while processing a predicate. Subsequent fetches from the cursor will return -501 SQLCODE. An UPDATE after the FETCH sqlcode will return a -507 SQLCODE. A CLOSE CURSOR after the FETCH sqlcode will return a -501 SQLCODE. DB2 10 for z/OS SQL Reference currently documents this behavior under the FETCH CURSOR SQL statement description. This apar adds another scenario where the cursor is closed. "CURSOR POSITION AFTER EXCEPTION CONDITION: If an error occurs during the execution of a fetch operation, the position of the cursor and the result of any later fetch is unpredictable. It is possible for an error to occur that makes the position of the cursor invalid, in which case the cursor is closed." Application code should handle a -501 SQLCODE on a FETCH or CLOSE CURSOR SQL statement if a previous error has occurred. UPDATE should also handle the -507 SQLCODE. A new reason (bullet 3) will be added to the list under the "Programmer response" for SQLCODE -501 : ============================================================= Programmer response Check for a previous SQL return code that might have closed the cursor. - Commit and rollback operations close cursors. - SQL return codes -404, -652, -679, -802, -901, -904,  -909, -910, -911, -913, and -952 might force the cursor to close. - An error SQLCODE during predicate processing closes the cursor. After the cursor is closed, any fetches or close cursor statements will receive SQLCODE -501. If no previous SQL return codes have been issued, correct the logic of the application program to ensure that the cursor is open at the time the FETCH or CLOSE statement is processed. ==============================================================). UK91643 HOLD DATA END UK91646 HOLD DATA START UK91646 HOLD DATA END UK91647 HOLD DATA START ++ HOLD(UK91647) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13056) COMMENT (A rebind is necessary for static applications to rebuild 1 access paths based on the changes in this APAR. APAR PM75619 corrects two problems. The first problem occurs on both V9 and V10. A query may produce incorrect output when a UNICODE table joins to an EBCDIC view which is a UNION ALL, and at the same time, when index-on-expression (IOE) is used for that query. The second problem occurs on V10 only. DB2 may not issue a REORG suggestion as it should. 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.). UK91647 HOLD DATA END UK91659 HOLD DATA START UK91659 HOLD DATA END UK91689 HOLD DATA START UK91689 HOLD DATA END UK91693 HOLD DATA START UK91693 HOLD DATA END UK91707 HOLD DATA START UK91707 HOLD DATA END UK91718 HOLD DATA START ++ HOLD(UK91718) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13046) COMMENT (***Action for PM80387: See PM80387 APAR/PTF text for additional information about why a REBIND is necessary. PM80387 corrects a problem of a storage overlay when the a query contains the following combination: 1. a CASE expression in a select list 2. an EXISTS predicate in the WHEN clause 3. a correlated reference inside the EXISTS predicate The known symptoms are: 1. ABEND04E LOC=DSNIBCTD.DSNSVSFB+0834 2. ABEND0C4 LOC=DSNXGRDS.DSNXOEXB+103A 3. ABEND04E LOC=DSNHCORE.DSNSVSFB+0844 4. ABEND04E LOC=DSNIDALC.DSNSVSFB+0854 5. ABEND04E LOC=DSNXEDC4.DSNSVSFB+0810 The storage overlay is a single bit alteration. The known 1 occurrences are: 1. eyecatcher 'TRLF' was modified to 'TRlF' 2. around offset '190'X before the eyecatcher 'PDA' There is a small chance of an incorrect output as the result of this storage overlay. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK91308) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13046) COMMENT (***Action for PM75883: See PM75883 APAR/PTF text for additional information about why a REBIND is necessary. PM75883 corrects a problem ofABEND04E at DSNIDM DSNOPUFF 5003 for an SQL statement that satisfies the following conditions: 1. The SQL statement contains a table expression or references to a view. 2. The view or table expression contains UNION/UNION ALL. 3. One branch of the UNION/UNION ALL select list references to a LOB column from a base table. The other branch of the UNION/UNION ALL select list references to a LOB column from a non base table. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK91718 HOLD DATA END UK91745 HOLD DATA START UK91745 HOLD DATA END UK91747 HOLD DATA START UK91747 HOLD DATA END UK91750 HOLD DATA START UK91750 HOLD DATA END UK91752 HOLD DATA START UK91752 HOLD DATA END UK91786 HOLD DATA START ++ HOLD(UK91786) SYS FMID(HDBAA10) REASON(ACTION) DATE(13049) COMMENT (This PTF updated DB2 code so that the DSNTIJTC job will update 1 the CCSID of the SYSIBM.SYSDUMMYA catalog table and the DSNDB06.SYSTSASC catalog table space. Applying this PTF will not correct the CCSID. Please do the following to ensure the CCSID is correct. For MIXED=YES specified in DSNHDECP: 1. Run query the get the CCSID: SELECT MIXED_CCSID FROM SYSIBM.SYSTABLESPACE WHERE DBNAME='DSNDB06' AND NAME='SYSTSASC'; 2. If the returned value does not match the value of AMCCSID specified in DSNHDECP, run the following utility: CATMAINT UPDATE UNLDDN CCSID Plans and packages that depend on the SYSIBM.SYSDUMMYA catalog table or the DSNDB06.SYSTSASC catalog table space will be invalidated after running the utility. For MIXED=NO specified (or by default) in DSNHDECP: 1. Run query the get the CCSID: SELECT SBCS_CCSID FROM SYSIBM.SYSTABLESPACE WHERE DBNAME='DSNDB06' AND NAME='SYSTSASC'; 2. If the returned value does not match the value of ASCCSID specified in DSNHDECP, run the following utility: CATMAINT UPDATE UNLDDN CCSID Plans and packages that depend on the SYSIBM.SYSDUMMYA catalog table or the DSNDB06.SYSTSASC catalog table space will be invalidated after running the utility.). UK91786 HOLD DATA END UK91791 HOLD DATA START UK91791 HOLD DATA END UK91796 HOLD DATA START UK91796 HOLD DATA END UK91805 HOLD DATA START UK91805 HOLD DATA END 1 UK91807 HOLD DATA START UK91807 HOLD DATA END UK91810 HOLD DATA START ++ HOLD(UK91810) SYS FMID(HDBAA10) REASON(ACTION) DATE(13050) COMMENT (The DB2 10 PTF for APAR PM78558 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.). UK91810 HOLD DATA END UK91812 HOLD DATA START UK91812 HOLD DATA END UK91827 HOLD DATA START UK91827 HOLD DATA END UK91829 HOLD DATA START UK91829 HOLD DATA END UK91830 HOLD DATA START UK91830 HOLD DATA END UK91831 HOLD DATA START UK91831 HOLD DATA END UK91836 HOLD DATA START ++ HOLD(UK91836) SYS FMID(HIZAA10) REASON(ACTION) DATE(13051) 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 1 IBM recommends that you install DB2 PTF UK77559 to fix HIPER APAR PM59756 before installing this PTF or other DB2 PTFs for RRSAF. PM59756 fixes a base DB2 V10 problem.). ++ HOLD(UK78783) SYS FMID(HIZAA10) REASON(ACTION) DATE(13051) COMMENT (This PTF requires either an ERLY code refresh or a re-IPL of zOS. To refresh, enter the following commands after applying the PTF: MODIFY LLA,REFRESH -STOP DB2 ... (only if the DB2 subsystem is started) -REFRESH DB2,EARLY -START DB2,PARM=... These commands are not necessary if you re-IPL after applying the PTF.). ++ HOLD(UK77171) SYS FMID(HIZAA10) REASON(ACTION) DATE(13051) COMMENT (This PTF requires either an ERLY code refresh or a re-IPL of zOS. To refresh, enter the following commands after applying the PTF: MODIFY LLA,REFRESH -STOP DB2 ... (only if the DB2 subsystem is started) -REFRESH DB2,EARLY -START DB2,PARM=... These commands are not necessary if you re-IPL after applying the PTF. This PTF requires either an ERLY code refresh or a re-IPL of zOS. To refresh, enter the following commands after applying the PTF: MODIFY LLA,REFRESH -STOP DB2 ... (only if the DB2 subsystem is started) -REFRESH DB2,EARLY -START DB2,PARM=... These commands are not necessary if you re-IPL after applying the PTF.). ++ HOLD(UK75933) SYS FMID(HIZAA10) REASON(ACTION) DATE(13051) COMMENT (In order for this PTF to be fully effective, an IPL with CLPA or REFRESH DB2,EARLY is required. The DB2 member should be stopped before issuing the REFRESH DB2, EARLY command.). UK91836 HOLD DATA END UK91837 HOLD DATA START 1++ HOLD(UK91837) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13051) COMMENT (***Action for PM65940: See PM65940 APAR/PTF text for additional information about why a REBIND is necessary. PM65940 corrects a problem that ABEND0C4 RC00000010 at the location DSNXGRDS DSNXRSIN OFFSET10D4 could happen for an SQL statement with an IN list predicate, which contains host variables mixed with string constants, when parallelism is enabled. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK91837 HOLD DATA END UK91848 HOLD DATA START ++ HOLD(UK91848) SYS FMID(HDBAA10) REASON(ACTION) DATE(13051) COMMENT (The PTF for APAR PM78014 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.). UK91848 HOLD DATA END UK91885 HOLD DATA START UK91885 HOLD DATA END UK91889 HOLD DATA START UK91889 HOLD DATA END UK91895 HOLD DATA START ++ HOLD(UK91895) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13053) COMMENT (***Action for PM79005: See PM79005 APAR/PTF text for additional information about why a REBIND is necessary. PM79005 corrects a problem that incorrect output could happen 1 for a MERGE SQL statement with a parameter marker that exists in the USING clause and compares with a constant in a predicate or in a search condition of a CASE expression. 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.). UK91895 HOLD DATA END UK91941 HOLD DATA START UK91941 HOLD DATA END UK91954 HOLD DATA START UK91954 HOLD DATA END UK91971 HOLD DATA START ++ HOLD(UK91971) SYS FMID(HDBAA10) REASON(ACTION) DATE(13056) COMMENT (***Action for PM79804 (V10) This PTF modifies the DB2 installation CLIST to correct errors in the migration handling of the DSN6SPRM.LRDRTHLD parameter. Actions for current DB2 10 customers: ------------------------------------ This PTF modifies CLIST member DSNTINST only in the prefix.SDSNCLST target library. After applying the PTF, you need to redo any record format changes and reapply any tailoring you have done to your copies of this CLIST. You may also want to move it to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside.). UK91971 HOLD DATA END UK91977 HOLD DATA START UK91977 HOLD DATA END UK91979 HOLD DATA START ++ HOLD(UK91979) SYS FMID(HDBAA10) REASON(DOC) DATE(13056) COMMENT (After APAR PM75150 changes, for an Non-Partitioned Index (NPI) on a LARGE (5-byte RID) table space, the index maximum number of pieces is independent of the table space maximum number of partitions, it is calculated from index page size and piece size as the following: Maximum NPI number of pieces = MINIMUM( 4096, 2^32 / (index piece size/index page size) ) Information about this change will be updated in the Information Management Software for z/OS Solutions Information Center 1 (http://publib.boulder.ibm.com/infocenter/imzic) at DB2 10 for z/OS > DB2 reference information > DB2 SQL > Statements > CREATE INDEX.). ++ HOLD(UK91979) SYS FMID(HDBAA10) REASON(ACTION) DATE(13056) COMMENT (After applying APAR PM75150, the following steps may help find the affected Non-Partitioned Indexes (NPIs) and the incorrect index maximum number of pieces can be fixed by ALTER INDEX... CLOSE. 1. run a query, such as the sample query below, to identify the databases, table spaces and indexes, SELECT TS.DBNAME AS DB_NAME, TS.NAME AS TS_NAME, IX.NAME AS INDEX_NAME, IX.CREATOR AS IX_CREATOR, HEX(IX.OBID) AS INDEX_OBID, IX.CLOSERULE AS CLOSERULE FROM SYSIBM.SYSINDEXES IX, SYSIBM.SYSTABLES TB, SYSIBM.SYSTABLESPACE TS WHERE TB.NAME = IX.TBNAME AND TB.CREATOR = IX.TBCREATOR AND TB.TSNAME = TS.NAME AND TB.DBNAME = TS.DBNAME AND (TS.PARTITIONS > 254 OR TS.TYPE = 'L' OR TS.TYPE = 'K' OR TS.DSSIZE > 0) AND TS.PARTITIONS <> 0 AND (TS.STATUS = 'C' OR NOT ((IX.INDEXTYPE = 'P' OR IX.INDEXTYPE = 'D') AND TB.PARTKEYCOLNUM <>0)) AND TS.DBNAME <> 'DSNDB06' ORDER BY IX.OBID; +------------------------------------------------------------+ |DB_NAME |TS_NAME |INDEX_NAME|IX_CREATOR|INDEX_OBID|CLOSERULE| +------------------------------------------------------------+ |DB161410|XTB10000|I_NODEIDXT|SC161410 |0056 |N | |DB161410|TU161410|I_DOCIDTB1|SC161410 |0058 |N | ... 2. run REPAIR DBD DIAGNOSE against all databases from the above query. For example REPAIR DBD DB161410. By searching OBDINNUM in th REPAIR DBD output, it has the following mismatch, DSNU916I DSNUEDFT - OBDS DIFFER AT OFFSET X'00000052' OBDINNUM - INFORMATION NOT IN DB2 CATALOG DSNU904I DSNUEDFT - EXISTING VALUE X'0100' DSNU905I DSNUEDFT - REBUILT VALUE X'1000' DSNU913I DSNUEDDR - OBD X'0056' COMPARISON COMPLETE -- 0 ERRORS 1 which confirms the index index I_NODEIDXT with OBID X'0056' has incorrect maximum number of index pieces. 3. fix these indexes found in the above repair DBD output by altering the CLOSE attribute of the identified indexes from their current value and then altering the CLOSE attribut back to their original value. The index name and index attribute information can be found in the query result at step 1. for example, the index SC161410.I_NODEIDXT has the CLOSE attribute CLOSERULE = N. To correct the maximum number of index pieces for I_NODEIDXT, run the following DDL ALTER INDEX index SC161410.I_NODEIDXT CLOSE YES ALTER INDEX index SC161410.I_NODEIDXT CLOSE NO). UK91979 HOLD DATA END UK91986 HOLD DATA START ++ HOLD(UK91986) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13056) COMMENT (***Action for PM78712: See PM78712 APAR/PTF text for additional information about why a REBIND is necessary. PM78712 corrects a problem of not issuing SQLCODE -122 for an SQL statement that contains a CASE expression in the SELECT list and the CASE expression contains an IN predicate which references to a column which is not in the GROUP BY list. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK91986 HOLD DATA END UK91991 HOLD DATA START ++ HOLD(UK70176) SYS FMID(HDBAA10) REASON(ACTION) DATE(13056) COMMENT (***Action for PM41956: This APAR/PTF fixes a problem found in the DSNTIJEN and DSNTIJNF jobs as part of the migration to new-function mode. The problem does not apply to new DB2 10 installations. Orphaned entries were found in the SYSCOPY and SYSOBDS catalog tables after running the DSNTIJEN and DSNTIJNF jobs as part of the migration to new-function mode. Application of this APAR/PTF will only prevent the DSNTIJEN and DSNTIJNF jobs from leaving the orphaned entries behind. This APAR/PTF does not remove the existing orphaned entries. 1 The orhpaned entries do not affect DB2 normal operations, however they may cause unexpected problems when migrating to future release of DB2. If the DB2 subsystem has never been migrated to new-function mode and the DSNTIJEN and DSNTIJNF jobs have never been run before, you just need to make sure this APAR/PTF is applied before running the DSNTIJEN and DSNTIJNF jobs. If the DSNTIJNF or DSNTIJEN jobs have been run prior to this APAR/PTF, you need to do the following: 1. Run the following query to identify the orphaned entries: (note: the RID built-in function requires new-function mode) SELECT RIGHT(HEX(RID(C)),10),'SYSCOPY' FROM SYSIBM.SYSCOPY C WHERE C.TSNAME = 'DSNVTH01' AND C.DBNAME = 'DSNDB06' AND NOT EXISTS(SELECT * FROM SYSIBM.SYSINDEXES I WHERE I.CREATOR = 'SYSIBM' AND I.NAME = 'DSNVTH01') UNION SELECT RIGHT(HEX(RID(O)),10),'SYSOBDS' FROM SYSIBM.SYSOBDS O WHERE O.DBID = 6 AND O.OBDTYPE = 'R' AND NOT EXISTS (SELECT * FROM SYSIBM.SYSTABLESPACE T WHERE T.DBID = O.DBID AND T.PSID = O.PSID); 2.For each row returned from the query, run the following utility using the RID in the row: (for the SYSCOPY rows returned) REPAIR OBJECT LOCATE TABLESPACE DSNDB06.SYSCOPY RID X'' DELETE (for the SYSOBDS rows returned) REPAIR OBJECT LOCATE TABLESPACE DSNDB06.SYSALTER RID X'' DELETE). UK91991 HOLD DATA END UK91998 HOLD DATA START ++ HOLD(UK91998) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13056) COMMENT (***Action for PM73177: PM73177 corrects a problem that SQLCODE -723 or following ABENDs could happen when a trigger is fired after altering the triggering table, which is because the triggering table and its table space are not recorded in the catalog table SYSIBM.SYSPACKDEP. 1. ABEND04E RC00E2000F at the location DSNXEFB DSNSVSFB OFFSET0858 on V10. 1 2. ABEND04E RC00E20027 at the location DSNXEFB DSNSVBK OFFSET0A1A on V9. 3. ABEND0C4 RC00000011 at the location DSNSLD1 DSNSVBK OFFSET100A on V9. 4. ABEND04E RC00E20002 at the location DSNXEXAT DSNSVBK OFFSET08F2 on V9. To make this fix effective, the trigger packages must be rebound after application of this PTF.). UK91998 HOLD DATA END UK92000 HOLD DATA START UK92000 HOLD DATA END UK92028 HOLD DATA START UK92028 HOLD DATA END UK92059 HOLD DATA START UK92059 HOLD DATA END UK92061 HOLD DATA START UK92061 HOLD DATA END UK92070 HOLD DATA START UK92070 HOLD DATA END UK92079 HOLD DATA START UK92079 HOLD DATA END UK92085 HOLD DATA START UK92085 HOLD DATA END UK92086 HOLD DATA START ++ HOLD(UK91647) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13059) COMMENT (A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. APAR PM75619 corrects two problems. The first problem occurs on both V9 and V10. A query may produce incorrect output when a UNICODE table joins to an EBCDIC view which is a UNION ALL, and at the same time, when index-on-expression (IOE) is used for that query. The second problem occurs on V10 only. DB2 may not issue a REORG suggestion as it should. 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 1 affected by this change.). ++ HOLD(UK92086) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13059) COMMENT (A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM63095 corrects a problem of ABEND04E RC00C90101 in DSNIPSFI :5007 when executing an SQL statement containing a User Defined Function (UDF) with the DISALLOW PARALLEL parameter while parallelism is enabled. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK92086 HOLD DATA END UK92088 HOLD DATA START ++ HOLD(UK92088) SYS FMID(JDBAA17) REASON(ACTION) DATE(13059) COMMENT (*** Action for PM78974 (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 a package from the DBRM listed above. For example: BIND PACKAGE (.DSNAOCLI) MEMBER(DSNCLIQR) ACTION(ADD) ENCODING(EBCDIC) where is the location of the DBMS where the package binds and where the description of the package resides. Failure to bind the required package could result in SQLCODE=-805 at application run time. Also, refer to the prolog of the CLI sample bind job DSNTIJCL in your SDSNSAMP data set for specific instructions on how to customize the BIND command for binding the DBRMs/packages and the application plan at your site. *** Attention: DB2 data sharing customers need to bind the package only once, after applying the PTF on the first member.). ++ HOLD(UK91277) SYS FMID(JDBAA17) REASON(ACTION) DATE(13059) COMMENT (*** Action for PM76547 (Version 10) This PTF includes the following updated DBRM for the ODBC executable modules DSNAOCLI, DSNAOCLX, and DSNAO64C: DSNCLIQR 1 After applying the PTF, all customers who use these modules need to bind a package from the DBRM listed above. For example: BIND PACKAGE (.DSNAOCLI) MEMBER(DSNCLIQR) ACTION(ADD) ENCODING(EBCDIC) where is the location of the DBMS where the package binds and where the description of the package resides. Failure to bind the required package 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.). UK92088 HOLD DATA END UK92094 HOLD DATA START UK92094 HOLD DATA END UK92095 HOLD DATA START UK92095 HOLD DATA END UK92099 HOLD DATA START UK92099 HOLD DATA END UK92118 HOLD DATA START ++ HOLD(UK92118) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13059) COMMENT (***Action for PM79782: See PM79782 APAR/PTF text for additional information about why a REBIND is necessary. PM79782 corrects a problem of ABEND0C4 at DSNXRWND OFFSET 0338 in DB2 V9 for z/OS or ABEND04E at DSNSVSFB OFFSET 0858 in DB2 V10 for z/OS. 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.). UK92118 HOLD DATA END 1UK92156 HOLD DATA START UK92156 HOLD DATA END UK92162 HOLD DATA START UK92162 HOLD DATA END UK92164 HOLD DATA START UK92164 HOLD DATA END UK92190 HOLD DATA START UK92190 HOLD DATA END UK92212 HOLD DATA START UK92212 HOLD DATA END UK92216 HOLD DATA START UK92216 HOLD DATA END UK92251 HOLD DATA START UK92251 HOLD DATA END UK92252 HOLD DATA START ++ HOLD(UK92197) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13064) COMMENT (***Action for PM80413: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM80413 corrects a problem in which page range screening (PAGE_RANGE='Y') is not chosen when a DPSI is used with one-fetch index access (ACCESSTYPE='I1'). To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK92081) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13064) COMMENT (***Action for PM80173: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM80173 corrects a problem that DB2 may select a nested loop join and a table space scan without sparse index used on the inner table for a join in a recursive common table expression. To make this fix effective for a static application, it must be 1 rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK92252) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13064) COMMENT (***Action for PM79973: See PM79973 APAR/PTF text for additional information about why a REBIND is necessary. PM79973 corrects a possible query performance problem for a query that contains FETCH FIRST 1 ROW ONLY, OPTIMIZE FOR 1 ROW, or an EXISTS subquery, and there exists an index where all the predicates can be matching predicates. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK91588) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13064) COMMENT (***Action for PM76495: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM76495 corrects a problem that an inefficient index may be selected when multiple indexes are estimated to be highly selective and the matching column coverage of one index is the superset of the matching column and the screening column coverage of the inefficient 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.). UK92252 HOLD DATA END UK92270 HOLD DATA START ++ HOLD(UK92270) SYS FMID(HDBAA10) REASON(DOC) DATE(13065) COMMENT (The DB2 V9 for z/OS DB2 Codes publication will be updated to include the SQLCODE +20224 as follows. ---------------------------------------------------------------- +20224 ENCRYPTED DATA THAT WAS ORIGINALLY A BINARY STRING CANNOT BE DECRYPTED TO A CHARACTER STRING Explanation The DECRYPT_BIT, DECRYPT_CHAR, or DECRYPT_DB function was invoked with an argument that represents an encrypted string that was originally a binary string. A binary string cannot be cast to a character string using the DECRYPT_BIT, DECRYPT_CHAR, 1 or DECRYPT_DB function. System action The statement cannot be processed. Programmer response The encrypted value can only be decrypted using the DECRYPT_BINARY function. SQLSTATE 01658 ----------------------------------------------------------------). UK92270 HOLD DATA END UK92276 HOLD DATA START UK92276 HOLD DATA END UK92278 HOLD DATA START UK92278 HOLD DATA END UK92280 HOLD DATA START UK92280 HOLD DATA END UK92286 HOLD DATA START UK92286 HOLD DATA END UK92287 HOLD DATA START UK92287 HOLD DATA END UK92288 HOLD DATA START UK92288 HOLD DATA END UK92320 HOLD DATA START ++ HOLD(UK78526) SYS FMID(JDBAA12) REASON(ACTION) DATE(13066) 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.). 1UK92320 HOLD DATA END UK92323 HOLD DATA START ++ HOLD(UK92323) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13066) COMMENT (***Action for PM80763: See PM80763 APAR/PTF text for additional information about why a REBIND is necessary. PM80763 corrects a problem of ABEND0C7 DSNXRDEC at OFFSET DBF4 may occur for an SQL statement that satisfies the following conditions: 1. The statement references to a view. 2. The view contains an arithmetic operation that involves SUM or AVG aggregate functions and decimal types. 3. The DEC options at create view time and reference time are different. 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.). UK92323 HOLD DATA END UK92325 HOLD DATA START ++ HOLD(UK78529) SYS FMID(JDBAA12) REASON(ACTION) DATE(13066) 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.). UK92325 HOLD DATA END UK92329 HOLD DATA START UK92329 HOLD DATA END UK92333 HOLD DATA START UK92333 HOLD DATA END UK92355 HOLD DATA START 1UK92355 HOLD DATA END UK92357 HOLD DATA START UK92357 HOLD DATA END UK92361 HOLD DATA START UK92361 HOLD DATA END UK92381 HOLD DATA START UK92381 HOLD DATA END UK92420 HOLD DATA START UK92420 HOLD DATA END UK92457 HOLD DATA START UK92457 HOLD DATA END UK92460 HOLD DATA START ++ HOLD(UK92460) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13071) COMMENT (***Action for PM81771: See PM81771 APAR/PTF text for additional information about why a REBIND is necessary. PM81771 corrects two problems. 1. A problem of INCORROUT for UNION ALL with FETCH FIRST N ROWS ONLY. 2. A problem of incorrect SQLCODE -811 for UNION ALL with FETCH FIRST N ROWS ONLY in the MERGE INSERT with SELECT clause. To make the fix for the second problem 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.). UK92460 HOLD DATA END UK92471 HOLD DATA START UK92471 HOLD DATA END UK92474 HOLD DATA START UK92474 HOLD DATA END UK92478 HOLD DATA START UK92478 HOLD DATA END 1 UK92479 HOLD DATA START UK92479 HOLD DATA END UK92481 HOLD DATA START UK92481 HOLD DATA END UK92490 HOLD DATA START UK92490 HOLD DATA END UK92492 HOLD DATA START ++ HOLD(UK92492) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13072) COMMENT (***Action for PM79464: See PM79464 APAR/PTF text for additional information about why a REBIND is necessary. PM79464 corrects a problem of ABEND04E RC00E70005 DSNXOSR:P014 when a query has one miniplan with PARALLELISM ON and MERGC = YES and SORTN_JOIN = YES and PRIMARY_ACCESSTYPE = 'T' and the query has a GROUP BY 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.). UK92492 HOLD DATA END UK92494 HOLD DATA START UK92494 HOLD DATA END UK92517 HOLD DATA START UK92517 HOLD DATA END UK92539 HOLD DATA START ++ HOLD(UK92539) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13073) COMMENT (***Action for PM80699: A rebind is necessary to rebuild package and/or plan structures based on the changes in this APAR. PM80699 corrects a problem that incorrect output may be returned for a query with a GROUP BY list which has a SUBSTR or YEAR function as well as the column referenced by the function. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover 1 letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK91069) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13073) COMMENT (***Action for PM78070: See PM78070 APAR/PTF text for additional information about why a REBIND is necessary. PM78070 corrects a problem of incorrect output that can happen for a query which contains UNION, EXCEPT, EXCEPT ALL or ORDER BY and whose top query block has exactly one table. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK92539 HOLD DATA END UK92609 HOLD DATA START ++ HOLD(UK92609) SYS FMID(HDBAA10) REASON(DOC) DATE(13077) COMMENT (PM71744 introduces the following changes to externals: A new MODE "STATS" is added to the ACCESS DATABASE command. .-,-------------. V | >>-ACCESS--DATABASE(-+---database-name-+-----+-)---------------> +-*---------------------+ +-dbname1:dbname2-------+ +-dbname*---------------+ +-*dbname---------------+ +-*dbname*--------------+ '-*dbstring1*dbstring2*-' .-,----------. V | >--SPACENAM--(--+---space-name-+--------------+--)-------------> +-*---------------------------+ +-spacename1:spacename2-------+ +-spacename*------------------+ +-*spacename------------------+ +-*spacename*-----------------+ '-*spacestring1*spacestring2*-' >--+---------------------------------------+-------------------> | .-,---------------------. | | V | | '-PART--(----+-integer-----------+-+--)-' '-integer1:integer2-' >--MODE--(--+-OPEN----+--)------------------------------------>< +-NGBPDEP-+ 1 '-STATS---' New option description: MODE( mode-value ) STATS: Externalizes the in-memory real-time statistics to the appropriate catalog tables. In data sharing environments, the in-memory statistics are externalized for all members. This mode does not physically open the page sets or change the states of the page sets. Information about this new command option will be included in the Information Management Software for z/OS Solutions Information Center (http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp) at a later date.). UK92609 HOLD DATA END UK92615 HOLD DATA START ++ HOLD(UK92615) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13077) COMMENT (***Action for PM79254: See PM79254 APAR/PTF text for additional information about why a REBIND is necessary. PM79254 corrects a problem of ABEND04E RC90101 at location DSNGD001.DSNGDVRT:500B or ABEND04E RC90101 at location DSNIDM.DSNIBNRF:5007, when an SQL statement satisfies all of the following conditions: 1. a common table expression (CTE#1) contains OUTER JOIN; 2. another common table expression (CTE#2) references CTE#1; 3. both of the CTE#1 and CTE#2 are referenced in full-select. 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.). UK92615 HOLD DATA END UK92625 HOLD DATA START ++ HOLD(UK90468) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13077) COMMENT (***Action for PM75044: See PM75044 APAR/PTF text for additional information about why a REBIND is necessary. PM75044 corrects the conditions that can lead to the 1 occurrence of an ABEND04E RC00E72018 at DSNXSZK OFFSET 2DBE when executing a query with group by, running with parallelism. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK83742) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13077) COMMENT (See PM69950 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. Apar PM69950 corrects the conditions that can lead to the occurrence of an ABEND04E RC00C90101 at location DSNOTCSO:5020 when executing a query that contains a select that references a HAVING clause with a LOB, but without a group by and set function, running with parallelism. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK92625 HOLD DATA END UK92700 HOLD DATA START UK92700 HOLD DATA END UK92704 HOLD DATA START UK92704 HOLD DATA END UK92720 HOLD DATA START UK92720 HOLD DATA END UK92783 HOLD DATA START UK92783 HOLD DATA END UK92835 HOLD DATA START ++ HOLD(UK92835) SYS FMID(HDBAA10) REASON(DOC) DATE(13081) COMMENT (PM77755 introduces the following changes to externals: Changed option for SQL statement ALTER TABLE ADD CLONE: When ADD CLONE is used to create a clone of the specified base table, the base table must conform to the following rules: ... - Not be hash-organized, whether or not the hash organization is materialized. New reason added for SQLCODE -148: 17 - The ALTER statement cannot be used to define a clone table because table is hash-organized. 1 Information about this new reason code and changed option 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.). UK92835 HOLD DATA END UK92841 HOLD DATA START UK92841 HOLD DATA END UK92878 HOLD DATA START UK92878 HOLD DATA END UK92880 HOLD DATA START UK92880 HOLD DATA END UK92916 HOLD DATA START ++ HOLD(UK92916) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13085) COMMENT (***Action for PM77208: See PM77208 APAR/PTF text for additional information about why a REBIND is necessary. PM77208 corrects an ABEND problem that occurs when DB2 handles a query with parallel processing and the combined record length going through parallel pipe is larger than 32K. 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.). UK92916 HOLD DATA END UK92956 HOLD DATA START UK92956 HOLD DATA END UK92961 HOLD DATA START UK92961 HOLD DATA END UK92963 HOLD DATA START UK92963 HOLD DATA END UK92971 HOLD DATA START UK92971 HOLD DATA END 1 UK92981 HOLD DATA START ++ HOLD(UK92981) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13087) COMMENT (***Action for PM83144: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM83144 corrects a problem in which DB2 could incorrectly discard an multi-index access path. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK92968) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13087) COMMENT (***Action for PM81295: See PM81295 APAR/PTF text for additional information about why a REBIND is necessary. PM81295 corrects a problem of poor query performance for a query containing OR predicates that map to a single index. The poor performance can occur when a range-list index access plan may not be assigned the lowest cost of any plan it beats. 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.). UK92981 HOLD DATA END UK92982 HOLD DATA START UK92982 HOLD DATA END UK93127 HOLD DATA START UK93127 HOLD DATA END UK93128 HOLD DATA START UK93128 HOLD DATA END UK93160 HOLD DATA START UK93160 HOLD DATA END UK93177 HOLD DATA START UK93177 HOLD DATA END UK93178 HOLD DATA START 1UK93178 HOLD DATA END UK93212 HOLD DATA START UK93212 HOLD DATA END UK93226 HOLD DATA START UK93226 HOLD DATA END UK93239 HOLD DATA START UK93239 HOLD DATA END UK93250 HOLD DATA START UK93250 HOLD DATA END UK93300 HOLD DATA START ++ HOLD(UK90215) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13100) COMMENT (***Action for PM75486: See PM75486 APAR/PTF text for additional information about why a REBIND is necessary. PM75486 corrects a problem that incorrect output could happen for an SQL statement that satisfies all of the following conditions: 1. It contains a table expression or view with a set operator, and the table expression or view is materialized. 2. It contains a LIKE predicate that can be pushed down to the materialized table expression or view. 3. After push down, the LIKE predicate contains an expression, which matches to an index on expression. 4. The expressions in the different set branches return data with different nullability. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK82227) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13100) COMMENT (***Action for PM70345: See PM70345 APAR/PTF text for additional information about why a REBIND is necessary. PM70345 corrects a problem that performance regression could happen for a static SQL statement that has a non-boolean term predicate with Timestamp column. To make this fix effective for a static application, it must be 1 rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK76115) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13100) COMMENT (***Action for PM53364: See PM53364 APAR/PTF text for additional information about why a REBIND is necessary. PM53364 corrects a problem that An incorrout may occur when the query satisfies all of below conditions: 1,A String representation of timestamp value is used as one parameter of a scalar function. 2,Above scalar function is referenced in the user query's predicate. 3,The result of the scalar function is used to compare with another timestamp value. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK73883) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13100) COMMENT (***Action for PM47894: See PM47894 APAR/PTF text for additional information about why a REBIND is necessary. PM47894 corrects a problem that an incorrout may occur when user's SQL statement satisfies all of following conditions: 1, The SQL statement contains a BETWEEN predicate which references a CASE expression in its low or high operator. 2, The CASE expression references a scalar fullselect in its searched-when clause. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK93300) SYS FMID(HDBAA10) REASON(ACTION) DATE(13100) COMMENT (***Actions for APAR PM81692 (V10) This PTF adds an online-changeable subsystem parameter in DSN6SPRM called PGRNGSCR that specifies whether to enable page range pruning in order to improve performance of queries that contain one or more of the following predicates: 1 < <= > >= BETWEEN AND Valid settings are: - NO : Do not enable this optimizer change. ==> This is the default setting - YES: Enable the change. Do not use this setting unless advised to do so by IBM service. Special attention for data sharing customers: * Do not specify PGRNGSCR=YES on any member of the group until this PTF has been applied to all members. Otherwise, DB2 can experience abend 04E-00E70005 at DSNXRTIM:P017 * If you specify PGRNGSCR=YES on any member, it is recommended that you specify it on all members. Not specifying the same option on all members may result in member flip-flop behavior. If you have already installed or migrated to this version of DB2 you need to take the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Update your customized copy of job DSNTIJUZ (3) Update private copies of the DSNTIDxx CLIST input member (4) Rebind any applications using one or more of the aforementioned predicates. 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 PGRNGSCR=, where is NO or YES, to the invocation of the DSN6SPRM macro. Make sure to add a continuation character in column 72 if needed. If you omit adding PGRNGSCR here, the value will be set to the default of NO when you assemble the DSNZPxxx module. 1 Special attention for data sharing customers: - Do not specify PGRNGSCR=YES on any member of the group until this PTF has been applied to all members. Otherwise, DB2 can experience abend 04E-00E70005 at DSNXRTIM:P017 - If you specify PGRNGSCR=YES on any member, it is recommended that you specify it on all members. Not specifying the same option on all members may result in member flip-flop behavior. * 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 PGRNGSCR to the CLIST default input members, DSNTIDXA and DSNTIDXB, in the SDSNSAMP target library. You need to add these entries to all private copies of your CLIST output DSNTIDxx member. In each such copy, add the following lines: PGRNGSCR CHAR M NO YES Change to the value you specified for PGRNGSCR in step (2), above. ---------------------------------------------------------------- (4) Rebind any applications using one or more of the aforementioned predicates. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change. End of actions for APAR PM81692 (V10)). ++ HOLD(UK91971) SYS FMID(HDBAA10) REASON(ACTION) DATE(13100) COMMENT (***Action for PM79804 (V10) This PTF modifies the DB2 installation CLIST to correct errors in the migration handling of the DSN6SPRM.LRDRTHLD parameter. Actions for current DB2 10 customers: ------------------------------------ This PTF modifies CLIST member DSNTINST only in the prefix.SDSNCLST target library. After applying the PTF, you need to redo any record format changes and reapply any tailoring you have done to your copies of this CLIST. You may also want to move it to the prefix.NEW.SDSNCLST data set, where the CLISTs 1 processed by job DSNTIJVC reside.). UK93300 HOLD DATA END UK93302 HOLD DATA START UK93302 HOLD DATA END UK93322 HOLD DATA START UK93322 HOLD DATA END UK93373 HOLD DATA START UK93373 HOLD DATA END UK93392 HOLD DATA START ++ HOLD(UK93392) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13102) COMMENT (***Action for PM83457: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM83457 corrects a problem that the DB2 optimizer may select an inefficient join sequence for a query if the query has a query block with a join of 3 or more tables. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK90219) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13102) COMMENT (***Action for PM73214: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM73214 corrects a problem of inaccurate cost estimation for a query when the query contains a view or table expression with UNION ALL. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK90222) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13102) COMMENT (***Action for PM72676: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM72676 corrects a problem of inaccurate cost estimation for a query when the query contains nested non-correlated 1 subqueries. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK77770) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13102) COMMENT (***Action for PM58932: A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. PM58932 corrects a problem that DB2 may choose an inefficient join sequence for a query joining 3 or more tables. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK68201) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13102) COMMENT (***Action for PM34814: See PM34814 APAR/PTF text for additional information about why a REBIND is necessary. PM34814 corrects a problem that DB2 may select an inefficient join sequence for a query if a declared global temporary table is involved or statistics on join columns are not collected. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK93392 HOLD DATA END UK93436 HOLD DATA START UK93436 HOLD DATA END UK93437 HOLD DATA START ++ HOLD(UK93437) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13103) COMMENT (A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM81848 corrects a problem of possible incorrect output for a query containing ORDER BY, GROUP BY, or set function DISTINCT when hybrid join is chosen with SORTN_JOIN=N, an index matches the ORDER BY, GROUP BY, or set function DISTINCT columns, and no extra sort is added to handle this sorting. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover 1 letter to determine which, if any, applications could be affected by this change.). UK93437 HOLD DATA END UK93439 HOLD DATA START UK93439 HOLD DATA END UK93522 HOLD DATA START UK93522 HOLD DATA END UK93532 HOLD DATA START UK93532 HOLD DATA END UK93545 HOLD DATA START UK93545 HOLD DATA END UK93546 HOLD DATA START UK93546 HOLD DATA END UK93552 HOLD DATA START UK93552 HOLD DATA END UK93556 HOLD DATA START UK93556 HOLD DATA END UK93589 HOLD DATA START UK93589 HOLD DATA END UK93592 HOLD DATA START UK93592 HOLD DATA END UK93614 HOLD DATA START UK93614 HOLD DATA END UK93651 HOLD DATA START UK93651 HOLD DATA END UK93660 HOLD DATA START UK93660 HOLD DATA END UK93661 HOLD DATA START UK93661 HOLD DATA END 1 UK93669 HOLD DATA START UK93669 HOLD DATA END UK93713 HOLD DATA START UK93713 HOLD DATA END UK93716 HOLD DATA START ++ HOLD(UK82112) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13113) COMMENT (See PK71143 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. APAR PK71143 corrects a problem that can cause RS00C90096 (number of page locks reached numlkus install limit) being issued for an update/delete/merge statement. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK93716 HOLD DATA END UK93719 HOLD DATA START UK93719 HOLD DATA END UK93725 HOLD DATA START UK93725 HOLD DATA END UK93759 HOLD DATA START ++ HOLD(UK93759) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13114) COMMENT (***Action for PM85238: See PM85238 APAR/PTF text for additional information about why a REBIND is necessary. PM85238 corrects a problem of incorrect output may be returned when a range list access path is chosen for the table and one of non-boolean term predicates under OR is not the matching predicate. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK78270) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13114) COMMENT (***Action for PM57955: 1 See PM57955 APAR/PTF text for additional information about why a REBIND is necessary. PM57955 corrects a problem of ABEND04E RC00E70005 in DSNXORGL P130 when query with range list access (ACCESSTYPE = 'NR' in PLAN_TABLE) participated in parallel sort-merge join group. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK93759 HOLD DATA END UK93800 HOLD DATA START UK93800 HOLD DATA END UK93802 HOLD DATA START UK93802 HOLD DATA END UK93882 HOLD DATA START ++ HOLD(UK93882) SYS FMID(HDBAA10) REASON(ACTION) DATE(13116) COMMENT (PM84232 corrects a problem of incorrect output when a native SQL procedure or a non-inline SQL scalar function contains an SQL statement that evaluates a datetime comparison with SQL variable or SQL parameter of the character string data type. type. After the application of this PTF, ALTER PROCEDURE REGENERATE is required for each native SQL procedure that is affected, or ALTER FUNCTION REGENERATE is required for each non-inline SQL scalar function that is affected. Review the PTF cover letter to determine which, if any, native SQL procedure or non-inline SQL scalar function could be affected by this change.). UK93882 HOLD DATA END UK93910 HOLD DATA START UK93910 HOLD DATA END UK93912 HOLD DATA START UK93912 HOLD DATA END UK93918 HOLD DATA START UK93918 HOLD DATA END UK93951 HOLD DATA START ++ HOLD(UK93951) SYS FMID(HDBAA10) REASON(DOC) DATE(13120) COMMENT (The value of QWHSSUBV has been increased to 3. If this value is 1 greater or equal to 3, DSNDQPAC section of IFCID239 contains class 3 times for IDAA offload.). UK93951 HOLD DATA END UK93952 HOLD DATA START UK93952 HOLD DATA END UK93989 HOLD DATA START ++ HOLD(UK93989) SYS FMID(HDBAA10) REASON(ACTION) DATE(13121) COMMENT (***Action for PM82438 (V10) System Requirements: To take advantage of the new LOCAL date format support for 'dd/mm/yyyy' on IBM DB2 Analytics Accelerator, the following fixes are needed in conjunction with this PTF: For IBM DB2 Analytics Accelerator, PM80112/UK93624 must be applied. For Netezza, level NZ 6.0.8 P-6 or higher is needed. This PTF adds a new option, 4, to the DB2 subsystem parameter DSN6SPRM.QUERY_ACCEL_OPTIONS, which is used to specify additional types of SQL queries to be included in query offloading. The new option is: * 4 : The queries that reference an expression with a DATE data type that uses a LOCAL format are not blocked from executing on IBM DB2 Analytics Accelerator. IBM DB2 Analytics Accelerator will use the dd/mm/yyyy format as LOCAL format. The option 4 should be specified only when the DATE FORMAT field of install panel DSNTIP4 specifies LOCAL or when application programs that process SQL on DB2 have been precompiled with the DATE(LOCAL) option. In either case, the LOCAL date exit routine must also define the specific 'dd/mm/yyyy' date format. If the LOCAL format is not defined as 'dd/mm/yyyy', the query may return unpredictable results. If option 4 is specified on any member of a data sharing environment, it is recommended to specify it on all members. NONE remains the default setting for QUERY_ACCEL_OPTIONS and is mutually exclusive from all other settings. The options can be specified individually or together. Examples: QUERY_ACCEL_OPTIONS=NONE QUERY_ACCEL_OPTIONS=1 QUERY_ACCEL_OPTIONS=3 QUERY_ACCEL_OPTIONS=(1,2) QUERY_ACCEL_OPTIONS=(1,3) QUERY_ACCEL_OPTIONS=(1,2,4) 1 No action is required after applying this PTF unless both of the following are true: * The DSN6SPRM.ACCEL parameter is set to AUTO or COMMAND to enable query acceleration. * The DSNHDECP.DATE parameter is set to LOCAL and the LOCAL date exit defines the specific dd/mm/yyyy date format. In that case, you need to take the following actions after applying this PTF: (1) Update your customized copy of job DSNTIJUZ (2) Update private copies of the DSNTIDxx CLIST input member Detailed guidance for these actions follows: ---------------------------------------------------------------- (1) Update your customized copy of job DSNTIJUZ ---------------------------------------------------------------- ==> This action is optional Update your customized copy of job DSNTIJUZ as follows: * Add or update the keyword parameter QUERY_ACCEL_OPTIONS= where is 4 or a list form such as (1,2,3,4) to the invocation of the DSN6SPRM macro. Make sure to add a continuation character in column 72 if needed. If your DSNTIJUZ job does not include QUERY_ACCEL_OPTIONS, that parameter will be set to the default of NONE when you assemble the DSNZPxxx module. * Run the first two steps of the DSNTIJUZ job you modified. * After the job completes, you must either use the -SET SYSPARM command or stop and restart DB2 for the change to take effect. ---------------------------------------------------------------- (2) Update private copies of the DSNTIDxx CLIST input member ---------------------------------------------------------------- ==> This action is required only if you added or updated the setting for QUERY_ACCEL_OPTIONS in the previous step. If you added or modified the setting for QUERY_ACCEL_OPTIONS, you need to add or update the entry for it in all private copies of your CLIST output DSNTIDxx member. In each such copy, add or update the following line: QUERY_ACCEL_OPTIONS CHAR M NONE NONE Change to the value you specified for QUERY_ACCEL_OPTIONS above in DSNTIJUZ. If you do not add QUERY_ACCEL_OPTIONS to a DSNTIDxx member, the DB2 installation CLIST will assume QUERY_ACCEL_OPTIONS=NONE when run with that member as input.). UK93989 HOLD DATA END 1 UK94011 HOLD DATA START ++ HOLD(UK94011) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13122) COMMENT (***Action for PM84869: See PM84869 APAR/PTF text for additional information about why a REBIND is necessary. PM84869 corrects a problem of incorrect output or an ABEND at DSNXOLDL OFFSET 0C0C, DSNXOLDL OFFSET 0C10, or DSNXOLDL OFFSET 0BC6 when a query contains a parallel IN-LIST group with a host variable or parameter marker and the IN-LIST predicate runs multiple times in a statement cache enabled system (CACHEDYN = YES) or in a statically bound application. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK93008) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13122) COMMENT (***Action for PM80679: See PM80679 APAR/PTF text for additional information about why a REBIND is necessary. PM80679 corrects a problem of an ABEND0C4 at DSNXRIPE +1DF6 may occur when a query contains a VIEW, Table Expression or CTE table in a correlated sub-select 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.). ++ HOLD(UK80266) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13122) COMMENT (***Action for PM63687: See PM63687 APAR/PTF text for additional information about why a REBIND is necessary. PM63687 corrects a problem of ABEND0C4-00000004 in dsnxripe +1DEA when a correlated subquery contains an IN-LIST table ( PLAN_TABLE ACCESSTYPE = 'IN') and the parent query block of the correlated subquery is running in parallel. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK94011 HOLD DATA END UK94060 HOLD DATA START UK94060 HOLD DATA END 1 UK94068 HOLD DATA START ++ HOLD(UK94068) SYS FMID(HDBAA10) REASON(ACTION) DATE(13123) COMMENT (================================================================ ++HOLD for APAR PM83534 ================================================================ This PTF adds a new job step, ENFM0019, to DSNTIJEN, the job for enabling new-function mode (ENFM). The new job step copies the SYSUTILX table space after it has been converted for ENFM. ---------------------------------------------------------------- (1) Update your customized copy of job DSNTIJEN ---------------------------------------------------------------- ==> This action is required for all DB2 10 customers who maintain a customized copy of DSNTIJEN This PTF modifies DSNTIJEN in the SDSNSAMP target library only. After applying this PTF, update your copy of DSNTIJEN as follows: (a) Copy the new job step, ENFM0019, from prefix.SDSNSAMP(DSNTIJEN) into your copy of DSNTIJEN, directly after the ENFM0010 job step. When copying, be sure to include the CHCK0010 check steps that precede and follow job step ENFM0019. (b) In the ENFM0019 PARM string, change 'DSN' to your subsystem name. (c) In the ENFM0019 TEMPLATE statement, change 'DSN!!0' to the prefix you use for DSNTIJEN image copy data sets. Also make other modifications as required, such as for device type, SMS classes, and volume/serial name. Tip: Use the TEMPLATE statement in job step ENFM0027 as a model (d) Save your changes. Do not run DSNTIJEN except for the ENFM process. ---------------------------------------------------------------- (2) Update customized copies of DB2 installation CLIST members ---------------------------------------------------------------- ==> This action is required for all DB2 10 customers This PTF modifies CLIST member DSNTINS3 in the SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your copies of this CLIST. You may also want to move it to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside.). UK94068 HOLD DATA END UK94121 HOLD DATA START UK94121 HOLD DATA END 1UK94174 HOLD DATA START UK94174 HOLD DATA END UK94179 HOLD DATA START ++ HOLD(UK94179) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13129) COMMENT (***Action for PM85976: See PM85976 APAR/PTF text for additional information about why a REBIND is necessary. PM85976 corrects an ABEND0C4 at DSNXEBR OFFSET 441C or an ABEND04E RC00E70005 at location DSNXESX4 M999 can occur when executing the SET CURRENT OPTIMIZATION HINT = '' or the SET CURRENT SQLID = '' statements. These failures can occur from dynamic or static SQL when a UNICODE encoding scheme is employed. 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.). UK94179 HOLD DATA END UK94239 HOLD DATA START UK94239 HOLD DATA END UK94265 HOLD DATA START UK94265 HOLD DATA END UK94283 HOLD DATA START UK94283 HOLD DATA END UK94308 HOLD DATA START UK94308 HOLD DATA END UK94311 HOLD DATA START ++ HOLD(UK69378) SYS FMID(HDBAA10) REASON(ACTION) DATE(13135) COMMENT (This fix corrects an SQLCODE20356 error when a TRUNCATE TABLE operation is run on a parent table that is defined with an informational referential constraint by use of the NOT ENFORCED keywords on the references-clause of the CREATE or ALTER TABLE statement. Note that if TRUNCATE TABLE is run on parent tables for which all constraints are informational (NOT ENFORCED), the TRUNCATE will no longer be blocked. Such a condition should have been ignored by TRUNCATE TABLE. 1 Customers making use of TRUNCATE TABLE can verify whether an object has only informational constraints defined by examining the ENFORCED column of the DB2 Catalog table SYSIBM.SYSRELS for the object being truncated. The DB2 Version 10 SQL REFERENCE manual under TRUNCATE statement "NOTES Rules and restrictions" will be changed approximately as follows: "Rules and Restrictions: The truncate operation cannot be executed if the table is a parent table in an enforced referential constraint".). UK94311 HOLD DATA END UK94344 HOLD DATA START UK94344 HOLD DATA END UK94426 HOLD DATA START ++ HOLD(UK94426) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13137) COMMENT (See PM85237 APAR/PTF text for additional information about why a REBIND is necessary. PM85237 corrects an incorrect output problem that may occur when (1) sparse index access is used on a table, and (2) not all the eligible join predicates are used for the sparse index key. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK91647) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13137) COMMENT (A rebind is necessary for static applications to rebuild access paths based on the changes in this APAR. APAR PM75619 corrects two problems. The first problem occurs on both V9 and V10. A query may produce incorrect output when a UNICODE table joins to an EBCDIC view which is a UNION ALL, and at the same time, when index-on-expression (IOE) is used for that query. The second problem occurs on V10 only. DB2 may not issue a REORG suggestion as it should. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK92086) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13137) COMMENT (A rebind is necessary for static applications to rebuild access paths based on changes in this APAR. PM63095 corrects a problem of ABEND04E RC00C90101 in DSNIPSFI :5007 when executing an SQL statement containing 1 a User Defined Function (UDF) with the DISALLOW PARALLEL parameter while parallelism is enabled. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK94426 HOLD DATA END UK94460 HOLD DATA START ++ HOLD(UK94460) SYS FMID(HDBAA10) REASON(ACTION) DATE(13140) COMMENT (This fix should be applied to V10 subsystems to handle the problem where inaccurate error messages could be issued when attempting to use new V10 bind parameters on previous version subsystems.). UK94460 HOLD DATA END UK94462 HOLD DATA START ++ HOLD(UK94462) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13140) COMMENT (***Action for PM87084: See PM87084 APAR/PTF text for additional information about why a REBIND is necessary. PM87084 corrects a problem of incorrect output for a query with an ORDER BY clause that refers to a table with an OMNIFIND 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.). UK94462 HOLD DATA END UK94466 HOLD DATA START UK94466 HOLD DATA END UK94483 HOLD DATA START UK94483 HOLD DATA END UK94559 HOLD DATA START UK94559 HOLD DATA END UK94568 HOLD DATA START ++ HOLD(UK83996) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13151) COMMENT (***Action for PM74543: 1 See PM74543 APAR/PTF text for additional information about why a REBIND is necessary. PM74543 corrects a problem of choosing a poorly performing access path for a partitioned table when queries involve partitioned tables, and EQUAL with host variables or parameter markers, and a DPSI (Data Partitioned Secondary Index). To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK83426) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13151) COMMENT (***Action for PM74152: A rebind is necessary for static applications to rebuild internal structures used at runtime based on the changes in this APAR. PM74152 corrects a problem in which DB2 may incorrectly return no rows for a query using page range access where a range predicate exists on a partitioning column defined as CHAR(1). To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK74779) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13151) COMMENT (***Action for PM52548: See PM52548 APAR/PTF text for additional information about why a REBIND is necessary. PM52548 fixes an abend 0C4 RC00000038 DSNISPBM + 1A64 that can occur during query execution for queries containing IS NULL or LIKE predicates. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK71244) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13151) COMMENT (***Action for PM38082: See PM38082 APAR/PTF text for additional information about why a REBIND is necessary. PM38082 corrects a query performance problem involving significant loss of page range screening for queries with multi-column partitioning keys and predicates with host variables or parameter markers on those columns. 1 To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). ++ HOLD(UK64444) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13151) COMMENT (See PM25059 APAR/PTF text in the PTF cover letter for additional information about why a REBIND is necessary. PM25059 corrects a problem where the page range reduction is done incorrectly. It may cause DB2 to choose a plan with an extra GROUP BY / ORDER BY sort where it is not needed. To make this fix effective for a static application, it must be rebound after application of this PTF. Review the PTF cover letter to determine which, if any, applications could be affected by this change.). UK94568 HOLD DATA END UK94590 HOLD DATA START ++ HOLD(UK94590) SYS FMID(HDBAA10) REASON(MULTSYS) DATE(13146) COMMENT (PM83559 is a pre-conditioning APAR that includes changes to support the fix for the reported problem, which will be enabled via another APAR at a later date. Changes in PM83559 should be generally transparent to users. In a data sharing group, the fix for this pre-conditioning APAR must be applied to all members before the later enabling APAR, when available, is applied to any member. The fix for the reported problem will not be effective until it is enabled by the follow-on APAR.). UK94590 HOLD DATA END UK94618 HOLD DATA START UK94618 HOLD DATA END UK94648 HOLD DATA START UK94648 HOLD DATA END UK94661 HOLD DATA START UK94661 HOLD DATA END UK94695 HOLD DATA START ++ HOLD(UK94695) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13155) COMMENT (***Action for PM84536: See PM84536 APAR/PTF text for additional information about why a REBIND is necessary. PM84536 corrects a problem of ABEND04E RC00E72018 at location DSNXGRDS.DSNXSINE: 1342 when an SQL statement satisfies all of 1 the following conditions: 1. a table expression or VIEW (VW#1) is defined with multiple SETOPs; 2. another table expression or VIEW referenced in VW#1 is also defined with multiple SETOPs. 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.). UK94695 HOLD DATA END UK94697 HOLD DATA START UK94697 HOLD DATA END UK94713 HOLD DATA START UK94713 HOLD DATA END UK94729 HOLD DATA START UK94729 HOLD DATA END UK94765 HOLD DATA START UK94765 HOLD DATA END UK94814 HOLD DATA START UK94814 HOLD DATA END UK94845 HOLD DATA START ++ HOLD(UK94845) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13162) COMMENT (***Action for PM84153: See PM84153 APAR/PTF text for additional information about why a REBIND is necessary. PM84153 corrects a problem that incorrect output may occur if an ORDER BY expression with DESC is specified in a table expression and the table expression is materialized for merge scan 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.). UK94845 HOLD DATA END UK94861 HOLD DATA START UK94861 HOLD DATA END 1 UK94868 HOLD DATA START UK94868 HOLD DATA END UK94997 HOLD DATA START UK94997 HOLD DATA END UK95006 HOLD DATA START UK95006 HOLD DATA END UK95009 HOLD DATA START UK95009 HOLD DATA END UK95038 HOLD DATA START UK95038 HOLD DATA END UK95206 HOLD DATA START UK95206 HOLD DATA END UK95210 HOLD DATA START ++ HOLD(UK95210) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13168) 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.). ++ HOLD(UK92607) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13168) 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 1 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.). ++ HOLD(UK90311) SYS FMID(HDBAA10) REASON(DB2BIND) DATE(13168) 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.). UK95210 HOLD DATA END UK95240 HOLD DATA START UK95240 HOLD DATA END UK95271 HOLD DATA START UK95271 HOLD DATA END UK95295 HOLD DATA START ++ HOLD(UK94096) SYS FMID(HDBAA10) REASON(DOC) DATE(13171) COMMENT (PM70981 introduces the following changes to externals: Changed messages or codes: description of change DSNB411I: Page read statistics When you specify the DETAIL option, the output includes the page read statistics for the buffer pool. DSNB411I - RANDOM GETPAGE = rgp SYNC READ I/O (R) = srr SEQ. GETPAGE = sgp SYNC READ I/O (S) = srs 1 DMTH HIT = dmt PAGE-INS REQ = pir SEQUENTIAL = seq VPSEQT HIT = vsh RECLASSIFY = rcy RANDOM GETPAGE = rgp The number of nonsequential GETPAGE requests. SYNC READ I/O (R) = srr The number of synchronous read I/O operations for nonsequential GETPAGE requests. SEQ. GETPAGE = sgp The number of sequential GETPAGE requests. SYNC READ I/O (S) = srs The number of synchronous read I/O operations for sequential GETPAGE requests. DMTH HIT = dmt The number of times that the data management threshold was reached. PAGE-INS REQ = pir The number of page-in operations that are required for read I/O. SEQUENTIAL = seq The number of buffers on the sequential least-recently-used (SLRU) chain. VPSEQT HIT = vsh The number of times that the size of the SLRU chain reached the sequential steal threshold (the VPSEQT value) for the buffer pool. RECLASSIFY = rcy A statistic that is used by IBM® for serviceability. Information about this changed message will be included in the Information Management Software for z/OS Solutions Information Center (http://publib.boulder.ibm.com/infocenter/imzic) at a later date.). ++ HOLD(UK82633) SYS FMID(HDBAA10) REASON(ACTION) DATE(13171) COMMENT (In order for DB2 to use "suppress cross-invalidation" enhancement for DELETE_NAME, all of the following must be true: - This PTF must be applied to the DB2 member that performs castout - The GBP must be allocated in a coupling facility of CFLEVEL=17 or higher. The following CFCC release supports the "suppress cross-invalidation" functionality: - z114 (2818) DR93G CFCC EC N48162 CFCC Release 17 at the requisite microcode load (MCL) level - z196 (2817) DR93G CFCC EC N48162 CFCC Release 17 at the requisite microcode load (MCL) level - zEC12 (2827) CFCC Release 18 - The DB2 member that performs castout must be running on z114 or z196 that supports the "suppress cross-invalidation" 1 functionality or a zEC12. The following z/OS releases support the "suppress cross-invalidation" functionality: - z/OS V1R12 and above with APAR OA38419 installed). ++ HOLD(UK94096) SYS FMID(HDBAA10) REASON(AO) DATE(13171) COMMENT (PM70981 introduces the following changes to externals: Changed messages or codes: description of change DSNB411I: Page read statistics When you specify the DETAIL option, the output includes the page read statistics for the buffer pool. DSNB411I - RANDOM GETPAGE = rgp SYNC READ I/O (R) = srr SEQ. GETPAGE = sgp SYNC READ I/O (S) = srs DMTH HIT = dmt PAGE-INS REQ = pir SEQUENTIAL = seq VPSEQT HIT = vsh RECLASSIFY = rcy RANDOM GETPAGE = rgp The number of nonsequential GETPAGE requests. SYNC READ I/O (R) = srr The number of synchronous read I/O operations for nonsequential GETPAGE requests. SEQ. GETPAGE = sgp The number of sequential GETPAGE requests. SYNC READ I/O (S) = srs The number of synchronous read I/O operations for sequential GETPAGE requests. DMTH HIT = dmt The number of times that the data management threshold was reached. PAGE-INS REQ = pir The number of page-in operations that are required for read I/O. SEQUENTIAL = seq The number of buffers on the sequential least-recently-used (SLRU) chain. VPSEQT HIT = vsh The number of times that the size of the SLRU chain reached the sequential steal threshold (the VPSEQT value) for the buffer pool. RECLASSIFY = rcy A statistic that is used by IBM® for serviceability. Information about this changed message will be included in the Information Management Software for z/OS Solutions Information Center (http://publib.boulder.ibm.com/infocenter/imzic) at a later date.). UK95295 HOLD DATA END 1 UK95350 HOLD DATA START UK95350 HOLD DATA END UK95781 HOLD DATA START UK95781 HOLD DATA END UK95806 HOLD DATA START ++ HOLD(UK73621) SYS FMID(HDBAA10) REASON(ACTION) DATE(13197) COMMENT (APAR PM45561 fixes a problem that causes incorrect output for readers of XML data that use ISOLATION LEVEL(UR) and and potentially allows DB2 to cause the readers of XML data to wait for a lock, even though ISO(UR) is specified. The situation usually occurs during heavy concurrent insert, update, or delete activity. The incorrect output problem is fixed by requiring ISOLATION(UR) readers to wait for XML locks. As a result, ISOLATION(UR) readers that access XML data might see increased wait time for locks. The additional wait time for unconditional locks sometimes result in deadlocks or timeouts(SQLCODE -911 or -913),therefore, application logic may need to change to accommodate SQLCODEs -911 and -913.). ++ HOLD(UK63153) SYS FMID(HDBAA10) REASON(ACTION) DATE(13197) COMMENT (TYPE: DB2 This fix corrects a problem with Compensation Log Records (CLRs) built for compression dictionary pages for classic (non-segmented) partitioned table spaces during insert. The problem is limited to classic partitioned table spaces that have an insert-generated compression dictionary and have been RECOVERED with log-apply. A dictionary page log record may not get applied correctly due to incorrect DBID/OBID, which happens under limited circumstances. Compression dictionaries built by DB2 LOAD or DB2 REORG are not affected. You can identify an insert-generated dictionary by checking the HPGZLD field in the page header of the header-page of each pageset/partition. HPGZLD would be = 'F' for an insert-generated dictionary. The symptoms of the problem include abends and/or error return codes in/from modules that try to use the dictionary pages, and/or DSNT501I RESOURCE UNAVAILABLE messages on the console with DB2 reason code = 00C9007F. The fix will correct the problem but it will not correct the log records already written with the error. So, after applying the fix, establishing new recovery bases for the table spaces which have the conditions specified above is advised. Forward log apply processes for such table spaces should not be 1 performed against image copies taken prior to the application of the fix.). UK95806 HOLD DATA END UK96219 HOLD DATA START ++ HOLD(UK96219) SYS FMID(HDBAA10) REASON(ACTION) DATE(13210) COMMENT (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 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 modules DSNADMSB and DSNADMSS plus the DBRM for DSNADMSS (the DBRM for DSNADMSB is not updated). 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. --------------------------------------------------------------- (2) Refresh the WLM environment --------------------------------------------------------------- ==> All DB2 V10 customers who use the SYSPROC.ADMIN_INFO_SQL stored procedure need to take this action after applying the PTF. Issue the following VARY MVS command from the console to refresh the WLM application environment: VARY WLM,APPLENV=!wlmenv!,REFRESH where !wlmenv! is the name of the WLM application environment for running the SYSPROC.ADMIN_INFO_SQL stored procedure. 1 --------------------------------------------------------------- (3) Update your customized copy of job DSNTEJ6I --------------------------------------------------------------- ==> All DB2 V10 customers who maintain a customized copy of DSNTEJ6I need to take this action after applying the PTF. The change affects the default input for the following parameters: Change COLLECT_COLUMN_STATS to N Change EDIT_DDL to Y These default changes are made to minimize the amount of data that is collected. Column statistics can be abundant, but are not always required for analysis. Edit mode will help create DDL in a format that can be more easily applied to a service environment.). UK96219 HOLD DATA END UK96335 HOLD DATA START ++ HOLD(UK96335) SYS FMID(HDBAA10) REASON(ACTION) DATE(13213) COMMENT (PM85432 does not fix existing data which has been affected by this problem, it does prevent future data from becoming corrupted by this issue. The user must identify the objects which have been affected and bring the data back to a consistent state. Objects that are affected must have the following conditions met: - The table must have an EDITPROC. - The table space or partition must have COMPRESS YES. - REORG with KEEPDICTIONARY must have been run with a dictionary present after PM44787/UK73145/UK73146 had been applied. The following SELECT statement can be used to determine if the first two conditions were met: SELECT TB.CREATOR, TB.NAME, TB.DBNAME, TB.TSNAME, TP.PARTITION FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSTABLEPART TP WHERE TB.EDPROC <> ' ' AND TB.DBNAME = TP.DBNAME AND TB.TSNAME = TP.TSNAME AND TP.COMPRESS = 'Y'; A REPORT RECOVERY can identify the names of the REORG job(s) that were run after the PTF was applied, however it cannot indicate if the KEEPDICTIONARY keyword was used during the REORG at the time. Because EDITPROCs can manipulate the data in many different ways, below are just a few methods for possibly identifying which tables may have corrupted data if it is not possible to determine whether or not the above conditions were all met: - A table space scan (SELECT * FROM XYZ) can be used to check if every record in the table can be EDITPROC decoded and 1 displayed in the output, however even if the display is successful it is possible the actual output of the data is not correct and therefore corrupted. An ABEND can also occur if the data after the EDITPROC decoding cannot be properly formatted. - If an index is present on the data/column that has been EDITPROC encoded then a CHECK INDEX could detect the data corruption with an index/data mismatch if it has not been rebuilt after the data was corrupted. An ABEND can also occur if the data after the EDITPROC decoding cannot be properly formatted.). UK96335 HOLD DATA END UK96595 HOLD DATA START ++ HOLD(UK90830) SYS FMID(HDBAA10) REASON(MULTSYS) DATE(13222) 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.). UK96595 HOLD DATA END READY END