REPEAT
Purpose
Submits the next Teradata SQL request a specified number of times.
Syntax
where:
Syntax Element |
Specification |
n |
The number of times to submit the next request. For workstation-attached systems, 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. |
RECS r |
An integer in the range of 1.2251636603879500. |
p |
Overrides the SET PACK command setting for the duration of the repeat. |
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 SELECT statement.
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
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” on page 252, for more usage details
Example
.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
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:
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
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
*** 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