Question
Morgan Stanley
IN
Last activity: 26 Aug 2020 8:17 EDT
Significance of pr_changelog table
I have come across a table pr_changelog table which holds all the changes being made to the database tables from the PRPC wizards..
What is the significance of this table and where do we make use of this.
Thanks
Avinash
Accepted Solution
Pegasystems Inc.
US
Hi,
pr_changelog table is primarily designed to keep track of schema changes source information. At high level we have segregated this data into 3 types (as you can see from below snippet).
PRPC Shipped – Information of this category is captured at the time of install or upgrade or update process. OOB Pega shipped schema information is bundled as part of distribution image and the same will be imported into pr_changelog. In case of upgrade/update process, schema information for earlier release will be invalidated by setting pyLatest to ‘0’ (zero), indicating it’s not a latest information.
PRPC Generated – All the schema changes done through various PRPC features (Property Optimization, Schema Import, Clone Table feature, etc. ) are captured under this category. Upgrade/Update process do not touch information.
Manual – Any schema changes done, by directly accessing database are shown in this category. The changes shown on selecting Manual’ option are calculated instantly (not persisted in pr_changelog) based on ‘PRPC Shipped’ and ‘PRPC Generated’ information compared with current state of schema. The queries fired to figure out Manual schema changes are specific to database type.
Hi,
pr_changelog table is primarily designed to keep track of schema changes source information. At high level we have segregated this data into 3 types (as you can see from below snippet).
PRPC Shipped – Information of this category is captured at the time of install or upgrade or update process. OOB Pega shipped schema information is bundled as part of distribution image and the same will be imported into pr_changelog. In case of upgrade/update process, schema information for earlier release will be invalidated by setting pyLatest to ‘0’ (zero), indicating it’s not a latest information.
PRPC Generated – All the schema changes done through various PRPC features (Property Optimization, Schema Import, Clone Table feature, etc. ) are captured under this category. Upgrade/Update process do not touch information.
Manual – Any schema changes done, by directly accessing database are shown in this category. The changes shown on selecting Manual’ option are calculated instantly (not persisted in pr_changelog) based on ‘PRPC Shipped’ and ‘PRPC Generated’ information compared with current state of schema. The queries fired to figure out Manual schema changes are specific to database type.
This information is primarily used for tracing purpose – to identify how and who has introduced a particular schema change into the system. I would say this feature is not extensively used by customers but I have seen few instances where this information is really useful.
E.g.
1) Applications may misbehave in case of missing PRPC Schema - for this a landing page has been build "Missing PRPC Schema" using OOB shipped schema information from pr_changelog table.
2) Say, Customer claimed that upgrade process had modified non-pega tables - this can be traced back easily with "Schema Change Tracking" report..
On the other hand, PRPC Schema Report feature is completely different and it doesn't use information from pr_chnagelog table. This feature is basically to upgrade customer tables which were cloned from Pega base Work, Data and Rule tables. We wanted those tables to be in synch with OOB Pega shipped tables. The newly added columns (from base work, data and rule tables) in latest release will be presented in this page.
In 7.2.1, This landing page is renamed to 'Update Applications Schema' and enhanced to provide even missing indices and column length increases information.
Pegasystems Inc.
IN
Hi Avinash,
This holds instances of the concrete classes derived from Log-System-. These record schema changes through the Property Optimization tool and the Modify Schema tab
Pegasystems Inc.
IN
Ah .. thats a good question and honestly i dont have answer . I assume that , because it deals with recording schema changes through the Property Optimization tool and the Modify Schema tab, thereby modifying DB related stuff, there is a need to keep a log on this . This is my thought but I m not sure whether it is correct or not.
Lets see if other in this forum can give some clue on this.
Pegasystems Inc.
IN
Hi Avinash,
Some more info regarding this table, specially from upgrade point of view:
Schema information for the shipped tables is loaded into to pr_changelog table during upgrade. This information is shipped as an archive with the upgrade kit and imported during upgrade.For the list of tables post upgrade the below query should gives the tables expected in rules and data schema.
select pydatabasename,pytablename from <rules schema name>.pr_changelog where pyChangeSource='PRPC-Shipped' and pylatest='1' and pxObjClass = 'Log-System-Schema-Table' group by pydatabasename,pytablename order by 1,2
Also, the landing page System->Release->Upgrade->PRPC SChema Report tell us if there are any missing tables/columns/indexes which are expected to be in that version. It uses the information in pr_changelog to generate this information
Morgan Stanley
IN
Thanks Arvind.
I'm just trying to understand the working of the schema report...
In case of upgrade the OOTB Shipped tables structure is compare with that of the new change log, that has come up with the upgrade (pr_changelog table).. So based on that the changes are recommended, right?
We also have a check enabled on the tables which Descend from Data- and Rule- which is shown below.. What in your view is the impact of pr_changelog on these descendent tables. How is the table structure compared.. For OOTB PRPC knows that a few columns are missing.. But for customer tables how is the structure compared?
Any thoughts?
Thanks in advance.
Avinash
Accepted Solution
Pegasystems Inc.
US
Hi,
pr_changelog table is primarily designed to keep track of schema changes source information. At high level we have segregated this data into 3 types (as you can see from below snippet).
PRPC Shipped – Information of this category is captured at the time of install or upgrade or update process. OOB Pega shipped schema information is bundled as part of distribution image and the same will be imported into pr_changelog. In case of upgrade/update process, schema information for earlier release will be invalidated by setting pyLatest to ‘0’ (zero), indicating it’s not a latest information.
PRPC Generated – All the schema changes done through various PRPC features (Property Optimization, Schema Import, Clone Table feature, etc. ) are captured under this category. Upgrade/Update process do not touch information.
Manual – Any schema changes done, by directly accessing database are shown in this category. The changes shown on selecting Manual’ option are calculated instantly (not persisted in pr_changelog) based on ‘PRPC Shipped’ and ‘PRPC Generated’ information compared with current state of schema. The queries fired to figure out Manual schema changes are specific to database type.
Hi,
pr_changelog table is primarily designed to keep track of schema changes source information. At high level we have segregated this data into 3 types (as you can see from below snippet).
PRPC Shipped – Information of this category is captured at the time of install or upgrade or update process. OOB Pega shipped schema information is bundled as part of distribution image and the same will be imported into pr_changelog. In case of upgrade/update process, schema information for earlier release will be invalidated by setting pyLatest to ‘0’ (zero), indicating it’s not a latest information.
PRPC Generated – All the schema changes done through various PRPC features (Property Optimization, Schema Import, Clone Table feature, etc. ) are captured under this category. Upgrade/Update process do not touch information.
Manual – Any schema changes done, by directly accessing database are shown in this category. The changes shown on selecting Manual’ option are calculated instantly (not persisted in pr_changelog) based on ‘PRPC Shipped’ and ‘PRPC Generated’ information compared with current state of schema. The queries fired to figure out Manual schema changes are specific to database type.
This information is primarily used for tracing purpose – to identify how and who has introduced a particular schema change into the system. I would say this feature is not extensively used by customers but I have seen few instances where this information is really useful.
E.g.
1) Applications may misbehave in case of missing PRPC Schema - for this a landing page has been build "Missing PRPC Schema" using OOB shipped schema information from pr_changelog table.
2) Say, Customer claimed that upgrade process had modified non-pega tables - this can be traced back easily with "Schema Change Tracking" report..
On the other hand, PRPC Schema Report feature is completely different and it doesn't use information from pr_chnagelog table. This feature is basically to upgrade customer tables which were cloned from Pega base Work, Data and Rule tables. We wanted those tables to be in synch with OOB Pega shipped tables. The newly added columns (from base work, data and rule tables) in latest release will be presented in this page.
In 7.2.1, This landing page is renamed to 'Update Applications Schema' and enhanced to provide even missing indices and column length increases information.