Changing the database for Interaction History and database schema for Predictive Analytics Director
Interaction History uses databases tables that are part of the PegaDATA schema, and the Predictive Analytics Director repository uses the PegaDATA database by default. However, you can use a different database when you want to store your records outside the Pega 7 Platform, or when the PegaDATA database contains too many records.
Changing the database for Interaction History
If you use a different database vendor than the one supporting the Pega 7 Platform database, make sure that the correct JDBC drivers are available. For more details, see More about Database data instances.
- Create a dedicated user for the Interaction History tables with resource and connect permissions by using a database administration tool of your choice.
- Open the
folder in your Pega 7 Platform distribution image.
- Copy the following files to the scripts folder in your local copy of the Pega 7 Platform distribution image:
- Open a command prompt, and go to the scripts folder.
- Run the generateIHddl script with the following arguments to generate the SQL files, for example:
generateIHddl.bat --dbType oracledate --dbDataSchema IHSTORE
|UNIX and Linux
./generateIHddl.sh --dbType oracledate --dbDataSchema IHSTORE
(IBM DB2 for z/OS only) Before running the script, configure the site-dependent properties and edit the setupDatabase.properties file. Running the script takes an extra argument, --zosProperties. For more information, see the Pega 7 Platform Installation Guide for IBM WebSphere and IBM DB2 for z/OS.
- dbType (Oracle: oracledate, IBM DB2 for LUW: udb, IBM DB2 for z/OS: db2zos, PostgreSQL: postgres, MS SQL Server: mssql)
- Open the IH_install_on_<dbType>.sql file. Use your database administration tool to run the SQL file that populates and configures the Interaction History schema.
folder in your local copy of the Pega 7 Platform distribution image to access the generated
- Define a new JDBC data source for Interaction History in the Pega 7 Platform server by following the steps for the application server of your choice. The JNDI entry to configure for this data source is jdbc/ihDataSource.
- Create a database data instance that points to the schema with the Interaction History tables.
- Use a JDBC connection pool to connect to the jdbc/ihDataSource data source.
- In the Records Explorer, go to
- Filter the results by Data-Decision-IH to view the Interaction History database tables.
- Change the database setting of the database table data instances to use the connection that was configured in step 1.
Testing the connectivity to the database throws errors about the table containing internal properties. You can safely ignore these errors.
Besides generating the SQL files to populate an empty schema, the generateIHddl batch/shell script supports drops and upgrade options. Both actions require that you provide the database driver, JDBC URL, and database credentials.
Clear the schema by using the drops action argument. For example, generate the IH_drops_on_oracledate.sql file in the generated folder to clear the tables in an Oracle database.
generateIHddl.bat --action drops --dbSchema IHSTORE --dbType oracledate --driverJar c:\usr\JDBCdrivers\ojdbc6.jar --url jdbc:oracle:thin:@localhost:1521:ORCL --username IH_USER --password IH_USER
Update the contents of the schema by using the upgrade action argument. For example, generate the IH_upgrade_on_oracledate.sql file in the generated folder to update the tables in an Oracle database.
generateIHddl.bat --action upgrade --dbSchema IHSTORE --dbType oracledate --driverJar c:\usr\JDBCdrivers\ojdbc6.jar --url jdbc:oracle:thin:@localhost:1521:ORCL --username IH_USER --password IH_USER
Although running the script should result in removing the generated folder, check the time stamp of the generated files. If the time stamp is not up to date, delete the generated folder and run the script again.
Changing the database for the Predictive Analytics Director repository
- Create the following resources using a database administration tool of your choice:
- A dedicated user for the Predictive Analytics Director repository tables with create table, create index, create view, create sequence, and drop table permissions.
- A dedicated schema for the Predictive Analytics Director repository.
- Create a database instance to connect to the database schema.
- Reference the new database instance from the Predictive Analytics Director Settings landing page. For more information, see Selecting an internal database.
You have created dedicated database tables for Interaction History and the database schema for the Predictive Analytics Director repository. You can now change the default settings and reference the new resources.
Published January 10, 2017 — Updated September 6, 2017