17.10 - PARTITION - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)

The PARTITION command displays granted and blocked partition-aware locks. These types of locks lock a range of row partitions.

Currently, the partition range is limited by default to a single row partition, except in the case of the DBC.AccessRights table, where a lock can be applied across a range of row partitions to a single rowhash or all rowhashes.

Syntax

{ PARTITION | PART } [
  DBname.Tablename TypeAndIndex |
  ALL
]

Syntax Elements

DBname
The name of a database.
Tablename
The name of a table in DBname.
TypeAndIndex
A subtable identifier.
A table is composed logically of one or more subtables. TypeAndIndex specifies one of these subtables. For example:
  • 0 is the table header.
  • hex 400 (decimal 1024) is a primary subtable.
  • hex values 404, 408, and 40C (decimal values 1028, 1032, and 1036), and other +4 incremental values, are secondary index subtables.
  • hex values 800, C00, and 1000 (decimal values 2048, 3072, and 4096), and other multiples of hex 400 (decimal 1024) are fallback subtables.
ALL
That all row-partitioned tables that have a lock applied to a range of partitions are considered.
ALL is the default if you do not specify an object name.

Usage Notes

The following table shows the components of partition command output.

Component... Specifies...
Tran currently running transactions with locks being applied.
Hash Locks Hash locks are not relevant to the PARTITION command.
Range Locks the total number of range locks applied by the transaction.
Partition Locks the number of partition range locks applied by the transaction.
Host the logical host ID (origin of the transaction).
Session the session number for the transaction.
Mode the type of lock mode:
  • Access
  • Read
  • Write
  • Exclusive
User the logon-ID for whom the lock is being requested.
Database the name of the database with granted or blocked locks.
Table the name of the table with granted or blocked locks.
PLLKind the type of partition-level lock that is applied. This only appears for locks on row-partitioned tables.
Subtable ID the identifier of the subtable to which the partition range lock is applied.
Partition1 and Partition2 the internal partition numbers that define the beginning and ending partitions in the range of locked partitions.
The partition range is currently limited to a single partition.

The internal partition number is part of the rowid of the physical row. It is displayed in hexadecimal as [[[hhhh.] hhhh.] [hhhh.hhhh]] or zero.

RowHash1 and RowHash2 the first and second portions of the row hash, representing the locked rowhash in the range of partitions.

Both decimal and hexadecimal numbers are accepted. A decimal number must be followed by a period.

In the following examples RPT1 and RPT2 are row-partitioned tables.

Examples: PARTITION command examples

This example shows:

  • a write lock on all rows in partition 0004 in a row-partitioned table.
  • a write lock on the row or rows having rowhash 1,17 in partition 0007 in a non-partitioned table.

     

    -> Please enter your selection from the list:
     partition
    - The following amps are available:
    
        0     1     2     3
    
    -> Which amp(s) do you want to request on (S=Sampling/A=all/C=cancel/Q=quit):
     1
    
    ---------------- AMP 1 REPORTS 2 LOCK ENTRIES -------------
    
                       GRANTED LOCK REQUEST(S):
    
    Tran: 30719 00015DCA
    Hash Locks     :          1
    
    Range Locks    :          6
    
    Partition Locks:          2
    
    Host:  1025 Session:     0, 1003 Mode: WR   User: EXAMPLE
    Database: EXAMPLE  Table: RPT1
    PLLKind:  IPN Partition Range         Subtable ID:  1024
    
                               : Partition1 :                0004 Row Hash1:     0,    0
                               : Partition2 :                0004 Row Hash2:     0,    0
    
    Host:  1025 Session:     0, 1003 Mode: WR   User: EXAMPLE
    Database: EXAMPLE  Table: RPT2
    PLLKind:  RowHash + IPN Part'n Range  Subtable ID:  1024
    
                                : Partition1 :                0007 Row Hash1:     1,   17
                                : Partition2 :                0007 Row Hash2:     1,   17
    
    
    
    -> Please enter your selection from the list:
     partition example.rpt1 400
    - The following amps are available:
    
        0     1     2     3
    
    -> Which amp(s) do you want to request on (S=Sampling/A=all/C=cancel/Q=quit):
     1
    
    ---------------- AMP 1 REPORTS 1 LOCK ENTRIES -------------
    
                       GRANTED LOCK REQUEST(S):
    
    Tran: 30719 00015DCA
    Hash Locks     :          1
    
    Range Locks    :          6
    
    Partition Locks:          1
    
    Host:  1025 Session:     0, 1003 Mode: WR   User: EXAMPLE
    Database: EXAMPLE  Table: RPT1
    PLLKind:  IPN Partition Range         Subtable ID:  1024
    
                                : Partition1 :                0004 Row Hash1:     0,    0
                                : Partition2 :                0004 Row Hash2:     0,    0
    
    
    
    -> Please enter your selection from the list:
     partition example.rpt2 400
    - The following amps are available:
    
        0     1     2     3
    
    -> Which amp(s) do you want to request on (S=Sampling/A=all/C=cancel/Q=quit):
     1
    
    ---------------- AMP 1 REPORTS 1 LOCK ENTRIES -------------
    
                       GRANTED LOCK REQUEST(S):
    
    Tran: 30719 00015DCA
    Hash Locks     :          1
    
    Range Locks    :          6
    
    Partition Locks:          1
    
    Host:  1025 Session:     0, 1003 Mode: WR   User: EXAMPLE
    Database: EXAMPLE  Table: RPT2
    PLLKind:  RowHash + IPN Part'n Range  Subtable ID:  1024
    
                               : Partition1 :                0007 Row Hash1:     1,   17
                               : Partition2 :                0007 Row Hash2:     1,   17