REPEAT - 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

This control enables users to specify the maximum number of times the next SQL request is to be submitted.

Syntax



where the following is true:

n
The number of times to submit the next request.

If a value is not specified, the default is 1, which voids the repeat function and submits the next request one time.

*
The next request is to be submitted continuously until the import file runs out of data.
RECSr
An integer in the range of 1..2251636603879500.
p
Overrides the SET PACK command setting for the duration of the repeat.
REQBUFLEN
Overrides the REQBUFLEN setting of the PACK command for the duration of the repeat.
REQBUFLEN takes one numeric argument (b) in the range of 1000000 to 7340032. See the PACK description for more details

Usage Notes

The REPEAT command is typically used with Teradata SQL requests that contain a USING clause. Each time the request is submitted, it uses the next data row from the input data stream. The REPEAT command is cancelled if the input file cannot be accessed. The REPEAT command only executes once (for each session) if it is followed by a syntactically incorrect SQL request .

The definition of request is very critical to this command. The following sequence, for example, submits only the BT (BEGIN TRANSACTION) statement 10 times, because BTEQ interprets the BEGIN TRANSACTION statement as a complete, single-statement request.

.REPEAT 10
BT; 
select ... ;
select ... ;
select ... ;
ET;

If a sequence is entered like this by mistake, be sure to enter nine extra END TRANSACTION statements. Otherwise, Teradata SQL treats any requests submitted until log off as part of an unfinished transaction, all of which rolls back when log off occurs.

To repeat the entire transaction 10 times, use:

.REPEAT 10 
BT 
;select ... 
;select ... 
;select ... 
;ET;

In this case, because of the placement of the semicolons, BTEQ interprets all of the statements in the transaction as one multi-statement request.

The REPEAT command is not valid in a Teradata SQL macro.

PACK Clause

The syntax of the REPEAT command can be extended to include the PACK clause.

Usage Notes for PACK Clause

The REPEAT command's PACK clause overrides the SET PACK command setting for the duration of the repeat. Once the repeat is over, the pack factor returns to the value associated with SET PACK use.

See the PACK command, PACK, for more usage details

Example 1 – REPEAT PACK Clause

.REPEAT * PACK 100

This equates to:

Repeat the request as many times as possible before reaching eof (or max n) and pack up to 100 records with each request. The number of records sent is determined at REPEAT completion. The number of records packed might vary for each request sent.

Example 2 – REPEAT RECS Clause

To ensure an exact number of records are transferred, use the RECS clause version for the repeat factor to compensate for any “reduced” requests. For example,

.REPEAT RECS 200 PACK 100

This equates to:

Repeat the request as many times as necessary to read up to 200 records and pack a maximum of 100 records with each request.

REPEAT and = Command Differences

The REPEAT command is similar to the = command, with the following differences:
  • Use the REPEAT command before the request to be repeated, and the = command after the request.
  • The REPEAT command specifies the total number of times to submit the following request. The = command specifies how many more times to resubmit the last request.
  • The REPEAT command can resubmit a request repetitively until the import file is exhausted; the = command cannot.

Using the REPEAT Command with the COMPILE Command

The COMPILE command is processed only once when used with the REPEAT command, regardless of the value of n. The n value defaults to 1.

REPEAT Error Handling

Errors encountered within a REPEAT are processed as follows:

Request Result
Can be re-executed BTEQ retries the request, as if it were supplied singly.
Cannot be re-executed BTEQ proceeds to the next request and continues processing.

Using REPEAT When QUIET is ON

Using the REPEAT command when the QUIET command option is set to ON and a single session is logged on, BTEQ displays a message indicating the estimated execution time after each statement. For example, if specifying .REPEAT 10, BTEQ returns 10 messages indicating estimated time. However, if multiple sessions are logged on, BTEQ displays only the time the first statement was specified and the time the last statement executed. Using QUIET ON ALL has a similar effect, regardless of how many sessions are used.

Running Multiple Sessions With REPEAT

When running multiple sessions with the REPEAT command, specify the REPEAT command with the number of sessions before the Teradata SQL DATABASE statement.

To run three concurrent sessions that repeat the Teradata SQL INSERT request as many times as needed, specify:

