Question

We have over 300,000 records in our Process Log.  We'd like to delete records that are not Running or Error that are over one month old.  They are supposed to be archived, but there are records from months ago that have not been archived. When I select a record, there is no option to delete it or archive it. How do I delete or archive records from the Process Log?

Like

4 comments

Dear Janine, 

Process log gradually gets cleared by the archivation process. We don't recommend to delete data from Process Log yourself. However, if you really need to delete that data we recommend to send a request to the support@bpmonline.com if your instance is in cloud as it requires very resource heavy sql operations that would not be possible to track with sql executor. 
If your instance is on-site you can take the instruction below as an example to adapt it based on your needs: 
1) Register SQL procedures tsp_DeleteSysProcessLog.sql and tsp_DeleteSysProcessLogByStartDate.sql (procedures themselves below)
2) Execute the procedure.
Example: EXEC [dbo].[tsp_DeleteSysProcessLogByStartDate] 'Error', '2017-09-22', -1,
Where 'Error' is the status of processes,  '2017-09-22' by which start date to delete processes, '-1' how many processes to delete, -1 means all processes. 

Please note that the operation is very resource heavy and time consuming and we don't recommend doing that during business hours. 

Best regards, 
Dennis

 

IF NOT OBJECT_ID('[dbo].[tsp_DeleteSysProcessLogByStartDate]') IS NULL
BEGIN
    DROP PROCEDURE [dbo].[tsp_DeleteSysProcessLogByStartDate]
END
GO

CREATE PROCEDURE [dbo].[tsp_DeleteSysProcessLogByStartDate]
    @Status nvarchar(max),
    @StartDate date,
    @RowsCountToDelete int
AS
BEGIN

    SET NOCOUNT ON

    IF NOT OBJECT_ID('#SysProcessLogId') IS NULL
    BEGIN
        DROP TABLE #SysProcessLogId
    END
    
    CREATE TABLE #SysProcessLogId (Id uniqueidentifier)

    IF @RowsCountToDelete > -1
        INSERT INTO #SysProcessLogId ([Id])
        SELECT Top (@RowsCountToDelete)
            SysProcessLog.Id
        FROM SysProcessLog
        JOIN SysProcessStatus ON SysProcessStatus.Id = SysProcessLog.StatusId
        WHERE
            SysProcessLog.ParentId IS NULL AND
            SysProcessLog.StartDate < @StartDate AND
            SysProcessStatus.[Name] = @Status
    ELSE
        INSERT INTO #SysProcessLogId ([Id])
        SELECT
            SysProcessLog.Id
        FROM SysProcessLog
        JOIN SysProcessStatus ON SysProcessStatus.Id = SysProcessLog.StatusId
        WHERE
            SysProcessLog.ParentId IS NULL AND
            SysProcessLog.StartDate < @StartDate AND
            SysProcessStatus.[Name] = @Status

    EXEC [dbo].[tsp_DeleteSysProcessLog]

END
GO

CREATE OR REPLACE PROCEDURE "tsp_DeleteSysProcessLogById" (id VARCHAR2)
IS
    TYPE IdSet IS TABLE OF VARCHAR2(38);
    input_ids IdSet;
    i NUMBER;
BEGIN
    SELECT
        "Id"
        BULK COLLECT INTO input_ids
    FROM "SysProcessLog"
    WHERE "ParentId" = id;

    IF input_ids.COUNT() > 0 THEN
    FOR i IN input_ids.FIRST .. input_ids.LAST
      LOOP
        BEGIN
          "tsp_DeleteSysProcessLogById"(input_ids(i));
        END;
    END LOOP;
    END IF;

    DELETE FROM "SysProcessElementLog"
    WHERE "SysProcessId" = id;

    DELETE FROM "SysProcessLog"
    WHERE "Id" = id;
END;
/

