Obj and RDB methods in Pega

Introduction

In this post, you will see different obj methods and rdb methods and how to use them in a activity.

I will be post the key difference at the end of the post πŸ™‚

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

Before that let’s discuss some basics πŸ™‚

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

So, how do you 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.

A post on report definition will peep soon πŸ˜‰

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?

I planned to make a separate post on Connect-SQL. So here I will touch some basics.

  • 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 perist 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 PDN link for more info

https://pdn.pega.com/understanding-pzpvstream-blob-database-column

These basics are enough :). We can jump into activity 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://pdn.pega.com/about-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.
Β select pzPVStream , pxCommitDateTime from public.pc_OIKGGB_MyKnowPega_Work where pzInsKey = β€˜OIKGGB-MYKNOWPEGA-WORK A-1’
Β 

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.

I will end this post here.

Please visit the below link for remaining Obj RDB methods πŸ™‚

29 thoughts on “Obj and RDB methods in Pega

    1. Hi Jagadish,
      Since many people have asked about a post for it, I’ll try my best to post about that as soon as possible. Stay tuned. So sorry for the late response.

  1. What if I have used RDB-List and browse tab is empty, we have query in all the 3 tabs like in open, save and delete ??

    I mean to say like all the methods Iike rdb- list or open or save or delete will only search their respective tabs like browse, open , save and delete??

  2. Hi Prem,

    Very well explained, thankyou.
    Why it is recommended to use RDB methods instead of Obj methods , while we need to hit external db ?

    1. say you need to update 1000 of records from a user session. If you go with Obj-methods, then you need to open all records individually and save those records. It may require 1000 DB hits. You can update the same in a single SQL query, in a single DB hit using where clause.

      But pega recommends to use Obj-methods, if the classes are mapped to external DB table. You can make use of commit and rollback.
      Both have advantages and disadvantages.

  3. Thq Very Much Prem. I really appreciate your efforts for making posts like this.
    Can you please elaborate more on RunInParallel option mentioned in RDB-LIST method and also usage of Connect-Wait Method.
    Thx Prem.

    1. Run in Parallel in Connectors- We use this for parallel processing in the activity. The current requestor spawns a parallel requestor. The connect method gets processed in a parallel requestor whereas the current requestor continues with the next step. You can use Connect-Wait method later in the activity to access results back from the connector.

  4. Good Expalnation Prem… πŸ™‚
    There is one option called RunInParallel,Can you elaborate it and when & how it can be used…

    1. Hi RayMir,
      say, whenever you are working on a process, every action will be handled within your requestor session. say you hit a Connect service from your requestor session, you may not do any action, till you receive response. agree. In order to avoid this, we can use Run in parallel option, where the method gets executed in child requestor session. This is independent of your session. later step you can use wait method to retrieve the results from your child requestor.

      RunInParallel – improves performance. You use this, when you don’t need the response at the moment and can be used later point of time

    1. Hi Gouri, Thanks for pointing it out. can you be more specific. It will be really helpful to correct the mistake πŸ™‚

Leave a Reply

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