Question
Okahara
Last activity: 25 Jun 2018 20:45 EDT
When to use "JDBC Connection pool" or "JDBC URL listed below"
Hi,
We are trying to setup an external database connection from Pega 7.3.1. We use JBOSS EAP 7.0.0 for application server. From JBOSS console, we ensured that JDBC connection works (i.e. jdbc:oracle:thin:@//externaldabasehostname:1521/ORCL). However, when we try it from Pega with the selection of "JDBC URL listed below", it throws an error as below:
"Problem encountered when getting a database connection: code: 0 SQLState: 08001 Message: No suitable driver found for xxxx"
I just found the this PDN article (https://community.pega.com/support/support-articles/issue-connecting-external-database-using-database-rule), and I changed the settings from "JDBC URL listed below" to "JDBC Connection Pool". Then it worked fine. I happened to find this JNDI name in the standalone.xml file. I have two questions:
#1. Why does not "JDBC URL listed below" work while "JDBC Connection Pool" works fine? Is this a Pega's defect or am I missing anything?
#2. Which is better to use, "JDBC URL listed below" or "JDBC Connection Pool"?
Thanks,
Accepted Solution
Pegasystems Inc.
PL
#1 - As far as I know this was a defect in 7.2. And it has been developed in 7.3.1
#2 - Yes, both are using JDBC connections. Just one of them gives you much more configuration options.
Cheers.
Pegasystems Inc.
PL
Hi,
Hi,
- Use JDBC Connection Pool to use a connection pool defined by a Java Naming and Directory Interface (JNDI) facility. Complete the JNDI Name field. Your Pega Platform server can use a JNDI datasource to access the PegaRULES database, using the
database/databases/pegarules/dataSourceelement in the prconfig.xml file. So in order to specific JDBC driver library to connect to an external database. If the database you are to define requires a library not yet installed, follow these steps before attempting to connect to the database:- Download and install the JDBC library from ibm.com, oracle.com, sybase.com, microsoft.com or other sites.
- Update server Environment variables to reference the library.
- Update the prconfig.xml file to reference the library.
- Restart the Pega Platform server.
-
Consult database vendor installation instructions for more details.
Use Connect SQL rules ( Rule-Connect-SQL rule type) to access relational databases other than the PegaRULES database.
- Use JDBC URL listed below to use a Universal Resource Locator to find the Java Database Connectivity information. Complete the JDBC URL field. And this is using the defaults
So the JDBC URL definition may look as below:
| JDBC Definition | |||||||||||||
| JDBC URL | This field appears only when you select Use JDBC URL below for the How to Connect field.
Enter the full URL. For example:
where Proprietary information hidden is the node name or IP address of the server hosting the database, and 1433 or 1521 is the TCP/IP port number for database access. Note: Precise syntax requirements vary depending on server platform, database version, and JDBC client; additional parameters may be required. Consult your database administrator (DBA) for details.
If this Data-Admin-DB-Name instance is to connect to the PegaRULES database, consult the Installation Guide for your platform. If your PegaRULES database is hosted by Oracle 9, the Type 2 (OCI or thick client) driver is required; the thin client (Type 4) works with Oracle 10g. Sybase databases are accessible only through Connect SQL rules. Sybase is not supported as a host for the PegaRULES database . Do not place any spaces or other extraneous characters before or after the JDBC URL value. |
And in case of JDBC connection pool :
| JDBC Datasource | |
| JNDI Name | This field appears only when you select Use JDB Connection Pool for the How to Connect field.
Enter the JNDI name of the data source for your connection pool. The procedure for defining a connection pool depends on two factors:
Note: If during installation of Pega Platform the installer specified a JDBC Datasource for connecting to the PegaRULES database, the username and password for that data source may appear in the prconfig.xml file. These two entries are not necessary; you can delete them from the prconfig.xml file. During startup, Pega Platform obtains the username and password for the database from authentication facilities of Tomcat (or your hosting application server).
|
| Admin JNDI Name | Optional. Leave blank unless (1) this database name instance is to access the PegaRULES database using JNDI, (2) the primary database user account that you entered in the JNDI name field above does not have the ALTER TABLE and CREATE TABLE capabilities, and you want developers to have any of these capabilities:
(Some of these capabilities have additional prerequisites.) Identify a second, distinct data source for the JNDI location (typically jdbc/AdminPegaRULES) that defines a PegaRULES database connection for which the account does have both the ALTER TABLE and CREATE TABLE capabilities. (This ordinarily is distinct from database account used by most application users, who only insert, update, and delete rows). Consult the Installation Guide (for your platform) for additional information on this field. Alternatively, you can specify the username and password in the prconfig.xml file. |
| For any database specified by a JDBC URL, its driver class must be included in the (semicolon-delimited) list of driver classes specified by the prconfig.xml setting "database/drivers". | |
| Read-only JNDI name | Optional. If you are using an external database and want to use the Schema Tools, Query Inspector, or Query Runner landing pages for performing database operations, enter the read-only JNDI name.
Enter the proxy class to use for determining access privileges for this account in the Proxyclass name field on the Advanced tab. |
Please let me know if this is this what you were looking for.
Thanks,
Lukasz
Okahara
You did not answer any of my questions. I have setup both JDBC connection pool and JDBC URL in my past experience and I already know all the steps. Again, my original questions are:
#1. https://community.pega.com/support/support-articles/issue-connecting-external-database-using-database-rule says JBOSS works with JDBC Connection Pool but not with JDBC URL. Is this a Pega's defect or not. Is it possible to get it to work with JDBC URL.
#2. If both JDBC Connection Pool and JDBC URL work, which is better to use.
Thanks,
Pegasystems Inc.
PL
So, please let me know which version of PRPC you are using. Then I try to find if in this version this feature has been added or not. As for the second question. From the developer perspective, I would prefer to use the JDBC Connection Pool, as it's allowed us to tune up this connection if is required. And from my experience, the lots of customers are adding the below params
Pegasystems Inc.
PL
#1. The following article https://community.pega.com/support/support-articles/issue-connecting-external-database-using-database-rule is related to the issues from version 7.2.0. And in version 7.3.1 this has been added.
#.2 Difference is actually very simple
* "JDBC URL listed below" is using the defaults configuration, and is using the libraries which are delivered with the PRPC.
#1. The following article https://community.pega.com/support/support-articles/issue-connecting-external-database-using-database-rule is related to the issues from version 7.2.0. And in version 7.3.1 this has been added.
#.2 Difference is actually very simple
* "JDBC URL listed below" is using the defaults configuration, and is using the libraries which are delivered with the PRPC.
* "JDBC Connection Pool" can be used as well with the default JNDI configuration and with the libraries delivered along with the PRPC. But in case of any issues with the JDBC connection. Allows the developer to tune up the way of connection. And this is one of the major advantages of using a connection pool. If your application confines itself to using generic JDBC you could even point it at a different vendor's database without changing any code! Different pool implementations will provide different settable properties to tune the connection pool. Typical properties include the number of initial connections, the minimum and the maximum number of connections that can be present at any time and a mechanism to purge connections that have been idle for a specific period of time.
In general, optimal performance is attained when the pool in its steady state contains just enough connections to service all concurrent connection requests without having to create new physical database connections. If the pooling implementation supports purging idle connections it can optimize its size over time to accommodate varying application loads over the course of a day. For example, scaling up the number of connections cached in the pool during business hours then dynamically reducing the pool size after business hours.
Thanks,
Lukasz
Okahara
Hi,
#1 - Is this article saying it is a Pega's defect? With JBOSS, should we always use connection pool instead of "JDBC URL listed below"?
#2 - Okay, I understand with connection pool it is possible to manage within JBOSS and no need to maintain DATA-ADMIN-DB-NAME. But as for techical perspective, both are using JDBC connection anyways, and both are about the same. Am I right? Is anything better with JDBC listed below than connection pool? If not, then we will use connection pool.
Thanks,
Accepted Solution
Pegasystems Inc.
PL
#1 - As far as I know this was a defect in 7.2. And it has been developed in 7.3.1
#2 - Yes, both are using JDBC connections. Just one of them gives you much more configuration options.
Cheers.
Pegasystems Inc.
JP
Hi,
I have documented how to setup both "JDBC Connection Pool" and "JDBC URL listed below". It does not make difference from performance perspectives as both are using JDBC connection anyways, but it makes difference from maintainability perspectives. If you use JDBC Connection Pool, the database host name, port, SID are all managed in application server level and you won't need to modify it per environment like Dev and Prod. In that sense I would recommend that you use Connection Pool over JDBC URL listed below approach. Also it is easier to monitor if you use Connection Pool.
Thanks,