Summary

For most deployments, Pegasystems recommends defining the PegaRULES database connection for Process Commander by creating a J2EE data source in your application server. However, some situations require that you define the data connection in the prconfig.xml file to use the Pegasystems connection pooling implemented in Process Commander.

For example, use prconfig.xml-based connection pooling when deploying Process Commander in Tomcat, and when calling Process Commander Java libraries directly in command-line environments.

To use Process Commander's connection pooling to manage your data sources, update the configuration file (prconfig.xml) to include your database connection information, as described in the next section.

• In a WAR file deployment, this file is located under the contextroot directory for the application in the \WEB-INF\classes subdirectory, for example \apache-tomcat-5.5.16\webapps\prdbutil\WEB-INF\classes.
• In an EAR file deployment, prconfig.xml is located in the /APP-INF/bin directory. If you deployed Process Commander as a binary application, that is, not in an exploded directory structure, you must extract prconfig.xml from the application.
  1. Stop the Process Commander application.
  2. Undeploy the Process Commander application.
  3. Locate the prresources.jar file in the APP-INF\lib directory. Then extract the prconfig.xml file from the .jar.
  4. After editing prconfig.xml, repackage the modified prconfig.xml file into prresources.jar and redeploy the Process Commander application.

For Oracle installations you will also need to create an oracle.conf file and add an additional line to the prconfig.xml file. See Configuring the Oracle 9i/10g Client, version 9.2.0.5 or higher below.

For UDB installations, you will need to create a udb.conf file specifying additional properties needed for the database connection, and add an additional line to the prconfig.xml file referencing the file. See Configuring UDB below.

Suggested Approach

Editing prconfig.xml

1. Open the prconfig.xml file for editing.

As initially distributed, the prconfig.xml file contains an example database connection commented out:

<!--
  <env name="database/drivers" value="
com.microsoft.jdbc.sqlserver.SQLServerDriver;com.mckoi.JDBCDriver;oracle.jdbc.OracleDriver" />
   <env name="database/databases/PegaRULES/url" value="
jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=cursor;
SendStringParametersAsUnicode=false;ProgramName=PR ${SystemName}@${NodeName} ${DBName} #${ConnectionID}" />
   <env name="database/databases/PegaRULES/userName" value="a_username" />
   <env name="database/databases/PegaRULES/password" value="a_password" />
S-->

2. Uncomment these lines by deleting the comment markers <!- and -->

3. In the element database/drivers, change the value to reference the appropriate database driver:

Note: Be sure that the appropriate JDBC drivers and, if necessary, database client software have been installed on your application server as recommended by your database vendor.

4. In the element database/databases/PegaRULES/url, change the value to the URL for your database server:

• Oracle 9i/10g (Native OCI Client):
  jdbc:oracle:oci:@your_tns_name
• DB/2 (Universal Driver):
  jdbc:db2://serverName:port/dbName
• SQL Server 2005
  jdbc:sqlserver://<your_sql_server_host>:1433;SendStringParametersAsUnicode=false;
SelectMethod=cursor;ProgramName=${NodeName}:${SystemName}.${ConnectionID}
• SQL Server 2000 (Type 4):
  jdbc:microsoft:sqlserver://<your_sql_server_host>:1433;SendStringParametersAsUnicode=false;
SelectMethod=cursor;ProgramName=${NodeName}:${SystemName}.${ConnectionID}

Specifically, change the content of value, bolded in the example below, to specify your database:

<env
   name="database/databases/PegaRULES/url"
   value="jdbc:microsoft:sqlserver://localhost:1433;
   SelectMethod=cursor;SendStringParametersAsUnicode=false;
   ProgramName=PR${SystemName}@${NodeName}
   ${DBName} ${ConnectionID}"
/>

5. In the username and password elements, change the values bolded in the following example to the user name and password you created for the database user on your database.

<env
    name="database/databases/PegaRULES/userName"
    value="a_username"
/>
<env
    name="database/databases/PegaRULES/password"
    value="a_password"
/>

Configuring the Oracle 9i/10g Client, version 9.2.0.5 or higher

This section applies to new Process Commander installations using Oracle Client version 9.2.0.5, as well as all later versions, including 10g.

Complete the following procedure to create an oracle.conf file and update Process Commander's prconfig.xml file.

1. Create the oracle.conf file.

You must create a file named oracle.conf that contains an Oracle compatibility switch oracle.jdbc.V8Compatible. This switch allows the 9.2.0.5 client to access a database with the Date (type) column. This file must also include the user ID and password for access to the database.

The format of the oracle.conf file is:

user=userid
password=password
oracle.jdbc.V8Compatible=true

Example:

user=pruser
password=pruserpassword
oracle.jdbc.V8Compatible=true

Note: Do not copy the file from this article and attempt to paste it into your oracle.conf file. Hidden characters in this HTML article may cause problems in the text file.

You can place the oracle.conf file anywhere on the system. However, Pegasystems recommends placing it in the same location as the prconfig.xml file. In a WAR deployment, this file is located under the contextroot directory in either the \WEB-INF\classes subdirectory or the \APP-INF\classes subdirectory. For example, for a WAR deployment on Tomcat, this location is:

