How to view the Table properties & values in Pega ?
Can anyone Please suggest how to view the Pega DB tables properties and values for a case?
For Example, I have created a case type with properties/data models like Customer name, number, Address etc, for each of the case different data will be stored in PEGA DB....How can i view these properties values using DB tools like Mysql, SQL Developer, Toad etc. (or) is there some other way?
There are multiple ways to retrieve the value of the table from database.
From designer studio, you could write an activity in the class which case belongs to or the properties belongs to. In the activity, you could invoke Obj methods to retrieve the value, it's somehow similar to SQL statements as it generates the same in the background.
More about Obj-Methods,
Use the Obj-Browse method to search instances of one class and copy the entire instances, or specified properties, to the clipboard as an array of embedded pages.
Only properties exposed as columns can be used as selection criteria. However, values of properties that are not exposed as columns, including embedded properties, can be returned.
Refer: Obj-Browse configuration and other parameters.
Use the Obj-Open method to open an instance stored in the PegaRULES database or in an external database linked to an external class, and save it as a clipboard page.
The system uses the specified class and key fields to find and open the object and place its data into the specified step page. The system searches up the class hierarchy as appropriate to find the instance. If it finds the specified step page, the system clears any data that is on it and reuses the page. If no existing page has a name matching the specified step page, the system creates a new page.
Except in rare cases, identify in the Step Page field the name of a page to be created by this method. If the Step Page field of the step is blank, this method opens the object into the primary page of the current activity, replacing the current contents of the primary page and changing the class of the primary page if necessary to match the class of the object. Typically, this is not desirable.
Refer Obj-Open for configuration and other details.
The handle of an instance is a unique key, in an internal format, assembled by the system that identifies an instance in the PegaRULES database. It may differ from the visible key generally used to identify instances. (Every instance has a unique handle, but more than one instance may share the same visible key.) For rule instances, the handle includes system-generated identifying values (including the create date and time) that make it unique.
Use the Obj-Open-By-Handle method only if you can determine the unique handle that permanently identifies which instance to open. Otherwise, use the Obj-Open method.
Rows of a view (rather than in a table) in the PegaRULES database do not have a handle. This method cannot be used with classes corresponding to views.
When used on a primary page, this method clears any data on the page and reuses its name and position in an embedded Page List.
An external class (defined through the Connector and Metadata wizard) may not contain one property that can serve as a handle. To support the Obj-Open-by-Handle method with external classes, a substitute string is assembled from the key columns of the table.
From Backend, you could run the queries as per database type. Use respective for Postgres, Oracle and other databases.
If you want to know the class mapping to the database table. Open that particular class definition in the designer studio. Click "Test Connection", a popup window will appear. In the popup screen, you could see the respective database mapping, use this table name in the SQL query for retrieving data.
Report Definitions could also be used to get the data from a table or multiple tables based on join conditions configured.A report definition rule defines a report definition report. This rule generates an SQL query that retrieves and sorts information from the PegaRULES database, an external database, or an Elasticsearch index, and generates HTML that displays the results in a variety of formats. You have a range of options for interacting with the results, depending on the settings on the Report Viewer tab.