Administer > Configuration > Database Administration > Purge Service Subscriptions and Audit Data

Purge Service Subscriptions and Audit Data

The purge tool can be used to delete service subscriptions and audit data.

About Service Subscriptions

Canceled, expired, failed, and retired service subscriptions store information in the database that, over time, is no longer needed. The purge tool can be used to delete canceled, expired, failed, and retired subscriptions along with specific associated or referenced artifacts and entities. Canceled, expired, and failed subscriptions must have a service instance status of failed, canceled, cancellation failed, or expiration failed to be deleted. Canceled, expired, and failed subscriptions that are not in one of these states will not be deleted. All retired subscriptions are deleted.

By default, when the purge tool is run, canceled, expired, failed, and retired subscriptions that are older than 400 days (subscriptions that have been in a canceled, expired, failed, or retired state longer than 400 days) and certain referenced artifacts and entities are deleted from the database. The age of deleted subscriptions can be increased or decreased by modifying the age.in.days.to.purge.subscription property in the configuration properties file used by the purge tool.

When a subscription is deleted, the following artifacts and entities are deleted from the database:

Deleted Artifact Referenced by (Reference Fields) Referenced Artifacts and Entities that are Deleted
ServiceSubscription

action
associatedRequest
basePrice
catalogItem
initiatingServiceRequest
pricingModel
property
serviceInstance
totalPrice
Notifications
ServiceRequest ServiceSubscription (associatedRequest or initiatingServiceRequest) action
basePrice
pricingModel
property
totalPrice
Notifications
ServiceInstance ServiceSubscription (serviceInstance) componentRoot
Notifications
ServiceComponent ServiceInstance (componentRoot) action
property
resourceBinding
ResourceBinding ServiceComponent (resourceBinding) action
catalogItem
lifecycleProperties
property
resourceInstance
ResourceSubscription ResourceBinding (resourceInstance) action
catalogItem
lifecycleProperties
property
ProcessInstance

About Audit Data

CSA creates audit event records in the database for events that occur during the lifetime of a running instance of CSA.

By default, when the purge tool is run, audit data that is older than 400 days is deleted from the database. The age of deleted audit data can be increased or decreased by modifying the age.in.days.to.purge.audit property in the configuration properties file used by the purge tool.

For more information about auditing data, refer to the Reporting and Auditing whitepaper.

Deleting Service Subscriptions and Audit Data

To delete canceled, expired, failed, and retired subscriptions or audit data from the database, do the following:

