Relational Databases

This page provides guidance on how to configure connections to relational databases in Appian.

Why connect relational databases?

Connecting relational databases in Appian brings your organization's information together in a meaningful way and allows you to manage your data more efficiently.

Configuring data sources

Provide data source connection information

Configure the connection information for your data sources in the Appian CR configuration file.

We recommend preserving the transaction isolation setting of READ_COMMITTED (as shown in the examples) to avoid deadlocks and performance issues that may occur with multiple concurrent reads and writes to the same table if you set the transaction isolation to TRANSACTION_SERIALIZABLE.

Note: If you are upgrading from a previous release of Appian, be sure that datasource declarations are ported over correctly. If they are not, it could disrupt running processes that make use of queries or the Query Database smart service.

MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
webapp:
  dataSources:
    primary:
      name: jdbc/MySqlDataSource
      type: javax.sql.DataSource
      factory: com.appiancorp.tomcat.datasource.DataSourceFactory
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://such_as_server.example.com:3306/such_as_mySQL_for_appian?useOldAliasMetadataBehavior=true
      username: username
      passwordSecretKeyRef:
        name: ""
        key: ""
      attributes:
        initialSize: "5"
        maxActive: "200"
        defaultTransactionIsolation: "READ_COMMITTED"
        maxWait: "30000"
        minIdle: "5"
        minEvictableIdleTimeMillis: "90000"
        timeBetweenEvictionRunsMillis: "450000"
        validationQuery: "SELECT 1"
        testOnBorrow: "true"

MariaDB

1
2
3
4
5
6
7
8
9
10
11
12
13
14
webapp:
  dataSources:
    primary:
      name: jdbc/MariaDbDataSource
      type: javax.sql.DataSource
      factory: com.appiancorp.tomcat.datasource.DataSourceFactory
      driverClassName: org.mariadb.jdbc.Driver
      url: jdbc:mariadb://such_as_server.example.com:3306/such_as_MariaDB_for_appian?useOldAliasMetadataBehavior=true
      username: username
      passwordSecretKeyRef:
        name: ""
        key: ""
      attributes:
        ...

Oracle

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
webapp:
  dataSources:
    primary:
      name: jdbc/OracleDataSource
      type: javax.sql.DataSource
      factory: com.appiancorp.tomcat.datasource.DataSourceFactory
      driverClassName: oracle.jdbc.OracleDriver
      url: jdbc:oracle:thin:@//oracle_server.example.com:1521/ORACLE_FOR_APPIAN
      username: username
      passwordSecretKeyRef:
        name: ""
        key: ""
      attributes:
        initialSize: "5"
        maxActive: "200"
        defaultTransactionIsolation: "READ_COMMITTED"
        maxWait: "30000"
        minIdle: "5"
        minEvictableIdleTimeMillis: "90000"
        timeBetweenEvictionRunsMillis: "450000"
        validationQuery: "select 1 from dual"
        testOnBorrow: "true"

If you want to connect your Appian platform to an Oracle database using Kerberos authentication, you can enable Kerberos authentication when providing the data source connection information. Kerberos is a network authentication protocol that enables resources to verify each other's identity over an untrusted network. It offers Oracle databases the capability to integrate with a central authentication service, such as Microsoft Active Directory (AD).

To enable Kerberos authentication for Oracle:

• Configure the java.security.krb5.conf property in your Kubernetes environment
• In the Appian CR configuration file, configure the Kerberos authentication properties:
  • Do not include passwordSecretKeyRef properties. You don't need to provide a password for Kerberos authentication.
  • The value of the username property must align with both the Oracle schema user and the central authentication service user you are using for authentication.
  • Add the following three Kerberos parameters to the connectionProperties:
    • oracle.net.authentication_services=(KERBEROS5)
    • oracle.net.kerberos5_mutual_authentication=true
    • oracle.net.kerberos5_cc_name=<cache location>

