A fix is available
APAR status
Closed as program error.
Error description
An ABENDU1101 or ABENDS0C4 can occur when attempting to unload, in native mode either from the online table space or from image copy data sets, the data from a table having a column which description in the SYSIBM.SYSCOLUMNS fulfills the following conditions: - the value of the COLTYPE column is VARCHAR or VARGRAPHIC - the value of the DEFAULT column is either 5 or 6. For example, this situation occurs if the CREATE TABLE statement of the involved table contains the following column definition: C1 VARCHAR(6) FOR BIT DATA NOT NULL WITH DEFAULT X'202020202020' The above conditions are only mandatory conditions but are not sufficient ones. The issue more likely occurs if at least one of the following additional conditions is true: - case 1) the tablespace involved in the unload is a not partitioned tablespace containing a single table. - case 2) the table space involved in the unload is partitioned and only a restricted subset of partitions of the partitioned table space to be unload are actually read -- either because a PART option was specified -- or because the SELECT statement of the logical unload specifies a WHERE clause and the OPTIMIZE_PART_RANGE feature is enabled - case 3) the table involved in the unload has versioning Local fix: If case 2 applies, adding OPTIM_LEVEL 0 to the GLOBAL options block or disabling the OPTIMIZE_PART_RANGE feature can prevent the issue from occurring. For all the cases, specifying the DB2 FORCE option - and replacing the PART option, if any, by an SQLPART one - solves the issue.
Local fix
If case 2 applies, adding OPTIM_LEVEL 0 to the GLOBAL options block or disablming the OPTIMIZE_PART_RANGE feature can prevent the issue from occuring. For all the cases, specifying the DB2 FORCE option - and replacing the PART option, if any, by an SQLPART one - solves the issue.
Problem summary
**************************************************************** * USERS AFFECTED: * * Users of High Performance Unload * **************************************************************** * PROBLEM DESCRIPTION: * * PH16192 * * Any unload fails with completion code of 8 after having * * issued the following message: * * INZI193E DB2 ERROR: éDB2 FOR OS/390èéODBC DRIVERèéDSN11015è * * DSNT408I SQLCODE = -4746, ERROR: THE STATEMENT * * CANNOT BE PROCESSED F * * ACCELERATED QUERY (REASON 1) * * DSNT418I SQLSTATE = 560D5 SQLSTATE RETURN CODE * * DSNT415I SQLERRP = DSNLXMRF SQL PROCEDURE * * DETECTING ERROR * * DSNT416I SQLERRD = -13 0 0 -1 0 0 SQL DIAGNOSTIC * * INFORMATION * * DSNT416I SQLERRD = X'FFFFFFF3' X'00000000' * * X'00000000' X'FFFFFF * * X'00000000' X'00000000' SQL DIAGNOSTIC * * INFORMATION * * ERRLOC=1:13:2 * * This occurs if the following conditions are fullfilled: * * - the Db2 settings enables the Query Acceleration feature * * (i.e. the Db2 ZPARM QUERY_ACCELERATION parameter is not set * * to 1 = NONE) * * - the SYSIBM.SYSDUMMY1 table is not excluded from the * * Accelerator * * * * PH17242 * * Data from an unexpected partition is unloaded whenever * * - an unload run in native mode requests the data from * * FlashCopy data set be unloaded (i.e. the option COPYDDN * * ddname, FLASHCOPY is specified) * * - one of the DD statement specified for COPYDDN ddname does * * not allocate the dataset for the relevant partition * * * * The following examples are affected cases. * * case a. With the following unload step * * //MYCPY03 DD * * DISP=SHR,DSN=FlashCopy.dataset.for.partition.number2 * * //MYOUT DD * * DISP=(NEW,CATLG,DELETE),DISP=output.dataset.for.all.partitio * * ns * * //SYSYN DD * * * UNLOAD TABLESPACE PART 3 * * DB2 NO * * COPYDDN MYCPY,FLASHOPY * * SELECT * FROM me.mytable * * OUTDDN MYOUT * * /* * * the MYOUT data set contains only data from partition #2 * * instead of data from partition #3. * * * * case b. With the following unload step * * //MYCPY01 DD * * DISP=SHR,DSN=FlashCopy.dataset.for.partition.number1 * * //MYCPY02 DD * * DISP=SHR,DSN=FlashCopy.dataset.for.partition.number2 * * //MYCPY03 DD * * DISP=SHR,DSN=FlashCopy.dataset.for.partition.number2 * * //MYCPYnn DD ... * * //MYOUT DD * * DISP=(NEW,CATLG,DELETE),DISP=output.dataset.for.all.partitio * * ns * * //SYSYN DD * * * UNLOAD TABLESPACE * * DB2 NO * * COPYDDN MYCPY,FLASHOPY * * SELECT * FROM me.mytable * * OUTDDN MYOUT * * /* * * the MYOUT data set contains data from partition #2 and no * * data from partition #3. * * * * case c. With the following unload step * * //MYCPY01 DD * * DISP=SHR,DSN=FlashCopy.dataset.for.partition.number1 * * //MYCPY02 DD * * DISP=SHR,DSN=FlashCopy.dataset.for.partition.number3 * * //MYCPY03 DD * * DISP=SHR,DSN=FlashCopy.dataset.for.partition.number2 * * //MYCPYnn DD ... * * //MYOUT01 DD * * DISP=(NEW,CATLG,DELETE),DISP=output.dataset.for.partition.nu * * mber1 * * //MYOUT02 DD * * DISP=(NEW,CATLG,DELETE),DISP=output.dataset.for.partition.nu * * mber2 * * //MYOUT03 DD * * DISP=(NEW,CATLG,DELETE),DISP=output.dataset.for.partition.nu * * mber3 * * ... * * //MYOUTNN DD * * DISP=(NEW,CATLG,DELETE),DISP=output.dataset.for.partition.nu * * mberNN * * //SYSYN DD * * * UNLOAD TABLESPACE * * DB2 NO * * COPYDDN MYCPY,FLASHOPY * * SELECT * FROM me.mytable * * OUTDDN MYOUT * * /* * * the MYOUT02 data set contains data from partition #3 and the * * MYOUT03 data set contains data from partition #2. * * Such usage error cases that lead to incorrect output with no * * notification to the user should be trapped. * * * * PH17958 * * During a logical unload run in native mode an ABENDS0CA * * occurs if the specified SELECT statement has a WHERE clause * * with a predicate requiring the comparison of a DECIMAL(n,p) * * type expression and a constant greater than the maximum * * value of the DECIMAL(n,p) data type. Such a comparision is * * needed for the evaluation of predicate involving the * * following operators : =, <, >, IN and NOT IN. * * * * PH18216 * * When a user searches for a given parameter in Tools * * Customizers panels, he usually knows the key (Vxxxnnn) * * and/or shortname of the parameter. * * Currently, these alternate designations are written at the * * end of the full parameter label such as in the following * * example: * * ............................................................ * * ... * * TCUSTMZR Product Parameters: DB2 HPU 11:57:49 * * Command ===> Scroll ===> CSR * * * * Complete the following tasks to customize the products. The * * required tasks, * * required steps within a required or selected task, and * * required parameters * * are preceded by an asterisk (*). Ensure that values are * * specified for the * * required parameters. Press End to save and exit. * * * * Commands: SAVE VERIFYOFF * * Line Commands: / - Select * * * * Product customization library : * * MZLFLB.TCZ.$S0W1$.INZ510.FLB * * More: - + * * Lock the tables in the table space (VUU012/ULLOCK) * * NO (YES, NO) * * Specify the technique used to lock the unloaded object * * (VUU074/ULLOCKTE) * * LOCK (LOCK, START_RO) * * Quiesce the table space (VUU013/ULQSCE) NO List * * NULL indicator, format USER (VUU014/ULNULL) * * FF00 * * NULL indicator, format DSNTIAUL (VUU014/ULNULL) * * NULL indicator, format VARIABLE (VUU014/ULNULL) * * ............................................................ * * ... * * As a consequence, searching this text that is not always * * written on the same column is not easy. Formatting the * * parameter label so that the alternate designation is right * * justified would make searching for a parameter much easier. * * The same parameters as above, presented with the new * * formatting rules, would look as in the following example: * * ............................................................ * * ... * * TCUSTMZR Product Parameters: DB2 HPU 12:03:20 * * Command ===> Scroll ===> CSR * * * * * * Complete the following tasks to customize the products. The * * required tasks, * * required steps within a required or selected task, and * * required parameters * * are preceded by an asterisk (*). Ensure that values are * * specified for the * * required parameters. Press End to save and exit. * * * * * * Commands: SAVE VERIFYOFF * * Line Commands: / - Select * * * * * * Product customization library : * * MZLFLB.TCZ.$S0W1$.INZ510.FLB4 * * * * More: - + * * Lock the tables in the table * * space.....................VUU012/ULLOCK * * NO (YES, NO) * * Specify the technique used to lock the unloaded * * object.VUU074/ULLOCKTE * * LOCK (LOCK, START_RO) * * Quiesce the table * * space................................VUU013/ULQSCE * * NO (YES, NO, YES_AND_SECURE, NO_AND_SECURE) * * NULL indicator, format * * USER............................VUU014/ULNULL * * FF00 * * NULL indicator, format * * DSNTIAUL........................VUU014/ULNULL * * * * PH19262 * * When an INTO clause explicitly specify a target field (i.e. * * specifies a name), the output format that applies to this * * field is the default format applicable to the data type of * * the field altered by the explicit output format specified * * for this field. * * As a consequence, the generic format changes specified by a * * REFORMAT clause are ignored for the fields that are * * explicitly named by the INTO clause. * * When the target field must be named explicitly (the column * * name of the target table is different from its counterpat * * column in the source table or the field in question is an * * SQL expression that does not equal to a column name) by the * * INTO clause, a REFORMAT clause specification that matches * * the data type of this field is not taken into account. * * When the INTO clause must be used to specify a format change * * that only apples to a given field and not to all the fields * * having the same data type and a REFORMAT clause is also * * needed to specify generic format changes, the user must * * write an INTO clause where only the fields with specific * * changes can be name. The other fields must not be named in * * order to ensure the REFORMAT clause specifications apply. If * * the table has a lot of columns and the column for which a * * specific format is needed has a rank with a big value, the * * INTo clause that must specify as many commas as there are * * unchanged columns which is error prone. * * There should be a way to request the output format for a * * field specified by the INTO clause is the one specified at a * * more general level than the field level, i.e. * * REFORMAT clause * * OPTIONS block at the SELECT level * * OPTIONS block at the UNLOAD level if FORMAT USER and for any * * FORMAT option if OPALLFMT is set to YES * * parmlib parameters * * * * PH19393 * * Most of the messages normally displayed after INZI020I are * * missing from the SYSPRINT dataset. * * This occurs if the following conditions are fullfilled: * * - the fix for PH03885 / PB54338 is applied * * - the SYSPRINT is allocated as a multi-volume dataset (i.e. * * with UNIT=(unit_name,n) where n > 1) * * * * PH19859 * * Db2 HPU can exploit zIIP processor for processing the output * * format process, even when CCSID conversion is involved. * * * * PH20349 * * When unloading the data from a global image copy or a * * concatenation of several image copies of a partitioned * * tablespace, an abend or an error - e.g. the INZRE42 error is * * reported - can occur when the following conditions are * * fulfilled: * * - the tablespace is partitioned and compressed * * - the image copy pages must be sorted * * - either because the unload statement specifies the * * INLINE, FROM_COPYTOCOPY or MERGE_IIC option (COPYDDN ddname * * option) * * - or because Db2 HPU determines, from the Db2 catalog * * (COPYDDN LAST_IC/-integer option or COPYDDN ddname with * * CHECK option), the image copy type requires to be sorted * * - a partition which PGNUM field - in the underlying LDS * * pages - leftmost bit is 1 contains compressed rows * * The number of the partitions that might be affected by the * * issue depends on the number of the number of bits used for * * the PGNUM field that is documented in the following two * * tables from the Db2 Diagnosis guide manual: * * Table 60. Number of bits used to identify partition number * * for non-EA-enabled table spaces * * Table 61. Number of bits used to identify partition number * * for EA-enabled table spaces * * Example: for a tablespace with DSSIZE(64G), the PGNUM field * * for the partition 128 is equal to 8000NNNN. If this * * partition contains compressed rows, the issue described * * above occurs. * * * * PH20378 * * The sequence of the rows in the unloaded data is not * * compliant with the key specified by the ORDER clause (either * * ORDER BY or ORDER CLUSTER). * * This can occur if the following conditions are fullfilled: * * - the unload is run in native mode * * - the sort key contains a vargraphic column from a table * * encoded in unicode * * - the indexscan feature, if involved, is not used with a * * 100% ratio * * * * PH20618 * * Db2 HPU does not set the RECFM,LRECL,BLKSZ DCB attributes of * * the data set containing the unloaded data when the following * * conditions are fulfilled : * * - the data is unloaded from a partitioned table space or * * from image copies taken at the data set level * * - the unload control cards require the data be unloaded per * * partition (i.e. an output data set is dedicated to each * * partition) * * - there is no input data for a partition * * - for the partition related to the affected output data set * * (the one with incorrect DCB): * * -- the input data set contains no data * * -- it is the last partition processed by a thread but is not * * the only one processed by this thread; this condition cannot * * be checked by examining the Db2 HPU report * * Example: for the unload of 10 partitions of a table space, * * with PARALLELISM 4. Let's assume the 4 allowed threads * * (named T1 to T5 hereafter) process the partitions as * * follows: * * Let's assume that: * * - T1 processes partitions 1 and 5, * * - T2 processes partitions 2, 6 and 8, * * - T3 processes partition 3, * * - T4 processes partitions 4, 7, 9, 10. * * If any of the involved partition is empty, only partitions * * 5, 8 and 10 are affected by the issue. * * * * PH20621 * * An ABENDU1101 or ABENDS0C4 can occur when attempting to * * unload, in native mode either from the online table space or * * from image copy data sets, the data from a table having a * * column which description in the SYSIBM.SYSCOLUMNS fulfills * * the following conditions: * * - the value of the COLTYPE column is VARCHAR or VARGRAPHIC * * - the value of the DEFAULT column is either 5 or 6. * * For example, this situation occurs if the CREATE TABLE * * statement of the involved table contains the following * * column definition: * * C1 VARCHAR(6) FOR BIT DATA NOT NULL WITH DEFAULT * * X'202020202020' * * The above conditions are only mandatory conditions but are * * not sufficient ones. The issue more likely occurs if at * * least one of the following additional conditions is true: * * - case 1. the tablespace involved in the unload is a not * * partitioned tablespace containing a single table. * * - case 2. the table space involved in the unload is * * partitioned and only a restricted subset of partitions of * * the partitioned table space to be unload are actually read * * - either because a PART option was specified * * - or because the SELECT statement of the logical unload * * specifies a WHERE clause and the OPTIMIZE_PART_RANGE feature * * is enabled * * - case 3. the table involved in the unload has versioning * * * * PH20642 * * An ABENDS0C4 intermittently occurs if the following * * conditions are fulfilled for an UNLOAD statement involving * * several logical unloads: * * - at least one logical unload is processed in SQL mode due * * to an unsupported SQL statement (i.e. the INZU063I * * UNSUPPORTED SELECT message is issued) * * - for at least one logical unload run in native mode, the * * involved table must be locked (e.g. LOCK YES Db2 HPU option * * is specified) via an SQL LOCK statement (i.e. the * * VUU074_ULLOCKTE parmlib parameter is set to LOCK) * * - if the VUU088/ULLOCKMO parmlib parameter is available, it * * is set to NATIVE_ONLY ou left blank * * * * PH21356 * * The display of parmlib parameters values triggered by the * * occurrence of an error (ABEND or complettion code >= 8) does * * not work as expected if the following conditions are * * fullfilled: * * - the DISPLAY_PARMLIB option is specified * * - the DISPPARM parmlib parameter is left blank. * * The following issues occur: * * Case #1. the parmlib parameters are correctly displayed but * * the ddname where the parameters have been printed is missing * * from the SYS00001 (or SYSOUT if allocated) dataset wherethis * * information is expected. * * Case #2. the parameters values are not written at all if * * DISPLAY_PARMLIB IF,parms_ddname is specified and * * parms_ddname is not allocated in the JCL * * * * PH21357 * * The mention of LISTDEFTBV is missing from the following * * items of Tools Customizer: * * - the help text of "Return code if an object is missing * * (VUU024/UNLZLRC)" * * - the label and help text for "Return code if no object is * * returned by LISTDEF (VUU024/UNLZLRC)" * * * * PH21358 * * The file displaying the implicit parameters or the file * * reporting the information retrieved by Db2 HPU from the * * explain tables contain incorrect or duplicated header as * * detalied below: * * 1- When VUU083/DISPPARM is set to YES,ddname, the following * * header is duplicated: * * ************************************************************ * * ******************** * * * LIST OF IMPLICIT PARAMETERS * * * * * * AS REQUESTED BY VUU083/DISPPARM = YES,ddname * * * * * ************************************************************ * * ******************** * * * * 2- When VUU087/DISPEXPL is set to YES,ddname, the following * * header is added * * ************************************************************ * * ******************** * * * LIST OF IMPLICIT PARAMETERS * * * * * * AS REQUESTED BY VUU083/DISPPARM = YES,ddname * * * * * ************************************************************ * * ******************** * * * * PH21360 * * There is a typo in the message text for INZU405E LOADDDN * * CANNOT BE GENERERATED ON TAPE TEMPLATE. The word GENERERATED * * should be corrected in GENERATED. * * * * PH21361 * * Db2 HPU exploits zIIP processors for a logical unload * * (SELECT statement and OUTDDN option) run in native mode * * requesting the data be unloaded in INTERNAL format except if * * the following conditions are fulfilled: * * - the data to be unloaded is from a non partitioned table * * space * * - the logical unlooad does not specify any ORDER or WHERE * * clause * * As nothing actually prevents Db2 HPU to exploit zIIP * * processors in such a case, this restriction should be * * lifted. * **************************************************************** * RECOMMENDATION: * * Apply the PTF. * ****************************************************************
Problem conclusion
Code has been changed to fix this problem.
Temporary fix
PH16192 Remove/exclude the SYSIBM.SYSDUMMY1 table from the Accelerator. PH17958 If applicable, consider running the unload in SQL mode by specifying the DB2 FORCE option. PH19393 If applicable, consider alocating the SYSPRINT data set - that is usually not a big data set - as a mono-volume (UNIT=(unit_name) or UNIT=(unit_name,1)) PH20378 If applicable, run the unload in SQL mode by specifying the DB2 FORCE option. PH20618 If applicable, consider unloading in SQL mode by specifying the DB2 FORCE option and adding an SQLPART option or replacing the PART option -if any - by an SQLPART one, or if the partition with the bigger partition number is not empty, disable parallelism through the PARALLELISM 1 option. PH20621 If case 2 applies, adding OPTIM_LEVEL 0 to the GLOBAL options block or disabling the OPTIMIZE_PART_RANGE feature can prevent the issue from occurring. For all the cases, specifying the DB2 FORCE option - and replacing the PART option, if any, by an SQLPART one - solves the issue. PH20621 If case 2 applies, adding OPTIM_LEVEL 0 to the GLOBAL options block or disabling the OPTIMIZE_PART_RANGE feature can prevent the issue from occurring. For all the cases, specifying the DB2 FORCE option - and replacing the PART option, if any, by an SQLPART one - solves the issue. PH20642 If applicable, force the use of SQL mode for all logical unloads by specifying the DB2 FORCE option.
Comments
×**** PE20/02/06 FIX IN ERROR. SEE APAR PH21832 FOR DESCRIPTION ×**** PE20/02/06 FIX IN ERROR. SEE APAR PH21840 FOR DESCRIPTION ×**** PE20/03/23 FIX IN ERROR. SEE APAR PH23549 FOR DESCRIPTION ×**** PE20/12/23 FIX IN ERROR. SEE APAR PH30121 FOR DESCRIPTION ×**** PE21/01/13 FIX IN ERROR. SEE APAR PH33305 FOR DESCRIPTION ×**** PE21/01/13 FIX IN ERROR. SEE APAR PH33305 FOR DESCRIPTION
APAR Information
APAR number
PH20621
Reported component name
DB2 HI PERF UNL
Reported component ID
5697F4400
Reported release
510
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2020-01-03
Closed date
2020-01-23
Last modified date
2021-04-27
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI67516
Modules/Macros
INZTDSC INZ$$PRM INZUTILK INZTVAR
Fix information
Fixed component name
DB2 HI PERF UNL
Fixed component ID
5697F4400
Applicable component levels
R510 PSY UI67516
UP20/02/04 P F002
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSZJXP","label":"DB2 Tools for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"5.1.0"}]
Document Information
Modified date:
28 April 2021