Table Valued Parameter in JAVA

It's an alternative and recommended approach to overcome the performance issues of simple JDBC batch approach in SQLServer (mssql-jdbc.jar).

How regular Batching works in MS SQL Server implementation?

Firstly, we must understand that network exchange is a costly activity. If you are making a call from application to Database to perform any database operation there will be communication happens between application and database server. In Bulk, if you are performing any DDL or DML activities it takes humongous time just because of these network exchanges and also time taken to commit on database for every record (sql server commits by default).

This can be avoided by using Batching. Batch Processing allows you to group related SQL statements into a batch and submit them with one call to the database. When you send several SQL statements to the database at once, you reduce the amount of communication overhead, thereby improving performance.

Steps to follow for regular JDBC Batching :

1. First set autocommit as false on connection
2. Instead of executeUpdate, use addBatch()
3. Based on your batch size call executeBatch()
4. After batch execution use clearBatch()
5. commit

It supposed to send all batch operations on one shot, however we identified at database side it is still executing each and every record one by one.
It leads to degrade performance.
Even it is ended up with same behaviour after applying "set nocount on".

Here is the SQL Server Profiler Trace which will show the queries executed during the JDBC batch run. [click here for the traces]


To Overcome this Microsoft came up with Table Valued Parameter.

In table valued parameter instead of adding each record to batch, we are adding to sqlserverdatatable (equivalent to database user defined table type object) and passing
sqlserverdatatable to the query as a single query param.

Steps to follow

1. First setautocommit as false on connection
2. in Query, instead of keeping individual columns as query params we will keep only one query param to which we need to pass sqlserverdatatable.
3. create a user defined table type object for desired table.
4. we have to create sqlserverdatatable using the same schema of table type object we have created in database using addColumnMetadata() method.
5. every record we should add to the sqlserverdatatable using addRow() method
6. using setStructed() method on SQLServerPreparedStatement you need to map the query, sqlserverdatatable and table type object name. It will use sqlserverdatatable and user defined table type object name and pass the bulk data from application to database server in one shot and inserts into the table.
7. execute the statement and clear the sqlserverdatatable object.

It reduces the network exchanges unlike JDBC batching.

Here is the SQL Server Profiler Trace which will show the queries executed during the TVP batch run.  [click here for the traces]

Runtimes to execute 100000 records with batch size of 500

JDBC Batching :

insert statement: INSERT INTO TVPEXAMPLE (name,age,sex,address,pin) values ( ?, ?, ?, ?, ?);set  nocount on
Time Taken for Batch execution : 1557 (ms)

TVP Batching:

TVP insert statement: INSERT INTO TVPEXAMPLE SELECT * FROM  ?; set nocount on
Time Taken for TVP execution : 1056 (ms)

You can see there is around 35% improvement in the performance if you use TVP object while doing batch.

However still the Oracle Batching is much faster than TVP Batching approach in SQL Server because of the driver implementation of Oracle.

You can download the Project from here.

Decision Rules PEGA PRPC

Decision Rules: They are from Decision Rule category. Define condition separately and we need to manually call that condition in different rules

• Decision Table
• Decision Tree
• Map Value
• When

Decision Table: [if, else if], 3 ways we can call Decision Table Methods

• Flows: Decision Shapes
• Activity: Property Map Decision Table (along with the condition if you want to add any page-level expression then we will go with this)
• Declare Expressions: Results of Decision table(along with the condition if you want to add any property-level expression then we will go with this)

Decision Tree: [if, nested if], 3 ways we can call Decision Tree

• Flows: Decision Shapes
• Activity: Property Map Decision Tree (along with the condition if you want to add any page-level expression then we will go with this)
• Declare Expressions: Results of Decision tree (along with the condition if you want to add any property-level expression then we will go with this)

 Map Value: Matrix Condition – Comparing (2 properties), 3 ways we can call Decision Tree

• Flows: Decision Shapes
• Activity: Property Map Value/ Value Pair (along with the condition if you want to add any page-level expression then we will go with this)
• Declare Expressions: Results of Map Value  (along with the condition if you want to add any property-level expression then we will go with this)

When: we can call them in only decision shape.

Validations in PEGA PRPC

There are two types

• Server side validations
• Client side validations

Server side validations: using Activity rule with some methods – post condition/jump

Client side validations: they are 3 types.

• Property Level Validations: can set from advanced tab. It is a fixed validation on property. It applies all over the application for the property. Pre-defined functions are available and we can define user defined validations
• Page Level Validations:  can set from Process > Validation In single rule, we can define multiple properties for validation in page level. We can set these from Flow Action. We can use predefined validations, but you can customize them. No chance to create User defined.
• Declare Validations: combination of Property level and Page level validations.Fixed validations like Property Level validation. Uses functions like Page Level validation

