Repository tools command to generate data warehouse creation scripts

Edit online

Use the generateWarehouseDDLScripts command to generate the data warehouse DDL creation scripts.

Purpose

The generateWarehouseDDLScripts command generates the data warehouse script files (.sql) that you can use to create the data warehouse. One of the purposes of generating script files is to create 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_generateWarehouseDDLScripts.log
noPrompt Do not prompt before updating the tables. No N/A
outputFolder The data warehouse script output folder. No repotools-jts_generateWarehouseDDLScripts.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.

Procedure

  1. Start the server and run through the Jazz® Team Server setup wizard to the Configure Data Warehouse page.
  2. On the Configure Data Warehouse page, enter the following values:
    • Mandatory fields:
      1. Database Vendor
      2. JDBC Location
      3. JDBC Password
      4. Any other mandatory fields for the database vendor, for example Database Table Space Folder for Oracle or SQL server
    • Optional fields:
      • Schema Prefix
      • Table Space names
  3. Click Test Connectionand ensure you are able to connect to the database.
  4. Select the I do not wish to configure the data warehouse at this time check box at the top of the page.
  5. Click Next to go to the next page. This will save the preferences entered above to the Jazz Team Server teamserver.properties file.
  6. Stop Jazz Team Server.
  7. To generate the script files, open a command prompt and change the directory to JTS_Install_Dir/server.
  8. Run the following command and replace user ID and user password with your etlDbUser user ID and password:
    repotools-jts -generateWarehouseDDLScripts additionalOptions="noAdmin;etlDbUser:user ID;defaultPsswd:user password"

Output

This command produces the following output files in the server directory:
  • A log file called repotools-jts_generateWarehouseDDLScripts.log is generated with details of the command execution.
  • A folder with the SQL scripts called: repotools-jts_generateWarehouseDDLScripts.out/[db_vendor]. The SQL scripts are numbered and must be run in that order. For example:
1-setupCoreSpace.sql
2-createCoreSchema.sql
3-grantCoreSchemaReadAccess.sql
4-populateDateDimension.sql
5-setupCalmSpace.sql
6-createCalmSchema.sql
7-grantCalmSchemaReadAccess.sql
After the scripts are generated and run to create the data warehouse, start Jazz Team Server and return to the setup wizard and on the Configure Data Warehouse page, clear the I do not wish to configure the data warehouse at this time.

Ensure that the user in the connection string (JDBC location) is the same as the etlDbUser that was specified when generating the scripts. Test the connection, and complete the setup wizard.