Creating and Using Macros - Basic Teradata Query

Additional Database Privileges

To create or use a macro the following additional database privileges are required, which are granted by the owner of the database.

The CREATE MACRO privilege.

Access to the appropriate database elements. A user cannot create a macro to access a database table that the user does not have permission to access.

Executing Commands

BTEQ executes commands and SQL statements in the same order they were entered. Each BTEQ command must be enclosed in a Teradata SQL ECHO statement. When the macro is executed, Teradata Database executes the Teradata SQL statements and returns the contents of the ECHO statements (the BTEQ commands) to BTEQ.

Note: It is recommended to echo BTEQ commands only in Field Mode, otherwise unexpected results might occur. No error messages are issued, and the echoed text might be dumped rather than executed.

Creating a Macro

To create a macro, enter the Teradata SQL CREATE MACRO and ECHO commands in an interactive BTEQ session as follows:

   CREATE MACRO macroname AS

   ( ECHO  '.BTEQcommand'

     ;Teradata SQLrequest;

);

Available Commands for Teradata SQL Macros

Table 1: BTEQ Commands for Teradata SQL Macros
BTEQ Command	Available for Teradata SQL Macro
AUTOKEYRETRIEVE	Yes
COMPILE	No
DECIMALDIGITS	Yes
DEFAULTS	Yes
ECHOREQ	No
ENCRYPTION	Yes
ERRORLEVEL	Yes
ERROROUT	Yes
EXIT	No
EXPORT	No
EXPORTEJECT	Yes
FOLDLINE	Yes
FOOTING	Yes
FORMAT	Yes
FORMCHAR	Yes
FULLYEAR	Yes
GOTO	No
HANG	Yes
HEADING	Yes
HELP BTEQ	Yes
IF...THEN...	Yes
IMPORT	No
INDICATORMODE	Yes
LABEL	No*
LARGEDATAMODE	Yes
LOGDATA	No
LOGMECH	No
LOGOFF	No
LOGON	No
LOGONPROMPT	Yes
MAXERROR	Yes
NOTIFY	No
NULL	Yes
OMIT	Yes
OS	Yes
PACK	Yes
PAGEBREAK	Yes
PAGELENGTH	Yes
QUIET	Yes
QUIT	No
RECORDMODE	Yes
REMARK	Yes
REPEAT	No
REPEATSTOP	Yes
REPORTALIGN	Yes
RETCANCEL	Yes
RETLIMIT	Yes
RETRY	Yes
RTITLE	Yes
RUN	No
SEPARATOR	Yes
SESSION CHARSET	Yes
SESSION RESPBUFLEN	Yes
SESSION SQLFLAG	No*
SESSION TRANSACTION	No*
SESSION TWORESPBUFS	Yes
SESSIONS	No*
SHOW	Yes
SHOW CONTROLS	Yes
SHOW ERRORMAP	Yes
SHOW VERSIONS	Yes
SIDETITLES	Yes
SKIPDOUBLE	Yes
SKIPLINE	Yes
SUPPRESS	Yes
TDP	Yes
TIMEMSG	Yes
TITLEDASHES	Yes
TMSMMSG	No
TRIMTRAILINGSPACES	Yes
TSO	Yes
UNDERLINE	Yes
WIDTH	Yes
=	No

* Although it is possible to ECHO this command, the command fails as a result of other constraints. For example, all sessions must be logged off to use the SESSION command.

Macro Example

To create a macro named deptdisplay, for example, that includes the following BTEQ SEPARATOR command and Teradata SQL SELECT statement:

   .SET SEPARATOR ' | '

   SELECT * FROM department;

enter:

   CREATE MACRO deptdisplay AS

   ( ECHO '.SET SEPARATOR '' | '' '

   ;SELECT * FROM department;

);

Note: When enclosing single quotes within quotes, double the inner quotes so that each inner, single quote becomes two single quotes.

After creating the macro, BTEQ displays:

   *** Macro has been created.

   *** Total elapsed time was 3 seconds.

Note: Develop more complex macros by placing them in a file and running the file in a BTEQ batch job.

Executing Macros

Macros can be executed either interactively, or included in BTEQ scripts or input stream files.

