Setting up an SQL Server database

Set up an SQL Server database to work with Jazz™ Team Server, IBM® Engineering Lifecycle Management applications, and Data Warehouse.
Important: In version 6.0.6.1 and later, you can partition a non-partitioned REPOSITORY.VERSION table in a configuration-enabled system. Database table partitioning helps manage the performance, availability, and scalability of large amounts of data (millions of artifacts) in a repository. To use the partitioning features, you must install an Enterprise edition of an SQL Server database. Standard, Workgroup, or Express editions of the database do not support partitioning.

This procedure requires that the following prerequisites have been met for the SQL Server:

Note: If you are using SQL server in a language other than English, you must change the language to English before you create the data warehouse:
  1. In SQL Server Management Studio, right-click the connection to the database server.
  2. From the pop-up window, select Properties.
  3. In the Server Properties window, select Advanced and set the Default language entry to English.
  4. Save your changes.

Setting up a database

Set up the SQL Server database by using the sqlcmd command line tool, which is provided with the SQL Server installation.
To activate the sqlcmd tool for Microsoft SQL Server Express edition:
  1. From the Start menu, locate Microsoft SQL Server, and click Configuration Tools > SQL Server Configuration Manager.
  2. In the SQL Server Configuration Manager window, in the left pane, click SQL Server Network Configuration > Protocols for SQLEXPRESS.
  3. Double-click the protocol Named Pipes.
  4. In the Name Pipes Properties dialog box, select Yes for the Enabled field.
  5. Change the Pipe Name as follows: \\.\pipe\sql\query.
  6. Click Apply; then, restart the SQL Server Services.
