[JBoss JIRA] (TEIID-3272) JDBC Translator | Segregation of batch execution and commit mode
by Shiveeta Mattoo (JIRA)
[ https://issues.jboss.org/browse/TEIID-3272?page=com.atlassian.jira.plugin... ]
Shiveeta Mattoo updated TEIID-3272:
-----------------------------------
Attachment: batch1.png
> JDBC Translator | Segregation of batch execution and commit mode
> -----------------------------------------------------------------
>
> Key: TEIID-3272
> URL: https://issues.jboss.org/browse/TEIID-3272
> Project: Teiid
> Issue Type: Enhancement
> Components: JDBC Connector
> Reporter: Shiveeta Mattoo
> Assignee: Steven Hawkins
> Labels: Batch-Commit, JDBC-Transator
> Attachments: batch1.png
>
>
> In JDBC Translator, the batch execution and commit are coupled together. Hence, we after every batch execution, a commit is executed.
> This enhancement is for providing an ability to support the commit after a configurable 'x' no. of batches has completed, instead of an automatic commit after every batch.
> The no of batches after which a commit is desired should be a configurable option.
>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 3 months
[JBoss JIRA] (TEIID-3272) JDBC Translator | Segregation of batch execution and commit mode
by Shiveeta Mattoo (JIRA)
[ https://issues.jboss.org/browse/TEIID-3272?page=com.atlassian.jira.plugin... ]
Shiveeta Mattoo commented on TEIID-3272:
----------------------------------------
On debugging the code, I am seeing that the input to : private int[] executeTranslatedCommand(TranslatedCommand translatedComm)
is a single batch of multiple records. Is that an incorrect expectation for the method?
The executed method public void execute() throws TranslatorException, recevies each batch one by one, and delegates processing to
executeTranslatedCommand for each batch which is getting committed by restoreAutoCommit.
> JDBC Translator | Segregation of batch execution and commit mode
> -----------------------------------------------------------------
>
> Key: TEIID-3272
> URL: https://issues.jboss.org/browse/TEIID-3272
> Project: Teiid
> Issue Type: Enhancement
> Components: JDBC Connector
> Reporter: Shiveeta Mattoo
> Assignee: Steven Hawkins
> Labels: Batch-Commit, JDBC-Transator
>
> In JDBC Translator, the batch execution and commit are coupled together. Hence, we after every batch execution, a commit is executed.
> This enhancement is for providing an ability to support the commit after a configurable 'x' no. of batches has completed, instead of an automatic commit after every batch.
> The no of batches after which a commit is desired should be a configurable option.
>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 3 months
[JBoss JIRA] (TEIID-3272) JDBC Translator | Segregation of batch execution and commit mode
by Shiveeta Mattoo (JIRA)
[ https://issues.jboss.org/browse/TEIID-3272?page=com.atlassian.jira.plugin... ]
Shiveeta Mattoo edited comment on TEIID-3272 at 1/5/15 8:33 AM:
----------------------------------------------------------------
The whole intent of the configurable batch commit size is to ensure that although we are capable of using the performance benefit offered by batching the SQL statements, we are able to control the atomicity by configuring the "batch count to commit".
Few scenarios around expected behaviour :
1. Batch size = 100, Batch count to commit = 2, Input record count = 500
If Batch # 3 has any error, then Batch#1,2 will be committed and whole of Batch # 3 will be rolled back, and no further batches processed.
2. Batch size = 100, Batch count to commit = 5, Input record count = 500
If any failure is last batch # 5, none of the records are committed, as all 500 records are logically one atomic transation
3.Batch size = 100, Batch count to commit = 2, Input record count = 500
Provide user an additional -- continueOnError option. If this option is enabled we will process the erroneous batch till the erroneous record is reached :
If Batch#3 has any error, a Savepoint is set at end of Batch#2 and further records pf batch# 3 are executed and committed one by one till the erroneous record is reached and then processing continues after the failed batch.
So only error records can be skipped in this case
was (Author: shiveeta.mattoo):
The whole intent of the configurable batch commit size is to ensure that although we are capable of using the performance benefit offered by batching the SQL statements, we are able to control the atomicity by configuring the "batch count to commit".
Few scenarios around expected behaviour :
1. Batch size = 100, Batch count to commit = 2, Input record count = 500
If Batch # 3 has any error, then Batch#1,2 will be committed and whole of Batch # 3 will be rolled back, and no further batches processed.
2. Batch size = 100, Batch count to commit = 5, Input record count = 500
If any failure is last batch # 5, none of the records are committed, as all 500 records are logically one atomic transation
3.Batch size = 100, Batch count to commit = 2, Input record count = 500
Provide user an additional -- continueOnError option. If this option is enabled we will process the erroneous batch till the erroneous record is reached :
If Batch#3 has any error, a Savepoint is set at end of Batch#2 and further records pf batch# 3 are executed and committed one by one till the erroneous record is reached and then processing stops.
> JDBC Translator | Segregation of batch execution and commit mode
> -----------------------------------------------------------------
>
> Key: TEIID-3272
> URL: https://issues.jboss.org/browse/TEIID-3272
> Project: Teiid
> Issue Type: Enhancement
> Components: JDBC Connector
> Reporter: Shiveeta Mattoo
> Assignee: Steven Hawkins
> Labels: Batch-Commit, JDBC-Transator
>
> In JDBC Translator, the batch execution and commit are coupled together. Hence, we after every batch execution, a commit is executed.
> This enhancement is for providing an ability to support the commit after a configurable 'x' no. of batches has completed, instead of an automatic commit after every batch.
> The no of batches after which a commit is desired should be a configurable option.
>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 3 months
[JBoss JIRA] (TEIID-3272) JDBC Translator | Segregation of batch execution and commit mode
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3272?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3272:
---------------------------------------
> This is how a commit is observed after each batch execution.
But the batch loop is nested within that try finally. So only after all batches have been processed will the commit occur. Are you sure you aren't seeing a scenario where the batching is being broken up at a higher level? Also note that the max batch size only affects prepared inserts.
> JDBC Translator | Segregation of batch execution and commit mode
> -----------------------------------------------------------------
>
> Key: TEIID-3272
> URL: https://issues.jboss.org/browse/TEIID-3272
> Project: Teiid
> Issue Type: Enhancement
> Components: JDBC Connector
> Reporter: Shiveeta Mattoo
> Assignee: Steven Hawkins
> Labels: Batch-Commit, JDBC-Transator
>
> In JDBC Translator, the batch execution and commit are coupled together. Hence, we after every batch execution, a commit is executed.
> This enhancement is for providing an ability to support the commit after a configurable 'x' no. of batches has completed, instead of an automatic commit after every batch.
> The no of batches after which a commit is desired should be a configurable option.
>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 3 months
[JBoss JIRA] (TEIID-3272) JDBC Translator | Segregation of batch execution and commit mode
by Shiveeta Mattoo (JIRA)
[ https://issues.jboss.org/browse/TEIID-3272?page=com.atlassian.jira.plugin... ]
Shiveeta Mattoo edited comment on TEIID-3272 at 1/5/15 8:24 AM:
----------------------------------------------------------------
The whole intent of the configurable batch commit size is to ensure that although we are capable of using the performance benefit offered by batching the SQL statements, we are able to control the atomicity by configuring the "batch count to commit".
Few scenarios around expected behaviour :
1. Batch size = 100, Batch count to commit = 2, Input record count = 500
If Batch # 3 has any error, then Batch#1,2 will be committed and whole of Batch # 3 will be rolled back, and no further batches processed.
2. Batch size = 100, Batch count to commit = 5, Input record count = 500
If any failure is last batch # 5, none of the records are committed, as all 500 records are logically one atomic transation
3.Batch size = 100, Batch count to commit = 2, Input record count = 500
Provide user an additional -- continueOnError option. If this option is enabled we will process the erroneous batch till the erroneous record is reached :
If Batch#3 has any error, a Savepoint is set at end of Batch#2 and further records pf batch# 3 are executed and committed one by one till the erroneous record is reached and then processing stops.
was (Author: shiveeta.mattoo):
The whole intent of the configurable batch commit size is to ensure that although we are capable of using the performance benefit offered by batching the SQL statements, we are able to control the atomicity by configuring the "batch count to commit".
Few scenarios around expected behaviour :
1. Batch size = 100, Batch count to commit = 2, Input record count = 500
If Batch # 3 has any error, then Batch#1,2 will be committed and whole of Batch # 3 will be rolled back, and no further batches processed.
2. Provide user an additional -- continueOnError option. If this option is enabled we will process the erroneous batch till the erroneous record is reached :
If Batch#3 has any error, a Savepoint is set at end of Batch#2 and further records pf batch# 3 are executed and committed one by one till the erroneous record is reached and then processing stops.
> JDBC Translator | Segregation of batch execution and commit mode
> -----------------------------------------------------------------
>
> Key: TEIID-3272
> URL: https://issues.jboss.org/browse/TEIID-3272
> Project: Teiid
> Issue Type: Enhancement
> Components: JDBC Connector
> Reporter: Shiveeta Mattoo
> Assignee: Steven Hawkins
> Labels: Batch-Commit, JDBC-Transator
>
> In JDBC Translator, the batch execution and commit are coupled together. Hence, we after every batch execution, a commit is executed.
> This enhancement is for providing an ability to support the commit after a configurable 'x' no. of batches has completed, instead of an automatic commit after every batch.
> The no of batches after which a commit is desired should be a configurable option.
>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 3 months
[JBoss JIRA] (TEIID-3272) JDBC Translator | Segregation of batch execution and commit mode
by Shiveeta Mattoo (JIRA)
[ https://issues.jboss.org/browse/TEIID-3272?page=com.atlassian.jira.plugin... ]
Shiveeta Mattoo commented on TEIID-3272:
----------------------------------------
The whole intent of the configurable batch commit size is to ensure that although we are capable of using the performance benefit offered by batching the SQL statements, we are able to control the atomicity by configuring the "batch count to commit".
Few scenarios around expected behaviour :
1. Batch size = 100, Batch count to commit = 2, Input record count = 500
If Batch # 3 has any error, then Batch#1,2 will be committed and whole of Batch # 3 will be rolled back, and no further batches processed.
2. Provide user an additional -- continueOnError option. If this option is enabled we will process the erroneous batch till the erroneous record is reached :
If Batch#3 has any error, a Savepoint is set at end of Batch#2 and further records pf batch# 3 are executed and committed one by one till the erroneous record is reached and then processing stops.
> JDBC Translator | Segregation of batch execution and commit mode
> -----------------------------------------------------------------
>
> Key: TEIID-3272
> URL: https://issues.jboss.org/browse/TEIID-3272
> Project: Teiid
> Issue Type: Enhancement
> Components: JDBC Connector
> Reporter: Shiveeta Mattoo
> Assignee: Steven Hawkins
> Labels: Batch-Commit, JDBC-Transator
>
> In JDBC Translator, the batch execution and commit are coupled together. Hence, we after every batch execution, a commit is executed.
> This enhancement is for providing an ability to support the commit after a configurable 'x' no. of batches has completed, instead of an automatic commit after every batch.
> The no of batches after which a commit is desired should be a configurable option.
>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 3 months
[JBoss JIRA] (TEIID-3272) JDBC Translator | Segregation of batch execution and commit mode
by Shiveeta Mattoo (JIRA)
[ https://issues.jboss.org/browse/TEIID-3272?page=com.atlassian.jira.plugin... ]
Shiveeta Mattoo commented on TEIID-3272:
----------------------------------------
I am observing the behaviour of commit after each batch in JDBCUpdateExecution - executeTranslatedCommand method.
Once a batch is processed, the method has a finally block as below
finally {
if (commitType) {
restoreAutoCommit(!succeeded, translatedComm);
}
}
The restoreAutoCommit performs the commit after an individual batch is processed without any exceptions as below
try {
if (!exceptionOccurred) {
connection.commit(); // in JbossAs setAutocommit = true does not trigger the commit.
}
connection.setAutoCommit(true);
This is how a commit is observed after each batch execution.
> JDBC Translator | Segregation of batch execution and commit mode
> -----------------------------------------------------------------
>
> Key: TEIID-3272
> URL: https://issues.jboss.org/browse/TEIID-3272
> Project: Teiid
> Issue Type: Enhancement
> Components: JDBC Connector
> Reporter: Shiveeta Mattoo
> Assignee: Steven Hawkins
> Labels: Batch-Commit, JDBC-Transator
>
> In JDBC Translator, the batch execution and commit are coupled together. Hence, we after every batch execution, a commit is executed.
> This enhancement is for providing an ability to support the commit after a configurable 'x' no. of batches has completed, instead of an automatic commit after every batch.
> The no of batches after which a commit is desired should be a configurable option.
>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 3 months
[JBoss JIRA] (TEIID-3271) JDBC Translator | Incorrect Update count reported on Batched Update Execution
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3271?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3271.
-----------------------------------
Fix Version/s: 8.10
Resolution: Done
Corrected both the jdbcupdateexecution and the requestworkitem handling. The requestworkitem would not report all of the counts if the number of values exceeded the batch size.
> JDBC Translator | Incorrect Update count reported on Batched Update Execution
> -----------------------------------------------------------------------------
>
> Key: TEIID-3271
> URL: https://issues.jboss.org/browse/TEIID-3271
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Reporter: Shiveeta Mattoo
> Assignee: Steven Hawkins
> Labels: Batch-Updates, JDBC-Transator
> Fix For: 8.10
>
>
> Issue is related to Batched updates scenario.
> Typically, the executeBatch() returns an array of integers, and each element of the array represents the update count for the respective update statement.
> However in case of Teiid JDBC Translator, over and above this, we are processing the array to return a sum of all the elements as in seen in JDBCUpdateExecution -> executeTranslatedCommand method
> int[] results = pstatement.executeBatch();
> for (int i=0; i<results.length; i++) {
> updateCount += results[i];
> }
> The bug is logged to fix this to meet the standard behaviour
> Refer thread https://developer.jboss.org/message/914596?et=watches.email.thread#914596 for details
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months