Oracle Enterprise Stage

[just4geeks]

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

[just4geeks]

Is there any way I can execute commit only after complete load,or any job restart ability

Thanks In Advance.

[ray.wurlod]

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).

[just4geeks]

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.

[singhald]

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.

[just4geeks]

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

[ray.wurlod]

When Write Method is Load you are not doing transctional inserts. You are using sqlldr. Therefore transaction size is irrelevant.

[just4geeks]

When Write Method is Load you are not doing transctional inserts. You are using sqlldr. Therefore transaction size is irrelevant.

how can one get commit only at the end of the load when write method is Load

[ray.wurlod]

The same way you can using sqlldr in isolation. It is simply not possible.
This is not a DataStage restriction.

[just4geeks]

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 .

[ray.wurlod]

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.