PM65722: OVERLAY OR VARIOUS ABENDS WHEN USING THE VARCHAR BUILT-IN FUNCTION WITH DECIMAL INPUT
Without this APAR installed all DB2 9 and DB2 10 for z/OS users of the built-in function VARCHAR with decimal input or the CAST AS specification with decimal input and VARCHAR or CHAR output are impacted.
- Version 9 only problem ABEND0C7 at DSNXRSBC +546C for an SQL query using the VARCHAR function with decimal input. Alternately, an overlay of storage may occur. This problem only occurs on Version 9. The overlay is 1 byte. This problem can also occur with CAST (decimal as VARCHAR)
- Version 10 only problem is that DB2 Version 10 currently returns the new incompatible semantics for the VARCHAR(decimal) function for static SQL bound on a prior release of DB2.The new semantics are also returned for CAST (decimal as VARCHAR or CHAR -> see PM66095).
- The following shows an example of a possible overlay with the VARCHAR(decimal) built-in function. The problem occurs when a literal value is negative and a value is not specified to the left of the decimal point.
SELECT HEX(VARCHAR(-.1001)),VARCHAR(-.1001), IBMREQD
In this example, the overlay causes a subsequent 0C7 on the same SQL statement.
SELECT HEX(VARCHAR(-.1)),VARCHAR(-.1), IBMREQD
DUMP00 TITLE=V91A,ABND=0C7-00000000,U=SYSADM ,M=(N),C=910.RDS
DUMP TAKEN TIME=xx.xx.xx DATE=yy/yy/yyyy
- WORK-AROUND by specifing a literal value with 0 before the decimal point. For the above example, '-0.1001', will avoid the overlay.