Database and Tables for Pega

 

[DB Server > Databases > Tables > Records (columns & cells)]

Working with PegaRULES database

⮚      RDBMS (Relational Database Management System):

So called popular Database Servers (like Oracle, Teradata, DB2, SQL Server, PostgreSQL…) are based on RDBMS which has table structure (Rows & Column)

🡪    Hierarchy: DB Server > Databases > Tables > Records (columns & cells)

DB Server contains multiple databases, each database can contain multiple tables which in turn contain n number of Records (or rows) & Columns where the real data get stored persistently (permanently)

⮚      Database Server: Is the server that stores, maintains and manages the data (by CRUD operations)

🡪    Ex: Oracle, Teradata, DB2, SQL Server, PostgreSQL (default for Pega) …

🡪    Some of DB Servers vs. DB Client (may vary)

PostgreSQL server            –          pgAdmin4 client

Oracle       server                         –          TOAD client / People Plus client

SQL            server              –          SQL Developer,SQL Server Management Studio

☞      DB Client: is the snapshot of Database Server, need to be refreshed always

🡪    Operations (CRUD):

INSERT (Create), SELEDatabase CT (Retrieve), UPDATE (Update) & DELETE (Delete) records

UPSERT: UPDATE + INSERT

●       COMMIT: To commit ALL the recent defer operations (uncommitted db changes either Save or Delete) and also releases the locks held

●       Rollback: to cancel all the previous deferred operations (uncommitted database changes)

🡪    INSTANCE (Object): The data saved persistently in the table as a record

Called Rule instances if it is Rule table, Work objects if Work table, Data objects if Data table, Index objects if Index table, Assignment objects if Assignment tables (Worklist & Workbasket), History objects if History table …

☞      OBJECT: Similar to instance

Object can have multiple instances – say we are objects of human but instances of Brother & daughter

🡪    Some of important SQL keywords (NOT case sensitive):

1.      SELECT - extracts data from a database

WHERE / GROUP BY / HAVING / ORDERED BY / DISTINCT

SELECT * FROM personaledition.pr_operators where pyUserIdentifier = 'Admin@HSBC'

2.      UPDATE - updates data in a database

3.      DELETE - deletes data from a database

4.      INSERT INTO - inserts new data into a database

5.      CREATE TABLE - creates a new table

6.      ALTER TABLE - modifies a table

ALTER syn tax to add a single column in the db table:

ALTER TABLE <personaledition>.pc_hsbc_hrms_work

ADD COLUMN duration numeric(18);

7.      To add, delete, or modify columns and also used to add and drop various constraints on an existing table

8.      DROP TABLE - deletes a table

9.      CREATE INDEX - creates an index (search key)

10.  DROP INDEX - deletes an index

🡪    Stored procedure (called in Connect SQL rule): It is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in a RDBMS system as a group, so it can be reused and shared by multiple programs. It will reduce network traffic and increase the performance

⮚      Database (storage location): DB Server > Database

●       Database is just a storage location that contains n number of tables (having data in terms of Rows & Columns)

●       RDBMS may be anything  like Oracle, Teradata, DB2, SQL Server, PostgreSQL in real-time projects (Client’s wish), but the database is same PegaRULES db where PRPC s/w is installed

●       Database has 2 types of structures (stores data in 2 formats): Structured & Unstructured data (BLOB data type) stored

●       Pega PRPC s/w is installed as records in the form of RDBMS tables contained in the database called PegaRULES (also called Rulebase)

☞      Records = RULES = Objects = Instances of Classes

●       PegaRULES db has Split-schema from V6.3 (2 schemas): PegaRULES & PegaDATA

1.      PegaRULES is also called Enterprise DB where all Rule- instances are stored

2.      PegaDATA is also called Work DB where all other than Rule- instances such as Work, Data, Assignment, History, and Index… are stored

☞      Customer Data (non-BLOB) database rule instance is introduced from V7.4. The tables created in it, don’t have pzPVStream (BLOB) columns

Note: But still better use PegaDATA than it, to handle embedded structure which is not possible in ‘Customer Data’ db

●       Pega stores the permanent data in database tables and temporary data in memory

●       In Pega, physical database is mapped by Database Name data instance (‘Data-Admin-DB-Name’) whereas physical table is mapped by Database Table data instance (‘Data-Admin-DB-Table’)

☞      In our training classes – we used to treat ‘Data-Admin-DB-Name’ as Database bridge & ’Data-Admin-DB-Table’ as Database Table bridge

