15.00 - Specifying Data Objects - Teradata Database

Teradata Database Support Utilities

prodname
Teradata Database
vrm_release
15.00
category
Configuration
featnum
B035-1180-015K

Specifying Data Objects

This section provides detailed information on the following:

  • “Numeric Input”
  • “Multitoken Parameters”
  • “Cylinders and Data Blocks”
  • “Rows and Row Ranges”
  • “Subtable Identifiers (tids)”
  • “Stored Input Values”
  • Numeric Input

    Numeric values can be entered into Filer in either decimal or hexadecimal format. The default numeric base for data input to and output from Filer depends on the radix settings for input and output:

  • When the radix for input is decimal, Filer interprets numeric input as decimal numbers. For example, a number entered as 45 will be interpreted as the value forty five.
  • When the radix for input in hexadecimal, Filer interprets numeric input as hexadecimal numbers. For example, a number entered as 45 will be interpreted as the value sixty nine.
  • The initial radix setting in Filer is hexadecimal for input and output. For more information on setting the radix, see “RADIX” on page 177.

    The following special numeric formatting conventions can be used to force Filer to interpret entered numeric values as decimal or hexadecimal, regardless of the radix setting:

  • 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 numeric values in Filer are unsigned 16-bit values, 0 through 65535 (0x0 through 0xFFFF), except when patching using /L or /W for 32-bit integers, in which case the valid range is 0 through 4,294,967,295 (0x0 through 0xFFFFFFFF). For more information, see “Subtable Identifiers (tids)” on page 79.

    You must separate two numbers on the same line from each other by a space or a comma.

    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. Filer also accepts multitoken parameters separated by hyphens.

    For example, Filer accepts either of the following as subtable identifiers:

    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).

    Cylinders and Data Blocks

  • Each cylinder has a 64-bit identifier, represented as a cylid in Filer commands. Cylids must be entered as 16-character hexadecimal numbers regardless of the Filer input radix setting.
  • Data blocks are specified by a cylid, and by their starting sector number and length, represented as sectornum and sectorcount in Filer commands. Type each of these parameters as a single numeric value. The format for entering sectornum and sectorcount values is subject to the input radix setting.
  • For more information on the formatting of numeric input and output, see “Numeric Input” on page 75 and “RADIX” on page 177.

    Rows and Row Ranges

    For more information on the Teradata Database File System and physical rows, see “The Teradata Database File System” on page 61.

    Every physical row is uniquely identified by a row ID. A row ID specification, also referred to as a ridspec or a rowspec, consists of an two 2- or 8-byte partition number, a 4-byte row hash value, and a 4-byte uniqueness value.

    The partition number is stored as 2-bytes if the numbers are in the range of 1 through 65535. If the range is greater, 8-bytes are used. If the table is not partitioned, the partition number is zero.

    NoPI tables, which include all column-partitioned tables, have a 16- or 20-bit hash bucket value in place of the 4-byte row hash value, and a corresponding 48- or 44-bit uniqueness value. For more information on NoPI tables, see SQL Data Definition Language.

    A physical row can store one of several types of data, such as table rows, a series of column partition values, a table header (metadata about the table), index data structures, and other types of data. Every physical row stores only one kind of data.

    A range of rows, also called a ridrange, is specified by the ridspec of the starting and ending rows:

    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

    nonpartitioned

     

    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, which is the value shown in Filer command output screens under the column heading: part. 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 h0 and h1.

    For start_rid_spec:

  • row_hash defaults to 0x0000 0x0000.
  • For end_rid_spec:

  • row_hash defaults to the value of row_hash specified in start_rid_spec. If no value for row_hash was specified in start_rid_spec, row_hash in end_rid_spec defaults to 0xFFFF 0xFFFF.
  • 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:

  • If row_uniq is omitted, and no end_rid_spec is specified, all the rows in row_hash are selected.
  • If row_uniq is omitted, and an end_rid_spec is specified, row_uniq defaults 0x0000 0x0000.
  • For end_rid_spec:

  • row_uniq defaults to the value of row_uniq specified in start_rid_spec. If no value for row_uniq was specified in start_rid_spec, row_uniq in end_rid_spec defaults to 0xFFFF 0xFFFF.
  • =

    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.

    This option cannot be used unless input values have already been defined for 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.

     

    Example  
    Filer ==> showdefaults
    The current object selected is Nothing
    The current defaults are:
     
    The present value for the = is:   Dec  (Hex)
        Cylinder Id =  : 0000FFFFFFFFFFFF
        Sector Number          972  (03CC)
        Sector Count            16  (0010)
        Memory Address       is undefined.
        Length               is undefined.
        Patch Offset         is undefined.
        TID                0    26  1024  (0000 001A 0400)
        Start Rowid        0   512   657     0  1059  (0000 0200 0291 0000 0423 )
        End Rowid      65535   512   657     0  1059  (FFFF 0200 0291 0000 0423 )
        Start WLSN           is undefined
        End WLSN             is undefined
        Where Clause         is undefined
     
    Filer is running as a startable utility
    Filer is running with Locking -- Enabled
    Filer is running with Script Mode --   Disabled
    File System has Internal Tracing -- Enabled
     
    Example  
    DB ==>
    dbrow /m
     
    ROW
    length            rowid             flags presences
           part hash0 hash1 uniq0 uniq1
    ------ ---- ----- ----- ----- ----- ----- ---------
      09CD 0000  7C6B  71AB  0000  0001   00         9C
      0097 0000  7D47  ADC8  0000  0001   00         91
      0800 0000  7D57  B91E  0000  0001   00         9C
      00D5 0000  823F  CDAE  0000  0001   00         99
      00D5 0000  871F  7438  0000  0001   00         99
      0097 0000  8913  51BD  0000  0001   00         91
     

    For more information on table row structure, see Database Design.

    Subtable Identifiers (tids)

    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 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:

    1 Specify the name of the database to which the table belongs and the table name, separated by a period. The names individually or together must be delimited. The following formats are valid:

  • "database_name.table_name"
  • "database_name"."table_name"
  • 'database_name.table_name'
  • 'database_name'.'table_name'
  • 2 Specify the unique numeric identifier of the table, which consists of two numeric values separated by a space. The table identifier consists of the first two numeric values returned by the TABLEID command. These two numbers are common to all subtables that comprise the table.

    Note: The format of the input numbers depends on the current radix setting, which is displayed by the RADIX command.

    =

    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:

     

    Syntax Element

    Description

    type

    The type of subtable. type can be one of the following:

     

    Type

    Description

     

     

    *

    All the subtables of this table.

     

     

    H

    The table header subtable.

    Because table header subtables have only one row and no secondary indexes or work subtables, any index and variant specifications that follow an H type are ignored.

     

     

    P

    The primary data subtable.

     

     

    F number

    The fallback data subtable specified by number. The default is 1.

     

     

    F *

    All of the fallback subtables.

     

     

     

     

     

     

     

    index

    The index subtable to examine. If the index subtable is not given, 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:

     

     

     

     

     

     

     

     

    Index

    Description

     

     

     

    number

    A secondary index and can be used instead of specifying X number. If you enter number by itself, then number must be a multiple of 4 and it is interpreted in the following way:

  • number = 0 is the same as entering X0
  • number = 4 is the same as entering X1
  • number = 8 is the same as entering X2
  • number = 12 is the same as entering X3
  • and so forth.

     

     

     

    *

    All the indexes of the table.

     

     

     

    D

    The primary data index (same as X0 or 0).

     

     

     

    X number

    The secondary index specified by number. The default is 1.

     

     

     

    X*

    All the secondary indexes, starting at 0.

     

     

     

     

     

     

     

    variant

    The possible subtables.

    variant can be one of the following:

     

     

     

     

     

     

     

     

    Variant

    Description

     

     

     

    *

    All the possible variant subtables.

     

     

     

    0

    The default. If you do not specify variant, 0 is assumed.

     

     

     

    1

    The value during a Sort or Table Modify operation.

     

     

     

    2

    A value that is not used.

     

     

     

    3

    A value that is not used.

     

     

     

     

     

     

     

    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/ *

    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
  • 'MYDB'.'T4' 400 h
  • 0 1198 400
  • 0 1198 P
  • Stored Input Values

    Filer stores the most recent values entered for the following parameters:

  • cylid
  • endcylid
  • length
  • memaddr
  • offset
  • row_hash
  • row_partition
  • row_uniq
  • sectorcount
  • sectornum
  • startcylid
  • tablenumber
  • tid
  • You can enter an equal sign (=) for any of these parameters to use the currently stored value.

    These context values are position-dependent. (For example, values stored for a start_rid_spec can be used only as part of a start_rid_spec argument in a later command and not as part of an end_rid_spec).

    The stored values are updated by the commands as well. For example, if you select a data block, the stored value for the tid is updated to the subtable identifier of the data block, and the RowIDs are updated to the first and last physical row in the data block.

    Note: To use = to represent a stored value for a current command, you must have entered an input value for that parameter in a previous command, or issued a previous command which saved a value for that parameter. Use the SHOWDEFAULTS command to see the currently stored values. For more information, see “SHOWDEFAULTS” on page 209.