Press "Enter" to skip to content

Obj and RDB methods in Pega – Extended

16

This will be continuation of the previous post 🙂

Obj and RDB methods in Pega

Let me straightly jump into the remaining activity methods.

Obj-Save

  • This method helps in saving the clipboard page to the PegaRULES database or an external database.

When do we use this method?

You use this method either to update any existing instance or create a new instance

Update

  • You can open any existing instance using Obj-Open method, then you can update the properties, once updated you can save the instance with updated values

New instance

  • Instead of opening any existing instance, you can just populate all the details in a particular page and Do a Obj-Save. This will create a new instance with the provided details.

What are the method parameters?

WriteNow

If true, you can commit the clipboard page to the database immediately.

If false, you can defer the commit operation 🙂

Why do we use deferred commit?

Imagine a pega application – Amazon sales.

The case details is stored in 3 tables.

a)      Amazon sales work table

b)      Customer table

c)       Vendor table

  • When you create a new sales work item, you will be updating three tables – making an entry in those 3 tables.
  • You made an entry in work table successfully, but when you commit an entry in customer table, the operation failed.It means, work table entry is of no use, since the operation failed to update the other tables.

Pega provides a solution here 🙂 –  Deferred Save.

You can commit all the details at once. If anyone table update fails, then the other commits are rolled back 🙂

What is deferred list?

  • Imagine this as a page list, which contains the commit operations waiting in queue to get committed in database.

Let’s come back to method parameters 🙂

Write Now –

True – The clipboard page will be committed to the database at once. No instances are added to deferred list.

False – It means we are not writing the instance now. We are sending the instance to wait in the deferred list queue.

WithErrors – You can also commit the clipboard, even if it contains error.

Note: Always keep it un-checked. Pega don’t recommend you to save an error clipboard page to database table.

OnlyIfNew –

True – Only insert operation is performed. Default keep it un-checked.

Let’s test the method parameters 🙂

Step 1: create a new  activity and add a page-new method.

Step 2: Do a property-set pyID=’A-1’.

Note: Case A-1 already exist in database.

We updated customer name from “PremG” to “PremChecksave”

Step 3: Add a Obj-Save method. First we will check writenow-true.

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

You can see commit operation is executed 🙂

Step 5: check in the DB table. Customer name is updated.

Step 6: Update the property customername=”Premkumar” and un-check the WriteNow parameter.

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

You can see no commit operation is performed.

Step 8: Check in the database table.

  • You can see, customer name is not updated.
  • The save operation instance is added to the deferred list.

Step 9: Now add a Commit method.

Step 10: Trace open the run and run the activity.

You can see the record commited in database table.

Step 10: Verify the table.

Done 🙂

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

  • Make sure there is no error message associated with the step page, when you do a Obj-Save
  • We know activities can of different types. Some activity types are explicitly referred in flows – utility, notify, assign, route. When you use obj-Save in such activity types, do not check Write-Now option. This is because, in flow standard activities help in committing 🙂
  • When you need to update any case(locking enabled), make sure if lock is obtained for the requestor session.

2. Obj-Save-Cancel

  • This method is used to cancel the uncommitted Obj-Save method.

When do we use this method?

  • Use this method, only when you need to cancel the uncommitted Obj-Save instance.

Remember, when we do Obj-Save with write now un-checked, the instance is queued to deferred list. When we do commit, all instances in the deferred list will be committed in the database.

In some situation, we need to remove particular Obj-Save instances before committing into database table.

Note: When you need to remove all instances from the deferred list, then use Rollback method.

What are the method parameters?

No Parameters required 🙂

Yes, we don’t require any parameter right!!. We just remove the uncommitted Obj-Save instance.

Provide a step page, that contains the case details.

Let’s test it.

Step 1: Create a new test activity.

Step 2: Add 2 new pages – ActivePage1, ActivePage2

Step 3: Add 2 property-set steps to update the cases – A-1 & A-2

 

I am updating customer name 🙂

Step 4: Do 2 obj-saves on ActivePage1 & ActivePage2 with write-now parameter unchecked.

So when step 6 got executed, then 2  Obj-Save instances will be added to the deferred list.

Step 5: Add a Obj-Save-Cancel method, followed by commit method.

Step 6: Before running the activity, check the customer names for case A-1 & A-2.

Step 7: Now run the activity and check the database again.

  • You can see only Case A-1 is updated, because we cancelled the Obj-Save for A-2.

Note: I hope earlier versions, Obj-Save-Cancel removes the most uncommitted Obj-Save instance from the deferred list. Irrespective of the step page you provide!! Please test it on your own 🙂

3. RDB-Save

  • This method is save the contents of a clipboard page to database table

When do we use this method?

  • Just like Obj-save method, RDB-Save is used to insert a new entry (INSERT) or update any existing entry (UPDATE) in the database table.

You don’t require any locking here. You can directly go and update any columns in any table.

But remember, whenever you need to update any internal PegaRULES table, then use Obj-Save method.

  • If external tables are not mapped to pega class, then you can go with RDB-Save method.

What are the method parameters?

