SQL Script to Generate Schemas
I'm installing Pega Platform 8.2 for the first time and I'm a little confused on the SQL configuration portion. I would like to use Pega best practices with dual-schema and dual-user configurations but I'm not familiar with creating schemas in SQL. Does anyone have a sql script that creates them per best practices?
***Moderator Edit-Vidyaranjan: Updated Platform Capability***
***Edited by Moderator Marissa to update SR Details***
PEG
GB
Within the installation media you should find a script call generateddl .sh or .bat under the /scripts directory.
You can use this to generate the schema file for your system.
Before running the script you'll need to configure the setupDatabase.properties file for your system.
In particular for the schema generation you'll need to fill in the #CONNECTION INFORMATION.
For example, I'm using (for Oracle): -
# CONNECTION INFORMATION
pega.jdbc.driver.jar=C:/JDBC/ojdbc7.jar
pega.jdbc.driver.class=oracle.jdbc.OracleDriver
pega.database.type=oracledate
pega.jdbc.url=jdbc:oracle:thin:@localhost:1521/PRPC2
pega.jdbc.username=sa
pega.jdbc.password=nottelling
And the schema information: -
# RULES SCHEMA NAME
# The default is the value specified for the user name
rules.schema.name=PEGARULES
# DATA SCHEMA NAME
# The default is the value specified for the rules schema name
data.schema.name=PEGADATA
# CUSTOMER DATA SCHEMA NAME
# The default is the value specified for the data schema name
customerdata.schema.name=PEGACUSTOM
Then run the generateddl script.
Within the installation media you should find a script call generateddl .sh or .bat under the /scripts directory.
You can use this to generate the schema file for your system.
Before running the script you'll need to configure the setupDatabase.properties file for your system.
In particular for the schema generation you'll need to fill in the #CONNECTION INFORMATION.
For example, I'm using (for Oracle): -
# CONNECTION INFORMATION
pega.jdbc.driver.jar=C:/JDBC/ojdbc7.jar
pega.jdbc.driver.class=oracle.jdbc.OracleDriver
pega.database.type=oracledate
pega.jdbc.url=jdbc:oracle:thin:@localhost:1521/PRPC2
pega.jdbc.username=sa
pega.jdbc.password=nottelling
And the schema information: -
# RULES SCHEMA NAME
# The default is the value specified for the user name
rules.schema.name=PEGARULES
# DATA SCHEMA NAME
# The default is the value specified for the rules schema name
data.schema.name=PEGADATA
# CUSTOMER DATA SCHEMA NAME
# The default is the value specified for the data schema name
customerdata.schema.name=PEGACUSTOM
Then run the generateddl script.
If this finishes successfully it should generate the schema file in a subdirectory structure under the /schema directory (where this structure will depend on your DB type)
US Bancorp
US
Thank you for your reply, two questions.
1. After the "schema file " is created, then what?
2. How do I use Windows authentication to SQL instead of an SA/SQL user?
US Bancorp
US
I believe I've figured out the schema portion of this but the Windows auth vs SA still alludes me. The documentation doesn't reference Windows Authentication, only Kerberos, but kerberos is the default used in Windows Authentication so I'm assuming that's the route I need to go. But in the documentation it simply says to change the jdbc.custom.connection.properties value to include integratedSecurity=true. But when I do that, the installation fails stating "Database username must be specified".
Where do I specify the username and password for Kerberos authentication? And if so, that means I have to save the password in clear text in the setupDatabase.properties file?
Pegasystems Inc.
IN
You need to use command line when you are enabling Kerberos authentication.
For this you need to provide the value for jdbc.custom.connection.properties in the setupdatabase.properties and also include all the values of the database connection properties (which includes Database username).
Once you have provided all the values in the setupdatabase.properties file then use install.sh or install.bat script to start the installation and it will consider the setupdatabase.properties file to get the DB connection properties.
US Bancorp
US
Please see the attached screen shot. This is what the documentation says for configuring the jdbc.custom.connection.properties. It doesn't reference where to put the username and password for the authentication if using kerberos authentication. It says to comment them out, save the file, and install from command line. Which I've done, and it errors out stating "Database username must be specified".
Please advise.
Pegasystems Inc.
IN
If you will check the installation guide the topic "Enabling Kerberos authentication" comes after the editing setupdatabase.properties topic as an optional step.
Inside the Editing SetupDatabase.properties topic you have a sub topic with the name "Database connection properties and script argument".
Not sure which version you are trying to install but for Pega 8.1 installation guide this is in page 20.
There it is mentioned below values to be updated in setupdatabase.properties:
If you will check the installation guide the topic "Enabling Kerberos authentication" comes after the editing setupdatabase.properties topic as an optional step.
Inside the Editing SetupDatabase.properties topic you have a sub topic with the name "Database connection properties and script argument".
Not sure which version you are trying to install but for Pega 8.1 installation guide this is in page 20.
There it is mentioned below values to be updated in setupdatabase.properties: