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.
Problem with Index while loading oracle table
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 151
- Joined: Fri Feb 13, 2009 4:19 pm
Problem with Index while loading oracle table
Thanks
Karthick
Karthick
-
- Premium Member
- Posts: 151
- Joined: Fri Feb 13, 2009 4:19 pm
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.
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.
Thanks
Karthick
Karthick
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.
No clue how you set things up, just wanted to make sure you understood the above point.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 151
- Joined: Fri Feb 13, 2009 4:19 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.
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.
Thanks
Karthick
Karthick
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 151
- Joined: Fri Feb 13, 2009 4:19 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.
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.
Thanks
Karthick
Karthick