Skip to main content

This content has been archived and is no longer being updated.

Links may not function; however, this content may be relevant to outdated versions of the product.

How to filter pzInskey for a range of values using prpcUtils and BIX

Suggest edit Updated on October 2, 2015

Learn how to filter pzInskey property values for a specific range. You can do this using the prpcUtils command line utility with the option for exposing database columns and the Business Intelligence Exchange​ (BIX) command line for data extraction.

Understanding that the pzInskey property is data type VARCHAR, not NUMERIC, is important for getting the results you expect.

Explanation

pzInskey parameters startKey and endKey

When using the command line utility prpcUtils or the command line option for BIX, you can filter the range of values returned for the property pzInskey.

The prpcUtils.properties parameters expose.startKey and expose.endKey

In the prpcUtils.properties file, you can specify values for the parameters expose.startKey and expose.endKey.

# KEYS AND DATE RANGES :

# Use the following arguments to specify a range of pzInsKey or

# pxCreateDateTime properties. If you provide both, only the

# pzInsKey range will be used.

# NOTE: if you provide the pzInsKey range then you may only provide ONE

# class with no descendants. Use the classes.included and provide one class).

# startKey Only instances with a pzInsKey equal to or greater than <start key>

# endKey Only instances with a pzInsKey equal to or less than <end key>

# startDate Only instances with a pxCreateDateTime equal to or greater than <start date>

# endDate Only instances with a pxCreateDateTime equal to or less than <end date>

expose.startKey=

expose.endKey=

expose.startDate=

expose.endDate=

The BIX command line parameters –z and –Z

Using the BIX command line, you can specify values for the parameters –z and –Z.

  • –z – Only extract instances with a pzInsKey equal to or greater than <start key>
  • –Z – Only extract instances with a pzInsKey equal to or less than <end key>

pzInskey property type VARCHAR

The pzInsKey is a text property of type VARCHAR. Therefore, it does not sort like a NUMERIC data type. Understanding this principle is important to successfully filtering the pzInskey property on a range of values.

The following example shows a SELECT statement on the database table pc_work, ordered by pzInsKey, and the results that this statement returns.

SELECT PZINSKEY

FROM PC_WORK

ORDER BY PZINSKEY

The following results clearly show that pzInskey is type VARCHAR, not NUMERIC.

PEGASAMPLE C-1
PEGASAMPLE C-10
PEGASAMPLE C-11
PEGASAMPLE C-2
PEGASAMPLE C-3
PEGASAMPLE C-4
PEGASAMPLE C-5
PEGASAMPLE C-6
PEGASAMPLE C-7
PEGASAMPLE C-8
PEGASAMPLE C-9

Problem scenario

Continuing the basic example, you can see what happens when you specify the range parameters for PEGASAMPLE C-1 to PEGASAMPLE C-10 using either prpcUtils or BIX.

If using prpcUtils.properties, specify the following parameters:

  • expose.startKey= PEGASAMPLE C-1
  • expose.endKey= PEGASAMPLE C-10

If using the BIX command line, specify the following parameters:

  • -z <PEGASAMPLE C-1>
  • -Z <PEGASAMPLE C-10>

In either situation, the SELECT queries return only two work records:

  • PEGASAMPLE C-1
  • PEGASAMPLE C-10

This is not the result you expect. You expect the SELECT queries to return all 11 work items of the example.

To achieve the correct result – namely, all work items within the range – you need to specify the parameters as shown in the following examples.

If using prpcUtils.properties, specify the following parameters:

  • expose.startKey= PEGASAMPLE C-1
  • expose.endKey= PEGASAMPLE C-9

If using the BIX command line, specify the following parameters:

  • -z <PEGASAMPLE C-1>
  • -Z <PEGASAMPLE C-9>

Problem scenario, BIX BatchUpdateException

BIX extracts and writes out a batch of records, one batch at a time. The PegaBIX log file records these events, including any error messages. Issues with the data, syntax errors, a database constraint violation, or some other issue raised by the database might interrupt the processing of a batch of extraction statements.

Different databases respond differently to batch update exceptions. However, in general, the database throws a BatchUpdateException, which BIX records in the PegaBIX log file.

Most database systems

In default mode, all successful INSERTS are committed, and the log file lists the pzInskey properties of the failing entries. For some databases, such as MS-SQL, all of the insertions from the batch, except the failures, are successfully committed to the database.

Insertions that fail the database return an exception that contains the exact information of the record in error. Therefore, BIX can pinpoint the failure to the pzInskey of the record involved.

Oracle

If an Oracle database encounters an error, it stops processing the remaining operations in the batch. The exception returned by the Oracle database does not point to the exact operation that caused the exception. For that reason, the BIX error log includes all the pzInsKey properties of the records that were in that batch. However, the BIX error log does not indicate which records from that batch were successfully committed and which records failed. The Oracle driver does not provide the information.

Example PegaBIX log file, ORA-01438

15:08:01,098 [<server>] (engine.database.ExtractImpl) ERROR - Batch Update Exception: ORA-01438: value larger than specified precision allowed for this column

java.sql.BatchUpdateException: ORA-01438: value larger than specified precision allowed for this column

