Summary

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 pr_history.  

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 pr_history table.

Suggested Approach

Note the following:

• The columns present in the pr_history table are also present in the pc_history_work table.
• 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:

1. Stop the Process Commander server or temporarily isolate the pr_history and pc_history_work tables from all transactions.
2. Export or back up the pr_history and pc_history_work tables.
3. Drop the indexes of the pc_history_work table.

From the SQL prompt of your database , follow the steps below to copy the records:

1. Determine the number of records in the pr_history table and note the value returned:

SQL> select count(*) from pr_history;

2. 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.

3. Copy the records from the pr_history table to the pc_history_work table:

SQL> insert /*+nologging */ into
pc_history_work(
PXHISTORYFORREFERENCE,
PXINSNAME,
PXOBJCLASS,
PXTIMECREATED,
PYLABEL,
PYMEMO,
PYPERFORMER,
PZINSKEY,
PZPVSTREAM )
select
PXHISTORYFORREFERENCE,
PXINSNAME,
PXOBJCLASS,
PXTIMECREATED,
PYLABEL,
PYMEMO,
PYPERFORMER,
PZINSKEY,
PZPVSTREAM
from
pr_history;

4. Confirm the copying was successful by re-checking the number of records in the pc_history_work table 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.

5. After confirming the copy was successful, save the changes:

SQL> commit;

If the copy process was not successful, you need to re-run the SQL statements beginning from Step 1.

5. Remove the records from the pr_history table:

SQL> truncate table pr_history;

6. Correct the class mappings by creating an instance of Data-Admin-DB-Table for the work object history class and mapping it to the pc_history_work table.
7. Run the ResaverServlet.  This step is optional, but it updates the PegaRULES database tables to support schema changes.