Regarding oracle error with indexes enabled
Moderators: chulett, rschirm, roy
Regarding oracle error with indexes enabled
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 ?
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 ?
RK Raju
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
This is well documented in the stage chapter in the Parallel Job Developer's Guide
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.
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
"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
RK Raju
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
RK Raju
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
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?
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?
RK Raju
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: