2.10 - Push Profile Attributes - Teradata QueryGrid

Teradata® QueryGrid™ Installation and User Guide

prodname
Teradata QueryGrid
vrm_release
2.10
created_date
September 2019
category
Administration
Configuration
Installation
User Guide
featnum
B035-5991-099K

The attributes of a push profile are described in the following sections. Except where noted, the attributes are used in default push profiles and custom push profiles. Create custom push profiles and overrides with the help of a Teradata Customer Support Representative.

<SourceProfile: SourceProfileName,>

<SourceProfile: SourceProfileName,> is the PushProfileID of the default push profile. A default push profile does not contain this attribute. A custom push profile always points to the PushProfileID of the default push profile used to create the custom profile.

ColOp

Changing ColOp is not recommended. Always use the default data type lists provided for Teradata Database, Presto, Hive, Spark, and Oracle.

ColOp is an array of data types and the operations that can be pushed to the remote database for a specified data type. For a disabled data type, none of the expressions/operations are pushed. When a default is used in ColOp, the expressions/operations are pushed to the remote system for all supported data types.

The data types and operations that are supported by a corresponding Teradata Database version are listed using true/false. True means an operation on a data type can be pushed to a remote database. You can override the default for a data type if a data type is specified.

In the following example, operations/conditions on all data types that can be pushed to the target connector are supported, except array.
"default":true,
"array":false,
In the following example, only operations/conditions on the following data types are pushed. No other data types are supported for the link.
"date":true,
"char":true,
"varchar":true,
Attribute Setting
Data type

TRUE: When a data type has a true flag, the operation on the data type is pushed to the remote system.

FALSE: No operation on this data type is pushed to the remote system.

Operation

TRUE: When an operation on any data type has a true flag, the operation is pushed to the remote system.

FALSE: The operation on any data type is not pushed to the remote system for any data type.

Example operations are:
  • CompareOp operations (EQ, NE, LT, and so forth)
  • LogicalOp operations (AND, OR, NOT, and so forth)
All ColOp attributes are subject to the limitations of the target connector. If a target connector does not support the operation, the operation is not pushed to the remote system.
Example of ColOp attribute and settings:
  "ColOp" : [

               {

                  "default" : true,

                  "date" : false,

                  "CompareOp" : { "default" : true },

                  "LogicalOp" : { "default" : true, "OR" : false },

                  "ArithOp" : { "default" : true }

               }

              ],

GroupOp

Changing GroupOp from the default provided for the target connector is not recommended. For example, if the default custom push profile states that an inner join is true and an outer join is false, do not change it.

GroupOp is the name of a group that contains similar operations. When a default is used, it implies that the default applies to all operations in the group. The value is either TRUE or FALSE.

When a group of similar operations like JoinOp or AggrOp are listed under GroupOp, and the value is set to TRUE or FALSE:

TRUE: The operation will be pushed to the remote system.

FALSE: The operation will not be pushed to the remote system.

Example of GroupOp operations and settings:
   "JoinOp" : { "inner" : true, "outer" : false },

   "AggrOp" : { "count" : true, "sum" : true } 

Within a group, if the default push profile supports the operation, you can override the default by specifying a specific operation (Op).

ServerSettings

Server-level settings impact the performance of the query. The cost factors for import and export can be set for a specific link. In addition, flags can be set to turn on the cluster phase and remote joins for an operation.
Attribute Setting
doSingleTblDelayedFetch

Enables and disables delayed fetch processing for single remote table clustered query. The default value is FALSE.

FALSE: Delayed fetch is disabled for single table clustering and only static planning applies.

TRUE: Delayed fetch is enabled for single table clustered query.

Changing the default is not recommended.

doCluster

Controls whether to push or not to push aggregation and predicates to the remote system. The default value is TRUE.

TRUE: Push aggregation and predicates to the remote system.

FALSE: Do not push aggregations and predicates to the remote system.

If the processing power of the remote system is not optimal, the recommendation is to set this to FALSE. Custom push profiles and overrides should be done only with the help of a Teradata Customer Support Representative.

doMultiTblClustering

Controls whether multiple remote tables should be joined at the remote system or if they should be imported to the local system and joined at the local system. The default value is TRUE.

TRUE: Enable remote join for clustered remote tables.

FALSE: Disable remote join for clustered remote tables. This implies that tables are imported to the local system and joined at the local system.

If the processing power of the remote system is not optimal, the recommendation is to set this to FALSE. Custom push profiles and overrides should be done only with the help of a Teradata Customer Support Representative.

doRemoteJoin

Controls whether remote join is enabled or not; i.e., exporting of a local table and join with remote table on the target system is allowed or not. Default value is TRUE.

TRUE: Enables exporting a table from a local system and performing a join with a table on the remote system.

FALSE: Disables remote join. A table will not be exported to a remote system for a remote join. Joins are performed on the local system.

If the processing power of the remote system is not optimal, and the cost of an export to the remote system and a join on the remote system is higher, it is recommended to update the cost adjustments of import and export.
The following attributes are applicable only when doRemoteJoin is set to TRUE:
  • importCostAdj
  • exportCostAdj
  • remJoinTransAdj
  • delayedFetchThresh
importCostAdj
Cost of importing one remote table of size ONE unit.
This attribute applies only when doRemoteJoin is set to TRUE: The importCostAdj and exportCostAdj factors are relative to each other.
exportCostAdj
Cost of exporting one local table of size ONE unit.
This attribute applies only when doRemoteJoin is set to TRUE: The importCostAdj and exportCostAdj factors are relative to each other.
remJoinTransAdj

Specifies the cost of performing a remote join and importing the result. The default is 0 and should not be changed. This factor is accounted for in exportCostAdj.

delayedFetchThresh
Specifies the minimum size of a remote table in order to perform a delayed fetch. In a delayed fetch:
  • The remote join is performed on the remote system and the resulting table is temporarily held at the remote system.
  • If the table is at or below the minimum size of the delayedFetchThresh, the temporary table is exported to the local system to perform the join.
  • If the table is above the minimum size of the delayedFetchThresh, a remote join is performed on the temporary table on the remote system, and the joined table is exported to the local system.
If the value is set to zero, delayed fetch is disabled and the temporary (result) table is exported to the local system to perform the remote join.

TableProfile

TableProfile allows you to override a custom push profile at the table level. It is useful when a set of operations for a few tables on a remote system are not performing according to expectations. Custom push profiles and overrides should be done with the help of a Teradata Customer Support Representative.