Expose a property in Pega

In this post we will see, how we can expose properties in database table.

First let’s get some basics

  • Pega uses properties to hold the data.
  • We can persist the data in database table.

Say for example, I have created a new amazon sales case, Say A-1 and captured all the customer details.

`CustomerName = “Prem”.

We can persist these details in the Work table in a single row that’s corresponds to Case A-1.

Note: Not all the properties exposed as data table columns.

Pega database supports blob storage. If the properties are not exposed, then they get stored in a blob storage column ‘pzpvstream’

So what do you mean by exposing property?

Exposing means, we will be creating a dedicated  column in the database table and map the corresponding property to the database column.

How to expose a property?

Here are some  pre-requisites you need to have in handy!

  1. Have a dedicated work table (or any table) with class mapping done already.

Here my work class will be “OIKGGB-MyKnowPega-Work-AmazonSales” and the table I mapped is


  1. Create new properties in that work class

I got 3 properties in work class – CustomerName, Age and PhoneNumber.

  1. Have a case ready with a flow action to capture the above customer details.

Since I have already exposed Customer Name and Age column for other tutorials, I am going to drop those columns.

My drop column query is like below

Alter Table pc_oikggb_myknowpega_Work DROP COLUMN customername, DROP COLUMN phonenumber.

Now my table is fresh :). No additionally exposed columns.

Now,I am going to explain how to expose the properties.

There are four ways to expose the properties.

  1. Using property rule  form – Optimize using Other actions button
  2. Using Modify Schema wizard.
  3. Directly create a column in database
  4. Exposing properties using declare index rule  – For more info, please visit my previous posts on declare index.

What is Declare index in Pega?


How to configure Declare index in Pega – step by step tutorial

1.Optimize from property form.

Step 1: Open the CustomerName property rule

Step 2: Go to ‘Actions’ button and click on the Optimize for reporting from the dropdown menu.

Step 3: You will be landed in the property optimization landing page

Properties and classes

Here you can get more than one class and the corresponding tables.

Say for example, you have 2 cases in your application and both the cases are mapped to dedicated database table. In such case we can have common properties. When we try exposing those properties, you will get an option to expose those properties in multiple classes and tables.

  • I selected Amazon sales and click on next.

You also have an option to populate the data.

Populating data – Whenever, we expose a property in later point of time, we can use column population jobs to copy the data from the blob storage to the exposed column. We will see more in detail in separate lesson 🙂

Population schedule – Now/Later

If you choose later, you get an option to specify the date and time.

Normally column population job invoves in updating the database table records. So in higher environment, we can schedule to run this job during offline hours.

Eligible classes

  • You will get a screen with a confirmation warning. It will list all the classes that will be affected.
  • You can click next to start optimizing.


You can also click on the Column population jobs dashboard, to check the job status

Now, let’s check the database table, if a new column is created or not.

Yes :). Create a new case and check if the customername is getting populated.

I have created a case A-6. Check in the database table for pyid =’A-6’.

We have successfully optimized the customername property.

Note: Optimise property action is available only for single value property. Also, a when rule ‘pxIsPropertyOptimizationAllowed’ should be true. If you dig the when rule, then you can see two conditions must be satisfied.

  1. Dynamic system setting – database/AutoDBSchemaChanges should be true

  2. Operator should contain the privilege ‘SchemaPropertyOptimization’ in his access rule.

Procedure 2

Here I am going to try optimizing Age property

  • You can make use of the Modify Schema wizard.

Designer studio -> System -> Database -> Modify Schema

You can see a 6 step wizard.

Step 1: Select a database.

Choose an available database.

I selected PegaDATA , where my work table is available

Step 2: Select a Table

Select the appropriate table from the database.

I selected pc_OIKGGB_MyKnowPega_Work, which is my work table.

Step 3: View table

You can view the contents of the table.

Click on the count of columns link inside the grid to view the properties – Step 5

Step 4: View columns. You can also directly click on view columns or Columns in the table : (92)

Just a read only view and you can come back.

Step 5: View properties.

  • You can see the exposed properties – Already greyed out
  • Unexposed (eligible) properties – You have an checkbox option to select those eligible properties.

Eligible refers to column visibility field :). We will see in detail.

You should get a question here!. Why Age property is not an eligible property?

Actually you need to check a configuration point in property rule form

Step 1: Open the age property.

In the advanced tab, you have a filed – column inclusion.

You have 3 options.

  1. Required / Recommended – selecting this will make this property as a candidate to optimize
  2. Optional – this is equal to making this field as empty.

Here I selected –Required.

  • Now again continue the modify schema wizard.

You will see the Set to visible  count increased from 102 to 103.

Click on the link to view the properties.

You will the age property. Select the property.

Scroll down.

  • You can either generate the SQL code or directly generate the database columns.

To generate the database columns, you need the database User ID and password.

If you are using personal edition provide ‘pega’ as user name and password.

  • Click on create.

You can see the success message.

Now let’s go and check the database table.

You can see a new column Age got created.

Note: The operator should contain ‘SchemaPropertyOptimization’ privilege to use wizard.

Procedure 3:

Here I am going to expose ‘phonenumber’ property.

Step 1: You can directly create a new column using SQL developer ( may be with the help of DB team)

You can run the query and check in the database table.

You can see the newly added column Phonenumber.

Note: After adding a new column, always re-save the database table instance. (This re-save will be handled by the wizard, when we expose the property from designer studio)

Let’s create a new case with valid phonenumber and check the value in database table.

  • The case I created is A-8.

Let’s check the phonenumber value in database table.

We have successfully exposed 3 different properties via 3 different procedures 🙂

Why do we expose a property?

  1. The main aim is to support reporting.

Say for example, I need to make a report and display all the customer details like ‘phonenumber, age & name’. here if I don’t expose those columns, then I need to decrypt the Blob and get their values. This is very expensive and degrades performance. Also you cannot use SQL query. SQL query can access only the exposed columns. This is the main reason why we expose the properties.

  1. Also keep in mind that, exposing large number of columns also degrades performance. So use it wisely.

We will see more about column population job in my next post 🙂

I will come up with a new post very soon 🙂




39 thoughts on “Expose a property in Pega

  1. Great site and great articles. Keep it coming.

    Can you please post some articles on page context and flow routing (worbasket, worklist) advanced stuff.


    1. Thank you so much for your appreciation, Amir. 🙂
      I’ll keep on posting new stuffs. Stay tuned. 🙂
      Yeah, I’ve noted those down. I’ll try to post about them as soon as possible. 🙂
      You are welcome. 🙂

  2. very good explanation.
    Can we somehow give the size of column to be created while exposing the property from property option ?

    1. Hi Faijas,
      You can always update the column size using SQL developer. I don’t think you can control it when optimizing

  3. This blog is just amazing, keep up the work. thanks for you time in making us learn PEGA in easy and better way. my request here is to learn all about the activities and in what circumstances we use the Step Pages and so.. 🙂

    It will also be helpful if you can let us know the upcoming topics somewhere in the website and the estimated arrival of the post, so that we can keep a track of them 😉

    Thanks a lot 🙂

    887 Subscriber – Sorry for the late. 🙂

    1. First of all. Thank you so much for your appreciation, Surya. I’m feeling so much happy that you are finding it useful. 😀 Hope you learn everything you wanted to learn here.
      I’m thinking about posting the upcoming topics like a flashing display somewhere. I’ll try to post it soon. 🙂
      Stay tuned. 🙂
      You are most welcome, Surya. 🙂
      No worries bro. 🙂

  4. Nice explanation 🙂

    Is there a way to check what are all the properties available inside pzpvstream blob column using, Database select query (in back end).

    1. Hi RK,
      BIX can make use of properties which are not exposed. I find it is not relevant to this topic 🙂
      I will try to make a separate post on BIX soon

Leave a Reply

Your email address will not be published. Required fields are marked *