BusinessObjects Board

Can BusinessObjects use Uncommitted Read (UR)

We are experiencing a problem where DB2 threads are not being canceled when quering large amounts of data. Eventually, these threads start bottlenecking our processors and affects system performance on that subsystem. One question in a meeting was, can an isolation level be set to Uncommented Read (UR). An example would be to append WITH UR to a SQL statement. Is this possible??

SELECT
  ADWJCIP.CUST_REGISTRY.ENT_ID,
  ADWJCIP.CUST_REGISTRY.CMP_ENT_ID,
  ADWJCIP.CUST_REGISTRY.LAST_NM,
  ADWJCIP.CUST_REGISTRY.FIRST_NM,
  ADWJCIP.CUST_REGISTRY.MIDDLE_NM,
  ADWJCIP.CUST_REGISTRY.PY_ADDR_TXT1,
  ADWJCIP.CUST_REGISTRY.PY_ADDR_TXT2,
  ADWJCIP.CUST_REGISTRY.PY_CITY,
  ADWJCIP.CUST_REGISTRY.PY_STATE
FROM
  ADWJCIP.CUST_REGISTRY
WHERE
  ( 
  ADWJCIP.CUST_REGISTRY.PY_STATE  =  'IA'
  )
[b]WITH UR[/b]

This has been a hair pulling experience. :wah:

Thanks in advance.


samuelb (BOB member since 2002-11-19)

Samuel,

There is a way to change connection type in BO. I don’t have all the details at had but you have to change the PRM or SBO file for your DB2 (in doubt change all of them, but make a backup 1st.

If you have a look at the DB2 connectivity guide there are 2 things which can be useful : ConnectInit= SET UR (or what ever the syntax is would do what you are looking for)

Or you may use the TxnIsolation level (If I remember this is the stuff I never managed to get to work with DB2.

Good luck


ClaireB :de: (BOB member since 2002-08-09)

Hi,

Open up the file called DB2UDBEN.PRM (or whatever relevant PRM file you have for the version of DB2 you are running).
The file is in the data access sub-directory in your Business Objects installation directory.

Add a line

 END_SQL=WITH UR 

in the ‘general’ section (usually the first one).

This should add “WITH UR” to any sql created by a query.


Michael Abelha :australia: (BOB member since 2002-08-16)

Hi,

Anyone know how to code this in E6? The prm file is now coded in XML. Cannot find the proper syntax anywhere. I bet I am not the first to discover this!


Bill Jones (BOB member since 2002-10-11)

Can’t uncommitted reads be set as default in DB2 at the system level? Seems like I heard the DBAs talking about this once. They called them “dirty reads” - if a row is locked, then skip it instead of waiting. They didn’t think it was a big deal either — the risk you take for using a transactional system instead of a data warehouse built for reporting.


scott copeland (BOB member since 2002-08-15)

The last time I went down this path, adding ‘for fetch only with ur’ to the sql was the surest way to assure an uncommitted read. Bobj 5x had a config keyword that could be added to the db2udben.prm file -END_SQL=FOR FETCH ONLY WITH UR
E6 does not seem to have this available, or it is not apparent what syntax to use in the new xml coded db2udben.prm file. Guess I will go ask Business Objects, no one at this forum has run into this issue.


Bill Jones (BOB member since 2002-10-11)

Please share any info you may find from Bus Objects with the forum. As other DB2 universes go to version 6 this question will come up again.


Michael Abelha :australia: (BOB member since 2002-08-16)

I have made some progress on this issue. I opened a case with Business Objects. The manipulation of the legacy\db2\db2udben.prm file, adding the statement ENDSQL=FOR FETCH ONLY WITH UR is still supported, but does not seem to work on existing universes (I did not try it on a new universe, however). There is another way, that is more global and does not require changing the local prm files on each client. In the universe, there is an END_SQL paramater. Open the universe in designer, File, Parameters, Parameters tab. Sadly, the END_SQL parameter is too short to hold FOR FETCH ONLY WITH UR. This has been identified by Bobj as a bug, may be fixed in next release (maybe August, maybe called 6.3 or 6.1.3).

However, the END_SQL parameter will hold FOR FETCH ONLY. This has helped with contention between two reports attempting to select (read) the same data (mainy BCA scheduled reports, for us).

The WITH UR is supposed to help with a read select contending with an update select. This scenario is rare for us as the DW tables are not updated during Bobj and Webi availability hours.

Looks like the problem will be fixed in the next release.


Bill Jones (BOB member since 2002-10-11)

Hi Bill,

Thanks for the update. It will definitely be of use to us. :slight_smile:


Michael Abelha :australia: (BOB member since 2002-08-16)

Excellent Bill - thanks for the follow-up. Duly noted and discussed with developers at our site.


scott copeland (BOB member since 2002-08-15)

In 6.1b I can enter the entire “for fetch only with ur” if I enter it in lowercase. Anyone know if this has to be entered in uppercase to be processed correctly in the database?


jcook (BOB member since 2003-08-21)

add it in to universe and then look in a query SQL to see if it is there. It should parse on the way out of the SQL window. On the mainframe, CAPS didn’t matter but I haven’t really tried that with UDB.
Strange that lower case fits - I assumed it was a character limit based on a table field, not a visible size limit in a display line.


scott copeland (BOB member since 2002-08-15)

In 6.1.3, the length of the display field END_SQL is still too short to hold FOR FETCH ONLY WITH UR. Gladly, the lower case string will fit, and I did not get an error from the parsed SQL DB2 UDB 7.2. Next week I will try it in production and see if my run times change. Thanks for the simple fix.


Bill Jones (BOB member since 2002-10-11)

Just a followup on this that I had been meaning to post. Seeing Andreas refer to this post, it seems like a good time to add my bit.

The trouble I found was that when “FOR FETCH ONLY” is added to the universe parameters, it’s also included within in any subquery you may have. Needless to say, this causes an sql error to have the clause in 2 places - at the end of the query and at the end of the subquery.


scott copeland (BOB member since 2002-08-15)

Hi Everybody,
I have one question on this “With UR” option. I understand this option added in a query will fetch only those uncomitted rows without affecting the application read/write.

So, can i assume that the application’s performance will not be affected due to this uncommitted read in the BO query?

Does this query do any kind of performance issues (kind of locking) for the application to use the tables?j

Thanks for your replies


saravanainfo :india: (BOB member since 2006-12-13)