7.9 Prepare Apply Process

The “prepare” Apply command is used to add a new Apply Process. The “prepare” command can not be used to validate the database connection, and availability of the Trail Files or the presence of the necessary database objects to be used by the Apply Process.

In order to validate that the Apply Process is configured properly and it is able to run on the Target Database, the “validate” command must be used (see chapter 7.12. Validate Apply Process).

The “prepare” command does not enable the Apply Process to write data into the Target Database until the specified Apply Process is run (see chapter 7.15.1 Run any Capture or Apply Processes).

Parameter id is mandatory. All other parameters are optional. All parameters except id can be changed using “alter” Apply Process command (see chapter 7.10 Alter Apply Process for the details).

7.9.1 Prepare Apply Process for MS SQL Server Database

REST API:

• Endpoint: https://repstance_url/configure/process
• Method: POST
• Header:
  • Content-Type: application/json
  • X-Token: token
• Body:
  {
    "command": "prepare",
    "parameters": [
        ["process","apply"],
        ["id","applyID"],
        ["dbtype","mssql"],
        ["capturename","captureName"],
        ["server","databaseHost"],
        ["port","databasePort"],
        ["user","username"],
        ["password","password"],
        ["dbname","databaseName"],
        ["autostart","{0|1}"],
        ["debuglevel","{0-15}"],
        ["repuser","0|1"],
        ["ddlcreate","objectMask:options"],
        ["ddldrop","objectMask:options"],
        ["ddlprocessing","native|dictionary"],
        ["skipapply","skipapplyMask"]
    ]
  }

Server response:

• HTTP Status – status of the command, possible codes are:
  • 200 – if no error occurs
  • 422 – if error occurs
• Body:
  {
        "Status":"{Failed|Completed}",
        "Message":"The command execution details"
  }

CLI Syntax:

The input parameters are:

• process – The only possible value is apply
• id – The Apply Process id
• dbtype – Type of RDBMS, the only possible value is mssql
• capturename – The name of the Capture Process which is providing the data to be used
• server – Host name or IP address of the database server
• port – Database port
• dbname – Database name
• user – Database User name
• password – Database User password
• autostart – Determines if the Apply Process must be run automatically. Possible values are:
  • 0 – do not run the Apply Process automatically (default value)
  • 1 – to run the Apply Process automatically
• debuglevel – The level of debugging. Possible values are 0-15
• repuser – Determines if the Apply Process must represent itself as SQL Server replication process. The possible values are:
  • 0 – do not represent itself as SQL Server replication process (default value)
  • 1 – to represent itself as SQL Server replication process
• ddlcreate – Used to define the Apply Process behavior when creating a table, if a table with the same name is found. The table is specified in schema _name.table_name format. In order to specify the list of the tables, each table must be separated by comma. The % symbol may be used in order to match any number of characters. It has the following options:
• skip– do not run DDL create command if the table already exists
• recreate – to drop the existing table and recreate it
• ddldrop – Used to define the Apply Process behavior when dropping a table, if a table with the same name is not found. The table is specified in schema _name.table_name format. In order to specify the list of the tables, each table must be separated by comma. The % symbol may be used in order to match any number of characters. It has the following option:
  • skip – do not run DDL drop command if the table doesn’t exist
• ddlprocessing – Determines if DDL must be processed based on the user statement or generated based on the dictionary changes. The possible values are:
• native – to process DDL based on the user statement (default value)
• dictionary – to generate DDL based on the dictionary changes
• skipapply – Used to define the behavior of the Apply Process when inserting data, which was originally produced by Apply Processes in the Source Database as part of the “loop-back” control function, in that, this Apply Process will insert, or ignore the data. The Apply Processes to be skipped are specified by ids separated by comma. In order to specify all Processes the “all” value to be used. In order to avoid skipping any Apply Processes the “none” value to be used. The default value is “none”.

Note – "dictionary" value is commonly used in the case of Heterogeneous replication. For Homogeneous type of replication it is recommended to use "native" method of DDL processing.

