Setting up an Oracle database

Set up an Oracle database to work with Jazz™ Team Server, ELM applications, and Data Warehouse.

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 an Oracle database. Standard, Personal, or Express editions of the database do not support partitioning.
  • To create and set up an Oracle database, the user who is preparing the database must have database administration authority over the database and that the database, tablespace storage, and appropriate storage configuration is created by a user with system administration authority.
  • The required Java Database Connectivity (JDBC) driver is ojdbc8.jar.
    Restriction: Because of a defect in Oracle JDBC driver 12.1.0.2.0, this version of the driver cannot be used. For details, see repotools -createTables command fails with ORA-01000 on Oracle 12 on the IBM Support portal page.
  • For a list of supported database versions and JDBC drivers, see Hardware and software requirements.
  • The configuration parameters and tablespace options used in this topic are examples and might not entirely apply to your environment. For example, if you cannot use the AUTOEXTEND setting, then the files must be large enough to allow for growth. Or if you cannot use the UNLIMITED size quota for the Jazz Team Server database user, then make sure to allow enough space for rapid growth.
  • If you install the IBM® Engineering Lifecycle Management applications on the same computer or distributed platforms, you can either create separate databases for each application or you can use the same database for all applications. However, you must create a separate tablespace and a database user who is associated with that tablespace for each application.
  • The examples in this topic are for one Oracle database with separate database users who are dedicated to each application.
  • The database that you create for the Jazz Team Server and all ELM applications must be formatted to use UTF-8 as the character set. By default, Oracle does not create a database with the UTF-8 character set. When you create a new database, ensure that you select AL32UTF8 in the Oracle DB setup.
  • Change the open cursors limit:
    • If you use spfile to start the database, open a SQL *Plus window and enter the following command:
      alter system set open_cursors = 10000 scope=both;
    • If you use pfile to start the database, open a SQL *Plus window and enter the following command:
      alter system set open_cursors = 10000;

