CustomerData DB Instance/Schema

General
, , , ,
1

Hello everyone,

I would like to reopen the discussion from the below post, as I am still having some doubts.

I understand since the introduction of CustomerData DB instance, Pega recommends to use this instance (over PegaDATA) when creating a fully exposed tables (non-pega tables), etc. However, I would like to ask the following questions…

[by db instance I mean the db instance rule at the app level, and by db schema I mean the schema name at the database level]

  1. Why Pega doesnt ship CustomerData db instance by linking it to a separate db schema? I see it is inheriting from PegaDATA db instance, hence being linked to pegadata db schema.

  2. When creating dedicated tables in our applications, it is recommended to keep them separate from the db schema where Pega OOTB tables are linked, hence we should create a new db instance (e.g. AppData) and link it to a separate dedicated db schema (e.g. App_Schema). Question is, what is now the best practice for the CustomerData db instance? Should it be linked to the db schema App_Schema or linked to another separate schema (e.g. AppCustomerData_Schema)?

  3. In case of cross border applications (on same infrastructure), where each country has its own dedicated db schemas (e.g. AppCountryA_Schema, AppCountryB_Schema, etc.), how the CustomerData db instance can be configured? Can we think of multiple CustomerData db instances such as CustomerDataCountryA, CustomerDataCountryB, etc. where each can be linked to their own dedicated schemas? If so, then when creating data types and sourcing them with the new db instances (CustomerDataCountryA, CustomerDataCountryB), will it mimic the same functionality as when selecting the OOTB CustomerData db instance, like creating a non-pega table?

Thanks you.

2

@OZKANMECH

Pega Platform uses a split-schema configuration where the rules and data objects reside on separate schemas. By default, Pega Platform includes two database data instances: PegaRULES, for rules that describe the behavior of your system, and PegaDATA, for case data. The CustomerData database instance was introduced to allow for a separate work schema that is not part of PegaDATA. However, by default, CustomerData points to PegaDATA. This is because it’s designed to be flexible and can be configured to point to a separate schema if needed, depending on the specific requirements of your application.

:warning: This is a GenAI-powered tool. All generated answers require validation against the provided references.

Planning a Pega Platform installation > Understanding Pega Platform split-sche

Creating database data instances

Database data instances

CustomerData (non-blob) vs PegaDATA (blob)

customerdata.schema.name how does it work - what does it do

3

@MarijeSchillern

Thanks a lot for your response. I had already gone through those pages you shared and I was aware of the main purpose of CustomerData db instance.

However, I would still like to get some answers for my points (2) and (3) above.

Point (2) - what is the best practice recommended by Pega (consider there is no any specific/complex requirements by the client - just standard project).

Point (3) - is there a solution for this particular scenario? Can we have separate CustomerData db instances for each region and when used during sourcing a data type it can mimic the functionality of OOTB CustomerData db instance (e.g. creating a non Pega table - no BLOB, and pxs/pys columns)?

Ozkan

4

@OZKANMECH this appears to be a duplicate question to Database Table Creation of History Classes

below is the observation made by our SME:

"Pega cloud adds the customerdata schema initially. It intends to differentiate the existing PegaDATA to store custom data types which by default has no Pega default columns like pzInsKey/pxObjClass/pzPvstream.

It’s a logical differentiation between PegaDATA and CustomerData due to that reason.

Using inherit will share the same connection pool as the PegaDATA.

As to additional custom schema to host custom data tables, platform deployment support split schema, without touching custom tables. But there are other concerns: share/optimize connection pool cross schemas, join cross database is not supported, default schema on Pega cloud already configures to point to data schema too. ".

@JohnPeterson may be able to provide more insight if necessary.

5

@MarijeSchillern thanks a lot for your response.

But, let me rephrase my statements and ask my questions in different way…

How would you configure an application for your client in Pega Cloud, which is on a single infrastructure and it is used cross-border between two (or more) countries. Ideally we should have the following structure (please correct me if otherwise):

(1) PegaData database instance (App level) linked to pegadata database schema (DB level) - the purpose of this configuration is to keep Pega OOTB tables separate, which can be greatly beneficial during Pega upgrades (mainly due to the lower size)

