Managing Your Database › Data Dereferencing › Use the Dbadmin Mode › How to Archive and Purge Historical Data

How to Archive and Purge Historical Data

Periodically remove the historical records from the system to keep the database at a manageable size for optimum performance. You create rules or activate the predefined rules to archive the historical records and purge them from the database.

The following diagram shows how to archive and purge the historical data:

Follow these steps:

1. Open the CA SDM Web UI.
2. Verify the Archive and Purge Options
3. Define the Archive and Purge Path.
4. Choose the Archive and Purge Rule.
   • Create an Archive and Purge Rule.
   • Use a Predefined Archive and Purge Rule.
5. Run the Archive and Purge Rule.
6. Verify the Archive and Purge Results.
7. (If necessary) Restore the Archived Data.

Open CA SDM Web UI

Log in to the web UI from the following servers, depending on your CA SDM configuration:

• Conventional: Primary or secondary servers
• Advanced availability: Application or background servers

Verify the Archive and Purge Options

Before you set up the archive and purge rule, verify the archive and purge options that you may require to install or uninstall, depending on your organization needs. For example, the default_schedule option specifies the default schedule (workshift entry) used by the archive and purge rules. If the option value is set to an invalid workshift entry name or empty string, the rule does not execute the rule with the schedule value. The option value field sets the NX_DEFAULT_SCHEDULE variable, which is located in the NX.env file.

Select Options Manager, Archive and Purge on the Administration tab. Install or uninstall the option that you require.

Define the Archive and Purge Path

You can define the location where you want to store the archived data. This path can be the root directory of a remote server or a UNC path. Depending on your CA SDM configuration, the following servers must run on Windows to access the UNC path:

• Conventional: Primary server
• Advanced availability: Background server

Follow these steps:

1. Log in to web UI from the following CA SDM servers, depending on your CA SDM configuration:
   • Conventional: Primary or secondary servers
   • Advanced availability: Application or background servers
2. If you want to archive and purge attachments, complete the following steps:
   1. Select Attachments Library, Repositories on the Administration tab.

      The Repositories page opens.

   2. Right-click on a repository (such as Service Desk) and click Edit.

      The repository details page opens.

   3. Edit the following fields:

      Archive Type

      Specifies the archive and purge action to be taken on the contents of the repository. Following values are the valid:

      None: No archive and purge process is performed.

      Archive and Purge: The historic records are written to the file specified in the archive field and purged from the database.

      Purge Only: The historic records are purged from the database, but are not written to the archive file.

      Archive Path

      Specifies the directory path or the UNC path to which files in the repository are moved during the archive process.

      UNC Credentials

      Specifies the credentials to access the UNC path. Click UNC Credentials to open the Credentials Search page.

      • If you have already created the credentials to access the specified UNC path, search using the fields and select the credentials.
      • If you want to create the credentials, click Create New. For more information about creating credentials, see the Create UNC Credentials topic.
   4. Click Save.
3. If you want to archive and purge data other than attachments, complete the following steps:
   1. Select Archive and Purge, Archive and Purge Settings on the Administration tab.
   2. Enter the path where you want to store the archived and purged data as the Archive Purge Rule Path.
   3. If you are using a UNC path to store the archived data, click UNC Credentials.

      The Credentials Search page opens.

      • If you have already created the credentials to access the specified UNC path, search using the fields and select the UNC credentials.
      • If you want to create the credentials, click Create New. For more information about creating credentials, see the Create UNC Credentials topic.
   4. Click Save.

   The archive and purge path is defined.

Create UNC Credentials

You create the UNC credentials to allow users to access shared resources from the CA SDM servers using UNC path.

Important! The UNC component does not work when the CA SDM server is in the domain and the shared location is in the WORKGROUP. It also does not work when the administrator password for the CA SDM server and the shared location are different.

Follow these steps:

1. Click the UNC Credentials in the General Setting page or select Security and Role Management, UNC Credentials on the Administration tab.

   The Credentials List page opens.

2. Click Create New.

   The Create New Credentials page opens.

