DSXchange

Hi,

Basically we need to insert data into sql server table with lock enable so that no sidu operation is allowed for other users.

I have tried having isolation level to serializable, using hint as TABLOCKX but when the job is under execution in DataStage, I am able to select records from front end client.

I have tried this with ODBC and DRS stages but result not as expected. I am able to select records in another session which should not happen until DS job completes.

Is there any way to do this (ie. need to lock the table exclusively and do the insert in DataStage job)?

Thanks in advance.

Use the transaction isolation level property.

isolation level has been set to serializable
hint used is TABLOCKX

I'm assuming your "hint" is being used via user-defined (custom) SQL, yes? You may want to have your DBA help you, found this online as a possible issue for it not working:

The table hints are ignored if the table is not accessed by the query plan. This may be caused by the optimizer choosing not to access the table at all, or because an indexed view is accessed instead. In the latter case, accessing an indexed view can be prevented by using the OPTION (EXPAND VIEWS) query hint.