Running Veridata GoldenGate Parameter Processing

6 Running Veridata GoldenGate Parameter Processing

Veridata GoldenGate Parameter Processing (VGPP) is a command-line tool that uses the map and table parameter of extract and replicat parameter files to create the Oracle GoldenGate Veridata configurations.

This chapter includes the following sections:

6.1 Overview of the Command-Line Interface

An Oracle GoldenGate Veridata parameter file contains all of the information required to extract or apply replicated data. The data-propagated replication is controlled by settings in the Extract and Replicat parameter files. Typically, the Extract parameter file specifies the tables to be replicated, the Replicat parameter file controls column mapping and restricts the tables. The Veridata GoldenGate Parameter Processing command-line utility accepts one or two parameter files as input. One of the files must be a Replicat parameter file, and the other optional file must be an Extract parameter file.

Replication captures information about the transaction responsible for changing the data as well as the actual changed data. However, Oracle GoldenGate Veridata can detect only the current state of the source and target databases, so it cannot support parameters for transactional changes, such as the INSERTDELETES parameter. Veridata GoldenGate Parameter Processing uses the MAP statements from a REPLICAT file to generate compare pairs. Other information in the parameter file is used to determine the relevant MAP statements. Optionally, you can use the TABLE statements from the EXTRACT parameter to restrict the compare pairs that are generated.

Because Oracle GoldenGate Veridata supports only single-column mapping, the column mapping assumes one-to-one mapping between source columns and target columns.

You can:

Reuse Replicat and Extract configurations in Oracle GoldenGate Veridata
Avoid creating separate Replicat and Extract parameter files.

6.2 Running the Veridata GoldenGate Parameter Processing

You can run the Veridata GoldenGate Parameter Processing program if you have the correct operating system permissions.

Ensure that the parameter files you want to use are on the system where the Oracle GoldenGate Veridata is installed. If you need to copy the files from another system, these files should be copied as binary files so that the CHARSET parameter remains valid.
Run the command shell of the operating system.
Navigate to the VERIDATA_DOMAIN_HOME/veridata/bin directory.
Run the Veridata GoldenGate Parameter Processing.

Syntax

veridata_param_process{.bat|.sh} required_parameter [optional_parameter]

Required Parameters

The following are required; otherwise an error is returned.

[-noscripting |
-create |
-replace|
-update ]
[-wlport port ] |
-wluser user_name |
[-p <propfile>]
[-o <outputFile>] 
[replicat_param_filename]

The -wluser option specifies the Oracle GoldenGate Veridata Server (server) user name to connect to the server. This server user should have the veridataCommandLineUser privilege to access and execute command-line operations. The user should also have the veridataAdministrator or veridataPowerUser privilege to successfully run jobs and to use the import and export utilities.

Optional Parameters

The optional parameters are:

[extract_param_filename]: If you provide the Extract and Replicat file names, then provide the Extract file name first, followed by the Replicat file name.

Table 6-1 VGPP Runtime Arguments

Argument	Description
`{ -noscripting \| -create \| -replace \| -update}`	Specifies that `veridata_scripting` is not run with the generated configuration file. The `-create`, `-replace`, and `-update` options indicate that `veridata_scripting` should be run with the generated configuration file. In either case, the generated scripting configuration file can be used as input to the `veridata_scripting`. The default option is `-create.`
-wluser	Specifies the Oracle WebLogic user name that authenticates and connects to the server.
-wlport	Specifies the Oracle WebLogic Server port number. The default listening port is 8830.
`-p`	Specifies a properties file containing additional information required for the configuration.
`-o`	Specifies the output file containing the generated scripting file. The default is `replicat_name_scripting.xml`; `replicat_name` is the value of the `REPLCAT` parameter. The optional `extract_param_filename` parameter specifies an `EXTRACT` parameter file containing source information for the comparison. The `replicat_param_filename` parameter is the `REPLICAT` parameter containing the target information.

6.2.1 Using a Property File

When the VGPP program is run, an optional property file can be specified. This file contains information that is not available in the Oracle GoldenGate parameter file and is required to generate a valid Veridata comparison configuration. The following are some of properties (information) that you can specify.

Table 6-2 Optional Parameters

