Michael,
I had the same problem a few years back and my solution was to set up a loop, deleting a set number of the oldest records in a table, then checking the time to see how long I have been looping. The procedure stops before the next delete loop if the time has been exceeded.
In the attached stored procedure file, you can set the number of records to delete in each loop in the variable 'recordLimit'. I have several 'dbms_output' lines to capture progress and results.
When calling this SP, it will look for
two input values, the number of records to keep in the table (using a date-time field in the table) and the duration of time the SP can keep looping before it ends, regardless of whether it finished or not (matching the number of days to keep in the table).
Our scheduling system ran on Perl, and its such a convenient tool, i included it as well. This Perl script launches the SP, then emails the results of the procedure. Its really handy, if you can use it.
After you edit these files, put down your own name as the author along with the date, so that you can have all the glory of a well executed query, and you can shoulder all the blame if it crashes the database. :-)
Happy coding!
------------------------------
Todd McCall
Bank of America
------------------------------
Original Message:
Sent: 10-31-2019 18:33
From: Jason McLennan
Subject: Best way to Purge ICON DB
That's a fair bit of data. We're not using MM, only voice, and our purge completes in about 30 seconds generally. We do also have the advantage of using oracle partitioned, so it's just Truncs the table by partition.
Have you checked G_LOG_MESSAGES tables for clues on where it may be slowed up?
Have you also reviewed your configured population of userdata history and events to ensure your only capturing the information required as opposed to all? Just thinking of solutions from the front end perspective.
------------------------------
Jason McLennan
Commonwealth Bank of Australia
Original Message:
Sent: 10-30-2019 07:00
From: Michael James Raquel
Subject: Best way to Purge ICON DB
Hi Everyone,
I am trying to purge my ICON_MM DB.
DBMS: PostgreSQL
Purge Script: SELECT gsysPurge81 (30,0);
- based on documentation URL: Documentation:ICON:UG:PurgeStoredProcedures:8.1.5 - Genesys Documentation
I tried executing this script but it is taking so much time then it will disconnect. What is the best approach to run this purge script?
I tried retaining 47 days since I have 48 days of interaction (multimedia data).
Current Size of the database is 838 GB (see below)
Thanks in advance for those who will spend time to reply.
#Reporting/Analytics
------------------------------
Michael James Raquel
Acquire BPO
------------------------------