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