uspSetArchivingDataObject

Purpose

The uspSetArchivingDataObject stored procedure is designed to register data objects for the archiving process within the ExMeX Archive Framework.

Motivation

Data archiving is vital for ensuring that outdated or inactive data is stored efficiently and securely, while remaining accessible when needed. Over time, archived data may need to be purged to optimize storage costs and maintain system performance.

Applicability

The stored procedure uspSetArchivingDataObject registers data objects in the ExMeX Archiving Framework for archiving.

Syntax and Usage

Below is an example of the stored procedure’s structure:

EXEC MetadataZoneCore.uspSetArchivingDataObject
    -- Action to perform: (A)dd, (U)pdate, (D)elete
    @SetArchivingExec          = <(A)dd, (U)pdate, (D)elete>,

    -- Source data object to be archived (mandatory)
    @SourceDatabaseName        = <optional - defaults to the current database if not submitted>,
    @SourceSchemaName          = <mandatory - source schema name>,
    @SourceDataObject          = <mandatory - source data object>,

    -- Source data item, used to identify records for archiving (mandatory only when registering a new data object)
    @ArchivingColumnCode       = <column name to apply archiving>,

    -- Target archiving object where data will be archived (data is physically deleted from this object)
    @ArchivingDatabaseName     = <optional - defaults to 'SourceDatabaseName'>,
    @ArchivingSchemaName       = <optional - defaults to 'SourceSchemaName' + 'Archive'>,
    @ArchivingDataObject       = <optional - defaults to 'SourceDataObject' + 'Archive'>,

    -- Options - if nothing is set, default will be applied
    @IsActive                  = <0 = OFF (default); 1 = ON>,
    @RetentionPeriod           = <days - how long to retain data before archiving, default = NULL>,
    @ArchivingRetentionPeriod  = <days - how long to retain archived data before deletion, default = NULL>,
    @ArchivingExecutionOrder   = <order applied during execution, default = NULL>
    @Comment                   = <comments or description>,

    -- For troubleshooting purposes
    @Debug                     = <optional debug flag <0 = OFF (default); 1 = ON>>

Input Parameters:

1. @SetArchivingExec: Mandatory; specifies the operation to be performed: Add, Update, or Delete a data object
2. @SourceDatabaseName: Optional; specifies the database name for the source data object. Defaults to the current database DB_NAME()
3. @SourceSchemaName: Mandatory; specifies the schema name for the source data object
4. @SourceDataObject: Mandatory; specifies the name of the source data object to be archived
5. @ArchivingColumnCode: Mandatory (A) / Optional (U,D); specifies the column that determines when data should be archived. Possible data types and formats see Considerations and Impacts
6. @ArchivingDatabaseName: Optional; specifies the database for the archiving data object. Defaults to SourceDatabaseName
7. @ArchivingSchemaName: Optional; specifies the schema name for the archiving data object. Defaults to SourceSchemaName + "Archive"
8. @ArchivingDataObject: Optional; specifies the name of the archiving data object. Defaults to SourceDataObject + "Archive"
9. @IsActive: Optional; activates or deactivates the archiving process for the specified data object. Default is OFF (0)
10. @RetentionPeriod: Optional; specifies the number of days data should be retained in the source before archiving
11. @ArchivingRetentionPeriod: Optional; specifies the number of days data should be retained in the archive before deletion
12. @ArchivingExecutionOrder: Optional; if set, the order is applied during execution. This means that an archiving data object with a low value is archived before one with a high value
13. @Comment: Optional; allows for additional comments or descriptions
14. @Debug: Optional; used for debugging purposes

Return Types

This stored procedure returns an integer value.

Return Values

Return Value	Description
0	Execution successful.
1	Source database does not exist.
2	Source schema or data object does not exist.
3	Specified archiving column <column name> does not exist in the source data object <data object name>.
4	Missing index on source data object.
5	The archiving data object <data object name> in archiving schema <schema name> already exists!
6	The archiving schema <schema name> does not exists!
7	The archiving column <column name> does not contain a valid format for archiving!
8	Adding archiving data object failed.
9	Deleting archiving data object failed.

When a data object is successfully registered, the stored procedure will return the corresponding data record.

Considerations and Impacts

The same considerations and impacts detailed in the ExMeX Archiving Framework apply when using this stored procedure.

Date Format for archiving column

The column used to archive the data must contain data that can be converted into a date. Possible data types: date, datime, datetime2, strings, int. Examples of valid variants of a date are but not limited to:

• 2025-02-26
• 2025
• 202502
• 20250226
• Dec 2 2023 12:00AM
• 2025-03-06 14:17:34.3366857

If you are not sure whether the content of the selected column can be used for archiving, the following SQL statement can be used for testing:

-- Test, if archiving column code contains valid format
IF (SELECT TOP 1 ISDATE(CAST([<Your @ArchivingColumnCode>] AS VARCHAR(24))) FROM [<Your @SourceDataObject>]) <> 0
    SELECT 'VALID format'
ELSE
BEGIN
IF (SELECT ISDATE(CONCAT(CAST(MAX([<Your @ArchivingColumnCode>]) AS VARCHAR(24)),'01')) /* ,MAX(CustomerID) */ FROM [<Your @SourceDataObject>]) <> 0
    SELECT 'VALID format'
ELSE
    SELECT 'INVALID format'
END

Archiving Logs

The table MetadataZoneCore.ArchivingLogMessage logs all actions performed during the archiving process:

