Using Load option in Oracle with indexes

[roy]

Hi All,
I must be missing something here but here goes:
In my Dev Project I'm loading a table which is indexed using the load method, I have the Env var set to 'OPTIONS(DIRECT=FALSE,PARALLEL=TRUE).
My dev project run works with this log entry:

ORA_LAK_LAKOACH_INS: When checking operator: APT_ORACLE_LOAD_OPTIONS has been set by user. Therefore, Orchestrate assumes that the DIRECT and/or PARALLEL options have been set to FALSE, and that it is okay to load table 'LAK.LAK_LAKOACH', even though it is indexed, and an index option (rebuild or maintenance) has not been included. APT_ORACLE_LOAD_OPTIONS has been set by the user to 'OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)'.

after export/import to my TEST project which seems to have the same env vars defined
job aborts and I get this error:

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

Any Ideas would be apreciated.
Thanks in advance for your time ,

[gbusson]

hi roy,

in the Oracle Enterprise Stage, there is an option: index mode.

Set this to tell the loader what to do with the indexes.

[Benouche]

Roy,

did you check the value the APT_ORACLE_LOAD_OPTION at runtime ?

You can check this in the job log, it's the second/third entry of the job log (Environment variables settings : ...).
If it is set to DIRECT=TRUE, this will explain the behaviour of the job, and you'll have to modify the settings of your project to fix the problem.

Benouche

[balajisr]

Indexes on table 'LAK.LAK_LAKOACH' preclude direct parallel loading unless an index option is included

When you give Direct = FALSE conventional path mode should be used. It seems that it uses direct path instead of conventional path mode inspite of your environment variable settings. Due to direct mode it asks you to specify index mode or drop indexes.

--Balaji S.R

[roy]

As can be seen in the environment variable's value in the parameters list of the job runs:
TEST:
$APT_ORACLE_LOAD_OPTIONS = OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)

DEV:
$APT_ORACLE_LOAD_OPTIONS = OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)

Further investigation led to the following.
Some how the env variable list in the job runs of the above APT variable seems to show only in my DEV project, even though it seems to be set in the TEST project as well (watching the project properties environment variables list)

Baring in mind that both projects are on the same server (hence use the same dsrpcd service and have the same dsenv loaded)
I'm investigating why this is so.

I'll keep updating as I'll have more info...

[roy]

The plot thikkens
I found out that DSParams file in DEV project had the APT var defined and the TEST didn't have that defined.
But I was expecting that existance of the env variable in the params list of the jobs with explicit (not $ENV) setting should override it anyway or define it in case it's none existant.

Still investigating ....

Any thoughts anyone?

[gbusson]

wen do you set it?

[kwwilliams]

Roy,

I wasn't sure if I ever saw a response to gbusson's post. I believe he is right in that there is an index option in the Oracle stage itself, I believe it is one of the optional properties. Set the index properties to rebuild and you should clear out this error.

[gbusson]

kwwilliams,

i was wrong with my first post.

the DIRECT=FALSE option causes oracle to avoid setting an index option.

I do not understand why the job does not have the same behaviour.
The 2 environment variables are set correctly at run time! Moreover, this APT variable is not a user defined variable!

[roy]

This is .
My DEV proj has // where the OPTIONS section is and
"APT_ORACLE_LOAD_OPTIONS=OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)"
at the end in the EnvVarValues Section

My TEST Project has the APT_ORACLE_LOAD_OPTIONS=OPTIONS(DIRECT=FALSE,PARALLEL=TRUE) set in suposed location (mid of DSParams File) but nothing in the EnvVarValues.
When I put single/double qoutues on the mid entry still not seeing it in env vars.
So I adjusted the TEST to the DEV config and now it shows on env.

I'm Waiting on my developer to test it and I"ll post was it ok.

Well That's the job I guess (to boldly go where no one has gone before )

[gbusson]

APT_ORACLE_LOAD_OPTIONS is set 2 times in your DSParams?

[roy]

After seeing the original set doesn't work I added one in the EnvVarValues section after emptying the original entire's values
Now when I run an EE job I can see the said APT variable in the environment variables of the job in the job log.

[gbusson]

that seems correct.

Why don't you use the Administrator to see the values? It is much easier!

[roy]

Administrator shows:
OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)
in both projects

[Benouche]

Roy,

you did not answer to my first query :
What is the value of the env. var AT RUNTIME for both jobs ?

If the values are different, you'll have to find out why, depending on how you set env var / job parameters in your projects ...

Benouche