Oninit cdi / Archive
Talk to an expert →

Using Informix syntax in SQL2000 DTS packages

Posted in 2008

Topics: Transactions, Locking & Isolation

ina.nortje@gmail.com — — source: Usenet: comp.databases.informix

Good day, We have a Business Intelligence system in SQL Server that uses data from our Informix-base OLTP system. I need to extract data from the Informix database into SQL Server using an Informix dirty read, because the OLTP system is being used constantly so we can't wait for all locks to be cleared. Currently we use SQL DTS Packages to get the data. We create an IBM INFORMIX 3.82 32 BIT data source and then a data transform task to take the data over to a SQL Server target. Now the problem is that the SQL Syntax that we enter to select the data from the informix source gets checked against Microsoft SQL Server syntax. So when we try to issue the "SET ISOLATION TO DIRTY READ" command, we get a syntax error. How can we do a "dirty select" from the Informix table? Any ideas will be welcome! Thanks Ina

↪ replying to ina.nortje@gmail.com
Ron MacNeil — — source: Usenet: comp.databases.informix

On Thu, Dec 18, 2008 at 1:08 AM, <ina.nortje@gmail.com> wrote: > Good day, > > We have a Business Intelligence system in SQL Server that uses data > from our Informix-base OLTP system. > > I need to extract data from the Informix database into SQL Server > using an Informix dirty read, because the OLTP system is being used > constantly so we can't wait for all locks to be cleared. > > Currently we use SQL DTS Packages to get the data. We create an IBM > INFORMIX 3.82 32 BIT data source and then a data transform task to > take the data over to a SQL Server target. Now the problem is that > the SQL Syntax that we enter to select the data from the informix > source gets checked against Microsoft SQL Server syntax. So when we > try to issue the "SET ISOLATION TO DIRTY READ" command, we get a > syntax error. > > How can we do a "dirty select" from the Informix table? > > Any ideas will be welcome! > > Thanks > > Ina > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > You can also create stored procedures for your selects, with 'set isolation dirty read;' as your first statement within the procedure.

Glossary