3. Complete the following fields as appropriate:

   Symbol

   Specifies the unique identifier to identify the credentials during a search easily.

   Userid

   Specifies the username to access the UNC path. The user can be a local or domain Windows user having access to the Service Desk server.

   Password

   Specifies the password to access the UNC path.

   Active

   Specifies if the UNC credentials are active or inactive. The inactive credentials cannot be used.

4. Click Save.

   The UNC credentials are created.

Choose the Archive and Purge Rule

You are required to select the appropriate archive and purge rule. Consider the following possibilities:

• Create an archive and purge rule. For example, you create an archive and purge rule for Knowledge Management forums.
• Use a predefined archive and purge rule. For example, you use the predefined rules for archiving and purging KPI data.

Create an Archive and Purge Rule

Before you can perform an archive or purge, you create a rule. The rule defines what you want to archive and when.

Example: You create an archive and purge rule to remove deleted forums from the database.

Follow these steps:

1. Select Archive and Purge, Archive and Purge Rules on the Administration tab.

   The Archive and Purge Rule List page opens.

2. Click Create New.

   The Create New Archive and Purge Rule page opens.

3. Complete the archive and purge rule fields, as appropriate.
   • Select knowledge document as the Config. Object Name.
   • Add KS_TYPE=20 as the Additional Query.
4. Click Save.

   The new rule is displayed on the Archive and Purge Rule List page.

Example: You create an optional configuration rule inside the arcpur_cfg.xml and itil_arcpur_cfg.xml files to archive and purge the KPI_Ticket_Data node.

Follow these steps:

1. Log in to the following server, depending on your CA SDM configuration:
   • Conventional: Primary server
   • Advanced availability: Background server
2. Find the following files in the $NX_ROOT/site/cfg/ directory:
   • arcpur_cfg.xml
   • itil_arcpur_cfg.xml
3. Edit the files to define the end_time (last_mod_dt) in KPI_Ticket_Data node. It specifies the criteria to select the records for archive and purge.
4. Link the records in the KPI_Ticket_Data table to the records in the Ticket table (such as cr, chg, or iss). This ensures that all ticket related records in the KPI_Ticket_Data table are archived and purged.
5. KPI_Ticket_Data node does not have a SREL relationship with any Ticket node and it relies on two fields, obj_name, and obj_id, to link with a ticket. The obj_name value can be cr, chg, or iss and the obj_id value is the ticket id. Define a main_obj for each ticket object.

   The following is a sample main_object definition for the ticket object, cr:

   <!-- KPI Ticket Data -->
   

   <main_obj>
   

   <name>KPI Ticket Data</name>
   

   <internal_name>KPI Ticket Data</internal_name>
   

   <factory>ktd</factory>
   

   <default_query>obj_name='cr'</default_query>
   

   <date_field>end_time</date_field>
   

   <ref_by value="obj_id">cr.id</ref_by>
   

   </main_obj> 
   

   Note: The configuration rule can only select records for cr. The ref_by tag can match the value of obj_id in KPI Ticket Data to the value of id in cr. If a match is found, it means that a KPI Ticket Data record is referenced by a cr record, so the KPI Ticket Data record is not archived and purged.

6. After adding the configuration rules for all ticket objects, depending on your CA SDM configuration, do one of the following:
   • Restart the CA SDM servers for conventional.
   • Restart the CA SDM servers for advanced availability.

   These configuration rules become selectable configuration object names in the Archive and Purge Rule Detail form.

Archive and Purge Rule Fields

You can use the following fields to define or edit rule definitions.

Rule Name

Specifies a unique identifier for the rule.

Status

Indicates whether this rule is active. The inactive rule runs only once, even if it is scheduled for a recurrent process.

Schedule

Specifies a workshift during which the rule should be in effect.

Recurrence Interval

Specifies how often this rule will run.

Archive File Name

Specifies the name of the file where you want to store the historic records. Enter the file name that you have mentioned while defining the archive and purge path. For more information, see the Define the Archive and Purge Path topic.

Operation Type

Specifies one of the following types of operation that the rule must execute:

Archive/Purge

Archives the historic records to a file and purges the archived records from the database.

Purge Only

