PM96959: INCORRECT PADDING WHEN EVALUATING AN SQL EXPRESSION LIKE CHAR(VARCHAR_CONSTANT_EXPRESSION)

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• In native mode (DB2 NO or DB2 YES with supported SELECT
  statement), the padding used when evaluating an SQL expression
  such as CHAR(varchar_constant_expression) is not correct.
  
  This occurs when the varchar_constant_expression is an
  expression which result is a constant (i.e. no table column is
  involved) varchar data having a length (lenght attribute of the
  varying character string) greater than the actual length of the
  constant (length of the character string).
  while the following content is expected
  
  C44444
  100000
  
  With the more complex expression below
  
  CHAR(VALUE(DIGITS(NULLIF(DECIMAL(1,9,0),DECIMAL(0,9,0))),' '))
  
  the output field is
  
  400000000
  000000000
  
  instead of
  
  444444444
  000000000
  
  PB12018
  

Local fix

• consider rewording the SQL expression to avoid creating a
  varchar constant with a length different from the actual length.
  As the result is a constant the simplest manner to solve the
  issue is to replace the whole expression by its result.
  
  In the example above, the CHAR(VARCHAR('A', 5)) can be replaced
  by CHAR(VARCHAR('A', 1), 5)
  or even CHAR('A     ') that give the same result and are
  processed correctly by DB2 HPU.
  
  CHAR(VALUE(DIGITS(NULLIF(DECIMAL(1,9,0),DECIMAL(0,9,0))),' '))
  can be replaced by CHAR('         ') or
  CHAR(VALUE(DIGITS(NULLIF(DECIMAL(1,9,0),DECIMAL(0,9,0))),
  '         ')) to get the correct result.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of DB2 High Performance Unload.        *
  ****************************************************************
  * PROBLEM DESCRIPTION: In native mode (DB2 NO or DB2 YES with  *
  *                      supported SELECT statement), the        *
  *                      padding used when evaluating an SQL     *
  *                      expression such as                      *
  *                      CHAR(varchar_constant_expression) is    *
  *                      not correct.                            *
  *                      This occurs when the                    *
  *                      varchar_constant_expression is an       *
  *                      expression which result is a constant   *
  *                      (i.e. no table column is involved)      *
  *                      varchar data having a length (length    *
  *                      attribute of the varying character      *
  *                      string) greater than the actual length  *
  *                      of the constant (length of the          *
  *                      character string).                      *
  *                      For example : With the following        *
  *                      expression                              *
  *                      CHAR(VARCHAR('A', 5))                   *
  *                      the output field is (in hexadecimal     *
  *                      view)                                   *
  *                      C0000                                   *
  *                      10000                                   *
  *                      while the following content is          *
  *                      expected                                *
  *                      C44444                                  *
  *                      100000                                  *
  *                      With the more complex expression below  *
  *                      CHAR(VALUE(DIGITS(NULLIF                *
  *                      (DECIMAL(1,9,0),DECIMAL(0,9,0))),' '))  *
  *                      the output field is                     *
  *                      400000000                               *
  *                      000000000                               *
  *                      instead of                              *
  *                      444444444                               *
  *                      000000000                               *
  *                                                              *
  *                      PB 12018                                *
  ****************************************************************
  * RECOMMENDATION: APPLY the PTF.                               *
  ****************************************************************
  The inznode, inznode modules were fixed.
  

Problem conclusion

• APPLY the PTF.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Modules/Macros

• INZUTILK
  

Fix information

• Fixed component name

  DB2 HI PERF UNL

• Fixed component ID

  5697F4400

Applicable component levels

• R410 PSY UK98654

     UP13/10/27 P F310

• R420 PSY UK98655

     UP13/10/27 P F310

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.