Hi Everybody,
I am new to DB2 and I am not a core Database admin. I use database for reports purposes with Business Objects. Following is the problem i am facing and expect a solution here.
Requirement:
1) We have a Transaction DB2 database
2) We need to create another database(a replica) of the above mentioned database and it should hold Live/Almost live data. This database will be used for Reporting. We thought of this approach because the reports selection(read only) will not cause any performance issues to the front end or to the database itself.
3) Initially we thought MQT will be a better option. Refresh Deferred will populate the staging table as and when the transaction database gets updated and we thought of using a trigger on the staging table. i.e. as and when the staging table gets updated, we refresh the target database. Not sure if this will work. Please comment on this. Also we came to know that using additional commands we can say that the transaction tables are not locked when we read for MQTs.
Is there a better way to create a seperate ODS for reporting? Which is live?
We planned to use Business Objects as the reporting tool and found that Business objects generated query can use "WITH UR" function so that the transaction database is not locked when the reports are reading directly to the transaction database.
Is it advisible for application and reports running directly on the transaction database? Will this also affect the performance?
I am new to DB2 and I am not a core Database admin. I use database for reports purposes with Business Objects. Following is the problem i am facing and expect a solution here.
Requirement:
1) We have a Transaction DB2 database
2) We need to create another database(a replica) of the above mentioned database and it should hold Live/Almost live data. This database will be used for Reporting. We thought of this approach because the reports selection(read only) will not cause any performance issues to the front end or to the database itself.
3) Initially we thought MQT will be a better option. Refresh Deferred will populate the staging table as and when the transaction database gets updated and we thought of using a trigger on the staging table. i.e. as and when the staging table gets updated, we refresh the target database. Not sure if this will work. Please comment on this. Also we came to know that using additional commands we can say that the transaction tables are not locked when we read for MQTs.
Is there a better way to create a seperate ODS for reporting? Which is live?
We planned to use Business Objects as the reporting tool and found that Business objects generated query can use "WITH UR" function so that the transaction database is not locked when the reports are reading directly to the transaction database.
Is it advisible for application and reports running directly on the transaction database? Will this also affect the performance?