Extracting data with BIX

To run an extract from within a PRPC application:

1. From the Rules Explorer select SysAdmin > Extract and select the extract rule you want to use.
2. On the rule form, click the run button. The Run Rule form displays.
3. Enter the number of records to update at a time, and a unique identifier for the extract.
   Note: To run the extract in non-batch mode, a single record at a time, set the number of records to update at a time to 1.
4. Click Perform Extract to begin extracting data from your application. A progress bar informs you when the extract is complete.

After the extract is complete, you can access the output data at the location (or locations, for XML output) you specified in the File Specifications tab of the rule.

In addition to running a BIX extract from within the Designer Studio in PRPC, you can directly call the ExtractImpl Java class in the PRPC application libraries to define and run an extract process. Specify the data to be extracted and the output format by referencing an Extract rule you have created in PRPC. ExtractImpl can either access the Extract rule in the PRPC database or read an XML file containing the XML representation of the rule.

You can add other optional parameters that filter the data by any property, including date ranges, and specify other processing options: see the next section for details on the optional parameters that are available.

Running an extract rule from the command line works in the following way:

1. BIX accesses the PRPC (source) database using the information defined in the BIX prconfig.xml file (see below).
2. Using the information configured in the bix.xml file (see below) and specified in the extract rule, BIX extracts the specified data from the PRPC (source) database.
3. The extracted data is output in the output format specified in the extract rule.
   Note: The output data includes a timestamp and a batch identifier. These help when you want to compares information from different extracts, or isolate information from a specific extract.

Configuring the BIX PRPC configuration files

To complete the command-line environment, provide information to the PRPC system in three configuration files:

• prconfig.xml
• prlogging.xml
• prbootstrap.properties (for v6.1 and newer)

In addition, you can create a pegarules.keyring file to support encryption of the database password that must be supplied with the database connection settings in prconfig.xml. Each of these files is described below.

A. Configure the prconfig.xml file

Edit the prconfig.xml file provided in the configuration directory of your BIX distribution to add database connection settings specifying the source PRPC database from which you want to extract data. If you want to use the Database Schema output format, add a second set of database connection settings specifying the target database to which the extracted data is written.

Create the source database

The prconfig.xml file contains sample database connection settings for an Oracle database:
<env name=”database/drivers” value=”com.microsoft.jdbc.sqlserver.SQLServerDriver;oracle.jdbc.OracleDriver” />
<env name=”database/databases/PegaRULES/url” value=”jdbc:icrosoft:sqlserver://localhost:1433;SelectMethod=cursor;
SendStringParametersAsUnicode=false;ProgramName=PR ${SystemName}@${NodeName} ${DBName} #${ConnectionID}” />
<env name=”database/databases/PegaRULES/icrosof” value=”a_username” />
<env name=”database/databases/PegaRULES/password" value="a_password" />


Modify the code as needed for your PRPC database, as described below.

1. In the database/drivers element, change the value to the appropriate driver for your database, for example:
   

   Database Driver	Value
   Oracle 9i/10g	oracle.jdbc.OracleDriver
   IBM DB/2 Type 4	com.ibm.db2.jcc.DB2Driver
   SQL Server 2005	com.microsoft.sqlserver.jdbc.SQLServerDriver
   SQL Server 2000	com.microsoft.jdbc.sqlserver.SQLServerDriver

2. In the database/databases/PegaRULES/url element, set the value to the connection URL for your database. Here are examples of the appropriate format for some supported databases:

   Database Driver	Value
   Oracle 9i/10g (Native OCI Client)	jdbc:oracle:oci:@your_tns_name
   Oracle 9i/10g (Thin Client)	jdbc:oracle:thin:@myServer:1521:myDatabase
   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}

3. Set the value of database/databases/PegaRULES/userName and database/databases/PegaRULES/password to the user name and password of a user for the PRPC database.

Optionally, define the Target database connection settings

