Cleaning Up the CDA Database

The Clean-up Utility DB-Cleanup.exe is a command line utility that can be used to delete workflow Executions, Packages, Artifacts, and History Records in the database.

Important! Stop the Release Manager before running the DB-Cleanup tool to avoid conflicts in the database.

This page includes the following:

About the Clean-up Utility

After running CDA for a while, the CDA database may contain an increasing number of outdated records, especially from workflow executions. To keep the size of the Automic Continuous Delivery Automation database small and ensure a good performance, you may want to remove outdated records. To do so, you can run the Clean-up Utility.

Important! The Clean-up Utility requires a direct database connection and operates through SQL statements. Therefore, the computer hosting the utility must allow a connection to the CDA database server.

The user specified in the connection string must have CRUD privileges on all tables of the CDA database and the right to create temporary tables in the database.

Installing the Clean-up Utility

If the Clean-up utility DB-Cleanup.exe is not already available on your system, perform the following steps.

To Install the Clean-Up Utility

Tip: Save the Clean-Up Utility in the following folder: C:\Automic\Release.Manager\Utilities

  1. Copy the DB-Cleanup.exe file to a computer which has access to the CDA database server.
  2. Configure the clean-up utility according to your needs.

    See: Configuring DB-Cleanup.exe.config File

Configuring DB-Cleanup.exe.config File

Note: The DB-Cleanup.exe.config file must be stored in the same folder as the DB-Cleanup.exe.

The parameters are organized in the following sections:

  • startParameters

  • connectionStrings from customer.config (path: Automic\Release.Manager\WebUI\customer.config)

    Connection string syntax:

    "Data Source=##DB SERVER##;Initial Catalog=##DB NAME##;User ID=##DB USER##;Password=##DB PASSWORD##,"database Schema=##DB SCHEMA##

    Parameters

    • Data Source: Replace ##DB SERVER## with your database server name, to which you have connected.
    • Initial Catalog: Replace ##DB NAME## with your database name (for example, CDA).
    • User ID: Replace ##DB USER## with your database user:
    • Password: Replace ##DB PASSWORD## with your database user password.

    Important! Unencrypted passwords cannot start with --10.

    Examples

    • SQL Server Database:

      connectionString="Data Source=##DB SERVER##;Initial Catalog=##DB NAME##;User ID=##DB USER##;Password=##DB PASSWORD##"

      <add connectionString="Data Source=tcp:localhost,1433;Initial Catalog=ARA;User ID=AUTOMIC;Password=P@ssw0rd" name="Database"/>

    • Oracle Database:

      connectionString=user id=##DB USER##;password=##PASSWORD##;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= ##DB SERVER##)(PORT=##PORT OF LISTENER##))(CONNECT_DATA=(SERVICE_NAME=##DB NAME##)))

      <add connectionString="User Id=ARA_DEV_8_0;password=ara;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.40.58)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=vviedb02.sbb01.spoc.global)))" name="Database"/>

      Note: See Configuring CDA via Customer.Config File

    • PostgreSQL Database:

      connectionString="User ID=<db_user>;password=<db_password>;Host=<db_host>;Port=<db_port>;Database=<db_name>;searchpath=<db_schema>"

      <add connectionString="User ID=ara;password=1a2b3c;Host=vpostgre01;Port=5332;

      Database=ara;searchpath=public" name="MainDatabase"

      >

      Note: If the searchpath parameter is not specified, the default value (public) is used.

Both sections can be encrypted.

Tip: You can set fixed parameters (for example, Application names) with <add key> tags. For example: <add key "applications" value="MyApp1,MyApp2">

Parameters

You can pass every parameter either via XML configuration file Db-Cleanup.exe.config or directly together with the command line call (Both, dash- and colon-syntax are supported to pass parameters).

Rules for combining parameters:

  • If parameters are passed via the command line call, they overwrite the corresponding values in the Db-Cleanup.exe.config
  • If applications are set but no workflows: all executions/packages of the specified applications are deleted.
  • If applications and workflows are set but the combination of specified applications and workflows does not exist: no executions are deleted.

Case-sensitivity of parameters depends on the underlying database:

  • For MS SQL Server, parameters are case-insensitive
  • For Oracle Database and PostgreSQL Server, parameters are case-sensitive

Available Commands

You can run the following commands:

  • execution
  • package
  • historyrecord
  • artifact

Important! If no command is specified, executions are cleaned up.

Examples

\DB-Cleanup.exe historyrecord

  • No command is specified (executions are cleaned up):

    \DB-Cleanup.exe --olderThan 120 --archivedOnly true --applications "CRM System"

  • HistoryRecord command is specified:

    \DB-Cleanup.exe historyrecord --olderThan 120 --keepAtLeast 20

Input Parameters

Common Parameters

