What's new in Db2 HPU

This topic summarizes the operational changes for this edition.

Installation and customization

  • The parmlib library can be automatically allocated during the customization process through the INZTVAR customization job that exploits the new "Dasd unit where to allocate the PARMLIB if necessary (VZM012/PERMUNIT)" parmlib parameter to do so (APAR PH27015).
  • The value of the VUU030/ULOPTNS parmlib can be specified over up to 60 lines. That makes the setting of this parameter, which has a lot of subparameters, easier by allowing to specify one subparameter per line and by allowing to specify also any valid combination of the subparameters (APAR PH30767).
  • If more than one library is specified for "DSNEXIT Library" through Tools Customizer, the INZCHECK customization job ends with completion code of 4 and issues a message to warn that Db2 HPU only uses the first library (APAR PH27513).
  • In the Db2 HPU interactive component, the dataset name fields accept up to 64 characters in the following panels: INZA30, INZA40, INB601I, INZB1, INZB70I. This allows to specify data set name including many symbols. (APAR PH26322)
  • The E15_PROBE suboption of the SORTUTIL option from the Technical Parameters options block requests that an estimate of the amount of memory below the 16 MB line used by the E15 sort be displayed. This feature is exploited in a step of the INZCHECK customization jobs that allows to check the setting for the VUX004/LOWMEM is suitable. (APAR PH35449)

Performance

  • Thanks to an optimized new architecture and a partial 64-bit implementation, the mean CPU cost of Db2 HPU jobs has been decreased.
  • Db2 HPU can exploit the zIIP processor (ZIIP option). The unload jobs run in native mode and the unload jobs involving complex formatting, whatever the processing mode, offload a significant part of their processing load to the zIIP processors (APAR PH21361).
  • The performance of logical unloads run in native mode involving a partitioned table, and a WHERE clause is enhanced by not processing the partitions that do match the WHERE clause (OPTIMIZE_PART_RANGE option). This enhancement exploits the Db2 for z/OS part range table populated by the EXPLAIN PLAN statement (APAR PH19457).
  • In order to help understand the performance of unloads run in SQL mode, messages are issued to list the parameters or attributes that have a potential influence on the Db2 performance. These messages (INZU478I, INZU479I and INZU480I) are issued for both the user-handled parameters and the implicit attributes that Db2 HPU adds to the user-specified SELECT statement when it defers the data selection to Db2 (APAR PI94324).
  • The "Number of simultaneously opened LOB LDS(VUU080/MXOPNLLD)" allows to specify how many LDS of a multi-LDS LOB tablespace can be kept allocated and opened simultaneously for unloading LOB data in native mode. This allows to ensure better performance when LOB data is unloaded (APAR PH01897).
  • When unloading from automatically selected image copies through the COPYDDN LAST_IC or COPYDDN -n option, Db2 HPU may select image copies that require to be sorted, such as inline image copies. In terms of performance, unloading from a regular image copy that do not require to be sorted is more efficient. The EXCLUDE_FIC TO_SORT option allows to request Db2 HPU not to choose image copies that require to be sorted thus avoiding the related extra processing time (APAR 117230).
  • The output formatting task for unloads that involve CCSID conversions performed by Db2 HPU itself - either in native or SQL mode - are eligible to offload to zIIP processors (APAR PH19859).
  • Db2 HPU supports up to 6 Db2 load libraries specified through the "Load Library" field from the CCQPDB2 (DB2 Parameters) panel in Tools Customizer (APAR PH23745).
  • Unload job allocating their output data set through TEMPLATE statement can be restarted. This allows to save up execution time when resuming multiple unload jobs (data from several tables unloaded into distinct datasets) that have failed. When the RESTART feature is used, the RSTARTDD must be reserved to Db2 HPU (APAR PH32514).
  • Db2 HPU can exploit the DB2SORT block level interface that allows performance improvement of the sorts performed outside of Db2 if DB2SORT is available and Db2 HPU is requested to use it (APAR PH35218).
  • The consumption of 31 bit memory of unloads fulfilling all the following conditions:
    • run in native mode
    • against a partitioned table space with more than one partition
    • with LDS parallelism enabled has been drastically decreased which allows to increase the LDS parallelism degree of such unloads elaborated before this enhancement (APAR PH45375).

