PopulateFsysInfoTable/PopulateFsysInfoTable_ANSI Macros | Teradata Vantage - PopulateFsysInfoTable/PopulateFsysInfoTable_ANSI - Advanced SQL Engine - Teradata Database

SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2023-04-27
dita:mapPath
qqu1556127655717.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1210
lifecycle
previous
Product Category
Teradata Vantage™

Purpose

Generates file system data to populate a table created by the CreateFsysInfoTable macro.

If you use ANSI session mode, use the _ANSI form of the macro.

Syntax

[DBC.] { PopulateFsysInfoTable | PopulateFsysInfoTable_ANSI } (
  { 'input_database_name', 'input_table_name', |
    'DBC', 'input_class'
  }
  { 'SHOWBLOCKS' | 'SHOWWHERE' | 'SHOWCOMPRESS' },
  'display_opt',
  'target_database_name',
  'target_table_name'
)
'input_database_name'
The database containing the tables for which file system information will be generated, depending on the command SHOWBLOCKS, SHOWCOMPRESS, or SHOWWHERE.

You must have appropriate privileges to select data from the tables in the input database.

If you specify database DBC, you can use the 'input_class' argument to select a class of tables for which information will be generated.

'input_table_name'
The name of the table for which file system information will be generated.
You must have appropriate privileges to select data from the tables in the input database.
'input_class'
A class of tables for which file system information will be generated.
You can specify an 'input_class' only when 'input_database_name' is DBC.
Valid values for 'input_class':
  • CLASSPERM
  • CLASSJRNL
  • CLASSREDRIVE
  • CLASSGLOBALTEMP
  • CLASSSPOOL
  • CLASSWAL
  • CLASSALL (shows all of the above-mentioned classes of tables)
