Regarding oracle error with indexes enabled

verify · Post by **verify** » Thu Apr 03, 2008 11:09 pm

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 ?

ray.wurlod · Post by **ray.wurlod** » Thu Apr 03, 2008 11:13 pm

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

verify · Post by **verify** » Fri Apr 04, 2008 5:13 pm

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

ray.wurlod · Post by **ray.wurlod** » Fri Apr 04, 2008 5:24 pm

"Maintenance". Because it works.

verify · Post by **verify** » Sat Apr 05, 2008 5:47 am

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

ray.wurlod · Post by **ray.wurlod** » Sat Apr 05, 2008 5:22 pm

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.

verify · Post by **verify** » Tue Apr 08, 2008 6:14 pm

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?

ray.wurlod · Post by **ray.wurlod** » Tue Apr 08, 2008 7:33 pm

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

verify · Post by **verify** » Tue Apr 08, 2008 9:58 pm

Still the option is "Maintenance"