Here is an example for Kerberos authentication in the Appian CR configuration file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
webapp:
  dataSources:
    primary:
      name: jdbc/KerberosOracleDataSource
      type: javax.sql.DataSource
      driverClassName: oracle.jdbc.OracleDriver
      url: jdbc:oracle:thin:@//oracle_server.example.com:1521/ORACLE_FOR_APPIAN
      username: kerberos_user
      connectionProperties: |
        oracle.net.authentication_services=(KERBEROS5);
        oracle.net.kerberos5_mutual_authentication=true;
        oracle.net.kerberos5_cc_name=[cache location];
      attributes:
        initialSize: "5"
        maxActive: "200"
        defaultTransactionIsolation: "READ_COMMITTED"
        maxWait: "30000"
        minIdle: "5"
        minEvictableIdleTimeMillis: "90000"
        timeBetweenEvictionRunsMillis: "450000"
        validationQuery: "select 1 from dual"
        testOnBorrow: "true"

SQL Server

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
webapp:
  dataSources:
    primary:
      name: jdbc/SqlServerDataSource
      type: javax.sql.DataSource
      factory: com.appiancorp.tomcat.datasource.DataSourceFactory
      driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
      url: jdbc:sqlserver://such_as_sqlServer.example.com:1433;databaseName=such_as_MS-SQL_for_appian
      username: username
      passwordSecretKeyRef:
        name: ""
        key: ""
      attributes:
        initialSize: "5"
        maxActive: "200"
        defaultTransactionIsolation: "READ_COMMITTED"
        maxWait: "30000"
        minIdle: "5"
        minEvictableIdleTimeMillis: "90000"
        timeBetweenEvictionRunsMillis: "450000"
        validationQuery: "SELECT 1"
        testOnBorrow: "true"

It is recommended to configure the socketTimeout parameter to 120000 (2 minutes) in the connection URL for the SQL Server database. This parameter determines the amount of time the SQL Server JDBC driver will wait for a socket to respond.

You can use Azure Active Directory password and service principal authentication methods for connecting to SQL Server. Simply add an authentication connection property to your connection URL and specify your preferred method.

• ActiveDirectoryPassword: Add authentication=ActiveDirectoryPassword to the connection URL.
• ActiveDirectoryServicePrincipal: Add authentication=ActiveDirectoryServicePrincipal to the connection URL and specify the application/client ID in the username property and secret of a service principal identity in the password property.

PostgreSQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
webapp:
  dataSources:
    primary:
      name: jdbc/PostgreSqlDataSource
      type: javax.sql.DataSource
      factory: com.appiancorp.tomcat.datasource.DataSourceFactory
      driverClassName: org.postgresql.Driver
      url: jdbc:postgresql://pg.example.com:5432/PostgreSQL_for_appian
      username: username
      passwordSecretKeyRef:
        name: ""
        key: ""
      attributes:
        initialSize: "5"
        maxActive: "200"
        defaultTransactionIsolation: "READ_COMMITTED"
        maxWait: "30000"
        minIdle: "5"
        minEvictableIdleTimeMillis: "90000"
        timeBetweenEvictionRunsMillis: "450000"
        validationQuery: "SELECT 1"
        testOnBorrow: "true"

By default, Appian will connect to the public schema, but you can connect to another schema if required. At the end of the url value, add the currentSchema parameter with the name of the schema (?currentSchema=mySchema).

For example, if your schema is named main, the url would look something like the following:

1
2
3
4
5
6
7
8
9
10
11
12
webapp:
  dataSources:
    primary:
      name: jdbc/PostgreSqlDataSource
      type: javax.sql.DataSource
      factory: com.appiancorp.tomcat.datasource.DataSourceFactory
      driverClassName: org.postgresql.Driver
      url: jdbc:postgresql://pg.example.com:5432/PostgreSQL_for_appian?currentSchema=main
      username: username
      passwordSecretKeyRef:
        name: ""
        key: ""

Clean up unused data in PostgreSQL

PostgreSQL does not automatically reclaim storage when individual references to binary large objects (BLOBs) are deleted, and this limitation can result in a large amount of unused storage over time. You may need to regularly run a cleanup utility like vacuumlo to ensure only needed BLOB data is being stored in the database.

DB2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
webapp:
  dataSources:
    primary:
      name: jdbc/Db2DataSource
      type: javax.sql.DataSource
      factory: com.appiancorp.tomcat.datasource.DataSourceFactory
      driverClassName: com.ibm.db2.jcc.DB2Driver
      url: jdbc:db2://your_db2_domain.example.com:50000/such_as_db2_for_appian
      username: username
      passwordSecretKeyRef:
        name: ""
        key: ""
      attributes:
        initialSize: "5"
        maxActive: "200"
        defaultTransactionIsolation: "READ_COMMITTED"
        maxWait: "30000"
        minIdle: "5"
        minEvictableIdleTimeMillis: "90000"
        timeBetweenEvictionRunsMillis: "450000"
        validationQuery: "select 1 from sysibm.sysdummy1"
        testOnBorrow: "true"

