DSXchange

Hi All,
I am using datastage 8.0.1 ( IBM Informaation Server ) .I have job design as below
SEQ ----->>>> TRANSFORMER ---->>>> ORACLE ENTERPRISE STAGE

We are getting the below Error Message on DS Log and Jobs are getting aborted.
i know that its coming because indexes are enabled on the table .Also partitioning is also there on the table .I searched the forum.There are some posts but could not able to find the resolution.

ERROR: Indexes on table 'emp'preclude direct parallel loading unless an index option is included. Add an index option or drop indexes and rerun step.


Could Any one help is it DataStage Error or Oracle Level Index probs Error ?

It's an Oracle restriction - nothing to do with DataStage. You have to specify (in DataStage) how to manage the indexes - you have two choices, essentially deferred update or drop and re-build.

This is well documented in the stage chapter in the Parallel Job Developer's Guide

We have created partitioned index in oracle table and we are using oracle enterprise stage optioin with rebuild option and getting the following errors

"The index-rebuild options may not be used on a partitioned index"

When we try with "Maintenance" option on enterprise stage we are able to run the job successfully.

My question is : Which option is better to use and why?

Thanks

"Maintenance". Because it works.

Thank you for reply.

We have used "Maintenance" option in oracle entreprise stage after that we are getting following error in sqlldr log files and DataStage job also getting aborted. Please advice us how to resolve the issue.

The following index(es) on table VERIFY_IDS.SECABS_RECORDS were processed:
index VERIFY_IDS.INDX1_SECABS_RECORDS partition P200709 was made unusable due to:
SKIP_INDEX_MAINTENANCE option requested
index VERIFY_IDS.INDX2_SECABS_RECORDS partition P200709 was made unusable due to:
SKIP_INDEX_MAINTENANCE option requested

If you search DSXchange you will find that Maintenance mode does render indexes unusable - you need to disable SKIP_INDEX_MAINTENANCE or a Close command to remedy that.

I am getitng the following error after choosing the index mode="Maintenance"

Ora_Ent_FILE_STATUS: Indexes on table 'VERIFY_CON.FILE_STATUS' preclude direct parallel loading unless an index option is included. Add an index option or drop indexes and rerun step.

I have also set ORACLE_SQL_LOAD_OPTIONS "DIRECT=TURE" to see if it resolves the problem. But this did not help.

Can someone help me with this?

As it tells you: "Add an index option or drop indexes". Have you left the Index option as "Maintenance" or changed it?

Still the option is "Maintenance"