Before Routines in Oracle Enterprise Stage

[Bill_G]

We are moving from 7.0 Server to 7.5.1a Enterprise. Our target database is Oracle 9.2.

As part of the ETL process, we mark the partitioned bitmap indexes as unusable prior actual loading of data by DataStage and then rebuild the indexes after the load.

In the server edition of DataStage, using the ORAOCI9 stage, we have been able to issue an ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE in the SQL > before tab.

In PX, we are using the Oracle Enterprise Stage and we are unable to issue this command - there doesn't seem to be an opportunity to run a before routine.

I have to think there is a work around. Has anyone else experieced this issue and solved it?

Thanks for your input.

[sud]

We are moving from 7.0 Server to 7.5.1a Enterprise. Our target database is Oracle 9.2.

As part of the ETL process, we mark the partitioned bitmap indexes as unusable prior actual loading of data by DataStage and then rebuild the indexes after the load.

In the server edition of DataStage, using the ORAOCI9 stage, we have been able to issue an ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE in the SQL > before tab.

In PX, we are using the Oracle Enterprise Stage and we are unable to issue this command - there doesn't seem to be an opportunity to run a before routine.

I have to think there is a work around. Has anyone else experieced this issue and solved it?

Thanks for your input.

Hi,

You can run the

Code: Select all

ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE

in the open command of the Oracle Enterprise stage.

[Bill_G]

This has worked for you? Does DataStage accept a delimiter like a / between the alter session statement and the update statement?

[Bill_G]

Unfortunately, the open command does not seem to be an option when the write method is 'UPSERT'

[sud]

Unfortunately, the open command does not seem to be an option when the write method is 'UPSERT'

Thats right, but u can put the command in the open command of a source Oracle E stage.

[Bill_G]

Maybe I am missing something here, but if I have an Oracle Enterprise Stage and choose the Write method as 'Upsert', the open command is not an option. I only see the open and close commands as an option when 'Load' is the chosen Write Method.

[sud]

Maybe I am missing something here, but if I have an Oracle Enterprise Stage and choose the Write method as 'Upsert', the open command is not an option. I only see the open and close commands as an option when 'Load' is the chosen Write Method.

I am sorry for not being explicit. In your job is there any Oracle Enterprise stage used as a source - where u r reading a table etc. There you can put this command.[/b]

[Bill_G]

I do have some lookups that are sourcing Oracle via Oracle Enterprise, but isn't the Upsert a completely different session?

[sud]

I do have some lookups that are sourcing Oracle via Oracle Enterprise, but isn't the Upsert a completely different session?

Pardon me, replied without seeing what you are doing in the sequel. Well, at first thought, instead of doing for the session, can u use

Code: Select all

ALTER INDEX <schema.index> disable

before the load and disable the specific index(here session won't matter). But I am not sure if this is exactly what u r intending to do.

Sorry for the useless posts.

[Bill_G]

Not a problem at all, I really appreciate you replying...


Basically i wrote a utility in PL/SQL that identifies the partitioned bitmaps for a specific range of time and marks them unusable. then I run my daily ETL load. Once it's complete, the indexes are rebuilt through another PL/SQL procedure by issuing a command like the following (please excuse the variables -but you get the picture):

Code: Select all

'ALTER TABLE ' || lower(ai.table_owner || '.' || ai.table_name) ||
            ' MODIFY PARTITION ' || lower(partition_name) ||
            ' REBUILD UNUSABLE LOCAL INDEXES' nds_index_str

We tested and found this the most efficient way fo dealing with the indexes, dropping them was more time consuming.

In our Server jobs, we simply issued the alter session command in the before tab. Now that we are using PX, I really would like to continue using the above process.

There must be something I am missing. I can't believe that they (Ascential/IBM) would take this functionality away from the upsert method of loading a table. I have a call into support and they are checking around for workarounds. Any creative adivice is always appreciated.