https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaa-p/13839305#M4870148 <P>I do not have a quote on hand, but IIRC if the grouped transaction fails (and therefore all according statements fail), DBREMOTE repeats each original transaction individually (as with -g 1).</P> Thu, 08 Aug 2024 04:30:39 GMT VolkerBarth 2024-08-08T04:30:39Z https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaq-p/13839303 <P>SQL Anywhere 17</P> <P>According to the help the SQL Remote -g n option groups transactions containing fewer then n (default 20) operations.</P> <P>My question is what happens if one of the grouped transactions fails, do all the transactions in the group fail? </P> <P>Thanks, Jim</P> Wed, 07 Aug 2024 14:43:07 GMT https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaq-p/13839303 JimDiaz 2024-08-07T14:43:07Z https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaa-p/13839305#M4870148 <P>I do not have a quote on hand, but IIRC if the grouped transaction fails (and therefore all according statements fail), DBREMOTE repeats each original transaction individually (as with -g 1).</P> Thu, 08 Aug 2024 04:30:39 GMT https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaa-p/13839305#M4870148 VolkerBarth 2024-08-08T04:30:39Z https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaa-p/13839306#M4870149 <P>That would be nice, I hope someone from SAP will validate this.</P> Mon, 12 Aug 2024 09:52:02 GMT https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaa-p/13839306#M4870149 JimDiaz 2024-08-12T09:52:02Z https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaa-p/13839304#M4870147 <P>Volker is correct.</P> <P>If a grouped transaction fails, dbremote attempts to re-apply the operations without grouping them. Below, you can see that the insert with VALUES (1000041,1,'27bdeb46-c8eb-42ef-b1f8-d61a78bf7acb') will fail. The entire grouped transaction is rolled back, and then the individual commits are applied after being un=-grouped.</P> <P>Reg</P> <PRE>I. 2024-08-12 16:10:16. SQL Remote Message Agent Version 17.0.11.7820 [snip copyright] I. 2024-08-12 16:10:16. I. 2024-08-12 16:10:16. 1: -c I. 2024-08-12 16:10:16. 2: **************************************** I. 2024-08-12 16:10:16. 3: -v I. 2024-08-12 16:10:16. 4: -o I. 2024-08-12 16:10:16. 5: cons.txt I. 2024-08-12 16:10:16. 6: -qc I. 2024-08-12 16:10:16. Received message from "rem1" (0-0000710632-0000712968-0) I. 2024-08-12 16:10:16. Applying message from "rem1" (0-0000710632-0000712968-0) I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000031,1000001,'ab8b77eb-f83f-4616-9b5e-18dfb100649b') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000032,1000002,'b71976d5-4cec-4b07-a669-0c10e61881fd') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000033,1000003,'e410ca5b-9b10-4e5f-ab13-38a896f720e3') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000034,1000004,'8db3a6b6-e614-47b8-859d-e0bf485435be') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000035,1000005,'4d58acce-794b-4201-9355-7c038c5affba') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000036,1000006,'462788fc-1083-43ff-9c9c-246700592501') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000037,1000007,'8734b6a3-8b17-4644-940c-a4064e6cbc61') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000038,1000008,'daa3bf01-155e-466f-88fd-b7add6b19968') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000039,1000009,'51033e11-28ce-44e1-8bfd-4c77c254d30a') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000040,1000010,'fa6f7b6d-f6e8-49a8-8b63-9006cef6fb88') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000041,1,'27bdeb46-c8eb-42ef-b1f8-d61a78bf7acb') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000042,2,'a8bd4682-6d48-45a4-8f30-6fe7902971bf') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000043,3,'ef194591-e295-485a-a11b-bd7f97bcacd8') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000044,4,'e18d3354-6960-4646-bbc6-f82d8e295bef') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000045,6,'12ead360-3fae-418d-a91c-9debaa000f45') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000046,11,'aa2210de-771a-4602-a7fb-d6a4b75eab03') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000047,13,'a0ae6427-32db-42b3-8ab0-1edf56b7a4da') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000048,14,'e0f6af3f-5572-4819-b1ae-3ea62056753f') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000049,16,'7625803b-5476-4421-b070-bf448223b8e0') I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000050,17,'616342ef-d5ff-4b30-8f1a-55dfe7b7df27') I. 2024-08-12 16:10:16. COMMIT E. 2024-08-12 16:10:16. SQL statement failed: (-194) No primary key value for foreign key 'parent' in table 'child' I. 2024-08-12 16:10:16. ROLLBACK I. 2024-08-12 16:10:16. Applying message from "rem1" (0-0000710632-0000712968-0) I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000031,1000001,'ab8b77eb-f83f-4616-9b5e-18dfb100649b') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000032,1000002,'b71976d5-4cec-4b07-a669-0c10e61881fd') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000033,1000003,'e410ca5b-9b10-4e5f-ab13-38a896f720e3') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000034,1000004,'8db3a6b6-e614-47b8-859d-e0bf485435be') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000035,1000005,'4d58acce-794b-4201-9355-7c038c5affba') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000036,1000006,'462788fc-1083-43ff-9c9c-246700592501') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000037,1000007,'8734b6a3-8b17-4644-940c-a4064e6cbc61') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000038,1000008,'daa3bf01-155e-466f-88fd-b7add6b19968') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000039,1000009,'51033e11-28ce-44e1-8bfd-4c77c254d30a') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000040,1000010,'fa6f7b6d-f6e8-49a8-8b63-9006cef6fb88') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000041,1,'27bdeb46-c8eb-42ef-b1f8-d61a78bf7acb') I. 2024-08-12 16:10:16. COMMIT E. 2024-08-12 16:10:16. SQL statement failed: (-194) No primary key value for foreign key 'parent' in table 'child' I. 2024-08-12 16:10:16. ROLLBACK I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000042,2,'a8bd4682-6d48-45a4-8f30-6fe7902971bf') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000043,3,'ef194591-e295-485a-a11b-bd7f97bcacd8') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000044,4,'e18d3354-6960-4646-bbc6-f82d8e295bef') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000045,6,'12ead360-3fae-418d-a91c-9debaa000f45') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000046,11,'aa2210de-771a-4602-a7fb-d6a4b75eab03') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000047,13,'a0ae6427-32db-42b3-8ab0-1edf56b7a4da') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000048,14,'e0f6af3f-5572-4819-b1ae-3ea62056753f') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000049,16,'7625803b-5476-4421-b070-bf448223b8e0') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000050,17,'616342ef-d5ff-4b30-8f1a-55dfe7b7df27') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. INSERT INTO cons.child(c_pkey,p_pkey,textcol) VALUES (1000051,18,'fcd3254a-0c9d-4555-b251-a72a1c668fb0') I. 2024-08-12 16:10:16. COMMIT I. 2024-08-12 16:10:16. Received message from "rem2" (0-0000710144-0000710144-0) I. 2024-08-12 16:10:16. Applying message from "rem2" (0-0000710144-0000710144-0) I. 2024-08-12 16:10:16. Scanning logs starting at offset 0000716563 I. 2024-08-12 16:10:16. Transaction log "C:/cons/cons.log" starts at offset 0000695225 I. 2024-08-12 16:10:16. Processing transaction logs from directory "C:/cons/" I. 2024-08-12 16:10:16. Processing transactions from active transaction log I. 2024-08-12 16:10:16. Sending message to "rem1" (0-0000719728-0000720165-0) I. 2024-08-12 16:10:17. Execution completed </PRE> Mon, 12 Aug 2024 10:44:59 GMT https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaa-p/13839304#M4870147 regdomaratzki 2024-08-12T10:44:59Z https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaa-p/13839307#M4870150 <P>Excellent thank you</P> Mon, 12 Aug 2024 15:54:47 GMT https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaa-p/13839307#M4870150 JimDiaz 2024-08-12T15:54:47Z https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaa-p/13839308#M4870151 <P>It's apparently another result of the Golden Watcom Rule... <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Tue, 13 Aug 2024 04:54:16 GMT https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaa-p/13839308#M4870151 VolkerBarth 2024-08-13T04:54:16Z https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaa-p/13839309#M4870152 <P><A href="https://sqlanywhere-forum.sap.com/users/71/reg-domaratzki">@Reg</A>: A nitpicking question: Will the un-grouping leave the original transactions from the remote as-is, or will it re-apply each operation individually? In other words, if a remote transaction consists of, say both a DELETE and an INSERT, and is applied as a group with other transactions, and that fails, will the DELETE and INSERT be still applied as one single transaction?</P> <P>(I'm sure the answer is "yes", as I think I had asked that years ago...).</P> Tue, 13 Aug 2024 11:42:33 GMT https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaa-p/13839309#M4870152 VolkerBarth 2024-08-13T11:42:33Z https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaa-p/13839310#M4870153 <P>Please refer back to your comment about the Golden Watcom Rule. </P> <P>The operations will be applied on the receiving side exactly as they were applied on the sending side. In your example, the DELETE and INSERT be applied as one single transaction.</P> <P>Reg</P> Tue, 13 Aug 2024 11:48:42 GMT https://community.sap.com/t5/technology-q-a/sql-remote-g-option/qaa-p/13839310#M4870153 regdomaratzki 2024-08-13T11:48:42Z