'input_class' is only supported for SHOWWHERE. The 'input_class' option is not available if the macro is used to generate SHOWBLOCKS information, due to high resource utilization.
'SHOWBLOCKS'
Displays statistics about data block size, number of rows per data block, and information about the compression status of data blocks and tables. This output resembles Ferret SHOWBLOCKS command output.
display_opt is the level of file system detail generated and populated into the target table. The display option you specify for PopulateFsysInfoTable must match the display option used for the corresponding execution of the CreateFsysInfoTable macro.
  • S
    Includes the following information for the primary data subtable of the specified tables:
    • A histogram of block sizes.
    • The minimum, average, and maximum block size per subtable.
    • Block compression information (status, estimated compression ratio, estimated uncompressed).

    Description of SHOWBLOCKS SQL Output Columns for S Display

    For more information about the output of this macro, login to https://support.teradata.com and search for the following Teradata Orange Book:
    • Avadhanam, Prasad N, SQL SHOWBLOCKS and SQL SHOWWHERE in Teradata Database, Teradata Orange Book, 541-0010699.
    The column names of the output when redirected to a target table versus when results are displayed, are not the same; see the Orange Book for details.
    • TableID: The TableID column in the SHOWBLOCK SQL output shows Unique0, Unique1 in byte-flipped format, and zeros for TypeAndIndex. This matches the common Teradata Database TableID format, so joins of target tables to dictionary tables can be performed easily. The TableID can also be compared to other places where TableIDs are printed (checktable, log messages, etc.).
    • TableIDTAI: Shows the TypeAndIndex part of the TableID. In the ‘S’ display output, it is always 1024 (= 0x400, the primary subtable’s ID).
    • CompressionMethod: One of the table level attributes (TLA) is Block Level Compression (BLC) and this attribute can be used to allow or disallow BLC on a table. The CompressionMethod column shows the BLC TLA of the table for which the SHOWBLOCKS information is being requested. A table with BLC TLA of AUTOTEMP has AUTOTEMP for this column. If the BLC TLA is DEFAULT, then this column shows the system default BLC TLA at the time when the macro is invoked.
    • CompressionState: The CompressionState of a table can be either C (Fully Compressed), PC (Partially Compressed), U (Fully Uncompressed), or N (Not Compressible).
      • If the table is not eligible for compression, CompressionState is set to N for that table. For example, DBC dictionary tables, whose unique part of the TableID is less than 1000, will never get compressed; so, they will have N in the compression status column.
      • When Data Blocks are created they are represented in the File System index structure by Data Block Descriptor entries (DBDs). When DBDs are created for a table eligible for compression, the COMPRESSIBLE flag is set in the DBD. The flag implies that the file system tried to compress the corresponding Data Block and that subsequent modifications of the Data Block should again try and compress the result. There DBSCONTROL tunables that affect whether the Data Block is actually stored in compressed format are MinDBSectsToCompress and MinPercentCompReduction.
      • If all DBDs of the primary subtable of the table have the COMPRESSIBLE flag set, then the CompressionState is ‘C’. If the table is eligible for compression, but some DBDs have the COMPRESSIBLE flag set and some do not, then the compression status is set to ‘PC’ to indicate that the table is partially compressed. This can happen in the following situations:
        • An aborted FERRET COMPRESS or UNCOMPRESS operation.
        • A populated table whose BLOCKCOMPRESSION attribute was changed and no IMMEDIATE clause was given.
        • A partitioned primary index table where some partitions are heavily used and some are not.
      • If the table is eligible for compression, but none of the primary subtable DBDs have the COMPRESSIBLE flag set, then the compression status for that table is marked as ‘U’.
    • EstCompRatio: This column shows the Estimated Compression Ratio for the table for which SHOWBLOCKS information is being extracted. For each available sector-size range of the Data Blocks, a sample of three Data Blocks is read from the Primary subtable blocks. In the compressed Data Block header, both compressed length and uncompressed lengths are recorded, so the compression ratio can easily be obtained using the following computation without having to do the trial compression.

      Compression ratio = {1‐ [size of sectors after compression / original uncompressed sector size ] } x 100

    • EstPctOfUncompDBs: In this column SQL SHOWBLOCKS indicates the estimated percentage of Blocks uncompressed of the table. For subtables marked with a ‘C’, the first three Data Blocks of each sector-size range are sampled and checked against their DBD’s compression state/info. If these blocks are actually compressed then the estimated value for the corresponding column is displayed as 0%.
    • PctDBsIn1to8 (and other similar PctDBsInXtoY columns): This column shows the percent of total Data Blocks whose size is in the range of 1 to 8 sectors (both inclusive). Likewise for the rest of the PctDBsInXtoY columns.
    • MinDBSize, AvgDBSize, and MaxDBSize: These columns respectively show the minimum size of the Data Blocks (in sectors), average size of the Data Blocks (in sectors) and maximum size of the data blocks (in sectors) for the specified table. The values are computed in each AMP as the cylinder indexes (CIs) are read and then aggregated across all AMPs.
    • TotalNumDBs: This value represents the total number of Data Blocks comprising the subtable identified by TableIDTAI. This value is counted in each AMP as the CIs are read and then aggregated across all AMPs.
    • LrgCyls, SmlCyls:
      • LrgCyls – The number of cylinders, in units of Large cylinders, that the table identified by the TableIDTAI occupies on the disk.
      • SmlCyls – The number of cylinders, in units of Small cylinders, that the table identified by the TableIDTAI occupies on the disk.

      This value is counted in each AMP as the CIs are read and then aggregated across all AMPs. For a given row, only one of the columns is filled in. Note, a cylinder can contain multiple subtables and multiple tables. Therefore, adding up the cylinder counts from each row of the ‘S’ display can exceed the actual total number of cylinders used.

  • M

    Includes the same information as S, but display the table name, map that the table uses for data distribution, and statistics for all subtables. For each subtable, display the BLOCKCOMPRESSION value set when the table was created or altered.

    Description of SHOWBLOCKS Output Columns for M Display

    The main difference between columns produced by the ‘S’ option versus the ‘M’ option is that ‘S’ produces block histogram details for only the primary subtable whereas ‘M’ produces the block histogram details for each subtable of the specified input. Additionally, for partially compressed subtables instead of a PC row, there are two separate C and U rows.

    Column values other than CompressionState are produced in the same fashion as for the ‘S’ option, for each of the rows. The decision to display either a C or U row is made on a subtable basis. For example, with a fallback table you could get one row for the primary subtable (assuming the primary is not compressed) and both a C row and a U row for fallback subtable if the fallback subtable is partially compressed.

    Note, a cylinder can contain multiple subtables and multiple tables. Therefore, adding up the cylinder counts from each row of the ‘M’ display can exceed the actual total number of cylinders used.

  • L
    Includes the following information for each block size category of each subtable of the specified tables:
    • Table name
    • Map that the table uses for data distribution
    • Number of blocks
    • The minimum, average, and maximum number of rows per data block size
    • Block compression information (status, estimated compression ratio, estimated percent uncompressed)
    • Statistics

    Description of SHOWBLOCKS SQL Output Columns for L Display

    In addition to the Compression related columns that the ‘M’ option produces, the ‘L’ option produces the DBSize, DBsPerSize, PctOfDBsInSubTable, MinNumRowsPerDB, AvgNumRowsPerDB,and MaxNumRowsPerDB columns for each subtable of the specified input. Because a row is generated for each different DB size, the columns referring to DB size ranges are not required and are not part of the L display.

    Zero DBSize row: In long display format, the total cylinder count columns LrgCyls and SmlCyls cannot be filled in for each output row since the number of Data Blocks (DBsPerSize) that exist for a given DBSize may not occupy a complete cylinder. Representing fractional parts in the cylinder count is not done. Also, if either LrgCyls or SmlCyls columns were filled in with a rounded off number of cylinders for each DBSize row, the sum total of all cylinders for a given subtable would be magnified way out of proportion. To avoid this issue, for each subtable in the long display, a special (extra) row with a DBSize of 0 is produced. In that particular row, after filling in the basic column values (for example, TheDate, TheTime, DatabaseName, TableName, TableID, TableIDTAI, and CompressionMethod), the cylinder count columns (LrgCyls or SmlCyls) are set to the total number of cylinders that the subtable occupies. All other columns in the row are 0 or blank depending on their data type. Note, Teradata Database does not create Data Blocks with a size of 0 sectors.