The BIX "Database Schema" output format writes the extracted data directly to a target database. To support this output, you must provide a second set of database connection settings specifying the target database to which the data will be loaded.

In the BIX prconfig.xml file, copy and paste the database connection settings you created for the source database.
In each element name, change PegaRULES to the name of the database you specified as the Output Database Name in the Extract rule, as in the following examples:

Change:	To:
database/databases/PegaRULES/url	database/databases/TargetDB/url
database/databases/PegaRULES/userName	database/databases/TargetDB/userName
database/databases/PegaRULES/password	database/databases/TargetDB/password

Repeat the procedure used for the source database connection to set the target database connection settings to the appropriate values.

Other optional settings

You can add the following settings to the end of the prconfig.xml file to address certain issues that may occur:
Correct XML output
When there are many records to process, a customer found that the resulting XML for some nodes was incorrect. To ensure correct XML output, add this property:
<env name=”compatibility/BIXUseOptimizedClipboardXML” value=”true” />
Record inclusion
To ensure that records added to the database while the extract is running get included in the extract, add this property:
<env name=”bix/useHistoryClasses” value=”true” />

B. Configure the prlogging.xml file

prlogging.xml is a log4j configuration file that enables two log files for the extraction process, PegaBIX and PegaBIX-ALERT. By default, the log files are written to the directory in which the extraction process is started.
You can modify the FileNamePattern for the PegaBIX and PegaBIX-ALERT logs to change the location to which they are output.

C. Configure prbootstrap.properties

From version 6.1 onwards, PRPC stores engine code in the database as a CodeSet. Edit the prbootstrap.properties file provided in the configuration directory of your BIX distribution to add database connection settings specifying the PRPC database which holds the engine code.
Specify the database connection settings using the naming convention:
          <unique-identifier>,<dbtype>.<property>
where

• <unique-identifier> is a distinct value that, together with <dbtype>, identifies a specific data source.
• <dbtype> refers to a set of common properties associated with a given database type. There are four values that may be used:
  • oracle
  • db2
  • mssql
  • derby
• <property> is one of the key values needed to access the data source.

Example: To connect to an Oracle database:
com.pega.pegarules.bootstrap.engineclasses.dbcpsource=example.oracle
com.pega.pegarules.bootstrap.assembledclasses.dbcpsource=example.oracle
example.oracle.url=jdbc:oracle:thin:@localhost:1521:codebase
example.oracle.username=user
example.oracle.password=pass
oracle.jdbc.class=oracle.jdbc.OracleDriver

Specify any additional properties needed to connect to the database using the connectionProperties entry, and provide a semicolon-separated list of values. For example:
example.oracle.connectionProperties=oracle.jdbc.V8Compatible=true
Note: set the system property com.pega.pegarules.bootstrap.ignorejndi to TRUE when using Pega-managed connections. This tells PRPC to ignore the JNDI data source information in the file, and to use the Pega-managed connection settings instead.
Note: In version 6.2, you can replace the following two lines:
com.pega.pegarules.bootstrap.engineclasses.dbcpsource=example.oracle
com.pega.pegarules.bootstrap.assembledclasses.dbcpsource=example.oracle
with this single entry:
com.pega.pegarules.bootstrap.allclasses.dbcpsource=example.oracle

D. Enable password encryption

BIX lets you pass in a PRPC username and password to rule-resolve the Rule-Admin-Extract rule that needs to run. If you want to enable security for the database username and password, you can implement JCE "keyring" encryption by creating a pegarules.keyring file.

You can use keyring encryption to encrypt the username and password. To do this, run the KeyringImpl java class, which accepts three parameters and generates the keyring file. The parameters are:

1. The path where the pegarules.keyring file is to be generated (including the filename)
2. The path to the prconfig.xml file
3. The path to the BIX distribution directory

