Re: Setting Isolation Level

On 2/1/06, Ekstrand, Pam <Pam.Ekstrand@oneneck.com> wrote: > I have an IDS 9.40FC6 database which receives queries from Sql Server 2000's > OpenQuery functionality. OpenQuery will only pass one select statement, so > the "set isolation to dirty read" cannot be passed followed by another > select statement.> > Does anyone have any ideas of how I can set the isolation level to dirty > read in any other way? All sessions that connect as a specific user id to a > given database need to have the isolation level set to dirty read. > > Any ideas would be greatly appreciated. Several options - some of them currently work. 1. Get a feature request to the list that's being generated for either the XPS sysdbopen() function or a declarative profile: CREATE PROFILE <somename> AS <list-of-profile-options>; SET PROFILE <somename> FOR <list-of-users>; Where <list-of-profile-options> would include SET ISOLATION and SET LOCK MODE and any other session characteristics that seem beneficial. [That's the bit that doesn't work yet.] 2. Create and execute a stored procedure which sets the isolation, runs the query and returns the data. This depends on you being able to run an EXECUTE PROCEDURE statement via your OpenQuery tool. 3. Create a stored procedure as in 2, but use SELECT * FROM TABLE(MULTISET(EXECUTE PROCEDURE ...)));. 4. If you have the EXEC datablade, you could have a generic procedure that accepts the SQL... And so on - no doubt there are other ways too. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

"Jonathan Leffler" <jleffler.iiug@gmail.com> wrote in message news:mailman.381.1138862999.740.informix-list@iiug.org... > > On 2/1/06, Ekstrand, Pam <Pam.Ekstrand@oneneck.com> wrote: > > > > I have an IDS 9.40FC6 database which receives queries from Sql Server > > 2000's OpenQuery functionality. OpenQuery will only pass one select > > statement, sothe "set isolation to dirty read" cannot be passed > > followed by another select statement. > > > > Does anyone have any ideas of how I can set the isolation level to > > dirty read in any other way? All sessions that connect as a specific > > user id to a given database need to have the isolation level set to > > dirty read. Any ideas would be greatly appreciated. > > Several options - some of them currently work. > > <SNIP> > > 2. Create and execute a stored procedure which sets the isolation, > runs the query and returns the data. This depends on you being able > to run an EXECUTE PROCEDURE statement via your OpenQuery tool. > > <SNIP> I have just tested this OK on an SQL Server instance with a link to an Informix database: SELECT * FROM OPENQUERY (linked-server-name, 'execute procedure ...') So, do as Jonathan says using WITH RESUME to return multiple rows from the stored procedure. This the easiest of the suggested options. -- Regards, Doug Lawry www.douglawry.webhop.org