[SOLVED] Running Stored Procedure From WebFOCUS Locks Out Others From SQL Server DB
Whenever I run a SQL Server 2000 stored procedure within WebFOCUS nobody else can connect to the SQL Server database or run any other stored procedures until it completes. This includes people running Access Projects and Access databases with linked tables, as well as trying to run stored procedures within SQL Server using SQL Query Analyzer. I can run the same stored procedure from within SQL Server using SQL Query Analyzer and the database is available to anyone. This behavior occurs no matter what the stored procedure is.
I opened a case for this several weeks ago. The IBI folks suggested I throw it out to the Focal Point community to see if anyone has (or had) this problem.
Below are two methods of creating the SQL Server table using WebFOCUS I have tried. The first executes the stored procedure. The second passes the SQL code that replicates the stored procedure executed in method one. They both cause the database access lockout problem.
Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2 Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
November 12, 2008, 12:38 PM
Michael Meagher
I will try changing the isolation level to 'Read Uncommitted'. I will also try Francis' suggestions on speeding up the stored procedure.
The interesting thing about this problem is that users cannot access any tables (not just HoldNAT) in the database while WebFOCUS runs a stored procedure.
Thanks for the help. I will get back with the results soon.
November 13, 2008, 03:04 PM
Michael Meagher
Changing the transaction isolation level to 'Read Uncommitted' did not help. The entire database was not available to other users while the HoldNAT table was created.
I am working on speeding up the stored procedure using tips from the article that Francis suggested.
This is all very odd. I wonder if it is a security issue with the way WebFOCUS connects to SQL Server?
November 13, 2008, 05:03 PM
Francis Mariani
Take a look at this documentation:
"Adapter Administration for UNIX, Windows, OpenVMS, i5/OS, and z/OS" > "Using the Adapter for Microsoft SQL Server" > "Customizing the Microsoft SQL Server Environment"
There may be a setting you can add to your update program - there's a lot of stuff to read.
Good luck,
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server