16.10 - Considerations - Teradata Unity

Teradata Unity User Guide

Teradata Unity
Release Number
January 2018
English (United States)
Last Update
Topic Consideration
  • Loads using passive routing must use a single system routing rule, not a session load balanced rule (round robin or weighted round robin).
  • Unity 16.20 supports Teradata Database 14.10 to 16.20.
  • Unity does not support mainframe clients.
  • To work with Row Level Security, Unity requires DBS bug to be patched.
  • When used through LDAP or SPNEGO, Teradata .Net client 14.10 is not supported if the managed system is TD 13.10. TD 13.10 users should update to .Net 15.01.
  • For managed sessions, proxy authorization must be enabled if any of the managed Teradata systems is version Teradata 14.10 or higher (a database restriction). This is not necessarily required for passive routing if only TD2 authentication is used.
  • TDNEGO is not currently supported via Unity.
Application Compatibility
  • Unity does not support more than 150,000 objects in managed dictionaries.
  • In managed sessions, the XML data types and XQuery are not currently supported.
  • In managed sessions, JSON data types are not currently supported.
  • Teradata (Query Grid) Foreign Server syntax is not supported in managed sessions, including Foreign Server definitions and Foreign Server access within SQL code.
  • SQL-H is not supported through Unity.
  • UDMs are not supported by Unity in managed sessions. This prevents most uses of GEOSPATIAL data types from working.
  • Table operators are not supported in Unity's managed sessions.
  • Load Isolation is not supported and should not be used via Unity. SQL syntax related to Load isolation will not parse (E.g. LOCKING tablename FOR LOAD COMMITTED).
  • In Unity 16.20 using CDM, updatable cursors or results sets are supported. In Unity 16.00 and previous versions, updatable cursors or results sets are not supported in managed sessions (e.g. SQLSetPos in ODBC, WHERE CURRENT OF, etc).
  • The Mloadx extension to Mload is not supported and is disabled. Clients using it must drop and recreate indexes during ETL operations.
  • DBQL and related system configuration settings should NOT be changed via Unity in managed sessions. They should be executed directly on each managed Teradata system. System specific performance data should be accessed directly on a single system. Unity does include an internal performance trace facility for measuring overall response time.
  • Any DDL that is system specific should be executed using a system specific passive routing rule. For example, any DBA maintenance that executes DDL that references specific disk or memory resources on one system.
  • The statement independence feature is not supported. This database feature allows a batch insert to succeed even if some rows fail. This is to speed recovery time; only the failed rows need to be retried. Unity disables this feature.
  • Unity does not support the UNICODE pass through feature introduced in Teradata Database 16.10 (SET SESSION CHARACTER SET UNICODE PASS THROUGH ON;).
  • Setting an alternate current user in a session using a QUERYBAND is not supported (SET QUERY_BAND = 'PROXYUSER=username;' FOR SESSION;).
  • UDF debugging via Unity is not supported. This is enabled with 'set session debug function UDFNAME on;'.
  • Unity does not support selecting directly from named Join Indexes.
  • Secure Zones are not compatible with Unity managed routing.
  • Usernames and database objects with periods, single quotes, spaces and other special characters in them are not supported as managed objects (for managed sessions).
Client Workload
  • ETL workloads that rely on intermediate volatile tables are not guaranteed to be recoverable, since a volatile table DDL (create table) ends a transaction. If an unexpected failure occurs immediately following a VT create table, the create is not replayed.
  • Any workload requireing CDM has a high amount of overhead. This includes both a fixed latency as well as some variable latency according to the size of the change set.
  • In managed sessions, DDL requests incur higher overhead because of extra processing to update the Unity repository. Heavy Workloads that include requests with low response times that also include DDL requests (tactical reports in Micro Strategy) may experience slower than expected performance when processing multiple, simultaneous requests using ETL routing. These workloads should use PASSIVE routing.
Non-Deterministic Functions
  • To support writing to tables with Identity columns, the CDM feature in Unity 16.20 must be enabled.
  • For managed sessions, time zones on both Teradata systems and Unity servers must be identical. All systems should be synchronized using NTP to the same time and time zone. All systems act as one logical system.
  • In Unity 15.00-16.00, in managed sessions, write statements (that can be identified) that insert non-deterministic data will be rejected. For example, create table or insert statements that contain SAMPLE or TOP in a sub-select with no deterministic ORDER BY. Unity 16.10 will add support for syncing SAMPLE or TOP with CDM, however large SAMPLE sets may be slow.
  • Any write that reads from system-specific information could be non-deterministic. For example, an insert-select that reads from DBQL tables. These writes are not supported via Unity managed routing. These are supported in Passive routing, but are not synchronized. Note that Unity 16.20 CDM feature will NOT synchronize any writes from the user DBC.
  • For managed sessions, when writing to a temporal table in Teradata 14.00.02 and earlier, the VALIDTIME and TRANSACTIONTIME fields use the database system time and are not in sync across systems.
  • Writes to a temporal table via Unity to a Teradata 14.x or higher system fails unless the user has the NONTEMPORAL privilege on that table.
Server-Side Code
  • Unity 16.00 (and earlier) has a number of limitations that impact the use of stored procedures. Unity 16.20 has enhanced stored procedure support that addresses these limitations. To enable this support, the CDM feature must be enabled in Unity and on the Teradata system.
  • CDM in Unity 16.20 cannot replicate any request that includes an XML/XQuery function call.
  • CDM cannot replicate tables or requests that use data types added since DBS 13.10. (NUMBER, DERIVED PERIOD, ARRAY, JSON).
  • To avoid conflict between values set on different systems, CDM requires IDENTITY columns to be redefined so that they have different ranges on different systems.In Unity 16.10, the replication of IDENTITY columns comes with a significant performance impact.
  • CDM cannot replicate any request that uses the SQL syntax: CREATE TABLE AS.
  • CDM in Unity 16.20 cannot replicate rows that use the 1MB perm space feature.
  • CDM cannot replicate bulk load utilities (E.g. Mload, Fastload, TPT Load, etc.).
  • Unity 16.20 contains CDM features that enhance support for stored procedures.
  • CDM cannot replicate requests that issues a Query Grid / SQL-H / Foreign Server request.
  • CDM cannot replicate any request that does an Update, Delete or Merge into NOPI tables.
  • CDM is not compatible with Teradata Secure Zones (Multi-tenancy).
  • CDM cannot replicate tables that use columnar or are column partitioned.
  • CDM in Unity 16.20 cannot replicate requests that use Unicode Pass through characters.
  • CDM cannot replicate requests that involve Volatile temp or Global temp tables.