Suggested Approach

The Blob (Binary Large OBject) column is currently part of all PegaRULES database tables.   This column is used to store data that is structured in forms that do not readily fit into the pre-defined database columns, like properties that contain multi-dimensional data such as pages, groups or lists. When the Blob is present in a database table, all the data in the instance is stored in the Blob, including some of the data which might also be in exposed columns in the table. The reason for this storage arrangement is twofold:

• It is not possible to select rows for reports based on data in the Blob, so any properties upon which queries will be run must be in exposed columns.
• When extracting data from the database, since some of the information is stored in the Blob and some in exposed columns, it is faster for the system to read it all out of the Blob.

Blob data handling is slow, however, and it takes up a lot of space. There is faster performance and a smaller data footprint if the Blob is not present. Also, if there is no Blob, then all data in the table must be exposed in columns, and reports may be written on all the properties stored in the instance.

Thus, you can convert some of the database tables to have no Blob column. As stated above, if there is no Blob, then everything must be exposed in columns; a vital requirement for mapping classes to a Blobless table is that all the properties must be able to be stored in individual columns. Properties of a group mode (Page Groups or Value Groups) and list (Page Lists or Value Lists) cannot be stored in a regular database column.

A side effect of this limitation is that it is not possible to store instances of a class upon which an index is defined in a Blobless table. When an instance is indexed, there is a reference to the index in the original instance; this property, which is on each page, maps to the page context path in the index definition. This is a Page Group property, and as such, must be stored in a Blob.

Instances of classes derived from the Work- and Rule- classes ship with standard indexes defined, so these cannot be stored in Blobless tables.

Procedure

First, make sure that the class is not restricted from using a Blobless table (is not indexed, contains only Single Value properties.). If the class is restricted, then errors will result when the conversion is attempted.

Next, expose each property as a column.

Finally, use SQL statements to delete the Blob column.