17.10 - Example: Using the PopulateFsysInfoTable Macro to Populate a Pre-Existing Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Administration
Programming Reference
Publication ID
B035-1210-171K
Language
English (United States)

On a Teradata Database Release 16.00 system, you can use the PopulateFsysInfoTable_TD15 macro to populate a pre-existing table, or alter tables to Teradata Database 16.00, and then populate using the updated PopulateFsysInfoTable.

An example for the successful output on the SHOWBLOCKS target table:

SHOW TABLE SystemInfo.showb_s;   <-- This table is an existing table created in Teradata Database 15.0/15.10. 

CREATE SET TABLE SystemInfo.showb_s ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      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 );

BTEQ -- Enter your SQL request or BTEQ command:

exec alterfsysinfotable_td16('SystemInfo','showb_s','showblocks','s');

exec alterfsysinfotable_td16('SystemInfo','showb_s','showblocks','s');   <-- Macro successfully completed. 

BTEQ -- Enter your SQL request or BTEQ command:

show table SystemInfo.showb_s;   <-- This same table has the column ‘MapName’ at the end of the original table definition. 


CREATE SET TABLE SystemInfo.showb_s ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      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,
      MapName VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC)   <--MapName column was created. 
PRIMARY INDEX ( TheDate ,TheTime ,TableID ,CompressionState );

An example for the successful output on the SHOWWHERE target table:

SHOW TABLE SystemInfo.showw_m;

SHOW TABLE SystemInfo.showw_m;

CREATE SET TABLE SystemInfo.showw_m ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      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 SMALLINT,
      Vproc SMALLINT,
      Drive INTEGER,
      LrgCyls BIGINT,
      SmlCyls BIGINT,
      TableType VARCHAR(12) CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL,
      Grade VARCHAR(6) CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL,
      GradePct DECIMAL(5,2),
      VHCyls BIGINT,
      HotCyls BIGINT,
      WarmCyls BIGINT,
      ColdCyls BIGINT,
      CylsinSSD BIGINT,
      TemperaturesMature VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( TheDate ,TheTime ,TableID ,TableIDTAI ,LrgCyls ,
SmlCyls );


BTEQ -- Enter your SQL request or BTEQ command:
exec alterfsysinfotable_td16('SystemInfo','showw_m','showwhere','m');

exec alterfsysinfotable_td16('SystemInfo','showw_m','showwhere','m');

*** Procedure has been executed.
*** Total elapsed time was 5 seconds.


BTEQ -- Enter your SQL request or BTEQ command:
show table SystemInfo.showw_m;

show table SystemInfo.showw_m;

*** Text of DDL statement returned.
*** Total elapsed time was 1 second.


CREATE SET TABLE SystemInfo.showw_m ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      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 SMALLINT,
      Vproc SMALLINT,
      Drive INTEGER,
      LrgCyls BIGINT,
      SmlCyls BIGINT,
      TableType VARCHAR(12) CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL,
      Grade VARCHAR(6) CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL,
      GradePct DECIMAL(5,2),
      VHCyls BIGINT,
      HotCyls BIGINT,
      WarmCyls BIGINT,
      ColdCyls BIGINT,
      CylsinSSD BIGINT,
      TemperaturesMature VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
       MapName VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC) 
PRIMARY INDEX ( TheDate ,TheTime ,TableID ,TableIDTAI ,LrgCyls ,
SmlCyls );

If the new populated macro is used without altering the table, the following error displays:

BTEQ -- Enter your SQL request or BTEQ command:
exec dbc.PopulateFsysInfoTable('CallData', 'Weekly', 'showblocks', 's', 'SystemInfo', 'SHOWB_Short' );

exec dbc.PopulateFsysInfoTable('CallData', 'Weekly', 'showblocks', 's', 'SystemInfo', 'SHOWB_Short' );
*** Failure 3813 POPULATEFSYSINFOTABLESP:The positional assignment list has too many values.
*** Total elapsed time was 4 seconds.