PM59942: SQLCODE132 WHEN NESTED CONCAT FUNCTION IN A LIKE PREDICATE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• An SQL statement like this:
  
  SELECT GETVARIABLE('SYSIBM.VERSION'),GETVARIABLE('SYSIBM.SSID')
  FROM   SYSIBM.SYSAUXRELS
  WHERE  TBOWNER LIKE CONCAT('MD',CONCAT('M','D'))  ;
  
  receives:
  
  DSNT408I SQLCODE = -132, ERROR:  AN OPERAND OF LIKE IS NOT VALID
  DSNT418I SQLSTATE   = 42824 SQLSTATE RETURN CODE
  DSNT415I SQLERRP    = DSNHSM2P SQL PROCEDURE DETECTING ERROR
  DSNT416I SQLERRD    = 5  0  0  -1  107  2702 SQL DIAGNOSTIC
  INFORMATION
  DSNT416I SQLERRD    = X'00000005'  X'00000000'  X'00000000'
  X'FFFFFFFF'
  X'0000006B'  X'00000A8E' SQL DIAGNOSTIC
           INFORMATION
  

Local fix

• Replace the outer CONCAT with the double vertical bars, for
  instance: LIKE 'MD'|| CONCAT('M','D');
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the LIKE predicate in DB2 for z/OS  *
  *                 V10.1.                                       *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQLCODE132 is returned for a nested     *
  *                      CONCAT operator used in the LIKE        *
  *                      predicate.                              *
  *                      The precompiler returns message         *
  *                      DSNH132I for the same syntax.           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The following illustrates the problem:
  
  For the following two queries, DB2 returns SQLCODE -132:
  
  SELECT COL1 FROM T1
  WHERE COL2 LIKE CONCAT('A',CONCAT('B','%'));
  
  SELECT COL1 FROM T1
  WHERE COL2 LIKE CONCAT('A',CHAR('%'));
  
  SQLCODE = -132, ERROR:  AN OPERAND OF LIKE IS NOT VALID
  SQLSTATE   = 42824 SQLSTATE RETURN CODE
  SQLERRP    = DSNHSM2P SQL PROCEDURE DETECTING ERROR
  
  There is a rule that the pattern-expression in the LIKE
  predicate must not contain a nested function invocation,
  but CONCAT should be treated as an operator, not as a function
  invocation, and therefore SQLCODE -132 should not be issued
  for the above queries.
  
  The following query receives SQLCODE132 because of the
  nested function invocation in the LIKE predicate, which is
  correct, and will not be changed by this APAR:
  
  SELECT COL1 FROM T1
  WHERE COL2 LIKE MYUDF1(MYUDF2('B'),1);
  
  Additional keywords: MSGDSNH132I SQLCODE132
  

Problem conclusion

• DB2 code is corrected so that SQLCODE -132 is no longer issued
  for a nested CONCAT in the pattern-expression of the LIKE
  predicate.
  

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:

  UK77494

Modules/Macros

• DSNHSM2P
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK77494

     UP12/04/13 P F204

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.