Previous Page | Next Page

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.


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.


See Also

Maximizing Teradata Load Performance

Using the TPT API

BULKLOAD= LIBNAME Option

BULKLOAD= Data Set Option

MULTILOAD= Data Set Option

MULTISTMT= Data Set Option

TPT= LIBNAME Option

TPT= Data Set Option

TPT_APPL_PHASE= Data Set Option

TPT_CHECKPOINT_DATA= Data Set Option

Previous Page | Next Page | Top of Page