A fix is available
APAR status
Closed as program error.
Error description
SQLCODE170 AND SQLCODE171 WHEN USING SUBSTRING FUNCTION AND ITS FIRST ARGUMENT IS BINARY DATA TYPE. (This will be the enabling apar. Apar PI28192 will be the pre-conditioning apar).
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 10 and DB2 11 for z/OS users of the * * SUBSTRING built-in function. * **************************************************************** * PROBLEM DESCRIPTION: The SUBSTRING function is to return a * * substring of a string. * * * * An SQLCODE170 or SQLCODE171 can be * * returned when using the SUBSTRING * * built-in function when its first * * argument is a binary (or varbinary) * * data type or binary expression. * **************************************************************** * RECOMMENDATION: * **************************************************************** This apar PI40667 is the enabling apar. Apar PI28192 is the pre-conditioning apar and should already be applied. An SQLCODE170 or SQLCODE171 can be returned when using the SUBSTRING built-in function when its first argument is a binary or varbinary data type or binary expression. The following queries (using the SUBSTR and SUBSTRING functions) which should return the same result, helps illustrate the problem for the SUBSTRING function with a binary input argument. SELECT HEX(SUBSTRING(BINARY('ABD', 3), 1)) FROM SYSIBM.SYSDUMMY1; This example returns an SQLCODE170 from DSNXOBFF. SELECT HEX(SUBSTR(BINARY('ABD', 3), 1)) FROM SYSIBM.SYSDUMMY1; This example returns the expected result +--------+ 1_| C1C2C4 | +--------+ However, the first query returns an SQLCODE instead of the expected or correct result. This apar will help provide the missing support for the SUBSTRING built-in function having a binary input so that it functions similarly to the SUBSTR built-in function. This apar will deliver the bindtime support and enable the new function and requires the pre-conditioning apar PI28192 to have previously been applied.
Problem conclusion
The code is modified in DB2 to provide the functionality for the SUBSTRING function to support a binary input argument. This apar serves as the enabling or bind time support to be applied after apar PI28192 which provides the pre-conditioning support. This apar will enable the new functionality. Additionally, the following publication changes are being made to the DB2 10 and 11 for z/OS SQL Reference along with apar PI40667 for the BINARY SUBSTRING built-in function. The description for BINARY SUBSTRING has changed. Here is the new text. ---------------------------------------------------------------- binary-expression An expression that specifies the string from which the result is derived. The string must be a built-in binary string. The result of the function is a binary string. A substring of binary-expression is zero or more contiguous units of binary-expression. start An expression that specifies the position within binary-expression to be the first byte of the result. It must be an integer value. Start can be negative or zero. (The length attribute of a varying-length string is its maximum length.) A value of 1 indicates that the first byte of the substring is the byte unit of binary-expression. length An expression that specifies the length of the resulting substring. If binary-expression is a fixed-length string, omission of length is an implicit specification of LENGTH(binary-expression) - start + 1 from the start position to the last position of binary-expression. If binary-expression is a varying length string, omission of length is an implicit specification of zero or CHARACTER_LENGTH(binary-expression) - start + 1, whichever is greater. If the resulting length is zero, the result is an empty string. If specified, length must be a value of an integer data type. The value must be greater than or equal to 0. If a value greater than n is specified, where n is the length attribute of binary-expression - start + 1, then n is used as the length of the resulting substring. ---------------------------------------------------------------- Also, a link is being provided for a "rigorous description" of the actual length and result (which has been changed slightly). Please note that information about the BINARY SUBSTRING changes 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. Additional Keywords: SQLCODE170 SQLCODE171 SQLSUBSTRING SQLSUBSTR SQLBINARY SQLVARBINARY
Temporary fix
Comments
APAR Information
APAR number
PI40667
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
A10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2015-05-07
Closed date
2015-10-19
Last modified date
2015-12-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI32169 UI32170
Modules/Macros
DSNTIAM DSNXEBR DSNXGRTM DSNXGSFN DSNXOBFA DSNXOBFF DSNXOBF3 DSNXOBF4 DSNXOFN2
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
02 December 2015