The following parameters can be used with all available commands:

  • connectionString

    Connection string to connect to the CDA database.

    See also: Configuring CDA via Customer.Config File

    Mandatory:  Yes

    Type: String

    Default: -

  • verbose

    Level of textual output.

    Values:

    • min: minimal - only errors and warnings are reported.
    • med: medium - errors, warnings, and information are reported.
    • max: maximum - all log messages are reported (for debugging).

    Mandatory: No

    Type: String

    Default: min

  • fi

    (Optional) Stores screen output including timestamps in a log file.

    Mandatory: No

    Type: String

    Default: -

  • --help -h

    Displays information about parameters and their descriptions.

    Mandatory: No

    Type: -

    Default: -

  • client

    Specifies which client will be cleaned up.

    Mandatory: Yes (if the database contains more than one client).

    Type: String

    Default: -

Execution Parameters

  • olderThan

    Only executions whose end-date lies before the number of days that are specified in the olderThan parameter date are deleted.

    Only whole days are selected.

    Example:

    If the current date and time is 2014-05-15 10:12 and the parameter olderThan is set to 1:

    2014-05-15 00:00 - 1 (olderThan value) = 2014-05-14 00:00

    All executions with an end date prior to 2014-05-14 00:00 are considered for deletion.

    Important! olderThan=0 deletes all executions.

    Mandatory: No

    Type: Number

    Default: 90

  • keepAtLeast

    Defines how many executions must remain per workflow irrespective of their end date.

    Example: keepAtLeast=5

    At least 5 executions of each workflow must remain in the system and are not deleted (even when olderThan or some other criterion is met).

    Mandatory: No

    Type: Number

    Default: 5

  • applications

    Comma-separated list of application names.

    If set, only executions of the specified applications are considered for deletion.

    Example: CRM System, Billing System

    Mandatory: No

    Type: String

    Default: -

  • workflows

    Comma-separated list of workflow names.

    For application workflows, this parameter has to be set together with the applications parameter. If the applications parameter is not set, only general workflows are considered for deletion.

    Example:

    Install, Uninstall

    Mandatory: No

    Type: String

    Default: -

  • archivedOnly

    This parameter defines whether all executions or only archived ones should be considered for deletion.

    Values:

    • true: only archived executions are considered for deletion.
    • false: all executions are considered for deletion.

    Mandatory: No

    Type: Boolean

    Default: false

  • force

    This parameter defines whether the utility should also consider deleting executions that belong to a current installation or not.

    Values:

    • false: Executions belonging to current installations are not deleted.
    • true: also Executions belonging to current installations are considered for deletion.

    Mandatory: No

    Type: Boolean

    Default: false

  • id

    Comma-separated id of executions to be deleted.

    This switch cannot be used together with any of the other filtering switches (olderThan, keepAtLeast, applications, workflows, archivedOnly).

    The number of Ids to be specified is limited to 100.

    Mandatory: No

    Type: String

    Default: -

Conditions for Deleting Executions

The Clean-up Utility only deletes a record if all of the following conditions are fulfilled:

  • The workflow execution must be in its final state - see Viewing Application Deployments
  • By default, the workflow execution must not belong to a current installation (force flag is set to false, see Parameters)
  • Only if the force flag is explicitly set to true, also workflow executions for current installations are considered to be deleted (force flag is set to true, see Parameters)
  • All conditions that are stated via Parameters are fulfilled

Artifact Parameters

Command name: artifact

  • olderThan

    Only artifacts whose end-date lies before the number of days that are specified in the olderThan parameter date are deleted.

    Only whole days are selected.

    Example:

    If the current date and time is 2014-05-15 10:12 and the parameter olderThan is set to 1:

    2014-05-15 00:00 - 1 (olderThan value) = 2014-05-14 00:00

    All artifacts with an end date before 2014-05-14 00:00 are considered for deletion.

    Important! olderThan=0 deletes all artifacts.

    Mandatory: No

    Type: Number

    Default: 90

  • keepAtLeast

    Defines how many artifacts must remain per artifact source, irrespective of their end date.

    Example: keepAtLeast=5

    At least 5 artifacts of each artifact source must remain in the system and are not deleted (even if olderThan or some other criterion is met).

    Mandatory: No

    Type: Number

    Default: 5

  • sources

    Comma-separated list of Artifact Source names. If set, only Artifacts of the specified Artifact Sources are considered for deletion.

    Example:

    Internal Nexus, Department SMB

    Mandatory: No

    Type: String

    Default: -

  • archivedOnly

    This parameter defines whether all artifacts or only archived ones should be considered for deletion.

    Values:

    • true: only archived artifacts are considered for deletion.
    • false: all artifacts are considered for deletion.

    Mandatory: No

    Type: Boolean

    Default: false

  • force

    This parameter defines whether the utility should also consider deleting artifacts that belong to old installations or packages. Artifacts belonging to current installations are never deleted.

    Values:

    • true: Artifacts which are assigned to any packages or used in old installations are considered for deletion. Artifact information of old installation records are cleared.
    • false: Only unused artifacts - Artifacts which do not belong to any installation or package - are considered for deletion.

    Mandatory: No

    Type: Boolean

    Default: false

  • id

    Comma-separated id of artifacts to be deleted.

    This switch cannot be used together with any of the other filtering switches (olderThan, keepAtLeast, sources, archivedOnly).

    The number of Ids to be specified is limited to 100.

    Mandatory: No

    Type: String

    Default: -