Purges historic records from the database, but they are not written to the archive file.

Archive Only (Test Run)

Writes historic records to the archive file without purging them from the database. Use this option to test a newly created or edited archive and purge rule.

Config. Object Name

Specifies the name of the database object this rule can archive and purge. The Object Name field is automatically populated according to your selection in the Config. Object Name field.

Days Inactive

Specifies the number of days a record is inactive to be eligible for the archive and purge from the database.

Additional Query

Archives and purges specific inactive records among the existing inactive records. Use this field when you want to create different rules for archiving and purging the subsets of expired records for the same object. Use the same syntax as you use for stored queries.

The following query archives and purges only assigned inactive request records with a priority of 1:

priority = 1 AND (assignee IS NOT NULL OR group IS NOT NULL) and active = 0

The following query format archives and purges records based on time-span:

close_date < EndAtTime(\'LAST_YEAR\')

Use a Predefined Rule

You can use the predefined rules in CA SDM to archive and purge historical data. These predefined rules are set to Inactive, by default. Set it active to use the rule.

Example: Use the predefined rule to archive and purge the KPI data.

Follow these steps:

1. Select Archive and Purge, Archive and Purge Rules on the Administration tab.
2. Search for any of the following predefined rules for KPI data:
   • KPI Ticket Data
   • KPI Data (System)
   • KPI Data (Stored Query)
   • KPI Data (SQL)
3. Select the rule from the search result.
4. Click Edit to modify the archive and purge rule fields.

   Important! Ensure that you select the Active option from the Status field.

5. Click Save.

   The predefined rule is ready for use.

Verify the Archive and Purge Results

You check the results of the archive and purge rule that you have scheduled. Do one or both of the following actions:

• View the Archive and Purge History.
• View the Archive and Purge Log File

View the Archive and Purge History

You can view the history for each archive and purge rule. For example, view the objects that are purged by a rule.

Follow these steps:

1. Select Archive and Purge, Archive and Purge History on the Administration tab.

   The Archive and Purge History List page opens.

2. Click Show Filter and specify the filter criteria. For example, enter the earliest start date to show only entries from the specified time frame.

   Note: To display the Additional Search Arguments field, click the spigot icon. This field is intended only for expert users who understand SQL and Majic and can use it to specify search arguments that are not available in the standard search filter fields. To specify an additional search argument, enter a SQL WHERE clause in this field.

   The list of matching rules are displayed.

3. Click the rule name for which you want to review the rule configuration.

   The Archive and Purge Rule Detail page opens.

View the Archive and Purge Log File

You can check the arcpur.log file from the $NX_ROOT/log/ directory to find the errors that have occurred while executing the scheduled archive and purge rule.

Note: The size limitation for arcpur.log files is defined in $NX_ROOT/NX.env as

# The size limit for the Archive and Purge log file and data file.
@NX_ARCPUR_FILESIZE=2000000000

Archive and purge creates arcpur.log.0, arcpur.log.1 though arcpur.log.9 after reaching the file limit for each log files.

Restore the Archived Data

You can restore the archived data when you need them in the database again.

Follow these steps:

1. Start the daemons in dbadmin mode. Dbadmin mode allows limited access, so you can safely run pdm_load to restore the archived data. Run the pdm_d_mgr -s DBADMIN command on the following servers, depending on your CA SDM configuration:
   • Conventional: Primary server
   • Advanced availability: Background server
2. Go to the root directory or the UNC location where you have stored the archived data.
3. Locate the archived data file (.dat file).
4. Copy the file to the following servers, depending on your CA SDM configuration:
   • Conventional: Primary server
   • Advanced availability: Background server
5. Run pdm_load against the data file. For example:

   pdm_load -a -f 2004611T1726_Call_Request.dat
   

6. If there is a problem with the pdm_load command, complete the following steps:
   1. Check the command line and $NX_ROOT/log/arcpur.log for errors.
   2. Restart the CA SDM servers.
7. To prevent the record from being archived and purged at the next cycle, complete the following steps:
   1. Update the record to make it active again.
   2. Inactivate the associated archive and purge rule.