Db2 compliance

  • Db2 HPU supports (exploits or tolerates) the Db2 for z/OS V12.1 specific features available at various function levels as indicated below:
    Function level Feature Compliance
    508 support for moving tables from deprecated multi-table simple and multi-table segmented table spaces to partition-by-growth universal table spaces (UTS) no impact on Db2 HPU
    507 application granularity for locking limits no impact on Db2 HPU
    507 deletion of old statistics when using profiles no impact on Db2 HPU
    507 CREATE or REPLACE for procedures no impact on Db2 HPU
    507 newly supported pass-through-only expressions with IBM Db2 Analytics Accelerator no impact on Db2 HPU
    506 support for implicitly dropping explicitly created universal and LOB table spaces no impact on Db2 HPU
    506 alternative function names support supported in SQL mode only with no specific maintenance
    505 improved concurrency for rebind of executing packages no impact on Db2 HPU
    505 automatic page sample by default for RUNSTATS no impact on Db2 HPU
    505 improved DECFLOAT data type support supported (toleration) with maintenance for APAR PH21189
    505 transparent encryption for data columns supported: in both native and SQL mode with maintenance for APAR PH14986
    505 temporal and archive transparency for WHEN clauses on triggers no impact on Db2 HPU
    504 new support for data with IBM Z hardware-based Huffman compression of Db2 data supported: in both native and SQL mode with maintenance for APAR PH18085
    504 the ability to prevent the creation of certain new deprecated objects types no impact on Db2 HPU
    504 new support for certain built-in functions by pass-through to IBM Db2 Analytics Accelerator no impact on Db2 HPU
    504 new SQL syntax alternatives for certain special registers and NULL predicates supported in SQL mode with no specific maintenance -supported in native mode with maintenance for APAR PH22201
    503 support for IBM Db2 AI for z/OS no impact on Db2 HPU
    503 replication of system-period temporal tables and generated expression columns no impact on Db2 HPU
    503 a change to temporal auditing support for temporal data no impact on Db2 HPU
    503 a new console message that indicates when catalog levels or function levels change no impact on Db2 HPU
    502 management of key labels for z/OS DFSMS data set encryption supported in both native and SQL mode with PH14986
    502 explicit casting of numeric values to GRAPHIC or VARGRAPHIC. supported: in SQL mode only with no specific maintenance
    501 the LISTAGG built-in function produces a list of all values in a group supported in SQL mode only with no specific maintenance
    500 function level 500 (V12R1M500) represents the first opportunity for applications to take advantage of most new capabilities in the initial Db2 12 release, including new SQL capabilities and subsystem parameter settings. In many respects, function level 500 is analogous to new-function mode in previous releases. supported (exploitation or no impact depending on the considered feature) except for the LOB compression
    100 In function level 100 (V12R1M100), Db2 runs on Db2 12 code, and virtual storage and optimization enhancements in the initial Db2 12 release become available. However, most new function, including and subsystem parameter changes and new SQL capabilities remain disabled. In many respects, function level 100 is analogous to conversion mode in previous Db2 releases. supported
  • The consistency between the Db2 HPU native and SQL mode can be ensured automatically by requesting Db2 HPU to use the Db2 BIF_COMPATIBILITY settings instead of its own value set through the BIF_COMPATIBILITY subparameter of the Db2 HPU additional features (VUU030/ULOPTNS) parmlib parameter. The setting mode can be chosen through the "BIF_COMPATIBILITY_FROM_DB2_SETTINGS" subparameter of the VUU030/ULOPTNS parameter.
  • The "Db2 plan name of the Call Level Interface (VUX044/CLIPLAN)" parmlib parameter allows to specify the plan name of the Db2 for z/OS Call Level Interface (CLI) when the default Db2 name DSNACLI was not kept (APAR PI98506).
  • The Pervasive Encryption feature is exploited (APAR PH14986). Unloading data from Db2 objects which have underlying data sets encrypted using ICSF key label is possible in SQL mode and in native mode regardless of the data source (underlying VSAM data sets of the online Db2 objects or image copy data sets). For a Db2 like behavior - i.e. any user that has the requested privilege to run a given SELECT statement involving encrypted table spaces or index spaces can also run a Db2 HPU unload specifying the same SELECT statement -, set the "User who reads encrypted VSAM data set (VUM037/CRYPTUSR)" parameter with a userid that has the authorization to access the key label for any of the encrypted data set involved by your Db2 HPU jobs.
  • The stop of the unload can be ensured whenever Db2 HPU cannot generate requested LOAD control cards that are fully compliant - i.e. directly usable - with the Db2 LOAD utility by specifying the ULRLDRC REJECT option or by setting the parmlib parameter VUU082/ULRLDRC to REJECT (APAR PH01897).
  • The new subparameter ORDER_CLUSTER_BHV of VUU030/ULOPTNS, allows Db2 HPU to remove 'ORDER CLUSTER' clauses from unsupported SELECT statements (processed by SQL). It prevents Db2 HPU unloads to stop because of SQL error -199 (APAR PH20818).
  • Db2 HPU can now exploit the ADMIN_INFO_SYSPARM stored procedure to retrieve the value of the DSNZPARM parameters. This avoids undesirable IFCID 376 records be triggered when Db2 HPU retrieves the value of the BIF_COMPATIBILITY parameter. This also allows Db2 HPU to determine the value of the TEMPLATE_TIME parameter (more details in the following note). This way of retrieving the DSNZPARM parameters is enabled by specifying USE_ADMIN_INFO_SYSPARM(YES) for the VUU030/ULOPTNS parameter (APAR PH16349).
  • The time and date variables used in the data set names patterns of TEMPLATE statements can now be expanded considering local time instead of gmt/utc. If USE_ADMIN_INFO_SYSPARM(YES) is specified for the VUU030/ULOPTNS parameter and the Db2 settings specify TIME LOCAL, the date/time variables are expanded with local time where they were formerly unduly expanded with utc. They suboption TIME of TEMPLATE statements is supported.
  • The data from table spaces compressed with Huffmann compression - available when the dynamic zPARM TS_COMPRESSION_TYPE is set to HUFFMAN - and from their image copies can be unloaded in native mode ( APAR PH14986 ).
  • The OVERRIDE option of the LOAD utility, available since Db2 V12 and intended to replace the obsolete but still supported PERIODOVERRIDE, TRANSIDOVERRIDE and IDENTITYOVERRIDE options, can be used to generate the LOAD control cards associated with an unload run against a Db2 subsystem in version 12.1 and later. The USE_V12_ALTERNATE_OVERRIDE_LOAD_SYNTAX(NO/YES) sub-parameter of the VUU030/ULOPTNS parmlib parameter requests that the OVERRIDE(SYSTEMPERIOD), OVERRIDE(TRANSID) and OVERRIDE(IDENTITY) options be used instead of their respective counterparts of the pre-V12 versions, i.e. PERIODOVERRIDE, TRANSIDOVERRIDE, IDENTITYOVERRIDE (APAR PH36718).
  • Db2 HPU can exploit the OVERRIDE(ROWCHANGE) option from the LOAD utility available from Db2 V12 (APAR PH25572) for unloads run in native mode. The VUU099/ULRWCHNG parmlib parameter allows to request this feature be used for the LOAD statement generation whenever a GENERATED ALWAYS AS ROW CHANGE TIMESTAMP column is unloaded by an unload run in native mode against a Db2 subsystem in version 12 or later (APAR PH36703).
  • Db2 HPU supports the Db2 for z/OS V13.1 specific features available at various function levels as indicated in the table below provided that the following requirements are fulfilled:
    • APAR PH42701 has been applied to IBM Tools Customizer for z/OS
    • APAR PH45499 has been applied to Db2 HPU
    • as required per the HOLD DATA action for APAR PH45499, the INZ$$CCQ member from the INZHLQ.SINZDENU library has been edited to add the line "<value>131</value>" so that its updated content is as follows:
      <db2level>
      <value>810</value>
      <value>910</value>
      <value>101</value>
      <value>111</value>
      <value>121</value>
      <value>131</value>
      </db2level>
