We have Cognos Impromptu Web Report users running reports throughout the day. We also run one set of mload jobs daily in the afternoon. We have a "delete Table_XX all;" statement inside the job. It frequently happens that an Impromptu report is running a report against Table_XX while the bteq delete script is trying to execute the statement at the same time. So, the Impromptu Report stays in the queue waiting for the table while the delete scripts run extremely slowly.
Do you have any suggestions to prevent this dead-lock situation from happening?
Can we start the batch process by doing something at the database level to kill any running queries (for example anything using a particular database sign-on userid) and then lock the tables such that queries won't execute.