How to expose a property as a database column

Pega 7 Platform stores the values of all aggregate properties and some Single Value properties in a BLOB column (the Storage Stream), usually in a compressed form. Such properties cannot support selection in list view and summary view reports, and can slow retrieval and processing in other operations.

For maximum flexibility, your development team can create new Single Value properties at any time, without the need to coordinate with a database administrator. However, exposing properties as distinct columns can significantly improve performance. You can expose a property even after the property has values, but additional processing is required to copy values for the new column from the BLOB value into the new column.

To make changes to the database schema, your connection to the PegaRULES database must allow certain SQL capabilities. See the notes on Admin Username fields on the topic Database form — Completing the Database tab.

• Property Optimization — Appropriate for ad-hoc changes when you have one or a few Single Value, top-level properties to expose in a development system
• Modify Schema — Used to review the current database table structure, understand class-to-database table mapping, and provide additional context. Can make the changes directly or create SQL statements to be applied later by a DBA.

The Column Inclusion field (on the Advanced tab of the Property form, for Single Value properties) contains one of four values (blank, Optional, Required, Requested) that indicates the judgment or opinion of the developer who created or updated the property. This value is advisory only; it does not affect any runtime behavior of the property. However, using the Database Class Mappings gadget, you can compare the values in this field with the current schema, identifying properties marked as Required which are not exposed. Select Designer Studio> Data Model > Classes and Properties > Database Class Mappings to access this gadget.

Property Optimization approach

The Property Optimization tool is available only on development systems — systems where the production level is 1 or 2.

1. Access the property through the Application Explorer.
2. Right-click. Choose Optimize for Reporting from the menu.
3. Wait. The new column is created immediately, but property values in the new column are copied (from the BLOB) by a background process. This may take from a few seconds to hours, depending on volume.

Select Designer Studio> System > Database > Column Population Jobs to monitor the background processing.

Computations involving the property may produce unpredictable or inconsistent results until the background processing is complete.

Modify Schema approach

Use the Modify Schema wizard to:

• Review the current schema of a database table.
• Change the width in bytes of a column in the database table.
• Expose a single value property as a new column.
• Create a text file with Structured Query Language defining a table.

Select Designer Studio> System > Database > Modify Schema to start the wizard. Click Help on each form of the wizard for additional details on completing the form.

You can use this facility to copy a single value property from the BLOB column and "expose" it as a column. This change may affect database performance and database size, but has no functional effect on activities, the property, or other rules referencing the property.

To modify a table schema with this facility, work at a time when no one else is using the table. Obtain a database user ID and password that allow you to modify the database schema, not just the rows in the database. (If your account does not provide the ability to modify the schema, you can generate the Data Description Language (DDL) statements that are required to modify the schema.)

When working offline, you can review the contents of the database schema as initially installed. Locate and open the schema HTM file in the Resource Kit directory of the Pega 7 Platform installation media.

Modify Schema: Viewing the current schema

1. SelectDesigner Studio> System > Database > Modify Schema. This opens the Modify Schema wizard.
2. In the Select a Database step, select a database and click Next.
3. In the Select a Table step, select a table and click Next .
4. The View Table step displays which Pega 7 Platform concrete classes are associated with the database table and database you selected.

• The top of this display lists four items:

• The database you selected
• The table you selected
• A count of rows in the selected table
• A count of columns in the table

• The table identifies information about each concrete class:
• Class name — Rule-Obj-Class name
• Ruleset — The ruleset associated with that class
• A link containing count of Single Value properties in the class that are marked as Required in the Column Inclusion field on the Advanced tab on the property form. Values in this field are advisory only; they do not affect database operation.
• A count of all properties in the class

