ApplicationXtender - Convert Application to use Secure Paths (SRM)

Applies to

ApplicationXtender Storage Retention Mgmt 6.5, 7.0

Summary

Convert Application to an SRM Application

Resolution

Convert Application to an SRM Application

Verify application setup

Verify the following for the application:

The application uses a secure path. Any currently used network share can be made into a secure path. To do this all you need to do is lock down access to the path to a single super user, and enter that user s credentials in XS Admin on the "Paths node. See ApplicationXtender documentation for more information on secure paths.

The secure path restriction for SRM applications is only enforced when creating an SRM application so you will be able to convert the application without a secure path, but it is highly recommended that you perform this step. Otherwise, the documents, even when on retention, can be deleted from the file system by any user with access to the path.

Application's "Document Write Path" and "Annotation Write Path" are the same. Verify they have never been different by viewing the ae_dl# table entries. If in any record the pathid and annote fields are different (and annote is not 0), then you will have to use migration wizard or move the annotation files into the document path (see step below).

To verify your DB for consistent annotation and document write paths, run the slq command, replacing the # with the application number:

SELECT COUNT(*) FROM ae_dl# WHERE pathid <> annote AND annote <> 0
If this command returns anything but 0, then you'll need to either move the annotation files or use migration wizard.

To move annotation into your document path you can perform the following steps.

Navigate to your annotation path. Copy the whole directory with the correct application name.
Paste/merge this directory into your document directory of the same name.
Look at the paths table to get an understanding of the path locations and the path number.
In the apps table, replace the "annpath" value with the value in the pathid column. This will ensure all new annotations go to the correct location.
In the ae_dl# table for the application, wherever the "annote" column value is greater than 0, this value will need to be changed to the new "pathid" ("pathid" and "annote" should be the same value when "annote" > 0). This allows you to find current document annotation files again.
To verify everything was done correctly, open a document in AX and right-click on it. Then click on "Information" and look at the "files" tab. Both paths here should be the same.

Ensure no two versions of a document share the same annotation file. This is a rare case but possible. There is no quick test for this. On a million record application, this operation is prohibitively slow. However, if this is the case on any document a direct conversion to an SRM application without using migration wizard will mean loss of data.

Convert Application to an SRM application

Step 1) Verify the application
Step 2) Backup the Database
Step 3) Test DB backup
Step 4) Create ae_dlsd# and ae_rtn# tables for the application. Probably the easiest way to do this is to create a dummy SRM application. After creating this new application you should see its empty ae_dlsd# and ae_rtn# tables in the database with the correct table indexes created. You can then rename/copy-rename this table to match the desired application number.

For example:
The application you re trying to convert has app number = 52. The new dummy SRM application has app number 104. You should see the table ae_dlsd104 and ae_rtn104. Rename these empty tables to ae_dlsd52 and ae_rtn52. This will automatically give you the correct table structure with indexes. You could also just create these tables manually.

Step 5) Move records from the current ae_dl# table to the ae_dlsd# table

For example if appid was 23:
INSERT INTO ae_dlsd23(
      docid,
      pagenum,
      subpagenum,
      pagever,
      objectid,
      pathid,
      annote,
      formatid,
      ftoffset,
      ftcount
)
SELECT
      docid,
      pagenum,
      subpagenum,
      pagever,
      objectid,
      pathid,
      annote,
      formatid,
      ftoffset,
      ftcount
FROM ae_dl23

Step 6) Make every record in the ae_dlsd# have a clipid value of 'X'

For example: UPDATE ae_dlsd23 SET clipid='X'

Step 7) Update the ae_apps table for the application you re converting
- Set the dlname equal to the name of the new ae_dlsd# table name
- Add 65536 to the current value of the flags field
Step 8) Test to verify documents from the application can be viewed with annotations and basic document and annotation operations complete successfully
Step 9) Launch RM Configuration Utility to enable and set up retention for the application.
Step 10) Restart your FAM service and Automatic Retention Filer services.

A test script (below) was written to automate steps 4-7 which is the most of the database work. Using the script in optimal conditions where the application was valid based on the criteria above took an extremely short amount of time (Under a minute). This was performed without indexes on the ae_dlsd# table as they can be added after the large insert of a million records has finished.

/*
Code to update AX in order to allow use of Secure paths on existing Applications.
*/
DECLARE @appid varchar(4)
DECLARE @sql1 varchar(500)
-- modify the appid below to update the Retention Module per application
SET @appid = '14'

SET @sql1 = 'CREATE table ae_rtn' + @appid +' (clipid nvarchar(82),createts nvarchar(16),retdate nvarchar(16),retlabel nvarchar(128),retholdid nvarchar(64))'
exec (@sql1)

SET @sql1 = 'CREATE table ae_dlsd' + @appid +' (docid int,pagenum int,subpagenum smallint,pagever tinyint,objectid int,pathid int, clipid varchar(82),filets varchar(16),
	annote smallint,formatid int,ftoffset int, ftcount int)'
EXEC (@sql1)

SET @sql1 = 'INSERT INTO ae_dlsd' + @appid +' (docid,pagenum,subpagenum,pagever,objectid,pathid,annote,formatid,ftoffset,ftcount)
SELECT docid,pagenum,subpagenum, pagever,objectid,pathid,annote,formatid,ftoffset,ftcount FROM ae_dl' +@appid + ''
EXEC (@sql1)

SET @sql1 = 'UPDATE ae_dlsd' +@appid+' SET clipid = ''X'''
EXEC (@sql1)

DECLARE @app varchar(15)
SET @app = 'ae_dlsd'
DECLARE @app2 varchar(20)
set @app2 = @app + @appid
SET @sql1 = 'UPDATE ae_apps set dlname = '''+ @app2+''''
Exec (@sql1)

SET @sql1 = 'UPDATE ae_apps set flags = 65536 where appid = '+@appid + ''
EXEC (@sql1)

CASO Knowledge Base

ApplicationXtender - Convert Application to use Secure Paths (SRM)

Applies to

Summary

Resolution

Migration of a million record application with auto-index import and key reference using an oracle DB with the migration wizard took around 17 hours.