TIMEMSG Command | Basic Teradata Query - TIMEMSG (TM)

Purpose

This control enables users to specify whether any elapsed or total query time messages are to be printed.

Syntax

Shorthand Syntax (Interactive Mode Only)
Command	Shorthand
TIMEMSG	TM

Option	Shorthand	Option	Shorthand	Option	Shorthand
DEFAULT	D	QUERY	Q	NONE	N

Usage Notes

DEFAULT is the initial default value.

The DEFAULTS command resets TIMEMSG setting to DEFAULT on workstation-attached systems, but it does not affect TIMEMSG setting on mainframe-attached systems.

Use the TIMEMSG DEFAULT command as the default where no Total Time message prints.

Use the TIMEMSG QUERY command to request that an additional Total Query Time message is printed, after all information associated with the request is received.

Use the TIMEMSG NONE command to suppress elapsed time messages for submitted database requests.

If using the combination of SET TIMEMSG QUERY and SET RETLIMIT (for ROWS), the following three time messages appear:

The Elapsed Time message which appeared previously.
An additional Elapsed Time message, which is printed when the RETLIMIT value for rows is engaged.
The Total Query Time message which is printed at completion.

The Total Query Time message includes the time that is returned in the first Elapsed Time message that appears.

Example 1 – TIMEMSG

To print the total time of a specified request, type the following:

.SET TIMEMSG QUERY

Example 2 – TIMEMSG Instigated By a Macro

The Example 1 TIMEMSG command in an SQL macro appears as follows:

ECHO '.SET TIMEMSG QUERY';

Example 3 – TIMEMSG

The following example depicts the default for a REPEATED SQL request:

.IMPORT DATA FILE = myfile.imp;
    .REPEAT 4 ;
USING ( i integer, c char(1) )
INSERT INTO mytable values (:i,:c);

Output produced:

+---------+---------+---------+---------+---------+---------+-.
.IMPORT DATA FILE = myfile.imp;
+---------+---------+---------+---------+---------+---------+-.
.REPEAT 4 ;
+---------+---------+---------+---------+---------+---------+-.
USING ( i integer, c char(1) )
INSERT INTO mytable values (:i,:c);
*** Starting record 1 at Wed Jan 17 17:00:35 2007
*** Insert completed. One row added.
*** Total elapsed time was 1 second.
*** Insert completed. One row added.
*** Total elapsed time was 1 second.
*** Insert completed. One row added.
*** Total elapsed time was 1 second.
*** Insert completed. One row added.
*** Total elapsed time was 1 second.
*** Finished at record 4 at Wed Jan 17 17:00:35 2007
*** Total number of statements: 4,  Accepted : 4,  Rejected : 0
*** Total elapsed time was 1 second.
*** Total requests sent to the DBC = 4
*** Successful requests per second =  4.000

To suppress the generation of elapsed time messages, use the TIMEMSG NONE command prior to using the IMPORT command as follows:

.SET TIMEMSG NONE;
.IMPORT DATA FILE = myfile.imp;
.REPEAT 4 ;
USING ( i integer, c char(1) )
INSERT INTO mytable values (:i,:c);

Output with elapsed time messages suppressed ...

+---------+---------+---------+---------+---------+---------+-.
.SET TIMEMSG NONE;
+---------+---------+---------+---------+---------+---------+-.
.IMPORT DATA FILE = myfile.imp;
+---------+---------+---------+---------+---------+---------+-.
.REPEAT 4 ;
+---------+---------+---------+---------+---------+---------+-.
USING ( i integer, c char(1) )
INSERT INTO mytable values (:i,:c);
*** Starting record 1 at Wed Jan 17 17:00:35 2007
*** Insert completed. One row added.
*** Insert completed. One row added.
*** Insert completed. One row added.
*** Insert completed. One row added.
*** Finished at record 4 at Wed Jan 17 17:00:35 2007
*** Total number of statements: 4,  Accepted : 4,  Rejected : 0
*** Total requests sent to the DBC = 4
*** Successful requests per second =  4.000

To further suppress the Activity Count messages, use QUIET ON in addition to TIMEMSG NONE as follows:

.SET QUIET ON;
SET TIMEMSG NONE;
.IMPORT DATA FILE = myfile.imp;
.REPEAT 4 ;
USING ( i integer, c char(1) )
INSERT INTO mytable values (:i,:c);

Output suppresses elapsed time message along with activity count message:

+---------+---------+---------+---------+---------+---------+-.
.SET QUIET ON;
*** Type QUIET OFF; to resume output.
+---------+---------+---------+---------+---------+---------+-.
.SET TIMEMSG NONE;
+---------+---------+---------+---------+---------+---------+-.
.IMPORT DATA FILE = myfile.imp;
+---------+---------+---------+---------+---------+---------+-.
.REPEAT 4 ;
+---------+---------+---------+---------+---------+---------+-.
USING ( i integer, c char(1) )
INSERT INTO mytable values (:i,:c);
*** Starting record 1 at Wed Jan 17 17:05:29 2007
*** Finished at record 4 at Wed Jan 17 17:05:29 2007
*** Total number of statements: 4,  Accepted : 4,  Rejected : 0
*** Total requests sent to the DBC = 4
*** Successful requests per second =  4.000