How to handle transaction in BASIC ROUTINE

[Rubu]

Hi All

My requirement is to read a row from a table. Then I want to update it with a new value. But untill I update the row, it should not be updated by any other application....... I think if I can lock the row after reading , untill we update it ..this purpose is served....

OR I can do both the operations inside a transaction block as follows...

BEGING TRANSACTION
select * from table
then store the value in some file,
update the row
END TRANSACTION

Please suggest me how to achieve it in datastage. Can I write a Basic routine for that... Any sample routine available??

Regards
rubu

[davidnemirovsky]

You can obviously do this a few ways. I would personally use Semaphores for locking and unlocking processes you need shared.

This post will help you get started: <a href="viewtopic.php?t=84481&highlight=semaphores">Click here.</a>

[ray.wurlod]

If the routine is connecting to DataStage database (hashed files, UV tables) then you can use BEGIN TRANSACTION, COMMIT, ROLLBACK and END TRANSACTION as you seek to do.

If you are accessing other databases via the BASIC SQL Client Interface (BCI), then you use SQLSetConnectOptions() to specify manual control of transactions, then SQLTransaction() actually to control the transaction.

Otherwise, it can't be done from BASIC as true transactions in the target database, which is why people like David resort to other techniques. However, these provide no protection against other processes interacting with your target database.