Using Load option in Oracle with indexes
Moderators: chulett, rschirm, roy
Sorry Benouche,
The environment variable value shown in the log of the job in DEV project is the same as noted in previous posts above.
The env variables shown in the log of the TEST project didn't show the mentioned APT variable at all before I made the above mentioned changes to the DSParams file.
This has solved the situation and the job runs fine in the TEST project now
The environment variable value shown in the log of the job in DEV project is the same as noted in previous posts above.
The env variables shown in the log of the TEST project didn't show the mentioned APT variable at all before I made the above mentioned changes to the DSParams file.
This has solved the situation and the job runs fine in the TEST project now
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Loading into Oracle Enterprise Stage
Hi All,
I'm able to load data into the Oracle Table and using the parameter "APT_ORACLE_LOAD_OPTIONS". But i'm using it blindly.Please can anybody tell me abt this?
I will be very thankful to them...
Purushotham
I'm able to load data into the Oracle Table and using the parameter "APT_ORACLE_LOAD_OPTIONS". But i'm using it blindly.Please can anybody tell me abt this?
I will be very thankful to them...
Purushotham
You have options to set DIRECT / PARALLEL command to set TRUE / FALSE using this variable.
By using this variable you (and without using the Index Mode options) you can load the Tables which has index in it.
If you want to load through index, you need to specify DIRECT=FALSE.
If you need to run the load option in Parallel you need to specify PARALLEL=TRUE.
If you want to by pass index as well as if you need to load in parallel, the APT_ORACLE_LOAD_OPTIONS will need to be set as 'OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)'.
Alternatively you can acheive the effect of Index operation through IndexMode option as by setting it to Rebuild. Or disabling constraints, if the Primary key is only Index.
By using this variable you (and without using the Index Mode options) you can load the Tables which has index in it.
If you want to load through index, you need to specify DIRECT=FALSE.
If you need to run the load option in Parallel you need to specify PARALLEL=TRUE.
If you want to by pass index as well as if you need to load in parallel, the APT_ORACLE_LOAD_OPTIONS will need to be set as 'OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)'.
Alternatively you can acheive the effect of Index operation through IndexMode option as by setting it to Rebuild. Or disabling constraints, if the Primary key is only Index.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Loading into Oracle Enterprise Stage
Thanks for the Replies..........
See I have the options of the APT_ORACLE_LOAD_OPTIONS defined as (DIRECT=Y,PARALLEL=TRUE,SKIP_INDEX_MAINTENANCE =Y). I'm not using this parameter anywhere in the Job.I'm declaring it only as Job Parameter.
When will this options will be taken by the compiler?
Purushotham
See I have the options of the APT_ORACLE_LOAD_OPTIONS defined as (DIRECT=Y,PARALLEL=TRUE,SKIP_INDEX_MAINTENANCE =Y). I'm not using this parameter anywhere in the Job.I'm declaring it only as Job Parameter.
When will this options will be taken by the compiler?
Purushotham
Check the Oracle table in development and test environment. I'm thinking you do not have index on the table in development but you have an index on the Oracle table in test. Run following query in both environment to confirm this:
select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME
from ALL_INDEXES where table_name = '<YOUR TABLE NAME>'
If the query returns that you have an index on the table in test environment that would meen you will have to add index rebuild option for load to be successful. You can skip the index maintenance in DataStage job by adding following option:
APT_ORACLE_LOAD_OPTIONS = OPTION(DIRECT=FALSE,PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=TRUE)
This will make to load run but you cannot query the table before you rebuild the indexes manually.
Hope it helps.......
select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME
from ALL_INDEXES where table_name = '<YOUR TABLE NAME>'
If the query returns that you have an index on the table in test environment that would meen you will have to add index rebuild option for load to be successful. You can skip the index maintenance in DataStage job by adding following option:
APT_ORACLE_LOAD_OPTIONS = OPTION(DIRECT=FALSE,PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=TRUE)
This will make to load run but you cannot query the table before you rebuild the indexes manually.
Hope it helps.......
Assume everything I say or do is positive
Loading into Oracel Stage
Guys,
I'm new to DataStage and in my Job I will Loading the Data from a DataSet to a Oracle Enterprise Stage in a parallel canvas.
I didn't even find the Index mode only. I saw that Loading can be happen in three ways- Load, Upsert and Delete.
I'm not knowing where this Indexed mode is coming into Picture.
Please clarify,Very Useful for me.......
I'm new to DataStage and in my Job I will Loading the Data from a DataSet to a Oracle Enterprise Stage in a parallel canvas.
I didn't even find the Index mode only. I saw that Loading can be happen in three ways- Load, Upsert and Delete.
I'm not knowing where this Indexed mode is coming into Picture.
Please clarify,Very Useful for me.......
Loading into Oracel Stage
Thanks Kumar...........
Got the Option........
Got the Option........