You should be aware that if the repuser parameter is set to “1” and is invoked, then when delivering data to a SQL Server Target Database, any changes of objects marked as ‘Not for Replication’ (Identity columns, table triggers, foreign keys and check constraints) will be handled in exactly the same way, as they would be produced by an SQL Server replication agent. As an example, any triggers which are created with the “Not for Replication” option will never be fired.

7.9.2 Prepare Apply Process for Oracle Database

REST API:

• Endpoint: https://repstance_url/configure/process
• Method: POST
• Header:
  • Content-Type: application/json
  • X-Token: token
• Body:
  {
    "command": "prepare",
    "parameters": [
       ["process","apply"],
       ["id","applyID"],
       ["dbtype","oracle"],
       ["capturename","captureName"],
       ["connectiontype","tns|ezconnect"],
       ["tnsname","tns_alias"],
       ["server","host_name"],
       ["port","port_number"],
       ["servicename","service_or_SID"],
       ["user","username"],
       ["password","password"],
       ["autostart","{0|1}"],
       ["ddlcreate","objectMask:options"],
       ["ddldrop","objectMask:options"],
       ["ddlprocessing","native|dictionary"],
       ["debuglevel","{0-15}"],
       ["skipapply","skipapplyMask"],
       ["ldtxsize","{0-9999999999}"],
       ["batchmode","{0|1}"],
       ["filter","filterID,filteringClause"],
       ["transformload","{0|1}"]
    ]
  }

Server response:

• HTTP Status – status of the command, possible codes are:
  • 200 – if no error occurs
  • 422 – if error occurs
• Body:
  {
       "Status":"{Failed|Completed}",
       "Message":"The command execution details"
  }

CLI Syntax:

The input parameters are:

• process – The only appropriate value is apply
• id – The Apply Process id
• dbtype – Type of RDBMS, the only possible value is oracle
• connectiontype – Specifies the Oracle connection method, the possible values are:
  • tns – Local Naming Method to be used
  • ezconnect – EZCONNECT to be used
• tnsname – Name of the TNS alias, alid only if connectiontype=tns
• server – Host name or IP address of the database server, valid only if connectiontype=ezconnect
• port – Database port number, valid only if connectiontype=ezconnect
• servicename – Database service name or SID, valid only if connectiontype=ezconnect
• capturename – The name of the Capture Process which is providing the data to be used
• user – Database User name
• password – Database User password
• autostart – Determines if the Apply Process must be run automatically. The possible values are:
  • 0 – do not run the Apply Process automatically
  • 1 – to run the Apply Process automatically
• ddlcreate – Used to define the Apply Process behavior when creating a table, if a table with the same name is found. The table is specified in schema _name.table_name format. In order to specify the list of the tables, each table must be separated by comma. The % symbol may be used in order to match any number of characters. It has the following options:
  • skip – do not run DDL create command if the table already exists
  • recreate – drops the existing table and recreates it
• ddldrop – Used to define the Apply Process behavior when dropping a table, if a table with the same name is not found. The table is specified in schema _name.table_name format. In order to specify the list of the tables, each table must be separated by comma. The % symbol may be used in order to match any number of characters. It has the following options:
  • skip – do not run DDL drop command if the table doesn’t exist
• debuglevel – The level of debugging, possible values are 0-15
• ddlprocessing – Determines if DDL must be processed based on the user statement or generated based on the dictionary changes. The possible values are:
• native – to process DDL based on the user statement (default value)
• dictionary – to generate DDL based on the dictionary changes
• skipapply – Used to define the behavior of the Apply Process when inserting data, which was originally produced by Apply Processes in the Source Database as part of the “loop-back” control function, in that, this Apply Process will insert, or ignore the data. The Apply Processes to be skipped are specified by ids separated by comma. In order to specify all Processes the “all” value to be used. In order to avoid skipping any Apply Processes the “none” value to be used. The default value is “none”.
• ldtxsize – Determines the number of records to be inserted in one transaction at the Initial Load stage. If the value is 0 (default) the table is loaded in a single transaction.
• batchmode – This parameter is to specify transaction processing method. If the parameter is enabled the Apply Process combines and processes set of transactions in a single transaction. The possible values are:
  • 0 – do not apply transactions in batch mode (default)
  • 1 – apply transactions in batch mode