Function level Feature Compliance
100 Db2 13 starts at function level 100 (V13R1M100) during migration to Db2 13. Most new capabilities in Db2 13 remain deactivated, and fallback to Db2 12 and coexistence with Db2 12 in data sharing remain possible. supported (APAR PH45499)
500 Function level 500 (V13R1M500) is the first opportunity after migration to Db2 13 for applications to use new features and capabilities with no Db2 catalog dependencies. Activating function level 500 or higher prevents coexistence with and fallback to Db2 12. supported - no new feature exploited (APAR PH45499)
501 Function level 501 (V13R1M501) is the first opportunity after migration to Db2 13 for applications to use new features and capabilities with Db2 catalog dependencies. supported - no new feature exploited (APAR PH45499)

Input

  • The unload of the data from FlashCopy data sets no longer requires the image copies to be referenced by the Db2 for z/OS catalog: the COPYDDN LAST_IC or COPYDDN integer was formerly mandatory to unload from FlashCopy data sets. The FlashCopy data set can be directly allocated by the Db2 HPU step and used through the COPYDDN ddname option (FLASHCOPY suboption / APAR PH15431).
  • Db2 HPU is able to unload the data from a set of image copies made of a Full Image Copy and one or several Incremental Image Copies allocated under a single DD name (MERGE_IIC suboption of the COPYDDN ddname option).
  • The uncatalogued image copies can be unloaded by using the COPYDDN LAST_IC or COPYDDN -integer options provided SEARCH_UNCATALOGED_IC(YES) is specified for the "Db2 HPU additional features (VUU030/ULOPTNS)" parameter (APAR PH15431).
  • The LISTDEFTBV statement allows to select a list of tables and/or views through a TABLE selection criterion that directly applies to the table or view names (as registered in the SYSIBM.SYSTABLES Db2 catalog table) the LISTED statement that allows neither to select views nor to select some tables from a give table space (if one table matches the TABLE selection criterion, all the tables of the table space it belongs to are also selected). Like the LISTDEF statement, the name of a list defined through a LISTDEFTBV statement is meant to be used as an argument of a FROM LIST clause (APAR PH19459).
  • When unloading from automatically selected image copies through the COPYDDN LAST_IC or COPYDDN -n option, the EXCLUDE_FIC TO_SORT option allows to request Db2 HPU not to choose image copies that require to be sorted which is especially useful regarding performance purposes. See the "Performance" section above for more details (APAR PH15431).
  • The LISTDEFTBV statement allows to specify a list of tables and/or views by specifying a selection criterion that directly applies to the table or view names (as registered in the SYSIBM.SYSTABLES Db2 catalog table) unlike the selection criterion of the LISTDEF statement that applies to the tale space name.
  • You can specify the data from which partitions numbers are eligible when unloading the from image copies of partition-by-growth table spaces. This especially helps you to allow to unload the data from an image copy dataset containing data from partitions with a partition number greater that the value of the MAXPARTITIONS attribute of the target table space. The "Highest partition number allowed in IC of PBG TS (VUU093/ICMAXP)" parmlib parameter allows to specify the highest partition number that Db2 HPU is allowed to process and the IC_MAXPARTITIONS option allows to override the value set for VUU093/ICMAX (APAR PH30969).
  • TIMESTAMP WITH TIME ZONE data can be unloaded in native mode provided the data is not involved in a non-trivial SQL expression (i.e. limited to the data itself). The data itself can be the result of a support SQL expression (APAR PH26425).
  • The reliable processing of an input image copy that either is an inline image or was created by the COPYTOCOPY utility requires its pages to first be sorted. When the COPYDDN ddname option is used to provide Db2 HPU with an input image copy, it may be informed about the kind of input image copy by the INLINE or FROM_COPYTOCOPY options or may be requested to try to determine the type of the input image copy by searching the Db2 catalog for the image copy dataset name. The "Sort pages of undetermined type image copy (VUU094/SORTIC)" parmlib parameter allows to specify whether to sort the image copy when its type is unknown. Setting this parameter to YES might trigger some unnecessary sorts but makes the processing of any input image copy safer (APAR PH28442).
  • The algorithm that checks the integrity of the input datasets (image copies or LDS) read by Db2 HPU - when run in native mode - can include advanced controls (LEVEL1) dedicated to image copies. These new controls may be enabled at the step level with the IC suboption of the CHECK_INTEGRITY option and at the product level by setting the VUX038/ULCHECKINT with a value including the IC subparameter. To enable these new controls where the CHECK_INTEGRITY or VUX038/ULCHECKINT specifies LEVEL1, both the LDS and IC must be specified so that the controls performed against LDS remain enabled (APAR PI69203).
  • The COMMENT_CHAR(ASTERISK) subparameter of the "DB2 HPU additional features (VUU030/ULOPTNS)" parmlib parameters allows to enable to use of the asterisk (*) in column #1 to specify a comment line within an unload statement coded with the Db2 HPU native syntax (APAR PI95528).

