HOW TO COMMIT AFTER EVERY 1 MILLION RECORDS

seeta · Post by **seeta** » Wed Dec 21, 2005 10:50 am

HI
I want to COMMIT my records after 1 million row of data in datasatge ,Is there any way to COMMIT in datastage.
I know how to COMMIT in DataBase

Any help would appreciated
Thanks

ArndW · Post by **ArndW** » Wed Dec 21, 2005 10:53 am

Seeta,

the term used in most places in DataStage for this is "transaction size", this equates to the commit frequency. Each Database stage has a way for you to set this value. I think the documentation for whatever stage you are using might be a good place to begin if you can't find the place where you should set this.

vmcburney · Post by **vmcburney** » Wed Dec 21, 2005 5:00 pm

In Enterprise database stages you will often find transaction size as an optional option, click on the stage options and look for it in the bottom right properties window. By default it gets set to 2000 (I think!). This may destroy your database, you will get multiple instances trying to build and save 1,000,000 rows of data in a single transaction which can lead to rollback space problems. If you have four partitions maybe you need to commit 250,000 rows at a time so you get a maximum of 1,000,000 outstanding inserts.