reports interfere other jobs in the server

general discussion
1

Hello,
Some times my report locks the tables in the database and kill other ETL jobs related to the table. This happens when both report and loads happens at the same time. Any solutions to this problem …
Thanks

BOWorker (BOB member since 2007-11-17)

2

You can try enabling “dirty” or “uncommitted” reads to prevent the BzO queries from locking the records. The syntax will differ depending on your db vendor. Here is an example for DB2. I’m sure a search will reveal the correct statement for other types of databases.

BoB LoblaW :us: (BOB member since 2007-10-23)

3

Thanks for the response,
I am using DB2 Version 8 as DB …How will I enable the code you sent me with the link??..Where I should I check in order to enable this code???. Is this code useful for the DB that I am using…???.
Please help me…

BOWorker (BOB member since 2007-11-17)

4

Are these adhoc or scheduled reports?

Firstly users should be locked out during ETL load / replication.

In the second instance why not get the database to create an ETL Completion file that your scheduled reports wait for based on a BOXI event?

Mak 1 :uk: (BOB member since 2005-01-06)

5

Its could be scheduled or Adhoc reports.When I noticed this issue last time because of a adhoc report. Is this problem act differently for as scheduled or adhoc report.

Setting up the BOXI to wait for event to be done in the database is something done by DBA right?? This could increase the report run time also…I just wondering how we can set up this to not kill any jobs thats related to that table.

Thanks

BOWorker (BOB member since 2007-11-17)

6

i.e. Users should not be accessing the DB at all, it could lead to inaccurate results. The DBA should lock select access for users during load.

The DBA needs to set up the database to create the file upon completeion of ETL.
BOXI will need to be configured to wait for the event before kicking off the scheduled reports…

Mak 1 :uk: (BOB member since 2005-01-06)

7

This is definitely the best practice.

For scheduled reports, it is possible to tell it to wait for an event to occur before running. In this case, you would need to create a file event in the CMC that looks for the file created by your DBA (e.g. \server\folder\etl finished.txt) and associate this event with the scheduled job. This will prevent the scheduled jobs from running during the ETL load.

For ad hoc reports, user education is probably the easiest route to take. If there is a known schedule for ETL, make this information publicly available to your users. You can get really fancy and create a program object to deny rights to the universe(s) during ETL then restore them when finished, but user education is so much easier.

----Edit----
I forgot to answer your other question about dirty reads. Open the universe in Designer and go to File → Parameters → Parameter. Add an entry titled END_SQL if it doesn’t exist and enter the command for your DB there.

BoB LoblaW :us: (BOB member since 2007-10-23)

8

We script to lock 'em out…no access… :twisted: .

Mak 1 :uk: (BOB member since 2005-01-06)

9

Thanks for all reply

End SOL command is already exist in the parameter list.So I am guessing just add the following syntax in the value field

END_SOL=FOR SELECT ONLY WITH UR

Is this syntax work for all version DB2???..I am using DB2 V8…

Also could anyone tell me how to write the script or program object to locks users to access the DB when running ETL jobs…

BOWorker (BOB member since 2007-11-17)

10

Hello,
Could any one explain to me in detail how to stop dirty reads in the db by setting up parameter values in the universe.Using DB2 version 8
Thanks

BOWorker (BOB member since 2007-11-17)