Setting up a Db2 database

The IBM® Engineering Lifecycle Management products support IBM Db2® Enterprise Server Edition. For platforms that do not support Enterprise Server Edition, you can use IBM Db2 Workgroup Server Edition. You can obtain a trial download of one these Db2 editions or the free IBM Db2 Express Server edition for deployments of 50 developers or fewer from ibm.com®.

Before you begin

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 a Db2 database. Standard, Workgroup, or Express editions of the database do not support partitioning.
Note:

These instructions do not apply to the parts that are being installed or configured on the IBM i or z/OS® operating systems. On IBM i, the Db2 for i database is configured automatically. For z/OS, see Setting up a Db2 database on a z/OS system.

This procedure requires that the following prerequisites are met. Consult your Db2 documentation or a Db2 database administrator (DBA) for help.
  • If you are using Db2 pureScale® for workload balancing, additional settings are required. To enable this feature in WebSphere® Application Server, see Database transfer: Enable the Db2 pureScale load balancing feature. To learn more about Db2 pureScale®, see Configuration of Db2 on Linux, UNIX, and Windows systems workload balancing support for Java clients.
  • The databases are not partitioned. Partitioned databases are not supported in this release and must not be used.
  • You have the correct user password. In the UNIX systems, get the password for the Db2 instance owner, which is typically the db2inst1 user.
  • You have reviewed the Db2 documentation to verify that your system meets the requirements and is configured correctly.
  • A supported version of Db2 is installed and running on a computer to be used as the database server. This computer can be a different one from the one that the Jazz™ Team Server runs on. For a list of supported versions of Db2, see Hardware and software requirements.
  • The user in the teamserver.properties file has system administrator authority over the database. For information about preparing a Db2 database with only DBADM authority, see this wiki page.
    On Linux, to get system administrator authority on the Db2 database, use the sudo command to get the db2inst1 Db2 user and run the bash command, where db2inst1 is the default user. You can use the following commands to check and maintain your database:

    db2start: Starts Db2 (on UNIX, the instance owner must run this command).

    db2stop: Stops Db2.

    db2sysc process: Checks whether Db2 processes are running. For average database use, you must have a minimum of 2 GB RAM available for Db2 processes. In a production environment, consider installing more RAM.

    reorg indexes/table: Reorganizes all indexes that are defined on a table by rebuilding the index data into unfragmented, physically contiguous pages. The table option reorganizes a table by reconstructing the rows to eliminate fragmented data and by compacting information.

    runstats: Updates statistics about the physical characteristics of a table and the associated indexes. These characteristics include the number of records, the number of pages, and the average record length. The optimizer uses these statistics when determining access paths to the data. Call this utility when a table has had many updates or after reorganizing a table.

    db2 backup: Backs up your database. When the command is complete, you have a new backup image that is located in the path or the directory from which the command was issued.

Note: Database reorg and runstats

This is a normal database administration task to be completed by a DBA. Whenever a large amount of data is added to a database, a reorg needs to be run and statistics (runstats) needs to be executed against the database tables. Running these commands (in Db2) ensures optimal performance to be realized when accessing the database. Refer to other supported DBMS (such as Oracle and SQL Server) user manuals for equivalent commands.

Important: If you install Jazz Team Server with the Change and Configuration Management, Quality Management, Jazz Reporting Service (two databases: one for Lifecycle Query Engine and one for Data Collection Component), Link Index Provider, Global Configuration, IBM Engineering Lifecycle Optimization - Engineering Insights, or Requirements Management applications on either the same computer or distributed platforms, a separate database and a DB user who is associated with that database must be created for each application. In addition, you must create a separate database for data warehouse.

Run the following commands in the Db2 Command Window. You can open the Command window from the application Start menu under the Db2 Command Line Tools menu. To connect to a remote server, you can use Telnet or SSH. On Unix systems, ensure that you are connected as the db2inst1 user by running the su db2inst1 command.

