Page 1 of 1
loading Oracle table that has indexes and partitions
Posted: Tue Oct 30, 2007 4:42 pm
by sgcpackfan
I am not very knowledgeable about DataStage, yet I have been put on a project where we will be using it. I am having some issues with loading to an Oracle table that has 78 partitions and 4 indexes. When I set the stage to use the "Load" option, it seems to have problems properly dropping and recreating the indexes. I don't have much documentation to reference, so if you have encountered this situation before and know the correct options to set, please share! I have tried various config combinations and can't seem to get it to work. I don't have this issue if I set the load type to "Upsert" but I would assume this is very inefficient when compared to doing a SQL Loader operation. (Note there is no primary key on this table, so all rows would be an insert.)
Thanks for any help!
Shan
Posted: Tue Oct 30, 2007 4:47 pm
by chulett
Welcome! More specifics on the problems you are having dropping and recreating indexes would help - post the actual errors if you can. Does your connection account actually have the grants it would need to do this?
Posted: Wed Oct 31, 2007 8:19 am
by sgcpackfan
How do I move this to another forum? Just re-post?
Posted: Wed Oct 31, 2007 9:40 am
by sgcpackfan
Sorry for not giving more specifics, I should have done that initially.
Options for Load in DataStage are:
- Disable Constraints = True
- Index Mode = Maintenance
- others are not pertinent to this discussion
Here is selected info from the SQL Loader LOG file:
Code: Select all
Table PRICE_HIST, partition PH_T_PART002, loaded from every logical record.
Insert option in effect for this partition: APPEND
...
The following index(es) on table PRICE_HIST were processed:
index PRICE_HIST_I1 partition PH_I_PART002 was made unusable due to:
SKIP_INDEX_MAINTENANCE option requested
index PRICE_HIST_I2 partition PH_I_PART002 was made unusable due to:
SKIP_INDEX_MAINTENANCE option requested
index PRICE_HIST_I3 partition PH_I_PART002 was made unusable due to:
SKIP_INDEX_MAINTENANCE option requested
index PRICE_HIST_I4 partition PH_I_PART002 was made unusable due to:
SKIP_INDEX_MAINTENANCE option requested
The data was loaded successfully, and the indexes appear to be valid when I look at them after the load. But the errors above apparently cause the job to fail with these error messages:
Price_Hist,4: The system(sqlldr ***/*** CONTROL=ora.819236.446208.4.ctl LOG=ora.819236.446208.4.log BAD=ora.819236.446208.4.log.bad SILENT=header PARFILE=ora.819236.446208.4.par) failed; see the log file for the Oracle specific message.
Price_Hist,4: The runLocally() of the operator failed.
Price_Hist,4: Operator terminated abnormally: runLocally did not return APT_StatusOk