15.00 - PopulateFsysInfoTablePopulateFsysInfoTable_ANSI - Teradata Database

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: CLASSPERM CLASSJRNL CLASSREDRIVE CLASSGLOBALTEMP CLASSSPOOL CLASSWAL CLASSALL (shows all of the above-mentioned classes of tables) 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: S includes the following information for the primary data subtable of the specified tables: A histogram of block sizes The minimum, average, and maximum block size per subtable Block compression information (status, estimated compression ratio, estimated uncompressed) M includes the same information as S, but shows these statistics for all subtables of the specified tables. L includes the following information for each block size category of each subtable of the specified tables: Number of blocks the minimum, average, and maximum number of rows per data block size Block compression information (status, estimated compression ratio, estimated percent uncompressed 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