16.20 - Example: Populate a File System Information Table for SHOWCOMPRESS Output, Option 'L' - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Administration
Programming Reference
Publication ID
B035-1210-162K
Language
English (United States)

Create table:

create multiset table Call_Logs.Daily_Log as dbc.tvm with data;

Create data table that will contain SHOWCOMPRESS data:

exec 
     createfsysinfotable('Call_Logs'
		   ,'showcTrgTableLong'
		   ,'perm'
		   ,'y'
		   ,'showcompress'
		   ,'l'
		   ,'TD_Map1');

To populate data into a target table using the stored procedure:

exec 
   populatefsysinfotable('Call_Logs'
     		,'Daily_Log'
     		,'showcompress'
     		,'l'
     		,'Call_Logs'
     		,'showcTrgTableLong');

*** Procedure has been executed. 

To display data for the long display output using bteq with the .foldline and .sidetitles formatting qualifiers:

exec populatefsysinfotable('Call_Logs','Daily_Log','showcompress','l','','');

 *** Procedure has been executed. 
 *** Warning: 3212 The stored procedure returned one or more result sets.
 *** Total elapsed time was 2 seconds.

 *** ResultSet# 1 : 9 rows returned by "SYSLIB.POPULATEFSYSINFOTABLESP".

               Date  17/12/08
               Time  07:11:01
            DB Name  Call_Logs
           Tbl Name  Daily_log
                TID  0000BF270000
            TID_TAI      -1
     TLA BLC Option  DEFAULT
  System BLC Option  MANUAL
        TLA BLC ALG  DEFAULT
     System BLC ALG  ZLIB
      TLA BLC Level  DEFAULT
   System BLC Level  6
           Map Name  TD_MAP1
         Comp State  COMPRESSED
          Comp Algo  IPPZLIB
         Comp Level  6
Exact Comp Ratio(%)    85.38
Exact (%) of Blocks    91.17
  Exact (%) of Data    88.28
      Exact Used GB
      
               Date  17/12/08
               Time  07:11:01
            DB Name  Call_Logs
           Tbl Name  Daily_log
                TID  0000BF270000
            TID_TAI      -1
     TLA BLC Option  DEFAULT
  System BLC Option  MANUAL
        TLA BLC ALG  DEFAULT
     System BLC ALG  ZLIB
      TLA BLC Level  DEFAULT
   System BLC Level  6
           Map Name  TD_MAP1
         Comp State  UNCOMPRESSED
          Comp Algo  N/A
         Comp Level  N/A
Exact Comp Ratio(%)
Exact (%) of Blocks     8.82
  Exact (%) of Data    11.71
      Exact Used GB
      
               Date  17/12/08
               Time  07:11:01
            DB Name  Call_Logs
           Tbl Name  Daily_log
                TID  0000BF270000
            TID_TAI       0
     TLA BLC Option  DEFAULT
  System BLC Option  MANUAL
        TLA BLC ALG  DEFAULT
     System BLC ALG  ZLIB
      TLA BLC Level  DEFAULT
   System BLC Level  6
           Map Name  TD_MAP1
         Comp State  UNCOMPRESSED
          Comp Algo  N/A
         Comp Level  N/A
Exact Comp Ratio(%)
Exact (%) of Blocks   100.00
  Exact (%) of Data   100.00
      Exact Used GB
      
               Date  17/12/08
               Time  07:11:01
            DB Name  Call_Logs
           Tbl Name  Daily_log
                TID  0000BF270000
            TID_TAI    1024
     TLA BLC Option  DEFAULT
  System BLC Option  MANUAL
        TLA BLC ALG  DEFAULT
     System BLC ALG  ZLIB
      TLA BLC Level  DEFAULT
   System BLC Level  6
           Map Name  TD_MAP1
         Comp State  COMPRESSED
          Comp Algo  IPPZLIB
         Comp Level  6
Exact Comp Ratio(%)    87.31
Exact (%) of Blocks   100.00
  Exact (%) of Data   100.00
      Exact Used GB
      
               Date  17/12/08
               Time  07:11:01
            DB Name  Call_Logs
           Tbl Name  Daily_log
                TID  0000BF270000
            TID_TAI    1028
     TLA BLC Option  DEFAULT
  System BLC Option  MANUAL
        TLA BLC ALG  DEFAULT
     System BLC ALG  ZLIB
      TLA BLC Level  DEFAULT
   System BLC Level  6
           Map Name  TD_MAP1
         Comp State  UNCOMPRESSED
          Comp Algo  N/A
         Comp Level  N/A
Exact Comp Ratio(%)
Exact (%) of Blocks   100.00
  Exact (%) of Data   100.00
      Exact Used GB
      
               Date  17/12/08
               Time  07:11:01
            DB Name  Call_Logs
           Tbl Name  Daily_log
                TID  0000BF270000
            TID_TAI    1032
     TLA BLC Option  DEFAULT
  System BLC Option  MANUAL
        TLA BLC ALG  DEFAULT
     System BLC ALG  ZLIB
      TLA BLC Level  DEFAULT
   System BLC Level  6
           Map Name  TD_MAP1
         Comp State  UNCOMPRESSED
          Comp Algo  N/A
         Comp Level  N/A
Exact Comp Ratio(%)
Exact (%) of Blocks   100.00
  Exact (%) of Data   100.00
      Exact Used GB
      
               Date  17/12/08
               Time  07:11:01
            DB Name  Call_Logs
           Tbl Name  Daily_log
                TID  0000BF270000
            TID_TAI    2048
     TLA BLC Option  DEFAULT
  System BLC Option  MANUAL
        TLA BLC ALG  DEFAULT
     System BLC ALG  ZLIB
      TLA BLC Level  DEFAULT
   System BLC Level  6
           Map Name  TD_MAP1
         Comp State  COMPRESSED
          Comp Algo  IPPZLIB
         Comp Level  6
Exact Comp Ratio(%)    87.31
Exact (%) of Blocks   100.00
  Exact (%) of Data   100.00
      Exact Used GB
      
               Date  17/12/08
               Time  07:11:01
            DB Name  Call_Logs
           Tbl Name  Daily_log
                TID  0000BF270000
            TID_TAI    2052
     TLA BLC Option  DEFAULT
  System BLC Option  MANUAL
        TLA BLC ALG  DEFAULT
     System BLC ALG  ZLIB
      TLA BLC Level  DEFAULT
   System BLC Level  6
           Map Name  TD_MAP1
         Comp State  COMPRESSED
          Comp Algo  IPPZLIB
         Comp Level  6
Exact Comp Ratio(%)    54.00
Exact (%) of Blocks   100.00
  Exact (%) of Data   100.00
      Exact Used GB
      
               Date  17/12/08
               Time  07:11:01
            DB Name  Call_Logs
           Tbl Name  Daily_log
                TID  0000BF270000
            TID_TAI    2056
     TLA BLC Option  DEFAULT
  System BLC Option  MANUAL
        TLA BLC ALG  DEFAULT
     System BLC ALG  ZLIB
      TLA BLC Level  DEFAULT
   System BLC Level  6
           Map Name  TD_MAP1
         Comp State  COMPRESSED
          Comp Algo  IPPZLIB
         Comp Level  6
Exact Comp Ratio(%)    61.00
Exact (%) of Blocks   100.00
  Exact (%) of Data   100.00
      Exact Used GB