Hi all,

We have a database against which lot of reports are run because of our reporting needs during the day time. We are thinking to have a seprate reporting server to cater to these reporting needs.

Now the way these reports are run, it creates a temporary table everytime. Is it possible to create a view and have these resports run against this view everytime rather than having to create a temporary table. Will that be better for performance? If I am not wrong if the tables are updated , the associated view will also get updated automatically.

I would appreciate if all of you could give some suggestions on what to consider when setting up a reporting server.

ENV: AIX, DB2 UDB v8.1 FP 7, repoting engine to be used: JReport

Thanks

No, In DB2 views do not exist as materialized tables separate from the base table.

Some other databases have materialized views, which are called materialized query tables (MQT) in DB2. There is only a perforamnce benefit if the MQTs are summaries of the base tables.

MQTs can get updated automatically whenever the base table changes (if you configure it that way) or can be udpated on a deferred basis.

HADR is one option to replicate the data on two different servers. Primary and secondary.
I am not sure about whether DB2 provide the facilty to replicate the data on multiple server some chaining etc, as I have seen there are two: primary server which is active and secondary server which is standby.

The secondary server can be used for reporting purposes.

This mitght solve your problem.

Solution about Performance Kill Temporary tables.

1) There is a need to optimize the database.
2) Bad indexing cause the creation
3) Need to refine the where clause in reports.

Istikhar Ahmad.

As of right now, you cannot connect to an HADR standby database. IBM has said that read-only access to a HADR standby database may be a future enhancement, but no date has been announcemed.


Tradtional replication and Q-Replication may be able to provide replication to stadby server to meet your reporting requirements.

Thanks for all your responses.