DSXchange

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

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

What is the value of APT_ORACLE_LOAD_OPTIONS given in Adminstrator client?

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.

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

Hi Purushotham,
The variable should be included in your job for the effect to take place. Else the option what ever you specified in Index Mode will be used.

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.......

I guess, this is a reply to Roy.

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.......

When you choose the Write mode as Load, you can find "Index Mode" under "Optinos".

Thanks Kumar...........

Got the Option........