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