- input_database_name
- The database containing the tables for which to generate file system information, depending on the command SHOWBLOCKS, SHOWCOMPRESS, SHOWOFSSPACE, 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 to generate information.
- input_table_name
- The name of the table for which to generate file system information.You must have appropriate privileges to select data from the tables in the input database.
- input_class
- A class of tables for which to generate file system information.
- 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 preceding classes of tables)
- CLASSOFSSPOOL (supported for SHOWOFSSPACE only)
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 usage. - SHOWBLOCKS
- Displays statistics about data block size, number of rows per data block, and information about the compression status of data blocks and tables.
- 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.
- SIncludes 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, see SQL SHOWBLOCKS and SQL SHOWWHERE Orange Book, 541-0010699.The column names of the output when redirected to a target table and 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 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, and so on).
- TableIDTAI: Shows the TypeAndIndex part of the TableID.The S output is 1024 (= 0x400, the primary subtable 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, are never compressed, and therefore have N in the compression status column.
- When created. Data Blocks 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 must again try and compress the result.
- If all DBDs of the primary subtable of the table have the COMPRESSIBLE flag set, the CompressionState is C. If the table is eligible for compression, and at least one but not all DBDs have the COMPRESSIBLE flag set, the compression status is set to PC to indicate that the table is partially compressed. This can happen in the following situations:
- A populated table whose BLOCKCOMPRESSION attribute was changed and no IMMEDIATE clause was given.
- A partitioned primary index table where not all partitions are heavily used.
- 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, compressed and uncompressed lengths are recorded, so the following formula computes the compression ratio 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 compression state/info. If these blocks are compressed then the estimated value for the corresponding column is displayed as 0%.
- PctDBsIn1to8 (and other similar PctDBsIn XtoY 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 PctDBsIn XtoY 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. 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 and 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 can 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.
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.
- LIncludes 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
The L option produces the Compression-related columns that the 'M' option produces and 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 optional 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 for each output row because the number of Data Blocks (DBsPerSize) for a given DBSize may not occupy a complete cylinder. Fractional parts in the cylinder count are not represented. If the LrgCyls or SmlCyls column has a rounded number of cylinders for each DBSize row, the sum of all cylinders for a given subtable is magnified 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 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. Vantage does not create Data Blocks with a size of 0 sectors.
- S
- SHOWCOMPRESS
- Displays compression information for tables on the system.
- 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, see SQL SHOWBLOCKS and SQL SHOWWHERE Orange Book, 541-0010699.The column names of the output when redirected to a target table and 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 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, and so on).
- 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), 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, a value from 1 to 9 that is meaningful only when the compression algorithm is ZLIB or IPPZLIB.
- 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.
- LThe 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:
- There are data blocks below BLC compression threshold levels.
- There are data blocks too small to be compressed.
- There are subtables that are never compressed.
Description of SHOWCOMPRESS SQL Output Columns for L Display
The L option produces the Compression-related columns the S option produces and 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 too.
- CompressionAlgorithm: Information from 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, ELZ_S, ELZS_H, IPPZLIB, ISAL, and N/A (when compression state is either compressible or uncompressed). This information is displayed at the table and subtable level.
- CompressionLevel: Information from 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 is 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 (a percentage). 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.
- ExactPctofBlocks: In this column, SQL SHOWCOMPRESS indicates the percentage of blocks specified Compression State, Algorithm, and Level. The percentage is calculated after checking if each data block is compressed, compressible, or uncompressed. This information is presented at both the table and subtable level.
- ExactPctofData: This column provides the percentage of the total data that is either compressed using a specific algorithm, compressible, or uncompressed.
- ExactUsedGB: This column provides the exact used size for each compression algorithm (ZLIB, ELZS_H, ISAL, 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 specific compression algorithm/compressible/uncompressed must be equal to the value at the table level for the corresponding compression algorithm/compressible/uncompressed.
- S
- SHOWWHERE
- Displays information about cylinder allocation and temperature.
- 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.
- S
- Description of SHOWWHERE SQL Output Columns
- For more information about the output of this macro, see SQL SHOWBLOCKS and SQL SHOWWHERE Orange Book, 541-0010699.The column names of the output when redirected to a target table and 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: 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 contains blanks 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.
- '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.
- 'L' – The DRIVE number is displayed. 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, and 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 in the grade as indicated by the Grade column, 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 on the frequency of access to the cylinders (the frequency of reads/writes to the cylinders), TVS classifies the temperature for the cylinders 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 may be older than expected. As of Teradata Database 15.10, this field has zeros when the output of the PopulateFsysInfoTable macro is directed to a target table and has 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 meaningful only if temperatures on the system are mature. That is, if the system has been in use long enough to gather meaningful statistics. A 'Y' in this column indicates mature temperatures and an 'N' indicates otherwise. If temperatures are not mature, use the system longer before using of the results of the SHOWWHERE SQL.
- SHOWOFSSPACE
- Shows current spool space allocation on the Object Store. Note: Due to the transient nature of spool, smaller spool tables might not be shown, as those are only present for a very short period of time and hence might not be detected by this command.
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.
Only the short (‘S’) option is supported for display_opt.
Description of SHOWOFSSPACE SQL Output Columns for S Display
- UserName: Name of the User or Database associated with the spool table. The User or Database name can be up to 128 characters long but here in case of long name, name is truncated to 25chars long. Spool tables generally have a generic name associated with the user or database that created it such as "DBC.DBC". Because of that, the TableID is often more useful.
- TableID: Similar to SHOWBLOCKS SQL output, the TableID column in the SHOWOFSSPACE SQL output shows the Unique0, Unique1 values in byte-flipped format and zeros for TypeAndIndex. This matches the common Teradata TableID format. The TableID can also be compared to other places where TableIDs are printed (checktable, log messages, and so on).
- ParentQueryID: Short for Parent Query Identifier. This column is used for the parent query identifier that generated the spool table.
- ParentUserID: Short for Parent User Identifier. This column is for the parent user’s system identifier.
- HostID: Short for Host Identifier. The host information that generated the spool table is displayed.
- SessionNumber: The session number that generated the spool table will be displayed.
- Request Number: The request number in that session will be displayed.
- TotalSpaceMB: This column is used to display the total estimated size of the spool table, in MBs
- 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 is 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 is 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.
- input_database_name
- The database containing the tables for which to generate file system information, depending on the command SHOWBLOCKS, SHOWCOMPRESS, SHOWOFSSPACE, 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 to generate information.
- input_table_name
- The name of the table for which to generate file system information.You must have appropriate privileges to select data from the tables in the input database.
- input_class
- A class of tables for which to generate file system information.
- SHOWBLOCKS
- Displays statistics about data block size, number of rows per data block, and information about the compression status of data blocks and tables.
- SHOWCOMPRESS
- Displays compression information for tables on the system.
- SHOWWHERE
- Displays information about cylinder allocation and temperature.
- SHOWOFSSPACE
- Shows current spool space allocation on the Object Store. Note: Due to the transient nature of spool, smaller spool tables might not be shown, as those are only present for a very short period of time and hence might not be detected by this command.
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.
Only the short (‘S’) option is supported for display_opt.
Description of SHOWOFSSPACE SQL Output Columns for S Display
- UserName: Name of the User or Database associated with the spool table. The User or Database name can be up to 128 characters long but here in case of long name, name is truncated to 25chars long. Spool tables generally have a generic name associated with the user or database that created it such as "DBC.DBC". Because of that, the TableID is often more useful.
- TableID: Similar to SHOWBLOCKS SQL output, the TableID column in the SHOWOFSSPACE SQL output shows the Unique0, Unique1 values in byte-flipped format and zeros for TypeAndIndex. This matches the common Teradata TableID format. The TableID can also be compared to other places where TableIDs are printed (checktable, log messages, and so on).
- ParentQueryID: Short for Parent Query Identifier. This column is used for the parent query identifier that generated the spool table.
- ParentUserID: Short for Parent User Identifier. This column is for the parent user’s system identifier.
- HostID: Short for Host Identifier. The host information that generated the spool table is displayed.
- SessionNumber: The session number that generated the spool table will be displayed.
- Request Number: The request number in that session will be displayed.
- TotalSpaceMB: This column is used to display the total estimated size of the spool table, in MBs
- target_database_name
- The database in which the target table was created by the CreateFsysInfoTable macro.
- target_table_name
- The name of the table which was created by the CreateFsysInfoTable macro in the target database to hold file system information.