Repository tools command to generate data warehouse upgrade scripts

Use the generateWarehouseUpgradeScripts command to generate the data warehouse upgrade scripts .

Purpose

The generateWarehouseUpgradeScripts command generates the data warehouse script files (.sql) that you can use to upgrade the data warehouse. One of the purposes of generating script files is to upgrade data warehouse without DBA permission. See this Deployment wiki article for more details.

Parameters

Attribute Description Required Default
teamserver.properties Path to the teamserver.properties file. No conf/jts/teamserver.properties
logFile Path to the log file. No repotools-jts_generateWarehouseUpgradeScripts.log
noPrompt Do not prompt before updating the tables. No N/A
outputFolder The data warehouse script output folder. No repotools-jts_generateWarehouseUpgradeScripts.out
separator Character used to separate each statement in the generated script. No Default
additionalOptions Additional options for script generation. See the following section for details. No none

Additional options

The following options can be used with the additionalOptions parameter:

  • noAdmin (required): Indicates that the generated scripts can be run without DBA privileges.
  • etlDbUser (required): The user ID of the database user that is used to connect to the data warehouse and run the ETL jobs from IBM® Engineering Lifecycle Management (ELM). This user does not need to already exist in order to generate the scripts and also does not require to have DBA privileges.
  • defaultPsswd (optional): The password to be assigned to all automatically created data warehouse users. This value is optional. However, if a default password is not specified, then the following individual passwords must be set instead.
  • cfgPsswd (optional): The password to be assigned to the automatically created data warehouse user. This value must be provided if a default password is not specified.
  • calmPsswd (optional): The password to be assigned to the automatically created data warehouse user. This value must be provided if a default password is not specified.
  • dwPsswd (optional): The password to be assigned to the automatically created data warehouse user. This value must be provided if a default password is not specified.
  • odsPsswd (optional): The password to be assigned to the automatically created data warehouse user. This value must be provided if a default password is not specified.
  • assetPsswd (optional): The password to be assigned to the automatically created data warehouse user. This value must be provided if a default password is not specified.
  • schkPsswd (optional): The password to be assigned to the automatically created data warehouse user. This value must be provided if a default password is not specified.

Prerequisites

Ensure the following tasks are performed before starting the data warehouse upgrade:

  • The Jazz® Team Server teamserver.properties file has been migrated from the previous version and has the data warehouse connection details.
  • The data warehouse tables have not been upgraded.
  • Note: Do not make any changes to the connection details or to database user privileges before generating the scripts and upgrading the data warehouse. After the upgrade is complete, any required changes can be made.

Procedure

  1. To generate the script files, open a command prompt and change the directory to JTS_Install_Dir/server.
  2. Run the following command and replace user ID and user password with your etlDbUser user ID and password:
    repotools-jts generateWarehouseUpgradeScripts additionalOptions="noAdmin;etlDbUser:user ID;defaultPsswd:user password"
  3. The command generates the following scripts. Run the scripts in this order:
    • 1-upgradeCoreSchema.sql
    • 2-grantCoreSchemaReadAccess.sql
    • 3-populateDateDimension.sql
    • 4-upgradeCalmSchema.sql
    • 5-grantCalmSchemaReadAccess.sql
  4. Ensure that the connection string for the data warehouse in Jazz Team Server and the applications, references the same etlDbUser that was specified when generating the scripts.