Conditions for Deleting Artifacts

The Clean-up Utility only deletes a record if all of the following conditions are fulfilled:

  • Artifacts belonging to current installations cannot be deleted.

Package Parameters

Command name: package

Important! Packages belonging to an execution, a patch chain, a package workflow instance (package hook), or a current installation cannot be deleted, regardless of the --force flag or any other criteria.

  • olderThan

    Only packages whose end-date lies before the number of days that are specified in the olderThan parameter date are deleted.

    Only whole days are selected.

    Example:

    If the current date and time is 2014-05-15 10:12 and the parameter olderThan is set to 1:

    2014-05-15 00:00 - 1 (olderThan value) = 2014-05-14 00:00

    All packages with an end date before 2014-05-14 00:00 are considered for deletion.

    Important! olderThan=0 deletes all packages.

    Mandatory: No

    Type: Number

    Default: 90

  • keepAtLeast

    Defines how many packages must remain per application, irrespective of their end date.

    Example:

    keepAtLeast=5

    At least 5 packages of each application must remain in the system and are not deleted - even if they match the olderThan or some other criteria.

    Mandatory: No

    Type: Number

    Default: 5

  • applications

    Comma-separated list of application names.

    If set, only Packages of the specified applications are considered for deletion.

    Example: CRM System, Billing System.

    Mandatory:

    Type:

    Default:

  • archivedOnly

    This parameter defines whether all packages or only archived ones should be considered for deletion.

    Values:

    • true: only archived packages are considered for deletion.
    • false: all packages are considered for deletion.

    Mandatory: No

    Type: Boolean

    Default: false

  • force

    This parameter defines whether the utility should also consider deleting packages that belong to old installations. Packages belonging to current installations are never deleted.

    Values:

    • true: Packages that are used in old installations are considered for deletion. Package information of old installation records are cleared.
    • false: Only unused packages - Packages which do not belong to any installation - are considered for deletion.

    Mandatory: No

    Type: Boolean

    Default: false

  • id

    Comma-separated id of packages to be deleted.

    This switch cannot be used together with any of the other filtering switches (olderThan, keepAtLeast, applications, archivedOnly).

    The number of Ids to be specified is limited to 100.

    Mandatory: No

    Type: string

    Default: -

If you set an invalid value for a parameter, the tool prints an error message informing about invalid parameters and quits.

Conditions for Deleting Packages

The Clean-up Utility only deletes a record if all of the following conditions are fulfilled:

  • Packages belonging to an execution, a patch chain, a package workflow instance (package hook) or a current installation are not deleted regardless of the --force flag and other criteria.

History Records

Command name: historyrecord

  • olderThan

    Only history records having an end-date older than the specified number of days before the current date are deleted.

    Important! Only whole days are selected.

    Example:

    • Current date and time is 2014-05-15 10:12
    • Parameter olderThan is set to 1
    • Calculation: 2014-05-15 00:00 - 1 (olderThan value) = 2014-05-14 00:00
    • Results: all executions with an end date prior to 2014-05-14 00:00 are considered for deletion

    Note: olderThan=0 means that all entities are deleted.

    Default: 90

  • keepAtLeast

    Defines how many entities must remain per parent entity.

    Example:

    keepAtLeast=15

    At least 15 history records of each application must remain in the system and are not deleted - even if they match the olderThan or some other criteria.

    Mandatory: No

    Type: Number

    Default: 15

Return Codes

After each run, the Clean-up utility returns an exit code to indicate the outcome of action. You may use this code in a script.

The utility returns the following codes:

  • 0

    Success

  • 1

    Unable to connect to the database

  • 99

    Unexpected error

    Note: To resolve this, review the log files.

Configuration Files

Common parameters can be specified in the configuration file with the same name as in the command line parameter.

<appSettings>
   <add key="verbose" value="max" />
   <add key="force" value="false" />
 </appSettings>

Command-specific parameters have the following format: [commandname].<parameter_name>

Example:

<appSettings>
   <add key="verbose" value="max" />
   <add key="force" value="false" />
   <add key="package.keepAtLeast" value="100" />
   <add key="package.olderThan" value="90" />
 </appSettings>

Command-specific parameters without prefix are considered as execution parameters:

<appSettings>
   <add key="verbose" value="max" />
   <add key="force" value="false" />
   <add key="keepAtLeast" value="100" /> <!-- this is considered as an execution command parameter -->
 </appSettings>

Examples

Examples for CLI calls and configuration file

Dash-syntax:

\DB-Cleanup.exe --olderThan 120 --archivedOnly true --applications "CRM System"

Colon-syntax:

\DB-Cleanup.exe olderThan:120 archivedOnly:true applications: "CRM System"

DB-Cleanup.exe.config:

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

<connectionStrings> <add name="Database" connectionString="Data Source=##DB SERVER##;Initial Catalog=##DB NAME##;User ID=##DB USER##;Password=##DB PASSWORD##"/> </connectionStrings>

<appSettings> <add key="verbose" value="max"/> <add key="force" value="false"/> </appSettings>

</configuration>

See also: