Hi,

Developer can easily optimize (expose) a property using optimization wizard in Dev. After application is released in Prod, let's say we have a new reporting requirement in the next phase. We have to optimize the property in Dev first, then Prod. In the past, I have seen quite a few customers faced issues in their migration process. In this post, I will share my analysis to eliminate such errors.

1. Optimization in Dev

Let's think of two scenarios. Both are purchase request applications. One is that you can only request a single item (Single Value property) and the other one is that you can request multiple items (Page List property).

1-1. Scenario 1 (Single Value property)

1-1-1. Properties creation and UI image

1-1-2. Data is stored in Blob (not optimized yet)

1-1-3. Run optimization wizard

1-1-4. Repeat this step for ItemName and ItemPrice

1-1-5. Columns are now exposed and populated in the table

1-2. Scenario 2 (Page List property)

1-2-1. Properties creation and UI image

1-2-2. Data is stored in Blob (not optimized yet)

1-2-3. Run optimization wizard

1-2-4. Repeat this step for Name and Price

1-2-5. Index table is now created and records are inserted to the table

As we saw above, optimization process is decomposed into two steps. From here onwards, I will use the term "Step1" and "Step 2".

• Step 1 is to run DDL statement to change table structure.
• Step 2 is to populate the exposed columns.

Below is the table state with two scenarios in time sequence.

2. Optimization in Prod

Now, how should we optimize properties in Prod? I wouldn't recommend that you use optimization wizard from Dev Studio. Basically you have two approaches, R-A-P or DDL. The recommendation is R-A-P approach because Step 1 and Step 2 are both done automatically as part of Pega 8 import process (not Pega 7). However, some customer doesn't grant DDL privileges (CREATE TABLE / ALTER TABLE) to your Pega-associated database user and in that case you can't change table structures from Pega's import process. You'll need to ask DBA to run DDL from DBMS directly for Step 1. If table structure is changed by DDL outside of Pega, system can't recognize it and you have to manually take care of Step 2 by either activity, prpcUtils, or prpcServiceUtils. For Single Value property, you can create a custom activity to loop over the instances and do Obj-Save (no need to Property-Set anything). This operation does copy data from Blob to the exposed properties. For Page List property, Index table is created. You can private edit Code-.ReCreateIndexesForClass out-of-the-box activity to check "Lock" in the Obj-Open-By-Handle step and run it, passing the class name. prpcUtils works without system restart while prpcServiceUtils requires it. Please see how to use prpcUtils and prpcServiceUtils at https://support.pega.com/discussion/how-use-prpcutils-and-prpcserviceutils.

• For Step 2 with Single Value property, you can create a custom activity. Below is a sample code.

• For Step 2 with Page List property (Declare Index), you can private edit Code-.ReCreateIndexesForClass out-of-the-box activity to enable "Lock" checkbox and run it passing the class name. The class name is not the one of Index class (ex. "Index-MyCo-Data-Item") but the one where the Page List property is defined, in this particular example "MyCo-MyApp-Work-PurchaseRequest".

• Pega 8 import process does Step 2 by default, but it is also possible to disable it from advanced mode in the import wizard.

* Single Value property (default is enabled. You can check it off to disable it)

* Page List property (default is enabled. You can check it off to disable it)

3. Troubleshooting and Frequently Asked Questions

Q. I have optimized a property in Dev and then imported R-A-P to Prod, but table structure wasn’t changed in Prod. Why?

A. Probably you didn't include Data-Admin-DB-Table instance after optimizing in Dev. PRPC holds table information (columns, indexes, and constraints) as part of Data-Admin-DB-Table instance. When importing R-A-P, system compares the actual table in the target environment with Data-Admin-DB-Table instance. If there are any differences, system detects it and runs DDL (CREATE TABLE / ALTER TABLE) to synchronize it.

* If you are exposing a Single Value property in Work class, include Work class.

* If you are exposing a property in Page List, include Index class.

Q. I imported R-A-P to Prod in Pega 7. The table structure was successfully changed but the exposed columns are still empty. Why?

A. Unlike Pega 8, Pega 7 import wizard does only Step 1 and doesn't do Step 2. Pega 8 import wizard is enhanced and now capable of doing both Step 1 and Step 2 (Step 2 is enabled by default, but you can also disable it if you desire).

Q. I asked DBA to run DDL from DBMS directly for Step 1. When I ran prpcUtils, the exposed columns got populated but when I run prpcServiceUtils it remains empty. Why?

A. When DDL is run outside of PRPC, the engine doesn't know the table structure changes. Hence, you need to restart system in order to let the engine know before you run prpcServiceUtils. On the other hand, prpcUtils works without system restart. This is because prpcUtils starts JVM every time and table structure changes are recognized at its start-up.

Q. Starting with Pega 8, Step 2 is automatically done in the import process by default. Then what is the use case that we need to use prpcUtils / prpcServiceUtils or activity?

A. Yes, Step 2 is now automatic with Pega 8 import process. So most of the cases you wouldn't need prpcUtils / prpcServiceUtils or activity. One scenario is that you don't have DDL privileges in your PRPC-associated database user and you can't do Step 1 as well as Step 2 in the import process. Another scenario is if DBA manually changes table structures for any reasons, such as extending the column length, you may want to manually re-run column population job to refresh table records. You can have flexible control over execution timing with these additional tools.

Q. In Pega 8 import process, Step 2 is enabled by default. What is the use case when you want to disable it?

A. Most of the cases you can keep the default, but if you want to do Step 2 at your own preferred timing, you can disable it. For example, you have many tables that have millions of records in the target environment and you want to separately run Step 2 one by one later on so system won't crash. In that case you can take prpcUtils / prpcServiceUtils approach instead.

Q. If custom activity to loop over the objects and Obj-Save works for Single Value property, I guess the same approach works for Declare Index as well (i.e. loop over the Index table "PR_INDEX_MYCO_DATA_ITEM" and do Obj-Save each instance)?

A. Correct, but it works only when you are adding columns in the existing Index table. In other words, this approach won't work when you are optimizing the very first property in the Page List, because the table will be created and records don't even exist at that point yet. The methods described above work for both cases.

• Notes:

I have also authored a related topic regarding Pega Blob and UDF. If you are interested, please take a look - https://support.pega.com/discussion/understanding-pega-blob-and-udf.

Hope this helps.

Thanks,