Creating Db2 databases

Create Db2® databases before installing IBM Business Automation Studio.

About this task

You must create a database for Application Engine playback server as well as for IBM Business Automation Studio before you can run Business Automation Studio.

Tip: If you need to know which database versions are supported for your installation, use this IBM® Support page to generate a software compatibility report.

1. You can choose to create the Application Engine database using the Db2 command line processor (CLP) or on Amazon Relational Database Service (Amazon RDS) for Db2.
   • To create the Application Engine database using Db2 CLP, run the following command in the Db2 CLP:

     CREATE DATABASE APP_ENGINE_DB_NAME AUTOMATIC STORAGE YES USING CODESET UTF-8 TERRITORY US PAGESIZE 32768;
     
     CONNECT TO APP_ENGINE_DB_NAME;
     
     CREATE BUFFERPOOL DBASBBP IMMEDIATE SIZE 1024 PAGESIZE 32K;
     
     CREATE REGULAR TABLESPACE APPENG_TS PAGESIZE 32 K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL DBASBBP DROPPED TABLE RECOVERY ON;
     CREATE USER TEMPORARY TABLESPACE APPENG_TEMP_TS PAGESIZE 32 K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL DBASBBP;
     
     GRANT USE OF TABLESPACE APPENG_TS TO USER APP_ENGINE_DB_USER_NAME;
     GRANT USE OF TABLESPACE APPENG_TEMP_TS TO USER APP_ENGINE_DB_USER_NAME;
     
     GRANT CONNECT ON DATABASE TO USER APP_ENGINE_DB_USER_NAME;
     
     CREATE SCHEMA APP_ENGINE_DB_SCHEMA_NAME AUTHORIZATION APP_ENGINE_DB_USER_NAME;
     
     GRANT CREATETAB ON DATABASE TO USER APP_ENGINE_DB_USER_NAME;
     
     CONNECT RESET;

     where
     • APP_ENGINE_DB_NAME is the database name for the Application Engine playback server database, for example, APPDB
     • APP_ENGINE_DB_USER_NAME is the username that Application Engine uses to connect to the database.
     • APP_ENGINE_DB_SCHEMA_NAME is the schema that Application Engine uses.
   • To create the Application Engine database on Amazon RDS for Db2, run the following command:

     -- Creating DB named: APP_ENGINE_DB_NAME
     CALL rdsadmin.create_database('APP_ENGINE_DB_NAME', 32768, 'UTF-8', 'US');
     
     --- Creation of the Database can take some time,Please wait for a few minutes before executing the statements below.
     
     -- Create bufferpool 
     CALL rdsadmin.create_bufferpool('APP_ENGINE_DB_NAME', 'DBASBBP', 1024, 'Y', 'Y', 32768, 0, 32);
     
     -- Create table spaces
     CALL rdsadmin.create_tablespace('APP_ENGINE_DB_NAME', 'APPENG_TS', 'DBASBBP', 32768, NULL, NULL, 'U', 'AUTOMATIC');
     CALL rdsadmin.create_tablespace('APP_ENGINE_DB_NAME', 'APPENG_TEMP_TS', 'DBASBBP', 32768, NULL, NULL, 'T', 'AUTOMATIC');
     
     -- Create role for the database with the role name of BAA
     CALL rdsadmin.create_role('APP_ENGINE_DB_NAME', 'BAA');
     -- Create a user
     CALL rdsadmin.add_user('APP_ENGINE_DB_USER_NAME', 'APP_ENGINE_DB_USER_PASSWORD', null);
     CALL rdsadmin.grant_role(?, 'APP_ENGINE_DB_NAME', 'BAA', 'USER APP_ENGINE_DB_USER_NAME', 'N');
     CALL rdsadmin.dbadm_grant(?, 'APP_ENGINE_DB_NAME', 'DATAACCESS', 'USER APP_ENGINE_DB_USER_NAME');
     
     --- Execute the below statement after the admin user is connected to the newly created Database
     GRANT SELECT ON SYSIBM.SYSVERSIONS TO USER APP_ENGINE_DB_USER_NAME;
     GRANT SELECT ON SYSCAT.DATATYPES TO USER APP_ENGINE_DB_USER_NAME;
     GRANT SELECT ON SYSCAT.INDEXES TO USER APP_ENGINE_DB_USER_NAME;
     GRANT SELECT ON SYSIBM.SYSDUMMY1 TO USER APP_ENGINE_DB_USER_NAME;
     GRANT USAGE ON WORKLOAD SYSDEFAULTUSERWORKLOAD TO USER APP_ENGINE_DB_USER_NAME;
     GRANT EXECUTE ON PACKAGE NULLID.SYSSH200 TO USER APP_ENGINE_DB_USER_NAME;
     
     GRANT USE OF TABLESPACE APPENG_TS TO USER APP_ENGINE_DB_USER_NAME;
     GRANT USE OF TABLESPACE APPENG_TEMP_TS TO USER APP_ENGINE_DB_USER_NAME;
     
     GRANT CONNECT ON DATABASE TO USER APP_ENGINE_DB_USER_NAME;
     
     CREATE SCHEMA APP_ENGINE_DB_SCHEMA_NAME AUTHORIZATION APP_ENGINE_DB_USER_NAME;
     
     GRANT CREATETAB ON DATABASE TO USER APP_ENGINE_DB_USER_NAME; 
     
     -- Done creating and tuning DB named: APP_ENGINE_DB_NAME

     where
     • APP_ENGINE_DB_NAME is the database name for the Application Engine playback server database, for example, APPDB
     • APP_ENGINE_DB_USER_NAME is the username that Application Engine uses to connect to the database.
     • APP_ENGINE_DB_USER_PASSWORD is the password that Application Engine uses to connect to the database.
     • APP_ENGINE_DB_SCHEMA_NAME is the schema that Application Engine uses.
