How to expose a property as a database column

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

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

Use the Modify a Database 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 > Data Model > Tools > Modify Database Schema to BYRNB 2/25/10 start the wizard. Click the help button () 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 rule, 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 Process Commander installation media.

1. Select> Data Model > Tools > Modify Database Schema. This opens the View/Modify Database 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 Process Commander 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. SR-7205 B-25362 BUG-701 incomplete
• 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.

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

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. BUG-1098 B-22559 SR-5574
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.

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 object 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. (The Declarative Index wizard and the Property Optimzation tool can automate these steps).

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.

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 object, a list view search for .pyID of "W-135" causes an expensive database table scan. Creating database indexes or Process Commander indexes can speed database operations in such situations. PEPEN 9/15/06 CLINIC

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.