• filter – The set of the parameters to determine objects’ filtering (see chapter 7.9.7 Objects Filtering for the details).
• transformload – Determines if the transformation rules must be applied to the Initial Load. The possible values are:
  • 0 – do not use transformation rules for the Initial Load (default)
  • 1 – use transformation rules for the Initial Load.

Note – "dictionary" value is commonly used in the case of Heterogeneous replication. For Homogeneous type of replication it is recommended to use "native" method of DDL processing.

7.9.3 Prepare Apply Process for PostgreSQL and Aurora PostgreSQL Databases

REST API:

• Endpoint: https://repstance_url/configure/process
• Method: POST
• Header:
  • Content-Type: application/json
  • X-Token: token
• Body:
  {
    "command": "prepare",
    "parameters": [
      ["process","apply"],
      ["id","applyID"],
      ["dbtype","postgresql"],
      ["capturename","captureName"],
      ["server","databaseHost"],
      ["port","databasePort"],
      ["user","username"],
      ["password","password"],
      ["dbname","databaseName"],
      ["autostart","{0|1}"],
      ["debuglevel","{0-15}"],
      ["ddlcreate","objectMask:options"],
      ["ddldrop","objectMask:options"],
      ["ddlprocessing","native|dictionary"],
      ["skipapply","skipapplyMask"]
    ]
  }

Server response:

• HTTP Status – status of the command, possible codes are:
  • 200 – if no error occurs
  • 422 – if error occurs
• Body:
  {
      "Status":"{Failed|Completed}",
      "Message":"The command execution details"
  }

CLI Syntax:

The input parameters are:

• process – The only possible value is apply
• id – The Apply Process id
• dbtype – Type of RDBMS, the only possible value is postgresql
• capturename – The name of the Capture Process which is providing the data to be used
• server – Host name or IP address of the database server
• port – Database port
• dbname – Database name
• user – Database User name
• password – Database User password
• autostart – Determines if the Apply Process must be run automatically, possible values are:
  • 0 – do not run the Apply Process automatically (default value)
  • 1 – to run the Apply Process automatically
• debuglevel – Level of debugging, possible values are 0-15
• ddlcreate – Used to define the Apply Process behavior when creating a table, if a table with the same name is found. The table is specified in schema _name.table_name format. In order to specify the list of the tables, each table must be separated by comma. The % symbol may be used in order to match any number of characters. It has the following options:
• skip – do not run DDL create command if the table already exists
• recreate – drops the existing table and recreates it
• ddldrop – Used to define the Apply Process behavior when dropping a table, if a table with the same name is not found. The table is specified in schema _name.table_name format. In order to specify the list of the tables, each table must be separated by comma. The % symbol may be used in order to match any number of characters. It has the following options:
  • skip – do not run DDL drop command if the table doesn’t exist
• ddlprocessing – Determines if DDL must be processed based on the user statement or generated based on the dictionary changes. The possible values are:
  • native – to process DDL based on the user statement (default value)
  • dictionary – to generate DDL based on the dictionary changes
• skipapply – Used to define the behavior of the Apply Process when inserting data, which was originally produced by Apply Processes in the Source Database as part of the “loop-back” control function, in that, this Apply Process will insert, or ignore the data. The Apply Processes to be skipped are specified by ids separated by comma. In order to specify all Processes the “all” value to be used. In order to avoid skipping any Apply Processes the “none” value to be used. The default value is “none”.

Note – "dictionary" value is commonly used in the case of Heterogeneous replication. For Homogeneous type of replication it is recommended to use "native" method of DDL processing.

7.9.4 Prepare Apply Process for MySQL and Aurora MySQL Databases

REST API:

