What is the best practice for updating large volume of Customer data (referencing purpose) in a 24*7 production environment
Hello,
is there any OOTB activity to update any property (for instance, Table Name) of Database Table instances?
We are interested in change table mapped to an other backup table (same structure) during update process of main table (this update process becomes main table blank for a few seconds).
So our approach to let end users to use data of main table during update process is to copy data in main table to a work table, update instance of Database Table that maps table to referenced class, using work table, udpate data in new table and when process is finished, update again Database Table instance to map to main table.
Any help to agree our need will be appreciated!
Regards,
David Encinas
M2C Consulting
Pegasystems
IN
Hi,
A question: How does the data updated in the backup table sync back to the main table? What about cases that are in progress at that time or created new during these few seconds?
-Rajiv
M2C
ES
Hi Rajiv,
first of all, thanks for your quick reply.
Our approach is to use a work table during update of main table, so we are thinking on switch table name of Database Table instance, when we switch main table to work table, both tables have similar data, and when we switch work table to main table after update process, new data updated in main table is published for users.
We don´t need to sync back data in work table to main table after the process because this table is only for read purposes, so it won´t change while users interact with it.
So our goal is to avoid time with blank data in main table during update process.
I hope it will give you a correct sense of our need.
Regards,
David
Pegasystems
IN
Hi David,
So if I understand correctly, you want to use the backup table to run reports and fetch data when the main table is unavailable during updates.
Changing the Data-Admin-DB-Table instance so that the class is mapped to the right backup table for even a small time is a costly operation. This is because we maintain the table information in memory so that we can do the property to column mapping correctly including property types to column types. Also, this change when updated on one node would need to be pulsed (using SystemPulse) to all other nodes as well and that delay could be up to 60 seconds. So depending on how long your update on main table will take, it may not be a good idea to do this at all.
That said, if you still want to do this, you should write an activity which can do an obj-open, modify the table name (property-set) and do an obj-save to change the value. I don't think we have any direct API available.
-Rajiv
M2C
ES
Hi Rajiv,
this is exactly our need. But as you said, I don´t know yet if my approach is the correct one, I am also studying to agree my need by sql script.
Thanks for your help, I appreciate it!
Regards,
David
Pegasystems Inc.
US
David,
I'm going to agree with Rajiv that your approach is questionable. What sort of update are you planning on doing to your main table that you'd make it unavailable during a time frame where you are trying to do work? I'm going to assume you are on Pega 7. If you have a split schema, I believe (and this is not my area of expertise, so I'm sure I'm overlooking something. Hopefully someone more knowledgeable will chime in) you can copy your data schema, make your changes and then point your system to the new schema. That would allow you to run uninterrupted and not require creating a custom activity to change the database on the fly.
Alternately, if there is specific data you'll want your read only users to have access to, you may just want to use a Rule-Declare-Index to mirror it in an index table that you could work directly off of while your other table is unavailable.
Thanks,
Mike
M2C
ES
Hi Mike,
let me explain you a little deeper our scenario:
We have a 4 million records table of customers of our final client, this table is updated daily. This table is used by agents of final users to run strategies and extract best offers for customers.
During daily update, we truncate customers table and insert complete set of customers, and while truncate is done until insert is finished, table is blank. It usually happens at night during non-use window of users, as we currently don´t have 24x7 service, but we are interested in move to 24x7 service so we need customers table available during update..
As we always have backup table of customers during update to recover it quickly it update fails, we are thinking about using this backup table be mapped during update of customers table, and map again customers table when update is finished.
As you said, I'm not sure if my approach (swich table name on table instance) to fulfill my need is the best one, I'm only checking available approaches to choose the one that meets better our need.
I hope it will give you better vision about what we need to do.
On the other hand, I'm not familiarized with options you have described, I'm not going to study them and let you know.
Thanks,
David
Pegasystems Inc.
US
David,
Ah! So you're not talking about a table full of work objects, that simplifies things some. How are you getting data into the Pega Platform for use? Reports? Obj-browse? Direct SQL queries from a Java step? All of the above? Depending on how you are accessing the data, you probably have a few options. Also, changing table names (perhaps via a Java step or something else outside the guardrails... but what you are doing is fairly custom) should be doable.
How do you generate the backup table today? Is that done via scripts in the DB? Couldn't you make the second table, insert directly into it, and then switch the table names once it has completed all from an SQL script? That probably has the fewest moving parts since the Pega Platform would always point to the same customer table. Once you've made the "new" table become the active customer table, you could drop the old one, or truncate it to prepare for the following go 'round. And if something goes wrong with the creation of the table/inserts, you just don't do the rename and work off of the current customer table until things are sorted.
Thanks,
Mike
M2C
ES
Hi Mike,
We mainly use this table for Obj-browse and reports.
As you said, we generate backup table by SQL scripts, and you are right about renaming tables is other option to study. The third one I'm currently studying is to change TRUNCATE&INSERT current mode for DELETE&INSERT&COMMIT, but in this case I'm afraid that temp data until commit is done is very large as we are managing 4 million records table with 150 columns.
I will check all options and decide with is the correct one (I'm almost sure that renaming tables will be the best one).
Thanks for your effort to help me, I appreciate it a lot!
Best Regards,
David
JPMC
US
How about having 2 classes, one clone of other but mapped to two different tables. and switch the class name for the rules performing obj-browse instead of switching table name, I believe reports wont be problem for this brief period of time even if they keep working on existing live table?
M2C
ES
Hello Dhirendra,
first of all, thanks for your reply, but unfortunately I think that your proposed solution is not suitable for my environment as current class mapped to Customer table is used in many activities,
Best Regards,
David
Pegasystems Inc.
JP
I suggest you to change the title of this discussion something like below, so it may reflect your intentions correctly and attract more suggestions from other participants.
"What is the best practice for updating large volume of Customer data (referencing purpose) in a 24*7 production environment "
M2C
ES
Hi Chunzhi,
I have updated title with your proposal as it is more correct with my primal need, as change table name of "Database Table" instance is a way to address it, but I'm open to new ways to achieve it.
Best Regards,
David