Note: Property level validation will be trigger automatically wherever we used that property. Page Level validation you need to explicitly call them.

Fetching multiple records using PEGA PRPC

How to Fetch Multiple Records/Objects?

In old PEGA versions like PEGA 6 we used Dynamic Select.

It was replaced by Data Page concept in PEGA 7+.

Data Page: Load the data from Data class and reuse it from different FLOWs

Dynamic Select: We need to add manually every time

Data Page can applicable for select box/dropdown/autocomplete/radio.

Data pages can be created under Data Classes, which you can find in Application Explorer.

Fetching Multiple Records for single property:

To Do :

Need to create Data Page under Data Classes > Data Model > Data Page

Need to create Activity in Data Classe.

After creating Data Page you need to add the same to dropdown property in section.

Data Page: All data pages are starts with prefix “D_“

Data Class > Data Model > Data Page > give label and click ok

Structure:  As said we can use Data Page in other pages as well, if you want to restrict it to the same page then we need to choose structure as “page”, if you want to allow then you can use List.

Structure: Page (if you want to restrict access to the same page and you want to load only one property values at a time then you can use this)

List(if you want to grant access to every page and also want to load values of all properties then you will go for this)

In the data source you need to give activity name after creating the activity for this Data page.

You need to create a activity to load the Data Page.

Note: You no need to give the Pages and Classes concept in activity while fetching using Data Page because we are simply calling the data page from activity we can directly check the values in the Data Page.

Step1: use Object-browse method for multiple objects fetching. No need to give the values in step page.

            In the parameters, you need to provide data page name and Data class name. In the select field you need to map the property name. (you need to put . in the box to show the values)

After activity creation, you need to add this activity name in the data source.

If you want to see whether the Data page is loaded or not you can see values by clicking the run option from the data page.

If you want to show the values on the UI then you need to configure the data page in the property configuration under List Source you need to choose Type as Data Page.

Since you can load multiple properties values you need to map the particular property name in the “property for value” option.

Fetching the Multiple Records/Objects for the multiple properties:

Create a Data Page in Data class.

We need to use structure as “List” this time.

Whenever you select List as structure then you need to write configure inside “code-pega-list” class.

Note: Activity will be configured in Data class if the Data Page structure is set to “Page”

Activity will be configured in “code-pega-List” class if the Data Page structure is set to “List”

In the rest of the scenarios, you configure Activity in Work class only.

Fetch Single Record from Database Table using PEGA PRPC

Part 1: Create two sections
first section will ask user to enter username
second section will display user details
Create activity will fetch the data from the database using the username provided in section 1




Part 2: create a flow and configure sections and activity and test the flow.




Here in the Classes and Pages configuration you need to use primary as reference for the Data class and any user-defined name to Work Class.
Step1: You need to use Object-open method to fetch the single object from DB. In the parameters, you need to select the data class and need to provide primary key property name.
You need to use Work class reference in steps page to debug

Step2: You need to use property set method to map values from Data class to Work class. You need to use Work class reference in the steps. In the parameters, you can map the Data class properties in Properties name and Work class properties in properties values.

Note: To insert and retrieve single object you need to write the activity in Work class side only.

Inserting a record into Database table with PEGA PRPC

Part 1: creating a case
creating data model properties at Data class (Data table)
creating data model properties at Work class
creating sec



Part 2: creating a flow action
creating a acitivity
configure stages and steps in cases
configure flowaction and activity using shapes inside a flow




Here is sample Work Flow in step by step

• Configure case (Stage & Processes)
• Add a Data Table (DTO in java which will creates table also)
• Add a Data Model (BEAN in java, create properties should match with the same DTO properties)
• Section (Add multiple layouts and properties)
• FlowAction
• Activity
• Flow
• Test the Case

Note: Any help related any rule / property / flow… you will find under Actions dropdown > Get Help.

Create Activity Rule:
Pages and Classes: You need to give pyWorkPage for Work class (mandatory)
For Data class you can use any name to use it as reference

Step:
Step 1: You need to use property-set method to set the values to the data class from work class, and use “Data Class reference” and in the parameters tab you need to map the Data class properties in Property name and in property values you can give Work Class properties.
Step 2: You need to use object-save method to insert the record in the DB.
Security: Here you need to choose “Utility” under action to visible the activity rule in Utility Shape.
Note: Here we need to create activity in Work class itself. You can debug weather the values from Work class are set to clipboard server or not using pyWorkPage from clipboard tool.
If you want to check weather the values are set to server or not in custom page name you have given for Data Class config in “Classes and Pages” in Activity.

Things you need to know before proceeding with your first CURD actions in PEGA

Here is sample Work Flow

