LinkedIn
Copied!

Table of Contents

Creating and updating external database instances with JDBC URLs

Version:

Only available versions of this content are shown in the dropdown

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.

  • 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.

  • Pega Cloud only: The connection of your external database instances require a VPN connection. Ensure that the CIDR range that you add to a list of allowed connections on the VPN for your environment includes the network for your database endpoints, because the connection of your external database instances requires a VPN.
  1. In the header of Dev Studio, click Create SysAdmin Database .

  2. Enter a short description, and in the Database field, enter a name for the additional database.

    The database name is case-sensitive.
  3. Click Create and open. The Edit <YourNewDatabaseName> Database page appears.

  4. Optional:

    To indicate the system of record, in the Integration system field, press the Down arrow key and select the name of the integration system 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.
    The following table shows some examples of JDBC URLs:
    Database URL
    Oracle jdbc:oracle:thin:@serverName:​1521:​service-name-or-SID
    Microsoft SQL Server jdbc:sqlserver://127.0.0.1:1433;​DatabaseName=database;​SelectMethod=cursor;​SendStringParametersAsUnicode=false
    IBM Db2 jdbc:db2://serverName:port/dbName:​fullyMaterializeLobData=true;​fullyMaterializeInputStreams=true;​progressiveStreaming=2;​useJDBC4ColumnNameAndLabelSemantics=2;
    PostgreSQL jdbc:postgresql:​//serverName:5432/dbName
  7. In the Authentication section, add the required and any optional credentials for the external database connection.

    1. To add standard users, In the Username field, enter the name of a database user who has read and update permission, and in the Password field, enter the password for that user.

      Specify a user that is capable of accepting unqualified table names and converting 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.
    2. Optional:

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

      The admin user is used to configure tables that extend the Pega data schema. The admin user is used for platform-generated schema changes, property optimization, Query Inspector, Query Runner, Schema Tools, and circumstance definitions.
    3. Optional:

      To add users with read-only permissions, in the Read-only username field, enter the name of a database user who has read-only permission, and 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. It is reserved for future use.

  11. Leave the Failover options fields blank. They are reserved for future use.

  12. Optional:

    On the Advanced tab, specify the names of other database instances that should be reachable by views in this database.

    1. Under the Database Name list, click the Add item icon.

    2. In the Database Name field, press the Down arrow key, and select the name of a database instance that views in the external database need to access.

    This database instance describes the EXTERNAL1 database. Your Pega application needs to access a view in EXTERNAL1 that joins data from the DATA1 and DATA2 databases. Do the following steps:
    • Define database instances for DATA1 and DATA2.
    • On the database instance for EXTERNAL1, list DATA1 and DATA2 as other databases.
  13. Click Save.

  14. To test the database connection, on the Database tab, click Test connection.

    • If the test fails, diagnostic information appears in a new window. Modify the database instance until the test succeeds.
    • This test does not test the administrative user, if any, that you might have specified.
  • Configuring connectivity parameters for an external Pega Platform database

    External database connections through a Java Database Connectivity (JDBC) URL are managed by Pega Platform and use connection pools to handle connection traffic. You can modify the default parameters of the connection pools to limit or increase connections, edit their timeout value, and create a new schema name for each database user to optimize connectivity for your external database connection. You can also request to add additional connectivity parameters specific to your external database vendor through My Support Portal.

Did you find this content helpful?

Have a question? Get answers now.

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