We are moving to a new database server and it is required of us that we change the database schema names. For example: We currently have names of 'ITGADM' and 'ITGRML'. But, we need to change to 'ITGADMDEV' and 'ITGRMLDEV', etc for EACH PPM Instance! (So, we'll need an 'ITGADMTST', 'ITGADMPRD', etc.) We have updated the server.conf and the Environment in workbench. But, there are a few HP-delivered database objects that seem to be using the old database schema name. (2 triggers: KCRT_REQ_RHT_AUDIT_30106_1 and KCRT_REQ_RHT_AUDIT_30196_1). One of the triggers prevented us from submitting any requests. If we manually update the trigger scripts, they work. However, we don't feel comfortable updating HP-delivered. Can you tell us where else the databgase schema names could be stored?
Thanks Utkarsh, Our DBA ran these 2 scripts: CreateKintanaUser.sql and CreatRMLUser.sql (which give the necessary db grants). Then re-imported the 'database dump'. However, we still have database objects that are invalid.
Thanks Alex, We also have an invalid function named 'NEW_CONTACT_ID' that is pointing to the old schema name. Is it okay to also modify this object type? We are just concerned that there are still 'behind-the-scenes' or 'internal' pointers to the OLD database schema name.
Both the triggers - KCRT_REQ_RHT_AUDIT_30106_1 and KCRT_REQ_RHT_AUDIT_30196_1 are automatically created whenever the request header is created/updated and are related to different request header types. The following steps should help:
1. Rename or drop the triggers in question - this is absolutely safe for the rht_audit triggers.
2. Open the workbench and open the request header types that refer to both these triggers
3. Recompile KCRT_AUDIT package again.
4. Make a minor change in each request header type (adding a space or character to the description field and then again undoing it to activate the save button). Save the request header type. As soon as you save the request header type, these two triggers would be recreated once again, using the correct schema.
Alternatively, if you update the trigger script and recompile it using the new DB names, it would work too, but the above method is the recommended one, as per HP. We had a similar situation and was recommended by HP.