at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:566)

at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9365)

at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:210)

at com.pega.pegarules.engine.database.ExtractImpl.addDbRow(ExtractImpl.java:3733)

at com.pega.pegarules.engine.database.ExtractImpl.writeRow(ExtractImpl.java:3513)

at com.pega.pegarules.engine.database.ExtractImpl.WritetoOutput(ExtractImpl.java:3425)

at com.pega.pegarules.engine.database.ExtractImpl.doExtract(ExtractImpl.java:2365)

at com.pega.pegarules.engine.database.ExtractImpl.runExtract(ExtractImpl.java:1186)

at com.pega.pegarules.engine.database.ExtractImpl.run(ExtractImpl.java:955)

at com.pega.pegarules.engine.database.ExtractImpl.init(ExtractImpl.java:929)

at com.pega.pegarules.engine.database.ExtractImpl.main(ExtractImpl.java:881)

15:08:01,098 [<server>] (engine.database.ExtractImpl) ERROR - Skipping 5 rows due to errors

15:08:01,098 [<server>] (engine.database.ExtractImpl) ERROR - pzInskey of the first failed record is: PEGASAMPLE W-27

15:08:01,098 [<server>] (engine.database.ExtractImpl) ERROR - Failed to execute an insert for the record with pzInsKey: PEGASAMPLE W-27

15:08:01,098 [<server>] (engine.database.ExtractImpl) ERROR - Failed to execute an insert for the record with pzInsKey: PEGASAMPLE W-28

15:08:01,098 [<server>] (engine.database.ExtractImpl) ERROR - Failed to execute an insert for the record with pzInsKey: PEGASAMPLE W-29

15:08:01,098 [<server>] (engine.database.ExtractImpl) ERROR - Failed to execute an insert for the record with pzInsKey: PEGASAMPLE W-3

15:08:01,098 [<server>] (engine.database.ExtractImpl) ERROR - Failed to execute an insert for the record with pzInsKey: PEGASAMPLE W-30

15:08:01,098 [<server>] (engine.database.ExtractImpl) ERROR - Failed to execute an insert for the record with pzInsKey: PEGASAMPLE W-31

Suggested approaches

The approaches for working with pzinskey range filtering with the BIX command line and the prpcUtils command line utility are similar. Use the range filtering parameters to narrow down the problematic work items from the batch, then run the tool again for the remaining work items in the batch.

Using the BIX command line

  1. From the Example PegaBIX log file, ORA-01438, you can see the work items in the failed batch: W-27, W-28, W-29, W-3, W-30, and W-31. Because you do not know which of the work items in the batch failed, you can run the extract for one work item at a time until you find the work item that failed.
  2. For each work item in the failed batch, you can rerun the extract by specifying the same value for pzInskey in the –z and –Z parameters, as shown in this example.

    Using the BIX command line, specify the parameters as shown below:
    -z "PEGASAMPLE W-27" -Z "PEGASAMPLE W-27"

  3. If W-27 extracts successfully, then move on to the next work item until you find the one that is causing the problem. In this example, assume that W-28 is causing the database error during the extraction.​

    -z "PEGASAMPLE W-28" -Z "PEGASAMPLE W-28"

  4. Once you have verified the problem work items and have corrected the problem, you can rerun BIX for the remaining records in that batch.
    Specify the pzInsKey range for the remaining records using the –z and –Z parameters, as shown in this example.
    Using the BIX command line, specify the parameters –
    -z "PEGASAMPLE W-29" -Z "PEGASAMPLE W-31"

Using the prpcUtils command line

  1. Use the same example work items from the approach for BIX: W-27, W-28, W-29, W-3, W-30, and W-31. Again, this example assumes that W-28 is throwing the database exception.
  2. Using the prpcUtils command line with the option for exposing database columns, specify the same value for the startKey and endKey until you determine which work item is throwing the database exception. Start with work item W-27, specifying these parameters:
    expose.startKey= PEGASAMPLE W-27
    expose.endKey= PEGASAMPLE W-27
  3. After the columns of work item W-27 are successfully exposed, move on to work item W-28, specifying these parameters:
    expose.startKey=PEGASAMPLE W-28
    expose.endKey=PEGASAMPLE W-28
  4. Once you have verified the problem work item and have corrected the problem, you can rerun the expose columns tool for the remaining records in that batch.

    Specify the pzInsKey range for the remaining records using the expose.startKey and expose.endKey properties, as shown in this example:

    expose.startKey= PEGASAMPLE W-29

    expose.endKey= PEGASAMPLE W-31

Related articles

Business Intelligence Exchange (BIX) ParametersAttachment: Business Intelligence Exchange (BIX) 2.3 User GuideAttachment: BIX 6.3 User GuideAttachment: BIX 7.1 User Guide

Tags

Pega Business Intelligence Exchange 2.3 - 7.1 Pega Platform Data Integration Pega Express
Did you find this content helpful? YesNo

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Ready to crush complexity?

Experience the benefits of Pega Community when you log in.

We'd prefer it if you saw us at our best.

Pega.com is not optimized for Internet Explorer. For the optimal experience, please use:

Close Deprecation Notice
Contact us