Manually Running BPM Database Creation Scripts

A database administrator can, when required, manually create the BPM database and (if it is separate) the BDS database by running the necessary scripts, previously generated by TIBCO Configuration Tool.

Procedure

  1. If necessary, edit the database scripts to run correctly on the target database, as shown in the following table.
    Database Changes required
    SQL Server When manually running scripts, incorrect use of ANSI_PADDING statements may change the ANSI_PADDING setting for SQL Server, causing all tables created or upgraded subsequently during the same query session to have the wrong setting.

    Before manually running any SQL scripts, ensure that you do the following:

    • Delete any SET ANSI_PADDING ON and SET ANSI_PADDING OFF statements in the script.
    • Check the database query session settings and ensure that ANSI_PADDING is turned ON. To do this, right click the query window and select Query Options, then select the Execution->ANSI section and ensure that SET ANSI_PADDING is selected.
    Oracle If you need to manually amend any of the BPM database scripts, make sure that you do not include any blank lines. If there are any blank lines, you will receive an error similar to the ones below:

    SP2-0734: unknown command beginning "CONSTRAINT..."

    SP2-0042: unknown command ")"

    and the rest of the line is ignored. To resolve this, you can:

    • remove all blank lines from the script
    • set sqlblanklines on on the sqlplus session
    DB2 Not all the database scripts use the same termination character. For example, the termination character for WorkManagerCreate.sql is "/" and for createdb-admin.sql and ProcessManagerCreate.sql scripts is ";".

    Ensure that you examine the scripts and set the appropriate termination character using the DB2 Control Center. Doing so ensures that the scripts can be used from either the DB2 Control Center or the DB2 Command Line Processor.

    You can set the statement termination character from the DB2 Control Center.

    Note: The SQL Server schema name is hardcoded as "amxbpm" and cannot be changed.
  2. To manually create the BPM database, run the following scripts in the order shown.
    Caution: Ensure that you commit the changes after running each script.
    Step Run the script... as ... Description
    1. CONFIG_HOME\bpm\BPMApplicationName\database\admin\setup-admin.sql a DBA user On:
    • SQL Server: Creates required logins that own the BPM schema and database.
    • Oracle: Creates required tablespaces.
    • DB2: You do not need to run this script.
    2. CONFIG_HOME\bpm\BPMApplicationName\database\admin\createdb-admin.sql a DBA user Creates the BPM database and/or schema.
    3. CONFIG_HOME\bpm\BPMApplicationName\database\ProcessManagerCreate.sql the BPM database user Creates the schema objects required by the BPM Process Manager.
    4. CONFIG_HOME\bpm\BPMApplicationName\database\WorkManagerCreate.sql the BPM database user Creates the schema objects required by the BPM Work Manager.
    Note: The BPM database user is the database server user account that ActiveMatrix BPM should use to connect to the BPM database, as specified in the ActiveMatrix BPM: Runtime Database Configuration screen of the Create TIBCO ActiveMatrix BPM Server Wizard.
  3. To manually create the BDS database, run the following scripts in the order shown.
    Note: You only need to do this if you selected Use different database to other AMX-BPM components on the ActiveMatrix BPM: BDS Database Configuration screen. If you left this option cleared, so that the BDS case data store will be hosted in the ActiveMatrix BPM database, you should not run these scripts.
    Caution: Ensure that you commit the changes after running each script.
    Step Run the script... as ... Description
    1. CONFIG_HOME\bpm\BPMApplicationName\database\admin\setup-admin-bds.sql a DBA user On:
    • SQL Server: Creates required logins that own the BDS schema and database.
    • Oracle: Creates required tablespaces.
    • DB2: You do not need to run this script.
    2. CONFIG_HOME\bpm\BPMApplicationName\database\admin\createdb-admin-bds.sql a DBA user Creates the BDS database and/or schema.