Property Name	Comments
`source.connection.name`	The name of the Veridata agent/manager connection. This may be the name of an existing Veridata connection. The default is the Extract name. This is the only source connection property needed to reference an existing connection.
`source.connection.port`	The port for the source agent. This is required when the connection does not already exist.
`source.connection.host`	The host name where the source agent is running. This is required when the connection does not exist.
`source.connection.user`	This defaults to the user information in the extract parameter file. This is required when the connection does not exist.
`source.connection.password`	This defaults to the user information in the extract parameter file. This is required when the connection does not already exist. If property name is specified without a value, the scripting utility will prompt for the value when the scripting configuration is loaded into Veridata.
`source.catalog`	This is valid for Sybase, SQL Server, and Oracle consolidated databases. For Sybase and SQL Server, it is the database containing the source tables. For Oracle, it specifies the Oracle PDB to use when processing an Extract parameter. Statements not associated with this PDB are ignored. The default value is the first PDB reference in the file. The reference can be a `SOURCECATALOG` parameter or the first part of a three-part name in a `TABLE` statement.
`extract.useansiquotes`	Indicates whether or not the Extract parameter file follows the ANSI quotation specification. This is a Boolean value. The default value is true. This is the same as the GoldenGate core `GLOBALS` parameters `USEANSISQLQUOTES \| NOUSEANSISQLQUOTES`.
`extract.charset`	The character set for the extract parameter file. This overrides any charset specified in the extract parameter file.
`extract.trail`	The trail file name to use when more than one trail file is specified in an extract parameter file. The default is the first trail file specified in the extract parameter file. When an extract parameter file contains multiple `rmttail` entries with the same name, Veridata will use the first occurrence.
`target.connection.name`	The name of the Veridata agent/manager connection. This may be the name of an existing Veridata connection. The default is the Replicat name. This is the only target connection property required to reference an existing connection.
`target.connection.port`	The port for the target agent. This is required when the connection does not already exist.
`target.connection.host`	The host name where the target agent is running. This is required when the connection does not exist.
`target.connection.user`	This defaults to the user information in the parameter file
`target.connection.password`	This defaults to the user information in the extract parameter file. If property name is specified without a value, the scripting utility prompts for the value when the scripting configuration is loaded into Veridata.
`target.catalog`	This is valid for Sybase and SQL Server. It is the database containing the target tables.
`replicat.useansiquotes`	Indicates whether or not the replicat parameter file follows the ANSI quotation specification. This is Boolean value. The default value is true. This is the same as the GoldenGate core `GLOBALS` parameters `USEANSISQLQUOTES \| NOUSEANSISQLQUOTES`.
`replicat.charset`	The character set for the replicat parameter file. This overrides any `CHARSET` specification in the replicat parameter file.

6.3 Parameter Handling

This section describes the handling of all of the parameters allowed in an Oracle GoldenGate Extract or Replicat parameter file. Each keyword is either supported, unsupported, or ignored. A supported parameter is used to generate the Veridata configuration. An unsupported parameter is something that interferes with the Veridata configuration generation. When an unsupported parameter (INSERTDELETES) is specified, subsequent MAP parameters are ignored. An ignored parameter specifies a feature that is not applicable to Veridata configuration generation.

The following table contains the known parameters and the expected handling: if a parameter is not listed, it is ignored.

Table 6-3 Parameter Handling

GoldenGate Parameters	Veridata Support
`CATALOGEXCLUDE`	This parameter is ignored. Veridata only processes items from a single catalog.
`CHARMAP`	Unsupported.
`CHARSET`	Supported. This parameter is supported in parameter files and include/obey files. Veridata does not process `GLOBALS` files.
`COLMATCH`	Supported.
`COMMENT \| --`	Supported.
`DICTIONARY`	Supported for NSK extract and replicat.
`EXPANDDDL`	Supported for NSK extract and replicat.
`EXCLUDEWILDCARDOBJECTSONLY`	Supported.
`EXTRACT`	Supported.
`EXTTRAIL`	Supported.
`FILE \| TABLE`	Supported for NSK extract.
`INCLUDE`	Supported. When the file is not found by the specified path, VGPP will look for the file name in the same directory as the parameter file.
`INSERTALLRECORDS`	Unsupported. Ignore all subsequent `MAP` statements.
`INSERTDELETES \| NOINSERTDELETES`	Unsupported \| Supported. Ignore all `MAP` statements following an `INSERTDELETES` command until a `NOINSERTDELETES` command is found.
`INSERTUPDATES \| NOINSERTUPDATES`	Unsupported \| Supported: Ignore all `MAP` statements between the `INSERTUPDATES` and the `NOINSERTUPDATES`.
`MACRO`	Supported.
`MACROCHAR`	Supported.
`MAP`	Supported.
`MAPEXCLUDE`	Supported.
`OBEY`	Supported. The same as `INCLUDE`.
`REPLICAT`	Supported.
`RMTTRAIL`	Supported.
`SCHEMAEXCLUDE`	Supported.
`SOURCECATALOG`	Supported for Oracle consolidated databases.
`TABLE \| MAP`	Supported.
`TABLEEXCLUDE`	Supported.
`UPDATEDELETES \| NOUPDATEDELETES`	Unsupported \| Supported.
`UPDATEINSERTS \| NOUPDATEINSERTS`	Unsupported \| Supported.
`USEANSISQLQUOTES \| NOUSEANSISQLQUOTES`	Supported.