Caution Deleted subscriptions and audit data cannot be restored unless you have backed up the database.

  1. Change to the CSA_HOME/Tools/DBPurgeTool/ directory where

    CSA_HOME is the directory in which CSA is installed

    .

  2. Generate the sample configuration files by running the following command (a sample configuration file is generated for each type of database supported by CSA):

    Oracle

    • Windows:

      "CSA_JRE_HOME\bin\java" -jar db-purge-tool.jar -g ‑j ojdbc6.jar

    • Linux:

      CSA_JRE_HOME/bin/java -jar db-purge-tool.jar -g ‑j ojdbc6.jar

    where ojdbc6.jar is the name of the Oracle JDBC driver installed in CSA_HOME/Tools/DBPurgeTool/.

    Note Additional command line options are required if a secure connection is enabled between the Oracle database and CSA. See step 4 below for more information.

    MS SQL and PostgreSQL

    • Windows:

      "CSA_JRE_HOME\bin\java" -jar db-purge-tool.jar -g

    • Linux:

    CSA_JRE_HOME/bin/java -jar db-purge-tool.jar -g

    where CSA_JRE_HOME is the directory in which the JRE that is used by CSA is installed.

  3. In the current directory, copy the sample configuration file that corresponds to the type of database you are using to a file named config.properties. For example, if you are using an Oracle database, make a copy of the config.properties.oracle file and rename it to config.properties. Update the content of config.properties as needed, as described in the table:

    Property Name Description
    jdbc.
    driver
    ClassName

    The JDBC driver class.

    Example

    Oracle: jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
    MS SQL: jdbc.driverClassName=net.sourceforge.jtds.jdbc.Driver
    PostgreSQL: jdbc.driverClassName=org.postgresql.Driver
    jdbc.dialect

    The classname that allows JDBC to generate optimized SQL for a particular database.

    Example

    Oracle: jdbc.dialect=org.hibernate.dialect.OracleDialect
    MS SQL: jdbc.dialect=org.hibernate.dialect.SQLServerDialect
    PostgreSQL: jdbc.dialect=org.hibernate.dialect.PostgreSQLDialect
    jdbc.
    databaseUrl

    The JDBC URL. When specifying an IPv6 address, it must be enclosed in square brackets (see examples below).

    Example

    Oracle (TLS not enabled): jdbc.databaseUrl=jdbc:oracle:thin:@//127.0.0.1:1521/XE

    Oracle (TLS not enabled, using an IPv6 address): jdbc.databaseUrl=jdbc:oracle:thin:@//[f000:253c::9c10:b4b4]:1521/XE

    Oracle (TLS enabled, CSA does not check the database DN): jdbc.databaseUrl=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST= (ADDRESS=(PROTOCOL = TCPS)(HOST = <host>)(PORT = 1521))) (CONNECT_DATA =(SERVICE_NAME = ORCL))) where <host> is the name of the system on which the Oracle database server is installed.

    Oracle (TLS enabled, CSA checks the database DN): jdbc.databaseUrl=jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = <host>)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL))(SECURITY=(SSL_SERVER_CERT_DN="CN=abc,OU=dbserver,O=xyz,L=Sunnyvale,ST=CA,C=US")))
    where <host> is the name of the system on which the Oracle database server is installed and the values for SSL_SERVER_CERT_DN are for the DN of the Oracle database server.

    MS SQL (TLS not enabled): jdbc.databaseUrl=jdbc:jtds:sqlserver://127.0.0.1:1433/
     example;ssl=request

    MS SQL (TLS not enabled, using an IPv6 address): jdbc.databaseUrl=jdbc:jtds:sqlserver://[::1]:1433/
     example;ssl=request

    MS SQL (TLS enabled): jdbc.databaseUrl=jdbc:jtds:sqlserver://127.0.0.1:1433/
     example;ssl=authenticate

    MS SQL (FIPS 140-2 compliant): jdbc.databaseUrl=jdbc:jtds:sqlserver://127.0.0.1:1433/
     example;ssl=authenticate

    PostgreSQL: jdbc.databaseUrl=jdbc:postgresql://127.0.0.1:5432/csadb
    securityAdminPassword

    The password for the CSA admin user. Required for the purge tool. The password should be encrypted (see Encrypt a password for instructions). An encrypted password is preceded by ENC without any separating spaces and is enclosed in parentheses.

    Example

    securityAdminPassword=ENC(1v72sEeKj0cDCRxSqZV23w==)

    jdbc.
    username    		 The user name of the database user you configured for CSA after installing the database.
    jdbc.
    password

    The password for the database user. The password should be encrypted (see Encrypt a password for instructions). An encrypted password is preceded by ENC without any separating spaces and is enclosed in parentheses.

    On Windows, if you have configured CSA to be FIPS 140-2 compliant, encrypt this password after you have configured CSA to be FIPS 140-2 compliant (that is, you should use the updated encryption tools to encrypt the password).

    Example

    jdbc.password=ENC(fc5e38d38a5703285441e7fe7010b0)

    idmConfig.Url

    The system on which CSA is installed.

    Default: https://127.0.0.1:8444
    securityTransport.
    UserName

    The user used to authenticate legacy REST API calls.

    Default:csaTransportUser

    securityTransport.
    password

    The password for the user used to authenticate legacy REST API calls. The password should be encrypted (see Encrypt a password for instructions). An encrypted password is preceded by ENC without any separating spaces and is enclosed in parentheses.

    Note Passwords generated in the config.properties file are sample passwords. You must sync the passwords with the passwords in the csa.properties file.


    On Windows, if you have configured CSA to be FIPS 140-2 compliant, encrypt this password after you have configured CSA to be FIPS 140-2 compliant (that is, you should use the updated encryption tools to encrypt the password).

    Example

    securityTransport.password=
    ENC(rlbE8430uFSDljert85441e7fe70ljkY)

    securityIdmTransport.
    UserName

    The user name to authenticate with Identity Management component.

    Default: idmTransportUser

    securityIdmTransport.
    password

    The password to authenticate with Identity Management component. The password should be encrypted (see Encrypt a password for instructions). An encrypted password is preceded by ENC without any separating spaces and is enclosed in parentheses.

    Note Passwords generated in the config.properties file are sample passwords. You must sync the passwords with the passwords in the csa.properties file.

    On Windows, if you have configured CSA to be FIPS 140-2 compliant, encrypt this password after you have configured CSA to be FIPS 140-2 compliant (that is, you should use the updated encryption tools to encrypt the password).

    Example

    securityIdmTransport.password=ENC
    (lDdh98Kfe76op8lhjE0El897klRCB532lsb)

    age.in.days.
    to.purge.
    audit

    The age of audit data, in days, that the audit data must be equal to or older than to be deleted by this tool.

    Default: 400
    age.in.days.
    to.purge.
    subscription

    The amount of time, in days, a subscription has been in a canceled, expired, failed, or retired state before it is deleted by this tool.

    Default: 400

    Example config.properties content

    Oracle (TLS not enabled)
    jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
    jdbc.databaseUrl=jdbc:oracle:thin:@//127.0.0.1:1521/XE
    jdbc.username=csa
    jdbc.password=ENC(fc5e38d38a5703285441e7fe7010b0)
    jdbc.dialect=org.hibernate.dialect.OracleDialect
    idmConfig.Url=https://127.0.0.1:8444
    securityTransportUserName=csaTransportUser
    securityTransport.password=ENC(rlbE8430uFSDljert85441e7fe70ljkY)
    securityIdmTransportUserName=idmTransportUser
    securityIdmTransport.password=ENC(lDdh98Kfe76op8lhjE0El897klRCB532lsb)
    age.in.days.to.purge.audit=400
    age.in.days.to.purge.subscription=400

    MS SQL (TLS not enabled)
    jdbc.driverClassName=net.sourceforge.jtds.jdbc.Driver
    jdbc.databaseUrl=jdbc:jtds:sqlserver://127.0.0.1:1433/example;ssl=request
    jdbc.username=csa
    jdbc.password=ENC(fc5e38d38a5703285441e7fe7010b0)
    jdbc.dialect=org.hibernate.dialect.SQLServerDialect
    idmConfig.Url=https://127.0.0.1:8444
    securityTransportUserName=csaTransportUser
    securityTransport.password=ENC(rlbE8430uFSDljert85441e7fe70ljkY)
    securityIdmTransportUserName=idmTransportUser
    securityIdmTransport.password=ENC(lDdh98Kfe76op8lhjE0El897klRCB532lsb)
    age.in.days.to.purge.audit=400
    age.in.days.to.purge.subscription=400

    MS SQL (TLS enabled)
    jdbc.driverClassName=net.sourceforge.jtds.jdbc.Driver
    jdbc.databaseUrl=jdbc:jtds:sqlserver://127.0.0.1:1433/
     example;ssl=authenticate
    jdbc.username=csa
    jdbc.password=ENC(fc5e38d38a5703285441e7fe7010b0)
    jdbc.dialect=org.hibernate.dialect.SQLServerDialect

    MS SQL (FIPS 140-2 compliant on Windows)
    jdbc.driverClassName=net.sourceforge.jtds.jdbc.Driver
    jdbc.databaseUrl=jdbc:jtds:sqlserver://127.0.0.1:1433/
     example;ssl=authenticate
    jdbc.username=csa
    jdbc.password=ENC(fc5e38d38a5703285441e7fe7010b0)
    jdbc.dialect=org.hibernate.dialect.SQLServerDialect
    idmConfig.Url=https://127.0.0.1:8444
    securityTransportUserName=csaTransportUser
    securityTransport.password=ENC(rlbE8430uFSDljert85441e7fe70ljkY)
    securityIdmTransportUserName=idmTransportUser
    securityIdmTransport.password=ENC(lDdh98Kfe76op8lhjE0El897klRCB532lsb)
    age.in.days.to.purge.audit=400
    age.in.days.to.purge.subscription=400

    PostgreSQL
    jdbc.driverClassName=org.postgresql.Driver
    jdbc.databaseUrl=jdbc:postgresql://127.0.0.1:5432/csadb
    jdbc.username=csadbuser
    jdbc.password=ENC(fc5e38d38a5703285441e7fe7010b0)
    jdbc.dialect=org.hibernate.dialect.PostgreSQLDialect
    idmConfig.Url=https://127.0.0.1:8444
    securityTransportUserName=csaTransportUser
    securityTransport.password=ENC(rlbE8430uFSDljert85441e7fe70ljkY)
    securityIdmTransportUserName=idmTransportUser
    securityIdmTransport.password=ENC(lDdh98Kfe76op8lhjE0El897klRCB532lsb)
    age.in.days.to.purge.audit=400
    age.in.days.to.purge.subscription=400

  4. Run the following command to delete subscriptions and audit data (you can specify options to delete only subscriptions or only audit data):

    Caution THE PURGE TOOL RUNS WITHOUT PROMPTING FOR A CONFIRMATION.

    Deleted subscriptions and audit data cannot be restored unless you have backed up the database.

    Verify that you have entered the correct information into the config.properties file before running this tool.

    Note When running the tool to delete subscriptions or audit data, you will be prompted for a username and password. This user MUST be assigned to the CSA Administrator role. Users who are not assigned to this role cannot delete subscriptions nor audit data.

    Oracle (TLS not enabled)

    • Windows:

      "CSA_JRE_HOME\bin\java" -jar db-purge-tool.jar ‑j ojdbc6.jar

    • Linux:

      CSA_JRE_HOME/bin/java -jar db-purge-tool.jar ‑j ojdbc6.jar

    where ojdbc6.jar is the name of the Oracle JDBC driver installed in CSA_HOME\Tools\DBPurgeTool and CSA_JRE_HOME is the directory in which the JRE that is used by CSA is installed.

    Oracle (TLS enabled, CSA does not check the database DN, client authentication is enabled on the Oracle database server)

    • Windows:
      "CSA_JRE_HOME\bin\java"
      -Djavax.net.ssl.keyStore="<certificate_key_file>"
      -Djavax.net.ssl.keyStorePassword=<certificate_key_file_password>
      -Djavax.net.ssl.keyStoreType=<certificate_key_file_type>
      -jar db-purge-tool.jar ‑j ojdbc6.jar
    • Linux:

      CSA_JRE_HOME/bin/java
      -Djavax.net.ssl.keyStore="<certificate_key_file>"
      -Djavax.net.ssl.keyStorePassword=<certificate_key_file_password>
      -Djavax.net.ssl.keyStoreType=<certificate_key_file_type>
      -jar db-purge-tool.jar ‑j ojdbc6.jar

      where ojdbc6.jar is the name of the Oracle JDBC driver installed in CSA_HOME\Tools\DBPurgeTool, certificate_key_file is the same keystore file defined by the certificate-key-file attribute in the ssl element of the CSA_HOMEjboss-as/standalone/configuration/standalone.xml file (for example, CSA_HOME/jboss-as/standalone/configuration/.keystore), certificate_key_file_password is the password to the keystore file (for example, changeit), certificate_key_file_type is the keystore type (for example, JKS or PKCS12), and CSA_JRE_HOME is the directory in which the JRE that is used by CSA is installed.

    Oracle (TLS enabled, CSA does not check the database DN, client authentication is NOT enabled on the Oracle database server)

    • Windows:

      "CSA_JRE_HOME\bin\java" -jar db-purge-tool.jar ‑j ojdbc6.jar

    • Linux:

      CSA_JRE_HOME/bin/java -jar db-purge-tool.jar ‑j ojdbc6.jar

      where ojdbc6.jar is the name of the Oracle JDBC driver installed in CSA_HOME\Tools\DBPurgeTool and CSA_JRE_HOME is the directory in which the JRE that is used by CSA is installed. .

    Oracle (TLS enabled, CSA checks the database DN, client authentication is enabled on the Oracle database server)

    • Windows:
      "CSA_JRE_HOME\bin\java"
      -Doracle.net.ssl_server_dn_match=true
      -Djavax.net.ssl.keyStore="<certificate_key_file>"
      -Djavax.net.ssl.keyStorePassword=<certificate_key_file_password>
      -Djavax.net.ssl.keyStoreType=<certificate_key_file_type>
      -jar db-purge-tool.jar ‑j ojdbc6.jar
    • Linux:

      CSA_JRE_HOME/bin/java
      -Doracle.net.ssl_server_dn_match=true
      -Djavax.net.ssl.keyStore="<certificate_key_file>"
      -Djavax.net.ssl.keyStorePassword=<certificate_key_file_password>
      -Djavax.net.ssl.keyStoreType=<certificate_key_file_type>
      -jar db-purge-tool.jar ‑j ojdbc6.jar

      where ojdbc6.jar is the name of the Oracle JDBC driver installed in CSA_HOME\Tools\DBPurgeTool,certificate_key_file is the same keystore file defined by the certificate-key-file attribute in the ssl element of the CSA_HOME\jboss-as\standalone\configuration\standalone.xml file (for example, CSA_HOME\jboss-as\standalone\configuration\.keystore, certificate_key_file_password is the password to the keystore file (for example, changeit), certificate_key_file_type is the keystore type (for example, JKS or PKCS12), and CSA_JRE_HOME is the directory in which the JRE that is used by CSA is installed.

    Oracle (TLS enabled, CSA checks the database DN, client authentication is NOT enabled on the Oracle database server)

    • Windows:

      "CSA_JRE_HOME\bin\java"
      -Doracle.net.ssl_server_dn_match=true -jar db-purge-tool.jar ‑j ojdbc6.jar

    • Linux:

      CSA_JRE_HOME/bin/java
      -Doracle.net.ssl_server_dn_match=true -jar db-purge-tool.jar ‑j ojdbc6.jar

      where ojdbc6.jar is the name of the Oracle JDBC driver installed in CSA_HOME/Tools/DBPurgeTool and CSA_JRE_HOME is the directory in which the JRE that is used by CSA is installed. .

    MS SQL and PostgreSQL

    • Windows:

      "CSA_JRE_HOME\bin\java" -jar db-purge-tool.jar

    • Linux:

      CSA_JRE_HOME/bin/java -jar db-purge-tool.jar

      where CSA_JRE_HOME is the directory in which the JRE that is used by CSA is installed.

    The following options are available in the purge tool

    Option Description
    -jar db-purge-tool.jar Required. The name of the tool to run.

    -a, --audit

    Optional. Purge audit data. If neither -a nor -s are specified, the tool purges both audit data and subscriptions.

    Note When running the tool to delete subscriptions or audit data, you will be prompted for a username and password. This user MUST be assigned to the CSA Administrator role. Users who are not assigned to this role cannot delete subscriptions nor audit data.
    -c <config_properties>, --config <config_properties> Optional. The name and location of the configuration properties file. By default, the tool looks for the configuration properties file in the working directory (the directory from which the tool is run). If this option is not specified, the tool looks for the config.properties in the working directory. The examples in this document assume the file is located in the working directory and is named config.properties.
    -g, --generate Optional. Generate example configuration properties files for supported databases.

    -h, --help

    Optional. List the options available in this tool.
    -j <jdbc_drivers>,
    --jars <jdbc_drivers>

    Optional. The name and location of the JDBC driver(s) to be used by this tool. If more than one driver needs to be specified, separate each driver by a space. By default, the tool looks for the JDBC driver(s) in the working directory (the directory from which the tool is run). If you are not running the tool from CSA_HOME/Tools/DBPurgeTool, specify the name and location of the JDBC driver(s) to be used. On Windows, if the path name contains a space, the path and file name should be enclosed in quotation marks. For example: ‑j "C:\Program Files/jdbc\ojdbc6.jar"

    For a list of supported JDBC driver versions, see the Cloud Service Automation System and Software Support Matrix.

    -s, --subscription

    Optional. Purge subscription data. If neither -s nor -a are specified, the tool purges both subscriptions and audit data.

    Note When running the tool to delete subscriptions or audit data, you will be prompted for a username and password. This user MUST be assigned to the CSA Administrator role. Users who are not assigned to this role cannot delete subscriptions nor audit data.

    Examples for Oracle (TLS is not Enabled)

    Display the purge tool help:

    • Windows:
      "CSA_JRE_HOME\bin\java" -jar db-purge-tool.jar ‑h ‑j ojdbc6.jar
    • Linux:

      CSA_JRE_HOME/bin/java -jar db-purge-tool.jar ‑h ‑j ojdbc6.jar

    Generate sample configuration properties files: \

    • Windows:
      "CSA_JRE_HOME\bin\java" -jar db-purge-tool.jar ‑g ‑j ojdbc6.jar
    • Linux:

      CSA_JRE_HOME/bin/java -jar db-purge-tool.jar ‑g ‑j ojdbc6.jar

    Purge subscriptions and associated entities:

    • Windows:
      "CSA_JRE_HOME\bin\java" -jar db-purge-tool.jar ‑s ‑j ojdbc6.jar
    • Linux:

      CSA_JRE_HOME/bin/java -jar db-purge-tool.jar ‑s ‑j ojdbc6.jar

    Purge audit data:

    • Windows:

      "CSA_JRE_HOME\bin\java" -jar db-purge-tool.jar ‑a ‑j ojdbc6.jar

    • Linux:

      CSA_JRE_HOME/bin/java -jar db-purge-tool.jar ‑a ‑j ojdbc6.jar

    Purge subscriptions and associated entities and audit data:

    • Windows:

      "CSA_JRE_HOME\bin\java" -jar db-purge-tool.jar ‑j ojdbc6.jar

    • Linux:

      CSA_JRE_HOME/bin/java -jar db-purge-tool.jar ‑j ojdbc6.jar

    Examples for PostgreSQL

    Display the purge tool help:

    • Windows:

      "CSA_JRE_HOME\bin\java" -jar db-purge-tool.jar ‑h

    CSA_JRE_HOME/bin/java -jar db-purge-tool.jar ‑h

    Generate sample configuration properties files:

    Windows:
    "CSA_JRE_HOME\bin\java" -jar db-purge-tool.jar ‑g

    CSA_JRE_HOME/bin/java -jar db-purge-tool.jar ‑g

    Purge subscriptions and associated entities:

    • Windows:
      "CSA_JRE_HOME\bin\java" -jar db-purge-tool.jar ‑s

    CSA_JRE_HOME/bin/java -jar db-purge-tool.jar ‑s

    Purge audit data:

    • Windows:

      "CSA_JRE_HOME\bin\java" -jar db-purge-tool.jar ‑a

    CSA_JRE_HOME/bin/java -jar db-purge-tool.jar ‑a

    Purge subscriptions and associated entities and audit data:

    • Windows:
      "CSA_JRE_HOME\bin\java" -jar db-purge-tool.jar

    CSA_JRE_HOME/bin/java -jar db-purge-tool.jar

Send Help Center feedback