(2) CountryA database instance (App level) linked to countryA database schema (DB level) - the purpose of this configuration is to keep client and case specific data separate. These tables will include pega columns and pzPvstream column, for instance dedicated/concrete tables (case type tables), index tables, etc. The CountryA database instance can be configured to inherit from PegaDATA (so same JDBC connection pool) but on the server level can be set to be linked to the dedicated database schema, in my example countryA database schema.

CountryB configuration would be exactly the same as CountryA above with its own dedicated database instance and database schema.

And, if I wanted to create a concrete class or a data type I can easily link it to the database schema I want from the application level - no need to go to the backend db level.

(3) Now, if we also wanted to introduce the CustomerDATA entity/concept (for the already known purpose) with exactly the same configuration as above, like the CountryA and CountryB, but with the difference that in these schemas, data tables like refdata to be created, for instance fully exposed tables with no any pega columns or pzPvstream column. Does Pega support this configuration? Like I already explained in my previous post, can I create and configure separate database instances and database schemas for each country (CustomerDATACountryA and CustomerDATACountryB) and have the OOTB functionality working when creating a Data Type and sourcing it with CustomerDATACountryA or CustomerDATACountryB so non-pega fully exposed tables are created automatically?

In short, I am looking for the following answers:

  1. For (2) above, we have already made a good progress and achieved our goal which was “do everything from the App level” (when creating classes and linking them to the correct schema). However, we managed that for the main classes but not for their History classes. But, I have a different thread for that issue… https://support.pega.com/question/database-table-creation-history-classes

  2. For (3) above, question are highlighted above.

Hope my description this time is more specific and can trigger more specific answers.

Ozkan

6

When creating a dedicated table for a new Class Definition, the target Data-Admin-DB-Name can be selected via the following thought process:

If the Class Definition inherits from Rule- or contains metadata (e.g. Rule-Declare-Index) about another Class Definition that inherits from Rule-, then it should be mapped to PegaRULES.

Otherwise, if the Class Definition is for driving application behavior or for leveraging a Pega feature then it should be mapped to PegaDATA. Some indicators that this is the case is that the class inherits from a Pega out of the box Class Definition which is mapped to PegaDATA, or is considered an ‘internal’ table.

But if the Class Definition is specifically for hosting external data, then CustomerData may be used, or a custom external database may be used. This is recommended to decouple Pega application data and metadata from client data that isn’t specific to the functioning of the application, or otherwise required by Infinity.

Pega ships some example tables and data in the CustomerData schema, and by default inherits the PegaDATA connection and schema initially, but users are allowed to point it to another schema or a different database connection. Keep in mind, however, that Pega does not support query joins across database connections.

The CustomerData Data-Admin-DB-Name, and all other external databases are not touched during patches, updates, or upgrades whereas the PegaDATA schema DDL can be modified, and the PegaRULES schema DDL and content are copied and modified.

A general rule of thumb is that if the table is identified as ‘Internal’ (pxObjClass and pzInsKey are populated and exposed) then map to PegaDATA otherwise map to CustomerData, but there are exceptions to this depending on if the application uses the Class Defintion to drive application behavior (use PegaDATA) or if Pega simply processes data which may be generated or leveraged elsewhere (recommended to use CustomerData or other external database).

As an aside tangent, I do not believe Pega supports regional schemas for cases - this is due to how the assignment, history, link, and index tables are tightly associated with the case tables themselves - but storing data, like PII, in dedicated databases per region or country makes senses to me.

7

@JohnPeterson this is the answer I was really looking for. So, thanks for your detailed response and explanation.

I think your description is really good and explains very well how we should drive the application segregation (coupling/decoupling), from the perspective of application/case behaviour and customer data. This is what I was also trying to explain above but with different words - if a table is a Pega table (with pzInsKey/pxObjClass/pzPvstream) then it should be part of the case behaviour and if not, for example it just stores some refdata where the data got generated elsewhere and we just want to refer to that (not driving the case behaviour though), then use CustomerData.

On an infrastructure with a single tenant (one Country only), your explanation admittedly leaves no shadow of doubt upon the use of PegaDATA and CustomerData.

