Avoiding Data disappear

mab_arif16 · Post by **mab_arif16** » Thu Jun 15, 2006 12:31 pm

Hi
One of my jobs refreshes the datamart using ODBC enterprise stage's truncate and write option.The job runs for about 10 minutes during which the data gets deleted and loaded and user sees an inconsistent data.
Is it any way possible to see the old data in the table during the run time ,and after the load is completed see the new data.
Thanks
Arif

DSguru2B · Post by **DSguru2B** » Thu Jun 15, 2006 1:48 pm

Sure, get a snapshot of the table before deleting and loading the new data. Then your comparison can be done.

chulett · Post by **chulett** » Thu Jun 15, 2006 2:14 pm

Or load a second copy of the table and when the load completes, swap it in for the original.

sud · Post by **sud** » Thu Jun 15, 2006 2:48 pm

chulett wrote:Or load a second copy of the table and when the load completes, swap it in for the original.

Exactly, this is called mirroring. It is infact pretty easy to achieve this through materialised views. Maintain a materialised view for your table and make sure that users always hit the materialized view. Refresh the view after each load.

Hope this helps :D

mab_arif16 · Post by **mab_arif16** » Thu Jun 15, 2006 2:58 pm

sud wrote:
chulett wrote:Or load a second copy of the table and when the load completes, swap it in for the original.
Exactly, this is called mirroring. It is infact pretty easy to achieve this through materialised views. Maintain a materialised view for your table and make sure that users always hit the materialized view. Refresh the view after each load.

Hope this helps :D

Thanks for help
Can this be done through Data Stage also.
Arif

sud · Post by **sud** » Thu Jun 15, 2006 3:42 pm

Yes , what you can do is create a procedure to refresh the materialised view and call this procedure in the close command of your target stage in the job or call the procedure through datastage after the loading job finishes. Since your users will always be hitting the materialised view, they will see the updated data.

mab_arif16 · Post by **mab_arif16** » Thu Jun 15, 2006 3:52 pm

sud wrote:Yes , what you can do is create a procedure to refresh the materialised view and call this procedure in the close command of your target stage in the job or call the procedure through datastage after the loading job finishes. Since your users will always be hitting the materialised view, they will see the updated data.

Thanks
That was helpful