Specifies the checkpoint data to return to Teradata when restarting a failed MultiLoad or Multi-Statement step that uses the TPT API.
| Valid in: | DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software) |
|---|---|
| Category: | Bulk Loading |
| Default: | none |
| Requirement: | To use this option, you must first specify TPT=YES and TPT_RESTART=YES. |
| Data source: | Teradata |
| See: | BULKLOAD= data set option, Maximizing Teradata Load and Read Performance, MULTILOAD= data set option, MULTISTMT= data set option, TPT= LIBNAME option, TPT_APPL_PHASE= data set option, TPT_RESTART= data set option, Using the TPT API |
Table of Contents
specifies the value to use to restart a failed MultiLoad or Multi-Statement step that uses the TPT API.
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.
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=myusr1 pw=mypwd1;
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 do not need to specify 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=myusr1 pw=mypwd1;
/* 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=myusr1 pw=mypwd1;
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;