Manipulating Replication Data
This tutorial manipulates replication data by:
Filtering data
Combining multiple fields
Excluding data fields
Applying mathematical formulas
Extracting a portion of a field
Renaming fields
Prerequisites
Install Adabas version 6.1.4 or above.
Install CONNX Data Integration Suite 1.1.2 or above.
Install Adabas Event Replicator for LUW.
Create an Adabas database with a DBID of 1 using the Adabas sample tables. For this example, we will use the Adabas sample table EMPLOYEES-NAT (File11).
Start the Adabas database.
An available SQL Server database. For this tutorial, we recommend that you create a database named SQLDatabase. Otherwise, you can use any existing SQL Server database.
Create a CDD containing the source and target databases. For this example, we will use the CDD Adabas to Relational Replication (our last opened CDD) that was created in the Performing a Simple Replication - Adabas to Relational Database tutorial.
Filtering Replication Data
In this example, we will copy a table view, while filtering out a portion of the original table during the copying process.
1. Start the CONNX Data Dictionary. The CONNX Data Dictionary Manager window appears.
2. Select the EMPLOYEES table and click the Table Columns tab.
3. Click Clone Table. The Clone Table Assistant appears.
4. In New Table Name, enter Employees_Filtered. Select all the columns and click Add>>. All the columns move from the left side to the right.
5. Click OK. The CONNX Data Dictionary Manager window appears. Employees_Filtered has been added to the CDD and the Table Columns tab contains the SQL Table column information.
6. Click the Table Properties tab. We use the SQL View Clause field to filter data for replication. In this example, we only want the employees who have a phone number with area code 1033. Enter AREA_CODE=1033 in SQL View Clause.
7. Click File -> Save
Employees_Filtered is now a clone of the view of the Employees table. Employees and Employees_Filtered are the same physical table. The Employees view shows the entire table's data; the Employees_Filtered view shows only the records for employees with AREA_CODE=1033. The filter will be applied when queries are applied to the Employees_Filtered table.
8. Close the CONNX Data Dictionary Manager window and start the Replication Administrator. If the Configure Servers window appears, click Cancel. The No Data Dictionary selected window appears.
9. Click File -> OpenCDD. Select Data Filtering.cdd and click Open. The Configure Servers window appears.
10. In CONNX Logon Credentials, enter sample in UserName and leave Password blank. Click Test Connection. The CONNX Integrated Logon window appears.
11. Leave Password blank and click OK.
12. You will be prompted to create a user in the CDD. Click OK. The User ID sample will be created with a blank password.
13. When the connection succeeds, enter localhost in Name/Address and 9200 in Port.
14. Click Done. The Add Tables window appears.
15. Select DBID1.dbo.Employees_Filtered from Select source tables and SQLDatabase from Select Target Databases.
16. Click OK. The main Adabas Event Replicator for LUW window appears.
This completes filtering replication data.
Concatenating Multiple Data Columns
In this example, we will combine the first name, middle initial, and last name fields in the copy of the Employees table into one field (FULL_NAME) so it's easier to read the contents.
1. Using the Employees_Filtered table created in Filtering Replication Data, click Map Columns.
2. Click Add Row. A new row appears at the end of the table description.
Note:
This example illustrates column mapping on a table that does not already exist on the target database. The Add Row, Delete Row, Row Up and Row Down buttons are only available when mapping to a new target table.
If you are mapping to an existing target table and wish to change the structure of that table, you will need to press the Drop Target Table button (available when mapping to an existing table). This will drop the target table and allow you to re-create it with the new structure. Only do this if you intend to physically drop and re-create the target table. All data in the existing table will be lost.
3. In Source Column enter the SQL expression FIRST_NAME + ' ' + MIDDLE_I + ' ' + NAME.
This concatenates the first, last, and middle initial into one field with a space between each name.
a. Enter FULL_NAME in Target Column.
b. Select CHAR in the target column Data Type.
c. Enter 240 in the target column Length.
FULL_NAME's length is 240 because each of the three columns that are being concatenated into FULL_NAME has a character length of 80.
5. Click Done. This completes concatenating multiple data columns.
Excluding Data Columns
In this example, we will exclude all the name data from a replication table so the information can be anonymously analyzed by a demographic program.
1. From the CONNX Data Dictionary Manager window, click the Replication Design tab. Click Add Tables. The Add Tables window appears.
2. Select DBID1.dbo.EMPLOYEES from Select source tables and SQLDatabase from Select Target Databases.
3. Click OK. The main Adabas Event Replicator for LUW window appears. Select the Employees table.
4. Click Map Columns. The Map Columns window appears.
5. Select the Name column.
6. Click Delete Row. A message appears asking if you want to delete a single row. Click Yes.
7. Delete the MIDDLE_I and FIRST_NAME rows the same way you deleted the NAME row.
8. Click Done.
This completes excluding data columns.
Performing Math Operations on Replication Data
In this example, we will calculate the maximum amount of money an employee can contribute to a special savings plan.
1. From the CONNX Data Dictionary Manager window, click the Replication Design tab. Click Add Tables. The Add Tables window appears.
2. Select DBID1.dbo.EMPLOYEES_INCOME from Select source tables and SQLDatabase from Select Target Databases.
3. Click OK. The main Adabas Event Replicator for LUW window appears. Select the EMPLOYEES_INCOME table.
4. Click Map Columns. The Map Columns window appears.
5. Click Add Row to create a new entry in the column map.
a. In Source Column enter SALARY * 0.15. This will calculate 15% of the value in SALARY and place it into a new column in the Target table.
b. Enter SALARY_PERCENT in Target Column.
c. Select NUMERIC in the target column Data Type.
d. Enter 19 in the target column Length.
e. Enter 10 in the target column Prec.
7. Click Done.
This completes performing math operations on replication data.
Using SQL Expressions to Split a Column
In this example, we will use CITY to create a two-letter City name abbreviation.
1. Select the EMPLOYEES table from the main Adabas Event Replicator for LUW window.
2. Click Map Columns. The Map Columns window appears.
3. Click Add Row to create a new entry in the column map.
a. In Source Column, enter LEFT (CITY, 2). This will take the leftmost two characters in CITY and place them into a new column in the Target table.
b. Enter CITY_ABBR in Target Column.
c. Select CHAR in the target column Data Type.
d. Enter 2 in the target column Length.
5. Click Done.
This completes using SQL expressions to split a column.
Renaming a Target Column
In this example, we will rename the MAR_STAT column to Marital_Status, so the column name more clearly reflects its contents.
1. Select the EMPLOYEES table from the main Adabas Event Replicator for LUW window.
2. Click Map Columns. The Map Columns window appears.
a. Enter Martial_Status in the target column MAR_STAT field
b. Select CHAR in the target column Data Type.
c. Enter 1 in the target column Length.
4. Click Done.
This completes renaming a target column.