Page 1 of 1
Before Routines in Oracle Enterprise Stage
Posted: Fri Jun 09, 2006 3:26 pm
by 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.
Re: Before Routines in Oracle Enterprise Stage
Posted: Fri Jun 09, 2006 3:38 pm
by sud
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.
Hi,
You can run the
Code: Select all
ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE
in the open command of the Oracle Enterprise stage.
Posted: Fri Jun 09, 2006 3:41 pm
by Bill_G
This has worked for you? Does DataStage accept a delimiter like a / between the alter session statement and the update statement?
Posted: Fri Jun 09, 2006 3:44 pm
by Bill_G
Unfortunately, the open command does not seem to be an option when the write method is 'UPSERT'
Posted: Fri Jun 09, 2006 5:13 pm
by sud
Bill_G wrote: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.
Posted: Fri Jun 09, 2006 5:18 pm
by 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.
Posted: Fri Jun 09, 2006 5:20 pm
by sud
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.
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]
Posted: Fri Jun 09, 2006 5:23 pm
by Bill_G
I do have some lookups that are sourcing Oracle via Oracle Enterprise, but isn't the Upsert a completely different session?
Posted: Fri Jun 09, 2006 5:52 pm
by sud
Bill_G wrote: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.
Posted: Fri Jun 09, 2006 6:04 pm
by 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.