You can also use a visual tool, such as SQL Server Studio Management, which is also available for the Express® version as SQL Server Studio Management Express. See the SQL Server documentation or a SQL Server database administrator (DBA) for more information.
Important: If you install Jazz Team Server with the ELM applications either on the same computer or distributed platforms, a separate database and a database user who is associated with that database must be created.
  1. Create a database for each instance of the Jazz Team Server, Change and Configuration Management, Quality Management, Requirements Management, Data Collection Component, Lifecycle Query Engine, Link Index Provider, IBM Engineering Lifecycle Optimization - Engineering Insights, Global Configuration Management applications. Also create a database for each data warehouse associated with a Jazz Team Server. For example, if you have one CCM application, one QM application, one RM application, one Jazz Team Server, one DCC application, one LQE application, one LDX application, one ENI application, one GC application, and one data warehouse, you will need to create 12 databases.
    CREATE DATABASE jts 
    GO 
    CREATE DATABASE ccm 
    GO 
    CREATE DATABASE qm 
    GO 
    CREATE DATABASE rm 
    GO 
    CREATE DATABASE dcc 
    GO 
    CREATE DATABASE lqe 
    GO 
    CREATE DATABASE ldx 
    GO 
    CREATE DATABASE eni 
    GO 
    CREATE DATABASE gc 
    GO 
    CREATE DATABASE dw 
    GO 
  2. Create a user and password and change the ownership of the database by entering these commands:
    CREATE LOGIN jtsDBuser
    WITH PASSWORD = 'jtsDBpswd';
    USE jts;
    exec sp_changedbowner 'jtsDBuser'
    GO 
    CREATE LOGIN ccmDBuser
    WITH PASSWORD = 'ccmDBpswd';
    USE ccm;
    exec sp_changedbowner 'ccmDBuser'
    GO 
    CREATE LOGIN qmDBuser
    WITH PASSWORD = 'qmDBpswd';
    USE qm;
    exec sp_changedbowner 'qmDBuser'
    GO 
    CREATE LOGIN rmDBuser
    WITH PASSWORD = 'rmDBpswd';
    USE rm;
    exec sp_changedbowner 'rmDBuser'
    GO 
    CREATE LOGIN dccDBuser
    WITH PASSWORD = 'dccDBpswd';
    USE dcc;
    exec sp_changedbowner 'dccDBuser'
    GO 
    CREATE LOGIN lqeDBuser
    WITH PASSWORD = 'lqeDBpswd';
    USE lqe;
    exec sp_changedbowner 'lqeDBuser'
    GO 
    CREATE LOGIN ldxDBuser
    WITH PASSWORD = 'ldxDBpswd';
    USE ldx;
    exec sp_changedbowner 'ldxDBuser'
    GO 
    CREATE LOGIN eniDBuser
    WITH PASSWORD = 'eniDBpswd';
    USE eni;
    exec sp_changedbowner 'eniDBuser'
    GO 
    CREATE LOGIN gcDBuser
    WITH PASSWORD = 'gcDBpswd';
    USE gc;
    exec sp_changedbowner 'gcDBuser'
    GO 
    CREATE LOGIN dwDBuser
    WITH PASSWORD = 'dwDBpswd';
    USE dw;
    exec sp_changedbowner 'dwDBuser'
    GO 
  3. Change the collation of the databases. ELM databases require case-sensitivity and the default for SQL Server is case insensitive collation. From the command line tool, enter these commands. Note that SQL_Latin1_General_CP437_CS_AS is an example and any CS_AS collation can be used:
    ALTER DATABASE jts COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE ccm COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE qm COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE rm COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE dcc COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE lqe COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE ldx COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE eni COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE gc COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE dw COLLATE SQL_Latin1_General_CP1_CS_AS
    GO
  4. Change the row version system for the JTS, CCM, QM, RM, DCC, LQE, LDX, ENI, GC, and DW databases. From the command line tool, run these commands:
    Note: Ensure that the connection to the database is open before running these commands.
    ALTER DATABASE jts SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE ccm SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE qm SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE rm SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE dcc SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE lqe SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE ldx SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE eni SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE gc SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE dw SET READ_COMMITTED_SNAPSHOT ON
    GO
  5. Create an environment variable named SQLSERVER_JDBC_DRIVER_FILE and point to the JDBC driver. The location of the driver varies depending on your installation. For Windows operating system
    1. Click Start > Control Panel > System.
    2. Click the Advanced tab, and then click Environment Variables.
    3. In the System variables list, click New.
    4. In the Variable name enter SQLSERVER_JDBC_DRIVER_FILE, and in the Variable value enter C:\<Path_to_JDBC_driver>\sqljdbc42.jar, where <Path_to_JDBC_driver> is the file path to the computer that host the applications and not the database.
    For Unix operating systems
    1. On Unix systems, add the following environment variable declaration:
      SQLSERVER_JDBC_DRIVER_FILE =.:Path_to_JDBC_driver/sqljdbc42.jar
    Note: If you are using WebSphere® Application Server, you must also add a custom property that points to the JDBC driver. For more information, see Setting up WebSphere Application Server.
  6. To configure your databases connections and create database tables complete one of these steps:
    1. If you are using WebSphere Liberty, start the server and continue with Running the setup by using Custom setup in the setup wizard.
    2. If you are deploying WebSphere Application Server, see Deploying and starting WebSphere Application Server, and then continue with Running the setup by using Custom setup in the setup wizard.
    3. If you prefer to manually modify the teamserver.properties file for database connection and run the repotools commands to create database tables, see Manually configuring an SQL Server database.
    4. If you are upgrading from a previous version, continue to run the appropriate upgrade scripts which will migrate your existing database connections.

Troubleshooting

If the repotools command does not work, check the appropriate log files, which are located in the JazzInstallDir/server directory.

Also, verify that these conditions are correct:

  • The SQL Server is configured to allow connections that use the TCP/IP protocol. If required, restart the SQL service.
  • The SQL Server computer host name, and the port is correctly reflected in the teamserver.properties files. For the LQE and LDX applications the properties file is dbconnection.properties.
  • The login name, password, and database name are correctly reflected in the teamserver.properties files.
  • A firewall does not prevent you from accessing the SQL Server. Use a Telnet command to connect to the server using the host name and port from the teamserver.properties file.
  • The created user has the correct permissions in the Jazz Team Server database.
  • The JDBC driver environment variables are properly configured for your environment.
Note: The database code for Jazz Team Server was designed to be generic and standard in order to run on multiple database platforms; however, differences between the enterprise databases exist in syntax, optimization strategies, and locking semantics. The server has been tested on SQL Server using a combination of automated tests and simulations of normal workloads and stress testing. The simulation of normal workloads showed acceptable performance. In some instances, database deadlocks have been observed in automated concurrency tests; however, these have not been consistently reproduced on different server hardware.

