9 Replies Latest reply: Jun 4, 2013 1:10 PM by Orlando De Abreu RSS

    INTEGRATION_STAGING purging

    892213
      Hi Experts,

      Please help, how to purge INTEGRATION_STAGING table of SIM.

      Thanks
        • 1. Re: INTEGRATION_STAGING purging
          Yaasheen-Oracle
          SIM does not have a purge mechanism for integration_staging table. It has to be manually removed if its not required for business

          Thanks
          Yaasheen
          • 2. Re: INTEGRATION_STAGING purging
            892213
            Hi Yasheen,

            Thanks for your reply,

            Is there any criteria/filter/depdendecy for deleting records from this table.

            Regards
            • 3. Re: INTEGRATION_STAGING purging
              892213
              Hi Yasheen,

              Is there any reference in SIM document where I can find that SIM don't have any purging mechanism for this table.

              Regards
              • 4. Re: INTEGRATION_STAGING purging
                892213
                Hi,

                I can see in
                Implementation Guide – Volume 2 – Integration Information
                Release 13.1

                Subject: "Database Considerations"

                "This must be coordinated to run after the daily purge batch script that removes already processed or deleted messages from the table."

                Clearly indicate that there is purge batch script, for purging data from this table. But problem is, not able to find out the exact pruge script which is used to purge this table

                Please advice
                • 5. Re: INTEGRATION_STAGING purging
                  praveenps
                  Hi,

                  The same is available only in 13.2. Till 13.1 it used to be implemeted by SI. If you need you can download 13.2 patch and have a look how it is implemented.

                  Regards,
                  PPS
                  • 6. Re: INTEGRATION_STAGING purging
                    892213
                    Hi,

                    Thanks for the update,

                    Should I download complete upgrade patch 13.2 or just any hotfix available, please suggest.

                    Regards
                    • 7. Re: INTEGRATION_STAGING purging
                      Orlando De Abreu
                      Hi,

                      SIM has a purge that runs from the following path:

                      "$ORACLE_HOME/j2ee/sim-oc4j-instance/sim-home/bin/PurgeIntegrationStaging.sh"

                      You can also run it manually through in the package "PURGE_DATA" in the function "DELETE_INTEGRATION_STAGING". This takes all the records that meet the following cursor:

                      select id
                      from integration_staging
                      where (processed = 'Y' or deleted = 'Y')
                      and update_time <= I_date-L_config_value;

                      where "L_config_value" is configured in the 'rk_config "under the config_value" DAYS_TO_HOLD_COMPLETED_STAGING_RECORDS" and "I_DATE" is the current date.

                      I hope has been helpful.
                      • 8. Re: INTEGRATION_STAGING purging
                        900483
                        Hi,

                        Thanks for replying.

                        I checked in rk_config table that DAYS_TO_HOLD_COMPLETED_STAGING_RECORDS is holding the value as 3. According to the below query it fetches 1 million data as of today in PROD. So 1 million records should be purged by “DELETE_INTEGRATION_STAGING” function.

                        Could you please advice why batch is not purging data,

                        select id from integration_staging where (processed = 'Y' or deleted = 'Y') and update_time <= sysdate-3; 

                        Thanks.
                        • 9. Re: INTEGRATION_STAGING purging
                          Orlando De Abreu
                          Hi,

                          When executing "DELETE_INTEGRATION_STAGING" You get an error? Deleting records is tiered according to the value of the variable "I_limit_size", which by default is set to 1000. This means that for every thousand records, deletes and does commit.

                          Could you provide output of function execution?

                          Another way to test it, would run in a test or development environment, incorporating trace function to know how many records have been erased.

                          Best Regards.