loading Oracle table that has indexes and partitions

sgcpackfan · Post by **sgcpackfan** » Tue Oct 30, 2007 4:42 pm

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

chulett · Post by **chulett** » Tue Oct 30, 2007 4:47 pm

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?

sgcpackfan · Post by **sgcpackfan** » Wed Oct 31, 2007 8:19 am

How do I move this to another forum? Just re-post?

sgcpackfan · Post by **sgcpackfan** » Wed Oct 31, 2007 9:40 am

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