Enabling one-way SSL/TLS connections

You can use Secure Socket Layer (SSL) or Transport Layer Security (TLS) in Appian to encrypt a connection to a supported database. SSL/TLS connections provide an additional layer of security by encrypting data transferred between Appian and your database instance or cluster.

In one-way SSL, only Appian validates the server certificate to ensure that it receives data from the intended database server.

To enable one-way SSL/TLS connections to an Appian-supported database:

1. Enable SSL/TLS on your database server.
2. Ensure that a valid SSL certificate has been uploaded to the Trusted Server Certificates in the Admin Console, especially if you are using a self-signed or private certificate authority (CA) signed SSL certificate on your database server.

3. Add SSL connection properties to your database connection URL.

   The following list example connection URLs for each supported database:

   • MariaDB:
     jdbc:mariadb://yourserver.example.com:3306/your_db_name?useOldAliasMetadataBehavior=true&useSSL=true&sslMode=VERIFY_IDENTITY
   • MySQL:
     jdbc:mysql://yourserver.example.com:3306/your_db_name?useOldAliasMetadataBehavior=true&useSSL=true&sslMode=VERIFY_IDENTITY
   • Oracle:
     jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=yourserver.example.com)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=YOUR_DB_NAME)))
   • SQL Server:
     jdbc:sqlserver://yourserver.example.com:1433;databaseName=your_db_name;encrypt=true;sslProtocol=TLS;trustServerCertificate=false
   • PostgreSQL:
     jdbc:postgresql://yourserver.example.com:5432/your_db_name?escapeSyntaxCallMode=callIfNoReturn&ssl=true&sslmode=verify-full

Add data sources to CR configuration file

After creating your data sources, you need to register both Appian and business data sources with Appian.

To do so, add the data sources to the Appian CR configuration file.

Appian data source

This required data source is used to store Appian instance-level data and metadata, such as News posts, CDT and Record Type definitions, and Admin Console properties. The schema is controlled by Appian and does not include any tables created by Designers when building or importing Appian applications. There is only one Appian data source for each installation of Appian.

When creating the database in a MySQL or MariaDB database, set the server's default character set to utf8mb4 and the collation to utf8mb4_unicode_ci, using the following command:

CREATE DATABASE AppianPrimary_schema default character set = "utf8mb4" default collate = "utf8mb4_unicode_ci";

If the database has already been created, ensure that the character set is set to utf8mb4 and the collation is set to utf8mb4_unicode_ci for the database and all of the tables within it, using the following commands:

ALTER DATABASE AppianPrimary_schema character set utf8mb4 collate utf8mb4_unicode_ci;
ALTER TABLE AppianPrimary_schema.table convert to character set utf8mb4 collate utf8mb4_unicode_ci, row_format=DYNAMIC, ALGORITHM=COPY;

The Appian data source must have a different JNDI name and use a different tablespace or database than the data source(s) used to store business data.

Business data sources

Business data sources are used to store business data generated in Appian applications. The business data source schema is controlled by developers, rather than by Appian itself. At least one business data source is required to use data stores, but any number of business data sources can be configured so that applications can write to multiple databases.

Appian Cloud customers can register business data sources with Appian from the Admin Console or with a data source connected system.

Self-managed customers can register business data sources the same way as Appian Cloud customers, or they can configure a business data source in the Appian CR configuration file.

To migrate a data source configured in the Appian CR configuration file to the Admin Console, create a data source in the Admin Console and give it the same name as a data source in the Appian CR configuration file.

To migrate a data source configured in the Appian CR configuration file or from the Admin Console to a connected system:

1. Create a new data source connected system.
2. Update any objects that refer to the data source to point to the new connected system. These objects include:
   • Data store objects.
   • Record type objects.
   • Process model objects with query database smart service nodes.

Configuring restrictions on query results

Case-sensitivity in query text comparisons

