DB2 FOR LUW / DB2FORLUW / 970 - DB2

Creating a task in the Task Center may fail with the message 
below if the special register USER contains extra blank 
characters. 
 
ICM0036N  An SQL error occurred while updating access control 
list "ACL_422". 
[IBM][CLI Driver][DB2/LINUX] SQL0438N  Application raised error 
or warning with diagnostic text: "Access denied". 
SQLSTATE=75001

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See ERROR DESCRIPTION.                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgraded to v9.7 FP3                                         * 
****************************************************************

The workaround is to is to modify the trigger ICM_ACEI  so that 
LTRIM and RTRIM are called to clean the special register USER 
before comparing it in the equality predicate. 
 
1) Note the TOOLCAT_DB and TOOLSCAT_INST parameters from the 
output of GET ADMIN CFG 
2) Create a DB2 script containing the commands below: 
 
--Replace with name of your tools database 
CONNECT TO <TOOLSCAT_DB>@ 
 
-- Replace <TOOLSCAT_INST> with the name of the tools schema. 
 
DROP PROCEDURE <TOOLSCAT_INST>.ICM_ACEI@ 
 
CREATE TRIGGER <TOOLSCAT_INST>.ICM_ACEI 
  AFTER INSERT ON TOOLS.ICM_ACE_LIST 
  REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL 
WHEN ( 
  NOT EXISTS 
 
    (SELECT DEFN.ACL_ID FROM TOOLS.ICM_ACL_DEFN DEFN 
    WHERE DEFN.ACL_ID = N.ACL_ID) 
 
  OR ( 
    NOT EXISTS 
 
-- Fixed by adding LTRIM(RTRIM(UCASE(USER))) 
 
      (SELECT DEFN.ACL_ID FROM TOOLS.ICM_ACL_DEFN DEFN 
      WHERE DEFN.ACL_ID = N.ACL_ID 
       AND DEFN.OWNER = LTRIM(RTRIM(UCASE(USER)))) 
 
    AND NOT EXISTS 
 
-- Fixed by adding LTRIM(RTRIM(UCASE(USER))) 
 
      (SELECT A.ACL_ID FROM TOOLS.ICM_ACE_LIST A 
      WHERE ( A.PRINCIPAL <> N.PRINCIPAL OR A.USER_FLAG <> 
N.USER_FLAG ) 
      AND A.ACL_ID = N.ACL_ID 
      AND A.UPDATE_PERM = 1 
      AND ( ( A.USER_FLAG = 'Y' AND A.PRINCIPAL = 
LTRIM(RTRIM(UCASE(USER))) ) 
        OR ( A.USER_FLAG = 'N' AND 
          ( A.PRINCIPAL = 'PUBLIC' 
          OR A.PRINCIPAL IN 
 
          (SELECT UCASE(T.GROUP) 
          FROM TABLE( SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID( USER 
) ) AS T ) 
 
      ) ) ) ) ) 
) BEGIN ATOMIC 
 
 
  SIGNAL SQLSTATE '75001' ('Trigger ICM_ACI Access denied'); 
 
END@ 
 
4) Save the file and run it:  db2 -td@ -vf <name of file>

DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

At minimum this fix should be applied to the system where the 
tools database is located.

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC71006 IC71007 
follow-up : 

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

01.09.2010
28.04.2011
28.04.2011

9.7.FP3