.SET SESSIONS 3
.LOGON 0/fml,fml
.REPEAT 3
DATABASE Workforce;
.REPEAT *
USING num(SMALLINT)
INSERT INTO Department (DeptNo) VALUES (:num) ;

The first REPEAT command specifies the default database for all three sessions. The second REPEAT command runs the three sessions until all data is inserted. If the first REPEAT command is not specified, only the first session uses the Workforce database. The other two sessions do not have a database specified.

Alternatively, the default database can be specified within the Teradata SQL command that specifies a table name. For example:

.SET SESSIONS 3
.LOGON 0/fml,fml
.REPEAT *
USING num(SMALLINT)
INSERT INTO Workforce.Department (DeptNo) VALUES (:num) ;

In this example, the default database Workforce is used with the table name in the Teradata SQL INSERT statement. This example requires only one REPEAT command.

If deadlocks occur when updating a table with a join index in Teradata mode (using BEGIN TRANSACTION and END TRANSACTION with multiple sessions), ANSI mode with COMMIT is an alternative.

ANSI Mode

In ANSI mode, a session opens its transaction by its first SQL statement. The transaction is closed completely by sending either a COMMIT or ROLLBACK statement on the session. If the .SESSION and .REPEAT commands are used in ANSI mode, the COMMIT statement has to be sent along with the repeated SQL statement as one request. For example:

.SESSION trans ansi
.SESSIONS 10
.logon TPID/USERID, PASSWD
.import data file = <data file name>
.repeat *
using i(integer), j(integer)
insert into <table name> (col1, col2)
values (:i,:j); COMMIT WORK;
.quit

If the repeated request is sent without the COMMIT statement, one of the requests is blocked by other sessions, and BTEQ will hang due to a deadlock.

Example 3 – SQL SELECT before REPEAT

The following example shows a series of SQL SELECT statements used first without a REPEAT (Repeat Off heading), and then repeated twice (Repeat 2 heading):

database workforce;
.defaults
.format on
.heading 'Repeat Off'
select   deptname
,loc
,deptno
from     department
order by deptno     ;
.defaults    
.format on    
.heading 'Repeat 2'
.repeat 2
select   deptname
,loc         ,deptno     from     department
order by deptno
;    
.format off
.logoff
.exit

BTEQ Response

 ** Query completed. 5 rows found. 3 columns returned.
                            Repeat Off 
   
DeptName          Loc   DeptNo
--------------    ---   ------
Administration    NYC      100
Exec Office       NYC      300
Engineering       ATL      500
Manufacturing     CHI      600
Marketing         NYC      700
*** Starting at Mon Jul 30 11:15:31 1990 
*** Query completed. 5 rows found. 3 columns returned.
                            Repeat 2 
   
DeptName         Loc   DeptNo
---------------  ---   ------
Administration   NYC      100
Exec Office      NYC      300
Engineering      ATL      500
Manufacturing    CHI      600
Marketing        NYC      700
*** Starting at Mon Jul 30 11:15:31 1990 
*** Query completed. 5 rows found. 3 columns returned.
                            Repeat 2 
   
DeptName         Loc   DeptNo
---------------  ---   ------
Administration   NYC      100
Exec Office      NYC      300
Engineering      ATL      500
Manufacturing    CHI      600
Marketing        NYC      700
*** Query completed. 5 rows found. 3 columns returned.
                            Repeat 2
   
DeptName         Loc   DeptNo
--------------   ---   ------
Administration   NYC      100
Exec Office      NYC      300
Engineering      ATL      500
Manufacturing    CHI      600
Marketing        NYC      700
*** Query completed. 5 rows found. 3 columns returned.
                            Repeat 2
   
DeptName         Loc   DeptNo
--------------   ---   ------
Administration   NYC      100
Exec Office      NYC      300
Engineering      ATL      500
Manufacturing    CHI      600
Marketing        NYC      700
*** Finished at Mon Jul 30 11:15:32 1990
*** Total number of statements: 2, Accepted: 2, Rejected: 0
*** Total elapsed time was 1 second.
*** Total requests run successfully = 2
*** Successful requests per second =  1.688