TIMEMSG - Basic Teradata Query - 16.20

Basic Teradata Query Reference

prodname
Basic Teradata Query
vrm_release
16.20
category
Programming Reference
featnum
B035-2414-108K

Purpose

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

Syntax



Usage Notes

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 a Teradata 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