01. I recently updated a Datastage job to use a direct Update query instead of calling a Stored Procedure(SP) through the job which had the Update query.
2. The result: The job failed with the error that the 'userid' did not have Update privilege on the table.
3. I was using the same userid to execute the SP as well.
4. It took me sometime to understand the reason for this.
5. The reason is that DB2 userid needs execute privileges for a SP.
6. The Execute privilege in turn grants indirect privilege to the userid executing the SP for the SQL statement specified in the procedure.
7. So, as I had the Update SQL statement in my procedure. And my userid had execute privilege on the procedure.
8. t was granted indirect privilege to update the table for the purpose of executing the SP. These are temporary privileges and last only till the SP is executing.
9. Now, I tried to update the table directly using the same userid. And I got an Update access error because that user id did not have explicit Update privilege on the table.
10. So, a point to keep in mind while deploying changes that replace stored procedures with direct SQL statements.
