BIX Extraction Performance
Hi Team,
I am extracting a full record from the work table of my application through BIX extract in Database format, below is the statistics for the same.
Statistics:
2016-06-28 17:32:06,929 [.PRPCWorkManager : 0] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - # Total Instances retrieved: 362724
2016-06-28 17:32:06,945 [.PRPCWorkManager : 0] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - Elapsed Time (seconds): 4626.313
2016-06-28 17:32:06,945 [.PRPCWorkManager : 0] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - Total CPU Time (seconds): 0.0
2016-06-28 17:32:06,945 [.PRPCWorkManager : 0] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - #Instances Extracted: 362723
Hi Team,
I am extracting a full record from the work table of my application through BIX extract in Database format, below is the statistics for the same.
Statistics:
2016-06-28 17:32:06,929 [.PRPCWorkManager : 0] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - # Total Instances retrieved: 362724
2016-06-28 17:32:06,945 [.PRPCWorkManager : 0] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - Elapsed Time (seconds): 4626.313
2016-06-28 17:32:06,945 [.PRPCWorkManager : 0] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - Total CPU Time (seconds): 0.0
2016-06-28 17:32:06,945 [.PRPCWorkManager : 0] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - #Instances Extracted: 362723
2016-06-28 17:32:06,945 [.PRPCWorkManager : 0] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - #Instances per second: 78.4043362392471
2016-06-28 17:32:06,945 [.PRPCWorkManager : 0] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - #Instances per hour: 282255.6104612896
2016-06-28 17:32:06,945 [.PRPCWorkManager : 0] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - #Instances not extracted due to errors: 1
My customer is using a simple ETL tool for extraction and in 1 second it retrieved around 11,000+ records. And same he is expecting from PEGA BIX.
I have few questions and needed an expert advise on it.
- This extract contains both the columns expose in work table as well as unexposed. I want to understand the number of data extracted in this much time (4626.313 sec) is normal behavior or it is very slow?
- Even if we are going to create indexes is it going to improve the extraction consioderably say total extract of that much data:362724 can be extracted in less that 15 or 20 mins?
- If we mention all the expose columns from the worktable in the extract,still the whole data will be fetched from BLOB?
Accepted Solution
Pegasystems
IN
BIX does support extracting data from external tables which don't have BLOB column (pzPVStream) from 7.1.9 onwards. Since you mentioned earlier that you are using 7.1.8, you would need your ETL to retrieve the data from these tables. Alternatively, we suggest you to upgrade to the latest version of the Pega platform and BIX to get this feature (and others).
Pegasystems
IN
Let me try and answer the questions
1) You can compare the performance of your extract with data provided in the BIX user guide performance section - https://docs-previous.pega.com/bix-721-user-guide (please refer page 43) or https://community.pega.com/sites/default/files/help_v721/procomhelpmain.htm
2) Performance improvements are also recommended in the BIX user guide - https://docs-previous.pega.com/bix-721-user-guide (please refer page 41) or https://community.pega.com/sites/default/files/help_v721/procomhelpmain.htm
3) If the pzPVStream column (BLOB) is present, then it will always read the data from the BLOB as BLOB is our source of truth.
DPIE
AU
Thanks for your input Rajiv, as per the article shared by you it says xml format is having a better performance. I have taken the similar extract in the xml format but it took almost twice time.
2016-06-30 16:41:04,335 [.PRPCWorkManager : 1] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - # Total Instances retrieved: 362738
2016-06-30 16:41:04,347 [.PRPCWorkManager : 1] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - Elapsed Time (seconds): 7864.722
2016-06-30 16:41:04,347 [.PRPCWorkManager : 1] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - Total CPU Time (seconds): 0.0
2016-06-30 16:41:04,347 [.PRPCWorkManager : 1] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - #Instances Extracted: 362738
2016-06-30 16:41:04,347 [.PRPCWorkManager : 1] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - #Instances per second: 46.122164267217585
2016-06-30 16:41:04,347 [.PRPCWorkManager : 1] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - #Instances per hour: 166039.79136198328
2016-06-30 16:41:04,347 [.PRPCWorkManager : 1] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - #Instances not extracted due to errors: 0
Thanks for your input Rajiv, as per the article shared by you it says xml format is having a better performance. I have taken the similar extract in the xml format but it took almost twice time.
2016-06-30 16:41:04,335 [.PRPCWorkManager : 1] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - # Total Instances retrieved: 362738
2016-06-30 16:41:04,347 [.PRPCWorkManager : 1] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - Elapsed Time (seconds): 7864.722
2016-06-30 16:41:04,347 [.PRPCWorkManager : 1] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - Total CPU Time (seconds): 0.0
2016-06-30 16:41:04,347 [.PRPCWorkManager : 1] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - #Instances Extracted: 362738
2016-06-30 16:41:04,347 [.PRPCWorkManager : 1] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - #Instances per second: 46.122164267217585
2016-06-30 16:41:04,347 [.PRPCWorkManager : 1] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - #Instances per hour: 166039.79136198328
2016-06-30 16:41:04,347 [.PRPCWorkManager : 1] [ ] [CCOLCHN_BIX:02.05.70] ( internal.access.ExtractImpl) INFO - #Instances not extracted due to errors: 0
Could you please help me to understand what else parameters I need to consider to improve the performance apart from index creation and prconfig changes, as this seems to be quite slow.
Any suggestions is most welcome as I am doing the POC and want to find the best approach to take the PEGA data in lesser time.
I have one more question:
- I want to extract the data in the output format as databse schema with incremental approach, in that case data will keep on accumulating in DB and will have the identical data in terms of pyID whenever the pyID is getting updated, say it is been updated twice since the first extract then I have 3 entries in the destination table. If the data keeps on accumlating doesn't it is going to hamper the performance down the time.
- Is there a way to achieve the incremental approach with the exact synchronization with the source table.
Pegasystems
IN
Hi Akshay,
1) The performance of the extract depends on the number of properties being extracted and how nested they are
2) The performance is also impacted by the different prconfig settings and database source table indexes, which I am sure you saw in the BIX user guide (or PDN help topics)
You said you took a similar extract for performance comparison. Couple of questions
1) does similar mean in terms of records to be extracted or does it mean it extracts the same number of properties with the same nesting (or hierarchy) and the BLOB sizes for the cases are also equivalent?
2) was the XML file being written on the local drive or a shared filesystem?
-Rajiv
DPIE
AU
Hi Rajiv,
Please find the answer as below:
1.Same number of properties were extracted, actually I saved as the same extract rule for xml output format and ran the extract through an agent.
2.XML was written on the local drive.
Please help with the below query as well:
I want to extract the data in the output format as databse schema with incremental approach, in that case data will keep on accumulating in DB and will have the identical data in terms of pyID whenever the pyID is getting updated, say it is been updated twice since the first extract then I have 3 entries in the destination table. If the data keeps on accumlating doesn't it is going to hamper the performance later down the line.How to avoid this?
Is there a way to achieve the incremental approach with the exact synchronization as having same number of records in source and destination table.
Pegasystems
IN
Is there a way to achieve the incremental approach with the exact synchronization as having same number of records in source and destination table.
There is no direct way available in BIX to overwrite (meaning update / delete) existing entries in the target table. You can refer to the delete queries in https://pdn.pega.com/bix-design-models for details on how to maintain the target table.
DPIE
AU
Hi,
Please clarify the below:
- If all the needed columns are already exposed in the work table, and data needs to be sent to other database. Still BIX can be recommended as a Solution to write data directly to the destination table (without having a staging table)?
- While creating the BIX extract as a databse schema output, I have selected the pagelist,pagegroup properties also in my extracct. As per my understanding for these pagelist and pagegroup type of properties data will be extracted in separate table. While selecting the Generate DDL option in my extract rule, DDL will be generated for this pagelist/pagegroup also?
Pegasystems
IN
If all the needed columns are already exposed in the work table, and data needs to be sent to other database. Still BIX can be recommended as a Solution to write data directly to the destination table (without having a staging table)?
BIX is recommended as it would be a unified solution to extract data from the Pega platform whether all properties are optimized or not. That said, the choice is with the customer on what they want to do.
While creating the BIX extract as a databse schema output, I have selected the pagelist,pagegroup properties also in my extracct. As per my understanding for these pagelist and pagegroup type of properties data will be extracted in separate table. While selecting the Generate DDL option in my extract rule, DDL will be generated for this pagelist/pagegroup also?
Yes, DDL statements will be generated for all tables involved including the separate tables for embedded repeating structures.
DPIE
AU
Thanks Rajiv,
Also I would like to know..what is the difference between running the BIX from designer studio and from command line?
Which approach will be better as performance point of view?
Pegasystems
IN
Hi Akshay,
Command line is better as it will run in its own JVM thus not affecting the resources of other nodes connected to the same PegaRULES database.
Command line also provides many options (command line arguments) which won't be available with the run button of the ruleform.
-Rajiv
DPIE
AU
Thanks Rajiv,
Please help with the below error. Attached is the log for reference.
Thanks Rajiv,
Please help with the below error. Attached is the log for reference.
2016-08-29 07:00:42,113 [.PRPCWorkManager : 3] [ ] [CCOLCHN_BIX:02.05.01] ( internal.access.ExtractImpl) ERROR - Could not return target db statements
com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:319)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.setAutoCommit(SQLServerConnection.java:1576)
at com.pega.pegarules.data.internal.access.DatabaseConnectionImpl.setAutoCommit(DatabaseConnectionImpl.java:798)
at com.pega.pegarules.data.internal.access.ExtractImpl.closeDbStatements(ExtractImpl.java:5301)
at com.pega.pegarules.data.internal.access.ExtractImpl.doExtract(ExtractImpl.java:3606)
at com.pega.pegarules.data.internal.access.ExtractImpl.runExtract(ExtractImpl.java:1730)
at com.pega.pegarules.data.internal.access.ExtractImpl.init(ExtractImpl.java:1383)
at com.pega.pegarules.data.internal.access.ExtractImpl.parseAndRunExtractCommand(ExtractImpl.java:1328)
at com.pega.pegarules.data.internal.access.ExtractImpl.executeCommandLineExtract(ExtractImpl.java:1045)
at com.pega.pegarules.data.internal.PRDataProviderImpl.executeCommandLineExtract(PRDataProviderImpl.java:1314)
at com.pega.pegarules.session.internal.mgmt.Executable.executeCommandLineExtract(Executable.java:8282)
Pegasystems
IN
Hi,
From the exception stacktrace it looks like you are using the pxExtractDataWithArgs activity to execute the rule. I need some details to understand this issue better
- How has the data source configured? Can we check the configuration settings of the data source to see if has been configured correctlly? Please refer to this forum
- How many records are being extracted at one time? Can we enable debug logging on the ExtractImpl class?
-Rajiv
DPIE
AU
Thank you Rajiv,
Thank you Rajiv,
- In past I have extracted the data in database format, only for this particular extract rule I am having this error.The other rule is also running after sometime which is running fine.
- 2016-08-30 07:00:09,231 [.PRPCWorkManager : 3] [ ] [CCOLCHN_BIX:02.05.01] ( internal.access.ExtractImpl) INFO - # Total Instances retrieved: 73
2016-08-30 07:00:09,246 [.PRPCWorkManager : 3] [ ] [CCOLCHN_BIX:02.05.01] ( internal.access.ExtractImpl) INFO - Elapsed Time (seconds): 10.015
2016-08-30 07:00:09,246 [.PRPCWorkManager : 3] [ ] [CCOLCHN_BIX:02.05.01] ( internal.access.ExtractImpl) INFO - Total CPU Time (seconds): 0.0
2016-08-30 07:00:09,246 [.PRPCWorkManager : 3] [ ] [CCOLCHN_BIX:02.05.01] ( internal.access.ExtractImpl) INFO - #Instances Extracted: 73
2016-08-30 07:00:09,246 [.PRPCWorkManager : 3] [ ] [CCOLCHN_BIX:02.05.01] ( internal.access.ExtractImpl) INFO - #Instances per second: 7.289066400399401
2016-08-30 07:00:09,246 [.PRPCWorkManager : 3] [ ] [CCOLCHN_BIX:02.05.01] ( internal.access.ExtractImpl) INFO - #Instances per hour: 26240.639041437844
2016-08-30 07:00:09,246 [.PRPCWorkManager : 3] [ ] [CCOLCHN_BIX:02.05.01] ( internal.access.ExtractImpl) INFO - #Instances not extracted due to errors: 0 - This extract will be extracting all the records (around 3.6 lakhs) as this will be the first run of the extract rule.
- I have enable the logging for the class :com.pega.pegarules.data.internal.access.ExtractImpl, please let me know if this is needed
DPIE
AU
Hi Rajiv,
I have enabled the log level to debug for the specified class.Please refer the log.
Below are the scenarios I noticed:
When I am running the extract with output database name as pega database then for this particular extract rule is throwing the error.
When the same rule is running with output database name other than pega database it is running fine.
Also I need your help on the below error and its root cause to understand and avoid it for coming in future.
ERROR - Batch Failed: pzInskey of the record that was
not inserted
Pegasystems
IN
Hi Akshay,
I do not see any DEBUG log statements in the attached log file. Was the debug level set for that class?
When you mention that it works with a database other than the pega database, what configuration differences are between the two of them?
The error message you highlighted provides you with the pzInsKey of the instance which could not be extracted because of an exception. This detail be used with the -z and -Z options to retry.
-Rajiv
DPIE
AU
Hi Rajiv,
Sorry I have placed the other log. Please refer the log:PegaBIX-2016-Aug-31_UAT.
Agreed for the Error Batch Failed: pzInskey of the record that was not inserted, we have the pzinskey, but there are too many records coming as error which will be very difficult to mention and re-run the rule and we are not getting the reason why it is not getting inserted (in this scenario might be due to com.microsoft.sqlserver.jdbc.SQLServerException: The result set is closed ). But other places where other records are successfully getting inserted and still many are failing and having this error. Please refer the log of 1-sept-2016 for the same.
Pegasystems
IN
Hi Akshay,
- Please note that the failed pzInsKeys are in order. So when you use the -z and -Z options of the command line, you can give the first and last pzInsKeys printed for each batch and extract them all together
- The error messages is
java.sql.BatchUpdateException: String or binary data would be truncated.
This shows that the data being extracted is larger than the column length. The column sizes may need to be altered. This could also happen if the columns have non ASCII characters (Unicode)
Pegasystems
IN
Hi Akshay,
BIX is only an extraction tool. It does not do transformation and load part of the ETL process. The need for BIX to extract the data from the BLOB to a file format or a normalized table structure for downstream ETL tools to consume.
-Rajiv
DPIE
AU
Hi Rajiv,
Currently we are using PEGA 7.1.8
Could you please answer the below queries:
- Is it possible to fetch the data of the class where there is no instances of the class.
- Is it possible to extract the data from two separate independent tables where there is no child relationship among them.
- Is it possible to install BIX 7.1.9 features in PEGA 7.1.8 version. If yes, would there be any issues in extraction?
Pegasystems
IN
Is it possible to fetch the data of the class where there is no instances of the class.
I am not sure I understand. What would you extract if there are no instances of that class?
Is it possible to extract the data from two separate independent tables where there is no child relationship among them.
You can have two separate extract rules, one for each class which maps to their respective tables and extract data from them using BIX
Is it possible to install BIX 7.1.9 features in PEGA 7.1.8 version. If yes, would there be any issues in extraction?
Yes, there will be issues. Each version of the Pega 7 platform comes with its own version of BIX which is part of the "additional products" folder of the Pega 7 platform distribution image.
DPIE
AU
Hi Rajiv,
Thanks for your input.
Regarding the first question. We have a class which is mapped with an external table with no pzPVStream but it is having values in DB. Please refer the attachment.
When I tried to create an extract rule for that class I got an error.
Error Message: No instance found in database.
Could not process : Invalid column name 'pxObjClass'
How to send such data where BLOB is not present through PEGA (is it possible without ETL)?
Accepted Solution
Pegasystems
IN
BIX does support extracting data from external tables which don't have BLOB column (pzPVStream) from 7.1.9 onwards. Since you mentioned earlier that you are using 7.1.8, you would need your ETL to retrieve the data from these tables. Alternatively, we suggest you to upgrade to the latest version of the Pega platform and BIX to get this feature (and others).