Output

  • A new flexible date, time, and timestamp format is available (column-format-option). It is described by a string that specifies the sequence of the fields (year, month, date, hour, etc.) from the date, time, and timestamp to be displayed and the separators to be used.
  • The DDL of the unloaded table can be generated within the file containing the LOAD control cards. This feature can be requested with the LOADDDL TABLE option of the LOADDDN block (APAR PI93908).
  • The "Oldest year assumed for DECIMAL to DATE conversions when no explicit century given by the DECIMAL source data (VUU077/ULCENT)" parmlib parameter allows to specify the rule to apply when converting decimal values into dates when the century digits are missing from the string value (APAR83839).
  • Parmlib parameters allow to set the default value for the NULLPAD option. A specific parameter is dedicated to every applicable format, as listed below:
    • "Fill NULL with padding character, format USER (VUU075/NULLPAD)" sets the NNULLPAD default to apply for FORMAT USER is requested
    • "Fill NULL with padding character, format VARIABLE (VUU075/NULLPAD)" sets the NNULLPAD default to apply for FORMAT VARIABLE is requested
    • "Fill NULL with padding character, format EXTERNAL (VUU075/NULLPAD)" sets the NNULLPAD default to apply for FORMAT EXTERNAL is requested
    With the DELIMTED format, the NULL values can be unloaded as strings defined through the NULLVAL option of the DELIMITED block syntax and the "NULL processing in DELIMITED format (VUU078/ULNULLFL)" parmlib parameter. The NULLSTRING feature of the UNLOAD PLUS syntax is supported (APAR PH01897).
  • The "Request the display of implicit parameters (VUU083/DISPPARM)" parmlib parameter allows to request the values of the parameters not specified by the UNLOAD command to be systematically or conditionally written into a user-specified data set (APAR PH01897).
  • A title line can be generated for the data unload in DELIMITED format by specifying the TITLE ON option of the FORMAT block.
  • The "Expansion ratio for conversion to UTF8 CCSID (VUU068/ULUNIEXP)" parmlib parameter and its counterpart UNICODE_EXPANSION_RATIO option from the OPTIONS block allow to specify what expansion ratio (i.e. target data length divided by source data length) to apply to char/varchar data when a conversion from a non-UTF8 CCSID to a UTF8 CCSID is made (APAR PH01897).
  • The FMT_VAR_FB_IF_FIXED subparameter of the VUU030/ULOPTNS parameter allows to specify that the default RECFM for an unload in FORMAT VARIABLE of only fixed length columns is FB instead of VB.
  • The LIMIT option of the TEMPLATE block allows to specify that the template switching feature applies, i.e. that an alternate TEMPLATE is to be used when the estimated amount of data to unload exceeds a given size. This feature can be enabled by specifying TEMPLATE_LIMIT(YES) in the VUU030/ULOPTNS setting (APAR PH03781).
  • The behavior when unloading LOB or XML data in native mode without making sure of the consistency between the base table space and its related LOB or XML table space can be controlled through the "Processing when inconsistency LOB encountered (VUU084/ULLOBBHV)" and the "Maximum issued messages for inconsistency LOB (VUU085/ULLOBBHM)" parmlib parameters (APAR PH05424).
  • Db2 HPU can handle large block for output data sets. The TAPELBI subparameter for VUU030/ULOPTNS parmlib parameter must be set to either ALL to enable this feature for any output data set or to NOSPANNED to limit the feature to the non-spanned data sets (APAR PH04468).
  • The "Sign for zoned-decimal numeric values (VUU037/ULSIGZ)" parmlib parameter allows to specify not only the positive sign but also the negative sign for zoned-decimal data (APAR PH18216).
  • The ALTERNATE FORMAT sub-option for the INTERNAL option from the FORMAT block allows to specify an alternate format to be used when the INTERNAL format specified for a SELECT statement with a FROM LIST clause is not applicable to some tables involved by the related LISTDEF statement (APAR112161).
  • The negative sign character for zoned-decimal data can be specified through the ZONED_DEC_SIGN of the OPTIONS block and the "VUU037/ULSIGZ" parmlib parameter formerly used to set the positive sign of the zoned-decimal only. Due to this change, this parameter is now described as "Sign for zoned-decimal numeric values" in the Tools Customizer's interface (APAR116989).
  • The value (V) for the subparameter of the PIC option and the VUU018/ULPIC counterpart parameter allows to request no byte sign to be used for formatting of the non-float numeric data.
  • Db2 HPU can produce a report about its exploitation of the EXPLAIN statement when performing the OPTIMIZE_PART_RANGE feature (for logical unloads processed in native mode) and the LOCK feature (for logical unloads run in SQL access mode). The production of this report is driven by the setting of the VUU087/DISPEXPL parmlib parameter and, depending on the setting of the setting of the latter parameter, of the allocation of a user-defined DDname to the Db2 HPU step (APAR PH19457)
  • Creating output data sets - for OUTDDN and UNLDDN and LOADDDN - encrypted using ICSF key label is possible by specifying the KEYLABEL attribute either on the related DD or TEMPLATE statement (APAR116453).
  • The 'INTO clause behavior if field-name without output-data-type (VUU089/INTORUL1)' parmlib parameter and its corresponding GLOBAL level sysin option INTO_RULES, allows to change the behavior of an INTO clause when a field-name is specified without its output-data-type. output-data-type can therefore be taken either from a REFORMAT clause that applies or from the type of the column of the resulting table (APAR PH19262).
  • The DATE/TIME and NULL DATE/TIME delimiters used for the DELIMITED format that could be specified through the "DATE/TIME delimiter option (VUU031/DTDELIM)" and "NULL DATE/TIME delimiter option (VUU043/DTNULDLM)" parmlib parameters can now also be specified at the job level respectively through the DATE_DELIM and NULL_DATE_DELIM option of the GLOBAL OPTIONS block (APAR PH15510).
  • An error affecting the scale or the value of the SQL expressions involving a BIGINT constant and a decimal data (APAR PH35541) and evaluated in native mode may be fixed by adding FIX(APAR_PH35541) to the VUU030/ULOPTNS parmlib parameter. As some programs might successfully exploit the data unloaded for unloads affected by APAR PH35541, make sure none of the existing jobs relies on the erroneous behavior before enabling the fix. (APAR PH35541).
  • When a logical unload involving a ROWID column, Db2 HPU may not be able to determine if the ROWID column is defined with the GENERATED_ALWAYS attribute. This information is necessary to generate a suitable LOAD command (LOADDDN option) regarding the ROWID field. The ROWID_AS_GENERATED_ALWAYS_IF_UNDETERMINED subparameter of the VUU030/ULOPTNS parmlib parameter allows to indicate whether the ROWID fields are considered as GENERATED_ALWAYS fields by default, i.e. when Db2 HPU cannot determine the value of this attribute of the column (APAR PH36328).
  • The ONE_GDS_TEMPLATE option from the GLOBAL OPTIONS block allows to specify whether a unique Generation Data Set (GDS) must be created for a TEMPLATE describing a new dataset of a given Generation Dataset Group (GDG), i.e. with a name-expression such as gdg_name (+1) where gdg_name is the expression defining the GDG name. With ONE_GDS_TEMPLATE YES, a single dataset is created for every distinct value of gdg_name. At the first invocation of the TEMPLATE, Db2 HPU creates the GDS and reuses this dataset for any further invocation of the TEMPLATE for the same GDG name. This is useful to merge the output into a single GDS using a single TEMPLATE. With ONE_GDS_TEMPLATE NO or without any ONE_GDS_TEMPLATE option, a new GDS is created for every invocation of the TEMPLATE. This is useful to generate a distinct dataset for several unloads using a single TEMPLATE (APAR PI97040).
  • An issue (APAR PH44174) that affects the generation of the LOAD control cards (LOADDDN option) can be fixed by specifying FIX(APAR_PH44174) in the value of the VUU030/ULOPTNS parmlib parameter. When this issue is not fixed, the options for the LOAD utility applicable at the table level are not correctly taken into account - mostly ignored - if the INTERNAL format is requested for the related logical unload (SELECT statement and OUTDDN option). This issue does not necessarily prevent the generated LOAD controls cards from being exploited by subsequent processes - even as a SYSIN for the LOAD utility - but their content is not compliant with the planned design and, in this case, the Db2 HPU behavior is not consistent with the one that applies when an output format different from INTERNAL is requested. The fix of the issue consists in making the Db2 HPU behavior in this matter compliant with the expected and documented design which implies potential changes in the LOAD control cards generated by existing jobs that fulfill the conditions of occurrence of this issue. Therefore, before enabling the fix, such unloads and the programs exploiting the LOAD control cards they generate should be reviewed as some changes might be needed to take the impact of the fix into account (PH44174).

