How to migrate work object history records that were incorrectly saved in the pr_history table
History classes track changes/updates to work objects, rules, or data objects. These history classes are automatically created for all concrete classes which:
- inherit from either Work- or Data-
- are either class groups or classes that do not belong to a class group
Data-Admin-DB-Table instances link the History class name and the name of the database table where these history instances will be stored. For example, if you create the class Acme-Work-, which has directed inheritance to Work-, then the history class History-Acme-Work- is automatically created. Since the class History-Work- is linked to the database table
pc_history_work, the class History-Acme-Work- would be copied from that record, and would also point to that database table; all instances of History-Acme-Work- would be stored in pc_history_work. Table
pc_history_work is specifically created to contain work object history; most of its columns are exposed to enable frequent, low-overhead reporting.
Other types of History classes are stored in other database tables; the abstract base class History- is linked to the
pr_history table. Since the
pr_history table has only few exposed columns, it requires retrieval of the BLOB to report most values, and so the
pr_history table should not contain history instances for work objects or data objects.
If a work type is not mapped properly, the associated history class may not be created with the correct inheritance. Instead of creating a History-Work- class, the system may create a direct History- class. In the example above, when creating History-Acme-Work, instead of copying from the existing History-Work- instance and pointing to the
pc_history_work table, the Data-Admin-DB-Table instance may copy directly from the History- class, and point to
It is important to ensure that classes with many saved instances or high activity are not mapped to table
pr_history. (For example, some applications may create numerous history records for high-turnover classes, such as interest rates.) If work object history mapping to
pr_history does occur, your system will operate as expected, because the mapping is referenced for read and write operations. However, you must resolve the mapping issue as soon as possible, due to the constraints on the
Note the following:
- The columns present in the
pr_historytable are also present in the
- In testing, it took 48 seconds to copy 188,928 records. Therefore, on this system it should take approximately 20 minutes to copy a table containing 2.6 million records.
Before migrating the history rows to the correct table:
- Stop the Process Commander server or temporarily isolate the
pc_history_worktables from all transactions.
- Export or back up the
- Drop the indexes of the
From the SQL prompt of your database , follow the steps below to copy the records:
- Determine the number of records in the pr_history table and note the value returned:
SQL> select count(*) from pr_history;
- Determine the number of records in the pc_history_work table and note the value returned:
SQL> select count(*) from pc_history_work;
The values from steps 1 and 2 will help you determine later whether the copying of the records (Step 3) is successful.
- Copy the records from the pr_history table to the pc_history_work table:
SQL> insert /*+nologging */ into
- Confirm the copying was successful by re-checking the number of records in the
pc_history_worktable and note the value returned:
SQL> select count(*) from pc_history_work;
This value should match the sum of the values from steps 1 and 2 (add the original
pr_history count to the original
pc_history_work count). This calculation takes into account any records that remained in the
pc_history_work table before the issue arose.
- After confirming the copy was successful, save the changes:
If the copy process was not successful, you need to re-run the SQL statements beginning from Step 1.
- Remove the records from the
SQL> truncate table pr_history;
- Correct the class mappings by creating an instance of Data-Admin-DB-Table for the work object history class and mapping it to the
- Run the ResaverServlet. This step is optional, but it updates the PegaRULES database tables to support schema changes.