Community
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

SQL Remote -g option

JimDiaz
Participant

on ‎2024 Aug 07 3:43 PM

1,214

SQL Anywhere 17

According to the help the SQL Remote -g n option groups transactions containing fewer then n (default 20) operations.

My question is what happens if one of the grouped transactions fails, do all the transactions in the group fail?

Thanks, Jim

Know the answer?

Help others by sharing your knowledge.

Answer

Need more details?

Request clarification before answering.

Show replies
Show replies
VolkerBarth
Contributor
‎2024 Aug 08 5:30 AM
0 Likes

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).

JimDiaz
Participant
‎2024 Aug 12 10:52 AM
0 Likes

That would be nice, I hope someone from SAP will validate this.

Accepted Solutions (0)

Answers (1)

Answers (1)

regdomaratzki
Product and Topic Expert
Product and Topic Expert
‎2024 Aug 12 11:44 AM

Volker is correct.

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.

Reg

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
Show replies
Show replies
JimDiaz
Participant
‎2024 Aug 12 4:54 PM
0 Likes

Excellent thank you

VolkerBarth
Contributor
‎2024 Aug 13 5:54 AM

It's apparently another result of the Golden Watcom Rule... 🙂

VolkerBarth
Contributor
‎2024 Aug 13 12:42 PM
0 Likes

@Reg: 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?

(I'm sure the answer is "yes", as I think I had asked that years ago...).

regdomaratzki
Product and Topic Expert
Product and Topic Expert
‎2024 Aug 13 12:48 PM

Please refer back to your comment about the Golden Watcom Rule.

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.

Reg

Ask a Question