However, on an infrastructure with multitenant (CountryA, CountryB, etc.), from your last paragraph I understand that segregating the tenants into different database schemas is not the best practice? Not sure what exactly you meant by “I do not believe Pega supports regional schemas for cases” because it is very much possible to configure your application to store all the data that is driving your application/case behaviour into a different schema. For example, your case dedicated Work class, in CountryA, and any other related dedicated “internal” tables for that case can be easily mapped to e.g. CountryA_Schema (database level) and the population of those tables can be configured through the application level with e.g. CountryA database instance. So, the architecture of your application would look like this…

PegaDATA => All OOTB tables (assignment, link, attachment, etc.)

CountryA => All case related data that is driving the case behaviour, otherwise your “internal” dedicated tables related to CountryA

CountryB => All case related data that is driving the case behaviour, otherwise your “internal” dedicated tables related to CountryB

So, all are driving the case behaviour but the tables are segregated between Pega OOTB tables and regional Case dedicated tables.

Ozkan

8

@MarijeSchillern John’s answer perfectly covers the 1st part of my question, but before I accept that as a solution I was thinking to keep it open until I get some feedback for the 2nd part as well.

I thought I already outlined the 2nd part of my question in my previous comment, in the third paragraph where I describe the multitenant scenario. But please allow me to reiterate my question.

Is it not best practice to segregate the tenants into different database schemas (in the way as my example in my previous comment with CountryA and CountryB)?

If there is no an absolute answer for that, then is there any drawback for following this design approach**?**

Ozkan

@JohnPeterson

9

@OZKANMECH

I recommend storing cases in PegaDATA because a case is an application construct and also because of the expected coupling of the related objects (links, indexes, history, assignments, attachments, etc.) and also other OOTB functionality expectations (e.g. reports). Also, I’m not aware of a single Pega deployment supporting the ability to save cases into country specific databases, in this situation, I believe you’d want multiple Pega deployments.

CustomerData or other ‘external’ databases is really only to allow the Application to obtain information that is generated or leveraged elsewhere, and not for information needed by the application to function (e.g. If there is no case, there is nothing to do).

10

@OZKANMECH

I’m still trying to undersand the purpose of your question and its explanation. But let me try to add my 2 cents.

Working with many aplications and different flavours of Pega, this is what I can think of in a very highlevel pov.

PegaRULES → Essentially should store all Rules schema table, usually no tables are from Customers, all are Pega created tables or upgraded tables.

PegaDATA → Here is where lot of times I see confusion among many Pega customers, Often PegaDATA and CustomerDATA is same. This could be various reasons, Appps might be still OnPrem, Apps might have had Single schema i.e., upgraded to latest split schema from a single schema, or in general wanted to keep less infrastructure rather than maintaining bigger server footprint across their infrastructure.

CustomerDATA → This concept of having Customer own data residing in its dedicated schema instaed of mixing with OOTB tables Operator tables or Assisgnment tables etc.,

Usually this kind of JDBC connection pooling found in either latest Fresh installs or PegaCLOUD configurations.

There are quite few usecases might derive this for this architecture i.e., To make High availability possible with out touching the environments during the Patch updates and Upgardes, if the data processed by Pega is needed to be supplied or enable access to downstream systems, or to improve performance etc.,

While coming to Country Specific schema’s, something practially not suggestable or possible, unless there are lega and compliance requirements, as an instance Republic of China mandates all the Software Stack should be hosted with in the China data centers, so there is a slight possiblities in such edge cases, but in general, that’s not a suggested case as it defaets the purpose Pega Layer Case Approach.

Hope this explanation helps you to clarify some of your question(s).

11

@OZKANMECH if John answered your question would you be able to mark his reply with 'Accept Solution?

If you need further info, could you please outline the main points which still need to be clarified?

12

@JohnPeterson

Thanks for your feedback again!

Regarding the Pega deployment you are right, however that’s not my scenario as my question was related to different database schemas (not databases). I should have probably been more specific stating that the database schemas are on the same database. Apologies for the confusion.

As for the CustomerDATA and ‘external’ database, it was already pretty clear with you previous comments, but thanks for emphasizing it again.

Ozkan