Question
Swedish Unemployment Funds
SE
Last activity: 22 Nov 2016 2:22 EST
Multiple schemas for customer specific work tables in 7.2.x
We have a system with 20+ different implementation layers / applications and want to separate the different work and history_work tables into different data-schemas for each customer. We have already a split schema database with rules & data separated. Will adding almost 30 different schemas for worktables have any future consequences for e.g., upgrades, performance?? We are today running (in development as demo) with only one implementation layer and this works fine with work & history_work tables in a separate schema. i.e., we have three schemas in the database, 'rules', 'data' as standard for split schema and additionally 'DemoData'. We are presently running Pega 7.2.0 / JBoss 6.4 / Sql Server 2012
Updated: 15 Sep 2016 5:05 EDT
Virtusa IT Consulting
AE
You can use Database rule to connect to different schemas and using preferences file as 'prconfig.xml' you can define <env name="database/databases/ExternalDB/dataSource" value="java:comp/env/jdbc/ExternalDB"/> and connect to the external schemas. However the usecase as adding 30 different schemas is something I dont believe that has been tested, so I am not sure about the performance but you may surely face some challenges while upgrading.
Swedish Unemployment Funds
SE
Yes thank you, this is pretty much how we've done.
And yes I also suspect the upgrade would be an issue unfortunately. So while (maybe) a good idea in theory we might have to forego this approach.
Swedish Unemployment Funds
SE
Hi again baigh! You wrote in your answer "but you may surely face some challenges while upgrading". Is there some extra procedure we need to follow during upgrades do you mean? Du you know if the approach with multiple data-schemas is officially supported or not?
Pegasystems Inc.
IN
Hi Christer,
Creating n number of schemas will not be a big problem but during upgrade we upgrade only pegaDATA schema. Other schema tables won't be upgraded or taken care of. But as part of post upgrade process, we have landing pages to update other schemas as well.
As long as PegaDATA schema is present, base platform functionality should work as is. Having so many schemas will be good for your applications but platform might read database metadata like schemas, internal tables etc.. in that case, certain functionality like (PRPC Schema Report) and Database schema modification landing pages may work slower.
But i suggest you to report any performance problems you encounter with rules and data schemas so that platform itself will support without any problems. Creating different schemas may solve only certain portion of problem but maintaining them and using those schemas in your flows, activities will be a bottle neck when you start interacting between different applications / schema tables.
Thanks,
Venkat
American Express
IN
Hi ChristerT_SO,
Can you please send the list of work tables you have seperated into seperate data-schema in your implementation?
Thanks
Atanu