Procedure

  1. As a system administrator, create a database with UTF-8 encoding for the Jazz Team Server and all applications.
    1. Start the Database Configuration Assistant wizard and on the Welcome page click Next.
    2. On the Operations page, select Create a Database and then click Next.
    3. On the Templates page, select Custom Database and click Next.
    4. In both the Global Database Name and SID fields, enter a name for the content storage database; for example, ELMDB. Click Next.
    5. To configure the database control, enter the required information for alert notification and optionally enable the daily disk backup schedule. To continue without database control configuration, clear the check box next to Configure Enterprise Manager and then click Next.
    6. On the Database Credentials page, choose an option for your user account, and then click Next.
      Note: Oracle password complexity policy requires that passwords have minimum of 12 characters in length. In addition, the password must contain at least one uppercase character, one lowercase character, and one digit. If your password contains special characters, multibyte characters, or starts with numbers or special characters, you must enclose it in double quotation marks. For details, see the Oracle documentation.
    7. On the next page, select a storage type and location for database files. Click Next.
    8. On the next page, choose a recovery option for the database, and then click Next.
    9. On the next page, specify whether or not to add the Sample Schemas to your database. Click Next.
    10. On the next page, click the Character Sets tab and select Use Unicode (AL32UTF8). Click Next.
      Note: Setting the character set to Unicode (AL32UTF8) enables you to store multiple language groups.
    11. On the Database Storage page verify or change the database objects and click Next.
    12. On the next page, click Finish to create the database.
    13. In the Confirmation window that opens, optionally save the page as an HTML file, and then click OK. A progress bar is displayed to show the database creation progress.
  2. Open an SQL *Plus window and log in as SYSTEM or SYSDBA.

    To create a tablespace for Jazz Team Server, enter the following command. Replace JTS with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and JTS.DBF with the datafile name that you want to create.

    CREATE BIGFILE TABLESPACE JTS 
    DATAFILE 'ORACLE_BASE/oradata/ELMDB/JTS.DBF' 
    SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
    To create a temporary tablespace for Jazz Team Server, enter the following command. Replace JTS_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and JTS_TEMP.DBF with the temporary file name that you want to create.
    CREATE TEMPORARY TABLESPACE JTS_TEMP 
    TEMPFILE 'ORACLE_BASE/oradata/ELMDB/JTS_TEMP.DBF' 
    SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

    To create a tablespace for the Change and Configuration Management application, enter the following command. Replace CCM with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and CCM.DBF with the datafile name that you want to create.

    CREATE BIGFILE TABLESPACE CCM 
    DATAFILE 'ORACLE_BASE/oradata/ELMDB/CCM.DBF' 
    SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
    To create a temporary tablespace for the Change and Configuration Management application, enter the following command. Replace CCM_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and CCM_TEMP.DBF with the temporary file name that you want to create.
    CREATE TEMPORARY TABLESPACE CCM_TEMP 
    TEMPFILE 'ORACLE_BASE/oradata/ELMDB/CCM_TEMP.DBF' 
    SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

    To create a tablespace for the Quality Management application, enter the following command. Replace QM with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and QM.DBF with the datafile name that you want to create.

    CREATE BIGFILE TABLESPACE QM 
    DATAFILE 'ORACLE_BASE/oradata/ELMDB/QM.DBF' 
    SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
    To create a temporary tablespace for the Quality Management application, enter the following command. Replace QM_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and QM_TEMP.DBF with the temporary file name that you want to create.
    CREATE TEMPORARY TABLESPACE QM_TEMP 
    TEMPFILE 'ORACLE_BASE/oradata/ELMDB/QM_TEMP.DBF' 
    SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

    To create a tablespace for the Requirements Management application, enter the following command. Replace RM with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and RM.DBF with the datafile name that you want to create.

    CREATE BIGFILE TABLESPACE RM 
    DATAFILE 'ORACLE_BASE/oradata/ELMDB/RM.DBF' 
    SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
    To create a temporary tablespace for the Requirements Management application, enter the following command. Replace RM_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and RM_TEMP.DBF with the temporary file name that you want to create.
    CREATE TEMPORARY TABLESPACE RM_TEMP 
    TEMPFILE 'ORACLE_BASE/oradata/ELMDB/RM_TEMP.DBF' 
    SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

    To create a tablespace for the Data Collection Component application, enter the following command. Replace DCC with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and DCC.DBF with the datafile name that you want to create.

    CREATE BIGFILE TABLESPACE DCC 
    DATAFILE 'ORACLE_BASE/oradata/ELMDB/DCC.DBF' 
    SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
    To create a temporary tablespace for the Data Collection Component application, enter the following command. Replace DCC_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and DCC_TEMP.DBF with the temporary file name that you want to create.
    CREATE TEMPORARY TABLESPACE DCC_TEMP 
    TEMPFILE 'ORACLE_BASE/oradata/ELMDB/DCC_TEMP.DBF' 
    SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

    To create a tablespace for the Lifecycle Query Engine application, enter the following command. Replace LQE with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and LQE.DBF with the datafile name that you want to create.

    CREATE BIGFILE TABLESPACE LQE 
    DATAFILE 'ORACLE_BASE/oradata/ELMDB/LQE.DBF' 
    SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
    To create a temporary tablespace for the Lifecycle Query Engine application, enter the following command. Replace LQE_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and LQE_TEMP.DBF with the temporary file name that you want to create.
    CREATE TEMPORARY TABLESPACE LQE_TEMP 
    TEMPFILE 'ORACLE_BASE/oradata/ELMDB/LQE_TEMP.DBF' 
    SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

    To create a tablespace for the Link Index Provider application, enter the following command. Replace LDX with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and LDX.DBF with the datafile name that you want to create.

    CREATE BIGFILE TABLESPACE LDX 
    DATAFILE 'ORACLE_BASE/oradata/ELMDB/LDX.DBF' 
    SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
    To create a temporary tablespace for the Link Index Provider application, enter the following command. Replace LDX_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and LDX_TEMP.DBF with the temporary file name that you want to create.
    CREATE TEMPORARY TABLESPACE LDX_TEMP 
    TEMPFILE 'ORACLE_BASE/oradata/ELMDB/LDX_TEMP.DBF' 
    SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

    To create a tablespace for the IBM Engineering Lifecycle Optimization - Engineering Insights application, enter the following command. Replace ENI with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and ENI.DBF with the datafile name that you want to create.

    CREATE BIGFILE TABLESPACE ENI 
    DATAFILE 'ORACLE_BASE/oradata/ELMDB/ENI.DBF' 
    SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
    To create a temporary tablespace for the IBM Engineering Lifecycle Optimization - Engineering Insights application, enter the following command. Replace ENI_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and ENI_TEMP.DBF with the temporary file name that you want to create.
    CREATE TEMPORARY TABLESPACE ENI_TEMP 
    TEMPFILE 'ORACLE_BASE/oradata/ELMDB/ENI_TEMP.DBF' 
    SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

    To create a tablespace for the Global Configuration Management application, enter the following command. Replace GC with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and GC.DBF with the datafile name that you want to create.

    CREATE BIGFILE TABLESPACE GC 
    DATAFILE 'ORACLE_BASE/oradata/ELMDB/GC.DBF' 
    SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
    To create a temporary tablespace for the Global Configuration Management application, enter the following command. Replace GC_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and GC_TEMP.DBF with the temporary file name that you want to create.
    CREATE TEMPORARY TABLESPACE GC_TEMP 
    TEMPFILE 'ORACLE_BASE/oradata/ELMDB/GC_TEMP.DBF' 
    SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

    To create a tablespace for the common data warehouse, enter the following command. Replace DW with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and DW.DBF with the datafile name that you want to create.

    CREATE BIGFILE TABLESPACE DW 
    DATAFILE 'ORACLE_BASE/oradata/ELMDB/DW.DBF' 
    SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
    To create a temporary tablespace for the common data warehouse, enter the following command. Replace DW_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and DW_TEMP.DBF with the temporary file name that you want to create.
    CREATE TEMPORARY TABLESPACE DW_TEMP 
    TEMPFILE 'ORACLE_BASE/oradata/ELMDB/DW_TEMP.DBF' 
    SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

    In the Oracle Enterprise Manager Database Control, click Server > Tablespaces and verify that the tablespaces you created are displayed and accessible. Also ensure that the default Oracle tablespace named USERS already exist. If this tablespace does not exist in your Oracle database, create it.

    Oracle Enterprise Manager

  3. Create an Oracle database user for each new tablespace.

    After the tablespaces are created, a special Oracle user must be created for each application to use the Oracle database. For multiple Jazz servers that run on one Oracle database, an Oracle database user must be created for each application. The Oracle database user must be able to create database objects in its tablespace (either unlimited or with a space quota) with privileges to create session, table, procedure, and views.

    To create a user for Jazz Team Server tablespace and to grant the required permissions, enter the following commands:
    CREATE USER JTS_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE JTS QUOTA UNLIMITED ON JTS TEMPORARY TABLESPACE JTS_TEMP;
    GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO JTS_DB_USER;
    To create a user for Change and Configuration Management application tablespace and to grant the required permissions, enter the following commands:
    CREATE USER CCM_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE CCM QUOTA UNLIMITED ON CCM TEMPORARY TABLESPACE CCM_TEMP;
    GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO CCM_DB_USER;
    To create a user for Quality Management application tablespace and to grant the required permissions, enter the following commands:
    CREATE USER QM_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE QM QUOTA UNLIMITED ON QM TEMPORARY TABLESPACE QM_TEMP;
    GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO QM_DB_USER;
    To create a user for Requirements Management application tablespace and to grant the required permissions, enter the following commands:
    CREATE USER RM_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE RM QUOTA UNLIMITED ON RM TEMPORARY TABLESPACE RM_TEMP;
    GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO RM_DB_USER;
    To create a user for Data Collection Component application tablespace and to grant the required permissions, enter the following commands:
    CREATE USER DCC_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE DCC QUOTA UNLIMITED ON DCC TEMPORARY TABLESPACE DCC_TEMP;
    GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO DCC_DB_USER;
    To create a user for Lifecycle Query Engine application tablespace and to grant the required permissions, enter the following commands:
    CREATE USER LQE_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE LQE QUOTA UNLIMITED ON LQE TEMPORARY TABLESPACE LQE_TEMP;
    GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO LQE_DB_USER;
    To create a user for Link Index Provider application tablespace and to grant the required permissions, enter the following commands:
    CREATE USER LDX_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE LDX QUOTA UNLIMITED ON LDX TEMPORARY TABLESPACE LDX_TEMP;
    GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO LDX_DB_USER;
    To create a user for IBM Engineering Lifecycle Optimization - Engineering Insights application tablespace and to grant the required permissions, enter the following commands:
    CREATE USER ENI_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE ENI QUOTA UNLIMITED ON ENI TEMPORARY TABLESPACE ENI_TEMP;
    GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO ENI_DB_USER;
    To create a user for Global Configuration Management application tablespace and to grant the required permissions, enter the following commands:
    CREATE USER GC_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE GC QUOTA UNLIMITED ON GC TEMPORARY TABLESPACE GC_TEMP;
    GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO GC_DB_USER;
    To create a user for common data warehouse tablespace and to grant the required permissions, enter the following commands:
    CREATE USER DW_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE DW QUOTA UNLIMITED ON DW TEMPORARY TABLESPACE DW_TEMP;
    GRANT DBA TO DW_USER;
    Note: The creation of the data warehouse on Oracle requires more permissions as compared to other databases. When you specify the database user in the connection spec for data warehouse, ensure that the database user has DBA permissions. You can change this permission after the data warehouse is created. For more information, see Completing the installation.

    To create the data warehouse without DBA permissions, see this Deployment wiki article.

  4. Create an environment variable named ORACLE_JDBC_DRIVER_FILE and point to the ojdbc8.jar JDBC driver. The location can vary depending on the Oracle product and operating system. You can download the supported Oracle JDBC driver from the Oracle website.
    1. For Windows operating systemClick 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 ORACLE_JDBC_DRIVER_FILE, and in the Variable value enter C:\<Path_to_JDBC_driver>\ojdbc8.jar, where <Path_to_JDBC_driver> is the file path to the computer that host the applications and not the database.
    1. For Unix operating systemsOn UNIX systems, add the following environment variable declaration:
      export ORACLE_JDBC_DRIVER_FILE =/Path_to_JDBC_driver/ojdbc8.jar
      Note: For a list of supported versions of Java Runtime Environment and Oracle Java Database Connectivity (JDBC), see Hardware and software requirements.

      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.

  5. To configure your database connection 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 the database connection and run the repotools commands to create database tables, see Manually configuring an Oracle database.
    4. If you are upgrading from a previous version, continue to run the appropriate upgrade scripts, which will migrate your existing database connections.

