Setting up a Db2 database

The IBM® Engineering Lifecycle Management products support multiple IBM Db2 editions. Each client license that is purchased from Engineering Lifecycle Management includes a license to run one instance of Db2 Standard Edition with Engineering Lifecycle Management.

Before you begin

For large deployments that require more advanced features, you might need to purchase licenses for Db2 Advanced Edition. For more information on deployment planning and design, including the differences between Db2 Standard Edition and Db2 Advanced Edition, see Deployment planning and design.

Important: 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 (or Advanced edition if using Db2 11.5) 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, extra settings are required. To learn more about Db2 pureScale®, see Configuration of Db2 on Linux, UNIX, and Windows systems workload balancing support for Java clients.
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 for IBM Engineering Lifecycle Management.
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.
Learn more about Db2 commands: 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) user manuals for equivalent commands.

Important: If you install Jazz Team Server with the Engineering Workflow 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

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 Engineering Workflow 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 Engineering Test 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 Engineering Requirements Management DOORS® Next 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
```
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 Engineering Insights application, create a database called Engineering Insights with 16K pages and the UTF-8 code set.
```
db2 create database ENI 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
```
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
```
To configure your databases connections and create database tables, complete one of these steps:
1. If you are using the default IBM WebSphere® Liberty, start the server and continue with Running the setup by using Custom setup in the setup wizard.
2. 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
3. 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 Engineering Lifecycle Management 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 less 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 less 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

What to do next

For more information about Db2 data warehouse setup, see More Control over the Db2 Data Warehouse Setup.