The server is designed to handle a large number of simultaneous requests. In exceptional cases, deadlocks can occur in the repository database, as similar or equal objects are updated concurrently in transactions that are associated with the requests. These are rare, but typical events. The system is designed to be defensive against deadlocks, and data integrity is maintained; there is no loss of data. Occasionally, deadlocks are logged by tasks, such as build engine execution. Generally, the condition is detected, and the task is retried. A deadlock can result in a failed build. In very rare cases, a user action, such as working with work items or source code, is stopped to resolve a deadlock. In that case, the user action fails and an error message with details regarding the deadlock is displayed to the user. The user can then try the action again.

All deadlocks are logged on the server to provide information about what happened when the deadlock occurred. The log entry has text similar to the following entry: com.ibm.team.repository.common.RetryableDatabaseException: Serialization failure.

If you encounter frequent deadlocks, contact your product support or check the forums on jazz.net for the latest information, updates, or mitigating techniques.

Setting up a Jazz Authorization Server MS SQL Server database

Jazz Authorization Server is configured to use the default Apache Derby database, but you might want to use an enterprise database such as MS SQL Server in your Jazz Authorization Server environment.
  1. Create a file called createOauthMSSQL.sql. Refer to the following SQL example to create the database and tables for Jazz Authorization Server. You can use a different database name than oauth2db, but you must use the OAuthDBSchema schema. Also note that these values are examples and you should change them according to your usage and environment. For example, if the TOKENSTRING value of 2048 is too small, you can increase that by using a datatype of TOKENSTRING NVARCHAR(MAX) NOT NULL,.
    -- Manually create DATABASE using object explorer and provide name as OAUTH2DB2;
    
    USE OAUTH2DB2;
    GO
    
    CREATE SCHEMA OAUTHDBSCHEMA;
    GO
    
    ---- CREATE TABLES ----
    CREATE TABLE OAUTHDBSCHEMA.OAUTH20CACHE
    (
    LOOKUPKEY VARCHAR(256) NOT NULL, 
    UNIQUEID VARCHAR(128) NOT NULL,
    COMPONENTID VARCHAR(256) NOT NULL,
    TYPE VARCHAR(64) NOT NULL, 
    SUBTYPE VARCHAR(64), 
    CREATEDAT BIGINT,
    LIFETIME INT, 
    EXPIRES BIGINT, 
    TOKENSTRING VARCHAR(2048) NOT NULL,
    CLIENTID VARCHAR(64) NOT NULL,
    USERNAME VARCHAR(64) NOT NULL, 
    SCOPE VARCHAR(512) NOT NULL, 
    REDIRECTURI VARCHAR(2048), 
    STATEID VARCHAR(64) NOT NULL,
    EXTENDEDFIELDS NVARCHAR(2048) NOT NULL DEFAULT '{}'
    );
    GO
    
    CREATE TABLE OAUTHDBSCHEMA.OAUTH20CLIENTCONFIG 
    (
    COMPONENTID VARCHAR(256) NOT NULL, 
    CLIENTID VARCHAR(256) NOT NULL, 
    CLIENTSECRET VARCHAR(256), 
    DISPLAYNAME VARCHAR(256) NOT NULL,
    REDIRECTURI VARCHAR(2048), 
    ENABLED INT,
    CLIENTMETADATA NVARCHAR(2048) NOT NULL DEFAULT '{}'
    );
    GO
    
    CREATE TABLE OAUTHDBSCHEMA.OAUTH20CONSENTCACHE (
    CLIENTID VARCHAR(256) NOT NULL,
    USERID VARCHAR(256),
    PROVIDERID VARCHAR(256) NOT NULL,
    SCOPE VARCHAR(1024) NOT NULL,
    EXPIRES BIGINT,
    EXTENDEDFIELDS NVARCHAR(2048) NOT NULL DEFAULT '{}'
    );
    GO
    
    ---- ADD CONSTRAINTS ----
    ALTER TABLE OAUTHDBSCHEMA.OAUTH20CACHE
    ADD CONSTRAINT PK_LOOKUPKEY PRIMARY KEY (LOOKUPKEY);
    GO
    
    ALTER TABLE OAUTHDBSCHEMA.OAUTH20CLIENTCONFIG 
    ADD CONSTRAINT PK_COMPIDCLIENTID PRIMARY KEY (COMPONENTID,CLIENTID);
    GO
    
    ---- CREATE INDEXES ----
    CREATE INDEX OAUTH20CACHE_EXPIRES ON OAUTHDBSCHEMA.OAUTH20CACHE (EXPIRES ASC);
    GO
    
    ---- GRANT PRIVILEGES ----
    ---- UNCOMMENT THE FOLLOWING IF YOU USE AN ACCOUNT 
    OTHER THAN ADMINISTRATOR FOR DB ACCESS ----
    
    -- Change dbuser to the account you want to use to access your database
    --GRANT ALL ON OAUTHDBSCHEMA.OAUTH20CACHE TO dbuser;
    --GRANT ALL ON OAUTHDBSCHEMA.OAUTH20CLIENTCONFIG TO dbuser;
    --GRANT ALL ON OAUTHDBSCHEMA.OAUTH20CONSENTCACHE TO dbuser;
    
    ---- END OF GRANT PRIVILIGES ----
  2. Open a sqlcmd command line tool and run the following script to create the database, tables, and indexes, and to grant privileges:
    createOauthMSSQL.sql
    GO

    The script will create a database called oauth2db, and then create tables in the schema OAuthDBSchema. The tables require a buffer pool that has a page size of at least 8K. The SQL script creates the necessary buffer pool and table spaces, but you can skip this if your buffer pools use page sizes of 8K or greater already.

  3. After creating the database tables, you must configure Jazz Authorization Server to use the tables. Go to the Jazz Authorization Server installation directory and open the appConfig.xml file for editing. The default path to the appConfig.xml file on Windows is C:\IBM\JazzAuthServer\wlp\usr\servers\jazzop and on Linux is /opt/IBM/JazzAuthServer/wlp/usr/servers/jazzop.
  4. Comment out the following Apache Derby database section:
    <dataSource id="OAuthFvtDataSource" jndiName="jdbc/OAuth2DB">
            <jdbcDriver libraryRef="DerbyLib" />
            <properties.derby.embedded
                databaseName="asDB"
                createDatabase="create" />
        </dataSource>
    
        <library id="DerbyLib">
            <fileset dir="${shared.config.dir}/lib/global" includes="derby.jar" />
        </library>
  5. Add the following section to configure a SQL database. You must customize this section to work with your specific SQL server:
    <dataSource id="OAUTH2DBDS" jndiName="jdbc/OAUTH2DB">
    <jdbcDriver libraryRef="MSJDBCLib"/>
    <properties.microsoft.sqlserver user="username" password="Password" databaseName="OAUTH2DB" serverName="SQLDBServer" portNumber="1433"/>
    </dataSource>
    <library id="MSJDBCLib">
    <fileset dir="${shared.config.dir}/lib/global" id="sqljdbc42" includes="sqljdbc42.jar"/>
    </library>
    Ensure that you have the correct values for the following attributes:
    • For user, provide the name of the MS SQL Server user that is used to connect to the database.
    • For password, use the MS SQL Server user password.
    • For databaseName, use the database name you created in the previous step. If you used the sample script file, the database name is OAUTH2DB.
    • For portNumber, use the port number that the MS SQL Server instance is running on. The default port number is 1433.
    • For serverName, enter the host name of the MS SQL Server.
  6. In the oauthProvider section of the appConfig.xml file, update the databaseStore property so it specifies the MS SQL Server data source rather than the default Apache Derby data source. The databaseStore property should specify the name of the MS SQL Server data source, which in this example, is OAUTH2DBDS:
    <oauthProvider id="JazzOP"
    httpsRequired="true"
    autoAuthorize="true"
    customLoginURL="/jazzop/form/login"
    accessTokenLifetime="7201" 
    authorizationGrantLifetime="604801">
    <autoAuthorizeClient>client01</autoAuthorizeClient>
    <databaseStore dataSourceRef="OAUTH2DBDS" /> 
    </oauthProvider>
  7. Save and close the appConfig.xml file.
  8. Copy the MS SQL Server sqljdbc42.jar JDBC drivers from your MS SQL Server server to this directory: C:\IBM\JazzAuthServer\wlp\usr\shared\config\lib\global.

video icon Video

Jazz.net channel
Software Education channel

learn icon Courses

IoT Academy
Skills Gateway

ask icon Community

Jazz.net
Jazz.net forums
Jazz.net library

support icon Support

IBM Support Community
Deployment wiki