Contribute in GitHub:

Setting up Db2 as DBB server database

The DBB server is packaged with WebSphere® Liberty and can be run out of the box without any customization for a trial or POC. To run production, it is recommended that you configure the application server to run with IBM Db2®.

The DBB server is pre-configured to use a Derby database. The Derby database is also included as part of the default setup. If you want to switch to a Db2 database, do the following steps.

Create a database for the DBB artifacts.
- Db2 for Linux, UNIX, and Windows
  
  As the database instance user, issue the following command:
```
db2 create database DBB using codeset UTF-8 territory en PAGESIZE 8192
```
  Note: Db2 requires a PAGESIZE of 8k or larger.
- Db2 for z/OS
  
  When running DBB with Db2 for z/OS, you must create a Db2 storage group and a Db2 database. You must also authorize a user to the storage group and database.
  1. Create a storage group
```
CREATE STOGROUP DBBSTG VOLUMES ('*') VCAT yourHlq ;
```
    The storage group can be named something other than DBBSTG.
    
    yourHlq is the high-level qualifier of your Db2 files. It must exist on your system, and the DBB server JDBC user must have full access to it.
  2. Create the database
    
    You must create the database with UNICODE as the CCSID. The following example shows Db2 SQL create statements:
```
CREATE DATABASE DBBZ001 STOGROUP DBBSTG BUFFERPOOL BP16K0 CCSID UNICODE;  
COMMIT;
```
    You can replace the database name on the CREATE DATABASE statement with a different name. The database name is used during table creation so the script must be changed to match.
    
    BP16K0 is an example of the buffer pool name. On z/OS, a 16 K page size or larger is required. This buffer pool is used for creating tables. Table spaces are created in the default 16 K buffer pool, unless you selected a larger buffer pool.
  3. Authorize user access to the database
    
    The server requires a user ID and password to access the database. The user ID and password are specified later in the wlp/usr/servers/dbb/server.xml file. This user ID is not used to log on to the server. It is used only to provide authority for the server to access the Db2 for z/OS database. Specifically, this user ID requires permissions as shown in the example. In this example, the user has the name dbbuser.
    
    The minimum authority to create the tables and indexes in the next step require DBADM access to the database and USE access to the storage group.
```
GRANT USE OF STOGROUP DBBSTG TO dbbuser;
GRANT DBADM ON DATABASE DBBZ001 TO dbbuser;
```
    The DBADM can also be used in the server configuration. However, the minimum authority to access the database in a running server requires DELETE, INSERT, SELECT, and UPDATE on the database tables.
```
SET SCHEMA DBBZ;
GRANT DELETE, INSERT, SELECT, UPDATE
    ON TABLE
         A_CONTENT_MAPPING,
         ARTIFACT,
         ARTIFACT_CONTENT,
         BR_ATTACHMENT_MAPPING,
         BR_PROP_MAPPING,
         BR_REPORT_DATA_MAPPING,
         BR_REPORT_MAPPING,
         BUILD_RESULT,
         BUILD_RESULT_PROPERTY,
         COL_LF_MAPPING,
         COLLECTION,
         DBB_SEQ_TABLE,
         DBB_TABLE_VERSIONS,
         LD_CATEGORIES,
         LD_LIBRARIES,
         LF_DEP_MAPPING,
         LF_LANGUAGES,
         LOGICAL_DEPENDENCY,
         LOGICAL_FILE,
         PERMISSIONS
    TO dbbuser;  
COMMIT ;  
```
Create your database tables by using the supplied scripts.

Refer to the SQL scripts in wlp/usr/servers/dbb/DDL for the supported databases to create the schema and tables used by the DBB server to store file metadata.

Modify the schema and database name in the script as necessary. To change schema name, set one or both schema properties. For more information, see Configuring DBB Server or Configuring DBB Container.
- Db2 for Linux, UNIX, and Windows
As the database instance user, from the wlp/usr/servers/dbb/DDL directory, run the following commands to create database tables:
```
db2 connect to DBB  
db2 -stvf create_DB2.sql
db2 disconnect DBB
```
- Db2 for z/OS
You can use tools such as Data Studio or SPUFI to run the script to create the database tables used by the DBB server to store file metadata.

The build result attachments, build report, and build report data are stored as BLOBs. The supplied scripts set the BLOB size to 100 M. If you anticipate attaching larger files or have an exceptionally large source code repository, you might want to consider increasing the BLOB size.

If you find that your BLOB size is too small (i.e. SQLCODE -302 from INSERT into table ARTIFACT_CONTENT) after installation, you can modify the BLOB definition, for example to 150 M, using the following command:

alter table ARTIFACT_CONTENT alter column CONTENT_DATA set data type blob(150M)

  
1. Store your database drivers in an accessible location.

  Get the driver and the license JAR files from your installation directory of Db2 for Linux, UNIX, and Windows or Db2 for z/OS and store them into a directory that is accessible by your server.

  * Db2 for Linux, UNIX, and Windows

      Copy the `db2jcc4.jar` and `db2jcc_license_cu.jar` files from your installation directory of Db2. For example, these files are typically located in the `/opt/ibm/db2/V<version>/java` directory on Linux.

  * Db2 for z/OS

      Copy the `db2jcc4.jar` and `db2jcc_license_cisuz.jar` files from your z/OS system installation directory of Db2 for z/OS. These files are typically located in the `<baseInstallDir>/jdbc/classes` directory.

1. Modify the `dataSource` for your database

Several examples are available in `wlp/usr/servers/dbb/config_samples` that can be moved into the server's `configDropins/overrides` folder. Check the prerequisites document to ensure that the database is supported.

  * Comment out the default Derby `jdbcDriver` and `dataSource` elements.
  * Uncomment the example `jdbcDriver` and `dataSource` elements for either Db2 or Db2 for z/OS.
  * Modify the `jdbcDriver` fileset element with the directory where you stored your database drivers.
  * Modify the `dataSource` `properties.db2.jcc` element to set the correct `databaseName`, `serverName`, `portNumber`, `user`, and `password`.