Hi Team,

I have a scenario like,

High Level flow

Some Tool runs and calls -> KSH Script (shell script) -> calling java by passing args as tablename dynamically -> we should pick max id from common table -> bulk update should be done sequentially -> again update back that max seqid to COMMON TABLE

user 1 -> runs1 tool -> sends tablename as tab1 -> picks max id from table say COMMONTABLE (say 1000)-> bulk update starts from (1001) maxid ...and generate sequence for all records say 2000 -> again update 2000 in COMMON TABLE

user 2 -> runs1 tool -> sends tablename as tab2 -> picks max id from table say COMMONTABLE (2001) (because above recent id is 2000)-> bulk update starts from (2001) maxid ...and generate sequence for all records say 3000 -> again update 3000 back to COMMON TABLE

...
All transactions should be done.

Problem: As it is a main method every time treating as a NEW REQUEST. If one time enters MAIN method then we use threads to control, but problem is every request enters MAIN method.

==========

1 tool runs continuously and calls ksh script which internally calls java application by passing DYNAMIC TABLENAME
Note: The tool which is sending tablename say TAB1, in that table SEQID col are NULL


- It is a standalone application (means main method application)

- If 1 user is running tool from his system as mentioned above it calls 1 java application by passing SOME TABLENAME say "tab1"
- Similarly, if other user runs same tool from different system same things happens, say he passes OTHER TABLE OR SAME TABLE NAME
let us say tablename as "tab2"


Logic

- For every user request say tablename tab1 or tab2 or same table tab2 from other user,

- First should hit to common table let us say tablename as "COMMONTABLE"
- From their it should pick up the MAX SEQUENCE ID (max row no)

- Next, it should use that MAX ROW for the table which passes say TAB1, that is let's say max row =100, then TAB1 sequence should starts from 101, 102........say 200 total records and once it done it again updates COMMON TABLE SEQUENCE TO 200 (after bulk update)


- Say, other table comes TAB2 as it also enters main() method first looks MAX ID from COMMON TABLE WHICH IS 200 and then it should starts from 201.....say 300 (bulk update) and again should update 300 as max seq id to COMMON TABLE.


PROBLEM:

here every request it is treating as a new request. Presently say 1 million are records are coming in between it is trying to update second table (taking second request) which is not supposed to pick the request.

NOTE: - Transaction should be sequential.
- Timer kind of thing should not be used. Because we will be getting Millions of records or 1 or 2 records.
- Immediately transaction should be done.

My part is java part and using db2 database.

Really appreciate if anybody provide better solution.


Thanks & Regards,
P.Sandeep