Create Task

In order to compare data you need to create comparison task and specify tables to be verified along with the verification rules. You can create task using  repcli or via REST API or Web UI.

Create task using “repcli”

The "compare add" command is used to create the task:

The input parameters are:

• capture - id of the capture process
• tables - criteria for the tables to be compared
• deep - The parameter is to specify data validation percentage. Possible values are 1-100. In order to exclude table from validation the "-1" value is used. The default value is 10.
• chunk - To compare data Repstance breaks down data into chunks and validate given percentage of data within a chunk. This parameter is to specify number of records to be processes in one chunk. Default value is 10000
• excludecols - comma separated list of the columns to be ignored during the validation
• excludetypes - comma separated list of the data types to be ignored during the validation
• mapcols - Used to specify a user-defined function to transform a column value when comparing. The format is "SQL_FUNCTION(%COLUMN_NAME%)". The parameter can be provided multiple times
• maptypes - Used to specify a user-defined function to transform a column value of the given data type when comparing. The format is "SQL_FUNCTION(%DATA_TYPE%)". The parameter can be provided multiple times
• where - Used to specify additional "where" condition for the data to be compared
• keys - comma separated list of the columns to be used as the key of the tables instead of their primary key or unique index, for example to compare a table that has none. The columns are searched by name in each table selected by the “tables” parameter; a table that has none of them is skipped. The data repair (the mode 3 of the “compare run” command, see Start/Stop Task) merges the rows by the primary key or unique index of the table, so do not repair a table that has none
• taskname - Name of the task. If the parameter is not specified the task will be created with "default" name
• preview - When this parameter is enabled the details of the tables that are affected by the configuration will be provided but the task configuration will not be applied. Possible values are -
  • 0 - do not enable preview mode
  • 1 - enable preview mode

NOTE: if the “add” command if executed for the existing task it will overwrite the objects along with the validation rules

Examples

The following example shows how to create the "TASK1" task for the capture=1 to compare 5% data for all tables in the dbo schema:

The following command modifies the “TASK1” task to  compare "employees" table without the "img" column:

To ignore any "TEXT" and "NTEXT" data types columns:

Configure the EMP_NAME column to be compared in uppercase:

Compare only first 100 characters for all columns of the "VARCHAR" type:

REST API

• Endpoint: https://repstance_url/compare/add
• Method: POST
• Header:
  • Content-Type: application/json
  • X-Token: token
• Body:
  {
    "capture_id":id_of_the_capture_process,
    "name":"name_of_the_task",
     "data":[
         {
          "tables":"tables_specification",
          "deep":data_validation_percentage,
          "chunk":chunk_size,
          "excludecols":"columns_to_ignore",
          "excludetypes":"data_type_to_ignore",
          "mapcols":["custom_validation_criteria_for_column1", "custom_validation_criteria_for_column2", …],
          "maptypes":["custom_validation_criteria_for_datatype1", "custom_validation_criteria_for_datatype2", …],
          "where":"filter_condition",
          "keys":"table_key_columns"
         }
       ],
        "preview":true|false,
        "showcols":true|false
    }
   

The “showcols” element is optional. When it is true, the response contains the details of the columns of every selected table: the data type, the key flag, the comparison function and the flag of a column that is excluded from the comparison.

Web UI

In the Web UI tasks are created in the “Compare” window of the Capture Process. Click “Compare” on the card of the Capture Process on the dashboard:

Click “+ Add” at the right end of the tabs (“Add New Task” if the Capture Process has no task yet) and provide the name of the task. Provide the search conditions for the tables and click “Show results”:

For each table in the list you can provide custom settings such as the validation percentage and the size of the chunk. “Show additional parameters” sets the data types to be ignored and the validation functions for all the tables at once, and under the arrow of a table you can exclude columns, change the key and add a validation function to a column:

Click “Save Changes” to save the task. It appears as a new tab of the “Compare” window. To change a task later select its tab and click “Edit”. All the steps and settings are described in Task Configuration, the principles of the comparison are explained in Compare Data.