Recovery DB setup for Archive (task persistence archive) DB is not working

Products

• CA Identity Suite

Issue/Introduction

We have changed the database connection for Archive (task persistence archive) in standalone xml but while executing Task persistence cleanup for archive the data, not able to move the data to newly created archive DB tables.

Is that because of both taskpersistance DB and Archive (task persistence archive) db should be in same database and that is in IDM?

Environment

Release : 14.3

Component : CA IDENTITY SUITE (VIRTUAL APPLIANCE)

Cause

Script of creation Archive DB has the wrong order of the fields into CREATE TABLE [dbo].[archive_tasksession12_5]

Resolution

In the script archive_db_script.sql inside CREATE TABLE [dbo].[archive_tasksession12_5] there is one shifted order of creation of the fields.
Please ensure ROW
[numericSequence] [bigint] NULL,
is at the end of the creation table block just after row:
[attributes] [nvarchar(2000)] NULL,

This wrong order could cause issues with procedure  archiveByTaskId in commands
INSERT INTO archive_tasksession12_5
SELECT * FROM tasksession12_5

Correct part of DB creation script:

CREATE TABLE [dbo].[archive_tasksession12_5](

                [tasksessionid] [nvarchar](50) NOT NULL,

                [state] [int] NOT NULL,

                [created_time] [datetime] NOT NULL,

                [last_access_time] [datetime] NOT NULL,

                [user_dn] [nvarchar](512) NOT NULL,

                [org_dn] [nvarchar](512) NOT NULL,

                [environmentid] [nvarchar](50) NOT NULL,

                [name] [nvarchar](512) NOT NULL,

                [description] [nvarchar](2000) NOT NULL,

                [acknowledged] [char](1) NOT NULL,

                [parent_of_wf_id] [nvarchar](50) NULL,

                [action_type] [int] NOT NULL,

                [priority] [int] NULL,

                [initiatorid] [nvarchar](50) NULL,

                [nesting_type] [int] NOT NULL,

                [next_state] [int] NULL,

                [taskeventwfapproval] [nvarchar](512) NULL,

                [approvalstatus] [nvarchar](50) NULL,

                [decisionmaker] [nvarchar](512) NULL,

                [approvaltime] [datetime] NULL,

                [finalstatus] [nvarchar](50) NULL,

                [attributes] [nvarchar](2000) NULL,

                [numericSequence] [bigint] NULL,

 

Archive DB part of incorrect creation:

 

CREATE TABLE [dbo].[archive_tasksession12_5](

                [tasksessionid] [nvarchar](50) NOT NULL,

                [state] [int] NOT NULL,

                [created_time] [datetime] NOT NULL,

                [last_access_time] [datetime] NOT NULL,

                [user_dn] [nvarchar](512) NOT NULL,

                [org_dn] [nvarchar](512) NOT NULL,

                [environmentid] [nvarchar](50) NOT NULL,

                [name] [nvarchar](512) NOT NULL,

                [description] [nvarchar](2000) NOT NULL,

                [acknowledged] [char](1) NOT NULL,

                [parent_of_wf_id] [nvarchar](50) NULL,

                [action_type] [int] NOT NULL,

                [priority] [int] NULL,

                [initiatorid] [nvarchar](50) NULL,

                [nesting_type] [int] NOT NULL,

                [next_state] [int] NULL,

                [numericSequence] [bigint] NULL,

                [taskeventwfapproval] [nvarchar](512) NULL,

                [approvalstatus] [nvarchar](50) NULL,

                [decisionmaker] [nvarchar](512) NULL,

                [approvaltime] [datetime] NULL,

                [finalstatus] [nvarchar](50) NULL,

                [attributes] [nvarchar](2000) NULL,