CREATE OR REPLACE PROCEDURE "tsp_DeleteSysProcessLog" (inputRowsCountToDelete IN INT, status IN VARCHAR2)
    IS
    TYPE IdSet IS TABLE OF VARCHAR2(38);
    input_ids IdSet;
    i NUMBER;
    step INT := 1000;
    startDate TIMESTAMP;
    rowsCountToDelete INT := inputRowsCountToDelete;
    allRowsCount INT;
    rowsLeft INT;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Database: ' || USER);

    startDate := CURRENT_TIMESTAMP;
    IF (inputRowsCountToDelete <= 0) THEN
            SELECT COUNT("Id") INTO rowsCountToDelete FROM "SysProcessLog";
    END IF;

    SELECT
        "SysProcessLog"."Id"
        BULK COLLECT INTO input_ids
    FROM "SysProcessLog"
    WHERE
    "SysProcessLog"."ParentId" IS NULL
    AND "SysProcessLog"."StatusId" IN
        (SELECT
            "SysProcessStatus"."Id"
        FROM "SysProcessStatus"
        WHERE INSTR(status, "SysProcessStatus"."Value") > 0)
    AND NOT EXISTS(SELECT "SysProcessData"."Id" FROM "SysProcessData" WHERE "SysProcessData"."Id" = "SysProcessLog"."Id")
    AND ROWNUM <= rowsCountToDelete;

    allRowsCount := input_ids.COUNT();
    DBMS_OUTPUT.PUT_LINE('Number of records to process: ' || rowsCountToDelete);

    IF allRowsCount > 0 THEN
        rowsLeft := allRowsCount;
        FOR i IN input_ids.FIRST .. input_ids.LAST
            LOOP
                BEGIN
                    IF MOD(i, step) = 0 THEN
                        DBMS_OUTPUT.PUT_LINE('Step ready: ' || TO_CHAR(CURRENT_TIMESTAMP, 'DD-MM-YYYY HH24:MI:SS.FF2'));
                        DBMS_OUTPUT.PUT_LINE('Rows left: ' || rowsLeft);
                    END IF;
                    "tsp_DeleteSysProcessLogById"(input_ids(i));
                    rowsLeft := rowsLeft - 1;
                END;
            END LOOP;
        END IF;
    DBMS_OUTPUT.PUT_LINE('Number of records processed: ' || allRowsCount);
    DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Execution time: ' || TO_CHAR((CURRENT_TIMESTAMP - startDate), 'DD-MM-YYYY HH24:MI:SS.FF2'));
END;
/

--CALL "tsp_DeleteSysProcessLog"(10000, '2,4');

 

 

Thanks, I have reported it to Support since there are records even from April that haven't been archived.

I found Beesender tried to delete process log by a process to delete items from Process log (actual). Is that correct or is there any risk of doing that way? 

Van Ly,

Process log is connected to several other tables so there is a risk of not deleting all necessary records or possibly corrupting some necessary data.

Best regards,
Dennis  

Log in or register to comment
Question

Hi Community,

Any Idea, how can I retrieve not only the unique identifier of the deleted record but also the parent foreign key of the deleted record on delete signal?

My scenario is:

On deletion of a child record, I want to create a business process that will be triggered on delete signal. Then do the re computation of the total amount on parent record after the deletion of the child record. But the problem is on delete signal I can only retrieve unique identifier of the deleted record, I need also to retrieve the parent foreign key of the deleted record for me to re compute and update to total amount of the parent record.

Thanks,

Like

4 comments

Dear Fulgen,

If a process that starts on record deletion is started not in the background you can simply add "read data" element that will read all data needed from a record that is deleted. 

Best regards,
Angela

Angela Reyes,

Hi Angela,

Thanks for the reply.

Can you show me example how can i design this logic on business process.

Fulgen Ninofranco,

Just uncheck "Run following elements in the background"

checkbox (http://prntscr.com/o2rt5q) and read all data from the record with this Id. 

Thank you Angela, this solves my problem

Log in or register to comment
Question

Hi,

 

Is there a way to delete all of the data from any of the sections, say I want to delete all the accounts? I am looking for a way to delete all the records in the instance i.e. all the leads, accounts, contacts, any cases etc.

One way is to select all the records in particular section and then delete but when I do that, it brings out all the dependencies and therefore have to go into the records, or more dependencies to delete those records and it literally takes ages to delete them.

So I am looking for ways to refresh the instance, but not damaging or changing the packages and other settings in any fashion but just delete the records from the instance.

Regards,

AK

Like

2 comments

Hello! 

Unfortunately, for now the only option is deleting records by selecting them all, deleting and dealing with the dependencies. You also can delete records using SQL Executor, however you would need to manually deal with the dependencies as well in this case. 

Best regards, 
Dennis 

Alright! Thanks.

Log in or register to comment