Database Table - Test Connection failure - NonexistentTable exception
We've two MS SQL 2008 R2 databases (pega 7.1.8) which have all the connectivities working.
The size of the database PegaRULES has grown over the last few years, backup and recovery of database are taking more time.
To reduce the time taken for the backup and recovery, we've made below changes at the database.
1. Created an additional database, PegaRULES-Syn
2. Copied few tables from PegaRULES to PegaRULES-Syn.
2. Deleted few tables in PegaRULES, tables list below
3. Created a synonym for the above table in PegaRULES database and pointed it to the table pca_work in the database PegaRULES-Syn.
After making the above changes, executing the SQL queries on pca_work by selecting the PegaRULES database is working fine, but when tried to test the connectivity (restarted Pega App Server) after making above changes, it fails. Pega is not able to establish the connectivity with the tables that have synonyms created, suggesting that the table doesn't exist in the PegaRULES database!
Does pega have any different steps if the database / tables is setup this way? If "No", what changes would fix the connectivity issue?
***Updated by moderator: Vidyaranjan. Removed user added #helpme and Ask the Expert tags. Apologies for confusion, shouldn't have been an end-user option.
Is this PegaRules-Syn a seperate database or a seperate schema in the same database? If it is the same database but different schema you have a couple of options. In either case you should remove the synonym.
1. Within the DBDT rule for pca_work specify the schema name that this table is now found in.
2. Create a view in your original schema of the table in your new schema.
If it is truely an entirely new database then you probably will need a new datasource and to use XA drivers.