Crystal Reports Locking Tables
Users running many Crystal Reports against Informix 7.3 via ODBC found tables locked, blocking other applications. Replies said Crystal itself doesn't lock; the cause is the session's isolation level (Committed Read, or Repeatable Read for ANSI databases) plus long-lived sessions, and advised setting DIRTY READ or SET LOCK MODE TO WAIT n. It was also noted Crystal often fetches whole tables and filters client-side; alternatives (Visionary, InfoMaker) were suggested. Workarounds mentioned: a nightly report-only copy of the data, and cutting Crystal Page Server idle timeouts so threads expire in minutes. No definitive fix was reached.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We have a lot of crystal reports accessing our database vers. 7.3. The tables are getting locked causing problems with other applications. Is this an ODBC problem or is there something else that can be done to prevent this locking problem. HELP PLEASE. Thanks.
Cavin,
AFAIK, Crystal Report doesn't lock any row by itself. But if you are using
any stored procedure then it may lock row/table depending what it's doing.
It's not an ODBC problem.
Set isolation to dirty read or lock mode wait 'n' should be the solution.
Regards,
Nick.
Calvin Shoults <jcsatlcom.net@mindspring.com> wrote in message
news:87qktl$7e3$1@nntp1.atl.mindspring.net...
> We have a lot of crystal reports accessing our database vers. 7.3.
> The tables are getting locked causing problems with other applications.
> Is this an ODBC problem or is there something else that can be done
> to prevent this locking problem. HELP PLEASE. Thanks.
>
>
Just say no to lame report applications. No vale la pena. -- --------------------------------------------------------- Steven Hauser email: hause011@tc.umn.edu URL: http://www.tc.umn.edu/~hause011 ---------------------------------------------------------
Hi Calvin.
I don't know if this is helpful but we ran into a similar problem. If you
have a selection criteria in Crystal report, the filtering is done on the
client side. So lets say if your query reads
select * from customer where cust_id = {Some Formula that gets the value atrun time}
then Crystal report only sends:
select * from customer
and once the data gets to the client, it only displays the one that is
required. This is stupid but very true and will kill not only your database
but also your network if the table is big.
Do an onstat -g ses on the server and see what query is being sent by the
client
Thanks,
Imran.
=====================================================================
Imran Hussain
imranh@imranweb.com
For FREE Software visit
http://www.imranweb.com/freesoft
=====================================================================
Calvin Shoults wrote:
> We have a lot of crystal reports accessing our database vers. 7.3.
> The tables are getting locked causing problems with other applications.
> Is this an ODBC problem or is there something else that can be done
> to prevent this locking problem. HELP PLEASE. Thanks.
--
On Sat, 12 Feb 2000 02:38:46 GMT, Imran Hussain <imranh@imranweb.com> wrote: >I don't know if this is helpful but we ran into a similar problem. If you >have a selection criteria in Crystal report, the filtering is done on the >client side. > ... Aaargh... seems I can save a lot of time _not_ testing Crystal Reports for our customers. Are there any useful report generators out there or should I write all my reports from scratch? Of course our customers _do_ want all kind of tables, charts and diagrams, so the client has to be windows while the database itself resides on a Sun... Axel
Axel Sander (axsander@okay.net) wrote: : Are there any useful report generators out there or should I write all my : reports from scratch? Of course our customers _do_ want all kind of tables, : charts and diagrams, so the client has to be windows while the database : itself resides on a Sun... : Axel Might want to take a look at Visionary. Depends on the reports, of course, but it does charts and graphs, etc. It also does not filter the queries itself. -- Rob Wilson rwilson@ntsource.com
Hi! Have a look at Infomaker at Sybase site. It works well, can use stored procedure as a source (not just select as some others), has graph and charts and deployment to client PCs is free (no runtime or licence fees). HTH Michael Axel Sander wrote: > On Sat, 12 Feb 2000 02:38:46 GMT, Imran Hussain <imranh@imranweb.com> > wrote: > > >I don't know if this is helpful but we ran into a similar problem. If you > >have a selection criteria in Crystal report, the filtering is done on the > >client side. > > ... > > Aaargh... seems I can save a lot of time _not_ testing Crystal Reports for > our customers. > > Are there any useful report generators out there or should I write all my > reports from scratch? Of course our customers _do_ want all kind of tables, > charts and diagrams, so the client has to be windows while the database > itself resides on a Sun... > > Axel
On Sun, 13 Feb 2000 15:17:43 GMT, rwilson@ntsource.com (Rob Wilson) wrote: >Might want to take a look at Visionary. Do you have any URL handy? >Depends on the reports, of >course, but it does charts and graphs, etc. For the users who can read numbers a simple EXCEL ODBC connection would do it... but for those who needs pie charts I have to create a simple click fully automated management version ;-) >It also does not filter the queries itself. Sounds good. I hope there is a german version for those customers who has to interact with the generator. THX Axel
Axel Sander (axsander@okay.net) wrote: : On Sun, 13 Feb 2000 15:17:43 GMT, rwilson@ntsource.com (Rob Wilson) wrote: : >Might want to take a look at Visionary. : Do you have any URL handy? http://www.informix.com/visionary/ : >Depends on the reports, of : >course, but it does charts and graphs, etc. : For the users who can read numbers a simple EXCEL ODBC connection would do : it... but for those who needs pie charts I have to create a simple click : fully automated management version ;-) That is how Visionary positions itself, management dashboards. : >It also does not filter the queries itself. : Sounds good. : I hope there is a german version for those customers who has to interact : with the generator. Unfortunately, this will be something you will have to play with. The last I heard is that GLS will not be enabled. However, Visionary 2.0 is in Beta and if you could make a strong enough case I am sure they would at least look at putting it in that one (2.0 is due out end of Q1 I believe). : THX : Axel -- Rob Wilson rwilson@ntsource.com
Hello Calvin, Have you find any solution to the Locking problem? We too are using Crystal Reports and have run into similar locking problem. The lock goes into a "Wait on Condition" mode and doesnt get released. Any suggestions/help would be great Thanks Regards Vidya. Calvin Shoults wrote: > We have a lot of crystal reports accessing our database vers. 7.3. > The tables are getting locked causing problems with other applications. > Is this an ODBC problem or is there something else that can be done > to prevent this locking problem. HELP PLEASE. Thanks.
Hello Calvin, Have you find any solution to the Locking problem? We too are using Crystal Reports and have run into similar locking problem. The lock goes into a "Wait on Condition" mode and doesnt get released. Any suggestions/help would be great Thanks Regards Vidya. Calvin Shoults wrote: > We have a lot of crystal reports accessing our database vers. 7.3. > The tables are getting locked causing problems with other applications. > Is this an ODBC problem or is there something else that can be done > to prevent this locking problem. HELP PLEASE. Thanks.
Somewhere in your Crystal Reports setup or your specific report's options, you should be able to set the isolation level for your reports' SELECT statement. The default isolation level is Committed Read for non-ansi databases and Repeatable Read for ansi databases. You should set it to DIRTY READ (no locks placed, no locks honoured), but this could return incorrect results if the tables being reported upon are in the process of being updated. Alternatively, examine the SET LOCK MODE TO WAIT [n] command. Rudy Vidya Viswanathan wrote: > Hello Calvin, > > Have you find any solution to the Locking problem? > We too are using Crystal Reports and have run into similar locking problem. > The lock goes into a "Wait on Condition" mode and doesnt get released. Where is this "Wait on Condition" coming from? You're probably referring to a session status rather than its locking status. > > Any suggestions/help would be great > > Thanks > Regards > Vidya. > > Calvin Shoults wrote: > > > We have a lot of crystal reports accessing our database vers. 7.3. > > The tables are getting locked causing problems with other applications. > > Is this an ODBC problem or is there something else that can be done > > to prevent this locking problem. HELP PLEASE. Thanks.
Well,
When I connect to Informix Database from CR (Crystal Reports) using ODBC (CR
Informix and DataDirect, both), my default isolation level (Iso Lvl from
onstat -g ses session-id) is set to DR!!!
And I don't know how this is being set to DR (Dirty Read). Yes and when I
connect to the same database (non ANSI - unbuffreded logging) with SQL
Editor, the isolation level is set to CR (Committed Read)!!
I don't think that I have set anything to do so. For me, it's like default
Isolation level with CR!
Hope this confusion helps somehow :-)
Regards,
Nick.
Rudy Fernandes <rferdy@americasm01.nt.com> wrote in message
news:38BBE5A2.8F2957EF@americasm01.nt.com...
> Somewhere in your Crystal Reports setup or your specific report's options,
you
> should be able to set the isolation level for your reports' SELECT
statement.
> The default isolation level is Committed Read for non-ansi databases and
> Repeatable Read for ansi databases.
>
> You should set it to DIRTY READ (no locks placed, no locks honoured), but
this
> could return incorrect results if the tables being reported upon are in
the
> process of being updated.
>
> Alternatively, examine the SET LOCK MODE TO WAIT [n] command.
>
> Rudy
>
>
> Vidya Viswanathan wrote:
>
> > Hello Calvin,
> >
> > Have you find any solution to the Locking problem?
> > We too are using Crystal Reports and have run into similar locking
problem.
> > The lock goes into a "Wait on Condition" mode and doesnt get released.
>
> Where is this "Wait on Condition" coming from? You're probably referring
to a
> session status rather than its locking status.
>
> >
> > Any suggestions/help would be great
> >
> > Thanks
> > Regards
> > Vidya.
> >
> > Calvin Shoults wrote:
> >
> > > We have a lot of crystal reports accessing our database vers. 7.3.
> > > The tables are getting locked causing problems with other
applications.
> > > Is this an ODBC problem or is there something else that can be done
> > > to prevent this locking problem. HELP PLEASE. Thanks.
>
Hi Vidya, We are still looking into the problem. I have noticed that some of the session threads still remain until the ODBC connection is terminated long after the report has finished. This could be part of the problem. One thing we have done, is create a snapshot of the data and port it over to a report database on a different server each night. This takes the contention away from other applications that are making modifications to our database and server. Another thing I would like to find out, is if Informix Stored Procedures work in Crystal. If you can exec a stored procedure, you could return only the results set that the report needs and it be done at the database level not from Crystal. This could take care of some problems possibly. I have not had a chance to really study the problem. I hope to put more time into it soon. I will post as soon as I get more info. .......Calvin Vidya Viswanathan <vidya@miel.mot.com> wrote in message news:38BB5CB0.EAD3A411@miel.mot.com... > Hello Calvin, > > Have you find any solution to the Locking problem? > We too are using Crystal Reports and have run into similar locking problem. > The lock goes into a "Wait on Condition" mode and doesnt get released. > Any suggestions/help would be great > > Thanks > Regards > Vidya. > > Calvin Shoults wrote: > > > We have a lot of crystal reports accessing our database vers. 7.3. > > The tables are getting locked causing problems with other applications. > > Is this an ODBC problem or is there something else that can be done > > to prevent this locking problem. HELP PLEASE. Thanks. >
Hello, There has been some improvement on my side. what i did was to change some settings in the Crystal Page Server component, though am not sure i understand why that has made the difference. Well, In the Web Reports Configuration--Page Server tab--Advance Settings, i set the Idle Times for the Job and the Client to 0 minutes. It was by default 1hr and 2 hr respectively. And this has made some considerable differece where the duration of the lock is concerned. So now, after the report brower window is closed or if no request is sent, the user thread expires after 2-3 minutes. This too is actually quite a long time, especially since the isolation level is RR, but atleast some improvement from hours to minutes. But still i havnt found why it sets the level to RR and how i can change it to DR. Ok, will post on further development Regards Vidya. Calvin Shoults wrote: > Hi Vidya, > We are still looking into the problem. I have noticed that some of the > session threads still remain until the ODBC connection is terminated long > after the report has finished. This could be part of the problem. > One thing we have done, is create a snapshot of the data and port it > over to a report database on a different server each night. This takes the > contention away from other applications that are making modifications to our > database and server. > Another thing I would like to find out, is if Informix Stored Procedures > work in Crystal. If you can exec a stored procedure, you could return only > the results set that the report needs and it be done at the database level > not from Crystal. > This could take care of some problems possibly. I have not had a chance to > really study the problem. I hope to put more time into it soon. I will > post as soon as I get more info. > .......Calvin > Vidya Viswanathan <vidya@miel.mot.com> wrote in message > news:38BB5CB0.EAD3A411@miel.mot.com... > > Hello Calvin, > > > > Have you find any solution to the Locking problem? > > We too are using Crystal Reports and have run into similar locking > problem. > > The lock goes into a "Wait on Condition" mode and doesnt get released. > > Any suggestions/help would be great > > > > Thanks > > Regards > > Vidya. > > > > Calvin Shoults wrote: > > > > > We have a lot of crystal reports accessing our database vers. 7.3. > > > The tables are getting locked causing problems with other applications. > > > Is this an ODBC problem or is there something else that can be done > > > to prevent this locking problem. HELP PLEASE. Thanks. > >