Discussion
Pegasystems Inc.
JP
Last activity: 28 Jun 2023 2:47 EDT
How to set up JDBC Connection to External Database
Hi,
In this post, I will show detailed steps to set up JDBC Connection to external database for on-premise. Please see attached document.
1. Environment
In this document, I used Oracle for external database as many enterprises still use it.
2. JDBC Driver
Download the appropriate JDBC driver that supports external database vendor and version. If target database is Oracle, here is the support matrix.
| No | Oracle Database version | JDBC Jar files specific to the release |
| 1 | 21.x |
ojdbc11.jar with JDK11 and JDK17 ojdbc8.jar with JDK8 and JDK11 |
| 2 | 19.x |
ojdbc10.jar with JDK11 ojdbc8.jar with JDK8, JDK11 |
| 3 | 18.x |
ojdbc8.jar with JDK8 and JDK11 |
| 4 | 12.2 or 12cR2 | ojdbc8.jar with JDK8 |
| 5 | 12.1 or 12cR1 |
ojdbc7.jar with JDK7 and JDK8 ojdbc6.jar with JDK6 |
| 6 | 11.2 or 11gR2 |
ojdbc6.jar with JDK6, JDK7, and JDK8 ojdbc5.jar with JDK5 |
3. Steps
Attached document includes the following configuration patterns:
3-1. JDBC URL w/ Tomcat
3-2. JDBC Connection Pool w/ Tomcat
3-3. JDBC URL w/ JBoss
3-4. JDBC Connection Pool w/ JBoss
* Miscellaneous notes:
- JDBC URL vs JDBC Connection Pool
In the Database rule, you have two options - JDBC URL or JDBC Connection Pool. How are they different? Prior to Pega 7.4, when "JDBC URL" is chosen, Pega connected to external database directly without connection pooling facility while with "JDBC Connection Pool", application server's Connection Pool is used. From Pega 7.4 onwards, when "JDBC URL" is chosen, embedded HikariCP is used automatically. From performance perspectives, I prefer HikariCP over application server's Connection Pool. Be noted, this topic is only for external database. For internal database, application server's Connection Pool is always used regardless of Pega version.
Starting with 8.6, HikariCP configuration can be done from Advanced tab of Database rule without prconfig.xml / DSS. There is no need to access filesystem or reboot system, and everything can be done thru UI. Below is the screenshot of HikariCP configuration parameters and default values. This section appears only when "JDBC URL" is selected in Database tab. For parameters, refer to Hikari GitHub (https://github.com/brettwooldridge/HikariCP).
- SID vs Service
If you are connecting to SID, use colon before SID (ex. jdbc:oracle:thin:@// Proprietary information hidden:1521:<SID>). If you are connecting to Service, use slash before Service (ex. jdbc:oracle:thin:@// Proprietary information hidden:1521/<SERVICE_NAME>). Be noted, SID is no longer recommended by Oracle to be used. Oracle is replacing identifying database by the SID with the services approach.
- Two-Phase Commit
A two-phase commit capability is a Java Transaction API (JTA) feature supporting distributed transactions. When Process Commander is installed as an Enterprise tier application (and the XA version of JDBC database drivers are installed) the Commit and Rollback methods apply to both internal and external classes, implementing a J2EE distributed transaction. For example, a flow may update tables in external relational database such as a customer master file, and also update assignments and properties in the work object. If when the Commit method occurs either the PegaRULES database or the customer master database operation fails, a rollback occurs automatically.
If you need Java Transaction API (JTA): Two-phase commit, you need to use EAR file with J2EE compliant server such as JBoss, WebSphere, or WebLogic. Be noted,Tomcat is not a J2EE compliant server and hence, EAR can't be deployed.
- EAR deployment support
Starting with Pega 8.6, the EAR deployment is deprecated. Starting with Pega 8.8, the EAR deployment is no longer supported.
- JBoss support
Starting with Pega 8.8, all application servers except Tomcat are deprecated. This includes JBoss, WebSphere, and WebLogic. They will continue to be deprecated in version 8.9, and will be out of support in version 8.10.
- External DBMS vendor support
Some people may believe Pega only supports PostgreSQL, Oracle, MS SQLServer, and DB2 for external database connection but this is not correct. This is a line-up of DBMS that are supported for Pega internal database. Pega only provides DDLs to create database objects for these vendors. When you are connecting to external database, it is not really Pega's matter any more. As long as JDBC driver supports the DBMS vendor, we should be able to map their table and Pega class. Here are a couple of examples of JDBC connection strings for miscellaneous DBMS.
| No | DBMS | JDBC connection string example |
|---|---|---|
| 1 | PostgreSQL | jdbc:postgresql:// Proprietary information hidden:5432/dbName |
| 2 | Oracle | jdbc:oracle:thin:@// Proprietary information hidden:1521/dbName |
| 3 | MS SQL Server | jdbc:sqlserver:// Proprietary information hidden:1433;database=dbName;SelectMethod=cursor;SendStringParametersAsUnicode=false |
| 4 | IBM DB2 | jdbc:db2:// Proprietary information hidden:50000/dbName:fullyMaterializeLobData=true;fullyMaterializeInputStreams=true;progressiveStreaming=2;useJDBC4ColumnNameAndLabelSemantics=2; |
| 5 | MySQL | jdbc:mysql:// Proprietary information hidden:3306/dbName |
| 6 | Hitachi HiRDB | jdbc:hitachi:hirdb://DBID=22200,DBHOST= Proprietary information hidden |
| 7 | Fujitsu SymfoWARE | jdbc:symford:// Proprietary information hidden:2050/dbName |
Hope this helps.
Thanks,
Wells Fargo
IN
@KenshoTsuchihashi: Instead of placing the jar on file system, can we rather import the jdbc jar file through pega import feature?
Updated: 28 Jun 2023 6:08 EDT
Pegasystems Inc.
JP
Yes, we can. Did you check the document attached? Sheet 2-1. JDBC URL approach w/ JBoss includes the steps. This works for Tomcat as well (steps are the same for all application servers). FYI, below are the complete steps for JDBC URL approach against external Oracle database.
1. Using the Import wizard, import Oracle JDBC driver. The file will be loaded onto PR_ENGINECLASSES table in RULES schema. Codeset is Customer:06-01-01. Application server restart is required after the file is imported, but you can do this after the next step as it will require application server restart anyways.
2. Create a Dynamic System Settings instance. Application server restart is required for this DSS to take effect.
Owning Ruleset: Pega-Engine
Setting Purpose: prconfig/database/drivers/default
Value: oracle.jdbc.OracleDriver
Yes, we can. Did you check the document attached? Sheet 2-1. JDBC URL approach w/ JBoss includes the steps. This works for Tomcat as well (steps are the same for all application servers). FYI, below are the complete steps for JDBC URL approach against external Oracle database.
1. Using the Import wizard, import Oracle JDBC driver. The file will be loaded onto PR_ENGINECLASSES table in RULES schema. Codeset is Customer:06-01-01. Application server restart is required after the file is imported, but you can do this after the next step as it will require application server restart anyways.
2. Create a Dynamic System Settings instance. Application server restart is required for this DSS to take effect.
Owning Ruleset: Pega-Engine
Setting Purpose: prconfig/database/drivers/default
Value: oracle.jdbc.OracleDriver
3. Create a Data-Admin-DB-Name instance. Configure "use JDBC URL listed below" and enter jdbc:oracle:thin:@// Proprietary information hidden:1521/PDB1 for JDBC URL. Then enter Oracle username and password. Save the changes.
4. That's it. You can now try "Test connection" button in the rule form and make sure status value is "Good".
Thanks,
Ford Motor Company
US
com.ibm.db2.jcc.DB2Driver;com.teradata.jdbc.TeraDriver;com.simba.googlebigquery.jdbc.Driver;
Sample BigQuery JDBC URL:
jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=********;OAuthType=1;
Sample Teradata JDBC URL:
jdbc:teradata://ServerName_WithoutPort/DATABASE=DatabaseName
Sample SAP BI DB2 JDBC URL:
jdbc:db2://sapb2pdb.***:Port/DatabaseName:fullyMaterializeLobData=true;fullyMaterializeInputStreams=true;progressiveStreaming=2;useJDBC4ColumnNameAndLabelSemantics=2;