Procedure

  1. Open a Db2 command window and create the database.
    For Jazz Team Server, create a database called JTS with 16K pages and the UTF-8 code set.
    db2 create database JTS using codeset UTF-8 territory en PAGESIZE 16384
    For the Change and Configuration Management application, create a database called CCM with 16K pages and the UTF-8 code set.
    db2 create database CCM using codeset UTF-8 territory en PAGESIZE 16384
    For the Quality Management application, create a database called QM with 16K pages and the UTF-8 code set.
    db2 create database QM using codeset UTF-8 territory en PAGESIZE 16384
    For the Requirements Management application, create a database called RM with 16K pages and the UTF-8 code set.
    db2 create database RM using codeset UTF-8 territory en PAGESIZE 16384
    For the Lifecycle Query Engine application, create a database called LQE with 32K pages and the UTF-8 code set.
    Note: The Lifecycle Query Engine database requires 32K pages.
    db2 create database LQE using codeset UTF-8 territory en PAGESIZE 32768
    The LQE database must have the MAXAPPLS increased to allow for concurrent connections in Data Collection Component to process data if it is not set to AUTOMATIC. Increase the value to 300:
    db2 update db cfg for LQE using maxappls 300
    This means that the lock list must also be increased if it is not set to AUTOMATIC:
    db2 update db cfg FOR LQE using locklist 20000
    The transaction logs will also grow as data is processed in parallel. Increase the LOGFILSIZ to 20000:
    db2 update db cfg for LQE using LOGFILSIZ 20000
    Increase the number of primary and secondary transactions log files:
    db2 update db cfg for LQE using logprimary 25
    db2 update db cfg for LQE using logsecond 100
    For the Link Index Provider application, create a database called LDX with 32K pages and the UTF-8 code set.
    Note: The Link Index Provider database requires 32K pages.
    db2 create database LDX using codeset UTF-8 territory en PAGESIZE 32768
    For the Data Collection Component application, create a database called DCC with 16K pages and the UTF-8 code set.
    db2 create database DCC using codeset UTF-8 territory en PAGESIZE 16384
    For the Global Configuration Management application, create a database called GC with 16K pages and the UTF-8 code set.
    db2 create database GC using codeset UTF-8 territory en PAGESIZE 16384
    For the IBM Engineering Lifecycle Optimization - Engineering Insights application, create a database called RELM with 16K pages and the UTF-8 code set.
    db2 create database RELM using codeset UTF-8 territory en PAGESIZE 16384
    For the Data Warehouse, create a database called DW with 32K pages and the UTF-8 code set.
    db2 create database DW using codeset UTF-8 territory en PAGESIZE 32768
    The warehouse database must have the MAXAPPLS increased to allow for concurrent connections in Data Collection Component to process data if it is not set to AUTOMATIC. Increase the value to 300:
    db2 update db cfg for DW using maxappls 300
    This means that the lock list must also be increased if it is not set to AUTOMATIC:
    db2 update db cfg for DW using locklist 20000
    The transaction logs will also grow as data is processed in parallel. Increase the LOGFILSIZ to 20000:
    db2 update db cfg for DW using logfilsiz 20000
    Increase the number of primary and secondary transactions log files:
    db2 update db cfg for DW using logprimary 50
    db2 update db cfg for DW using logsecond 200

    For more information about Db2 data warehouse setup, click here.

    Note: If you are creating the database with a user other than the user specified in the teamserver.properties file, you must grant DBADM authority to that user:
    db2 connect to database name
    db2 grant dbadm on database to user user name
    db2 disconnect database name
  2. To configure your databases connections and create database tables, complete one of these steps:
    1. If you are using the default 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, 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 repository tools commands to create database tables, see Manually configuring a Db2 database
    4. If you are upgrading from a previous version, continue to run the appropriate upgrade scripts which will migrate over your existing database connections.
    Note: If you create all ELM databases on the same database server, verify that the Max number of concurrently active databases is set to a number greater than the number of databases you created. If this value is lower than the number of active databases, you will get the SQL Code 1041 error.
    1. To open the database manager configuration, from the Db2 command window enter the following command:
      db2 get dbm cfg
    2. Look for the Max number of concurrently active databases line. If this number is lower than the installed databases, increase the number by entering the following command:
      db2 update dbm cfg using numdb 11
    3. Stop and start the database manager for these changes to take effect by entering these commands:
      db2stop
      db2start