• Endpoint: https://repstance_url/configure/process
• Method: POST
• Header:
  • Content-Type: application/json
  • X-Token: token
• Body:
  {
    "command": "prepare",
    "parameters": [
      ["process","apply"],
      ["id","applyID"],
      ["dbtype","mysql"],
      ["capturename","captureName"],
      ["server","databaseHost"],
      ["port","databasePort"],
      ["user","username"],
      ["password","password"],
      ["dbname","databaseName"],
      ["autostart","{0|1}"],
      ["debuglevel","{0-15}"],
      ["ddlcreate","objectMask:options"],
      ["ddldrop","objectMask:options"],
      ["ddlprocessing","native|dictionary"],
      ["skipapply","skipapplyMask"]
    ]
  }

Server response:

• HTTP Status – status of the command, possible codes are:
  • 200 – if no error occurs
  • 422 – if error occurs
• Body:
  {
      "Status":"{Failed|Completed}",
      "Message":"The command execution details"
  }

CLI Syntax:

The input parameters are:

• process – The only possible value is apply
• id – The Apply Process identifier
• dbtype – Type of RDBMS, the only possible value is mysql
• capturename – The name of the Capture Process which is providing the data to be used
• server – Host name or IP address of the database server
• port – Database port
• dbname – Database name
• user – Database User name
• password – Database User password
• autostart – Determines if the Apply Process must be run automatically. Possible values are:
  • 0 – do not run the Apply Process automatically (default value)
  • 1 – to run the Apply Process automatically
• debuglevel – Level of debugging, possible values are 0-15
• ddlcreate – Used to define the Apply Process behavior when creating a table, if a table with the same name is found. The table is specified in schema _name.table_name format. In order to specify the list of the tables, each table must be separated by comma. The % symbol may be used in order to match any number of characters. It has the following options:
  • skip – do not run DDL create command if the table already exists
  • recreate – drops the existing table and recreates it
• ddldrop – Used to define the Apply Process behavior when dropping a table, if a table with the same name is not found. The table is specified in schema _name.table_name format. In order to specify the list of the tables, each table must be separated by comma. The % symbol may be used in order to match any number of characters. It has the following options:
  • skip – do not run DDL drop command if the table doesn’t exist
• ddlprocessing – Determines if DDL must be processed based on the user statement or generated based on the dictionary changes. The possible values are:
  • native – to process DDL based on the user statement (default value)
  • dictionary – to generate DDL based on the dictionary changes
• skipapply – Used to define the behavior of the Apply Process when inserting data, which was originally produced by Apply Processes in the Source Database as part of the “loop-back” control function, in that, this Apply Process will insert, or ignore the data. The Apply Processes to be skipped are specified by ids separated by comma. In order to specify all Processes the “all” value to be used. In order to avoid skipping any Apply Processes the “none” value to be used. The default value is “none”.

Note – "dictionary" value is commonly used in the case of Heterogeneous replication. For Homogeneous type of replication it is recommended to use "native" method of DDL processing.

7.9.6 Prepare Apply Process for Snowflake

REST API:

• Endpoint: https://repstance_url/configure/process
• Method: POST
• Header:
  • Content-Type: application/json
  • X-Token: token
• Body:
  {
    "command": "prepare",
    "parameters": [
      ["process","apply"],
      ["id","applyID"],
      ["dbtype","snowflake"],
      ["capturename","captureName"],
      ["account","account"],
      ["region","region"],
      ["warehouse","warehouse"],
      ["user","username"],
      ["password","password"],
      ["dbname","databaseName"],
      ["autostart","{0|1}"],
      ["debuglevel","{0-15}"],
      ["ddlcreate","objectMask:options"],
      ["ddldrop","objectMask:options"],
      ["ddlprocessing","native|dictionary"],
      ["skipapply","skipapplyMask"],
      ["ldtxsize","{0-9999999999}"],
      [“filter”,”filterID,filteringClause”],
      ["transformload","{0|1}"]
    ]
  }

Server response:

