Explain SQL replication in DataStage
Replication is a collection of technologies that allow data and database objects to be copied. Then replicate it from one database to another, and then synchronized to maintain continuity between databases. Merge replication is primarily intended for mobile applications or distributed server applications where data conflicts are possible. In this article let us study the SQL replication in DataStage. You can use SQL replication in DataStage but before that you need to install DataStage.
Install DataStageThe DataStage server supports operating systems with AIX, Linux , and Windows. You may pick according to requirement. Then you can use the Asset Interchange tool to move the data from an older version of infosphere to a new version. Data stage online course helps you to learn more skills and techniques.
Installation files
For Infosphere Datastage to be installed and configured, you must have the following files in your setup.In Windows,EtlDeploymentPackage-oracle.pkg for windowsEtlDeploymentPackage-db2.pkgFrom Linux,Package EtlDeployment-linux-db2.pkgEtlDeploymentPackage, oracle-linux.pkgNow that you have installed DataStage. Let us set up the SQL replication.
Setup SQL server Replication in DataStage
Step 5
Use the following command to create Inventory table and import data into the table using the command below.Inventory.ixf import db2 from ixf builds an inventory
Step 6
Make a table of targets. Then Name STAGED as target database.Since now you have created both the source and target databases, we'll see how to replicate the next step. The following information can be useful when setting up a source of ODBC data.Creation of objects with SQL Replication in DataStageThe image below shows how the data change flow from source to target database is delivered. You create a source-to-target mapping of tables known as subscription set members, and group members into a subscription.
The replication unit (Change Data Capture) within InfoSphere CDC is referred to as a subscription.The adjustments that have been made in the source are recorded in the "Capture control table" that is sent to the CD table and then to the target table. While the application program will have the specifics of the row from which changes need to be made. In the subscription package, it will also enter the CD table.A subscription contains mapping details which specify how data is being applied to a target data store in a source data store. Note, CDC is now referred to as replication of data from the Infosphere.Upon execution of a transaction, InfoSphere CDC records modifications on the source site. InfoSphere CDC delivers the change data to the target, and stores information about the sync point in the target database in a bookmark table. datastage administrator training from industrial experts.
InfoSphere CDC uses the information from the bookmark to track InfoSphere DataStage work progress.
The bookmark information is used as a restart point in the event of a malfunction. In our example, the table ASN.IBMSNAP FEEDETL stores syncpoint information related to DataStage, which is used to track DataStage progress.
You must do following stuff in this segment,To store replication options, build CAPTURE CONTROL tables and APPLY CONTROL tablesEnter the tables PRODUCT and INVENTORY as sources of replicationCreate a two-Member subscriptionBuild members of subscription set and aim CCD tablesUse the command line program ASNCLP to set up a SQL replication
Step 1
Locate the script file within the directory.
Step 2
Replace your user ID and password for connecting to the SALES database in the file < db2-connect-ID > and "< password >."
Step 3
Shift directories to the directory sqlrepl-datastage-tutorial / setupSQLRep and execute script. Use the Command below. The command will connect to the SALES database and generate a SQL script to create tables for the Capture control.asnclp –f crtCtlTablesCaptureServer.asnclpStep 4 Find the script file crtCtlTablesApplyCtlServer.asnclp in the same folder. Now replace the user ID and password for connecting to the STAGE DB database with two instances of < db2-connect-ID > and "< password >"
Step 5
Now use the following command to construct control tables for application in the same command prompt.
asnclp –f crtCtlTablesApplyCtlServer.asnclp
Step 6
Locate the crtRegistration.asnclp script files and replace all < db2-connect-ID > instances with the User ID to connect to the SALES database.
Step 7
Use the following script to register the source tables. The ASNCLP program will create two CD tables as part of creating the registry. CDPRODUCT AND Distributionasnclp –f crtRegistration.asnclpThe command CREATE REGISTRATION uses the options ofDifferential Refresh: Invites Apply program to update the target table only when rows change in the source table Image Both: This option enables the value to be recorded in the source column before the change occurred, and the value after the change occurred.
Step 8
Use the following steps to connect to the destination database (STAGEDB).Find the file crtTableSpaceApply.bat, open it in a word editorReplace the user ID and password with < stagedb-connect-ID > and < stagedb-password >Enter crtTableSpaceApply.bat in the DB2 command window, and run the file.This batch file creates a new destination database tablespace (STAGEDB)
Step 9
You need to Locate the script files crtSubscriptionSetAndAddMembers.asnclp. Then make the following modifications.
Replace all < sales-connect-ID > and < sales-password > instances with a user ID and password to connect to the SALES database (source).
Replace all < stagedb-connect-ID > and < stagedb-password > instances with the User ID to connect to the STAGEDB database (target).After changes, the script will run to build a subscription set (ST00), which will bring the source and goal tables together. The script also generates two subscription set members, and the target database CCD (consistent update data), which will store the changed data. Infosphere DataStage will consume those data.Step 10 Run the script to create tables for subscriptions, subscription-set members, and CCDs.asnclp –f crtSubscriptionSetAndAddMembers.
asnclpVarious options and two members used to create a subscription set includeFull down on condensedOutsideImport load type ExportSteady pacingStep 11Because the replication administration devices are faulty. In the IBMSNAP SUBS SET control table, you will execute another batch file to set the TARGET CAPTURE SCHEMA column to null.Locate the updateFileTgtCapSchema.bat. Using a text editor to open it. To connect to the STAGE DB database replace <stage db-connect-ID> and <stage db-password> with the user Name.Enter the updateTgtCapSchema.bat command in the DB2 command window, and execute the file.
Conclusion
I hope you reach a conclusion about setting up SQL in DataStage. You can learn more through DataStage Online Training.