Examples | Populate a File System Information Table | Teradata Vantage - 17.10 - Example: Populate a File System Information Table for SHOWBLOCKS Output, Option 'S' - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

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

As a first step, you can create a target table to store the file system information rows in.

Create the target table manually or by using the CreateFsysInfoTable macro:

exec createfsysinfotable('Call_Logs','showbtrgttableshort','perm','y','showblocks','s', 'TD_Map1');

The following is the definition of the target table for S display option when creating the target table for storing the file system information for SHOWBLOCKS:

show table Call_Logs.showbtrgttable;

CREATE SET TABLE Call_Logs.showbtrgttable,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     (
      TheDate DATE FORMAT 'YY/MM/DD',
      TheTime TIME(6),
      DataBaseName VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
      TableName VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
      TableID BYTE(6),
      TableIDTAI INTEGER,
      CompressionMethod CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
      CompressionState VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
      EstCompRatio DECIMAL(5,2),
      EstPctOfUncompDBs DECIMAL(5,2),
      PctDBsIn1to8 BYTEINT,
      PctDBsIn9to24 BYTEINT,
      PctDBsIn25to64 BYTEINT,
      PctDBsIn65to120 BYTEINT,
      PctDBsIn121to168 BYTEINT,
      PctDBsIn169to216 BYTEINT,
      PctDBsIn217to256 BYTEINT,
      PctDBsIn257to360 BYTEINT,
      PctDBsIn361to456 BYTEINT,
      PctDBsIn457to512 BYTEINT,
      PctDBsIn513to760 BYTEINT,
      PctDBsIn761to1024 BYTEINT,
      PctDBsIn1025to1034 BYTEINT,
      PctDBsIn1035to1632 BYTEINT,
      PctDBsIn1633to2048 BYTEINT,
      MinDBSize INTEGER,
      AvgDBSize INTEGER,
      MaxDBSize INTEGER,
      TotalNumDBs BIGINT,
      LrgCyls BIGINT,
      SmlCyls BIGINT,
      MapName VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL
)
PRIMARY INDEX ( TheDate ,TheTime ,TableID ,CompressionState );

The next example shows the BTEQ output from running PopulateFsysInfoTable macro with the S display option, and empty strings for target_database_name and target_table_name to force the output to display on screen.

The column titles names of the output change when redirected to the screen instead of using the target table to store the output. For example, the column name is “PctDBsIn1to8” in the target table and the corresponding column is “% Of DBs In 1 to 8” when the output is redirected to the screen.

The warning that is returned after PopulateFsysInfoTable macro is expected behavior when the output is displayed to the screen.

The BTEQ .foldline and .sidetitles commands have been used to cause the column titles of the returned data set to be displayed on the side, and the corresponding values of those columns to be shown beside the column titles.
The following PopulateFsysInfoTable() call example redirects the output/result to the screen (empty strings specified for database and table names). If inserting the output/result to a specific table, ensure the table was created with the CreateFsysInfoTable() macro and that the same /display_opt is specified in both the PopulateFsysInfoTable and CreateFsysInfoTable macro calls.
exec populatefsysinfotable('Call_Logs', 'Daily_Log','showblocks','s','','');
 *** Procedure has been executed.
 *** Warning: 3212 The stored procedure returned one or more result sets.
 *** Total elapsed time was 34 seconds.

*** ResultSet# 1 : 1 rows returned by "SYSLIB.POPULATEFSYSINFOTABLESP".
               Date 13/12/22
                Time 21:45:29
            DB//Name Call_Logs
           Tbl//Name Daily_Log
          TID               0000E90A0000
      TID//TAI              1024
        Comp//Method MANUAL
         Comp//State U
  Est Comp//Ratio(%)
  Est Uncomp//DBs(%)
      % Of DBs In//1 to 8//Sects
     % Of DBs In//9 to 24//Sects
    % Of DBs In//25 to 64//Sects
   % Of DBs In//65 to 120//Sects 50
  % Of DBs In//121 to 168//Sects
  % Of DBs In//169 to 216//Sects
  % Of DBs In//217 to 256//Sects 50
  % Of DBs In//257 to 360//Sects
  % Of DBs In//361 to 456//Sects
  % Of DBs In//457 to 512//Sects
  % Of DBs In//513 to 760//Sects
 % Of DBs In//761 to 1024//Sects
% Of DBs In//1025 to 1304//Sects
% Of DBs In//1305 to 1632//Sects
% Of DBs In//1633 to 2048//Sects
         Min DB Size          96
         Avg DB Size         176
         Max DB Size         254
           Total DBs                    8
            Lrg Cyls                    4
            Sml Cyls
            Map//Name