6.4 Map and Table Statement Handling

Oracle GoldenGate Veridata generates a compare pair element in the scripting configuration file for each Map statement in the Replicat parameter file. The generated scripting file lists the specific table mappings first, followed by the wildcard mappings, and finally the excluded mappings. This matches the behavior of the Oracle GoldenGate Replicat where specific mappings take precedence over wildcard mappings.

When the same source and target table specification appears in multiple MAP statements, the first occurrence will be used for the compare pair specification. The multiple occurrences can occur when the MAP statements use thread specifications and range filters.

The following table lists all of the keywords for the MAP and TABLE statements and support level in VGPP. MAP statements containing unsupported keywords will not generate a Oracle GoldenGate Veridata comparison configuration. Items marked with maybe indicate that more information is needed in order to determine the value for Oracle GoldenGate Veridata.

Table 6-4 Map and Table Statement Handling

Keyword	Veridata Support
`TARGET`	Supported.
`COLMAP`	Supported. Only simple source column to target column mapping is supported. Target columns mapped to functions or literals is excluded from the comparison configuration. The `USEDEFAULTS` keyword is supported. The `BINARYINPUT` keyword is ignored.
`COLS`	Supported.Results in an explicit column list in the generated compare pair configuration.
`COLSEXCEPT`	Supported. If an explicit column mapping does not exist, this results in system mapped columns with a list of omitted columns.
`COMPARECOLS`	Ignored
`COORDINATED`	Ignored.
`DICTIONARY`	Supported for NSK.
`TARGETDICT`	Supported for NSK.
`DEF`	Supported for NSK.
`TARGETDEF`	Supported for NSK.
`EVENTACTIONS`	Ignored.
`EXCEPTIONSONLY`	Unsupported.
`EXITPARAM`	Ignored.
`FETCHBEFOREFILTER`	Ignored
`FETCHCOLS\|FETCHCOLSEXCEPT`	Ignored
`FETCHMODCOLS\|FETCHMODCOLSEXCEPT`	Ignored
`FILTER`	Ignored.
`GETBEFORECOLS`	Ignored.
`HANDLECOLLISIONS \| NOHANDLECOLLISIONS`	Ignored
`INSERTALLRECORDS`	Unsupported.
`INSERTAPPEND \| NOINSERTAPPEND`	Ignored
`KEYCOLS`	Supported.
`MAPEXCEPTION`	Ignored.
`PARTITION`	Supported. It defines the partition name for the table with or without wildcard symbol and considers only those partition for the compare pair.
`PARTITIONEXCLUDE`	Supported. It defines the partition name for the table with or without wildcard symbol to be excluded for the table. It also considers all other matched partition between source and target table partitions.
`REPERROR`	Ignored
`RESOLVECONFLICT`	Ignored.
`SQLEXEC`	Ignored
`SQLPREDICATE`	Ignored
`THREAD`	Ignored.
`THREADRANGE`	Ignored.
`TOKENS`	Ignored.
`TRIMSPACES \| NOTRIMSPACES`	Supported.
`TRIMVARSPACES \| NOTRIMVARSPACES`	Supported.
`WHERE`	Ignored.

6.5 VGPP Example

The example shows usage of VGPP utility using a simple extract file, replicat file, and property file.

./veridata_param_process.sh -noscripting -wluser veridata -wlport 8830 /scratch/ggcore/dirprm/extract.prm /scratch/ggcore/dirprm/replicat.prm -p properties.txt -o output.xml

The following is an example of including PARTITION and PARTITIONEXCLUDE parameters in the replicat files:

Replicat oraclesource
--SETENV (ORACLE_SID=ORA1110A)
USERID qatarget, PASSWORD qatarget
assumetargetdefs
discardfile ./dirrpt/robey.dsc, purge
map qasource.tcustmer, target qatarget.tcustmer;
MAP sales.order, TARGET rpt.*;
PARTITION sales.tab.part1;
PARTITION sales.order.part2;
PARTITION rpt.order.pa*;
PARTITIONEXCLUDE rpt.order.ts*;

Here: extract.prm: the extract param file. This is optional.

replicat.prm: the replicat param file. This is required.

PARTITION sales.tab.part1: includes Partition part1 from Table tab and schema sales.