• RowsRead: Number of records found in the source data object.
• RowsArchived: Number of records moved from the source to the archive data object.
• RetentionPeriod: Records older than the defined retention period (based on SYSUTCDATETIME() - RetentionPeriod) are archived.
• RowsDeletedPhysical: Number of records physically deleted from the archive data object.
• ArchivingRetentionPeriod: Records older than the defined archiving retention period (based on SYSUTCDATETIME() - ArchivingRetentionPeriod) are physically deleted from the archive.
• ArchivingStatusNumber: 0 - Successful, 1 - Running, >1 - Error.

An example to select all log data:

SELECT *
FROM MetadataZoneCore.ArchivingLogMessage
ORDER BY InscriptionTimestamp DESC
;

Examples

Registration of a new data object for archiving

Note that the retention period is set by two global variables that are set globally within the ExMeX core framework. Both are used if no other values are set.

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'A',
     @SourceSchemaName          = N'MetadataZone',
     @SourceDataObject          = N'ProcessStepVapourTrail',
     @ArchivingColumnCode       = N'ProcessEndDateTime',
     @IsActive                  = 1,
     @Comment                   = N'ExMeX Framework logging table'
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result

• Source database is set to: current database
• Archiving schema is set to: MetadataZoneArchive ('SourceSchemaName' + 'Archive')
• Archiving data object is set to: ProcessStepVapourTrailArchive ('SourceDataObject' + 'Archive')
• Archiving data object is active

Registration of a new data object for archiving in another database

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'A'
    ,@SourceDatabaseName        = N'Playground'
    ,@SourceSchemaName          = N'dbo'
    ,@SourceDataObject          = N'Customer'
    ,@ArchivingColumnCode       = N'RegistrationYearPeriod'
    ,@ArchivingDatabaseName     = N'Playground'
    ,@ArchivingSchemaName       = N'dbo'
    ,@IsActive                  = 1
    ,@Comment                   = N'ExMeX Framework logging table'
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result

• Source database is set to: Playground
• Archiving database is set to: Playground
• Archiving schema is set to: dbo (Same as source schema)
• Archiving data object is set to: CustomerArchive ('SourceDataObject' + 'Archive')
• Archiving data object is active

Registration of a new data object for archiving with a different archiving schema to the default one

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'A',
     @SourceSchemaName          = N'MetadataZoneCore',
     @SourceDataObject          = N'BatchExecuteLoadProcessStepLog',
     @ArchivingColumnCode       = N'ModifiedAt',
     -- Default schema would have been 'MetadataZoneCoreArchive'
     @ArchivingSchemaName       = N'MetadataZoneArchive',
     @IsActive                  = 1,
     @Comment                   = N'ExMeX Framework module change logging table'
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result

• Source database is set to: current database
• Archiving schema is set to: MetadataZoneArchive
• Archiving data object is set to: BatchExecuteLoadProcessStepLogArchive ('SourceDataObject' + 'Archive')
• Archiving data object is active

Update an Existing Data Object - Retention Period

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'U',
     @SourceSchemaName          = N'MetadataZoneCore',
     @SourceDataObject          = N'BatchExecuteLoadProcessStepLog',
     -- Updating retention period to a total rention period of 60 days
     @RetentionPeriod           = 30,
     @ArchivingRetentionPeriod  = 30,
     @Comment                   = N'Updated retention period'
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result

Archiving configuration for the data object MetadataZoneCore.BatchExecuteLoadProcessStepLog is updated to a new total retention period of 60 days.

Update an Existing Data Object - Set execution order

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'U',
     @SourceSchemaName          = N'MetadataZone',
     @SourceDataObject          = N'SatParallelAsyncLPSChunkLog',
     -- Set archiving execution order
     @ArchivingExecutionOrder   = 1,
     @Comment                   = N'Updated execution order'
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result

The archiving execution order for the MetadataZone.SatParallelAsyncLPSChunkLog data object is updated to 1 - it will be executed first.

Update an Existing Data Object - Set to inactive

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'U',
     @SourceSchemaName          = N'MetadataZoneCore',
     @SourceDataObject          = N'BatchExecuteLoadProcessStepLog',
     -- Deactivate archiving of data object
     @IsActive                  = 0,
     @Comment                   = N'Updated active indicator'
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result

Archiving configuration for the data object MetadataZoneCore.BatchExecuteLoadProcessStepLog is updated to a new retention period of 90 days.

Update an Existing Data Object - Archiving Column Code

Remember to add an index to the new column for archiving. For example

CREATE NONCLUSTERED INDEX [IX_MetadataZoneCore_BatchExecuteLoadProcessStepLog_ModifiedAt]
    ON [MetadataZoneCore].[BatchExecuteLoadProcessStepLog] ([ModifiedAt] ASC) WITH (DATA_COMPRESSION = PAGE)

The update of the archiving column will then succeed.

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'U',
     @SourceSchemaName          = N'MetadataZoneCore',
     @SourceDataObject          = N'BatchExecuteLoadProcessStepLog',
     -- Previous used column: AssertionTimestamp
     -- New column:
     @ArchivingColumnCode       = N'ModifiedAt',
     @Comment                   = N'Updated archiving column.'
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result

Archiving configuration for the data object MetadataZoneCore.BatchExecuteLoadProcessStepLog is updated to a new archiving column code.

Delete an Existing Data Object

If you want to delete the archive data object as well, please execute the SQL statement returned by the stored procedure. Caution: If you have not made a backup, the process cannot be reversed! The archiving data object will not be dropped.

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'D',
     @SourceSchemaName          = N'MetadataZoneCore',
     @SourceDataObject          = N'BatchExecuteLoadProcessStepLog'
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result

The archiving configuration for the data object MetadataZoneCore.BatchExecuteLoadProcessStepLog is unregistered. An additional SQL statement is returned (as message) to drop the archive data object. For example:

DROP TABLE [MetadataZoneArchive].[BatchExecuteLoadProcessStepLogArchive]