●       The PegaRULES & PegaDATA databases, as initially installed, consists of about 200+ tables that hold all the rules, data instances, work items, history, and other concrete objects from internal classes of your Process Commander system

●       Classes that are mapped to the internal databases (i.e. PegaRULES & PegaDATA db schemas) are known as internal classes. Concrete classes that correspond to rows of an external database (i.e. non-PegaRULES & non- PegaDATA) are known as external classes.

●       Schema (design / structure): Overall design of DB, XML…

☞      Like folders within database

☞      Don’t forget to go back & update Database Table instance (by clicking on Test connectivity button) in Pega, once Schema is changed, otherwise schema changes will not reflect in Pega

●       Modify Database Schema: To review & modify the current db table structure in PRPC itself

●       JDBC (Java Database Connectivity - Java API)in Pega: It establishes the connection between Pega Application (in App Server) with database [Privileges can have it]

●       XML component: To exchange the data by tagged format

BLOB

⮚      In general – BLOB (Binary Large Object) is the data type that stores unstructured huge data in the BLOB column

⮚      What is BLOB in Pega?

Stands for Binary Large Object, is the type of unstructured data stored in the ‘pzPVStream’ (Storage Stream or BLOB column) column in the form of compressed XML & mostly it is used to store user defined custom Properties (Property value pairs)

⮚      Why BLOB?

To preserve the data in unstructured format by avoiding multiple tables & also for future reporting (querying), hence performance is good

Advantages: Memory size is less (max up to 2 GB in single cell) to back up, Performance is high and Security is good...

Disadvantages: Duplicate data is maintained. But putting the condition on this BLOB column may cause JVM crash. So Pega doesn’t allow you to put any condition on this. If so, optimize it from BLOB for future reporting

⮚      Property Optimization(Exposing Property):

●       A single value property that is visible as a column in the table is said to be exposed.

●       When you optimize a Property, Pega creates a column same as property name in the db table [Map column & Property in External Mappings tab of Class, if we want different names]

●       Optimizing Properties:

🡪    Exposing Single Value Properties:

🡪    Exposing Page mode Property values (Single Page / Page List)

⮚      Database physical Table is mapped by Database Table instance of Data-Admin-DB-Table in PRPC

⮚      Internal tables vs. External tables: Internal tables are the tables of Pega databases (PegaRULES & PegaDATA) whereas External tables are the tables of other than Pega databases

⮚      Every internal table of PRPC must have four columns: [Available in @baseclass]

pzInsKey (called Instance handle): is the db side unique key throughout the database

pxObjClass: Stores Class name

pxCreateDateTime: Date and time the object was first saved

pyLabel: A Short Description of any rule

⮚      Primary keys:

🡪    pzInsKey is db side unique key throughout the Database

Syntax:

CLASSNAME    CLASSKEY   !   NAME    #     YYYYMMDD    T   HHMMSSTTTTMMMGMT

🡪    Class key (or Visible key) is Pega side unique key within table & is always a part of pzInsKey

Some of important WORK related

⮚      Work is a discrete task that can be accomplished

⮚      Work Object (or Work item or Case or Job): primary unit of work

●       The work saved persistently (permanently) into Class Group table  as a record & it is identified by pyID (which has again 3 parts as below)

●       This pyID (Case ID) Property holds the work object ID (ex: C-3-Hyd) & is defined as class key in work pool (Class Group)

It has 3 parts as follow:

1.      pyWorkIDPrefix Property: holds prefix of case

2.      pyWorkIDSuffix Property: holds suffix of case

3.      Generate ID activity (Utility type): generates sequential unique id (its recent number is stored in pyLastReservedID column in pc_data_uniqueid table)

●       Basic Work Info of any Case:

1.      ID (above Case ID - pyID) holds work ID

2.      Status (Case Status - pyStatusWork): Progress of Work Object

These can be found: DS > Process & Rules > Processes > Status Values (in the form ‘Field Values’)

3.      Urgency (Case Urgency - pxUrgencyWorkClass): Priority of the Work (range: 0 – 100)

●       Assignments:

1.      Generate a Case against other Case creation

2.      Generate child Case against Parent Case creation

3.      Allow users to create a child Case at 2^nd Stage

4.      Allow users to create a  Case on form submission (or even by button click)

5.      Schedule to generate a Case

6.      Generate a Case through Screen Flow

⮚      Work Object form is an application related screen defined by Harness

⮚      Work type(or Work Class or Work Object Class) is a concrete class derived directly or indirectly from Work- base class that represents type of work being performed