PARTITIONEXCLUDE rpt.order.ts*: excludes all partitions starting with ts from table order and schema rp.

If both the extract and replicat files are given, then the extract file should be given before replicat file.

properties.txt- this file contains properties related to veridata agents.

output.xml- this is the generated xml file which can be used with Veridata Scripting Import tool.

PARTITION - It defines the partition name for the table with or without wildcard symbol and considers only those partition for the compare pair.

PARTITIONEXCLUDE: It defines the partition name for the table with or without wildcard symbol to be excluded for the table. It also considers all other matched partition between source and target table partitions.

Sample outputs:

extract.prm

CHARSET utf-8
EXTRACT sqlParamSrcConnection
LOGALLSUPCOLS
RMTHOST localhost, MGRPORT 7000
RMTTRAIL sqlParamSrcConnection_trail
TABLE SOURCE.CHAR_*;

replicat.prm

CHARSET US-ASCII
REPLICAT sqlParamTrgConnection
ASSUMETARGETDEFS
MAP SOURCE.CHAR_TYPES, TARGET TARGET.CHAR_TYPES_NOTNULL, COLMAP ( USEDEFAULTS, NCHAR_COL = NVARCHAR_COL, NVARCHAR_COL = NCHAR_COL, KEY_COL = CHAR_COL, CHAR_COL = KEY_COL ),KEYCOLS (KEY_COL, CHAR_COL);

replicat.prm

Replicat oraclesource--SETENV (ORACLE_SID=ORA1110A)USERID
    qatarget, PASSWORD qatargetassumetargetdefsdiscardfile ./dirrpt/robey.dsc,
    purgemap qasource.tcustmer, target qatarget.tcustmer;MAP sales.order, TARGET
    rpt.*;PARTITION sales.tab.part1;PARTITION sales.order.part2;PARTITION rpt.order.pa*;PARTITIONEXCLUDE rpt.order.ts*;

replicat.prm, which has compare pairs generated with table partitions:

<configuration wildcard="ggs" operation="create" validation="required">
<group name="oraclesource" source-conn="default_source_connection" target-conn="oraclesource">
<compare-pair source-schema="qasource" source-table="tcustmer" target-schema="qatarget" target-table="tcustmer"></compare-pair>
<compare-pair source-schema="sales" source-table="order" target-schema="rpt" target-table="*">
<table-partition name="part2" default="true" side="source"></table-partition>
<table-partition name="pa*" default="true" side="target"></table-partition>
<table-partition name="ts*" default="true" side="target" exclude="true"></table-partition>
</compare-pair>
</group>
<job name="oraclesource">
<group name="oraclesource"></group>
</job>
</configuration>

properties.txt

source.connection.host=localhost
source.connection.name=sqlParamSrcConnection
source.connection.port=7860
source.connection.user=source
source.connection.password=source

target.connection.host=localhost
target.connection.name=sqlParamTrgConnection
target.connection.port=7861
target.connection.user=target
target.connection.password=target

Generated output.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration SYSTEM "configuration.dtd">
<!--
Hostname : localhost
OGGV-30003: Extract filename : /scratch/ggcore/dirprm/extract.prm
OGGV-30002: Replicat filename : /scratch/ggcore/dirprm/replicat.prm 
January 3, 2017 4:33:40 AM PST
-->
<configuration wildcard="ggs" operation="create" validation="required">  
 <connection name="sqlParamSrcConnection" host="localhost" port="7860" user="source" password="source"></connection> 
 <connection name="sqlParamTrgConnection" host="localhost" port="7861" user="target" password="target"></connection>  
 <group name="sqlParamSrcConnection_sqlParamTrgConnection" source-conn="sqlParamSrcConnection" target-conn="sqlParamTrgConnection">   
  <filter type="include" schema="SOURCE" table="CHAR_*"></filter>    
  <compare-pair source-schema="SOURCE" source-table="CHAR_TYPES" target-schema="TARGET" target-table="CHAR_TYPES_NOTNULL">    
  <key-column target-name="KEY_COL" source-name="CHAR_COL"></key-column>      
  <key-column target-name="CHAR_COL" source-name="KEY_COL"></key-column>     
  <column source-name="*" type="hash"></column>     
  <column target-name="NCHAR_COL" source-name="NVARCHAR_COL" type="hash"></column>      
  <column target-name="NVARCHAR_COL" source-name="NCHAR_COL" type="hash"></column>   
 </compare-pair> 
</group>
<job name="sqlParamSrcConnection_sqlParamTrgConnection">    
 <group name="sqlParamSrcConnection_sqlParamTrgConnection"></group> 
 </job>
</configuration>