ORAOCI stage : Transaction isolation options

[Rea_Antony]

Hi,

I'm using Datastage Server 7.5 on a windows platform.

I was trying to use the ORA OCI stage to extract data from an oracle table and i found that there is an option called 'Transaction Isolation'

There are 3 options under it:
Read Commited
Read Only
Serializable

Can some one plz explain the significance of 'Transaction Isolation'. Also, what do these options refer to.

Thanks in advance

[sachin1]

one of the datastage doc explains like

This Isolation level provides
the necessary consistency and concurrency control between
transactions in the job and other transactions for optimal
performance. Because Oracle does not prevent other transactions
from modifying the data read by a query, that data may be
changed by other transactions between two executions of the
query. Thus, a transaction that executes a given query twice may
experience both nonrepeatable reads and phantoms. Use one of
the following transaction isolation levels:
Read Committed. Takes exclusive locks on modified data and
sharable locks on all other data. Read committed is the default ISO
level for all transactions.
Serializable. Takes exclusive locks on modified data and sharable
locks on all other data. Serializable transactions see only those
changes that were committed at the time the transaction began.

[ray.wurlod]

Most (if not all) stage types have a Help button. You could have clicked this and gotten the answer sooner.

[ray.wurlod]

Most (if not all) stage types have a Help button. You could have clicked this and gotten the answer sooner.

[Rea_Antony]

Thank you for the response.

Yes, I do know that the help button provides us with the basic definition but it was a different case here.

My job keeps on aborting with the error that 'Snapshot is too old'. Upon googling, I got the response that it has something to do with the RETENTION_UNDO space etc... Unfortunately, I am not allowed to make changes in the source settings.

I was looking around for something that would help to make some difference when I came across this particular setting. Hence the query.

[chulett]

Ah... what you should have done is specifically ask about 'Snapshot too old' issueinstead of focusing on a perceived solution. Transaction Isolation won't help here.

You'll need to talk to your DBA about the issue - they may need to increase the retention times on the UNDO logs, or advise you on how to improve your queries so they don't take so long, or simply run the job at a time when the data is not changing quite as much or as rapidly.

More details on your actual job design and if this is a 'source' or 'target' error could lead to more specific advice. For a typical example, when your source throws this error try landing the data to disk rather then trying to process it all the way through in one shot. This allows you to 'release' the source database as quickly as possible, avoiding the snapshot issue and gives you a static starting point for a second job to perform the actual load.