Troubleshooting

  • The Db2 HPU for z/OS status is displayed when running the QUIESCE utility is impossible. The text of the messages INZU176I and INZU406E (QUIESCE IMPOSSIBLE) now display the restricted state of the unloaded object that prevented the QUIESCE to be performed (APAR PI66139).
  • The report produced by the DISPEXPL feature can help better understand the reason of a failure or an unexpected behavior in the following use cases:
    • an unload run in native mode involving the OPTIMIZE_PART_RANGE feature,
    • an unload run in SQL mode involving the LOCK feature (APAR PH19457).
  • For easier determination of its root cause, the INZU063I UNSUPPORTED SELECT message gives a reason why the SELECT statement is not supported (APAR PH24825).
  • More detailed diagnosis information (RPL) is written to a SYSnnnn SYSOUT dataset whenever DDb2 HPU receives a completion code of 8 and a reason code of x'78 from a GET when reading the data from the LDS of a Db2 object (APAR PH27650).

Concurrency and consistency

  • For logical unload processed in native mode, Db2 HPU can honor the LOCK YES option by starting the table space to unload in RO restricted state. The new parmlib parameter "Technique to lock the unloaded tablespace (VUU074/ULLOCKTE)" allows to specify the technique used to lock the unloaded tablespace when LOCK(YES) is requested.
  • Db2 HPU can honor the LOCK YES option in SQL mode. The "Eligible processing mode(s) for locking data feature (VUU088/ULLOCKMO)" parmlib parameter allows to specify for which processing mode(s) the LOCK YES must be honored (APAR PH13896).
  • A retry mechanism allows a better concurrency between Db2 HPU unload steps and processes that allocate the Db2 HPU parmlib in exclusive mode. This especially helps submitting in parallel the Db2 HPU INZCHECK customization jobs that allocate the Db2 HPU parmlib in exclusive mode (PH47018).

