Changed data from a SQL Server source can be captured only if CDC is enabled at the database, table, and column levels, in the same order. To perform a changed data transformation (CDT) from a SQL Server to an Enterprise Database Server, you must enable CDC on all the transformation source tables and the respective columns before loading the schema into the DDW. You must then define one or more transformations and deploy them to the Administration Site.
Before starting a CDT from a SQL Server to an Enterprise Database Server, ensure the following:
The bulk data transformation (BDT) is completed if you had decided to perform both BDT and CDT.
The Capture job and SQL Server Agent are running.
Notes:
If the source database was restored, or if any changes were made to the schema, it is recommended that you disable CDC and re-enable it, before proceeding with the CDT. You may also want to perform a bulk data transformation again, if there has been a considerable amount of change in the data in the source database.
To perform Delete and Update operations on the target database, Data Exchange needs a unique key to identify a record and a spanning set to find and lock the record before it is updated or deleted.
To start a CDT
Click Manage.
The hosts associated with the deployed transformations appear in the Source Database Hosts pane.
In the Source Database Hosts pane, expand Windows Hosts and then expand the required host to view various databases hosted on it.
Select the database on which you want to perform a CDT.
Point to Start, and then click Changed Data Transformation.
The Data Exchange Transformation Session Setup appears.
On the Transformations tab, select the transformation(s) you want to run, and click Next.
The General Settings tab appears.
On the General Settings tab, enter or change the following information, as appropriate:
The Agent Host box displays the host name on which the Data Exchange Agent for SQL Server is installed.
The Agent TCP Port box displays the default port number (9298) on which the Agent is listening. You must change the TCP port number only if you are not using the default port number.
The Runtime Service Host box displays the host name or IP address of the Windows partition where the Runtime Service is installed.
You must change the value of the Runtime Service host if both the Runtime Service and the Administrative Service are not running on the same Windows host.
The Runtime Service TCP Port box displays the TCP port number of the Runtime Service that was configured during installation.
If you require a secured connection, turn on Secure Connection. This enables you to configure a secure connection for data transfer between Runtime Service and the target database.
The Target Host displays the target MCP host name.
The DMSII Adapter TCP Port box displays the default port number (8299) on which the adapter is listening.
In the AIS Connection Name box, type the AIS Connection Name that you had defined using the AIS Configuration Utility.
If you need a secure connection from Runtime Service to target database, enable secure connection in AIS, and then enter the corresponding Connection Name.
Click Next.
The Transformation Settings tab appears.
On the Transformation Settings tab, in the Date Time box, enter the date and time (of the Windows host where the source SQL Server database is located) from when the DE Agent for SQL Server needs to read the captured changed data.
This is required only if the bulk data transformation was run with the option With No Lock. You cannot enter a future date or time.
Note: The changed data is retained in the change tables based on the retention value. By default, the retention value is three days. If you enter a date that is three days earlier than the current date, then Data Exchange starts reading the earliest changed data from the CDC tables. Additionally, if a CDT is stopped in the middle for more than three days, then the changed data might be cleaned from the CDC tables and Data Exchange will not be aware of it. This may lead to data loss.
If the bulk data transformation was run with the option With Update Lock, then Data Exchange automatically takes the start time for the CDT.
Click Next.
The Start Transformations tab appears.
Verify all the details, and then click Start.
If the CDT fails to start, the error is displayed on the Changed Data Transformation Status box. Click the icon in the Details column for more information on the cause and resolution of the error. The transformation starts after you have resolved all the errors.
After you initiate the CDT, each change that occurs in the transformation source according to the activated transformation is continuously updated in the transformation target in near real time. Click Monitor on the Summary tab to see the statistical data corresponding to the CDT. You can also log in to the transformation target and verify the update.
Notes:
If the credentials of your transformation target have changed since you loaded the target schemas to the DDW, then the Runtime Service cannot update the transformation targets because of the mismatch in the credentials. In this case, you must use the Change Login Credentials option and update the credentials in the DDW, and then redeploy the transformation to the Administrative Service. Refer to Changing Login Credentials on the Information Center for more information.
While starting a CDT and while checking the format timestamp before performing a CDT, Data Exchange calls the command LogonCheck if SecuritySupport is configured in MCP. LogonCheck captures logon attempts for all MCP usercodes. For more information on LogonCheck, refer to the *SYMBOL/SECURITYSUPPORT file.
If any of the recoverable errors configured on the Recovery subtab (of the Settings tab) occur, Data Exchange automatically tries to recover from the error for the number of times specified on the Recovery subtab. To stop the automatic recovery and to perform the CDT at a later point, click Stop Recovery. The Stop Recovery button is available only when the system is in the recovery state.
If any errors not configured for recovery occur while CDT is running, you must resolve the issue and restart the CDT. Data Exchange automatically starts processing from where it stopped. You can see the error details on Events. See Common Errors and Possible Solutions for more information about possible errors and how to recover from them.
During the CDT, the overall Data Exchange Status changes to Changed Data Transformation Running on the Summary tab. During the automatic recovery, the overall status of Data Exchange on the Summary tab changes to Recovering.
Click Refresh to obtain the current status (stopped, running, or error) of the CDT.