Oracle Enterprise Stage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
Oracle Enterprise Stage
Hi,
I have a job which loads 4 million rows into Oracle table using Oracle Enterprise stage wrtie mode is upsert-insert only. This job aborts after writing suppose 50000 rows to the table.How do I ensure that next time when I run the job it doesn't write previously written 50000 rows .
Thanks InAdvance
I have a job which loads 4 million rows into Oracle table using Oracle Enterprise stage wrtie mode is upsert-insert only. This job aborts after writing suppose 50000 rows to the table.How do I ensure that next time when I run the job it doesn't write previously written 50000 rows .
Thanks InAdvance
Attitude is everything....
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
Re: Oracle Enterprise Stage
Is there any way I can execute commit only after complete load,or any job restart ability
Thanks In Advance.
Thanks In Advance.
Attitude is everything....
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Commit after complete load is achieved by setting rows/transaction to 0.
Restart has to be designed - you have to keep records about how many rows were successfully sent to Oracle, or select these from the Oracle table before (as part of) the restart run. Facilitate this by keeping track of which job/date was used to load each record into the table (as a column in the record).
Restart has to be designed - you have to keep records about how many rows were successfully sent to Oracle, or select these from the Oracle table before (as part of) the restart run. Facilitate this by keeping track of which job/date was used to load each record into the table (as a column in the record).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
ray.wurlod wrote:Commit after complete load is achieved by setting rows/transaction to 0.
Restart has to be designed - you have to keep records about how many rows were successfully sent to Oracle, or select these from the Oracle table before (as part of) the restart run. Facilitate this by keeping track of which job/date was used to load each record into the table (as a column in the record).
Hi,
Can you please guide me
1)How to set rows/transaction to 0 in Oracle Enterprise Stage.
Thanks In Advance.
Attitude is everything....
hello,
you can use the Parallel Operator sepecefic enviornment variable
APT_ORAUPSERT_COMMIT_ROW_INTERVAL to 0,
Use this variable in job parameter and change the default value to zero.
and check your job.
you can use the Parallel Operator sepecefic enviornment variable
APT_ORAUPSERT_COMMIT_ROW_INTERVAL to 0,
Use this variable in job parameter and change the default value to zero.
and check your job.
Regards,
Deepak Singhal
Everything is okay in the end. If it's not okay, then it's not the end.
Deepak Singhal
Everything is okay in the end. If it's not okay, then it's not the end.
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
singhald wrote:hello,
you can use the Parallel Operator sepecefic enviornment variable
APT_ORAUPSERT_COMMIT_ROW_INTERVAL to 0,
Use this variable in job parameter and change the default value to zero.
and check your job.
Thanks for the help,
I wanted to know if the write method is load and write mode is append ,
In that case which is the parallel specific environment variable which governs the Transaction size.
Thanks In Advance
Attitude is everything....
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
ray.wurlod wrote:The same way you can using sqlldr in isolation. It is simply not possible.
This is not a DataStage restriction.
Thanks for reply.
I have one more query: I just need to insert records into the table, The upsert method gives us an option of update only but is there any way we can use insert only, I don't want to update any record .
Attitude is everything....
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
In your ETL stream you have identified the rows that need to be inserted. Use Upsert with "Insert, then Update".
To make doubly sure, add a "false" constraint to the UPDATE statement, for example WHERE 1 = 0.
To make doubly sure, add a "false" constraint to the UPDATE statement, for example WHERE 1 = 0.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.