1

In my application which is implemented using SoftwareAG Webmethods, I'm using JMS to process more than 2 millions of records in parallel, so basically each JMS thread will have a batch of records (lets's x1000) to process and then insert into database table (let's call it table A) and after each thread inserts each batch they will send result message on JMS which I will aggregate later to update the process status.

The problem I'm facing now is that the thread will process its batch, insert and put the result message on JMS queue but the insert transactions will get queued in the mssql database but it doesn't wait in the application itself. it considers it as done and continues with the next line of logic.

Therefore the process on each thread is completed and the main process is marked as completed while there are a lot of records still waiting to get inserted into the database yet.

so my question is that is there any trigger in mssql that can be used for when the queued transactions on a table are finished?

arash moeen
  • 4,533
  • 9
  • 40
  • 85
  • Few questions. How that batches would look like? Simple INSERT statements? What you want to get from SQL Server when batch finish running? – gofr1 Jun 28 '16 at 18:00
  • it's a simple insert query which will happen after each other, basically there is a loop for document list, converting each document into an insert query and then that query is passed to jdbc adapter which will queue up due to the size of the documents (1kk+) – arash moeen Jun 29 '16 at 15:23
  • could be a simple trigger that updates another table after all those queued insertions are done. – arash moeen Jun 29 '16 at 15:24
  • I have posted answer with one suggestion. I am on my phone right now so I could add some job scripts later. – gofr1 Jun 29 '16 at 15:36
  • "the main process is marked as completed while there are a lot of records still waiting to get inserted into the database" what is your signal of "completed"? – Yosi Dahari Jun 29 '16 at 15:47
  • In our particular situation job after creation add row in the results table with 'complited' = 0. After successful inserting it change status field to 'complited' = 1. In you situation you will send insert batch to server, than application must check results table for status. – gofr1 Jun 29 '16 at 17:06

1 Answers1

0

I suggest you instead of INSERT batches, use batches that will create a jobs with two steps. First step is insertion of data and second insert data about batch complete in some results table. After that you can check table with results.

gofr1
  • 15,741
  • 11
  • 42
  • 52
  • I didn't quiet catch the part 'create a jobs ...'. these batches are running in parallel and each insertion happens independently so you can imagine of 1kk x INSERT INTO xxx(a,b,c...) passed to the adapter – arash moeen Jun 29 '16 at 15:46
  • That's the point, you create 1000 jobs each inserting 1000 rows, then you wait and check result table if batches are complete. If some jobs failed you just need to restart them later without resending data. If you send batches and they fail what would you do? How your application will check if batch is done? The jobs and result table will help. Or I am not getting the point of what you need? – gofr1 Jun 29 '16 at 15:55
  • I suggest this based on some experience. We have one API, that gets reports from the user. This reports are SQLite databases. This API fetches that DBs, and create jobs to load data in some tables. That DBs are variable sized. And amount of inserted rows are from 0 to 10k and sometimes even more. Also we got a special job that restart falling job every hour. – gofr1 Jun 29 '16 at 16:02