DB2 FOR LUW / DB2FORLUW / A10 - DB2

I you REVOKE EXECUTE on a procedure containing a VARCHAR as 
input parameter using Control Center, it may result in producing 
an incorrect SQL statement to run resulting in a SQL0104N error. 
 
 
Here is an example on how you could get this error: 
 
    1. Open the 'Control Center'. 
    2. Expand the SAMPLE database and go to: 'Application 
Objects --> User Defined Functions' 
    3. Right-click on 'MON_GET_BUFFERPOOL' from schema SYSPROC 
and select 'Privileges...' to open the dialog box. 
    4. Click 'Add User...', choose a username and click 'OK'. 
    5. Select the added user and choose 'YES' from the fly-out 
menu under 'Privileges: EXECUTE'. 
    6. Click 'Apply' to apply the changes to the user. 
    7. Leave the dialog box with 'Cancel'. 
    8. Right-click on 'MON_GET_BUFFERPOOL' and select 
'Privileges...' to  open the dialog box again. 
    9. Select the added user and choose 'NO' from the fly-out 
menu under  'Privileges: EXECUTE' to revoke the previously 
granted privilege. 
   10. Click 'Apply' to apply the changes to the user and you 
will receive above mentioned SQL0104N: 
 
[IBM][CLI Driver][DB2/NT64] SQL0104N  An unexpected token 
"VARCHAR" was found following "BUFFERPOOL("SYSIBM".".  Expected 
tokens may include:  "LONG".  SQLSTATE=42601 
 
If you do not take steps 7. and 8. (which means as long as you 
don't close the dialog box) then the SQL0104N will not occur. 
The 'Show SQL' button returns different results for these two 
cases. 
 
Working SQL: 
REVOKE EXECUTE ON FUNCTION 
SYSPROC.MON_GET_BUFFERPOOL(VARCHAR(),INTEGER)FROM USER DB2TEST 
RESTRICT; 
 
Non-working SQL: 
REVOKE EXECUTE ON FUNCTION 
"SYSPROC".MON_GET_BUFFERPOOL("SYSIBM".VARCHAR,"SYSIBM".INTEGER)F 
ROM USER DB2TEST RESTRICT;

**************************************************************** 
* USERS AFFECTED:                                              * 
* all                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to version 10.1 Fix Pack 5                           * 
****************************************************************

First fixed in version 10.1 Fix Pack 5

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

11.08.2014
13.07.2015
13.07.2015