• Configure Case: Cases can be configure while creating application or can be done from Cases Explorer. Which consists Stages and Steps. Here steps are nothing but a flow.
• Data Table: Need to create properties in Data class, which acts as a column in the Database.
• Data Model: Need to create properties in Work class to bind with UI components.
• Section: Where you will assemble all the UI components
• Flow Action: Configure Section here to add any page level validations
• Activity: Your business logic will happen here
• Flow: you can configure your flow here
• Test the Case: Use tools like Clipboard, UI tree, UI inspect, Tracer.

Note: Any help related any rule / property / flow… you will find under Actions dropdown > Get Help.

Stages & Processes:

              You can go to Process by clicking cases types and click on any case; you can change the name of the Stage. Inside Stage, you can define any number of processes (steps). Each step should have one flow.

There are two types of Flows

• Process Flows/ Starter Flows/ Straight through Flows/ Main Flows
• Screen Flows

All 4 possible ways are allowed, like in Process FLOW you can call Process/Screen Flows

In Screen Flows you can call Process/Screen Flows.

Flows/ Starter Flows/ Straight through Flows/ Main Flows

           Right click on any steps click on open then you can see Process Flows. It is called Modular Tool, which has pre-defined shapes.

We use around 7 to 8 shapes in PEGA.

• Start: Begin the flow
• End: end the flow
• Assignment:
• Developer : Adding a Flow Action
• Business Analyst: will assign the task to developer using router and SLA.
• Subprocess:  used to call one flow in another flow
• Decision: used to call a condition in flow 
• Decision Table
• Decision Tree
• Map Value
• When
• Utility:  used to call method inside flow
• Integrator: connectors (inside connectors we need to use  methods with Activity Rule)

In both Flows, all shapes are same but behaviour is different for few shapes

• Assignment shape: In Process Flow, we can define Flow Action in assignment shape connector, but in Screen shape, we can see only empty inside connector. We need to give in the Assignment shape only.
• Routing, Service Level and Notifications are available in Screen Shapes but not there in Process Flows.
• Start shape: In Process Flow, it is empty. In screen, flow you can configure predefined screenplay types, means you can change the appearance of the page by using predefined screen flow type.
• Integration Shape: available only in process screen, not in screen flow.
• In process flow, you cannot navigate back flow. Only forward flow you can navigate. Where as in Screen flow both front and back navigation is possible.
• Work object will be created once after saving a flow, the work can added to the work group. It will automatically defined in the work group. So from the process flow it will generate case id, using that they will monitor the flow. Screen flows does not support work object. However, we can add screen flow to existing work object (process flow). Using subprocess shape, we can call the screen flows in the application.

Section:

           For every section, we need to add FLOW ACTION In Flow Action you need to configure section Under Flow Action > you can set page level validations. Page level business logic can set in Action tab

Activity:

          Activity rule comes Under Technical Rule Category, Using this we can build business logic. 

We can use Activity Rule in 10 different ways. 

• Utility Shape
• Section
• Property
• Flow Action rule
• Harness
• Onchange
• Connectors
• Services.

Explanation of Activity Rule Tabs

• Steps:  

            We can define methods. Only Method is mandatory in this page, rest of them are optional.

• Label: You can either use // to comment that method or you use this to pass parameter.
• Loop: to use loop condition then you can use this
• When: if you want to call this method on any pre condition then we can use
• Parameters: What type of parameters are mapping to which properties need to give it here.
• Method: use the existing method
• Step: to debug the application 
• Description: used to provide info of that method
• Jump: if you want to jump control 

• Pameters: You can provide temporary variable here in this tab
• Pages & Classes: Pages are used for debugging purpose and also reference purpose on what class we can performing the debuggers like Work or Data classes. Pega by default has some page names. 
• pyWorkPage: Generally for the work class we will give. It is case sensitive.          PEGA contain internal server (Clipboard tool/ Testing and debugging tool). Whenever we enter any data in form, first the data will be save in clipboard tool before saving to Database.You need to use pyWorkPage as a reference for insert operation to the Work class.You can give any page name to Data class while inserting.We will use pyWorkPage while inserting records.Using this you can perform server level debug.These will acts as reference to the Work and Data classes in Steps tab.
• Primary: we will give this to Data class reference while fetching single record.

• Security: Controlling the usage of the activity

 You can configure any number of methods in this Activity. Execution will start from order top to bottom. If you want you can jump from one method to another method. Methods are predefined in PEGA; we need to provide inputs/parameters to it.

Different types of methods are there:

• Connector methods
• Obj methods
• Obj-save: insert/update
• Obj-open: Fetch single record
• Obj-browse: Fetch multiple records
• Obj-delete: Delete record
• Property methods
• Property-set method
• Property-map-decision-table
• Property-map-decision-tree
• Property-map-value/value-pair
• Page methods
• RDB methods
• Show methods

Flow: 

        You can create Flow from Cases Explorer or from Process rule category.

Test:

       Use the clipboard debugger tool to debug the values and the flow.