Oracle Stage

[Munish]

Hi All,

Could I please request you champs to comment on the following:

Question/Suggestion related to Oracle Stage:

1.
Original question which we have sorted out:

"We are planning to use bitmap indexes on our fact tables. We're currently experiencing some issues with our ETL load due to the way we have configured our indexes. It looks like to enable bitmap indexing; we need to use local range partitioning. We think the problem is that are indexes are currently global. Could you please let me know if there are any other "gotchyas" that you know of when using the DataStage bulk loader (SQL*Loader) on tables with bitmap indexes using range partitioning?"

We are using
Index Mode: 'Maintenance'.
Additional efforts:
We used
'APT_ORACLE_LOAD_OPTIONS' with values as 'OPTIONS(DIRECT=TRUE,PARALLEL=FALSE)'.
These values are opposite to default which is (DIRECT=FALSE,PARALLEL=TRUE).

We are not getting any error. Everything seems to be fine in Dev environment where the bill file size is hardly 200MB, Real time it is going to be at least 200 GIG, whopping 10000 times.

However,
Some questions to ask:
1. What should we do if the indexes are local but not 'range-partitioned', let us say 'Hash'.

2. What the various options for 'APT_ORACLE_LOAD_OPTIONS' other than DIRECT and PARALLEL.
How to use them and when?? There is nothing in documentation regarding this.

3.
It seems that 'Maintenance' do not rebuild the indexes but tries to place the data at the right place where it should be and that is why setting is 'PARALLEL = FALSE".

IS IT TRUE ?????
If yes, considering our data volume which in Phase III is going to be increased from 280 mills to 0.5 bills, will it be efficient??????
Can we run it parallel ??

4. Are there any other ways to load the data like
Developers are using DS to create .ctl file which is used by sqlloader and doing manual load.
Is it recommended?

5. What might be the best approach for such a large data and with partitioned - sub partitioned tables using local bit map indexes?????


Question 2:
Oracle stage (OCI) can have rejection link for upsert mode

Exceptions Table: This property enables you to specify an exceptions table, which is used to record ROWID information on rows that violate constraints when the constraints are re enabled. The table must already exist.

What is the schema of this exception table???
There is no mention of this in documentation??

Any thoughts/ suggestions are welcome.

Thanks and regards,
Munish

[ray.wurlod]

Enrol on the IBM class "Advanced DataStage (EE)" (code DX436). Most of your questions on this thread are covered there.

[Munish]

Thanks Ray,
Cheers,
Munish

[chulett]

Most of these questions aren't DataStage questions, per se - they relate directly to Oracle. You should be able to take them to your friendly neighborhood DBA man and put them to him. Or her.

[Munish]

Ya, I will catch hold of our DBA.

Thanks,
Munish