Example: Macro For DELETE - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
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.