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. Apache Derby database is only supported for evaluation purpose in a POC environment, but not in the production environment. This limitation applies to all Engineering Lifecycle Management applications including Jazz Authorization Server.

Procedure

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.

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(20000) 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;

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
```
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.

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>

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 for Db2 11.5.5 and earlier versions is 50000 and for Db2 11.5.6 and later versions is 25000.
- For serverName, enter the host name of the Db2 server.

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>

Save and close the appConfig.xml file.
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.