Problem with Index while loading oracle table

dsuser_cai · Post by **dsuser_cai** » Mon Apr 20, 2009 9:42 am

Hi

Im trying to load an oracle table, im using Oracle stage as target and ODBC as a source. The target table has several indexes on it. When i load the table i get the following error message:

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

I searched the forum for this and i got only one result, but it was really help ful.

ALso I have one more question, how do i define environment variable:

APT_ORACLE_LOAD_OPTIONS. I checked in the parallel jobs advanced user guide and it says,

APT_ORACLE_LOAD_OPTIONS='OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)'.

i would like to try use these Env Variable but im not sure how to set them in Admin, i dont see any place where it says Direct or Parallel. Please can somebody help me how to define this environment variable. Thanks in advance.

ShaneMuir · Post by **ShaneMuir** » Mon Apr 20, 2009 9:58 am

Under the options in your Oracle load stage there is a setting for 'Index Mode'. Set it to rebuild.

chulett · Post by **chulett** » Mon Apr 20, 2009 10:12 am

Do you understand what "DIRECT=FALSE" means, does?

dsuser_cai · Post by **dsuser_cai** » Mon Apr 20, 2009 10:47 am

I believe when we set DIRECT = False, we use the conventional method which uses the Insert statement while loading. but where as when we set DIRECT = TRYE oracle uses direct path load method which directly writes to the data base files. please correct me if im worng.

Now i set the environement variable as 'OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)' in the job level and int he target side i made as upsert and the table is getting loaded.

please let me know if im doing in a wrong way.

chulett · Post by **chulett** » Mon Apr 20, 2009 2:25 pm

Yes, basically correct, it switches it back to a 'conventional' load using normal inserts and thus loses you any speed benefits you would get from a bulk load. It does, however, mean you don't have to futz with the indexes on the table.

No clue how you set things up, just wanted to make sure you understood the above point.

dsuser_cai · Post by **dsuser_cai** » Mon Apr 20, 2009 3:49 pm

Thanks for your reply,

Now i set the variable in the job level and its working. this is how I set the vairable.

Job Properites windo / Parameters tab -- add the environment variable APT_ORACLE_LOAD_OPTIONS and in the default value section, jus type the following within single quotes.

'OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)'

this will fix the issue.

chulett · Post by **chulett** » Mon Apr 20, 2009 4:01 pm

Note that it doesn't really "fix" anything, you've taken another path to your goal. As Shane noted, you "fix" the issue by leveraging the Index Mode option in the stage. For example you can emulate the typical process one would do outside of DataStage - drop the indexes, bulk load the table and then rebuild the indexes - using the option he mentioned.

dsuser_cai · Post by **dsuser_cai** » Mon Apr 20, 2009 4:06 pm

I checked int he oracle stage, but i do not have that option. Where do i see that?

Im using Oracle Enterprise stage and in options i see only 2 properites:

OutPut reject Records and table has Nvarchar/Nvarchar, i do not see the index option there. Please let me know if im looking at a wrong place.