Hi
We use sybase sql server 11 and we have extract tables that have nightly jobs run every day to extract data. We have problems in running extract jobs if someone is running the query at the time that extract jobs are scheduled to run. The query has a lock on the table and so the table cannot be truncated, and so finally the extract jobs get delayed. I need information is there is some way to set a flag on the BO queries to n at the time the extracts are supposed to run and a flag to y after are extracts are done. Any information or suggestions will help.
We have had a very similar problem, and have just implemented a solution in a slightly devious way. This solution is still very new to us, so you have been warned :o)
We have set up a cron job that checks the status of the overnight batch processing every 5 minutes. Whilst it is in the batch window it checks the Business Objects repository (ds_pending_job), looking for jobs submitted within the next 15mins, it then adds 15mins to the execution time. Once the flag is removed, this stops.
This effectively shunts all jobs submitted in the batch window, to the end of the batch window, and thus prevents conflicts.
We actually use some SQL to test a value in a table, but it could equally (and more easily) test for a flag file.
This method is of course updates the repository and so probably invalidates any support agreement.
The important SQL fragment (for Ingres) is:
UPDATE ds_pending_job
SET begin_date =
interval(‘sec’,date(‘now’) - date(‘15/12/1970 00:00’)) + 900 WHERE job_status in (2,4,5,6)
AND begin_date <
interval(‘sec’,date(‘now’) - date(‘15/12/1970 00:00’)) + 900
We use sybase sql server 11 and we have extract tables that have nightly jobs run every day to extract data. We have problems in running extract jobs if someone is running the query at the time that extract jobs are scheduled to run. The query has a lock on the table and so the table cannot be truncated, and so finally the extract jobs get delayed. I need information is there is some way to set a flag on the BO queries to n at the time the extracts are supposed to run and a flag to y after are extracts are done.
Regards,
Rob Blackmoor
Project Manager
Tarmac Heavy Building Materials UK Ltd.