Archiving issues

, ,
1

I’m working with webMethods 9.10 and we’re setting up an archiving process.

I keep running into an ORA- error in OPERATION_LOG table when archiving data, getting a unique constraint on WORKFLOW_ARCHIVE.IDX_RECENT_UUID. I’m using the Oracle Stored Procedures.

First thing I do is DELETE from the ARCHIVE schema then I want to INSERT new data into the ARCHIVE schema.

Steps:

  1. Update OPERATION_PARAMETER set PARAMETER_VALUE = ‘WORKFLOW_ARCHIVE’ where PARAMETER_CD in (‘PROCESS_SCHEMA’,‘ISCORE_SCHEMA’);

  2. Update OPERATION_PARAMETER set PARAMETER_VALUE = ‘DELETE’ where PARAMETER_CD = ‘PROCESS_ARCHIVE_ACTION’

  3. Run the 4 Stored Procedures (DOCUMENT_ARCHIVE, PROCESS_ARCHIVE, SERVER_ARCHIVE, SERVICE_ARCHIVE) with the following parameters:
    p_rentaindays=1, p_action=‘DELETE’, p_status=‘2,4,1024,32768’, p_batchsize=‘100’

  4. Update OPERATION_PARAMETER set PARAMETER_VALUE = ‘WORKFLOW_PROCESS_AUDIT’ where PARAMETER_CD in (‘PROCESS_SCHEMA’);

  5. Update OPERATION_PARAMETER set PARAMETER_VALUE = ‘WORKFLOW_AUDIT’ where PARAMETER_CD in (‘ISCORE_SCHEMA’);

  6. Update OPERATION_PARAMETER set PARAMETER_VALUE = ‘ARCHIVE’ where PARAMETER_CD = ‘PROCESS_ARCHIVE_ACTION’

  7. Run the 4 Stored Procedures (DOCUMENT_ARCHIVE, PROCESS_ARCHIVE, SERVER_ARCHIVE, SERVICE_ARCHIVE) with the following parameters:
    p_rentaindays=2, p_action=‘ARCHIVE’, p_status=‘2,4,1024,32768’, p_batchsize=‘100’

2

I should add that this is happening at step #7. If I then truncate WORKFLOW_PROCESS_AUDIT.PRA_PROCESS I can run the procedures and there are no ORA- errors.

3

Hi Patrick,

there are currently two questions coming up:

  1. What is the exact ORA-Error-Message ?
  2. Why is the parameter PROCESS_SCHEMA not set to Archive Schema the same way as it is set for ISCORE_SCHEMA in step 1?

Truncating any single tables in the webMethods schemas is not a good idea as this can lead to corruption of these schemas eventually forcing to recreate the complete schema.

Regards,
Holger

4

  1. ORA-00001 - unique constraint error on the UUID index in ARCHIVE schema, table PRA_PROCESS_RECENT and IDX_RECENT_UUID.

  2. My thinking was that when I’d first DELETE data from the ARCHIVE schema setting all schemas to the ARCHIVE schema (step #1). Then I would go and ARCHIVE off the data in the CORE and PROCESS schemas (steps #4 and 5).

5

I did just find a trigger in WMWORKFLOW_ARCHIVE called PRA_PROCESS_RECENT1 that looks like it will insert records if specific criteria exist, maybe that is what is causing the ORA-00001.

6

See line 130 in the xls file for the error, can probably save as a csv to view it easier.
wm_ORA_error.xls (21.6 KB)

7

So far I’ve managed to track it down to the process archive parameters. If I run the following w/ a ‘2’ for status it fails. If I run with ‘4’,'1024,‘32768’ everything is fine.

PROCESS_ARCHIVE.START_PROCESSARCHIVE(‘1’, null, ‘ARCHIVE’,‘2’,‘1000’);

8

Problem is I can’t see the exact sql that is being run, it’s being cut off…

INSERT INTO WMWORKFLOW_ARCHIVE.PRA_PROCESS_RECENT( MODELVERSION,PARENTINSTANCEID,FIRSTSTATUS,PARENTSTEPTYPE,HASPARENT,INSTANCEID,INSTANCEITERATION,CUSTOMID,A
UDITTIMESTAMP,FIRSTTIME,ATRESTTIMESTAMP,PROCESSKEY,STATUS )(SELECT MODELVERSION,PARENTINSTANCEID,FIRSTSTATUS,PARENTSTEPTYPE,HASPARENT,INSTANCEID,INSTANCEITERATION,CUSTOMID,AUDITTIMESTAMP,FIRSTTIME,ATRESTTIMESTAMP,PROCESSKEY,STATUS FROM WMWORKFLOW_PROCESS_AUDIT.PRA_PROCESS_RECENT WHERE INSTANCEID IN (SELECT DISTINCT INSTAN

9

Hi,

Try the following:

  • Recheck the parameter table.
  • Recheck the DB components versions.
  • Try to narrow it down to a/some process models.

I also find some running instances are not deleted, no matter the chosen parameters.

Best regards,

10

So after weeks of trying all sorts of things I made 1 small change to the PROCESS_ARCHIVE package and ALL IS GOOD NOW, I removed a check on v_procschema being ‘’.

Original — IF (v_procschema IS NOT NULL) AND (v_procschema <> ‘’) THEN
Modified — IF (v_procschema IS NOT NULL) THEN