MESSAGEOUT - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
16.20
Published
October 2018
Language
English (United States)
Last Update
2020-02-20
dita:mapPath
kil1527114222313.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

Purpose

Specifies the name of a file to which BTEQ is to redirect the contents that would normally get sent to the standard output stream.

Syntax

The following figures show the syntax for both mainframe-attached and workstation-attached systems.





where the following is true:

xxx
The name of the z/OS JCL DD or workstation file name to receive data that is normally sent to the standard output stream.
BOM|NOBOM
For workstation-attached systems, determines if a BOM (Byte Order Mark) is inserted at the beginning of a new MESSAGEOUT file for Unicode sessions.

Specifying BOM inserts a UTF-8 or UTF-16 byte order mark, while NOBOM does not. The default is BOM.

This option does not apply if the file already exists and is non-empty. It is not available for non-Unicode sessions or for mainframe-attached systems.

This option does not apply to mainframe-attached systems because standard output is written in EBCDIC for Unicode sessions.

RESET
Redirect the standard output back to the original stdout/SYSPRINT stream. This may be a terminal screen, a redirected file, a pipe, or some other device. Upon successful completion, BTEQ closes the previous MESSAGEOUT file.

Usage Notes

If specifying an existing file, BTEQ appends data to that file. Otherwise, a new file is created. Use any device name that is valid on the system as a file name.

BTEQ closes the MESSAGEOUT file when one of the following is executed:
  • A MESSAGEOUT command specifying another message file
  • A MESSAGEOUT command specifying the RESET option
  • An EXIT or QUIT command

BTEQ does not close the file until the last file record is written. If the file is read before it is closed, the last few records might be missing. They are added when the file is closed in response to the MESSAGEOUT, EXIT, or QUIT command.

For workstation-attached systems, when BTEQ is started with the -m command line option, stdio is based on the system locale. Therefore, the MESSAGEOUT file contains locale-specific characters (instead of UTF-8 or UTF-16 encoded characters) and does not contain a BOM.

If a MESSAGEOUT command fails in batch mode, BTEQ will print an error message and terminate. If a MESSAGEOUT command fails during interactive mode, BTEQ will continue to write its standard output to the previous MESSAGEOUT file, if it exists. Otherwise, BTEQ will continue to write to the stdout/SYSPRINT stream.

Example 1 – MESSAGEOUT

For z/OS, to redirect messages to the DDNAME MYOUT use the following command:

.MESSAGEOUT DDNAME=MYOUT

Where DDNAME was previously defined with a TSO command. For example:

.TSO ALLOCATE DDNAME(MYOUT) DSNAME(YOUR.MSG.OUT) SHR

Example 2 – MESSAGEOUT

From a workstation-attached system, to redirect standard output to the file OUT1, use the following command:

.MESSAGEOUT FILE=OUT1
In order to restore standard output back to the stdout stream and close the OUT1 file, use the following command:
.MESSAGEOUT RESET

Example 3 – MESSAGEOUT

From a workstation-attached system, part of BTEQ's output can be discarded by sending it to /dev/null via the MESSAGEOUT command. Since an import normally generates a few lines of output for each record imported, the result could be a large output file. The standard output can be reduced by redirecting it to /dev/null and then restoring it with the RESET option.

Script commands:
.MESSAGEOUT FILE=/dev/null
.IMPORT DATA FILE=mydata
.REPEAT *
USING c1 (VARCHAR(100)), c2 (VARCHAR(100)), c3 (VARCHAR(100))
INSERT INTO my_table1 VALUES (:c1, :c2, :c3);
.MESSAGEOUT RESET
SELECT 'Validate Inserts', count(*) from my_table1;
Relevant output:
+---------+---------+---------+---------+---------+---------+

.MESSAGEOUT FILE=/dev/null
*** Redirecting output messages to file /dev/null
+---------+---------+---------+---------+---------+---------+
SELECT 'Validate Inserts', count(*) from my_table1;

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

'Validate Inserts'     Count(*)
------------------  -----------
 Validate Inserts          53285

+---------+---------+---------+---------+---------+---------+