Data Set Options for Relational Databases

TPT_RESTART= Data Set Option

Specifies that a failed Fastload, MultiLoad, or Multi-Statement run that used the TPT API is being restarted.

Default value:	NO
Valid in:	PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Teradata

Syntax
Syntax Description
Examples
See Also

Syntax

TPT_RESTART=YES | NO

Syntax Description

YES: specifies that the load process is being restarted.
NO: specifies that the load process is not being restarted.

Details

To use this option, you must first set TPT=YES. This option is valid only when using the TPT API.

SAS can restart from checkpoints any Fastload, MultiLoad, and Multi-Statement insert that are run using the TPT API. The restart procedure varies: It depends on whether checkpoints were recorded and in which phase the step failed during the load process. The error message in the log is extremely important and contains instructions on how to restart.

Here are the rules that govern restart.

The TPT API does not return a checkpoint value when FastLoad records a checkpoint. Therefore, you need not set TPT_CHECKPOINT_VALUE= when you use FastLoad. Set TPT_RESTART= instead.
If the default error table name, work table name, or restart table name is overridden, SAS must use the same name while restarting the load process.
Teradata loads data in two phases: the acquisition phase and the application phase. In the acquisition phase, data transfers from SAS to Teradata. After this phase, SAS has no more data to transfer to Teradata. If failure occurs after this phase, set TPT_APPL_PHASE=YES while restarting. (Multi-Statement insert does not have an application phase and so need not be restarted if it fails after the acquisition phase.) Use OBS=1 for the source data set because SAS has already sent the data to Teradata, so there is no need to send any more data.
If failure occurred before the acquisition phase ended and the load process recorded no checkpoints, you must restart the load process from the beginning by setting TPT_RESTART=YES. However, you need not set TPT_CHECKPOINT_VALUE= because no checkpoints were recorded. The error message in the SAS log provides all needed information for restart.

Examples

In this example, assume that the MultiLoad step that uses the TPT API fails before the acquisition phase ends and no options were set to record checkpoints.

libname x teradata user=testuser pw=testpw;
data test;In 
do i=1 to 100;
output;
end;
run;

/* Set TPT=YES is optional because it is the default. */
data x.test(MULTILOAD=YES TPT=YES);
set test;
run;

This error message is sent to the SAS log. You need not set TPT_CHECKPOINT_DATA= because no checkpoints were recorded.

ERROR:  Teradata connection:  Correct error and restart as an APPEND process 
with option TPT_RESTART=YES.  Since no checkpoints were taken, 
if the previous run used FIRSTOBS=n, use the same value in the restart.

Here is an example of the restart step.

proc append data=test base=x.test(MULTILOAD=YES TPT=YES TPT_RESTART=YES);
run;

In this next example, failure occurs after checkpoints are recorded.

libname tera teradata user=testuser pw=testpw;
/* Create data */
data testdata;
do i=1 to 100;
	output;
end;
run;
/* Assume that this step fails after loading row 19. */
data x.test(MULTISTMT=YES CHECKPOINT=3);
set testdata;
run;

Here is the resulting error when it fails after loading 18 rows.

ERROR:  Teradata connection:  Correct error and restart as an APPEND process 
with option TPT_RESTART=YES. If the previous run used FIRSTOBS=n, 
use the value ( n-1+ 19) for FIRSTOBS in the restart.  Otherwise use FIRSTOBS=19. 
Also specify TPT_CHECKPOINT_DATA= 18.

You can restart the failed step with this code.

proc append base=x.test(MULTISTMT=YES TPT_RESTART=YES 
    TPT_CHECKPOINT_DATA=18) data=test(firstobs=19);
run;

If failure occurs after the end of the acquisition phase, you must write a custom C++ program to restart from the point where it stopped.

Here is a sample SAS program that failed after the acquisition phase and the resulting error message.

libname x teradata user=testuser pw=testpw;
data x.test(MULTILOAD=YES TPT=YES CHECKPOINT=7);
do i=1 to 20;
output;
end;
run;

ERROR:  Teradata connection:  Failure occurred after the acquisition phase. 
Restart outside of SAS using checkpoint data  14.

Set TPT_APPL_PHASE=YES to restart when failure occurs in the application phase because SAS has already sent all data to Teradata.

proc append base=x.test(MULTILOAD=YES TPT_RESTART=YES 
    TPT_CHECKPOINT_DATA=14 TPT_APPL_PHASE=YES) data=test(obs=1);
run;

You must always use TPT_CHECKPOINT_DATA= with TPT_RESTART= for MultLoad and Multi-Statement insert.