15.00 - PopulateFsysInfoTablePopulateFsysInfoTable_ANSI - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

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