5. To view a list of columns in the table, advance to the View Columns step by clicking View Columns or clicking the numeric link in the Columns in the table field. Each row in the list contains the column name, its data type, and column width in bytes.
6. Click Back to return to the View Table step.
7. To advance to the View Properties step, click a numeric link in the Properties — Set to be Visible column in a class row. This form displays properties that are exposed, plus others for which exposure is requested; the Column Visibility value is Required or Recommended).
8. Click Back to return to the View Table step (this skips the View Columns step) or Close to exit the wizard.

Modify Schema: Exposing a top-level Single Value property

1. Go to the View Table step to access the table containing the property of interest.
2. Locate the class that contains the scalar property to be exposed.
3. Click the number in the Set to be Visible column.
4. The resulting detail window displays the properties already exposed as columns in gray text at the bottom of the display. Properties that are candidates to be exposed are shown at the top of the display in black text and contain empty check boxes at the beginning of the rows.
5. Check the box for each property to become exposed as a column. For properties with a Type of Text, Identifier, or Password, you can modify the column width shown in the Column/Size column. The default width is 64 characters, or the Max Length of the property (if that was defined for the property). If you change these values, remember to update later the Max Length values if defined on the Property form.

To directly update the schema:

1. If you have a database user ID and password that allows you to update the database schema and you want to make the change now, select the Generate Database Columns radio button.
2. Enter a Database UserID and password that grants the ability to modify the schema of this database.
3. Click Create Selected Columns >>. The new columns are added to the table and existing rows are resaved if requested. A confirmation form appears. At this point, new or updated rows can assign a value to the exposed property.

To create columns using SQL:

1. Click the Generate SQL Code radio button.
2. Click Generate SQL to create selected columns >> . The SQL code appears. Copy this into a text file for use at a later time. Populating the column must occur after the schema change is complete.

If, in the Storage Stream (BLOB) column, the selected property or properties has non-blank values for at least some rows, you must extract (copy) the existing values into the new column. Use the Column Populator utility. See Working with the PegaRULES database — Using the Column Populator utility.

To expose embedded properties

Only top-level Single Value properties can be exposed as columns. Two tactics are available when you need a database column that contains the value of an embedded property:

Copy approach — Copy the value to a new top-level property each time the embedded property changes (or each time the instance containing the property is saved). For example, if a list view report needs to select rows based on property pyWorkPage.pxFlow("LoanDisburse").Sheet.Detail(4), you can:

1. Create a new top-level Single Value property to hold a copy of this value.
2. Create a one-step activity to copy the value to the new top-level property, with Activity Type set to Trigger.
3. Create a Declare Trigger rule that calls the activity each time the work item is saved.
4. Expose the top-level property.
5. Reference the top-level property in the list view rule.

Declare Index approach — If not one but many or all values of a Value List or Value Group are needed as exposed columns, a Declare Index rule is a better approach.

1. Create a concrete class derived from the Index- base class.
2. Create Single Value properties in the new class to hold values of the embedded values.
3. Create a Declare Index rule with the appropriate embedded Page Context value that copies the embedded values into a new Index- instance.
4. Save the Declare Index rule. It executes immediately, adding and deleting instances of the new class.
5. Expose database columns corresponding to the Index- class.
6. Reference the Index- properties in the list view rule.

Notes

Don't confuse an exposed property with an indexed database column. For example, the property Work-.pyID is an exposed property in the table pc_work. However, if this table contains millions of rows, one for each work item, a list view search for .pyID of "W-135" causes an expensive database table scan. Creating database indexes or Pega 7 Platform indexes can speed database operations in such situations.

Exposing too many properties in a table may speed reporting and searching operations, but make insert and update operations slower. The tradeoff and relative impact depends on hardware and software and no general guidelines can be stated.

	Column Populator utility, exposed property, schema, Storage Stream
	About the Modify Schema wizard About the Property Optimization tool How to monitor Storage Stream operations Working with the PegaRULES database Working with the PegaRULES database — Using the Column Populator utility
	Atlas — Standard Declare Index rules

SysAdmin category

Open topic with navigation