BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Launch Works 
Launch Works (Opens a new window)  

General Notice: No events within the next 45 days.

Ensuring that "NO LOCK" is included in the SQL gen

Goto page 1, 2  Next
 
Search this topic... | Search DI: Installation, Administration, and Connectivities... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Installation, Administration, and Connectivities  Previous TopicPrint TopicNext Topic
Author Message
icytrue
Principal Member
Principal Member



Joined: 10 Apr 2006

Posts: 139



PostPosted: Thu Nov 09, 2006 11:52 am 
Post subject: Ensuring that "NO LOCK" is included in the SQL gen

This may already be happening automatically, but what I am trying to understand is whether BO DI 11.5.1.5 can be configured (or is set by default) to use "No Lock" when it is reading from a datastore.

I am working with MS SQL Server 2000.

Can anyone enlighten me on this? Thanks!
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3456
Location: Massachusetts, USA


flag
PostPosted: Fri Nov 10, 2006 8:13 am 
Post subject: Re: Ensuring that "NO LOCK" is included in the SQL

No, DI does not issue *any* hints, be them locking hints or index hints.

The only way to do this, if a WITH (NOLOCK) is mandatory in your environment, is to have your dataflows' sources be a SQL transform instead of a source table. Then you can hand-code your SELECT query from your source table and include the NOLOCK.

(I'd suggest you enter an enhancement with customer support for this. If you do, let us know the ER # and I'd be happy to add my vote to it too.)
Back to top
icytrue
Principal Member
Principal Member



Joined: 10 Apr 2006

Posts: 139



PostPosted: Fri Nov 10, 2006 8:44 am 
Post subject: Re: Ensuring that "NO LOCK" is included in the SQL

Thanks for that. I've done a bit more reading.....

Firstly, do you know if using the SQL Transform limit in any way what DI can do in terms of its optimization of the SQL it sends to the database?

Secondly, are you aware of any difference in the behaviour at the database between

1. using a pre-sql command to set the isolation level to read uncommitted for a dataflow

and

2. using NOLOCK in an SQL Transform

?
If there is no difference (as the MS SQL 2000 literature suggests) then the isolation level method would be easier to introduce to an existing DI job as it is required once per dataflow, rather than once per datasource.


All comments welcome!
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3456
Location: Massachusetts, USA


flag
PostPosted: Fri Nov 10, 2006 9:43 am 
Post subject: Re: Ensuring that "NO LOCK" is included in the SQL

The pre-SQL command applies to the target table, not the source table. (I made the assumption that you're more worried about locking/blocking on your source tables vs. your target tables. Perhaps that's not the issue.)

So in DI terms, these aren't equivalent. #1 relates to the target table. #2 relates to the source table.
Back to top
eepjr24
Forum Enthusiast
Forum Enthusiast



Joined: 16 Sep 2005
ASUG Icon
Posts: 1700
Location: Orlando, FL


flag
PostPosted: Fri Nov 10, 2006 5:11 pm 
Post subject: Re: Ensuring that "NO LOCK" is included in the SQL

icytrue wrote:
Firstly, do you know if using the SQL Transform limit in any way what DI can do in terms of its optimization of the SQL it sends to the database?


SQL transform prevents DI from doing ANY optimization. It will only send exactly what you type into the box. Period. Not only that, but it cannot push down anything that you join to it, will not generate metadata for it, etc. Generally a bad idea unless there is no other method available to you.

If this is the only way you can get your query to run (due to the locking issues you mentioned), then be very careful to have your DBA review the SQL with performance tuning in mind and add appropriate indexes to optimize it.

I agree with DNewton, if you request an enhancement, let me know and I will voice my support for you as well.

- Ernie

_________________
Please Search before you post.
___________________________
Ernie Phelps
Engagement Manager
Decision First Technologies
http://decisionfirst.com
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Fri Nov 10, 2006 8:05 pm 
Post subject: Re: Ensuring that "NO LOCK" is included in the SQL

Would a "set ........" command help? If yes, this is enhancement request: ADAPT00677995
_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3456
Location: Massachusetts, USA


