The variable, parameter, in the Ferret syntax diagram includes various types of parameters, including multitoken parameters and decimal and hexadecimal numeric input.
Multitoken Parameters
Multitoken parameters, such as subtable identifiers, which are typed as three values, are typed on a single line with one or more spaces separating the individual tokens. You can also enter multitoken parameters separated by hyphens.
For example, either of the following as subtable identifiers is acceptable:
0 200 0 0-200-0
You can separate parameters from each other and from the command by spaces or a comma. To specify a command option (cmdoption ), type it on the same line as the command (cmd).
Numeric Input
- When the radix for input is decimal, numeric input is interpreted as decimal numbers. For example, input of 45 is interpreted as the value 45.
- When the radix for input in hexadecimal, numeric input is interpreted as hexadecimal numbers. For example, input of 45 is interpreted as the value 69.
The initial radix setting is hexadecimal for input and output. For more information on setting the radix, see the documentation for the RADIX command.
- Decimal values can be signified by adding a 0i or i prefix, or by adding a decimal point suffix:
0i45 45. 0I45
- Hexadecimal values can be signified by adding a 0x or x prefix, or by adding an h suffix. Leading zeros are optional:
0x2D X2D 2Dh 002DH
The valid range of for numeric values is unsigned 16-bit values, 0 through 65535 (0x0 through 0xFFFF), except when patching and using /L or /W for 32-bit integers, in which case the valid range is 0 through 4,294,967,295 (0x0 through 0xFFFFFFFF).
You must separate two numbers on the same line from each other by a space or a comma.
Specifying a Subtable Identifier (tid)
Commands operate on subtables, rather than on tables. A table is a collection of subtables, and a subtable is a collection of physical rows. Each subtable has physical rows that store one particular type of data. For example, primary and fallback data subtables have data rows, primary and fallback index subtables have index rows, and the physical rows of table header subtables contain table metadata.
Each subtable is identified by a subtable identifier ( tid ). The tid has two parts. The first part identifies the table to which the subtable belongs. All subtables that are part of the same table have the same table identifier as part of their tid. The second part identifies one of the subtables that comprise the table, and indicate the subtable type.
A tid is defined as follows:
{ table_unique | = } type_and_index
table_unique and type_and_index together uniquely identify a subtable.
Syntax Element |
Description |
|---|---|
| table_unique | Unique identifier of the table to be acted upon. The table can be uniquely identified in either of two ways:
|
| = | Specifies to use the most recently saved value for table_unique . |
| type_and_index | Uniquely identifies the subtable within the group of subtables that comprise a table. The type_and_index identifies both the type of subtable, and the individual subtable itself. |
The syntax for type_and _index is as follows:
{ type [ / index [ / variant ] ] | number }
| Syntax Element | Description | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| type | The type of subtable. type can be one of the following:
|
||||||||||||
| index | The index subtable to examine. If an index subtable value is not provided, it defaults to the data subtable. Index 1 is the first secondary index. index 2 is the second secondary index, and so forth. index can be one of the following:
|
||||||||||||
| variant | The possible subtables. variant can be one of the following:
|
||||||||||||
| number | A single number that represents internally the type of subtable (header, primary data, or fallback data), the index to use when ordering the physical rows (primary data index or one of the secondary indexes), and the variant. The table header has a type_and_index number of zero. Examples of these numbers are listed below. |
The following table gives examples that describe the type_and _index fields.
| Subtable Description | Number | Type | Type/Index | Type/Index/Variant |
|---|---|---|---|---|
| Table header | 0 | H | ||
| Primary data subtable | 1024 (0x0400) | P | P/D | |
| First secondary index | 1028 (0x0404) | P/X1 | ||
| First fallback table | 2048 (0x0800) | F1 | F1/D | |
| Second secondary index of the third fallback table | 4104 (0x1008) | F3/X2 | ||
| All primary subtables | P/* | |||
| All primary secondary indexes | P/X* | |||
| All fallback subtables | F* | |||
| All subtables of this table | * | |||
| Sort table of the first secondary index | 1029 (0x0405) | P/X1/1 | ||
| Both tables during a sort of the first secondary index | P/X1/ * |
For example, assume that table T4 is a table in database MYDB and has a table_number of 0 1198. Also assume that input is accepted in hexadecimal format.
Some valid specifications of a tid for primary subtables of table T4 are as follows:
- "MYDB.T4" 400
- "MYDB.T4" 1024.
- "MYDB"."T4" P
- u 'MYDB'.'T4' 400 h
- 0 1198 400
- 0 1198 P
Classes of Tables
Vantage differentiates among the following five classes of tables. You can specify a table classification when defining the SCOPE parameters of an action.
| Table Type | Description |
|---|---|
| Permanent Journal (PJ) Tables | Tables that can survive system restarts, but do not contain user-visible data. Each PJ table contains data generated internally by Vantage. The PJ data is usually used to restore the journaled tables to a given checkpointed state by rolling transactions forward or backwards from an archived copy of the tables. |
| Permanent Tables | Tables containing the real data, which can survive system restarts. |
| Temporary Tables | Tables that can exist as either global or volatile temporary tables as defined:
|
| Spool Tables | Tables that contain non-permanent data and can be divided into classes according to their scope of persistence. Intermediate result spool tables hold temporary results during the processing of a single SQL query and persist only for the duration of that processing. Response spool tables hold the final answer set from a query and a limited number can optionally persist across further queries in the same session. Spool tables can be discarded as follows:
|
| Persist ant Spool Tables | These are treated like spool tables, but they survive system restarts. |
The attributes associated with each class of tables can affect system performance, since the attributes are set individually, and each class of tables is used for a different purpose.
For example, you might want to pack only Permanent and PJ tables. Therefore, you would specify these tables when defining the SCOPE of the PACKDISK command.
Rows and Row Ranges
- The partition number is stored as 2 bytes if the number of partitions defined for the table is up to 65535, or 8 bytes if the number is greater. If the table is not partitioned, no space is reserved in the row ID for a partition specification. A value of zero means the table is not partitioned.
- The specification of the remainder of the row ID depends on whether the table includes a primary index:
- PI tables have a 4-byte rowhash value and a 4-byte uniqueness value, which is used to impose uniqueness in cases of rowhash synonyms for different physical rows.
- Tables without a primary index (NoPI tables) and tables with a primary AMP index (PA tables) have a 16- or 20-bit hash bucket value and a 44-bit uniqueness value. For systems using 16-bit hash bucket values, the four bits between the hash bucket value and the uniqueness value are unused. The remainder of the RowID is treated as two 4-byte values, and present them as hash and uniq fields:
hash0 hash1 uniq0 uniq1 ----- ----- ----- ----- FFA9 CB78 0000 0002
For NoPI and PA tables displayed or manipulated, hash0 and hash1 actually represent a 20-bit hash bucket value and the 12 high-order bits of the uniqueness value, while uniq0 and uniq1 display the 32 low-order bits of the uniqueness value. For more information on NoPI and PA tables, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
A range of rows, also called a ridrange , is specified by the ridspec of the starting and ending rows:
start_rid_spec [ TO end_rid_spec ]
For purposes of input, the required and optional components of a ridspec depend on whether the table containing the physical row is partitioned.
| Table Type | Ridspec Syntax |
|---|---|
| partitioned | partitioned_ridspec:{ row_partition | = } [ { row_hash | = } [ row_unique | = ] ]
|
| nonpartitioned | nonpartitioned_ridspec:{ partitioned_ridspec |
[ row_partition | = ] { row_hash | = } [ row_unique | = ]
}
|
| Syntax Element | Description |
|---|---|
| row_partition | The partition number of the physical row. The partition number is a single numeric value. It is the internal partition number used by the file system. The value must be between 0 and 9223372036854775807 (0 and 7FFFFFFFFFFFFFFF in hexadecimal notation). For tables with multiple levels of partitioning, each physical partition results from the combination of all partitioning expressions in the PARTITION BY clause for a specific set of values in the partitioning columns. This gives a single, unique combined partition number, which is mapped to an internal partition number. Although the combined partition number of a partition can change if partition ranges are dropped or added for the partition level, the internal partition number does not change. For rows in nonpartitioned tables, the partition number must be zero, if specified, or can be omitted from the ridspec. |
| row_hash | The row hash value of the physical row. The row hash determines the AMP to which the physical row is distributed. The row hash consists of 2 numeric values, usually shown in output screens under column headings hash0 and hash1. For start_rid_spec :
For end_rid_spec :
|
| row_uniq | The system-generated uniqueness value which is used with the row hash value to uniquely identify a physical row. The uniqueness value consists of two numeric values, usually shown in output screens under column headings u0 and u1. For start_rid_spec :
For end_rid_spec :
|
| = | The program will use the most recently saved values for row_partition, row_hash, or row_uniq. The most recently specified values for row_partition , row_hash , and row_uniq are stored. Separate values are stored for start_rid_spec and end_rid_spec . row_partition , row_hash , or row_uniq . An error is reported if a saved start_rid_spec or end_rid_spec value is used for a partitioned table, but the table referenced in the current command is not partitioned, and vice versa. |
For more information on the file system and physical rows, see The File System.
For more information on table row structure, see Teradata Vantage™ - Database Design, B035-1094.
Vproc Numbers
In Ferret, the vproc number (vproc_number) is used in the SCOPE command to specify one AMP or a range of AMPs for which the utility performs an action, such as reconfiguration or disk space display.
Valid AMP vprocs have numbers in the range of 0 through 16199.
Map Names
A map includes one or more AMP vprocs. If you specify a map name in the Ferret SCOPE command, the name must be enclosed with double quotation marks. The scope is effectively the same as if you had scoped Ferret to the individual vprocs that are included in the specified map.