'SHOWCOMPRESS'
Displays compression information for tables on the system. This output resembles the Ferret SHOWCOMPRESS command output.
'display_opt' is the level of display detail generated. The display option you specify for PopulateFsysInfoTable must match the display option used for the corresponding execution of the CreateFsysInfoTable macro.
  • S

    The display option you specify for PopulateFsysInfoTable must match the display option used for the corresponding execution of the CreateFsysInfoTable macro. This is the default display.

    Description of SHOWCOMPRESS SQL Output Columns for S Display

    For more information about the output of this macro, login to https://support.teradata.com and search for the Teradata Orange Book:
    • Avadhanam, Prasad N, SQL SHOWBLOCKS and SQL SHOWWHERE in Teradata Database, Teradata Orange Book, 541-0010699.
      The column names of the output when redirected to a target table versus when results are displayed, are not the same; see the Orange Book for details.
    • TableID: The TableID column in the SHOWCOMPRESS SQL output shows Unique0, Unique1 in byte-flipped format, and zeros for TypeAndIndex. This matches the common Teradata Database TableID format. Therefore, joins of target tables to dictionary tables can be performed easily. The TableID can also be compared to other places where TableIDs are printed (checktable, log messages, etc.).
    • TableIDTAI: Shows the TypeAndIndex part of the TableID.
    • TLA_BLCOption: This column shows the table level attribute (TLA) of Block Level Compression Option (BLC Option). It shows the compression method of the table for which the SHOWCOMPRESS information was requested, such as AUTOTEMP, MANUAL, ALWAYS, or NEVER.
    • System_BLCOption: If the TLA BLC Option is DEFAULT, this column shows the system default BLC Option as AUTOTEMP, MANUAL, ALWAYS, or NEVER, at the time when the macro is invoked.
    • TLA_BLCALG: This column shows the table level attribute (TLA) of Block Level Compression Algorithm (BLC ALG) of the table for which the SHOWCOMPRESS information is being requested. The supported algorithms are ZLIB, IPPZLIB, and ELZS_H.
    • System_BLCALG: If the TLA BLC Algorithm is DEFAULT, then this column shows the system default BLC algorithm value as ZLIB, IPPZLIB, or ELZS_H, at the time when the macro is invoked.
    • TLA_BLCLevel: This column shows the table level attribute (TLA) of Block Level Compression Level (BLC Level) of the table for which the SHOWCOMPRESS information is being requested. It is meaningful only when the compression algorithm is ZLIB or IPPZLIB. It indicates the compression level from 1 to 9.
    • System_BLCLevel: If the TLA BLC Level is DEFAULT, then this column shows the system default BLC Level value at the time when the macro is invoked.
    • MapName: This column shows the name of the map used for the specified table.
  • L
    The display option you specify for PopulateFsysInfoTable must match the display option used for the corresponding execution of the CreateFsysInfoTable macro.
    This display option requires longer to display than the /S option because all of the data blocks of the tables in scope must be examined.
    The output shows blocks that are compressed, uncompressed, and disqualified. Disqualified blocks are those that did not qualify for compression for one of these reasons:
    • Some data blocks fall below BLC compression threshold levels, which are set in the Compression group of DBS Control fields.
    • Some data blocks are too small to be compressed.
    • Some special subtables are never compressed.

    Description of SHOWCOMPRESS SQL Output Columns for L Display

    In addition to the Compression related columns that the ‘S’ option produces, the ‘L’ option produces the CompressionState, CompressionAlgorithm, CompressionLevel, ExactCompRatio, ExactPctofBlocks, ExactPctofData, and ExactUsedGB columns for each subtable of the specified input.
    • CompressionState: This column provides the compression state of each subtable of the specified table. Possible values are COMPRESSED (DBs of the subtable are compressed), DISQUALIFIED (DBs of the subtable are compressible but not compressed) and UNCOMPRESSED (DBs of the subtable are neither compressible nor compressed). The information is summarized and provided at the table-level as well.
    • CompressionAlgorithm: This information is obtained by traversing each data block of subtable or table as there may be a possibility of multiple compression algorithms being used for a single subtable or table. Possible compression algorithms are ZLIB, ELZS_H, IPPZLIB, and N/A (when compression state is either compressible or uncompressed). This information is displayed at the table and subtable level.
    • CompressionLevel: This information is obtained by traversing every data block of the subtable or table as there may be a possibility of multiple compression levels for the same or different compression algorithm being used for a single subtable or table. In case of the same compression algorithm with a different compression level for a subtable or table, the range of values will be displayed. Possible compression levels are from 1 to 9 (when compression algorithm used is ZLIB or IPPZLIB) and N/A (otherwise). This information is displayed at the table and subtable level.
    • ExactCompRatio: This column shows the Exact Compression Ratio for the table for which SHOWCOMPRESS information is being extracted. This information is calculated after traversing each of the data blocks present in the compressed table. This information is presented at both the table and subtable level. It is displayed as a percentage.
    • ExactPctofBlocks: In this column, SQL SHOWCOMPRESS indicates the exact percentage of blocks that are in the specified Compression State, Algorithm, and Level. It is calculated after traversing each of the data blocks and checking whether it is compressed, compressible, or uncompressed. This information is presented at both the table and subtable level. It is displayed as a percentage.
    • ExactPctofData: This column provides the exact percentage of the total data that is either compressed using a particular algorithm, compressible, or uncompressed. It is displayed as a percentage.
    • ExactUsedGB: This column provides the exact used size for each compression algorithm (ZLIB, ELZS_H, and IPPZLIB) or exact used size for compressible or uncompressed data blocks in gigabytes. Summation of all the values within the subtable of a table for a particular compression algorithm/compressible/uncompressed should be equal to the value at the table level for the corresponding compression algorithm/compressible/uncompressed.
