5 Configuration Scripting

In addition to using the Oracle GoldenGate Veridata Web User Interface, you can use scripting to define portions of your configuration. This chapter includes the following topics:

Introduction to Scripting
Elements

5.1 Introduction to Scripting

With scripting you can create XML documents that are used to configure Oracle GoldenGate Veridata. The DTD (Document Type Definition) that governs these XML documents is stored in the web/bin directory of the Oracle GoldenGate Veridata installation location. You can refer to the online help for detail on the input since the DTD entries mostly mirror the Oracle GoldenGate Veridata Web User Interface.

This documentation assumes that the user has a knowledge of basic XML and its rules.

This scripting has the following advantages:

It can reduce the time needed to define repetitive tasks
It allows you to create reusable configurations
It can ensure that your test configuration mirrors the one you use for production

5.1.1 Supported Configurations

Oracle GoldenGate Veridata scripting supports configuring:

Database connections
Comparison groups (jobs, groups, and compare pairs)

Oracle GoldenGate Veridata scripting does not support configuring:

Profiles
Overriding data type formats on connections

5.1.2 Running the Scripting Utility

The scripting utility runs from the DOMAIN_HOME/veridata/bin directory of the Oracle GoldenGate Veridata installation location. The Windows version is named veridata_scripting.bat and the UNIX and Linux is veridata_scripting.sh.

The syntax for running the script on Windows is:

veridata_scripting.bat [-create | -update | -delete] configuration.xml

One of these optional operations can be requested at run time:
- -create: All items listed in the configuration are new. If any item in the list exists in the repository, nothing is added.
- -update: New items are added to the repository and existing items are modified. Items existing in the repository and not listed in the configuration are deleted.
- -delete: All named items that exist for the configuration are removed from the repository.
configuration.xml: The name of scripting XML configuration file that you created to describe the configuration. This is a required entry.

The scripting utility returns an exit status of 0 if successful and non-zero if an error was encountered. More information on the errors can be found in the shared logs/scripting.log file in the installation location.

5.1.3 Processing the Configuration

The scripting utility first parses the configuration.xml file attempting to complete the entire file before aborting due to the errors. Any errors it finds are logged in the shared logs/scripting.log. If it does not abort because of errors, it makes a second parsing pass, this time processing the configuration.

Matching Object Names

Database object names, such as catalogs, schema, tables, indexes, and columns will be matched according to these rules:

The matching is case insensitive
The hypen (-) is considered a match to the underscore (_) to support matching Enscribe DDL and SQL columns
Wildcard expressions for table names and source column names match against the exact name and against the uppercase version of the name.

Determining Key Columns

The key columns are selected in the following order:

Explicit key column definitions if they are available. In this case if source-pkey and target-pkey compare-pair element attributes are set it will generate an error.
Columns in the index specified by source-pkey and target-pkey attributes of the compare-pair element. The number of columns and all data types must match and the data types must be compatible.
Columns in the system-selected primary key.

Generating Compare Pairs

Compare pair generation has the following characteristics:

Generating from wild cards works the same as the user interface generation except that regular expressions can be used
Compare pairs are processed in the order specified in the configuration.xml file
The compare pairs generated by a single compare pair element are generated in alphabetical order of the source table name
When compare pairs are generated by more than one compare pair element, the first one will be used

As a general rule, the order of the compare pair elements should be:

Compare pairs with specialized configuration requirements, such as user-defined keys
Compare pairs that match general patterns
Exclusions of compare pairs that would otherwise match general patterns

5.2 Elements

The configuration is defined by the top level configuration element and several nested elements. Most of these elements have attributes that define their characteristics, such as the operation attribute for the configuration element or the port attribute for the connection element.

CHANGE THE ELEMENTS TO BRIDGEHEAD?

5.2.1 configuration

The main element is configuration.

The following elements can be nested within the configuration element:

Table 5-1 configuration Elements

Elements	Description
connection	One or more Oracle GoldenGate Veridata Web User Interface database connection definitions.
group	One or more comparison group definitions.
job	One or more job definitions.

The following attributes describe the configuration element:

Table 5-2 configuration Attributes

Attribute Description

Attribute	Description
validation	Specifies the type of validation that is used for the configuration. The options are: "`required`" - All compare pairs must be successfully validated before any pairs are added to the repostiory. This is the default value. "`omit-failures`" - Successfully validated compare pairs are added to the repostiroy and compare pairs that cannot be validated are ignored. "`none`" - Compare pairs are added to the repository without any validataion. If this option is selected, the Oracle GoldenGate Veridata Web User Interface should be used to review and fix validation problems.
operation	Specifies how data is applied to the repository. The options are: "`create`" - All items listed in the configuration are new. If any item in the list exists in the repository, nothing is added. This can be used to prevent unintended modification to existing repository items. This is the default value. "`update`" - New items are added to the repository and existing items modified. Items existing in the repository and not listed in the configuration are deleted. "`delete`" - All named items in the configuration are removed from the repository. You can use a command line flag to override the value entered for this attribute.
wildcard	Specifies the pattern matching method that is used. The options are: "`ggs`" - Use the typical Oracle GoldenGate pattern using an asterisk (*). See the Oracle GoldenGate Veridata Web User Interface help for details on this type of matching. This is the default value. "`regex`" - Use regular expressions for matching.

validation

Specifies the type of validation that is used for the configuration. The options are:

"required" - All compare pairs must be successfully validated before any pairs are added to the repostiory. This is the default value.

"omit-failures" - Successfully validated compare pairs are added to the repostiroy and compare pairs that cannot be validated are ignored.

"none" - Compare pairs are added to the repository without any validataion. If this option is selected, the Oracle GoldenGate Veridata Web User Interface should be used to review and fix validation problems.

operation

Specifies how data is applied to the repository. The options are:

"create" - All items listed in the configuration are new. If any item in the list exists in the repository, nothing is added. This can be used to prevent unintended modification to existing repository items. This is the default value.

"update" - New items are added to the repository and existing items modified. Items existing in the repository and not listed in the configuration are deleted.

"delete" - All named items in the configuration are removed from the repository.

You can use a command line flag to override the value entered for this attribute.

wildcard

Specifies the pattern matching method that is used. The options are:

"ggs" - Use the typical Oracle GoldenGate pattern using an asterisk (*). See the Oracle GoldenGate Veridata Web User Interface help for details on this type of matching. This is the default value.

"regex" - Use regular expressions for matching.

Example

The following example adds compare pairs that can be validated and ignores those that cannot; uses regular expressions for wild carding; and uses the "create" default to adds all items as new items, adding nothing if any item already exists.

<configuration validation="omit-failures" wildcard="regex">
.
.
.
</configuration>

5.2.2 column

The column element defines a set of columns to be included or excluded from the compare pair. The column element has no nested elements or text data.

The following attributes describe the column element:

Table 5-3

Attribute	Description
source-name	A regular expression that defines a set of source column names. This value is required.
target-name	A regular expression that defines a set of target column names. It can include references to groups captured by the `source-name` expression.
exclude	Indicates whether or not the matched columns should be excluded from the compare pair. The options are: `"true"` - The matched columns should be excluded. `"false"` - The matched columns should be included. This is the default.
type	Indicates the type of the column. The options are: `"key"` - The column is used as a key. `"hash"` - The column is compared using a hash value. This is the default value. `"literal"`- The column is a literal value.
format	Specifies a format to override the comparison format that would normally be used. QUESTION: okay? The values can be any of the data types supported by Oracle GoldenGate Veridata.
scale	Specifies a scale to override the default scale for the comparison.
precision	Specifies a precision to override the default precision used for the comparison.
timezone	Specifies a timezone to override the default timezone of the comparison.

5.2.3 compare-pair

The compare-pair element specifies a set of compare pair items. As in the Oracle GoldenGate Veridata Web User Interface, the compare pairs default to system mapped keys and columns.

The following elements can be nested within the compare-pair element:

Table 5-4 compare-pair Elements

Element	Description
enscribe-info	One or more sets of information used when comparing NonStop Enscribe files.
partition	One or more specifications of a subset of tables.
column	One or more definitions of a set of columns to be included or excluded.

The following attributes describe the compare-pair element:

Table 5-5 compare-pair Attributes

Attribute	Element
name	An expression defining the name of the compare pair. This expression can include groups captured with `source-table` expressions and target table group `$0`.
source-table	A regular expression that defines the table or tables to be compared. See "Regular Expression Grouping" later in this section for more detail. The default is to match all tables.
target-table	A regular expression that defines the target tables for the comparison. This may contain references to groups captured by the source table expression. The default is $0 for the full source table name.
source-schema	The name of the default schema for the source tables referenced for the compare pair. QUESTION: SHOULD THE FOLLOWING BE INCLUDED FOR THE GROUP DEF OF SOURCE-SCHEMA? The default is the value specified for the `group`. For SQL/MP, this is the subvolume of the SQL catalog. This is not used with Enscribe files.
target-schema	The name of the default schema for the target tables referenced for the compare pair. The default is the value specified for the `group`. For SQL/MP, this is the subvolume of the SQL catalog. This is not used with Enscribe files.
source-catalog	The default catalog for the source tables referenced in this compare pair. QUESTION (AS ABOVE) For SQL/MP, this is the volume of the SQL catalog. This is not used for Oracle, DB2, Enscribe, or Teradata.
target-catalog	TThe default catalog for the source tables referenced in this compare pair. For SQL/MP, this is the volume of the SQL catalog. This is not used for Oracle, DB2, Enscribe, or Teradata.
exclude	Indicates whether or not the compare pair should be included in the `group` element. This can be used to remove a compare pair generated by an earlier compare pair element. The options are: `"true"` - Exclude the compare pair. `"false"` - Include the compare pair. This is the default.
source-file-pattern	The default file pattern for the source if the data source is Enscribe or SQL/MP.
target-file-pattern	The default file pattern for the target if the data target is Enscribe or SQL/MP.
source-pkey	The name of the unique index to use as the source portion of the user-specified primary key. The default is no user-specified index name.
target-pkey	The name of the unique index to use as the target portion of the user-specified primary key. The default is the value of the `source-pkey`.
delta-processing	Indicates whether or not delta processing is enabled for this compare pair. The options are: `"true"` - delta processing is enabled. `"false"` - delta processing is not enabled. This is the default.
profile-name	The name of the profile to use when running the compare-pair comparison.
system-key	If the compare pair has no column elements and no specified source-pkey, Oracle GoldenGate Veridata will select the most appropriate primary key or unique index to use. The options are: "`true"` - Oracle GoldenGate Veridata selects the key if it is not defined. This is the default. `"false"` - Oracle GoldenGate Veridata does not select the key.
system-columns	Indicates that the compare pair contains column elements with the type attribute set to `key`, so the generated compare pair will have user-defined columns for the key. The options are: `"true"` - Compare pair has `key` column elements. This is the default. `"false"` - Compare pair does not have `key` column elements.
wildcard	Specifies the pattern matching method that is used. The options are: "`ggs`" - Use the typical Oracle GoldenGate pattern that matches an asterisk (*) to any number of characters. "`regex`" - Use regular expressions for matching. `"default"` - Use the setting for the `configuration`. This is the default.

Regular Expression Grouping

Regular expression grouping can be used to capture the parts of the source table names to be used for matching the target table name. Groups to be matched are referenced as $1, $2, $3 and so on. Group $0 matches the entire source table name.

Examples of matching groups include:

P(.*) - Matches table names that begin with P. It captures the variable portion in $1. This matches table PROSPECTS. (QUESTION: why variable portion captured here and initial letter for the third bullet point?)
[^PV].* - Matches table names that do not begin with P or V. This does not match the table PROSPECTS, but does match the table REGIONS.
([P-R](.*)) - Matches table names starting with P, Q, or R and captures the initial letter in group $1 and the rest of the name in group $2.

Captured groups ($n) are then used in expressions for selecting the target tables.

Example

QUESTION: HOW TO DEFINE THIS EXAMPLE? SHOULD IT BE AN EXAMPLE THAT IS NOT IN THE SAMPLE FILE? The following example describes the key-only compare-pair. Its source tables are defined in the test schema and target tables in the other schema. It will exclude pairs where the source column name begins with S and the target column name begins with T and otherwuse matches the source column name.

.
.
.
<compare-pair name="key-only" source-schema=test target-schema=other
   source-table="S(.*)" target-table=T$1">
   <column source-name="(.*)" target-name="$1" exclude="true"/>
</compare-pair>
.
.
.

5.2.4 connection

The connection element defines a connection to a source or target comparison database through an Oracle GoldenGate Veridata agent.

The following element can be nested within the connection element :

Table 5-6 connection Elements

Element	Description
description	Provides a description of the connection.

The following attributes describe the connection element:

Table 5-7 connection Attributes

Attribute	Description
name	A name that identifies the connection. This is a required entry.
host	The name of the system on which the Oracle GoldenGate Veridata agent is running.
port	The port number of the system on which the agent is running.
user	The user name the agent uses to connect to the database.
password	The password the agent uses to connect ot the database.
agent-timeout	The amount of time Oracle GoldenGate Veridata will wait before timing out when sending requests to the agent. WE MAY DELETE THIS ONE. QUESTION: Is this correct? what time interval, seconds?
truncate-spaces	Either `"true"` or `"false"` to indicate whether or not spaces will be removed from the end of character columns. The default is `"true"` to truncate spaces.
fetch-size	(Oracle only) The number of rows fetched in each batch.

Example

The following example identifies the connection named source.

<configuration>
   <connection name="source" host="somehost"
    port="7850" user="somename" password="somepw"/>
.
.
.
</configuration>

5.2.5 description

The description element is free-form text that can be used to attach a description to the containing element. It has no associated attributes.

Example

The following example provides a description for the connection named source.

<configuration>
   <connection name="source" host="somehost"
    port="7850" user="somename" password="somepw"
      <description>
         <![CDATA[
           This connection is used when the Veridata agent connects
           to the source.
         ]]>
      </description>
   </connection>
.
.
.
</configuration>

5.2.6 enscribe-info

The enscribe-info element provides additional information used to compare NonStop Enscribe records at the field level.

The following elements can be nested within the enscribe-info element:

Table 5-8 enscribe-info Elements

Element	Description
expandddl	Describes the rules that are used when applying the DDL.

The following attributes describe the enscribe-info element:

Table 5-9 enscribe-info Attributes

Attribute Description

Attribute	Description
`side`	Indicates whether the information applies to the source or the target table. The options are: `"source"` to specify the source table. This is the default. `"target"` to specify the target table.
dictionary	The volume and subvolume containing the data dictionary.
record	The name of the record in the data dictionary.

side

Indicates whether the information applies to the source or the target table. The options are:

"source" to specify the source table. This is the default.

"target" to specify the target table.

dictionary

The volume and subvolume containing the data dictionary.

record

The name of the record in the data dictionary.

5.2.7 enscribe-key

The enscribe-key element defines the key that is to be used for Enscribe files. The enscribe-key element has no nested elements or text data.

The following attributes describe the enscribe-key:

Table 5-10 enscribe-key Attributes

Attribute Description

Attribute	Description
start-key	The key that is to be used to begin reading the Enscribe file. This is a required entry.
end-key	The key of the last Enscribe record that should be read. This is a required entry.
format	Specifies the format of the Enscribe key. The options are: `"ascii"` - The format of the key is ASCII. This is the default. `"hexadecimal"` - The format of the key is hexadecimal.

start-key

The key that is to be used to begin reading the Enscribe file. This is a required entry.

end-key

The key of the last Enscribe record that should be read. This is a required entry.

format

Specifies the format of the Enscribe key. The options are:

"ascii" - The format of the key is ASCII. This is the default.

"hexadecimal" - The format of the key is hexadecimal.

5.2.8 expandddl

The expandddl element describes the rules used when applying the DDL.

The following attributes describe the expandddl element:

Table 5-11 expandddl Attributes

Attribute	Description
expandGroupArrays	Whether or not to expand group arrays. The options are: `"true"` to expand the array. This is the default. `"false"` not to expand the array.
redefined-columns	Whether or not to include redefined columns. The options are: `"include"` - Includes redefined columns `"omit"` - Leaves out redefined columns. This is the default.
resolvedups	Specifies how to resolve duplicates that result when the array is expanded. The options are: `"appendIndex"` - Adds a unique numeric index to the end of the duplicate. This is the default. `"appendAlphaIndex"` - Adds an alpha character index to the end of the duplicate. `"prependGroup"` - Prefixes the name of the array group to the duplicate.
ddl-separator	The character separator for defining array output into columns. An example is the dash used in FIELDX-3, which is the third occurance of `FIELDX` in the array. The options are: `"none"` - There is no separator. This is the default. `"dash"` - Use a dash (-) as the separator. `"bracket"` - Use brackets [] as the separator. `"underscore"` - Use underscore (_) as the separator. `"double-underscore"` - Use double underscore (__) as the separator.
zero-fill-length	Prepends zeros to adjust the number of the occurance. The value is the number of digits enclosed in quotation marks. "0" is the default.
fix-long-names	Whether to fix the names that result from resolving duplicates if they exceed the `max-col-name-length`. The options are: `"true"` - Fix the names that exceed the maximum. This is the default. `"false"` - Do not change the names that exceed the maximum.
max-col-name-length	The maximum length allowed for a column name. The entry is a number within quotation marks. The default is `"120"`.

5.2.9 group

The group element defines a set of compare pairs that all have the same source and target database connections. These compare pairs also have other properties in common.

The following elements can be nested within the group element.:

Table 5-12 Group Elements

Element	Description
description	Provides a description of the group.
partition	Defines an Oracle GoldenGate Veridata partition that will be applied to all the compare pairs in the group. The parition specifies subsets of the data.
compare-pair	Defines one or more compare pairs. The `compare-pair` elements are added to the group in the order they are specified. If the same compare pair fits the criteria of another specification in the group, the first compare pair will be used.

The following attributes describe the group element:

Table 5-13 Group Attributes

Attribute	Description
name	A name that identifies the group. This value is required.
source-conn	The name of the connection to the source database. This can reference an existing connection in the Oracle GoldenGate Veridata repository or a connection previously defined in this configuration. This attribute is required if it references an existing connection in the repository.
target-conn	The name of the connection to the target database. This can reference an existing connection in the Oracle GoldenGate Veridata repository or a connection previously defined in this configuration. This attribute is required if it references an existing connection in the repository.
source-schema	The name of the default schema for the source tables referenced in the compare pairs that make up the group.
target-schema	The name of the default schema for the target tables referenced in the compare pairs that make up the group.
`source-catalog`	The default catalog for the source tables referenced in this group.
`target-catalog`	The default catalog for the target tables referenced in this group.
`validation`	Specifies the type of validation that will be used for the configurations. The options are: "`required`" - All compare pairs must be successfully validated before any pairs are added to the repostiory. "`omit-failures`" - Successfully validated compare pairs are added to the repository and compare pairs that cannot be validated are ignored. "`none`" - Compare pairs are added to the repository without any validataion. If this option is selected the Oracle GoldenGate Veridata Web User Interface should be used to review and fix validation problems. `"default"` - Use the type of validation specified for a higher level, such as the `configuration` element. This is the default.
`source-file-pattern`	The default file pattern for the source if the data source is Enscribe or SQL/MP.
`target-file-pattern`	The default file pattern for the target if the data target is Enscribe or SQL/MP.

Example

QUESTION: WHAT SHOULD WE DEFINE FOR THE GROUP?

<group name="weekly-tables" source-conn="source" target-conn"="target">
   <description>
   .
    .
    .
   </description>
   <partition>
    .
    .
    .
   </partition>
   <compare-pair>
    .
    .
    .
</compare-pair>
</group>

5.2.10 job

The job element defines an Oracle GoldenGate Veridata comparison job.

The following elements can be nested within the job element:

Table 5-14 job Elements

Element	Description
description	Provides a description of the job.
group	The name of the group associated with the job. This can be a new group or a previously defined group.

The following attributes describe the job element:

Table 5-15 job Attributes

Attribute	Description
name	A name that identifies the job. This is a required attribute.
source-conn	The name of the connection to the source database. This can reference an existing connection in the Oracle GoldenGate Veridata repository or a connection previously defined in this configuration. This attribute is required if it references an existing connection in the repository. The job `source-conn` is used to override the source connection spsecified for the groups included in the job.
target-conn	The name of the connection to the target database. This can reference an existing connection in the Oracle GoldenGate Veridata repository or a connection previously defined in this configuration. This attribute is used to override the target connection for the groups included in the job.
profile	The default profile to use when running the job.

Example

QUESTION:

<job name="all-groups" profile="server-sort">
    <group name="all-tables"/>
    <group name="selected-tables"/>
</job>

5.2.11 partition

The partition element defines a subset of the tables that will be compared.

The following elements can be nested within the partition element:

Table 5-16 partition Elements

Element	Description
`query`	A Boolean SQL expression used in the where clause applied to the initial comparison query for the source and target.
`source-query`	A Boolean SQL expression used in the where clause applied to the initial comparison query for the source.
`target-query`	A Boolean SQL expression used in the where clause applied to the initial comparison query for the target.
`enscribe-key`	An Enscribe key value to be applied to the source and target files when comparing Enscribe files.
`source-enscribe-key`	An Enscribe key value to be applied to the source file when comparing Enscribe files.
`target-enscribe-key`	An Enscribe key value to be applied to the target file when comparing Enscribe files.

The following attributes describe the partition element:

Table 5-17 partition Attributes

Attribute Description

Attribute	Description
name	A name that identifies the partition. This is a required attribute.
type	Indicates the type of selection to be used. The options are: `"sql"` - Specifies a SQL expression.This is the default. `"enscribe"` - Specifies an Enscribe key specification.
default	Indicates whether this is the default partition that will be automatically applied to the compare pair at run time. It corresponds to the "use at run time" option in the Oracle GoldenGate Veridata Web User Interface. The options are: "true" - This is the default partition. "false" - This is not the default partition. This is the default value.

name

A name that identifies the partition. This is a required attribute.

type

Indicates the type of selection to be used. The options are:

"sql" - Specifies a SQL expression.This is the default.

"enscribe" - Specifies an Enscribe key specification.

default

Indicates whether this is the default partition that will be automatically applied to the compare pair at run time. It corresponds to the "use at run time" option in the Oracle GoldenGate Veridata Web User Interface. The options are:

"true" - This is the default partition.

"false" - This is not the default partition. This is the default value.

Example

QUESTONS:WHAT DOES THIS DO? DO WE WANT A DIFFERENT EXAMPLE?

<partition name="replicate" type="sql" default="true">
  <source-query>
    &quot;replicated&quot; =apos;false&apos;
  </source-query>
  <target-query>
    &quot;replicated&quot; =apos;true&apos;
  </target-query>
</partition>

5.2.12 query, source-query, target-query

The query, source-query, and target-query elements are free-form text that can be used to attach a query to the containing element. All three define Boolean SQL expressions that can be used in a where clause of the initial comparison query.

The query elements have no attributes. The source-query element is applied to the source, the target-query to the target, and query to both.