⮚      Work pool is a collection of work types and it grants access to users to create work objects

⮚      Work List is a list of assignments for a specific user, ordered by decreasing Urgency.

Note: Don’t forget it is not a rule but just an element, which is automatically associated with Operator ID creation itself

⮚      Workbasket (Work Queue) is a list of assignments for a group of users, ordered by decreasing Urgency & an instance of Data-Admin-Workbasket

☞      Also called ‘Work queue’ - A centralized, shared pool of assignments

⮚      Work Group is a group of work lists and workbaskets Operators that report to Manager defined in it & is an instance of Data-Admin-WorkGroup

⮚      Some of predefined Tables that come with PRPC installation as:

●       Work related tables (Under PegaDATA): These are involved in Flow progress until resolved

Remember Main work (Case) related data is again organized into several tables as below:

Table	Class	Page	Class Key	Purpose
pc_work (later Cloned to CG)	Work-	pyWorkPage	pyID	Work Object info (Business transactions)
pc_assign_worklist	Assign-Worklist	newAssignPage	pxRefObjectKey, pxFlowName	Worklist info (assignee)
pc_assign_workbasket	Assign-WorkBasket	newAssignPage	pxRefObjectKey, pxFlowName	Workbasket info (assignees)
pc_link_attachment	Link-Attachment	---	pxLinkedRefFrom, pxCreateDateTime	Attachment links
pc_data_workattach	Data-WorkAttach-	---	pxRefObjectKey, pxAttachKey	Attachment content (1 GB)
pc_history_work	History-work-	---	pxHistoryForReference, pxTimeCreated	Work Object history (from CG)
pr_index	Index-	---	pxInsIndexedKey, pxIndexCount, pxIndexPurpose	Embedded info (from CG - BLOB)

 

●       DATA related tables: (Under PegaDATA)

🡪    Our custom user defined reference tables (Custom class; Custom key) i.e. Data Tables (Data Types)

🡪    Admin related components (derived from Data-) are stored in their respective tables i.e. Operator IDs in pr_operators& Access Groups, Org, Div, Unit, Work Groups, Workbaskets, Calendar etc… in pr_data_admin

🡪    Some other tables are: pr_data, pi_data_connect, pr4_base, pr_data_customproperties, pySysNodeID, pr_data_file

🡪    pr_history_data(History-Data-): History of Data objects

●       RULE related tables: (Under PegaRULES):

🡪    pr4_ruletable (Rule-): [Most of the rules]

Most of the Rules will share this single table pr4_rule table where as some other Rules are stored in their respective tables i.e. Flow rules in pr4_rule_Flow, Property rules in pr4_rule_property, Field Value rules in pr4_rule_FieldValue …

🡪    pr4_history_ruletable (History-Rule class): History of Rule info

●       HISTORY related tables: (Under PegaDATA)

🡪    pc_history_work table (History-work-): Work Object history (from CG)

Use AddHistory activity / History-Add method / even ‘Audit note’ field (available most of the shapes) to insert into its Work History table

🡪    pr4_history_rule table (History-Rule): Rule instances History

🡪    pr_history_data (History-Data-): Data objects History

🡪    pr_history

●       Some Other tables:

🡪    pr4_base table (Rule-Obj-Class; pyClassName key): [Instances of 4 foundation classes]

Below 4 concrete classes are known as foundation classes and Instances of these 4 classes are always stored in this pr4_base table

1.      Rule-Obj-Class (Class rule instances)

2.      Data-Admin-DB-Table (Database Table data instances)

3.      Data-Admin-DB-Name (Database data instances)

4.      Data-Admin-DB-ClassGroup (Work pool data instances)

☞      Never change the database table mappings (bridges) for these four classes. These classes must stay in the pr4_base table as initially installed, with the Catalog and Schema fields blank

☞      This table is Specified in 'prconfig.xml' by PRPC

🡪    pr_sys_queues (System-Queue-DefaultEntry): Work & Assignment queue items info

🡪    pr_sys_locks (System-Locks): Lock info of Work (Case)

Whenever the work object is first accessed (opened) by an Operator, PRPC acquires a lock on it and makes an entry into this pr_sys_locks table

🡪    pc_data_uniqueid (Data-UniqueID): Stores next available unique ID (i.e. recent id)

To store the most recently assigned work item ID for a specific prefix

🡪    pr_other table (NO class): dummy table in Pega

Each time an object is saved to the pr_other table, the system adds a PEGA0041 alert to the alert log

🡪    pr_history table Etc…