17.05 - Example: Macro For DELETE - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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.