Hi Team,

I am reporting this bug now, although I have seen the same issue in earlier JDBC versions also. The guide suggests the below points.

“Insert Notifications, Update Notifications, Delete Notifications, and Ordered
Notifications support LOCAL_TRANSACTION mode only.”

“For BatchInsertSQL services, you must use a LOCAL_TRANSACTION
connection. If you do not use LOCAL_TRANSACTION, you will not see a
list of tables in the Tables tab.”

These points are very true when we try to follow the conventional way, that is we try to create a no transaction connection and then try to create the batch services and notifications , then we will see the errors mentioned in the guide. But if we try to do a work around. That is create all the batch services and notifications using local connection and then change the local connection to no transaction , the components still works as long as they are enabled.

I believe this is a BUG as the components should not work like that. Then the batch insert starts behaving like a no tx. Suppose I have 100 transactions and 95th one failed, then ideally batch template should roll back the entire set , but here using no tx , it is inserting the 94 records and then failing for the 95th one. This I suppose is an anomaly. The service should not function when the connection gets changes from local to no tx.

Please share your thoughts. Any SAG team members seeing this post please share your inputs.

Thanks and Regards
Abir Banerjee

Thanks for your time and bringing this to the notice. The best way to report and track this bug (if it so) is via SAG empower, as you can raise a incident or SR for further consultation.

You are performing a work around to change that. Assume you try to do that from file system level, the system will not know unless something fails in the run time.

It was clear from documentation that, batch cannot work with NO. That stands still the same. It might not be supported by SAG Global Support in case of any issues.

-Senthil

Hi Senthil,

You are right , the documentations says that batch does not work with no. But the case is , it works. The template works as long as the components are enabled. For adapter services it continue to work, for notification it works till its disable and then when we try to enable it it gives error. But my point is during run time should there be any check to find out the connection type. I checked the jar files for WmART , batchInsert classes but didn’t find any conclusive point like that. Which suggests that , once the components are tweaked as mentioned it will still continue to run , however the functionality will change. The batch insert adapter will function as a normal insert adapter with No TX functionality.

Thanks and Regards
Abir Banerjee

Also the guide says insert , update , ordered notifications use LOCAL TX only. If a notification uses XA Tx , it will not be enabled.
"Notification should not be configured on a connection with “XA_TRANSACTION” . But it is not explained why it is not allowed. Once the trigger and buffer table are created as long as the notification is not disabled , they work independently of the connection type I believe . ( Please correct me if I am wrong here ). Hence the trigger will continue to fetch the new records and insert in the buffer table. I don’t think the connection type has to do something here. Hence my question is , it is just by compulsion , that Insert , Update , Ordered notification will not get enabled unless we use Local TX.

I did the below research.

1. Tried to create a table and trigger using a custom template with NO TX type. It worked perfectly.
   ---- This means if the transaction type is NO TX for a notification also , it should not be a problem to create the buffer table and trigger on the DB side.

2. Tried to create a table and trigger using custom template with XA type. It failed with below error.
   “ORA-02089: COMMIT is not allowed in a subordinate session”

The reason for that error:

a) I have defined a data source base on Global Transaction (XA)
b) In a Global Transaction, the ‘Two-Phase Commit’ protocol for global transaction processing is automatically being selected. It means that the webMethods server is responsible to complete all of the transactions (or non of them).
c) Now any DDL commands (ie create table) is commit in Oracle.
d) Hence in short – adding a COMMIT to the code (being invoked automatically) is not allowed.

There are few ways to solve this error:

1. Change the data source to use Non-XA
   (and check the “Supports Global Transactions” & “Emulate Two-Phase Commit” buttons)
2. Delete the COMMIT from your code — Not Possible here as it is default Oracle behavior.
3. Use the “PRAGMA AUTONOMOUS_TRANSACTION“. This will kind of create a separate transaction that will allow to use a commit.

For example:

CREATE PROCEDURE XXX AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN …

Hence I can understand that using XA transaction , we will not be able to create the buffer table and trigger , hence notification will not be enabled. But what is the reason behind not able to use a NO Transaction for the same.

Please share your inputs on the same.

Hi Abir,
There are use cases when to use LOCAL and when to use XA.

XA - when you have two or more resources (like different db instances or one db and one jms) and you would want to perform a two phase commit, thats when you will choose XA. In this type of scenario, both the connections cannot be of LOCAL in nature as the adapter design doesn’t allow. One should be XA and the other can be either LOCAL or XA.

If you have just a single database and you are dealing with that db, then you can use LOCAL.

There could be n number of probability combinations that you might try and see something that works different in run time when you change in the file system or during execution but these are not a recommended approach.

I am not sure what you are trying to achieve. Can you please explain your use case? What are you trying to achieve?

regards
Senthil

Hi Senthil,

I am not trying any use case. I am just trying to find the BUG and also just wandering about the reason behind the notes mentioned in the adapter guide. I am looking for the reasons behind those notes as why they are not achievable and all. The guide just mentions the points , no explanation is provided. That was my POC all about.

Regards
Abir Banerjee