Install the Database Schema
This document will guide you through the installation and update of the CIB seven database schema used by the process engine. Regardless of the architecture of your application setup, the process engine always requires this database schema. In a production environment, we recommend setting up this schema yourself and reference the prepared database instance in your application configuration. Consult the installation guide related to your setup to configure the database for a remote engine, shared engine, or embedded engine accordingly.
This guide will not detail how to set up an instance of your target database or how to create a schema object on it. Consult the documentation of your target database on how to do that. CIB seven supports a variety of databases that are documented in the supported environments.
CIB seven supports the following ways of installing the database schema:
- Use the database migration tool Liquibase with the provided changelog for a semi-automatic installation and update. Liquibase keeps track of database schema changes. This allows you to focus on when changes should be applied rather than also on which changes are relevant. Starting with version
7.16.0, CIB seven comes with a curated changelog file that Liquibase can consume. - Use the provided SQL scripts with the tools related to your database for a fully manual installation and update. A manual procedure allows you to fully control the SQL statements that are executed on your database instance and to adjust those statements to your needs if necessary.
Isolation level
READ COMMITED is the required isolation level for database systems to run CIB seven with. You may have to change the default setting on your database when installing CIB seven. For more information see the documentation on isolation levels.
Installation
You can either install your database schema using Liquibase or using the provided SQL scripts manually. You can switch between those mechanisms when updating your CIB seven version at a later stage if desired. However, this might come with additional preparation work to work reliably. The update paragraph provides more details on this topic.
Liquibase installation
CIB seven comes with a maintained changelog file that Liquibase can consume.
This changelog defines which SQL statements to execute on a database.
You can find the changelog and its referenced resources on our Artifact Repository.
Select the respective version ($PLATFORM_VERSION) and download the resources as a zip or tar.gz file.
Open the cibseven-sql-scripts-$PLATFORM_VERSION/liquibase folder to find the changelog.
In case you are using a pre-packaged distribution, the Liquibase resources already reside in the sql/liquibase folder of the distribution.
The liquibase folder contains the following resources:
camunda-changelog.xmlbaselinedirectory
Liquibases uses these resources in combination with the scripts in the upgrade folder next to the liquibase folder to install the schema.
Perform the following steps to install the database schema on your database instance:
- Setup Liquibase, e.g. by downloading Liquibase CLI
- Run Liquibase’s update command referencing the
camunda-changelog.xml. You can pass on the connection details to your database instance via parameters as described in the Liquibase documentation or create a properties file.
Liquibase creates two additional tables to keep track of the changes that have been applied to your database.
The DATABASECHANGELOG table keeps track of all applied changes. The DATABASECHANGELOGLOCK table prevents conflicts from concurrent updates on your database instance by multiple Liquibase instances. You can read more about it in the Liquibase guide.
As you create the tables externally via Liquibase, you have to configure the engine to not create tables at startup as well.
Set the databaseSchemaUpdate property to false (or, in case you are using Oracle, to noop).
Consult the manual installation guide of your distribution for further information on how to achieve that.
Heads Up!
Liquibase provides additional commands to preview all changes that will be applied by commands that execute SQL statements on a database. For the update command, you can execute the updateSql command. This will let you inspect all statements that Liquibase will issue on your database without actually executing them.
Furthermore, if you have defined a specific prefix for the entities of your database, you will have to manually adjust the create scripts in the liquibase/baseline directory accordingly so that the tables are created with the prefix.
Manual installation
To install the database schema required for CIB seven, we provide a set of scripts with prepared DDL statements.
Those scripts create all required tables and default indices. You can find the provided SQL scripts on our Artifact Repository.
Select the respective version ($PLATFORM_VERSION) and download the scripts as a zip or tar.gz file.
Open the cibseven-sql-scripts-$PLATFORM_VERSION/create folder to find all available scripts.
In case you are using a pre-packaged distribution, the SQL scripts already reside in the sql/create folder of the distribution.
The create folder contains the following SQL scripts:
$DATABASENAME_engine_$PLATFORM_VERSION.sql$DATABASENAME_identity_$PLATFORM_VERSION.sql
There are individual SQL scripts for each supported database ($DATABASENAME).
Select the appropriate scripts for your database and run them with your database administration tool (e.g., SqlDeveloper for Oracle).
As you create the tables manually, you have to configure the engine to not create tables at startup as well.
Set the databaseSchemaUpdate property to false (or, in case you are using Oracle, to noop).
Consult the manual installation guide of your distribution for further information on how to achieve that.
Heads Up!
If you have defined a specific prefix for the entities of your database, you will have to manually adjust the create scripts accordingly so that the tables are created with the prefix.
Update
Updating to a newer CIB seven minor version also requires a database schema update. You can reuse all the options available for a schema installation here as well. If you are switching from one option to another, you might need to perform additional preparation work to update reliably. The individual sections on the mechanisms will provide details if necessary.
In case you are just updating to a newer patch level of your CIB seven installation, a schema update might not be necessary.
Liquibase update
This section assumes you are already set up with Liquibase as described in the installation section. In case you have not set up Liquibase itself yet and want to update a database that you manually installed and updated until now, please consult the migration section first.
Camunda comes with a maintained changelog file that Liquibase can consume. This changelog helps Liquibase to keep track of the changes that have been made to your database already. Based on that changelog and the tracking tables, Liquibase determines which changes it needs to apply when you instruct it to update your schema.
Perform the following steps to update the database schema on your database instance:
- Select the respective version you want to update to (
$Y) on our Artifact Repository and download the resources as aziportar.gzfile. Open thecibseven-sql-scripts-$Y/liquibasefolder to find the changelog file. In case you are using a pre-packaged distribution, the Liquibase resources already reside in thesql/liquibasefolder of the distribution with version$Y. - Run Liquibase’s update command referencing the new
camunda-changelog.xmlof version$Y. Liquibase takes care of determining the necessary changes and applying them to your database according to the new changelog. You can pass on the connection details to your database instance via parameters as described in the Liquibase documentation or create a properties file. - We highly recommend updating to the latest patch version that is within the bounds of the new minor version you are updating to (
$Y).
Dry run
Liquibase provides additional commands to preview all changes applied by commands that execute SQL statements on a database. For the update command, you can execute the updateSql command to inspect all statements that Liquibase will issue on your database without actually executing them.
Migrate to Liquibase
Liquibase provides workflows to update databases that were not set up using Liquibase from the very beginning. For such a scenario to work, you need to populate a tracking table that represents the current state of your database with regards to the changelog file you want to update against. In other words, you need to let Liquibase know which parts of the changelog your database already contains.
Perform the following steps to migrate your manual installation to Liquibase:
- Setup Liquibase, e.g. by downloading Liquibase CLI
- Identify your current database schema version. You can extract this information from the
ACT_GE_SCHEMA_LOGtable. Find the row with the highest value in theID_column and use the value of this row’sVERSION_column. - Run Liquibase’s changelogSyncToTag command referencing the
camunda-changelog.xmland using your current database schema version as the tag. You can pass on the connection details to your database instance via parameters as described in the Liquibase documentation or create a properties file.
Liquibase uses this information to create the tracking tables and mark all changesets until the tag you defined as executed.
Liquibase determines if there are any changes that it needs to apply to your database for any subsequent update commands.
You have migrated your manual installation to Liquibase.
Manual update
Updating from your current minor version ($X) to its follow-up version ($Y) requires updating the database schema as well.
Follow the outlined procedure to perform this update:
-
Check for available database patch scripts for your database that are within the bounds of your update path. You can find the scripts on our Artifact Repository. Select the respective version you want to update to (
$Y) and download the scripts as aziportar.gzfile. Open thecibseven-sql-scripts-$Y/upgradefolder to find all available scripts. In case you are using a pre-packaged distribution, the SQL scripts already reside in thesql/upgradefolder of the distribution with version$Y. We highly recommend executing these patches before updating. Execute those related to your database type ($DATABASENAME) in ascending order by version number. The naming pattern is$DATABASENAME_engine_$X_patch_*.sql. -
Execute the corresponding update scripts named
$DATABASENAME_engine_$X_to_$Y.sql. The scripts update the database from one minor version to the next and change the underlying database structure. So make sure to backup your database in case there are any failures during the update process. -
We highly recommend checking for any existing patch scripts for your database that are within the bounds of the new minor version you are updating to (
$Y). Execute them in ascending order by version number. The procedure is the same as in step 1, only for the new minor version.
Patch level update
This section explains how to perform a patch-level update for your database schema. The patch level is the version number “after the second dot”. For example, update from 7.14.2 to 7.14.3.
Between patch levels, the structure of the database schema does not change. The database structure of all patch releases is backward compatible with the corresponding minor version. For example, the database schema of all 7.14.x versions are backward compatible with the 7.14.0 schema.
The one exception to this is a bug in the database schema itself. If you are affected by such a bug, you have the option to run a patch script. The following list contains all available patch scripts, information on what the fixes are related to, and a link to the corresponding Camunda Jira issue:
| Camunda Version | Patch file | Description | Affected databases | Issue link |
|---|---|---|---|---|
| 7.1 | engine_7.1_patch_7.1.4_to_7.1.5.sql | Add a missing index on foreign key to prevent deadlocks | H2, MySQL, Oracle, PostgreSQL | CAM-2567 |
| 7.1 | engine_7.1_patch_7.1.9_to_7.1.10.sql | Add a missing index on foreign key to prevent deadlocks | DB2, SQL Server | CAM-3565 |
| 7.2 | engine_7.2_patch_7.2.4_to_7.2.5.sql | Add a missing index on foreign key to prevent deadlocks. This is the same patch as engine_7.1_patch_7.1.9_to_7.1.10.sql. | DB2, SQL Server | CAM-3565 |
| 7.2 | engine_7.2_patch_7.2.6_to_7.2.7.sql | Add indices to improve deployment performance. | All databases | CAM-4497 |
| 7.3 | engine_7.3_patch_7.3.0_to_7.3.1.sql | Adjust column size of ACT_HI_JOB_LOG.ACT_ID_ to 255. | All databases | CAM-4037 |
| 7.3 | engine_7.3_patch_7.3.2_to_7.3.3_1.sql | Add a missing index on ACT_RU_AUTHORIZATION#RESOURCE_ID_ to prevent deadlocks. | All databases | CAM-4440 |
| 7.3 | engine_7.3_patch_7.3.2_to_7.3.3_2.sql | Add indices to improve deployment performance. This is the same patch as engine_7.2_patch_7.2.6_to_7.2.7.sql. | All databases | CAM-4497 |
| 7.3 | engine_7.3_patch_7.3.5_to_7.3.6_1.sql | Adjust column size of ACT_RU_JOB.PROCESS_DEF_KEY_ to 255. | All databases | CAM-4328 |
| 7.3 | engine_7.3_patch_7.3.5_to_7.3.6_2.sql | Add indices to improve performance of group authorizations. | All databases | CAM-5364 |
| 7.4 | engine_7.4_patch_7.4.2_to_7.4.3_1.sql | Add index to improve historic activity instance statistics query performance. | All databases | CAM-5257 |
| 7.4 | engine_7.4_patch_7.4.2_to_7.4.3_2.sql | Add a missing index on ACT_RU_EXT_TASK#EXECUTION_ID_ to prevent deadlocks. | All databases | CAM-5440 |
| 7.4 | engine_7.4_patch_7.4.2_to_7.4.3_3.sql | Add indices to improve performance of group authorizations. This is the same patch as engine_7.3_patch_7.3.5_to_7.3.6_2.sql. | All databases | CAM-5364 |
| 7.4 | engine_7.4_patch_7.4.5_to_7.4.6.sql | Adjust column size of ACT_RU_JOB.PROCESS_DEF_KEY_ to 255. This is the same patch as engine_7.3_patch_7.3.5_to_7.3.6_1.sql. | All databases | CAM-4328 |
| 7.6 | engine_7.6_patch_7.6.0_to_7.6.1.sql | Adjust column size of ACT_RU_EVENT_SUBSCR.ACTIVITY_ID_ to 255. | All databases | CAM-6788 |
| 7.6 | engine_7.6_patch_7.6.2_to_7.6.3_1.sql | Add a missing index on ACT_RU_EXT_TASK#ERROR_DETAILS_ID_ to prevent deadlocks. | All databases | CAM-7263 |
| 7.6 | engine_7.6_patch_7.6.2_to_7.6.3_2.sql | Remove an incorrect index ACT_RU_JOB#ACT_IDX_JOB_HANDLER for MSSQL Server. | MSSQL Server | CAM-7442 |
| 7.7 | engine_7.7_patch_7.7.3_to_7.7.4.sql | Insert new startup.lock in ACT_GE_PROPERTY. | All databases | CAM-8162 |
| 7.7 | engine_7.7_patch_7.7.4_to_7.7.5_1.sql | Add indices to improve performance of history cleanup | All databases | CAM-8184 |
| 7.7 | engine_7.7_patch_7.7.4_to_7.7.5_2.sql | Increase the field length of ACT_RU_AUTHORIZATION.RESOURCE_ID_ | All databases | CAM-8177 |
| 7.7 | engine_7.7_patch_7.7.5_to_7.7.6.sql | Add indices to improve historic activity instance statistics | All databases | CAM-8485 |
| 7.7 | engine_7.7_patch_7.7.8_to_7.7.9_1.sql | Add indexes on Process Definition ID and End Time for Historic Process Instance and Historic Activity Instance | All databases | CAM-8833 |
| 7.7 | engine_7.7_patch_7.7.8_to_7.7.9_2.sql | Add a missing index on foreign key to prevent deadlocks. | DB2, SQL Server | CAM-9165 |
| 7.8 | engine_7.8_patch_7.8.0_to_7.8.1.sql | Add indices to improve historic activity instance statistics. This is the same patch as engine_7.7_patch_7.7.5_to_7.7.6.sql. | All databases | CAM-8485 |
| 7.8 | engine_7.8_patch_7.8.4_to_7.8.5.sql | Add indexes on Process Definition ID and End Time for Historic Process Instance and Historic Activity Instance. This is the same patch as engine_7.7_patch_7.7.8_to_7.7.9_1.sql. | All databases | CAM-8833 |
| 7.8 | engine_7.8_patch_7.8.7_to_7.8.8.sql | Add a missing index on foreign key to prevent deadlocks. This is the same patch as engine_7.7_patch_7.7.8_to_7.7.9_2.sql. | DB2, SQL Server | CAM-9165 |
| 7.8 | engine_7.8_patch_7.8.8_to_7.8.9.sql | Drop ACT_IDX_JOB_HANDLER index causing issues on DB2. | DB2 | CAM-7676 |
| 7.8 | engine_7.8_patch_7.8.11_to_7.8.12.sql | Add index to improve history cleanup performance. | All databases | CAM-9435 |
| 7.8 | engine_7.8_patch_7.8.12_to_7.8.13_1.sql | Add support for Optimize 2.3. | All databases | CAM-9523 |
| 7.8 | engine_7.8_patch_7.8.12_to_7.8.13_2.sql | Add support for Optimize 2.3. | All databases | CAM-9525 |
| 7.9 | engine_7.9_patch_7.9.0_to_7.9.1.sql | Add index to improve historic operation logs performance. | All databases | CAM-9006 |
| 7.9 | engine_7.9_patch_7.9.1_to_7.9.2.sql | Add a missing index on foreign key to prevent deadlocks. This is the same patch as engine_7.8_patch_7.8.7_to_7.8.8.sql. | DB2, SQL Server | CAM-9165 |
| 7.9 | engine_7.9_patch_7.9.2_to_7.9.3.sql | Drop ACT_IDX_JOB_HANDLER index causing issues on DB2. This is the same patch as engine_7.8_patch_7.8.8_to_7.8.9.sql. | DB2 | CAM-7676 |
| 7.9 | engine_7.9_patch_7.9.5_to_7.9.6.sql | Add index to improve history cleanup performance. This is the same patch as engine_7.8_patch_7.8.11_to_7.8.12.sql. | All databases | CAM-9435 |
| 7.9 | engine_7.9_patch_7.9.6_to_7.9.7_1.sql | Add support for Optimize 2.3. This is the same patch as engine_7.8_patch_7.8.12_to_7.8.13_1.sql. | All databases | CAM-9523 |
| 7.9 | engine_7.9_patch_7.9.6_to_7.9.7_2.sql | Add support for Optimize 2.3. This is the same patch as engine_7.8_patch_7.8.12_to_7.8.13_2.sql. | All databases | CAM-9525 |
| 7.9 | engine_7.9_patch_7.9.11_to_7.9.12.sql | Add support for Optimize 2.5. | All databases | CAM-10264 |
| 7.10 | engine_7.10_patch_7.10.5_to_7.10.6.sql | Add support for Optimize 2.5. This is the same patch as engine_7.9_patch_7.9.11_to_7.9.12.sql. | All databases | CAM-10264 |
| 7.10 | engine_7.10_patch_7.10.6_to_7.10.7.sql | Add index to improve history cleanup performance.
This patch script is introduced in 7.10.9. If your current patch is 7.10.6, 7.10.7 or 7.10.8, please execute the script to upgrade to 7.10.9+. |
All databases | CAM-10616 |
| 7.10 | engine_7.10_patch_7.10.13_to_7.10.14.sql | Add index to improve Historic Activity Instance query performance. | All databases | CAM-11117 |
| 7.11 | engine_7.11_patch_7.11.2_to_7.11.3.sql | Add index to improve history cleanup performance. This is the same patch as engine_7.10_patch_7.10.6_to_7.10.7.sql. | All databases | CAM-10616 |
| 7.11 | engine_7.11_patch_7.11.7_to_7.11.8.sql | Add index to improve Historic Activity Instance query performance. This is the same patch as engine_7.10_patch_7.10.13_to_7.10.14.sql. | All databases | CAM-11117 |
| 7.11 | engine_7.11_patch_7.11.18_to_7.11.19.sql | Introducing new engine lock properties | All databases | CAM-12590 |
| 7.12 | engine_7.12_patch_7.12.0_to_7.12.1.sql | Add index to improve Historic Activity Instance query performance. This is the same patch as engine_7.11_patch_7.11.7_to_7.11.8.sql. | All databases | CAM-11117 |
| 7.12 | engine_7.12_patch_7.12.10_to_7.12.11.sql | Add support for Optimize 3.2. | All databases | CAM-12383 |
| 7.12 | engine_7.12_patch_7.12.11_to_7.12.12.sql | Introducing new engine lock properties This is the same patch as engine_7.11_patch_7.11.18_to_7.11.19.sql. | All databases | CAM-12590 |
| 7.13 | engine_7.13_patch_7.13.4_to_7.13.5_1.sql | Add index to improve Task query performance. | All databases | CAM-4441 |
| 7.13 | engine_7.13_patch_7.13.4_to_7.13.5_2.sql | Add support for Optimize 3.2. This is the same patch as engine_7.12_patch_7.12.10_to_7.12.11.sql. | All databases | CAM-12383 |
| 7.13 | engine_7.13_patch_7.13.5_to_7.13.6.sql | Introducing new engine lock properties This is the same patch as engine_7.12_patch_7.12.11_to_7.12.12.sql. | All databases | CAM-12590 |
| 7.14 | engine_7.14_patch_7.14.2_to_7.14.3.sql | Re-define ACT_IDX_JOB_HANDLER index to make it work with extended data types on Oracle. | Oracle | CAM-12832 |
Liquibase patch level update
CIB seven comes with a maintained changelog file that Liquibase can consume. This changelog helps Liquibase to keep track of the changes that have been made to your database already. Based on that changelog and the tracking tables, Liquibase determines which changes it needs to apply when instructing it to update your schema. Therefore, the procedure for patch-level updates is equivalent to that for minor version updates.
Manual patch level update
You can find the necessary scripts on our Artifact Repository.
Select the respective patch version you want to update to ($Y) and download the scripts as a zip or tar.gz file.
Open the cibseven-sql-scripts-$Y/upgrade folder to find all available patch scripts.
In case you are using a pre-packaged distribution, the SQL scripts reside in the sql/upgrade folder of the distribution you want to update to.
The patch scripts are named $DATABASENAME_engine_$MINOR_patch_$A_to_$B, with $A being the patch level version to update from, $B the patch level to update to, and $MINOR the minor version they are on, e.g., 7.16.
If you do choose to apply a database patch, then you must apply all patch scripts that are within the bounds of your update path. This means if your current patch version is X.X.1 and you update to X.X.5 you have to execute all patch scripts first where $A ≥ X.X.1 and $B ≤ X.X.5.
Note: Some patches are provided for multiple versions. It is not required to execute them more than once. See the description of the patch version list for information on duplicate fixes.