PureEngage On-Premises

Discussion Thread View
Expand all | Collapse all

Best way to Purge ICON DB

  • 1.  Best way to Purge ICON DB

    Posted 10-30-2019 07:01
    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
    ------------------------------


  • 2.  RE: Best way to Purge ICON DB

    GCAP Member
    Posted 10-31-2019 18:33
    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
    ------------------------------



  • 3.  RE: Best way to Purge ICON DB

    Posted 11-01-2019 12:39
    Edited by Todd McCall 11-01-2019 13:35
      |   view attached
    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
    ------------------------------

    Attachment(s)

    txt
    G_LOG_CLEANUP.sql.txt   5K 1 version


  • 4.  RE: Best way to Purge ICON DB

    Posted 11-01-2019 13:50
      |   view attached
    Had to edit the perl file... here it is.

    ------------------------------
    Todd McCall
    Bank of America
    ------------------------------

    Attachment(s)

    txt
    G_LOG_CLEANUP.pl.txt   2K 1 version


  • 5.  RE: Best way to Purge ICON DB

    Posted 11-02-2019 18:20
    Thanks Todd!

    ------------------------------
    Michael James Raquel
    Acquire BPO
    ------------------------------



  • 6.  RE: Best way to Purge ICON DB

    Posted 11-02-2019 18:25
    Thanks Jason. Can you share how I can configure that?

    As per checking,

    ICON MM DB Size

    Table Size

    The Top 5 tables are consuming so much space.

    Regards,

    Michael


    ------------------------------
    Michael James Raquel
    Acquire BPO
    ------------------------------



  • 7.  RE: Best way to Purge ICON DB

    GCAP Member
    Posted 11-03-2019 17:32
    Hi  Michael,

    • Assuming that your using GIM as your final reporting layer, you can exclude ICON Storage as per the Controlling IDB Storage section

    here
    These wont change what is currently stored, but if you've currently got adata-userdata-history set to all then it shoudl save you quite a bit of storage.

    ------------------------------
    Jason McLennan
    Commonwealth Bank of Australia
    ------------------------------



  • 8.  RE: Best way to Purge ICON DB

    Posted 11-04-2019 01:46
    Thanks Jason! Great help!

    ------------------------------
    Michael James Raquel
    Acquire BPO
    ------------------------------