| Maintain Logical Transaction Integrity in a High Availability ODS [message #248953] |
Mon, 02 July 2007 18:35  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
We're beginning the design of an Operational Data Store using DataStage to load data from various source systems into a 10gR2 database, with a Business Object reporting interface. The ODS requires high availability, so we will be performing ETL during the working day whilst users are running reports.
We can't work out how we can gurantee logical transaction integrity.
If a transaction in the source system inserts rows into two or more tables as part of a logical transaction then we want to make sure that all of those rows become visible in the ODS at the same time.
Datastage works best in a single table-to-table (or file-to-table) transformation. So if you have a separate job for each table, logical transaction integrity will not be preserved. ie. Rows loaded into the source system in 1 transaction are loaded into the ODS in 2 or more transactions - one for each table.
Does anyone have any creative solutions to this? Ideas that have occurred to me:
- DBMS_FLASHBACK package to freeze users view of data whilst the ETL is active. Problem is that I cannot work out how to get Business Objects to issue the procedure calls; it just runs queries - not procedures.
- Standby database of some type. Load one database whilst users query another. Unsure as to how to go about migrating sessions between databases without killing them. Unsure whether this is even possible.
- Create Datastage jobs that transform many tables at once. I am concerned that this solution is not extensible, and that I'll end up with one job transforming dozens of tables.
- Obtain iterfaces from the source systems that preserve transactional integrity by including many record types in a single file. This would make for insanely comlicated DataStage jobs - DS is not especially good at different record types in data files.
None of these sound viable to me (except possibly standby database if there is a neat way of switching databases). Can anyone think of any other Oracle-based solutions?
Ross Leishman
|
|
|
|
|
|
| Re: Maintain Logical Transaction Integrity in a High Availability ODS [message #249276 is a reply to message #249215] |
Tue, 03 July 2007 21:22  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Not sure I understand your question....
The system is used for reporting. The problem is that we want the reports to have a consistent view of the data whilst the ETL is running.
I may have selected the wrong forum to post this in, but I this one would be best bet to find out what sort of 2-database approach could be applied. I will move it to Server Admin tomorrow if I still get no interest.
Ross Leishman
[RL: Used my moderator privs for evil and moved this to Server Admin to reach a broader audience]
[Updated on: Wed, 04 July 2007 21:57] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Maintain Logical Transaction Integrity in a High Availability ODS [message #250754 is a reply to message #249823] |
Wed, 11 July 2007 03:24 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The data extract from the source system will use Oracle CDC, so we can guarantee that the source files will retain transaction integrity (ie. if the app committed transactions in 2 or more files, either all transactions will be present in a set of extract files or none of them will.
So now we have a set of (say) 10 files that have logical transaction integrity. The problem is:
How to load those 10 files in a single transaction?
Clearly I can do this with EOTs and SQL, but the ETL will be written in DataStage, not SQL; so that's no help.
Say we use one database for ETL, and a Logical Standby Database for reporting. We could load the data file-by-file using DataStage into the primary database, but then the refresh of the Logical Standby Database would also be file by file because DataStage performed a commit after each file.
Logical Standby Database would only be a solution if we could re-synch ALL changes since the last synch in a single commit.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