Same like, other RDB methods 🙂

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 Save tab with the Update query.

You can see the values CustomerName & pyID are set dynamically from the page ‘ActivePage’

Note: ASIS keyword is used to restrict substituting the values dynamically inside double quotes.

Step 3: Create a new test activity and set the CustomerName and pyID values.

Step 3: Use the RDB-Save method.

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

You can see the SQL query.

Step 5: Now check in the database table.

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

  • You can use a directive {SQLPage:<pagename>} as a first line of the SQL code. So whenever the SQL query gets executed, then the page(<pagename>) gets created. If error occur, then the properties in that page will contain the error details.

Differences between Obj-Save and RDB-Save method:

a) In Obj-save method, you have an option to delay committing the changes in DB. You can add te Obj-Save instance to deferred list and can be processed later point of time. RDB-Save method always commit the changes as soon as the method gets executed.

b) You can update the blob properties in Obj-Save method where as in RDB-Save you can update only properties that are exposed columns.

c) You can use Commit or rollback methods to control the Obj-Save method.

d) If locking is enable for the instance, then you can update using Obj-Save only when lock is acquired. Locking is not required for RDB-Save method.

e) There may be some situation, where you need to update a column in millions of records. In such situation, you can use RDB-Save method. If you use Obj method, then you may require millions of DB connection to update the records.

So always, think and use wisely 🙂

4. Obj-Delete

  • This method helps in deleting and instance from database table

When do we use this method?

  • Whenever, we need to delete any instance from database table.

It’s like we are deleting an entry from the database table.

  • There are some restrictions in using Obj-Delete method.
  • You can use this method, only when the Object/Instance is already opened in the clipboard page.
  • You will be using the same page as step page for Obj-Delete method.

What are the method parameters?

  • Same like Obj-Save, this method too support deferred committing the instance

Remove

True – system deletes the step page from the clipboard, as well as deleting the instance from database table

False –  then the page will not be removed from the clipboard. You can use Page-Remove method to delete the page.

Immediate

True –  the system commits the changes immediately. I mean the record will be deleted as soon the method gets executed.

False –  then the system adds the Obj-Delete instance to the deferred list.

When the next commit occurs, then the changes are committed to the database table

Let’s test the Obj-delete method.

Step 1: Create a new test activity

Step 2: Add a Obj-Open method and open the A-1 instance.

Step 3: Add a Obj-Delete method with the same step page – ‘ActivePage’.

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

ERRORRRRRRRR 😀

Let me explain what happens at the back end.

Whenever Obj-Delete method gets executed

Step 1: System first checks the step page class.

Step 2: System, then checks if locking is enabled for that class.

If no, then no issues, system will go ahead and delete the instance.

If Yes, then

Step 3: System browse the pr_Sys_Locks table and check if the current requestor holds the locks for the class instance.

If lock is not held, then you get the above error.

Step 4: Now enable locking in the Obj-Open method.

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

You can see, all the index records too deleted for the case A-1. You can see 5 delete instances.

Step 7: Check in the database table. You can see the record got deleted 🙂

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

  • Always open the object/instance, acquire lock if required, then delete the instance using Obj-Delete method, else you may end up with error.
  • System automatically performs index processing. It means it clears the indexes in different index tables.

5. Obj-Delete-By-Handle

  • This method is used to open any instance using Handle

When do we use this method?

  • This method is same as Obj-Delete method.

We saw before, Obj-Delete method, requires opening the instance before deleting.

In Obj-Delete-By-Handle, we don’t need to open the instance, we can directly delete the instance using pzInsKey

Obj-Delete-By-Handle = Obj-Open-By-Handle + Obj-Delete

What are the method parameters?

InstanceHandle – You specify the pzInsKey of the instance

Lock – If the class allows locking, then you should enable this to obtain the lock.

ReleaseOnCommit

If True, lock held by the requestor will be released once we commit the changes to the database table.

If False, requestor will hold the lock even after commiting the changes. This may result in other people unable to update the instance.

Note: Always try to pair lock & ReleaseOnCommit to true. Only in some situation, you may need to retain the lock even after committing the changes.

LockInfoPage – the page to hold the lock details

Immediate – same like the previous methods.

If True, changes are committed at once.

If false, Obj-Delete instance is added to deferred list and will committed once the commit method get executed.

Let’ test the method.

We are going to delete the A-2 instance.

Step 1: First open the database table and get the pzInsKey value.

Step 2: Add a new step Obj-Delete-By-Handle to delete the A-2 instance.

Provide the parameters as shown below.

Here we will test Rollback method

Rollback – This method withdraw any previous uncommitted database changes. Just like it removes all the instances from deferred list.

  • Exact opposite of Commit method.

Step 3: Add a Rollback method.

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

  • You can see Rollback method, not only cancel the uncommitted changes, but also removes the sys lock entry.

Note: Sys lock entry will also be removed when you commit the changes.

Step 5: Verify the database table.

Record still persists.

Step 6: Now replace the rollback method will the commit method.

Step 7: Now run the activity and check in the database table.