<app-server-install-root>\webapps\<contextroot>\WEB-INF\classes\oracle.conf

Example:

D:\apache-tomcat-5.5.16\webapps\prdbutil\WEB-INF\classes\oracle.conf

7. Edit the database node in the prconfig.xml file to add a propertiesFile setting giving the location of the oracle.conf file. The original prconfig.xml file looks like this:

<env name="database/drivers" value="
com.microsoft.jdbc.sqlserver.SQLServerDriver;com.mckoi.JDBCDriver;oracle.jdbc.OracleDriver" />
<env name="database/databases/PegaRULES/url" value="
jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=cursor;
SendStringParametersAsUnicode=false;ProgramName=PR ${SystemName}@${NodeName} ${DBName} #${ConnectionID}" />
<env name="database/databases/PegaRULES/userName" value="a_username" />
<env name="database/databases/PegaRULES/password" value="a_password" />

The propertiesFile setting holds the path to the oracle.conf file. Add it to the file in the same section as the URL, name, and password information. For example:

<env name="database/drivers"
value="com.microsoft.jdbc.sqlserver.SQLServerDriver;com.mckoi.JDBCDriver;
oracle.jdbc.OracleDriver" />
<env name="database/databases/PegaRULES/url"
value="
jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=cursor;
SendStringParametersAsUnicode=false;
ProgramName=PR ${SystemName}@${NodeName} ${DBName} #${ConnectionID}" />
<env name="database/databases/PegaRULES/userName" value="a_username" />
<env name="database/databases/PegaRULES/password" value="a_password" />
<env name="database/databases/PegaRULES/propertiesFile" value="
e:/weblogic/user_projects/domains/pegarules/myserver/stage/prweb/WEB-INF/classes/oracle.conf" />

8. Save and close the prconfig.xml file.

Configuring UDB

UDB requires additional properties to be specified for the database connection. Complete the following procedure to create an udb.conf file containing the required properties and update Process Commander's prconfig.xml file to reference this file.

1. Create the udb.conf file.

The udb.conf file contains system properties that are required in this installation. This file must also include the user ID and password for access to the database. Make sure the user you specify has appropriate grant and execute privileges on the database schema.

The format of the udb.conf file is:

user=<userid>
password=<password>
currentSchema=<user Db schema name>
currentFunctionPath=SYSIBM,SYSFUN,<user Db schema name>

Example:

user=pruser
password=pruserpassword
currentSchema=PRPC52
currentFunctionPath=SYSIBM,SYSFUN,PRPC52

Note: Do not copy the file contents from this article and attempt to paste it into your udb.conf file. Hidden characters in this document can cause problems in the text file.

You can place the udb.conf file anywhere on the system. However, Pegasystems recommends placing it in the same location as the prconfig.xml file. In a WAR deployment, the prconfig.xml file is located under the contextroot directory in either the \WEB-INF\classes subdirectory or the \APP-INF\classes subdirectory. For example for for a WAR deployment on Tomcat, this location is:

<app-server-install-root>\webapps\<contextroot>\WEB-INF\classes\udb.conf

Example:

D:\apache-tomcat-5.5.16\webapps\prdbutil\WEB-INF\classes\udb.conf

2. Update the prconfig.xml file and the udb.conf file with the User ID and password. Edit the prconfig.xml file.

3. Edit the database node in the prconfig.xml file to add a propertiesFile setting giving the location of the oracle.conf file. The original prconfig.xml file looks like this:

<env name="database/drivers" value="
com.microsoft.jdbc.sqlserver.SQLServerDriver;com.mckoi.JDBCDriver;oracle.jdbc.OracleDriver" />
<env name="database/databases/PegaRULES/url" value="
jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=cursor;
SendStringParametersAsUnicode=false;ProgramName=PR ${SystemName}@${NodeName} ${DBName} #${ConnectionID}" />
<env name="database/databases/PegaRULES/userName" value="a_username" />
<env name="database/databases/PegaRULES/password" value="a_password" />

The propertiesFile setting holds the path to the udb.conf file. Add it to the file in the same section as the URL, name, and password information, for example:

<env name="database/drivers" value="
com.microsoft.jdbc.sqlserver.SQLServerDriver;com.mckoi.JDBCDriver;oracle.jdbc.OracleDriver" />
<env name="database/databases/PegaRULES/url" value="
jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=cursor;
SendStringParametersAsUnicode=false;ProgramName=PR ${SystemName}@${NodeName} ${DBName} #${ConnectionID}" />
<env name="database/databases/PegaRULES/userName" value="a_username" />
<env name="database/databases/PegaRULES/password" value="a_password" />
<env name="database/databases/PegaRULES/propertiesFile" value="
D:\apache-tomcat-5.5.16\webapps\prdbutil\WEB-INF\classes\udb.conf " />

4. Save and close the prconfig.xml file.

Additional information

How to set the maximum number of JDBC connections to the PegaRULES database