• HTTP Status – status of the command, possible codes are:
  • 200 – if no error occurs
  • 422 – if error occurs
• Body:
  {
      "Status":"{Failed|Completed}",
      "Message":"The command execution details"
  }

CLI Syntax:

The input parameters are:

• process – The only possible value is apply
• id – The Apply Process identifier
• dbtype – Type of RDBMS, the only possible value is snowflake
• capturename – The name of the Capture Process which is providing the data to be used
• account – Snowflake Account name
• region – Snowflake Region name
• warehouse – Warehouse name
• dbname – Database name
• user – Database User name
• password – Database User password
• autostart – Determines if the Apply Process must be run automatically. Possible values are:
  • 0 – do not run the Apply Process automatically (default value)
  • 1 – to run the Apply Process automatically
• debuglevel – Level of debugging, possible values are 0-15
• ddlcreate – Used to define the Apply Process behavior when creating a table, if a table with the same name is found. The table is specified in schema _name.table_name format. In order to specify the list of the tables, each table must be separated by comma. The % symbol may be used in order to match any number of characters. It has the following options:
  • skip – do not run DDL create command if the table already exists
  • recreate – drops the existing table and recreates it
• ddldrop – Used to define the Apply Process behavior when dropping a table, if a table with the same name is not found. The table is specified in schema _name.table_name format. In order to specify the list of the tables, each table must be separated by comma. The % symbol may be used in order to match any number of characters. It has the following options:
  • skip – do not run DDL drop command if the table doesn’t exist
• ddlprocessing – Determines if DDL must be processed based on the user statement or generated based on the dictionary changes. The possible values are:
  • native – to process DDL based on the user statement (default value)
  • dictionary – to generate DDL based on the dictionary changes
• skipapply – Used to define the behavior of the Apply Process when inserting data, which was originally produced by Apply Processes in the Source Database as part of the “loop-back” control function, in that, this Apply Process will insert, or ignore the data. The Apply Processes to be skipped are specified by ids separated by comma. In order to specify all Processes the “all” value to be used. In order to avoid skipping any Apply Processes the “none” value to be used. The default value is “none”.
• ldtxsize – Determines the number of records to be inserted in one transaction at the Initial Load stage. If the value is 0 (default) the table is loaded in a single transaction.
• filter – The set of the parameters to determine objects’ filtering (see chapter 7.9.7 Objects Filtering for the details).
• transformload – Determines if the transformation rules must be applied to the Initial Load. The possible values are:
  • 0 – do not use transformation rules for the Initial Load (default)
  • 1 – use transformation rules for the Initial Load.

How to find “Snowflake Account” and “Snowflake Region” is described in chapter 7.1.7 Prepare Snowflake as Target Database.

Note – "dictionary" value is commonly used in the case of Heterogeneous replication. For Homogeneous type of replication it is recommended to use "native" method of DDL processing.

7.9.7 Objects Filtering

Filters are used by Apply Process to filter out data while processing DML operations. The filter should be configured for either single table or group of the tables and at least for one DML operation. The filtering statement might also be provided, which is to be used to construct “where” condition of the DML statement. If the filtering statement is not provided, then the Apply Process will ignore this DML operation.

The syntax used is:

where:

• id – The id, which is unique to each Filter has the purpose of determining the order in which the Filter is used by the Apply Process.
• table_mask – It is used to specify list of the tables. The format is schema _name.table_name and the % symbol may be used in order to match any number of characters.
• dml_operations – List of the DML operations separated by comma. The valid DML operations are: INSERT, UPDATE,DELETE.
• filter_statement – The statement to be used to build the “where” clause of DML operation.

The following examples show various ways of defining the Filter parameters:

• Skip any “delete” operations for “logs” table in “scott” schema:

• Skip any “delete” operations for the dbo.orders table if processed column equal to “1”:

• Skip any DML operations for thedbo.emp table if the info column has “do_not_replicate” value:

• Do not insert record into the dbo.acct table if the record with the same id already exists: