refreshing oracle materialized views

[jasper]

hi,
we're using 2 types of materialized views in oracle(10g), for snapshot refresh from other oracle DB's and for standard MV to pre-calculate views.
I would like to make some kind of datastage jobs to refresh these MV's.
For now a unix script is run during the datastage flow to do this, but we would like to have it more in the datastage flow.
For this I see different options, has anyone done something like this in the past ?

different options I see:
-create a routine to which a tablename is given. This can then be used in a job that selects from a table containing MV-names( or maybe from oracle-sys tables if this exists?). output from the routine could be written to a log table (this MV was refreshed at that time, with status)
-create a custom stage that calls a unix-script that refreshes the mv. I have no experience with this however.
-handle it in user-defined statements in oracle stages, I'm not sure how we can catch problems with the refresh this way.

things to consider:
-we cannot access every source at the same moment so would like to have a job that refreshes all MV's for one source given as a jobparameter.
-for some sources we have a lot of MV's, we cannot refresh all in parallel (eg 500 MV's but only 10 can be handled together)

[roy]

Hi,
IMHO (In My Humble Opinion),
The best way if you want it handled with DS is to get your DBA to give you a user defined sql to refresh the Materialized View/s you need.
Or to run a SP that does it.
Ask your DBAs for their opinion (being thaat a pure DBA task).

If you were saying you want to replace the MVs with DS jobs that was fine but invoking existing Oracle replication mechanisms does seem unnatural to me.

Using the central MVs as a source for DS proccessing the destributed data makes scense to me, but invoking the MV's refresh poses more implications you might not want to be responsible for.

In short it can be done but not my cup of tea .

IHTH,

[cohesion]

In my experience, Materialized Views in Oracle are tricky enough to refresh without bringing DataStage into the equation. Have you defined refresh groups for the MVs to ensure the entire group is refreshed as an atomic transaction? If not, is it acceptable that any queries running as the refresh kicks in may end up using some refreshed and some stale MVs?

Even if this is okay, MVs are a fairly Oracle-specific feature, the only way to trap an error is using a PL/SQL routine in which case the easiest way to get that into DS is probably to write it to an error log table (inside PL/SQL) and then, from DataStage, query that table for any errors.

[ray.wurlod]

It was always my understanding that materialized views are best automatically maintained by the database. Certainly this is the case with Red Brick (in which they are called precomputed views). Otherwise what's the point of calling it "materialized"? If it's out of date then it's no better than a standard view. Worse, actually, since the used could be lulled into a false sense of security about how up-to-date the data are.

[jasper]

ray,
as far as I know in Oracle you always have to trigger a refresh, in a typical DWH this is not a real problem, you load all your dimensions/facts and then refresh the MV, which will then be valid untill the next run(in our case next nigth).

Solution I'm now checking is:
- Created a routine that executes a unix-command(started from the code of ExecSH)
- Trough this routine I call a unix-script that takes a MV-name(and DB,USER,PWD,...) and refreshes this.
-in a job I start from an oracle stage that selects from all_mviews route the name to a basic-transformer which runs the routine( number of parallel processes determines the number of parallel refreshes)
-output is then written to another table , warnings are generated in the log based upon this output. Script also logs directly to a logfile(for details of failure)

[ray.wurlod]

Please let us know whether this approach achieves the desired result. I am sure that there will be others who need something like this in the future. (Or convert to Red Brick, where automatic maintenance of precomputed views "just happens"! )