When querying data and using text comparison to filter the results (for example, a query rule that only returns results starting with the prefix USA), the case-sensitivity behavior of the filter is determined by the database settings, not an Appian configuration. To ensure your text comparison is case-insensitive, you need to change the default collation settings for your RDBMS.

• MySQL & MariaDB: The collation determines text comparison and sort order.
  • As mentioned in the Internationalization section below, setting the default collate to utf8mb4_unicode_ci will make it case-insensitive.
• Oracle: The system variable NLS_COMP determines case-sensitivity.
  • You can set the RDBMS to be case-insensitive system-wide by modifying the system variable to the following: alter system set NLS_COMP = linguistic;
• SQL Server: The collation determines case-sensitivity and is case-insensitive by default. No changes are needed.
• PostgreSQL 12: A custom nondeterministic collation can be created to change the default text comparison behavior and sort order.
• DB2 & PostgreSQL 11: Do not support case-insensitive comparisons.
  • Consider generating an extra column with text converted to uppercase and converting your query argument to uppercase as well.

Row return limits for the Query Database smart service

In Appian Cloud, the Query Database smart service returns a maximum of 1,000 rows of data.

In a self-managed environment, that maximum is only 10 rows of data. To configure Appian to read additional rows of data from a database query, you can edit the .spec.customProperties field in the Appian CR configuration file:

1
resources.appian.process.application.arv.limit: "<number>"

where <number> specifies the number of rows you want the query to return.

Query rule return size limits

The amount of memory it takes to return query results may affect performance, so the following recommended limits have been set:

• Queries that result in more than 1MB of application server memory usage, by default, are canceled.
• Queries that take longer than 10 seconds are also, by default, canceled.

These settings can be configured as custom properties in the Appian CR configuration file.

See also: Query limits to external databases

Node input and output limits

Appian node inputs and node outputs that handle multiple values from a form are restricted to a limit of up to 1250 indices.

Internationalization

In order to store an extended range of characters (including languages that use multiple-byte character sets and emojis), follow the instructions below for your database. These configurations must be used whenever multi-byte characters appear in a custom data type definition.

• MySQL & MariaDB: When creating the databases, set the server's default character set to utf8mb4 and the collation to utf8mb4_unicode_ci, using the following command:
  • CREATE DATABASE example_db_name default character set = "utf8mb4" default collate = "utf8mb4_unicode_ci";
• Oracle: When creating the database, select Use Unicode AL32UTF8.
• SQL Server: No additional configuration required.
• PostgreSQL: When initializing the database, set the locale to en-US.UTF-8.
• DB2: When creating the databases, set the codeset and territory to UTF-8 and us. Note that the pagesize is also defined during database creation.
• create database example_db_name using codeset UTF-8 territory us pagesize 32768

The database collation settings determine the sort order of characters, which may be different for different languages. Choose the setting that is most appropriate for the primary language of the site.

Although the site can be configured to support users of multiple languages, a single collation must be selected, which affects sorting for all languages. For most languages, the above settings are sufficient.

A site with Swedish as the primary language, however, may choose to set the collation settings in order to have characters that are unique to Swedish sort in the correct order. To do so, complete the following:

• MySQL & MariaDB: set collate to utf8mb4_swedish_ci instead of utf8mb4_unicode_ci when running the command mentioned above.
• Oracle: run the following command:
  • create or replace trigger after_logon after logon on schema begin execute immediate 'alter session set nls_language = ''SWEDISH'''; end;
• SQL Server: run the following command with <DATABASE_NAME> replaced with your database name:
  • create database <DATABASE_NAME> collate Finnish_Swedish_100_CS_AS
• PostgreSQL: run the following command, replacing <DATABASE_NAME> with the name of your database:
  • CREATE DATABASE <DATABASE_NAME> TEMPLATE template0 LC_COLLATE 'sv_SE.UTF-8' LC_CTYPE 'sv_SE.UTF-8'
• DB2: run the following command with <DATABASE_NAME> replaced with your database name:
  • CREATE DATABASE <DATABASE_NAME> COLLATE USING UCA500R1_LSV PAGESIZE 32768

Time zones

The database can be set to any time zone as long as the datetime values persisted from Appian are always returned as the same value when retrieved by Appian. Appian stores and retrieves datetime values as if they are set in the GMT time zone.

