例: PopulateFsysInfoTableマクロを使用して、既存のテーブルにデータを入力 - Teradata Database - Teradata Vantage NewSQL Engine - AlterFsysInfoTable_TD16 AlterFsysInfoTable_ANSI_TD16の例。

Teradata Vantage™ SQL演算子およびユーザー定義関数

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
2019年3月
Language
日本語
Last Update
2019-10-30
dita:mapPath
ja-JP/drp1544241916620.ditamap
dita:ditavalPath
ja-JP/drp1544241916620.ditaval
dita:id
B035-1210
Product Category
Software
Teradata Vantage

Teradata Database Release 16.00システムでは、PopulateFsysInfoTable_TD15マクロを使用して既存のテーブルに入力すること、またはテーブルをTeradata Database 16.00に変更してから更新されたPopulateFsysInfoTableを使用して入力することができます。

SHOWBLOCKSターゲット テーブルでの正常な出力の例:

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 );

SHOWWHEREターゲット テーブルでの正常な出力の例:

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 );

テーブルを変更することなく新しく入力されたマクロを使用すると、次のエラーが表示されます。

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.