Easier troubleshooting for error at record level

  • The HANDLE_RECORD_ID option of the GLOBAL OPTIONS block allows to get more information to determine the location of a record for which an error at record level is raised (APAR90466).

Syntax compatibility

  • The UNLOAD PLUS CNTLCARDS DB2 is supported. This feature was formerly ignored and this situation reported through the message INZU027W CNTLCARDS DB2 NOT SUPPORTED, WILL USE CNTLCARDS DB2LOAD INSTEAD and a completion code of 8. The new behavior can be enabled through the setting of the CNTLCARDS_DB2 subparameter of the VUU030/ULOPTNS parmlib parameter.
  • The ZONEDDECOVP option of the UNLOAD PLUS syntax is supported (APAR116989).
  • Db2 HPU now supports the two new syntax alternatives for NULL predicate introduced by the Db2 V12 function level 504.ISNULL and NOTNULL can be used instead of IS NULL and IS NOT NULL (APAR PH22201).

Control and reporting

  • The behavior of Db2 HPU when it cannot generate requested LOAD control cards that are fully compliant - i.e. directly usable - with the Db2 LOAD utility can be chosen through the ULRLDRC option or the VUU082/ULRLDRC parmlib parameter. This allow to request Db2 HPU either to ignore such a situation or to issue a warning messages when it occurs or to simply reject the situation by stopping the unload process (APAR PH01897).
  • The DISPLAY_PARMLIB feature gives information about format and syntax-dependent parameters by describing in the header how the values of this type of parameter are coded and by marking the format dependent parameters with (1) and the syntax-dependent parameters with (2) as in the following example:
    VUU017/ULTMSTP *(1)* = U(TMSTP_B)
    VUU071/LOSSCHAR *(2)* = HPU(IGNORE),ULPLUS(IGNORE),FASTU(IGNORE)
    (APAR PH17659)
  • Unload job allocating their output data set through TEMPLATE statement can be restarted (see RESTART feature). This allows to easily resume multiple unload jobs (data from several tables unloaded into distinct datasets) that have failed. When the RESTART feature is used, the RSTARTDD must be reserved to Db2 HPU (APAR PH32514).
  • You may enable a fix of the APAR PH36904 and choose for the mode of the fix by adding the FIX(APAR_PH36904 [,VERBOSE] ) value to the VUU030/ULOPTNS parmlib parameter (APAR PH36904).
  • The ability to request Db2 HPU to unload null indicators on more than one-byte fields exists since Db2 HPU 4.2 (APAR PI07343) but remained undocumented since then. The VUU014/ULNULL sets of parmlib parameters can specify up to four character null values and the U_NULLVAL and U_NULLTYPE may be specified in the value for the VUU030/ULOPTNS parmlib parameter to request Db2 Db2 HPU to support the NULLCHAR and NULLTYPE Unload Plus features. (GA)
  • In order to ease the tuning of the amount of memory to allocate to an Db2 HPU step, to facilitate the troubleshooting in case of memory outage and to avoid termination in ABEND, Db2 HPU can check the amount of memory available before launching an unload process and, if any potential lack of memory is estimated, report the situation and either skip the current process or flush the remaining unload processes. This feature can be invoked through the CHECK_MEMORY option or, by default, through the setting of the "Memory checking mode before starting an unload (VUU097/CHKMEM)" parmlib parameter (APAR PH34768).
  • The DISPLAY UTILITY feature allows to display progress information about the running Db2 HPU steps. This feature is enabled by setting the "Enable the display of Db2 HPU activity (VUU095/ENBDISP)" parmlib parameter to YES and requested by the DISPLAY UTILITY command of the INZTOOLS facility (APAR PH40463).
  • A feature allows to get warned on each execution of a logical unload run in native mode which output - unloaded data and related LOAD control cards - is based on an SQL expression containing either CHAR(decimal_value) or VARCHAR (decimal_value) evaluated as in Db2 V9. Specifying SIGNAL (V9_BIF_USE) in the value of the VUU030/ULOPTNS parmlib parameter requests Db2 HPU to issue messages to the MVS console (INZU583I) and to the SYSPRINT dataset (INZU584I) providing information to locate the unload in question. This feature is a substitute to the IFCID(376) records that are issued in the same situation for logical unloads run in SQL mode (APAR PH41470).
  • The messages related to the Db2 HPU QUIESCE feature can be externalized to the MVS console in order to make the monitoring of this phase of the process easier. This feature can be enabled by specifying the WTO keyword in the value of the "Quiesce process(VUU028/ULQSCEBH)" parmlib parameter (APAR PH42713).

Tuning and control of internal process

  • The parameter VUM038/QSSBFNO and the QSAM-BUFFER option allow Db2 HPU to set the number of data buffers for sequential QSAM (i.e. the BUFNO parameter of DCB QSAM) exclusively for the temporary dataset SORTIN used during SORT process. This capability is meant to help solving unexpected situations where using the same BUFNO value to access to the SORTIN data set as the BUFNO value used for QSAM access to any other data set (i.e. the VUM022/QSBUFNO parmlib parameter) causes troubles. A known use case is when the default sort program is DFSORT and zHPF is enabled (see the explanation of VUM038/QSSBFNO for details).