How are SQL's in one transactions written in DS. I mean open a transaction , insert, update, delete, close transaction.thanks,Chandra

Interview Candidate
Dec 21st, 2006
3
2746

Showing Answers 1 - 3 of 3 Answers

nikhilanshuman
Profile Answers by nikhilanshuman

Mar 18th, 2010

Datastage does not necessarily write all records in one transaction.
For example : For OCI stage,following Environment variables are set in the datastage administrator :

APT_ORAUPSERT_COMMIT_ROW_INTERVAL = 5000
APT_ORAUPSERT_COMMIT_TIME_INTERVAL = 60

The above values specify that the commit will be done either after 5000 rows or after 60 seconds. Whichever comes first.

For example say, there are 15000 records. Since commit row interval is 5000, there will be three commits on each 5000 record's interval. Or say there will be 3 transactions.

If the job fails at 7000th record, 5000 records will be there in target table where as the remaining 3000 will not be there because next commit was to be done at 10000th record.