Setting up a Jazz Authorization Server Db2 database

Jazz Authorization Server is configured to use the default Apache Derby database, but you might want to use an enterprise database such as Db2 in your Jazz Authorization Server environment.

Procedure

  1. Create a file called createOauthTablesDB2.sql. Refer to the following SQL example to create the database and tables for Jazz 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,.
    CREATE DATABASE oauth2db USING CODESET UTF8 TERRITORY US;
    CONNECT TO oauth2db;
    
    CREATE BUFFERPOOL BUFF16K IMMEDIATE SIZE 2500 AUTOMATIC PAGESIZE 16K;
    create tablespace TAB16K pagesize 16K bufferpool BUFF16K dropped table recovery on;
    CREATE SYSTEM TEMPORARY TABLESPACE TEMPSYS16K PAGESIZE 16K BUFFERPOOL BUFF16K;
    
    ---- 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 CLOB NOT NULL DEFAULT '{}'
    );
    
    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 CLOB NOT NULL DEFAULT '{}'
    );
    
    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 CLOB NOT NULL DEFAULT '{}'
    );
    
    ---- ADD CONSTRAINTS ----
    ALTER TABLE OAuthDBSchema.OAUTH20CACHE
      ADD CONSTRAINT PK_LOOKUPKEY PRIMARY KEY (LOOKUPKEY);
    
    ALTER TABLE OAuthDBSchema.OAUTH20CLIENTCONFIG
      ADD CONSTRAINT PK_COMPIDCLIENTID PRIMARY KEY (COMPONENTID,CLIENTID);
    
    ---- CREATE INDEXES ----
    CREATE INDEX OAUTH20CACHE_EXPIRES ON OAUTHDBSCHEMA.OAUTH20CACHE (EXPIRES ASC);
    
    ---- 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 USER dbuser;
    -- GRANT ALL ON OAuthDBSchema.OAUTH20CLIENTCONFIG TO USER dbuser;
    
    ---- END OF GRANT PRIVILIGES ----
    
    DISCONNECT CURRENT;
  2. Open a Db2 command window and run the following script to create the database, tables, and indexes, and to grant privileges:
    db2 -stvf  createOauthTablesDB2.sql
  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 Db2 database. You must customize this section to work with your specific Db2 server:
    <jdbcDriver id="db2Universal" libraryRef="DB2JCC4LIB"/>
    <library id="DB2JCC4LIB" filesetRef="db2jcc4" apiTypeVisibility="spec,ibm-api,third-party"/>
    <fileset dir="${shared.config.dir}/lib/global" id="db2jcc4" includes="db2jcc4.jar db2jcc_license_cu.jar"/>
    <dataSource id="OAUTH2DBDS" jndiName="jdbc/oauthProvider" jdbcDriverRef="db2Universal">
    <properties.db2.jcc password="*****" databaseName="OAUTH2DB" user="db2inst1" portNumber="50000" serverName="yourDB2.com" driverType="4"/>
    </dataSource>

    Ensure that you have the correct values for the following attributes:

    • For user, provide the name of the Db2 user that is used to connect to the database.
    • For password, use the Db2 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 Db2 instance is running on. The default port number is 50000.
    • For serverName, enter the host name of the Db2 server.
  6. In the oauthProvider section of the appConfig.xml file, update the databaseStore property so it specifies the Db2 data source rather than the default Apache Derby data source. The databaseStore property should specify the name of the Db2 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 Db2 db2jcc4.jar and db2jcc_license_cu.jar JDBC drivers from your Db2 server to this directory: opt/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