PM18687: SQLNonTransientException: No authorized routine named 'UPPER' of type 'FUNCTION' having compatible arguments was found

APAR status

• Closed as program error.

Error description

• Environment:
  
   - Product name (Build ID or version/patch level): IBM Rational
  Application Developer 7.5.5.1
  
  Reproducible Y/N: Y
  
  Description of problem:
  
  Caused by: java.sql.SQLNonTransientException: No authorized
  routine named 'UPPER' of type 'FUNCTION' having compatible
  arguments was found.
  SQLCODE=-440, SQLSTATE=42884, DRIVER=4.3.111DSRA0010E: SQL State
  =  42884, Error Code = -440
  
  
  Steps to reproduce:
  
  1) Create an EJB QL like SELECT  OBJECT(e) FROM Emp e where
  UCASE(e.name) like CONCAT(UCASE(?1),'%')
  2) Run a prepare for deployment
  3) Publish to Websphere Application Server
  4) Test the EJB with the Universal Test Client
  
  Workaround:
  
  Generated code needs to be modified manually using
  VARCHAR(3999), for example:
  
  findEmpCaseInsensitiveQuery_0=' select q1.\'ID\',  q1.\'NAME\',
   q1.\'DEPT_ID\' from AMTEAM.EMP q1 where  ( upper ( q1.\'NAME\')
  LIKE  concat ( upper (CAST(? AS VARCHAR(3999))), \'%\')) ';
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Generated SQL like concat ( upper (CAST(? AS
  VARCHAR(32672))), \'%\')) will cause
  java.sql.SQLNonTransientException: No authorized routine
  named "LIKE" of type "FUNCTION" having compatible arguments
  was found.. SQLCODE=-440, SQLSTATE=42884,
  DRIVER=4.3.111DSRA0010E: SQL State = 42884, Error Code =
  -440
  

Problem conclusion

• EJB 2.1 for CONCAT needs to generate VARCHAR(CONCAT(..),
  4000) to avoid SQLException on DB2 LUW.
  
  Fixed in Rational Application Developer v7.5.5.3
  

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:

Fix information

• Fixed component name

  RATL APP DEV WI

• Fixed component ID

  5724J1901

Applicable component levels

• R750 PSN

     UP