Also can you please help me to understand why Declare Global Temporary Table is getting dropped in my program. Is it a common problem with DGTT? What is the remedy for it?
No, i can't explain this. If you continue with the temp table, you may need to some diagnostic code to determine under which circumstances this happens. One place to start mikght be to make sure that every
sqlcode is checked for every sql issued.
"Done correctly" means that the work table be designed to accomodate what is needed without introducing unneeded complexity or performance degraders.
Yes, there will be only one table for this. From the little i understand of the process, the key to the table would be the user/customer id and a sequence number (one for each entry into the table). I suspect that you will only want a given id to have one process active at any one time.
Part of defining a new user/customer to the system would be to create the work entries in this table.