To execute a macro interactively, use the Teradata SQL EXECUTE statement:

   EXECUTE macroname;

To execute the macro deptdisplay, enter:

   EXECUTE deptdisplay;

Including a Macro in a Script

Use the editor on the system to include a BTEQ macro in script or input stream file. To execute the deptdisplay macro from a script, for example, enter the Teradata SQL EXECUTE statement after the LOGON statement in the script:

   .LOGON tdpid/userid, password

   EXECUTE deptdisplay;

   .LOGOFF

Creating Reports with Macros

Create a report from a macro by instructing BTEQ in a prepared script or input stream file to export the data returned as a report. To do this, use one of the following options:

Create a macro on a Teradata Database and execute it from the script file. In this case, the macro contains the appropriate BTEQ commands to generate a report, and the script file contains the BTEQ EXPORT command, the Teradata EXECUTE macro statement, and other BTEQ commands. This is the preferred method.

Place the macro in the script file and create and execute it from there. This method creates the macro every time it executes the request, which is a major disadvantage.

Macro Example

The following is an example of a macro to produce a report called Department Information:

   CREATE MACRO deptdisplay AS (

   ECHO '.SET SEPARATOR " | " ';

   ECHO '.SET RTITLE "Department Information" ';

   ECHO '.SET FORMAT ON';

   SELECT * FROM department;);

Defining the ddname of the Export File

After creating the macro, use the appropriate syntax to define the ddname of the export file as follows:

Operating System

Export File Syntax

z/OS

//EXPORT EXEC TDSBTEQ,

// L='logon string'

//REPORT DD DSN=your.report.dataname,DISP=(,KEEP),

// DCB=(RECFM=FB,LRECL=80,BLKSIZE=6160),

// SPACE=(TRK,(5,5))

//SYSIN DD DISP=SHR,DSN=your.BTEQ.input

Note: On z/OS, if the TSO command is used to define the ddname of the export file in the input stream file, it is not necessary to define the ddname in the CLIST. The input stream might include a command similar to the following example:

   .TSO ALLOCATE DDNAME(repfile) DSNAME(your.report.dataname) SHR

An ALLOCATE statement must be used for TSO.

Running the Macro

With the macro is created and the export file defined, the following script exports the data returned as a report and executes the macro:

   .LOGON tdpid/userid, password

   .EXPORT REPORT DDNAME=report

   EXECUTE deptdisplay;

   .SET FORMAT OFF

   .EXPORT RESET

   .LOGOFF

Execute the z/OS JCL (from outside of BTEQ) to submit the script and execute the macro.

Report Example

   88/03/07        Department Information       Page 1

   DeptNo |  DeptName      | Loc | MgrNo

   ------    --------------  ---   -----

      500 |  Engineering   | ATL | 10012

      700 |  Marketing     | NYC | 10021

      300 |  Exec Office   | NYC | 10018

      600 |  Manufacturing | CHI | 10007

      100 |  Administration| NYC | 10011

Command Execution Messages

The sample macro produces the following command execution messages in the SYSPRINT file:

   .LOGON e/fml,

   *** Logon successfully completed.

   *** Total elapsed time was 3 seconds.

   .EXPORT REPORT FILE=report.out

   *** To reset export, type .EXPORT RESET

   .SET RTITLE 'Department Information'

   .SET FORMAT ON

   EXECUTE deptdisplay;

   *** Echo accepted.

   *** Total elapsed time was 3 seconds.

   *** Query completed. 5 rows found. 4 columns returned.

   .SET FORMAT OFF

   .EXPORT RESET

   *** Output returned to console.

   .LOGOFF

   *** You are now logged off from the DBC.

   .EXIT

Creating and Executing a Macro from a Script

The following example shows how to produce the same report by creating and executing the macro from the script file:

   .LOGON e/fml,notebook

   .EXPORT REPORT DDNAME=report

   CREATE MACRO deptdisplay AS (

   ECHO '.SET SEPARATOR';

   ECHO '.SET RTITLE "Department Information" ';

   ECHO '.SET FORMAT ON';

   SELECT * FROM department;);

   EXECUTE deptdisplay;

   .SET FORMAT OFF

   .EXPORT RESET

   .LOGOFF