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

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','showbtrgttablemedium','perm','y',
'showblocks','m', 'TD_Map1');

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

show table Call_Logs.showbtrgttablemedium;
CREATE SET TABLE Call_Logs.showbtrgttablemedium ,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,
	PctDBsIn1025to1304 BYTEINT,
	PctDBsIn1305to1632 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 ,TableIDTAI ,CompressionState);
BTEQ -- Enter your SQL request or BTEQ command:

The next example shows the BTEQ output from running PopulateFsysInfoTable macro with the M 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.

exec populatefsysinfotable('Call_Logs', 'Daily_Log','showblocks','m','','');
 *** Procedure has been executed.
 *** Warning: 3212 The stored procedure returned one or more result sets.
 *** Total elapsed time was 32 seconds.

*** ResultSet# 1 : 2 rows returned by "SYSLIB.POPULATEFSYSINFOTABLESP".
                Date 13/12/22
                Time 21:46:12
                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(%)
                   % Of DBs In//1 to 8//Sects 100
                   % Of DBs In//9 to 24//Sects
                   % Of DBs In//25 to 64//Sects
                   % Of DBs In//65 to 120//Sects
                   % Of DBs In//121 to 168//Sects
                   % Of DBs In//169 to 216//Sects
                   % Of DBs In//217 to 256//Sects
                   % 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           2
               Avg DB Size           2
               Max DB Size           2
               Total DBs             4
               Lrg Cyls              4
               Sml Cyls
               Map//Name
               Date 13/12/22
               Time 21:46:12
               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
                  % OfDBs 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