z/os cics db2 cobol program to process database entries concurrently

Question

I have a DB2 table containing large amount of records to be send out to external system via MQs. There is a column in the table containing whether the record status (sent or pending to be sent).

I write a scheduler program to continually check if there are records in the table that are "pending to sent". If yes, the program will send the pending records out and update the status accordingly

That schedule will be started in multiple transactions. Therefore I am expecting multiple instances of the same program will be running concurrently

My questions is how to prevent the same records being pick up and sent by multiple schedulers at the same time?

I was told to use cursor with row level locks? but i am not sure how this works

remarks: I am working on CICS COBOL in z/os environment

Instead of wasting resources firing up something incessantly to look at the DB, why don't you just do the MQ when you do the "pending"? How are you thinking of doing the "scheduler" program? — Bill Woodger, Apr 14 '15 at 18:41
There is a long story behind @BillWoodger . The messages being sent out will be acknowledged from the counter systems. There is a threshold of sending out messages. Say if there are 500 messages being sent out but there is no acknowledge, we will need to stop sending out further messages. So there is some logic to determine and send messages. But multiple instances make the situation complicated — william, Apr 15 '15 at 00:14
You need to update your question with all the relevant information (use the edit link under the question). We can't do the whole design, as we don't know everything. Writing your own "scheduler" is very complex, and difficult to do without impacting other processing. Unlimited multiple tasks fired off automatically is a bad idea. Checker and doer are even accessing the same data. If you have records on a queue, you can always check and re-queue them if necessary. — Bill Woodger, Apr 15 '15 at 06:59

score 1 · Answer 1 · answered Apr 14 '15 at 22:53

I think you have a design problem. We accomplish something similar what you are trying to do by having a trigger on the DB2 table which sends an MQ message to a queue which is defined to trigger a CICS transaction.

In your case, you can probably dispense with CICS altogether and just do as @BillWoodger suggests and send the message when you set the pending flag.

score 0 · Answer 2 · answered Apr 14 '15 at 20:08

One way to do this is as follows 1) Determine the Clustering index for the large DB2 table 2) Then have different instances of the program run only looking at different portions of this clustering index. E.G if the clustering index was on a numeric ID field that is unique, like Account ID and the ID size is Integer 9 than have instance one look at account ID ranges from 0 - 099999999 and instance 2 look at account ID ranges from 100000000 to 1999999999 and .....

This way you can write your cusror with hold, perform updates and commits as needed.

score 0 · Answer 3 · answered Apr 15 '15 at 12:42

CICS will coordinate SQL transactions with DB2 for you. Each one of the CICS transactions you run will be able to select and lock for update rows and DB2 can coordinate between all of them and prevent the selection of multiple records if you do two things.

When you read the rows that qualify, use a SELECT FOR UPDATE type operation, this will lock every row you retrieve and prevent other concurrent transactions from accessing the same one (also requires you BIND with row level locks unless you want full pages locked, see your DBA about the options based on row size).

Before you release the records or end the CICS transaction, you must do something to flag said records as "sent" so that other, waiting, concurrent transactions do not grab them and send them again. This could be as simple as adding a sent Y/N column to the table and adding "AND sent <> 'Y'" to your select where clause. After you have sent the records, do an UPDATE on those records and set sent = 'Y'. Depending on your row data, you could maybe use something else, like time sent or whatever, it just needs to be something that would exclude said row from reselection.

z/os cics db2 cobol program to process database entries concurrently

3 Answers3