Topic
  • 3 replies
  • Latest Post - ‏2013-01-25T11:40:10Z by Rphilo
AbhilashJoseph
AbhilashJoseph
36 Posts

Pinned topic Handshake with source system

‏2013-01-22T23:54:17Z |
Hi 
I have been struggling to find  a way to define a solution for the handshake between source and our system because we do not have any mechanism to find whether CDC has completed processing records for all records which belong to a particular batch in source table .We capture only from log which captures the transactions and that is not a one to one representation of no of records in source system for that batch and hence we cannot say CDC has completed processing for what it was supposed to collect .  
 
We are using Live Audit to our staging table using archive log only and then have other ETL jobs reading from staging table to reporting layer . We are not allowed to read any data tables in source system ( only control tables and archive logs) The issue is with when to invoke the staging to reporting layer load . 
 
Any help appreciated  
 
Regards 
Abhilash 
Updated on 2013-01-25T11:40:10Z at 2013-01-25T11:40:10Z by Rphilo
  • Rphilo
    Rphilo
    384 Posts

    Re: Handshake with source system

    ‏2013-01-23T10:43:33Z  
    Abhilash
     
    If you use net change this would end the subscription, so you could use the Management Console commands to check the subscription status and when it changes from running to stopped you would know that you should be able to launch the ETL process. However this technique would have some of potentially serious downsides:
        - additional delay as you would have to process all the pending transactions before starting the ETL, whereas continuous mirroring would enable you to start the ETL launch much more quickly after the batch ends
        - if your monitoring misses a case where the subscription ends abnormally, you would start the ETL prematurely (you could eliminate this by using a java user exit to monitor the event log and detect a normal end of replication)
        - impact on the source database as CDC catches up (this impact could be mitigated by using continuous capture if supported by the source engine, albeit at the cost of additional disk storage to hold the staged transactions)
     
    Some other possibilities:
        - the operational processes which run the batch also execute the dmendreplication command on the source - a normal termination will allow CDC to process the  pending transactions up to the point where replication was ended. You could then use the MC commands or notifications user exit as above to detect that the subscription was not active on the target
        - a new table is added to the source database for control purposes. This table has a message field. When the source batch process ends it also updates this control table which is replicated to the target. The target table either has a tirgger, or there is a user exit in the table mapping which launches the ETL.
     
    Hope some of these ideas are applicable in your environment
     
    Regards
     
    Robert
     
  • AbhilashJoseph
    AbhilashJoseph
    36 Posts

    Re: Handshake with source system

    ‏2013-01-23T12:16:14Z  
    • Rphilo
    • ‏2013-01-23T10:43:33Z
    Abhilash
     
    If you use net change this would end the subscription, so you could use the Management Console commands to check the subscription status and when it changes from running to stopped you would know that you should be able to launch the ETL process. However this technique would have some of potentially serious downsides:
        - additional delay as you would have to process all the pending transactions before starting the ETL, whereas continuous mirroring would enable you to start the ETL launch much more quickly after the batch ends
        - if your monitoring misses a case where the subscription ends abnormally, you would start the ETL prematurely (you could eliminate this by using a java user exit to monitor the event log and detect a normal end of replication)
        - impact on the source database as CDC catches up (this impact could be mitigated by using continuous capture if supported by the source engine, albeit at the cost of additional disk storage to hold the staged transactions)
     
    Some other possibilities:
        - the operational processes which run the batch also execute the dmendreplication command on the source - a normal termination will allow CDC to process the  pending transactions up to the point where replication was ended. You could then use the MC commands or notifications user exit as above to detect that the subscription was not active on the target
        - a new table is added to the source database for control purposes. This table has a message field. When the source batch process ends it also updates this control table which is replicated to the target. The target table either has a tirgger, or there is a user exit in the table mapping which launches the ETL.
     
    Hope some of these ideas are applicable in your environment
     
    Regards
     
    Robert
     
     Hi Robert
    Thanks for the inputs. 
     We cannot end the subscription based on net changes as we do not know when the batch completes each day  and more over we use an N-tier architecture with source agent installed on our machine and not on source DB.
    Even if we one control table is edited by source system it does not ensure that all the records corresponding to completion of that batch is processed in other tables mirrored by CDC .
    Ideally I would think we would need source system to log the last committed transaction time for each mirrored table in a separate table and we would need to see whether a record with that time stamp is available in each mirrored target table . Do you concur ?

    Regards 
    Abhilash 
  • Rphilo
    Rphilo
    384 Posts

    Re: Handshake with source system

    ‏2013-01-25T11:40:10Z  
     Hi Robert
    Thanks for the inputs. 
     We cannot end the subscription based on net changes as we do not know when the batch completes each day  and more over we use an N-tier architecture with source agent installed on our machine and not on source DB.
    Even if we one control table is edited by source system it does not ensure that all the records corresponding to completion of that batch is processed in other tables mirrored by CDC .
    Ideally I would think we would need source system to log the last committed transaction time for each mirrored table in a separate table and we would need to see whether a record with that time stamp is available in each mirrored target table . Do you concur ?

    Regards 
    Abhilash 
     Abhilash
     
    If you can confirm that the timestamp of the transactions is taken from the commit time and not from the time the transaction was entered, that would be fine. 
     
    Regards
     
    Robert