Informix SQL syntax for NOLOCK 1

[StephHansen]

i have an access database that is sending a "pass-through" query to an informix data source...
I want to use DTS to pull three tables from this data source into MS SQL. One table is 500,000 rows. I want to make sure that this causes no locking inssues in Informix when I am pulling the data so I would like to specify a NOLOCK hint on the pass-through query. I need to know the Informix SQL syntax for a NOLOCK table hint.



Stephanie

 

[vpshriyan]

Hi,

If your intention is to pull the information to from those three tables and not to update them, I feel you need not worry about the locking factors. Alternatively you may specify your activity explicitly by:

set lock mode to wait; select * from tab1,tab2,tab3 where .... for read only;

If you want an unhindered access to the data, while reading information you may specify locking granularity by:

set isolation to dirty read; select * from tab1,tab2,tab3 where .... for read only;

The isolation stated above ignores all the transaction related locking errors and hence,
you need to note that such a query may lead to a phantom data selection; should your query target any rows that were in a ongoing transaction which may have resulted in rollback.

If you want to select only the committed information and want to instruct the database to wait till the transaction to get over, if any:

set lock mode to wait;
set isolation to committed read;
select * from tab1,tab2,tab3 where ....

Regards,
Shriyan