2. You can choose to create the Business Automation Studio database using the Db2 command line processor (CLP) or on Amazon Relational Database Service (Amazon RDS) for Db2.
   • To create the Business Automation Studio database using Db2 CLP, run the following command in Db2 CLP:

     create database STUDIO_DB_NAME automatic storage yes using codeset UTF-8 territory US pagesize 32768;
     
     -- connect to the created database:
     connect to STUDIO_DB_NAME;
     
     -- A user temporary tablespace is required to support stored procedures in BPM.
     CREATE USER TEMPORARY TABLESPACE USRTMPSPC1;
     
     UPDATE DB CFG FOR STUDIO_DB_NAME USING LOGFILSIZ 16384 DEFERRED;
     UPDATE DB CFG FOR STUDIO_DB_NAME USING LOGSECOND 64 IMMEDIATE;
     GRANT CONNECT ON DATABASE TO USER STUDIO_USER_NAME;
     CREATE SCHEMA STUDIO_SCHEMA AUTHORIZATION STUDIO_USER_NAME;
     GRANT CREATETAB ON DATABASE TO USER STUDIO_USER_NAME;
     GRANT USE OF TABLESPACE USRTMPSPC1 TO USER STUDIO_USER_NAME;
     
     connect reset;
     

     where
     • STUDIO_DB_NAME is the database name for the Business Automation Studio database, for example, BASDB
     • STUDIO_USER_NAME is the username that Business Automation Studio uses to connect to the database
     • STUDIO_SCHEMA is the schema that Business Automation Studio uses
   • To create the Business Automation Studio database on Amazon RDS for Db2, run the following command:

     -- Creating DB named: STUDIO_DB_NAME
     CALL rdsadmin.create_database('STUDIO_DB_NAME',32768,'UTF-8','US' );
     
     --- Comment out all SQL stored procedures below when you are creating the database.
     --- Once the database is created, comment out the create_database stored procedure statement and uncomment the below statements and execute them.
     --- Creation of the Database can take some time, Please wait for a few minutes before executing the statements below.
     
     -- Create bufferpool
     CALL rdsadmin.create_bufferpool('STUDIO_DB_NAME','DBASBBP',1024,'Y','Y',32768,0,32);
     
     CALL rdsadmin.create_bufferpool('STUDIO_DB_NAME','DBASBBP32K',-1,'Y','Y',32768,0,32);
     
     --
     ---- Create table spaces
     CALL rdsadmin.create_tablespace( 'STUDIO_DB_NAME', 'USRTMPSPC1', 'DBASBBP', 32768, NULL, NULL, 'T', 'AUTOMATIC');
     
     CALL rdsadmin.create_tablespace( 'STUDIO_DB_NAME', 'USERSPACE32K', 'DBASBBP32K', 32768, NULL, NULL, 'U', 'AUTOMATIC');
     
     --
     ---- Create role for the database STUDIO_DB_NAME with the role name of BAS
     CALL rdsadmin.create_role('STUDIO_DB_NAME','BAS');
     ---- Create a user
     CALL rdsadmin.add_user('STUDIO_USER_NAME','STUDIO_DB_USER_PASSWORD',null);
     CALL rdsadmin.grant_role(?,'STUDIO_DB_NAME','BAS','USER STUDIO_USER_NAME','N');
     CALL rdsadmin.update_db_param('STUDIO_DB_NAME','LOGSECOND','64');
     --
     ----- Execute the below statement after the admin user is connected to the newly created Database
     GRANT CONNECT ON DATABASE TO USER STUDIO_USER_NAME;
     GRANT SELECT ON SYSIBM.SYSVERSIONS TO USER STUDIO_USER_NAME;
     GRANT SELECT ON SYSCAT.DATATYPES TO USER STUDIO_USER_NAME;
     GRANT SELECT ON SYSCAT.INDEXES TO USER STUDIO_USER_NAME;
     GRANT SELECT ON SYSIBM.SYSDUMMY1 TO USER STUDIO_USER_NAME;
     GRANT USAGE ON WORKLOAD SYSDEFAULTUSERWORKLOAD TO USER STUDIO_USER_NAME;
     GRANT IMPLICIT_SCHEMA ON DATABASE TO USER STUDIO_USER_NAME;
     CREATE SCHEMA STUDIO_SCHEMA AUTHORIZATION STUDIO_USER_NAME;
     GRANT EXECUTE ON PACKAGE NULLID.SYSSH200 TO USER STUDIO_USER_NAME;
     
     GRANT CREATETAB ON DATABASE TO USER STUDIO_USER_NAME;
     
     GRANT USE OF TABLESPACE USRTMPSPC1 TO USER STUDIO_USER_NAME;
     GRANT USE OF TABLESPACE USERSPACE32K TO USER STUDIO_USER_NAME;
     
     --- need DATAACCESS to run the JDBC APIs inside DatabaseMetaData
     CALL rdsadmin.dbadm_grant(?,'STUDIO_DB_NAME','DATAACCESS','USER STUDIO_USER_NAME');
     
     ---- Done creating and tuning DB named: STUDIO_DB_NAME

     where
     • STUDIO_DB_NAME is the database name for the Business Automation Studio database, for example, BASDB
     • STUDIO_USER_NAME is the username that Business Automation Studio uses to connect to the database
     • STUDIO_DB_USER_PASSWORD is the password that Business Automation Studio uses to connect to the database
     • STUDIO_SCHEMA is the schema that Business Automation Studio uses
3. Optional: To use your own JDBC driver, complete the following steps.
   1. Package your JDBC files into a compressed file and use the sc_drivers_url configuration parameter to download them from an accessible web server. Follow the steps in Optional: Preparing customized versions of JDBC drivers and ICCSAP libraries.
   2. Add the customized JDBC driver information to the configuration parameters.
      • Set bastudio_configuration.database.use_custom_jdbc_drivers to true and insert your own JDBC driver files into bastudio_configuration.database.jdbc_driver_files in the configuration parameters. See Business Automation Studio configuration parameters.
      • Set application_engine_configuration.use_custom_jdbc_drivers to true in the configuration parameters. See Application Engine configuration parameters.
        Note: For the Application Engine database, you can use your own JDBC driver only to initialize the database. The JDBC driver is not used by Application Engine at runtime. Application Engine uses a Node.js library to connect to the database, and this library is not customizable.