Re: Setting Isolation Level

#1 (**permalink**) 04-20-2008, 10:26 AM

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/

#2 (**permalink**) 04-20-2008, 10:26 AM

"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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode