Hvradapt

Name

hvradapt - Explore base table definitions in the database(s) and adapt them into channel information

Synopsis

hvradapt [-options] -lloc hubdb chn

Description

Command hvradapt compares the base tables in the database with the table information for a channel chn. It will then either add, replace or delete table information in the catalog tables (hvr_table and hvr_column) so this information matches.

• If the location (-lloc) from where the hvradapt explores the base table definitions contains a table which is not present in the channel but is matched by the table filter statement then it is added to the channel.
• If a table is in the channel but is not matched by the table filter statement then it is deleted from the channel.
• If a table is both matched by the table filter statement and included in the channel, but has the wrong column information in the channel, then this column information is updated.
• If table filter statement is not supplied (no -n or -N option), then tables are not added or deleted; only existing column information is updated where necessary.

The argument hubdb specifies the connection to the hub database. For more information about supported hub databases and the syntax for using this argument, see Calling HVR on the Command Line.

Hvradapt is equivalent to the Table Explore dialog along with the Table Filter dialog in HVR GUI.

Options

This section describes the options available for command hvradapt.

Table Filter

Hvradapt supplied with table filter statement (option -n or -N) allows you to define which base tables in the adapt location should be included in or excluded from the channel. Only tables matching any of the given statement will be included or excluded.

Syntax for Table Filter

This section describes the syntax for the classic table filter statement that can be used with options -n or -N.

[schema.]tablename [-T target_schema] [-K (col_list)]

![schema.]tablename

+

• Value schema or tablename can be a literal (optionally enclosed in double quotes) or a pattern matching can be done (only for tables or columns) using the special symbols *, ? or [characters].

• Option -K marks the listed columns as distribution key columns in the 'column catalog', instead of defining a new ColumnProperties /DistributionKey action. In HVR GUI, marking a column as distribution key can be done from the table Properties dialog. For more information, see Marking a Column as Distribution Key in HVR GUI.

• Option -T defines the target schema into which the table should be replicated. Hvradapt will automatically define a new TableProperties /Schema action in this case.

• Options -K and -T only have an effect at the moment a table is added to channel, but are ignored otherwise.

• Special symbol ! (NOT) is used to define negative patterns. This type of pattern can only be used after a regular/positive pattern and therefore cannot be used as an orphan (without other patterns) or the first pattern in the adapt template. Tables matching the preceding pattern and the negative pattern are excluded from the channel. For example,

  *                 # Match all tables in default schema.
  !tmp_*            # Exclude all tables whose name begin with 'tmp_' in default schema.
  

• Special symbol + matches all tables already in the channel.

Empty lines and comments (e.g. #Test) are ignored.

Example for Table Filter

Hvradapt can filter tables using a table filter file (option -n) or on the command line (option -N).

• Examples for filtering tables or schemas.
  The following is an example of table filter file (e.g. /tmp/adapt.tmpl):

  +                       # Match all tables already in channel.
  tbl1                    # Match table named 'tbl1' in default schema.
  schema1.*               # Match all tables in 'schema1'.
  schema2.tbl1            # Match table named 'tbl1' in 'schema2'.
  history_*               # Match all tables whose name begin with 'history_' in default schema.
  "my table"              # Match table named "my table" in default schema. Use double quotes if there is a space in table name.
  schema2."tbl.tab*"      # Match all tables whose name begin with 'tbl.tab' in 'schema2'. Use double quotes if there is a special character in the table name.
  schema3.xx* -T schema4  # Match all tables whose name begin with 'xx' in schema3 for replicating them to schema4.
  tbl2 -K(col1 col2)      # Match table named 'tbl2' in default schema and add columns named 'col1' and 'col2' as distribution key columns.
  

  This table filter pattern can be supplied in the command line as:

  hvradapt -N + -N tbl1 -N schema1.* -N schema2.tbl1 -N history_* -N "my table" -N schema2."tbl.tab*" -N schema3.xx* -T schema4 -N tbl2 -K(col1 col2) -l mylocation hvrhub/hvrhub hvrdemochn
  

A shell script as shown in section Shell Script to Run Hvradapt can be created to run hvradapt for checking new or modified tables in a location.

Shell Script to Run Hvradapt

A shell script can be created to run hvradapt for checking new or modified tables in a location.

The following example demonstrates the use of a shell script to run hvradapt for checking new or modified tables in location loc1 and if any new or modified tables are found in location loc1, the script executes the necessary commands to enroll the tables into the channel mychn.

#!/bin/sh
hub=myhub/passwd                                    # Hub database and password (if required)
chn=mychn                                           # Channel
src=loc1                                            # Source location
F=/tmp/adapt_$chn.out                               # File where hvradapt writes list of new or changed tables
hvradapt -f$F -n/tmp/adapt.tmpl -l$src $hub $chn    # Add new or changed tables from source to channel based on the patterns defined in the pattern file.
if test -f $F                                       # If file $F exists then new or changed tables were detected
then
    hvrsuspend $hub $chn-integ                      # Stop integrate jobs
    hvrinit -oelj $hub $chn                         # Regenerate supplemental-logging, jobs and enroll info
    hvrrefresh -r$src -t$F -qrw -cbkr $hub $chn     # Re-create and online refresh tables in file $F
    hvrstart -r -u $hub $chn-integ                  # Re-trigger stopped jobs
    hvradapt -x -l$src $hub $chn                    # Double-check channel now matches target location (optional)
                                       
    rm $F                                           # Remove file with list of new or changed tables
fi

Marking a Column as Distribution Key in HVR GUI

Perform the following steps to mark a column as distribution key in HVR GUI:

1. To view a table's details or properties, right-click on a table in the channel and select Properties.
   

2. By default, the distribution key column is not displayed in the table Properties dialog. To display this column in the table Properties dialog, right-click on the header and select Distr. Key.
   

3. To mark a column as distribution key, select the respective column's checkbox available under Distr. Key.