Before Routines in Oracle Enterprise Stage
Moderators: chulett, rschirm, roy
Before Routines in Oracle Enterprise Stage
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.
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.
Re: Before Routines in Oracle Enterprise Stage
Hi,Bill_G wrote: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.
You can run the
Code: Select all
ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
Thats right, but u can put the command in the open command of a source Oracle E stage.Bill_G wrote:Unfortunately, the open command does not seem to be an option when the write method is 'UPSERT'
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
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 wrote: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.
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
Pardon me, replied without seeing what you are doing in the sequel. Well, at first thought, instead of doing for the session, can u useBill_G wrote:I do have some lookups that are sourcing Oracle via Oracle Enterprise, but isn't the Upsert a completely different session?
Code: Select all
ALTER INDEX <schema.index> disable
Sorry for the useless posts.
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
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):
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.
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
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.