Activity Methods Obj and RDB – Part 1

In this blog article, we will go through different Obj and RDB activity methods and also discuss about their key differences.

There are other blog articles as well on Activity methods which you can look into.

https://myknowtech.com/tag/activity-methods

Update: The activity rule methods remains the same across different Pega versions. The screenshots in this blog article were reused from Pega 7 version. Use this blog article to learn the concepts and you can try it out in the latest versions as well.

Activities are one of the most commonly used rules in Pega application execution.

In an activity rule, we can configure sequence of steps that can executed.

Each step in an activity rule contain a method.

Activity methods can be related to exactly Java methods (that is why Pega named it as method).

So Methods are more or less like a function that can accept some input parameters and executes. Pega accommodates lot of activity methods OOTB that servers different purpose.

In this article we will concentrate only on Obj and RDB methods.. Through-out this article, we will use a test activity and include the necessary methods and then use the Clipboard to test the method execution 😊

We will discuss the following methods

1. Obj-Browse
2. RDB-List
3. Obj-Open / Obj-Open-By-Handle
4. RDB-Open
5. Obj-Save
6. RDB-Save
7. Obj-Delete / Obj-Delete-By-Handle
8. RDB-Delete

Note: There are other Obj methods as well, which we will see in a separate blog article.

Let’s begin with some basics about Permanent data storage.

We know, in Pega we persist the data in database.

So, how do Pega communicate with the database?

you need a language and its Structured Query Language – SQL.

So you think everyone should be certified in SQL language to establish the communication between pega and database systems.

No No. Remember, Pega always makes it simple for developers.

– Pega builds its own query(exception – RDB methods). All you need to do is provide some valid input to pega system through Obj methods.

– There may be some complex query, where you may be forced to use RBD methods.

– You need to know some SQL basics to work on RDB methods.

Note: Prefer using report definition, in case you can achieve your complex reporting requirement using report definition.

So how the preference should be?

1) Always try using Obj methods

2) For complex query fetching, try report definition.

3) If you can’t achieve your requirement using the above two choices, then go with RDB methods.

What is a Connect-SQL rule?

– We know SQL query helps in communication between Pega application and backend database.

– Pega provides connect-SQL rule to run complex SQL queries or call store procedures against the database.

Let’s see how we can create a new SQL rule

Step 1: Create a new Connect-SQL rule.

Package Name – This is required, but no restricted. You can specify any key value here.

Step 2: Fill all the details and click on create button. Analyze the tabs available.

Open – RDB-Open; Delete – RDB-Delete; Save – RDB-Save; Browse – RDB-List

For demonstration purpose, I am going to use my work table

pc_oikggb_myknowpega_work

Before we go through the methods, we need to know  how we persist data in Pega.

Only concrete classes can be mapped to database table. So whenever we persist the concrete class instance, a record gets saved or updated in database table.

Let’s say, I can have a concrete class – OIKGGB-MyKnowPega-Work

This class is mapped to ‘pc_oikggb_myknowpega_work’

Step 1: Open the class – OIKGGB-MyKnowPega-Work

Step 2: Click on the test connectivity button, to check the mapping.

Now let’s check the class instances.

Step 3: Click on App explorer and click on the class, you will see the instances on the right work area.

You can open any instance and view all properties saved under the instance in clipboard.

Click on A-1 and check in the clipboard – please note those three properties – Age, Customername, Phonenumber

Now Let’s check the same instance in Database table.

Step 4: Open the table – pc_oikggb_myknowpega_work.

You can see the same instance is persisted in the db.

here, if you see only few columns are exposed – Customername and phonenumber and not age.

so where age will be saved?!!

The answer ‘Age’ is compressed and stored as binary data in column ‘pzpvstream’

We call it as blob 🙂

What is BLOB?

BLOB – Binary Large OBject

Blob helps in storing a particular instance effectively in database.

In pega, properties can be grouped in some complex structures. We call it as aggregate properties. But DB table save instance properties as discrete Columns. So in order to save these aggregate properties within the instance we can compress it and save it in pzpvstream column.

please visit the pega community link for more info

https://docs.pega.com/system-administration/86/hybrid-data-storage-model-pzpvstream-blob

These basics are enough :). We can jump into activity methods.

What are the common Obj-RDB methods?

1. Obj-Browse

This method helps in searching the class instances and get the required properties in some page.

When do we use this method?

Whenever we need to browse some database table and get some results.

When this method gets executed, Pega system forms a SQL query and search the database table.

For example: If you need to get the customer name and customer phone number for the case ID’A-1’ then you can use obj-browse method with where condition pyID=’A-1’

This method can be used to fetch one or more rows from database table.

What are the method parameters?

PageName – You can specify a page to contain the search results. Whatever class you specify, System defaults the class to Code-Pega-List.

ObjClass – This is a key field. We know classes will be mapped to database table. Pega system uses this class to identify which table we need to browse the results.

Here we specify ‘OIKGGB-MyKnowPega-Work’which is mapped to pc_oikggb_myknowpega_work table.

