16.20 - Example: Populate a File System Information Table for SHOWBLOCKS Output, Option 'L' - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Administration
Programming Reference
Publication ID
B035-1210-162K
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','showbtrgttablelong','perm','y','showblocks','l', 'TD_Map1');

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

show table Call_Logs.showbtrgttablelong;
CREATE SET TABLE Call_Logs.showbtrgttablelong,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 NOTNULL,
	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),
	DBSize INTEGER,
	DBsPerSize BIGINT,
	PctOfDBsInSubtable DECIMAL(5,2),
	MinNumRowsPerDB INTEGER,
	AvgNumRowsPerDB INTEGER,
	MaxNumRowsPerDB INTEGER,
	LrgCyls BIGINT,
	SmlCyls BIGINT)
	MapName VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL
PRIMARY INDEX ( TheDate ,TheTime ,TableID ,TableIDTAI ,DBSize ,
PctOfDBsInSubtable );
BTEQ -- Enter your SQL request or BTEQ command:

The next example shows the BTEQ output from running PopulateFsysInfoTable macro with the L 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. This example shows seven rows of output. The first column of each row is the Date column.
exec populatefsysinfotable('Call_Logs', 'Daily_Log','showblocks','l','','');
 *** Procedure has been executed.
 *** Warning: 3212 The stored procedure returned one or more result sets.
 *** Total elapsed time was 27 seconds.

*** ResultSet# 1 : 7 rows returned by "SYSLIB.POPULATEFSYSINFOTABLESP".
                 Date 13/12/22
                 Time 21:46:49
             DB//Name Call_Logs
            Tbl//Name Daily_Log
          TID               0000E90A0000
      TID//TAI              0
         Comp//Method MANUAL
          Comp//State N
   Est Comp//Ratio(%)
   Est Uncomp//DBS(%)
               DBSize           2
        DBs Per//Size                    4
% Of DBs In//SubTable  100.00
     Min Rows//Per DB                    1
     Avg Rows//Per DB                    1
     Max Rows//Per DB                    1
             Lrg Cyls                    4
             Sml Cyls
             Map//Name
                 Date 13/12/22
                 Time 21:46:49
             DB//Name Call_Logs
            Tbl//Name Daily_Log
          TID               0000E90A0000
      TID//TAI              1024
         Comp//Method MANUAL
          Comp//State
   Est Comp//Ratio(%)
   Est Uncomp//DBS(%)
               DBSize           0
        DBs Per//Size
% Of DBs In//SubTable
     Min Rows//Per DB
     Avg Rows//Per DB
     Max Rows//Per DB
             Lrg Cyls                    4
             Sml Cyls
             Map//Name
                 Date 13/12/22
                 Time 21:46:49
             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(%)
               DBSize          96
        DBs Per//Size                    1
% Of DBs In//SubTable   12.50
     Min Rows//Per DB                 2221
     Avg Rows//Per DB                 2221
     Max Rows//Per DB                 2221
             Lrg Cyls
             Sml Cyls
             Map//Name
                 Date 13/12/22
                 Time 21:46:49
             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(%)
               DBSize          98
        DBs Per//Size                    1
% Of DBs In//SubTable   12.50
     Min Rows//Per DB                 2263
     Avg Rows//Per DB                 2263
     Max Rows//Per DB                 2263
             Lrg Cyls
             Sml Cyls
             Map//Name
                 Date 13/12/22
                 Time 21:46:49
             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(%)
               DBSize          99
        DBs Per//Size                    1
% Of DBs In//SubTable   12.50
     Min Rows//Per DB                 2297
     Avg Rows//Per DB                 2297
     Max Rows//Per DB                 2297
             Lrg Cyls
             Sml Cyls
             Map//Name
                 Date 13/12/22
                 Time 21:46:49
             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(%)
               DBSize         102
        DBs Per//Size                    1
% Of DBs In//SubTable   12.50
     Min Rows//Per DB                 2359
     Avg Rows//Per DB                 2359
     Max Rows//Per DB                 2359
             Lrg Cyls
             Sml Cyls
             Map//Name
                 Date 13/12/22
                 Time 21:46:49
             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(%)
               DBSize         254
        DBs Per//Size                    4
% Of DBs In//SubTable   50.00
     Min Rows//Per DB                 5907
     Avg Rows//Per DB                 5907
     Max Rows//Per DB                 5907
             Lrg Cyls
             Sml Cyls
             Map//Name