Some triggers are invalid after database (DB2) is upgraded from 3.0.1 to 4.0 for Files/Wikis

Changes made to certain tables in the Connections Files/Wiks 3.0.1 to 4.0 migration script can cause some triggers to become invalid. Invalid triggers will cause functional issues.

After migration, if the user uploads a file and shares it with another user, then in the "My Files" view, the visibility icon indicates it's private file but it should appear as a shared file. This is an indication the underlying triggers are invalid.

Using following SQL to check if there are invalid triggers. If there is result, those triggers are invalid.

Files
db2 "SELECT T.TRIGNAME FROM SYSCAT.TRIGGERS T WHERE NOT EXISTS (SELECT TD.TRIGNAME FROM SYSCAT.TRIGDEP TD WHERE TD.TRIGSCHEMA = T.TRIGSCHEMA AND TD.TRIGNAME = T.TRIGNAME)"


Wikis
db2 "SELECT T.TRIGNAME FROM SYSCAT.TRIGGERS T WHERE NOT EXISTS (SELECT TD.TRIGNAME FROM SYSCAT.TRIGDEP TD WHERE TD.TRIGSCHEMA = T.TRIGSCHEMA AND TD.TRIGNAME = T.TRIGNAME)"

If invalid triggers are in this list, you can correct data with provided SQL. Otherwise, you need contact IBM on how to correct data.
1. MEDIA_UPD_FILE
2. R_MEDIA_SIZE_INS
3. M_VISI_COMP_UPD
4. MEDIA_SIZE_INS
5. R_MED_DEL_TAGS
6. R_MED_FL_DEL
7. MEDIA_SIZE_DEL
8. MEDIA_SIZE_UPD
9. R_MEDIA_SIZE_DEL
10. MEDIA_UPD_TAGS
11. MD_DEL
12. MEDIA_DEL_FILE
13. MEDIA_DEL_TAGS
14. MEDIA_DELETE

When you find the issue, following this step to correct triggers.

1. Stop Files/Wikis application.

2. Backup database;

3. Drop all triggers with dropTriggers.sql

Example command: db2 -td"@" -f dropTriggers.sql

4. Create all triggers with createTriggers.sql

Example command: db2 -td"@" -f createTriggers.sql

Files

createTriggers.sql dropTriggers.sql

Wikis

createTriggers.sqldropTriggers.sql

To prevent the problem during migration, you can drop and create triggers with the scripts right after database migration.