Cool 🙂

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

  • Obj-Delete requires the instance to be opened before hand. It means you need an additional step always before executing Obj-Delete. Obj-Delete-By-Handle method don’t require the instance to be opened before. We can directly the instance in a single step.
  • Obj-Delete-By-handle method requires pzInsKey as parameter while Obj-Delete don’t require any parameter but.. remember the first point J

If you know the pzInskey, go ahead and use Obj-Delete-By-Handle 🙂

6. RDB-Delete

  • This method is used to delete rows from the database table

When do we use this method?

  • Use this method, when you need to delete one row or multiple rows from the database table.

This method refers to Connect-SQL rule to delete database table entries

The SQL query can contain DELETE, TRUNCATE or DROP sql statement.

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.

Let’s test the method

Step 1: Prepare the delete tab in Connect-SQL rule.

Step 2: Create a new test activity.

Step 3: Add a new step RDB-Delete.

Step 4: Check the database table for A-4.

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

You can see the delete query got executed.

Step 6: Again check in the database table.

What are the differences between Obj-Delete and RDB-Delete method?

  • Using Obj-Delete method, you can delete only a particular class instance, where in RDB-Delete method, you can delete more than one class instance
  • Obj-Delete method always use a single where clause condition ‘pzInsKey’ to delete a instance, whereas RDB-Delete method can use more than one where clause condition.
  • RDB-Delete method does not invoke any declaratives.

 We are at the end of Obj vs RDB methods 🙂

One final difference between!!

What are the difference between Obj and RDB methods?

  1. Blob properties

Obj methods can update the blob properties while RDB methods can update only the exposed properties.

  1. Deferred Save option

Deferred save option is available only for Obj methods. It means we can delay committing in Obj methods. RDB methods commit as soon as the step got executed.

  1. SQL query formation

In Obj methods, System is responsible to form a query, whereas in RDB methods, we need to form an external query using Connect-SQL rule.

  1. Complex SQL query / Store procedure

Always try using Obj methods. I some situation, you may need to form a complex SQL query – multiple join or to call a store procedure. In such situation, you can prefer RDB methods.

Obj methods are always used to query a single instance, where as RDB methods are used to query multiple instances.

  1. Performance

Performance wise RDB methods are better comparing with Obj methods.

 Let me stop here 🙂

If you guys find other differences, please post as a comment!

Catch you guys in next post 🙂

 

  1. Navpreet Singh Navpreet Singh

    Hi Prem,

    Why Performance wise RDB method is better than Obj Method.

    • Premkumar G Premkumar G

      Hi Navpreet Singh,
      For every Obj-Methods, Pega forms a query and make a database connection to execute.
      say you are using Obj-Save, you cannot save all instance at once. You need to individually save the instances. It may require making many DB hits.
      The same can be achieved in a single SQL query with single hit.

      So that’s why RDB method performance is better 🙂

  2. Vikas Vikas

    Hi Prem,

    Thanks for the post.

    Can you please add about, how to call stored procedures using RDB methods?

  3. venkatesh R venkatesh R

    can u explain how to add a sql database in pega?

  4. Navakanth Navakanth

    Hi Prem,
    Can you please explain functionality present in Error Tab and Specifications Tab in Connect-SQL rule.
    Thx.

    • Premkumar G Premkumar G

      Hi Navakanth,

      I will make a new post on Connect-SQL soon.
      The basic idea of error tab, is to follow a error flow when SQL fails.

  5. vinod khanna vinod khanna

    please make video classes for pega rules we understood better than that

    • Premkumar G Premkumar G

      Hi Vinod,

      Many people have suggested me to use video tutorials. I’m planning on that. I’ll try to do video tutorials soon. Stay tuned. 🙂

  6. usha usha

    Hi Prem,

    Does system delete step page from the clipboard by using obj-Delete-By-Handle, RDB-Delete?

    • Premkumar G Premkumar G

      Hi usha,
      Actually for Obj-Delete-by-Handle and RDB-Delete, we don’t need any step page as mandatory. So its obvious system will not delete any step page

  7. Ben Ben

    Curious hear your thoughts on the RDB-Save only updating exposed columns. Does this mean when you next do an Obj-Open it will not retain the changes from the save if a blob column is present on that table? …Or, does the RDB-Save update the property in the blob also?

    • Premkumar G Premkumar G

      Hi Ben,
      First you cannot update the blob properties from a SQL query. So your RDB-Save query will be updating only the exposed columns.
      When you do Obj-Open next, It will retain all the values – exposed (Updated from RDB-Save) and blob values.

  8. Raj Raj

    Very useful site but got it very late. Sorry for this basic question. How can I connect to Pega Personal edition Postgre db? I have installed pgadmin but where I can find credentials? Appreciate your help

    • Premkumar G Premkumar G

      Hi Raj,
      Pega personal edition package includes Postgres database. You can open and create a new connection with credentials
      Username – pega
      Password – pega

      No need to update the postgres configuration files 🙂 . Please let me know, if you face any issues.

      • Raj Raj

        Thanks Prem! This really helps.

Leave a Reply

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

error: Content is protected !!