Example: Macro For DELETE - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

The following macro structures the SELECT and DELETE statements as a single multistatement request:

     CREATE MACRO res_use
           (from_date (DATE,       DEFAULT DATE),
            to_date   (DATE,       DEFAULT DATE),
            from_time (INTEGER,    DEFAULT 0),
            to_time   (INTEGER,    DEFAULT 999999),
            proc      (VARCHAR(4), DEFAULT 'P'),
            secs      (SMALLINT,   DEFAULT 600) )
        AS (SELECT
             the_date (TITLE 'Resource//TheDate'),
             the_time (TITLE 'Utilized//TheTime'),
             proc,
             AVERAGE(hits) (TITLE 'Avg//Hits'),
             MAXIMUM(cpu)  (TITLE 'Max//CPU'),
             AVERAGE(cpu)  (TITLE 'Avg//CPU'),
             MAXIMUM(disk) (TITLE 'Max//Disk'),
             AVERAGE(disk) (TITLE 'Avg//Disk'),
             MAXIMUM(host) (TITLE 'Max//Host'),
             AVERAGE(host) (TITLE 'Avg//Host'),
             MAXIMUM(chan) (TITLE 'Max//Chan'),
             AVERAGE(chan) (TITLE 'Avg//Chan')
             FROM DBC.res_use_view
             GROUP BY the_date, the_time, proc
             WHERE the_date BETWEEN :from_date AND :to_date
             AND   the_time BETWEEN :from_time AND :to_time
             AND   proc CONTAINS :proc
             AND   secs EQ :secs
             ORDER BY proc, the_date, the_time

           ;DELETE FROM res_use_view ALL;);

If the preceding macro is executed in Teradata session mode, but not within the boundaries of an explicit transaction, fastpath delete processing is used for the DELETE statement. See Unconstrained Fastpath Delete Processing.

In ANSI session mode, the system uses fastpath delete processing for the DELETE statement when a COMMIT is included in the macro or when a COMMIT is specified at the end of the request line, and immediately following the execution of the macro, within the request.

For example, the last line of the preceding macro would read as follows:

     DELETE FROM res_use_view ALL ;
     COMMIT;) ;

The system uses slow path processing for the DELETE statement when no COMMIT is stated in the macro itself or within the request that executes the macro.