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.
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