MaxRecords – You can specify any number of results to return.

If left blank – default value is 10,000

GetRowKey – When selected, this will be used to get the value of the primary key in the corresponding database table. Default selected.

RowKey – This is applicable, only when you select GetRowKey – true.

You can specify the primary of the database table. Default all internal table primary key will be pzInsKey.

UseLightweightList – This is a key aspect in performance.

When to use LightWeightList?

In some scenario, you may need to browse the records and use it only for display purpose. In such case, You can check this option. Please follow the below link for more details

https://docs.pega.com/system-administration/86/identifying-rules-not-using-lightweight-lists

Logic – In some scenario, you need to use more than one field in where condition.

Say for example, you need to fetch the cases, where customer type = “platinum” or Place = “India”.

– In such scenario, you can specify the logical condition here A OR B and use the label in each condition.

– If left blank, always AND condition is applied for all fields.

You can add many rows. Each row can refer to a field in the class instance.

Label – If more than one condition is required, then you can label each condition and use in the logic field.

Select – In some scenario, you need to fetch the value of the fields, which are used in where conditions. You can check this box.

Field – You can specify the properties you need to fetch and those properties you need to use in where condition.

Condition – You can ee a picklist with variety of conditions for where clause.

Value – You can specify the where clause value.

Sort – You can sort the results based on any field. No Sorting / Ascending/ Descending

Okay now let’s test.

We need to browse all amazon sales case ( pyID starts with ‘A-‘ and Age is not null) and get the values – Customer name, phonenumber and pyID

Step 1: Create a new test activity.

Step 2: Add a obj-Browse method.

You get an error message for Age field in where condition.

“Only exposed properties can be selected in where clauses”

Remove the age is where condition. We can use only condition pyID starts with ‘A-‘

Step 3: Trace open the rule and run the activity.

Click on the SQL and check the trace event. You can see pega forms a SQL query.

There you can see pzpvstream is used, because Age is a blob property and not exposed column.

Step 4: Open the clipboard and check the results.

You can see, the Obj-Browse method is used to fetch the 4 properties – Age, CustomerName, PhoneNumber, pyID.

 What are the things to remember when you use Obj-Browse method?

Max records – Try providing appropriate value here based on your requirement. This can contribute performance.

Use light weight list, when you want to use the search results only for display purpose

System creates the following SQL Query

Select * From <> Where <> Order By <>

Click on the GetCaseDetails (Code-Pega-List) page, to know the result count and SQL query.

 2. RDB-List

This method use a Connect-SQL rule to fetch the results.

RDB-List ->> Connect-SQL ->> Browse tab->> SQL Query.

When do we use this method?

Always try using Obj-browse method to get the search results.

In some situation, you need to browse more than one table and Obj-Browse method don’t support more than one instance in a single step. In such situation, you can use RDB-List method.

When you need to execute some complex search query, then go with RDB-List

What are the method parameters?

RequestType – Specify the request type of the Connect-SQL rule ( We can call it as rule name)

Access – Package name of the Connect-SQL rule

ClassName – Specify the class that include the Connect-SQL rule

MaxRecords – You can specify the max record count to fetch.

BrowsePage – You can specify a page to contain the search results. If left blank, the search results will be included in the step page.

ApplyDeclaratives – Normally declarative rules get executed, when you update some properties ( declare expression or on change) in the clipboard pages. You can conditionally skip the declarative processing.

RunInParallel – You can execute the Connect-SQL rule to execute in parallel.

Let’s test the method.

Step 1: You can make use of Connect-SQL rule created before – UpdateCase

Step 2: Update browse tab with the SQL query.

You see instead of providing the table name, I used the class name!! Why??

We know pega concrete classes can be mapped to database table ( both internal and external). So instead of hardcoding the table name, you can provide the class name 🙂

Step 3: Use the same test activity and include a RDB-List method.

Step 4: Trace open the rule and run the activity.

You can see the query. Class replaced by the table name.

Step 5: Check in the clipboard.

You can see GetWorkDetails contain the search results 🙂

What are the things to remember when you use RDB-List method?

You can specify any value in Access parameter. Normally if we use the database name there – ORACLE, MSSQL etc

When you use ‘RunInParallel’ a new requestor session copying the access group and clipboard is created. You can use Connect-Wait method later to access the results

pxSQLStatementPre/Post properties contain the SQL query.

3. Obj-Open

This method is used to open any instance stored in the internal or external database table.

When do we use this method?

Whenever we need to open any particular class instance, from an activity.

Note: Only concrete classes can have intances.

just think like you need some key to open any object in the database.

pzInsKey– represents the primary key of Internal data tables.

Normally pzInsKey = < pxObjClass + pyID>

What are the method parameters?

OpenClass – identify the class instance, which we need to open ( concrete class)

Lock – some instances support locking.

Why do we need locking?

Say for example, you created a purchase request case. Agent 1 starts to update the case. In this time space, we shouldn’t allow other people to update the case.