'SHOWWHERE'
Displays information about cylinder allocation and temperature. This output resembles the Ferret SHOWWHERE command output.
'display_opt' is the level of file system detail generated and populated into the target table. The display option you specify for PopulateFsysInfoTable must match the display option used for the corresponding execution of the CreateFsysInfoTable macro.
  • S

    Displays a summary listing of the cylinders showing one line for every cylinder type.

  • M

    Displays a medium length listing of the cylinders with one line for every cylinder type per AMP (vproc).

  • L

    Displays a long listing of the cylinders with one line for every cylinder type per AMP (vproc) per storage device.

Description of SHOWWHERE SQL Output Columns
For more information about the output of this macro, login to https://support.teradata.com and search for the Teradata Orange Book:
  • Avadhanam, Prasad N, SQL SHOWBLOCKS and SQL SHOWWHERE in Teradata Database, Teradata Orange Book, 541-0010699.
    The column names of the output when redirected to a target table versus when results are displayed, are not the same; see the Orange Book for details.
  • TableID: Similar to SHOWBLOCKS SQL output, the TableID column in the SHOWWHERE SQL output shows the Unique0, Unique1 values in byte-flipped format.
  • TableIDTAI: Currently there is no display option which produces SHOWWHERE rows for each subtable. The SHOWWHERE processing is done for the entire table. Therefore, the TableIDTAI column data is filled with zeros when the output is directed to the target table. The column has blanks in it when the output is directed for immediate results (to your screen, for example), such as if the target database/target table are not used when executing the PopulateFsysInfoTable macro.
  • Vproc: The content of this column depends on the display options specified.
    • ‘S’ – Because only a system-wide result is shown for each table or class of tables, a VPROC number is inappropriate, so -1 is assigned to Vproc.
    • ‘M’ and ‘L’ – The vproc number is assigned to Vproc.
  • Drive: The content of this column depends on the display options specified.
    • ‘S’ – Because only a system-wide result is shown for each table or class of tables, a DRIVE number is inappropriate, so DRIVE is -1 in this case.
    • ‘M’ – Because only the AMP-wide result is shown for each table or class of tables, a DRIVE number is inappropriate, so DRIVE is -1 in this case.
    • ‘L’ – The DRIVE number is displayed. Note, there may be more lines in the ‘L’ display if multiple drives/AMP are configured.
  • LrgCyls, SmlCyls:
    • LrgCyls – The number of cylinders, in units of Large cylinders, that the table identified by the TableID occupies on the disk for the given Grade.
    • SmlCyls – The number of cylinders, in units of Small cylinders, that the table identified by the TableID occupies on the disk for the given Grade.

    For a given row, only one of the columns is filled in.

  • TableType: Indicates the type of the source table for which the SHOWWHERE output rows are produced. Although WAL is not a table type, its cylinders are allocated separately from table cylinders, so they are reported separately.
  • Grade: TVS categorizes all cylinders into one of the three grades: FAST, MEDIUM or SLOW. For a given table, SHOWWHERE produces one row for each of these grades occupied by cylinders of the targets being displayed.
  • GradePct: GradePct represents the percent of cylinders of the table which are currently in the particular grade as indicated by the Grade column. It is computed based on the following formula:

    Total Cylinder of the table in the grade identified by the current row / Total Cylinder in the table

  • VHCyls: VHCyls represents the number of cylinders which are of the VERYHOT (VH) category.
  • HotCyls, WarmCyls, and ColdCyls: These are the number of HOT/WARM/COLD cylinders respectively. Depending upon the frequency of access to the cylinders (the frequency of reads/writes to the cylinders), TVS determines the temperature for the cylinders and accordingly classifies them as HOT, WARM, or COLD.
  • CylsinSSD: This column represents the number of cylinders that reside on the Solid State Device. There can be a lag between when the temperature category of a cylinder changes and when that cylinder is moved into or out of SSD. So, the corresponding temperature might be older than expected. As of Teradata Database 15.10, this field is not yet populated. It will have zeros when the output of the PopulateFsysInfoTable macro is directed to a target table and will have blanks when the output is directed for immediate results, such as if the target database/target table are not used when executing the macro.
  • TemperaturesMature: The temperature and grade information is only meaningful if temperatures on the system are mature, such that the system has been in use long enough to gather meaningful statistics. A ‘Y’ in this column indicates temperatures are mature and an ‘N’ indicates they are not mature. If temperatures are not mature, users are advised to use the system awhile longer before making use of the results of the SHOWWHERE SQL.
'target_database_name'
The database in which the target table was created by the CreateFsysInfoTable macro.
If this argument and 'target_table_name' are empty strings, the output will be directed to the computer screen.
You must have appropriate privileges to create or insert into tables in the target database.
Calling the CreateFsysInfoTable CreateFsysInfoTable_ANSI is unnecessary if the macro is called with empty strings for the database and table names
'target_table_name'
The name of the table which was created by the CreateFsysInfoTable macro in the target database to hold file system information.
If this argument and 'target_database_name' are empty strings, the output will be directed to the computer screen.

You must have appropriate privileges to insert into 'target_table_name' in the target database. If you created 'target_table_name' in the target database, then you already have implicit INSERT privileges on the target table.

Calling the CreateFsysInfoTable CreateFsysInfoTable_ANSI is unnecessary if the macro is called with empty strings for the database and table names.