Extract skipped/failed records running BIX in PRPC
User was extracting all attachments and Workbasket information using BIX extract rule in PRPC and encountering BatchUpdateException.
Also, user would like to know how to fix these issues -
1. Some of the records are skipped/failed, so he needs to extract the data for missed pzInskey records.
2. Some of the records are missing when he is doing last pylastupdatedate in PR_EXTRACT_TIME. So he is doing pxcreatedatetime as a filter and running every day.
java.sql.BatchUpdateException: ORA-12899: value too large for column "DEMO_RPT"."BIXATTACHMENT"."DESCRIPTION" (actual: 33, maximum: 32)
Steps to Reproduce
Run BIX extract with target output as database
- Increase the target table column length as if the data for the column has any special characters it will exceed the column length because of the extract bytes that they occupy and lead to this error.
- Suggested to use –f option to force the extract to stop at the first error it encounters.(BIX user guide contains all the optional parameters that are available https://pdn.pega.com/documents/bix-63-user-guide)
- Why do we need to truncate the tables and reload, if we have an issue?
- Every time a BIX extract is run it will extract the data and insert the records in the target table.
- BIX extract does not checks if the data is already present nor updates the existing records.
- It blindly extracts and insert the data in the destination table.
- If you have encountered an issue because of which BIX extract was not run successfully. However BIX extract has extracted some data already then you need to fix the issue first which caused the BIX failure.
-z Only extract instances with a pzInsKey equal to or greater than .
-Z Only extract instances with a pzInsKey equal to or less than .
The pzInsKey values of class instances whose extraction resulted in errors are logged automatically.
Option2: Truncate the older partial data in the target table from previous run and rerun the BIX extract to avoid duplicate data for partial data which was extract in the previous run.
More details on error handling in BIX - Chapter 8: Logging and error handling (https://pdn.pega.com/documents/bix-63-user-guide)
BIX extracts and writes out a batch of work object/instance records at a time. These events, including any error messages, are recorded in the PegaBIX log file. Inspecting the file can give you insights into what caused problems during a data extraction.
When BIX is run from within PRPC, any exceptions encountered are displayed on-screen as well as being logged. When running BIX from the command line, you can set the extract to stop when it encounters its first error; or to continue processing, and to log all errors for later consideration.
Errors normally cause the BIX application to terminate. The application may continue to run following certain exceptions:
· An invalid class definition for a single class described in a set of XML files.
· An invalid property definition.
· An invalid property transformation to the specified data type.
Understanding the BatchUpdateException error
Executing a batch of extraction statements may be interrupted because of issues with the data, syntax errors, a database constraint violation, or some other issue raised by the database. Different databases respond differently to batch update exceptions.
However, in general the database throws a BatchUpdateException which BIX records in the PegaBIX log file.
When an error occurs, BIX behaves differently depending on the database platform with which it is working: MS-SQL, DB2 LUW, and DB2 for z/OS servers. The server continues processing the operations in a batch even if one of the operations caused a database error. The database returns an exception which contains the exact information of the record in error, so you can pinpoint the failure to the pzInskey of the record involved. See chapter 7: Using the unique run identifier.
BIX supports two modes of execution: 1. Continue processing records even when there are failures (the default mode). 2. Stop processing when the first error is encountered.
Business Intelligence Exchange (BIX) User Guide V 6.3 30 In default mode, all successful inserts are committed and pzInsKeys of the failing entries are listed in the log file. To rectify the work object and rerun the extract, specify the same value for pzInskey in –z and –Z options. When –f option is in effect, the extraction process terminates after the first error is encountered.
All successful batch inserts up to that point are committed. The batch which caused the failure is rolled back completely.
Oracle stops processing of the remaining operations in the batch if it encounters an error.
The exception returned by the database does not point to the exact operation that caused the exception. For that reason, we include in the BIX error log all the pzInsKeys of the records that were in that batch. We do not indicate, because the Oracle driver does not provide the information, which records from that batch were successfully committed.
BIX supports two modes of execution:
1. Continue processing the records even when there are failures (the default mode).
2. Fail on first error. In default mode, all successful inserts up to the record which failed are committed and the entries in that batch that followed the error entry are not processed. All the pzInsKeys of that batch are written out to the log file.
To rectify the error and rerun BIX for the remaining records in that batch, specify the pzInsKey range for the remaining records using –z and –Z options. When the –f option is in effect, the extraction process terminates after the first error is encountered. All successful batch inserts up to that point are committed. The batch which caused the failure is rolled back completely.
This issue was resolved through the local change: Increase the target table column length as if the data for the column has any special characters it will exceed the column length because of the extract bytes that they occupy and lead to this error.
100% found this useful