Which version you are on? If you are in latest version of Pega 7 (718+)  platform then i think it will be easy.

You can try following approach:

You can create Rule-Admin-Product with required table, so while importing Pega import wizard has knowledge of creating missing columns in the target database. OR You can manually prepare prepare SQL with the required DB columns changes and run it on production.

And then -

1. Write an activity which can pull all work objects in that table (or atleast required work objects as per your requirement) and
2. iterate through the list
3. do property-set of new exposed property
4. Obj-Save
5. after exiting from loop you need to issue commit step. Make sure to run it in atomic transaction.

If you are not worrying about exisitng work objects then only import the RAP should suffice your requirement.

You can make use of 'Column populator' or prpcUtils utility from your PRPC media file.

Once you expose a column for the property in database you can run this utility which will extract the data from blob and update in the new column for the property.

Make sure to map your application access group while defining prpcUtils.properties file.

Hi Mohammad,

We followed below steps to acheive this scenario.

1. With appropriate DB access rights, one can expose a property through "Property Optimization" wizard, which crates a new column in the respective DB.

2. The advantage of optimization tool is, it will automatically index the existing work objects through column population jobs (Designer Studio --> System --> Data base --> Column population jobs)

3. But the wizard cannot run in higher environments so we need to manually run the SQL from DB. (ALTER TABLE PEGA_DATA.PC_SFC_CMS_WORK ADD PRPDATE varchar2(8 CHAR)) before release. Make sure to include the corresponding "Data-Admin-DB-Table" instance in the product archive (Or save the respective "Data-Admin-DB-Table" instance after the the column was manually added to take effect)

4. To repopulate data to the exposed column (Direct property or page list property) run the column population wizard (Find the attached "Populating database columns using the service-enabled Column Populator utility.pdf" file) or write an activity to patch the data.

Data Patching Manually:

1. Write an activity to browse all the eligible instances.

2. Loop through one by one and open the WO.

3. Call "@baseclass.RecreateIndexesForInstance" activity on each WO.

4. If you want index all instances run the activity "Code-.RecreateIndexesForClass" by giving class name as parameter. 

Hope this Helps.