For our Datawarehouse applications, we want to perform
ALTER TABLE ALTER CONSTRAINT <constname> NOT ENFORCED, before the load. Perform the load and then
ALTER TABLE ALTER CONSTRAINT <constname> ENFORCED.
Tables are owned by DBA (SYSADM) and the application id doesn't have rights to perform
So, the DBA asked me to create a Stored Procedure which will be owned by DBA and will be invoked by the application id.
For this, we explored DYNAMICRULES option of DB2.
So before the procedure is created by the DBA, the below call is made by the DBA
- CALL SET_ROUTINE_OPTS('DYNAMICRULES RUN)
- Procedure is created under DBA owner
- Execute grant privilege to the application id
When I called the procedure using the application id, the error that I get is
SQL0551N "App id" does not have the required authorization or privilege to perform operation "ALTER TABLE" on object "DBA.Object_name".
The documentation that I referred is
Any help on this would be very much appreciated, as I feel that Dynamic DDLs can't be allowed to execute using Definer rights by a invoking auth id.