flag
PostPosted: Sat Nov 11, 2006 8:11 am 
Post subject: Re: Ensuring that "NO LOCK" is included in the SQL

If you're saying that you can do a SET command on the source table at the start of the datafow, then yes that may help:

http://msdn2.microsoft.com/en-us/library/ms173763.aspx
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Sat Nov 11, 2006 8:17 am 
Post subject: Re: Ensuring that "NO LOCK" is included in the SQL

Will it really? What about the scenario of "update table (NOLOCK)..."? This is important, too, isn't it?
_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3456
Location: Massachusetts, USA


flag
PostPosted: Sat Nov 11, 2006 8:24 am 
Post subject: Re: Ensuring that "NO LOCK" is included in the SQL

I am confused about whether the issue is locking on the source or the target. crazy.gif Or both.

In our environment, turning off locking on the target table is less important. (And dangerous -- if you're seeing locking issues on the target table because multiple things are reading/writing to the target, then, turning off locking is just hiding the problem.)

Note that I'm not referring to the blocking that SQL Server has when using table compares. I suppose adding a (NOLOCK) hint to the SELECT statement that the Table Compare does for each row would be valuable. And if controlled by a checkbox, something that the designer can choose to take a risk with.
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Sat Nov 11, 2006 8:55 am 
Post subject: Re: Ensuring that "NO LOCK" is included in the SQL

How would you proceed if you want SQL Server to force into a row level lock. Normally, after updating a good amount of the table's rows SQL Server automatically forces the table lock, does it?

What I am thinking about is a feature where you can execute a custom statement right after the creation of the database session. So something similar like the pre_load_command but usable for all connections, lookups, table readers, etc. You will be able to specify such command globally per datastore or per dataflow.

Have you been at the Insight2006 this week? Have we met? icon_mrgreen.gif

_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3456
Location: Massachusetts, USA


flag
PostPosted: Sat Nov 11, 2006 9:00 am 
Post subject: Re: Ensuring that "NO LOCK" is included in the SQL

I believe there's a SQL server locking hint to force row-level locking (and prevent escalating the lock to a table lock). Something like:

select xxx
from YYY with (ROWLOCK)
where ...


Sorry, no, I'm not at the conference. nonod.gif
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Sat Nov 11, 2006 4:29 pm 
Post subject: Re: Ensuring that "NO LOCK" is included in the SQL

my point exactly. There is no "set transaction rowlock", it has to be part of the SQL statement "update ..... (rowlock)...". So my feature would not be sufficent...
_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
Victory
Forum Member
Forum Member



Joined: 04 Feb 2008

Posts: 6



PostPosted: Mon Feb 04, 2008 5:59 pm 
Post subject: Re: Ensuring that "NO LOCK" is included in the SQL

There is a support item from 6.5 about flipping a configuration switch to always add the no-lock. Does this work with 11.7? Is it the solution to this problem?

KB# 778090
SQLServerReadUncommitted=1 - Read uncommitted data in SQL Server datastore tables.
Back to top
RJones
Forum Member
Forum Member



Joined: 29 Aug 2007

Posts: 19


flag
PostPosted: Thu Aug 14, 2008 9:31 am 
Post subject: Re: Ensuring that "NO LOCK" is included in the SQL

Resurrecting an old thread....
Was there ever any resolution to this? We're using 11.7 and we appear to be running into this issue. When BODI reads from a SQL Server database it's locking the tables so operational DML is failing. I can't see any setting in the data store that would allow us to change this behavior. Has anyone been able to work around this? (Besides the SQL Transform solution)

Thanks!
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3456
Location: Massachusetts, USA


flag
PostPosted: Thu Aug 14, 2008 9:39 am 
Post subject: Re: Ensuring that "NO LOCK" is included in the SQL

Yes - that hint above (about SQLReadUncommitted) is also supported in 11.5/11.7, per tech support. Give it a try...
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Installation, Administration, and Connectivities  Previous TopicPrint TopicNext Topic
Page 1 of 2 All times are GMT - 5 Hours
Goto page 1, 2  Next
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0488 seconds using 17 queries. (SQL 0.0032 Parse 0.0350 Other 0.0106)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo