Discussion
Pegasystems Inc.
US
Last activity: 23 Oct 2022 18:20 EDT
Ask the Expert - Pega Database with Jie Long
Join @LONGJ in her 2nd Ask the Expert with us on Database starting October 10th through October 21st!
Make sure to Follow and Favorite for updates!
This session will open to questions on the 10th of October!
Meet your Expert, Jie:
Jie joined Pega in 2011 and has focused on Pega platform managed database engine, mostly in data model design, performed database APIs, High Available database architecture and configurations, tiered data storage solutions, Pega cloud database architecture, configurations and best practice. Today she’s working on next generation product design, mainly performed object storage and retrieve APIs as well as providing assistance to our customer's success.
Message from Your Expert:
I am pleased to address all of your questions on the Pega platform database. Through the session, I will be answering your questions on platform database, best practices in both cloud/on-prem environments. We look forward to hear from you all. We welcome any feedback from your experience with Pega platform database. This will add value to serve the unique needs of our users.
Ask the Expert Rules
- Follow the Pega Support Center's Rules of Engagement
- This is not a Live Chat - @LONGJ will reply to your questions over the course of the 2-week session
- Questions should be clearly and succinctly expressed
- Questions should be of interest to many others in the audience
- Have fun!
Updated: 19 Oct 2022 7:29 EDT
BUPA
AU
Hello All,
In our Pega Platform, the DB operation on pr_sys_serviceregistry table is running very slow. This table has low storage. This is causing system instability because of failed heartbeats. Due to this, node goes to safemode and then recovers itself after a while. Any help on this?
I have checked the below articles, but the main issue with the DB operation on this table (Select, Insert).
https://docs-previous.pega.com/pega0125-alert-service-registry-heartbeat-failed
https://community.pega.com/node/2186551
https://docs-previous.pega.com/pega0126-alert-service-registry-entering-safe-mode
Pega Version: 8.5.2
Database: PostgreSQL
Pegasystems Inc.
US
@SwarnenduM Thanks for your questions. Please create a support ticket to check applicable fixes with upgrade. For ex, 8.6.5+ has fixes that address full table scan OOTB queries on this t able.
Updated: 19 Oct 2022 7:29 EDT
LTIMindtree
SA
Hi,
As per your input, Looks to me that you're using Pega Robotics If I'm not wrong.
1. Did you check how many records presented in the mentioned DB Table?
2. Inside the application, There'll be an option to alter the time interval for Service Registry (if you're using the Robotics)
3. It's advised to create backup table in DB and push those high volume of Records to backup Table and the active one will get freed.
4. Otherwise, You can try increase the DB Size and implement the Archival of Records stated in previous step.
5. Implementing the Archival Records in the DB Table needs to DBA help to prepare a script to perform the operation and make it as recurring job to free up the mentioned DB Table.
Note: You can try the above solution and mark the inputs as accepted solution, post testing the results.
Updated: 19 Oct 2022 7:29 EDT
BUPA
AU
@Kishore Sanagapalli - Hello Sir, Thank you for your reply. We are not using Pega Robotics. Pega Platform is using this table to check the node health.
Updated: 19 Oct 2022 7:29 EDT
Proximus
BE
Dear,
We're running several PEGA 8.6.x applications (Websphere 9 / Oracle 19c) and we've got request to move our DBs to Oracle Transparent Application Continuity.
I'm not finding much info, so the question is whether this is supported by PEGA 8.
Thank you
Regards
Anthony
Updated: 19 Oct 2022 7:29 EDT
Pegasystems Inc.
US
@Anthony_Gourtay Thanks for your question.
Pega platform does not have explicit support or certify vendor specific feature like Oracle TAF. As long as the retry and DR mechanism is transparent to Pega application, this feature may work with Pega.
The customer may need to do extensive testing to see there is no network related performance/SLA issue.
Updated: 19 Oct 2022 7:29 EDT
Pegasystems Inc.
GB
Application Continuity (AC) is a feature available with the Oracle Real Application Clusters (RAC), Oracle RAC One Node and Oracle Active Data Guard options that masks outages from end users and applications by recovering the in-flight database sessions following recoverable outages.
I went through the Pega Support Platform Guide and on Page 14 I found this:
Pega Platform has been successfully deployed in Oracle RAC (Real Application Cluster) configurations, and Pega supports this deployment mode. Future product plans may broaden RAC support to leverage the Oracle Clusterware High Availability API and to publish scalability benchmark data.
Please let us know if that answers your question.
Updated: 19 Oct 2022 7:29 EDT
Proximus
BE
Dear,
Thank you for the reply.
If my understanding is correct RAC & TAC are not the same.
I think that by default PEGA has auto commit enabled.
We started with PEGA6 and these applications are now running in PEGA 8.6(+) and we always used Oracle XA driver.
This combination prevents from using any replay driver.
Is it correct understanding that for the moment we cannot set up full transparent application continuity?
Thank you
Regards
Anthony
Pegasystems Inc.
US
@Anthony_Gourtay Thanks for providing additional context. Your understanding is correct. So you don't get replay (silently disabled) on global transactions when XA driver is used; you only get replay on local transactions.
Updated: 19 Oct 2022 7:29 EDT
Not Displayed
US
Hi :-)
I have a couple of questions for you.
1) We are on Pega Cloud. How do we maintain the database when necessary? I have been faced with multiple issues that I have had to correct using a connect SQL rule and activity. Each time I have to do that, there is an entry in the log indicating that it should not be done this way, however, I am unaware of any mechanism my which I may alter table structures (for example, I was just recently forced to change the size of a column - I ended up having to drop the table and recreating it with a connect sql and activity.
2) the "ootb table naming" mechanism leaves MUCH to be desired. Typically, the class name is truncated and a random sequence of characters is added. It would be preferable to be able to specify table names, but even a way to update the table names (coming back to the initial question) would be acceptable. I'm guessing this is an enhancement request.
thank you!
scott
Updated: 19 Oct 2022 7:29 EDT
Pegasystems Inc.
US
@ScottW85 Thanks for you question.
1. On Pega cloud, you can navigate to schema tool to view table, column, index definitions and increase text column sizes, add index, etc. Please refer to the following link about this feature. It's available since 7.4 https://docs-previous.pega.com/system-administration/87/schema-tools
2. The name convention you see is to ensure DB objects name uniqueness and not overflow the vendor's length limit, required by vendor. Pega platform resolves the class and table mapping automatically, this is transparent to Pega application. May I know your use case/reason of need modifying the table names?
Not Displayed
US
Hi Jie, :-)
Thank you so much! I have not previously been on a Pega Cloud project, and simply was not aware of this, and did not find this information anywhere on the pega site... probably just unable to find it due to search terms. This is immensely helpful, and I wish that not only I, but also, my admin (and team), had been aware of this. life would have been much better. lol. I was about to open an SR due to errors and problems with adding properties to Data Types and the using the Optimize for Reporting... which is not working since we upgraded to Pega 8.7.3. i may go ahead and do that because it has caused us days and hours of problems... but hopefully this tool will allow me to at least fix the problems preventing me from deploying todays changes to staging. :-)
Hi Jie, :-)
Thank you so much! I have not previously been on a Pega Cloud project, and simply was not aware of this, and did not find this information anywhere on the pega site... probably just unable to find it due to search terms. This is immensely helpful, and I wish that not only I, but also, my admin (and team), had been aware of this. life would have been much better. lol. I was about to open an SR due to errors and problems with adding properties to Data Types and the using the Optimize for Reporting... which is not working since we upgraded to Pega 8.7.3. i may go ahead and do that because it has caused us days and hours of problems... but hopefully this tool will allow me to at least fix the problems preventing me from deploying todays changes to staging. :-)
RE the naming convention, many vendors these days (Oracle, Postgresql, are two I've been using) allow up to 128 characters for table names and column names now. So, truncation of the table names where they are impossible to distinguish from any others except for that 5 or 6 characters is incredibly aggravating, frustrating and time consuming. it would be much better if you guys just chop off the leading class names which are identical, and just create the table name out of the last 1 or 2 levels. Everywhere else, I have created my own DB scripts for table creation and I prefer to have that level of control. However, knowing that we have access to the new Schema tool on the Cloud, i can do what I need to do. I was just over a barrel and we don't have the time to wait for cloud support cases to have table changes done as we have 2 week sprints and move very quickly.
the Schema Tool at least solves my immediate issues. :-) thanks very much!
scott
Updated: 19 Oct 2022 7:29 EDT
Knowledge Expert
FR
@MarissaRogers Documentation says that I can increase column length:
https://docs-previous.pega.com/system-administration/87/increasing-column-length
In fact - once column exposed - I cannot do that anymore. Property/column just get disabled in Schema tools.
Updated: 19 Oct 2022 7:29 EDT
Pegasystems Inc.
US
Is this system a Pega cloud environment? You may need to refresh the schema tool tab to see the new exposed columns. The 'edit' should be shown once you click that column on the column detail section. To use this feature, you need PegaRULES:DatabaseAdministrator role.
Updated: 21 Oct 2022 7:21 EDT
Knowledge Expert
FR
@LONGJ this is on premise environment with PostgreSQL database. I have all roles. Unfortunately I don't have an access to Pega Cloud instance.
But even from code of database wizard (DBSchema_ListProperties) I can see that if pyIsColumn = true then row will be grayed out.
I see the same issue was raised on the forum:
https://support.pega.com/question/cannot-edit-database-field-database-table#comment-895636
Pegasystems Inc.
US
Only text field is supported for increasing column size. The privilege required to change column size is covered by PegaRULES:DatabaseAdmininstrator for PegaDATA/PegaRULES databases. Please check whether the column is a text field.
Updated: 19 Oct 2022 7:29 EDT
Rabobank
NL
Hi,
We are having a use case for which we have to migrate little over 5 million records from an on prem system (with Oracle as DB) to Pega cloud implementation and this should be done in multiple trenches. The on prem application can deliver the data in files that can be consumed on Pega cloud. Can you suggest if there could be a better way to support this migration than doing it through files?
Also we are thinking to store this data in CustomerData schema than PegaData. Appreciate your thoughts on this. Thanks.
Updated: 19 Oct 2022 14:25 EDT
Pegasystems Inc.
US
@JatinR85 Pega cloud supports BIX import/export, file listener, AWS SCT and DMS migration. Please create a cloud case to involve migration team to help evaluate the options based on the requirements including maintenance window, etc.
Pega data type by default is created in CustomerDATA and the mapped tables do not have the blob (pzpvstream) column by default, this behavior can be overridden to include a blob. So if your data requires to store complex structure embedded properties/page list, you may consider blob. Blob column has non-trivial performance impact.
Pegasystems Inc.
US
Thank you to @LONGJ for being our expert and thank you to everyone who asked her questions about Pega Database!
Stay tuned for our next session in November with @carsj on Pega Knowledge!