we will see more about Pega locking architecture in different lesson 🙂

True – This instance will be locked. Other requestors cannot obtain lock till it is released. Use this only when you need to update the instance.

False – Use this option, when you need to just open the case and no update is required.

ReleaseOnCommit – You can release the lock, once you commit your changes. So that other requestors can obtain the lock and work on the case.

True – Lock will be released, once instance is commited in database.

False – lock willl not be release, when the instance is commited in database.

LockInfoPage – Say for example I have acquired the lock on case ‘A-1’. Now when you try to acquire the lock on A-1, you will there thrown error.

• The error will contain the lock details like – Requestor who holds the lock, lock expiry date etc.
• You can specify a page here to hold all the lock details.

Property name & value – This is the key part. you need to specify the key to open the instance.

How to identify the key for any instance?

Step 1: Open the class of the instance – OIKGGB-MyKnowPega-Work

Step 2: Check the keys for the class instance.

You can the instance Keys – pyID. So you need to open the instance using pyID.

Property Name – .pyID

Property value – “A-1”

Let’s test the method 🙂

Step 1: Create a new test activity.

Step 2: Use Obj-Open method. Provide the required parameters as shown below.

Note: A-1 case is created already!

Step 3: trace open the rule and run the activity.

Remember to check the DB Query option in tracer settings.

Step 4: Run the activity and check the tracer.

You can see our Pega forms the SQL query.

Step 5: Check the clipboard. You will see the case instance A-1 details are populated in ActivePage.

We have opened the case instance 🙂

What are the things to remember when you use Obj-Open method?

Obj-Open method always open the instance in the step page specified.

a)      If the specified step page is not available, then a new page gets created and the instance is opened in the new page

b)      If the step page is already available, existing page is replaced by the new instance.

c)       If the step page is empty, then the primary page will be considered the step page to open the instance.

If you check lock option, then the case can only be opened when the case is not locked by other user. If it is locked, then we will get an error message.

If the instance you try to open is not available, then the system throws error

 4. Obj-Open-By-Handle

This method is used to open any instance using Handle

When do we use this method?

This method is the same as ‘Obj-Open’ method.

Use this method, only when you know the unique handle of the instance. Otherwise always use Obj-Open

Unique Handle = pzInsKey of the database table instance.

What are the method parameters?

InstanceHandle – pzInsKey of the instance.

You can open any case and identify the pzInsKey in the clipboard.

pzInsKey = pxObjClass + pyID

Lock, ReleaseOncommit, LockInfoPage – Same as Obj-Open method parameters.

You can test the method like the same using Obj-Open method.

Pega forms the same type of query.

 select pzPVStream , pxCommitDateTime from public.pc_OIKGGB_MyKnowPega_Work where pzInsKey = ‘OIKGGB-MYKNOWPEGA-WORK A-1’

What are the differences between Obj-Open and Obj-Open-By-Handle method?

Obj-Open method requires the instance class and instance primary key as input parameter, where as Obj-Open-By-Handle requires only handle as its method parameter.

Prefer using Obj-open method. When you are sure of pzInsKey, then go with Obj-Open-By-Handle.

But there is no restriction like we should always use particular method 🙂

5. RDB-Open

This method is used to retrieve a single row from the database table

When do we use this method?

When there is some complex query and you need to return only single row from the database, you can use RDB-Open method.

Actually we use it very very rare 😉

If you are very sure, that the result count will be 1, then use this method.

Even if the SQL query where clause returns more than one row, only first record will be opened in the step page.

If the SQL query returns no row, then you will be thrown wit run time exception.

What are the method parameters?

RequestType – Specify the request type of the Connect-SQL rule ( We can call it as rule name)

Access – Package name of the Connect-SQL rule

ClassName – Specify the class that include the Connect-SQL rule

RunInParallel – You can execute the Connect-SQL rule to execute in parallel.

Step 1: Open the same connect-SQL

Step 2: Update the Open tab with SQL query, use pyID like ‘AB-‘.

Here the query is not right, because it will return 0 rows

Step 3: Use the RDB-Open method.

Step 4: Run the activity and check in the tracer.

Step 5: Now update the Connect-SQL query to fetch pyID=’A-1’

Step 6: Now again run the activity and check no error. Open the clipboard.

You can see the details are populated in step page – ActivePage

What are the things to remember when you use RDB-Open method?

You this method, only when you need to access a single instance

Differences between Obj-Open and RDB-Open method:

1. Obj-Open requires pyID value where clause – pyID(primary key), whereas RDB-Open can use any exposed column in where condition.
2. Obj-Open decompress the blob and populate all the details in the step page that includes all the embedded properties too. RDB-open can fetch only the exposed columns, you cannot access blob fields. This is a major drawback in RDB methods.
3. Internal PegaRules database – Always use Obj-Open method.

Remember, you can map pega classes to external table. In such case, you can use Obj-Open method.

If the external tables are not mapped to pega classes, then you can use RDB-Open method.