Install Codar database schema

The schema installation tool is used to upgrade the existing Codar database schema or install a fresh database schema without re-installing Codar. Use this tool if you did not install Codar database components onto the database during installation, did not upgrade the database schema during an upgrade, or if you want to drop the existing schema and install a fresh Codar database schema. You can also use this tool to complete an upgrade if the upgrade failed, the database schema was not updated, the failure was not due to a database problem, and the problem can be fixed without rerunning the upgrade installer. For example, if the upgrade failed but can be completed successfully by manual configuration but the database schema was not updated, you can simply make the manual changes to complete the upgrade and run the schema installation tool instead of reverting Codar back to the previous version and running the upgrade installer again.

Note Do not run this tool if you installed the database components during the installation of Codar or if you upgraded the database schema when you upgraded Codar.

If you run this tool on an existing schema (where Codar has been upgraded but the database schema was not upgraded), the schema is upgraded and no data in the database is lost. However, if you drop the existing schema and run this tool, all data in the database associated with the dropped schema is lost. Once you run the tool, a fresh schema is installed and you cannot revert back to the dropped schema.

Caution Once you drop an existing schema and run the database schema installation tool, you cannot revert back to the dropped schema.

Upgrade or install database schema

To upgrade or install a fresh Codar database schema, complete the following steps:

  1. If Codar is running, stop Codar. See Stop Codar.

  2. Change to the CSA_HOME\Tools\SchemaInstallationTool\ directory.

  3. During upgrade or installation of Codar, a file named db.properties is generated in CSA_HOME\Tools\SchemaInstallationTool\. Verify the property values in this file. If you changed any database property values in the CSA_HOME\jboss-as\
    standalone\configuration\standalone.xml     file after installation, the values in db.properties may not be up‑to‑date.

    If you have dropped the existing database schema and are installing a fresh database schema after upgrading to Codar 1.80, you must update the driverFiles property value. The properties defined in db.properties are described in the following table.

    Property Name Description

    dbScriptsDir

    The location of database scripts installed with Codar used by the tool. If you are running a fresh installation of Codar 1.80 (you did not upgrade to Codar 1.80), you do not need to change these values.

    If you have upgraded to Codar 1.80 and want to upgrade the existing schema, you do not need to change these values.

    If you have upgraded to Codar 1.80, have dropped the existing database schema, and are installing a fresh database schema, you must update this value to the following:

    Oracle: (upgrade and dropped schema only)
    dbScriptsDir=CSA_HOME\scripts\freshinstallscripts\
    oracle

    PostgreSQL: (upgrade and dropped schema only)
    dbScriptsDir=CSA_HOME\scripts\freshinstallscripts\
    postgresql

    Microsoft SQL: (upgrade and dropped schema only)
    dbScriptsDir=CSA_HOME\scripts\freshinstallscripts\
    mssql
    dbUrl

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

    Examples

    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, Codar 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, Codar 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/codardb
    dbUserName The user name of the database user you configured for Codar after installing the database.
    dbPassword

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

    While you may enter a password in clear text, after you run the tool, the clear text password is automatically replaced by an encrypted password.
    If you have configured Codar to be FIPS 140-2 compliant, encrypt this password after you have configured Codar to be FIPS 140-2 compliant (that is, you should use the updated encryption tools to encrypt the password).

    Example

    dbPassword=ENC(fc5e38d38a5703285441e7fe7010b0)

    driverFiles

    The database driver files used by this tool.

    • You do not need to change these values if:

      • You are running a fresh installation of Codar 1.80 (you did not upgrade to Codar 1.80).
      • You upgraded to Codar 1.80 and want to upgrade the existing schema.

    • You must update this value to the value shown below, if you upgraded to Codar 1.80, dropped the existing database schema, and are installing a fresh database schema:

      Oracle (upgrade and dropped schema only)
      driverFiles=CSA_HOME\scripts\schemainstallforupg\
      create-oracle-schema.sql,
      CSA_HOME\scripts\schemainstallforupg\
      create-oracle-topology-schema.sql,
      CSA_HOME\scripts\schemainstallforupg\oracle\
      seed_data_driver.sql,
      CSA_HOME\scripts\reporting\oracle\
      install_views_driver.sql,
      CSA_HOME\scripts\reporting\oracle\
      grant-reporting-user.sql

      PostgreSQL (upgrade and dropped schema only)
      driverFiles=CSA_HOME\scripts\schemainstallforupg\
      create-postgres-schema.sql,
      CSA_HOME\scripts\schemainstallforupg\
      create-postgres-topology-schema.sql,
      CSA_HOME\scripts\schemainstallforupg\postgres\
      seed_data_driver.sql,
      CSA_HOME\scripts\reporting\postgres\
      install_views_driver.sql,
      CSA_HOME\scripts\reporting\postgres\
      grant-reporting-user.sql

      Microsoft SQL (upgrade and dropped schema only)
      driverFiles=CSA_HOME/scripts/schemainstallforupg/
      alterdb.sql,
      CSA_HOME\scripts\schemainstallforupg\
      create-mssql-schema.sql,
      CSA_HOME\scripts\schemainstallforupg\
      create-mssql-topology-schema.sql,
      CSA_HOME\scripts\schemainstallforupg\
      mssql\seed_data_driver.sql,

      On Linux only:
      CSA_HOME\scripts\reporting\mssql\
      install_views_driver.sql,
      CSA_HOME\scripts\reporting\mssql\
      grant-reporting-user.sql

    Note Add the grant-reporting-user.sql file only if you have created the reporting database user for Codar.
    jdbcDriverClassName

    The JDBC driver class. Do not change this value.

    Examples

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

    The location of the JDBC driver(s) used by this tool. Do not change this value.

  4. Run the following command:

    Windows:

    • Oracle (TLS not enabled), MS SQL, and PostgreSQL
      "CSA_JRE_HOME\bin\java" ‑jar schema‑installation‑tool.jar

    • Oracle (TLS enabled, Codar does not check the database DN, client authentication is enabled on the Oracle database server)
      "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 schema-installation-tool.jar

      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.

      certificate_key_file_type is the keystore type (for example, JKS or PKCS12).

    • Oracle (TLS enabled, Codar does not check the database DN, client authentication is NOT enabled on the Oracle database server)
      "CSA_JRE_HOME\bin\java" ‑jar schema‑installation‑tool.jar

    • Oracle (TLS enabled, Codar checks the database DN, client authentication is enabled on the Oracle database server)
      "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 schema-installation-tool.jar

      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.

      certificate_key_file_type is the keystore type (for example, JKS or PKCS12).

    • Oracle (TLS enabled, Codar checks the database DN, client authentication is NOT enabled on the Oracle database server)
      "CSA_JRE_HOME\bin\java" ‑Doracle.net.ssl_server_dn_match=true
      -jar schema-installation-tool.jar

    Linux:

    • Oracle (TLS not enabled), MS SQL, and PostgreSQL
      CSA_JRE_HOME/bin/java ‑jar schema‑installation‑tool.jar

    • Oracle (TLS enabled, Codar does not check the database DN, client authentication is enabled on the Oracle database server)
      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 schema-installation-tool.jar

      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.

      certificate_key_file_type is the keystore type (for example, JKS or PKCS12).

    • Oracle (TLS enabled, Codar does not check the database DN, client authentication is NOT enabled on the Oracle database server)
      CSA_JRE_HOME/bin/java ‑jar schema‑installation‑tool.jar
    • Oracle (TLS enabled, Codar checks the database DN, client authentication is enabled on the Oracle database server)
      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 schema-installation-tool.jar

      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.

      certificate_key_file_type is the keystore type (for example, JKS or PKCS12).

    • Oracle (TLS enabled, Codar checks the database DN, client authentication is NOT enabled on the Oracle database server)
      CSA_JRE_HOME/bin/java ‑Doracle.net.ssl_server_dn_match=true
      -jar schema-installation-tool.jar

Send Help Center feedback