If you rely on database functions that are specific to the time zone, such as the NOW or CURTIME functions, it is recommended that you set your database to the GMT time zone to be consistent with Appian's handling of datetime time zones. Alternatively, you must be aware of your databases configured local time zone and shift the value returned by these functions to the equivalent value in GMT.

Optimizing IBM DB2

In order to achieve maximum performance when using DB2 with Appian, the REOPT bind option must be configured to REOPT(ONCE).

See also: IBM DB2 bind option documentation.

Also note that a pagesize of 32768 or greater is required and must be set during the creation of the database.

To apply the REOPT(ONCE) configuration, complete the following for each database used for Appian data source and business data sources:

1. On the DB2 server, navigate to <DB2_HOME>/bin.
2. Execute the DB2 command line processor: ./db2
3. Connect to the database used to store Appian data: connect to <database-name>
   • If multiple databases are used for Appian data source and business data sources, these steps must be followed for each.
4. Run the bind command: bind ../bnd/db2clipk.bnd collection NULLIDR1
5. Grant the user execute privileges: GRANT EXECUTE ON PACKAGE NULLIDR1.SYSSH200 TO <database-user>
   • Note: The grant may fail because the user does not have the authority to grant privileges to itself. Should this occur, you can run the above grant with another user that has admin privileges to the database.
6. To grant another user admin privileges: GRANT DBADM ON DATABASE TO USER <some-other-database-user>

When using a database named appiandata and a user named appianuser, the output looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.4

db2 => connect to appiandata

   Database Connection Information

 Database server        = DB2/NT64 9.7.4
 SQL authorization ID   = APPIANUSER
 Local database alias   = APPIANDATA

db2 => bind ../bnd/db2clipk.bnd collection NULLIDR1

LINE    MESSAGES FOR db2clipk.bnd
------  --------------------------------------------------------------------
      SQL0061W  The binder is in progress.
      SQL0091N  Binding was ended with "0" errors and "0" warnings.

db2 => GRANT EXECUTE ON PACKAGE NULLIDR1.SYSSH200 TO appianuser
DB20000I  The SQL command completed successfully.

db2 => quit

Once configured, the individual data sources are able to specify a the REOPT(ONCE) option by using the connection parameter jdbcCollection=NULLIDR1. The example data source configuration for DB2 above includes this option.

Additionally, it is recommended that the RUNSTATS command be used to periodically update the optimizer information about the database. Consult the IBM DB2 RUNSTATS documentation for details on its usage.

The following is an example of using the command with the built-in TP_FEED_ENTRY table in a schema called APPIANDATA:

1
2
3
4
5
    RUNSTATS ON TABLE "APPIANDATA"."TP_FEED_ENTRY"
    ON COLUMNS ( "CREATED_TS" , "AUTHOR"  )
    WITH DISTRIBUTION ON COLUMNS ( "GROUP_UUID" NUM_FREQVALUES 15 NUM_QUANTILES 25  )
    AND SAMPLED DETAILED INDEXES ALL
      ALLOW WRITE ACCESS;

Troubleshooting

The following basic steps can be performed to help troubleshoot connectivity issues.

• Verify that the database is up and running properly using a database utility or monitoring tool.

• Check that your database driver is stored in the proper location on your application server.

• Check your .spec.customProperties configurations in the Appian custome resource.
  • Verify that you have the proper Appian data source configuration for your metadata repository.
  • Verify that you have the proper listing for your business data repository.
• From the Build view, attempt to create a new CDT from a database table. Verify that the data source shows up as an option and that it shows the appropriate schemas and tables.

Database configuration error messages

The Appian web application will fail to deploy during application server start-up if the Appian data source is not configured in the Appian CR configuration file or is unreachable due to an error. Possible error messages related to this include the following:

The following runtime Appian error messages might also be attributable to an issue with your database configuration.

Oracle Errors

The following error may indicate a problem with your application server's data source configuration.

1
TNS:listener does not currently know of SID given in connect descriptor

MySQL Errors

The following error may indicate that the maximum number of simultaneous client connections to the MySQL database should be increased. Encountering this error is more likely in environments with multiple application servers and heavy load.

1
Too many connections

Based on internal performance testing, Appian recommends setting the following MySQL system variables to at least the values indicated here:

• max_connections=200 (see MySQL documentation)
• innodb_buffer_pool_size=512MB (see MySQL documentation)

See also

• Query Database Smart Service.

Feedback