HASHBAKAMP
Purpose
Returns the identification number of the fallback AMP corresponding to the specified hash bucket. If no hash bucket is specified, HASHBAKAMP returns one less than the maximum number of AMPs in the system.
Syntax
where:
|
Syntax element … |
Specifies … |
|
expression |
an optional expression that evaluates to a valid hash bucket number. For information on obtaining a hash bucket number that you can use for expression, see “HASHBUCKET” on page 898. |
ANSI Compliance
This is a Teradata extension to the ANSI SQL:2011 standard.
Argument Type and Value
The expression argument must evaluate to INTEGER data type where the valid range of values depends on the system setting for the hash bucket size.
|
IF the hash bucket size is … |
THEN the range of values for expression is … |
|
16 bits |
0 to 65535. |
|
20 bits |
0 to 1048575. |
For information on how to specify the system setting for the hash bucket size, see “DBS Control utility” in Utilities: Volume 1 (A-K).
If expression cannot be implicitly converted to an INTEGER, an error is reported.
If expression results in a UDT, Teradata Database performs implicit type conversion on the UDT, provided that the UDT has an implicit cast that casts between the UDT and any of the following predefined types:
To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information on CREATE CAST, see SQL Data Definition Language.
Implicit type conversion of UDTs for system operators and functions, including HASHBAKAMP, is a Teradata extension to the ANSI SQL standard. To disable this extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE. For details, see Utilities: Volume 1 (A-K).
For more information on implicit type conversion, see Chapter 13: “Data Type Conversions.”
Result
|
IF expression … |
THEN … |
|
does not appear in the argument list |
HASHBAKAMP returns an INTEGER value that is one less than the maximum number of AMPs in the system. |
|
evaluates to NULL |
HASHBAKAMP returns NULL. |
|
evaluates to a valid hash bucket number |
HASHBAKAMP determines the fallback AMP corresponding to the hash bucket and returns the identification number of the AMP. The result is an INTEGER value that is greater than or equal to zero and less than the maximum number of AMPs in the configuration. |
For information on the hash map that defines the relationship between hash buckets and fallback AMPs, see “Reconfiguration utility” in Utilities: Volume 2 (L-Z).
Examples
The following examples assume a table T with an INTEGER column B populated with integer numbers from zero to the maximum number of hash buckets on the system.
Example
If you call HASHBAKAMP without an argument, it returns one less than the maximum number of AMPs on the system.
SELECT HASHBAKAMP();
Example
If you call a HASHBAKAMP function with an argument of NULL, the function returns NULL.
SELECT HASHBAKAMP(NULL);
Example
This query returns the distribution of the hash buckets among the fallback AMPs.
SELECT B, HASHBAKAMP (B)
FROM T
ORDER BY 1;