ROUTING UPDATE - Teradata Unity

Teradata Unity User Guide

Product
Teradata Unity
Release Number
16.00
Published
March 2017
Language
English (United States)
Last Update
2018-03-29
dita:mapPath
ipb1476817227329.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2520
lifecycle
previous
Product Category
Analytical Ecosystem

Purpose

This command allows you to create routing rules:

REJECT LOGON
Use this routing rule when a connection should be rejected.
MANAGED
Use this routing rule when you want a managed routing connection.
PASSIVE
Use this routing rule when you want a passive routing connection.

Syntax

ROUTING UPDATE <RoutingName>
[REJECT LOGON]
| [READ [ALL | <tdpid> [, <tdpid>, ...][PREFERRED ]] [WRITE [ALL | <tdpid> [, <tdpid>,...]] [CREATE {PREFERRED | BALANCED}]] [OPTIONS <option_name> [,<option_name>, . . . ]] [ERRORLIST<ErrorListName>] |PASSIVE[ALLOW MANAGED WRITES] [RR|LU] <tdpid> [,tdpid>,..][ERRORLIST<ErrorListName>]
;

Common Parameters

RoutingName
Name of the routing rule to update.
If a routing name does not exist, this command creates the routing name.
tdpid
Unique TDPID identifier of a Teradata Database.
ErrorProfileName
Specifies the error list profile for this routing rule to use.
If this parameter is not specified, Unity uses the DefaultErrorList.

Reject Logon Parameters

REJECT LOGON
Creates a routing rule that rejects any new connections that are initiated by the user you specify.

Managed Routing Parameters

READ [ALL | <tdpid> [, <tdpid>, ...] ]
Sets the READ attributes for this routing rule by specifying the Teradata Database systems to use when submitting Read requests. If you specify the ALL option, all systems managed by Unity become available for Read requests. If you specify a list of Teradata Database systems (tdpid), Unity sends Read requests to only the systems that you list. By default, the READ parameter uses automatic routing among the systems in the list. If you specify the PREFERRED parameter, the order of the systems in the list defines the preferred routing order for Read requests. All routing rules must include this READ parameter.
PREFERRED
Specifies that Read requests adhere to the Preferred routing algorithm. A Preferred routing rule sends Reads to the first TDPID specified. If a system failure or failover occurs, Reads are then sent to the next TDPID specified.
If this option is not provided, the Default Read Routing behavior is used. The Default Read Routing rule applies auto-routing behavior using the shortest queue algorithm.
WRITE [ALL | <tdpid> [, <tdpid>, ...] ]

Sets the WRITE attributes for the routing rule by specifying the Teradata Database systems to use when submitting Write requests. At logon, Unity opens database Write sessions only on the systems specified in the WRITE parameter.

This parameter is optional. If the WRITE parameter is not specified, the routing rule is created as Read-only.
Parameter Description
WRITE Routing rule submits Write requests to all managed systems with tables that are affected by the request.
WRITE ALL Routing rule submits Write requests to all systems currently managed by Unity. If you add a new Teradata Database system in the future, this routing rule will send Write requests to the newly added system without you performing additional updates to the rule.
WRITE <tdpid> [, <tdpid>, ...] Routing rule submits Write requests only to the Teradata Database systems (tdpid) in the list. If you specify CREATE PREFERRED, the order of the systems in the list defines the preferred routing order, and Unity sends CREATE requests to the first available system in the list.
CREATE [PREFERRED | BALANCED]
Specifies the routing method to use for CREATE statements.
If the CREATE command is not specified, CREATE statements are sent to all systems and are treated as standard Write requests.
Parameter Combination Description
CREATE PREFERRED Sends CREATE statements to the first TDPID specified. If Unity detects a failure or failover, CREATE statements are sent to the next TDPIP specified, and all in-flight work is lost.
CREATE BALANCED Sends all CREATE statements for the given session to the system Unity determines to be least busy. If Unity detects a failure or failover, CREATE statements are sent to the next least busy system, and all in-flight work is lost.
OPTIONS
Specifies a list of additional options for this routing rule. Options specified by [OPTIONS <option_name> [, <option_name>,...]] are on/off type flags. Each option is turned off for this routing rule by default. The whole [OPTIONS <option_name> [, <option_name>,...]] sub-option is also optional. If not specified, all supported options are turned off for this routing rule. The following <option_name> is available.
<option_name> Description
ACCESS UPGRADE Automatically upgrade ACCESS locks to READ locks in user request.

Passive Routing Parameters

PASSIVE
This keyword indicates that the rule is passive. This keyword must be specified for every passive routing rule; otherwise Unity considers the rule managed.
ALLOWED MANAGED WRITES
If specified, connections that match this rule are allowed to write to objects managed in the Unity dictionary. By default, this option is not enabled.
RR
Round robin. If RR is specified, connections are opened on the tdpids specified in round robin style. Alternating based on systems listed.
LU
Least used. If LU is specified, connections are opened on the tdpid that has the least number of connections through Unity.

Usage Considerations

When the Sequencer processes a ROUTING UPDATE command, it updates the repository and in-memory routing rules.

Examples

At the unityadmin prompt:

Input Description
ROUTING UPDATE MSIUser WRITE CREATE BALANCED ERRORLIST MsiErrorList; Creates a routing rule that reads and writes to all systems, but only creates volatile tables to one system. Unity chooses the least busy system.
ROUTING UPDATE readSys1 READ sys1 WRITE; Creates a routing rule that only reads from system 1, but allows writes to all Teradata Database systems.
ROUTING UPDATE readOnlySys1 READ sys1; Creates a Read-only routing rule that only accesses system 1.
ROUTING UPDATE prefReadOnly READ PREFERRED; Creates a Read-only routing rule with a read-preferred algorithm.
ROUTING UPDATE DefaultRouting REJECT LOGON; Updates the default routing rule to reject incoming connections. This prevents connections from any user without an explicit routing rule definition.
ROUTING UPDATE prAllSys PASSIVE sys1, sys2; Creates a passive routing rule that allows connections to sys1 and sys2. All connections are initially opened to sys1. Connections cannot write to Unity-managed objects.
ROUTING UPDATE prAllSysLU PASSIVE ALLOW MANAGED WRITES LU sys1, sys2; Creates a passive routing rule that allows connections to sys1 and sys2. Each connection is opened on the system that has the lowest number of connections through Unity at that time. The connection is allowed to write managed objects.
ROUTING UPDATE accessUpgrade READ WRITE OPTIONS ACCESS UPGRADE Create a routing rule that reads and writes to all systems, but all ACCESS locks in the user request are upgraded to READ locks.