Skip to main content


         This documentation site is for previous versions. Visit our new documentation site for current releases.      
 

Creating and updating external database instances with JDBC URLs

Updated on May 11, 2022

Specify a JDBC URL to create a database data instance so that Pega Platform can access a supported external database. Pega Platform is optimized to connect to external databases via JDBC URLs.

When you deploy on Pega Cloud services environments, use JDBC URLs to define external database instances which your application uses to access and manage connections to that database.

To deploy on-premises or client-managed cloud systems, use JDBC connection pools. For more information, see Creating database instances for JDBC connection pools.

Before you begin:
  • Clients are responsible for ensuring that their connections are encrypted and secured according to their requirements.
  • Pega recommends keeping your chosen database patched, and to not use end-of-life database versions. Connecting to external databases that use deprecated version can lead to connection failures.
  • If you use an Oracle database, you must ensure that your database supports UTC time zones by running the following query:

    SELECT tzname FROM V$TIMEZONE_NAMES where tzname like 'Etc/UTC'

    If the query does not return a record, you must perform one of the following actions:

    • Upgrade the database time zone file using the Oracle DST patch.

      For more information, see the official Oracle documentation.

    • Make a Cloud change request to upgrade your database time zone file by selecting New request in My Support Portal, and select For something I need.

      In the request, state to update all node JVMs with the property -Duser.timezone=UTC.

  • If you use an Oracle database, you must receive the following privileges from your database administrator:
    • SELECT ON SYS.V_$PARAMETER
    • ALTER SESSION

    For additional database privilege requirements, see step 7.

  • If you use an external database other than PostgreSQL or Oracle, define the driver. Defining an PostgreSQL or Oracle driver may cause potential conflict and failures.

    To define other database drivers, download a JDBC driver version that is compatible with the Java version that runs on the Pega Platform application server, then define a new dynamic system setting for the driver. For more information, see Defining the database driver. For details about database support and your driver's Java compatibility, see the Platform Support Guide.

  • If you are on Pega Platform version 8.5 and earlier, specify the connectivity parameters for your JDBC URL external database instances using dynamic system settings or the prconfig file.

    If you are on Pega Platform 8.5, see Configuring connectivity to an external database for more information.

  • If you are on Pega Platform version 8.6 and later, specify the connectivity parameters for your JDBC URL external database instance using the Data-Admin-DB-Name rule. If you specified connectivity parameters for the JDBC URL external database instance through dynamic system settings or the prconfig file on a previous Pega Platform version, you must first delete those settings before creating or updating JDBC URL external database instances.

    For more information, see Deleting dynamic system setting connection parameters.

  1. In the header of Dev Studio, click CreateSysAdminDatabase.
  2. In the Create database tab, enter a short description, and in the Database field, enter a name for the external database.
    The database name is case-sensitive.
  3. Click Create and open.
  4. Optional: To indicate the system of record, in the Integration system field, press the Down arrow key, and then select the name of the integration system that you want to associate with this database.
    The value that you select is for informational purposes only, and does not affect the behavior of the database instance. You can use this value to organize rules for integration connectors, data types, and sources for data pages.
  5. In the How to connect list, click Use JDBC URL listed below.
  6. In the JDBC URL field, enter the JDBC URL.
    The format of the JDBC URL depends on the database platform. Precise syntax requirements and parameters vary depending on the server platform, database version, and JDBC client. Consult your database administrator for details.Note: If the Pega Platform server connects to the selected database using a JDBC URL, the URL can include < and > characters. Some authentication and security software can be configured to reject URLs containing these characters. As a workaround, you should work with your security team to explore if it is possible to update the software settings to allow these characters.
    For example: The following table shows some examples of JDBC URLs:
    DatabaseURL
    Oraclejdbc:oracle:thin:@serverName:​1521:​service-name-or-SID
    Microsoft SQL Serverjdbc:sqlserver://127.0.0.1:1433;​DatabaseName=database;​SelectMethod=cursor;​SendStringParametersAsUnicode=false
    IBM Db2jdbc:db2://serverName:port/dbName:​fullyMaterializeLobData=true;​fullyMaterializeInputStreams=true;​progressiveStreaming=2;​useJDBC4ColumnNameAndLabelSemantics=2;
    PostgreSQLjdbc:postgresql:​//serverName:5432/dbName
  7. In the Authentication section, add the required and optional credentials for the external database connection:
    • To add standard users, in the Username field, enter the name of a database user who has permissions to read and update the database, and then, in the Password field, enter the password for that user.

      Specify a user who can accept unqualified table names and convert them to fully qualified table names. If this database is to be accessed through Connect SQL rules, confirm that this database user has search, update, delete, and other permissions that support the SQL statements in those rules, and that this database is the default database of the user.

    • To add users with administrator permissions, in the Admin username field, enter the name of a database user who has permissions to alter and create tables, and then, in the Admin password field, enter the password for that user.

      The administrator user can configure tables that extend the Pega data schema, including schema changes that are generated by Pega Platform, property optimization, Query Inspector, Query Runner, Schema Tools, and circumstance definitions.

    • To add users with read-only permissions, in the Read-only username field, enter the name of a database user who has read-only permissions, and then, in the Read-only password field, enter the password for that user.
  8. Click Save.
  9. Optional: To specify connection pool parameters and test connectivity to your database, click the Advanced tab.
  10. Leave the Connection count limit field blank.
    The field is reserved for future use.
  11. Leave the Failover options fields blank.
    The fields are reserved for future use.
  12. In the Connection pool parameters for HikariCP section, specify your connection parameters for the JDBC URL database connection:

    For more information, see the Hikari GitHub page for all HCP parameters.

    1. In the maximumConnectionTimeout field, enter the connection timeout in milliseconds.
      If this time is exceeded without Pega Platform making a connection, Pega Platform logs a SQLException.
      • Default: 30000 (30 seconds)
      • Minimum: 250
    2. In the maximumPoolSize field, enter the maximum size that Pega Platform allows the connection pool to reach for both idle and in-use connections.
      If connection pools reach this size and do not contain any idle connections, Pega Platform then blocks calls to getConnection*() for the time that you specify in the maximumConnectionTimeout parameter.
      Important: External databases do not release connections when they are not in use.
      • Standard user – 20 connections
      • Administrator – 5 connections (if an administrator is configured on the external database)
      • Read-only user – 5 connections (if a read-only user is configured on the external database)
    3. In the maxLifetime field, enter the maximum number of milliseconds that Pega Platform keeps an unused connection in a pool.
      In-use connections retire only after Pega Platform closes them. Set the parameter several seconds shorter than any database-imposed lifetime limit.
      • Default: 1800000 (30 minutes)
      • Minimum: 30000 (30 seconds)
      • When you set this parameter to 0, Pega Platform never retires a connection from a pool with the exception of connections that exceed the value for the idleTimeout parameter. For more information, see step 14.
    4. In the poolName field, enter a name for the connection pool.
      Pega Platform uses this name in logs to identify pools and their configurations.
      • Pega Platform auto-generates a name by default
  13. In the Advanced parameters section, specify additional parameters for the JDBC URL connection:
    1. In idleTimeout field, enter the maximum number of milliseconds that a connection can sit idle in a connection pool.
      Pega Platform never retires connections before this timeout.
      Important:
      • The parameter only applies only when parameter minimumIdle is less than maximumPoolSize.
      • Pega Platform determines if a connection is idle within an average variance of an additional 15 seconds, and a maximum variation of 30 seconds.
      • Default: 600000 (10 minutes)
      • Minimum: 10000 (10 seconds)
      • When you set this parameter to 0, Pega Platform never removes connections from the pool.
      When you set this parameter to 0, Pega Platform never removes connections from the pool.
    2. In the minimumIdle field, enter the minimum number of idle connections in a connection pool.
      Idle connections that fall below the maximumPoolSize value instruct Pega Platform to add additional connections to the pool. Leave the default value to permit a fixed size connection pool for maximum connectivity responsiveness.
      • Default: Same as maximumPoolSize.
    3. If your database supports catalogs, in the catalog field, define the metadata that contains database definitions for objects, tables, indexes, and other object.
      If you do not specify the catalog metadata, Pega Platform uses the driver default.
      • Default: Uses the driver default if left unspecified.
    4. If your JDBC URL does not include the driver class name, in the driverClassName field, enter the name.
    5. In the leakDetectionThreshold field, enter the number of milliseconds that a connection remains outside of the connection pool before a potential leak is logged.
      • Default: 0 (off by default)
      • Minimum: 2000 (2 seconds)
    6. In the connectionTestQuery field, enter the connection name that Pega Platform uses to validate a live connection to the external database.
      Important: This property is designed only for legacy non-JDBC4 drivers that do not support JDBC Connection.isValid() API. An error is logged if your driver is not JDBC4 compliant.
    7. In the initializationFailTimeout field, enter the number of milliseconds Pega Platform tries to acquire the first connection with an initialized connection pool before throwing a timeout exception.
      Pega Platform does not start the connection pool if the first connection fails.
      Important: Pega Platform tries to acquire the first connection after the time that you specify in the connectionTimeout parameter.This parameter causes the following system actions:
      • When you set the parameter to 0, Pega Platform tries to obtain a connection.
        • If Pega Platform obtains a connection, but fails validation, Pega Platform does not initialize the connection pool.
        • If Pega Platform does not obtain a connection, Pega Platform still initializes the pool, but later connection attempts might fail.
      • When you set the parameter to a value that is less than 0 (-x), Pega Platform bypasses initial connection attempts, and the connection pool immediately starts.
    8. In the connectionInitSql field, enter an SQL statement that Pega Platform runs after creating a new connection before adding that connection to the pool.
    9. In the validationTimeout field, enter the maximum duration in milliseconds that Pega Platform sends a heartbeat to validate the connection and determine if the connection is still active.
      Important: Property value must be less than the value of the maximumConnectionTimeout parameter.
      • Default: 5000 (5 seconds)
      • Minimum: 250 (.25 seconds)
  14. Optional: To specify the names of other database instances that views in this database can reach, on the Advanced tab, define the database parameters:
    1. In the Database Name list, click the Add item icon.
    2. In the Database Name field, press the Down arrow key, and then select the name of a database instance that views in the external database need to access.
    For example: This database instance describes the EXTERNAL1 database. To configure your application to access a view in EXTERNAL1 that joins data from the DATA1 and DATA2 databases, define database instances for DATA1 and DATA2, and then list DATA1 and DATA2 as other databases on the database instance for EXTERNAL1,
  15. Click Save.
  16. Optional: To test the database connection, on the Database tab, click Test connection.
    This test does not test the administrative user that you specify.
    1. Diagnostic information appears in a new window if the test fails.
    2. Modify the database instance until the test succeeds.
  • Previous topic Creating database instances for prconfig references
  • Next topic Deleting dynamic system settings connection parameters

Related articles

Configuring dynamic system settings

Tags

Pega Platform 8.6 System Administration

Have a question? Get answers now.

Visit the Support Center to ask questions, engage in discussions, share ideas, and help others.

Visit the Support Center

Did you find this content helpful?

Yes
No

Want to help us improve this content?
Suggest an edit

We'd prefer it if you saw us at our best.

Pega.com is not optimized for Internet Explorer. For the optimal experience, please use:

Close Deprecation Notice
Contact us