Hi All,

I am getting an unusual error while trying to do an update for one of my tables.although the user have createin/alterin/dropin privilges on the table schema .
FYI : I can successfully update any other tables .this table have some triggers associated.

command :
db2 "UPDATE USERS SET MODIFIED= TIMESTAMP('2001-01-01 00:00:00.0')WHERE MODIFIED IS NULL"

Error : -

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0727N An error occurred during implicit system action type "7".
Information returned for the error includes SQLCODE "-551", SQLSTATE "42501"
and message tokens "DB2USRBH|ALTER TABLE|DB2USRBH.USERS". LINE NUMBER=0.
SQLSTATE=56098

According to error message, command: "db2 ? SQL0727N" you have some trigger problem. It looks like you try to update table, but trigger executes some action that can't be completed, so trigger rollbacks you transaction.

Check trigger action:
select * from syscat.triggers where tabschema='table_schema' and tabname='table_name'

Regards

thanks . I had a fix for it. I had dropped and recreated the trigger with a change in function path ( used in resolving functions and types).
earlier it was ""SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","<def ault user schema>" . I replaced <default user schema> with the base table schema.

I recommend that you fully qualify all function calls (except for system functions). Otherwise, the next person who has to maintain (fix or extend) the trigger will run into the same problem. So a permanent solution should be much better than the temporary work-around with setting the function path for the trigger creation.