To encrypt BIX passwords in the same file, pass an additional argument named bix. Running the KeyringImpl Java file with this additional parameter prompts the user for the BIX username and password. The file encrypts the information you enter:

For details about how to set up keyring password encryption, see the PDN article KB-25881 How to encrypt database passwords using a JCE Keyring file .

Preparing a target database

Before extracting data to the target database, you must create a schema to match the extracted data. You can use BIX to generate a SQL file that will create the appropriate schema.

Call the ExtractImpl class, as described below, with the parameter -X <Path><FileName> to generate a DDL for the target database. Run the SQL script against the target database using your database tools.

Note: To use the -X parameter, the output format of the Rule-Admin-Extract rule you specify must be Database schema. The user must have Write access to the directory. If the output format is XML or CSV, an error message appears:

Cannot generate database schema when output type is set to CSV/XML.

Running the command-line extract process

In a properly configured environment, from the command line or in a script, make a Java call to com.pega.pegarules.data.internal.access.ExtractImpl supplying arguments to specify

• JVM memory options: Ensure your JVM is allocated enough memory for the operation by setting the initial Java heap size to 512m (-Xms512m) and the maximum heap size to at least 768m (-Xmx768m).
• system properties giving the location of the PRPC configuration files prconfig.xml, prlogging.xml, and, if used, pegarules.keyring; for example:
  • -Dpegarules.config=config/prconfig.xml
  • -Dpegarules.logging.configuration=config/prlogging.xml
  • -Dpegarules.keyring=config/pegarules.keyring
• an Extract rule definition by providing either
  • The -i switch and the name (pzInsName) of the Extract rule in the source PRPC database.
  • The -I switch and the path and filename of the .xml file containing the XML representation of the Extract rule.
• Optional parameters specifying ranges of the data to be extracted and other processing configuration. For example, you can
  • filter the data by key or date range,
  • specify the number of statements to issue before committing to the target database, or number of records to process before a batch commit,
  • provide a PRPC username and password to allow rule resolution of the Extract rule in the PRPC database.

to use this option, call the main program com.pega.pegarules.pub.PegaRULES, and provide the username and password to ExtractImpl as the first argument. The resulting statement might look like this:
java -Dpegarules.config="./config/prconfig.xml" -Dpegarules.logging.configuration="./config/prlogging.xml" com.pega.pegarules.pub.PegaRULES com.pega.pegarules.data.internal.access.ExtractImpl -a $USERNAME$ -p $PASSWD$ -i PegaSample!SampleBIX_CSV

• specify the number of threads to run if you are running more than one Extract simultaneously, or
• add a unique identifier for the current extract process.

See How to set optional command-line BIX parameters for details on all available parameters.
From PRPC V6.1+, the engine Java classes are stored in the database and not in the file system. The ExtractImpl class cannot be directly run using the Java interpreter. Instead, run the PegaRULES class and pass the ExtractImpl class as an argument. The PegaRULES class is part of the prbootstrap.jar library.
Example: to run the extract “PegaSample!SampleBIX” on a DB2 system:

java –Xms512m –Xmx768m –classpath
".;lib\prbootstrap.jar;lib\prdbcp.jar;lib\db2jcc_v95.jar;lib\jsr94-1.0.jar;%CLASSPATH%" -Dcom.pega.pegarules.bootstrap.properties.url=config\prbootstrap.properties -Dpegarules.config=config\prconfig.xml -Dpegarules.logging.configuration=config\prlogging.xml -Dcom.pega.pegarules.bootstrap.ignorejndi=true com.pega.pegarules.pub.PegaRULES com.pega.pegarules.data.internal.access.ExtractImpl -i PegaSample!SampleBIX

Note: provide the appropriate path and JDBC driver name.

In certain circumstances, you may want to establish BIX as a stand-alone command-line process. For information about using BIX in this way, see How to use a stand-alone command-line BIX process.

See How to work with BIX logging and error handling for information on tracing extract errors and identifying data from partial extracts.