PopulateFsysInfoTable
PopulateFsysInfoTable_ANSI
Purpose
Generates file system data to a table created by the CreateFsysInfoTable macro.
Note: If you use ANSI session mode, use the _ANSI form of the macro.
Syntax
Syntax element … |
Specifies … |
input_database_name |
the database containing the tables for which file system data block histogram information will be generated. Note: You must have appropriate privileges to select data from the tables in the input database. If you specify database DBC, you can use the input_class argument to select a class of tables for which information will be generated. |
input_table_name |
the name of the table for which file system information will be generated. Note: You must have appropriate privileges to select data from the tables in the input database. |
input_class |
a class of tables for which file system information will be generated. Valid values for input_class are: You can only specify a class of tables if you specify the DBC database for input_database_name. |
display_opt |
the level of file system detail that will be generated and populated into the target table. Valid values for display_opt are S, M, and L: includes the following information for the primary data subtable of the specified tables: includes the same information as S, but shows these statistics for all subtables of the specified tables. includes the following information for each block size category of each subtable of the specified tables: Note: The display option chosen for PopulateFsysInfoTable must match the display option that was used for the corresponding execution of the CreateFsysInfoTable macro. |
target_database_name |
the database into which the table will be created. If this argument and target_table_name are empty strings, the output will be directed to the computer screen. Note: You must have appropriate privileges to create or insert into tables in the target database. Calling the CreateFsysInfoTable CreateFsysInfoTable_ANSI is unnecessary if the macro is called with empty strings for the database and table names. |
target_table_name |
the name of the table that will be created to hold file system information. If this argument and target_database_name are empty strings, the output will be directed to the computer screen. Note: You must have appropriate privileges to insert into target_table_name in the target database. If you created target_table_name in the target database, then you already have implicit INSERT privileges on the target table. Calling the CreateFsysInfoTable CreateFsysInfoTable_ANSI is unnecessary if the macro is called with empty strings for the database and table names. |
Authorization
In addition to the privileges mentioned above, to run this macro you must have EXECUTE privileges on the PopulateFsysInfoTable macro or on the database containing the macro.
Usage Notes
The macro name and arguments are case-insensitive.
Example : Populate a File System Information Table for Short Display
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:
createfsysinfotable('visuserdata','showbtrgttableshort','perm','y','showblocks','s');
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 visuserdata.showbtrgttable;
CREATE SET TABLE visuserdata.showbtrgttableshort,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)
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.
Note: 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.
Note: 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('visuser','test3','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 visuser
Tbl//Name test3
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
Example : Populate a File System Information Table for Medium Display
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('visuserdata','showbtrgttablemedium','perm','y',
'showblocks','m');
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 visuserdata.showbtrgttablemedium;
CREATE SET TABLE visuserdata.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,
PctDBsIn1025to1034 BYTEINT,
PctDBsIn1035to1632 BYTEINT,
PctDBsIn1633to2048 BYTEINT,
MinDBSize INTEGER,
AvgDBSize INTEGER,
MaxDBSize INTEGER,
TotalNumDBs BIGINT,
LrgCyls BIGINT,
SmlCyls BIGINT)
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.
Note: 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 two rows of output. The first column of each row is the Date column.
Note: 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('visuser','test3','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 visuser
Tbl//Name test3
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
Date 13/12/22
Time 21:46:12
DB//Name visuser
Tbl//Name test3
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
Example : Populate a File System Information Table for Long Display
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('visuserdata','showbtrgttablelong','perm','y','showblocks','l');
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 visuserdata.showbtrgttablelong;
CREATE SET TABLE visuserdata.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)
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.
Note: 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('visuser','test3','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 visuser
Tbl//Name test3
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
Date 13/12/22
Time 21:46:49
DB//Name visuser
Tbl//Name test3
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
Date 13/12/22
Time 21:46:49
DB//Name visuser
Tbl//Name test3
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
Date 13/12/22
Time 21:46:49
DB//Name visuser
Tbl//Name test3
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
Date 13/12/22
Time 21:46:49
DB//Name visuser
Tbl//Name test3
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
Date 13/12/22
Time 21:46:49
DB//Name visuser
Tbl//Name test3
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
Date 13/12/22
Time 21:46:49
DB//Name visuser
Tbl//Name test3
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