Setting up a Jazz Authorization Server Oracle database

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

Procedure

  1. Create a file called createOauthOracle.sql. Refer to the following SQL example to create the tablespaces required for Jazz Authorization Server.

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

    createOauthOracle.sql creates two tablespaces: OAUTH and OAUTH_TMP, and creates the tables in the OAUTH tablespace. If you have an existing tablespace that you want to use, you can change the CREATE TABLE statement to use your existing tablespace. If you want to create new tablespaces, then you should edit the DATAFILE and TEMPFILE clauses to point at the directory that you want to use for the tablespace.

    Jazz Authorization Server expects to find a user named OAUTHDBSCHEMA, and it expects to find tables in a schema named OAUTHDBSCHEMA. You must create the OAUTHDBSCHEMA user in Oracle. Customize the CREATE USER statement to specify a password for the OAUTHDBSCHEMA user. When you create the OAUTHDBSCHEMA user, you must also associate that user with the tablespace you plan to use for the OAUTH tables.

    CREATE TABLESPACE OAUTH DATAFILE '/home/oracle/tablespaces/OAUTH.dbf' SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
    CREATE TEMPORARY TABLESPACE OAUTH_TMP TEMPFILE '/home/oracle/tablespaces/OAUTH_TMP.dbf' SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
    CREATE USER OAUTHDBSCHEMA IDENTIFIED BY <your password> DEFAULT TABLESPACE OAUTH QUOTA UNLIMITED ON OAUTH TEMPORARY TABLESPACE OAUTH_TMP;
    GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO OAUTHDBSCHEMA;
    
    ---- 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 NUMBER(19,0),
    LIFETIME INT,
    EXPIRES NUMBER(19,0),
    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 DEFAULT '{}' NOT NULL
    )
    TABLESPACE OAUTH STORAGE(INITIAL 50K);
    
    CREATE TABLE OAuthDBSchema.OAUTH20CLIENTCONFIG 
    (
    COMPONENTID VARCHAR(256) NOT NULL, 
    CLIENTID VARCHAR(256) NOT NULL, 
    CLIENTSECRET VARCHAR(256), 
    DISPLAYNAME VARCHAR(256),
    REDIRECTURI VARCHAR(2048), 
    ENABLED INT,
    CLIENTMETADATA CLOB DEFAULT '{}' NOT NULL
    ) TABLESPACE OAUTH STORAGE(INITIAL 50K);
    
    CREATE TABLE OAuthDBSchema.OAUTH20CONSENTCACHE (
    CLIENTID VARCHAR(256) NOT NULL,
    USERID VARCHAR(256),
    PROVIDERID VARCHAR(256) NOT NULL,
    SCOPE VARCHAR(1024) NOT NULL,
    EXPIRES NUMBER(19,0),
    EXTENDEDFIELDS CLOB DEFAULT '{}' NOT NULL
    ) TABLESPACE OAUTH STORAGE(INITIAL 50K);
    
    ---- 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 OAUTHDBSCHEMA;
    GRANT ALL ON OAuthDBSchema.OAUTH20CLIENTCONFIG TO OAUTHDBSCHEMA;
    GRANT ALL ON OAuthDBSchema.OAUTH20CONSENTCACHE TO OAUTHDBSCHEMA;
    
    ---- END OF GRANT PRIVILIGES ----
  2. After you finish customizing the createOauthOracle.sql script, open SQL Plus and log in as SYSDBA, and run the SQL script:
    @createOauthOracle.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 Oracle. You must customize this section to work with your specific Oracle server:
    <jdbcDriver id="oracle" libraryRef="ORAJCC4LIB"/>
    <library id="ORAJCC4LIB" filesetRef="orajcc4" apiTypeVisibility="spec,ibm-api,third-party"/>
    <fileset dir="${shared.config.dir}/lib/global" id="orajcc4" includes="ojdbc8.jar"/>
    <dataSource id="OAUTH2ORA" jndiName="jdbc/oauthProvider" jdbcDriverRef="oracle">
    <properties.oracle password="*****" databaseName="ORCL" user="OAUTHDBSCHEMA" portNumber="1521" serverName="yourOracle.com">
    </dataSource>

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

    • For password, use the password you specified when creating the OAUTHDBSCHEMA user in Oracle.
    • For databaseName, use the Oracle SID for your database. The sample uses the default value assigned by Oracle - ORCL. Your value might be different.
    • For portNumber, use the port number that the Oracle instance is running on. The default port number is 1521.
    • For serverName, enter the host name of the Oracle server.
  6. In the oauthProvider section of the appConfig.xml file, update the databaseStore property so it specifies the Oracle data source rather than the default Apache Derby data source. The databaseStore property should specify the name of the Oracle data source, which in this example, is OAUTH2ORA:
    <oauthProvider id="JazzOP"
    	    httpsRequired="true"
    		autoAuthorize="true"
    		customLoginURL="/jazzop/form/login" 
    		accessTokenLifetime="7201" 
    		authorizationGrantLifetime="604801">
    		<autoAuthorizeClient>client01</autoAuthorizeClient>
      	     <databaseStore dataSourceRef="OAUTH2ORA" /> 
    	</oauthProvider>
  7. Save and close the appConfig.xml file.
  8. Copy the Oracle ojdbc8.jar JDBC driver from your Oracle 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