Data Set Options for Relational Databases

TPT_CHECKPOINT_DATA= Data Set Option

Specifies the checkpoint data to return to Teradata when restarting a failed MultiLoad or Multi-Statement step that uses the TPT API.

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

Syntax
Syntax Description
Details
Examples
See Also

Syntax

TPT_CHECKPOINT_DATA=checkpoint_data_in_error_message

Syntax Description

checkpoint_data_in_error_message: specifies the value to use to restart a failed MultiLoad or Multi-Statement step that uses the TPT API.

Details

To use this option, you must first set TPT=YES and TPT_RESTART=YES.

SAS can restart from the last checkpoint any failed Fastload, MultiLoad, and Multi-Statement insert that are run using the TPT API. Teradata returns a checkpoint value each time MultiLoad or Multi-Statement records a checkpoint. The SAS log contains this value when a load fails. SAS must provide the same value as a data set option when it tries to restart the load process.

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(FASTLOAD=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;