Loading data from SAS to Teradata - When is it ready?

Question

When loading tables from SAS to Teradata, SAS loads the data (usually using the FASTLOAD facility) and then continues down the script. However, I often get critical errors because SAS says the data is loaded, but Teradata is still assembling the data within the table.

So the data is in the database, but not ready to be used. I have yet to find a way to know if the data is ready for processing with other tables. I have been successful at using a sleep command, but that is arbitrary and unreliable (because who knows how long it will take).

How would you fix this problem?

Answer 1

Could you sleep, try to query, catch any error and loop until ready?

Answer 2

Refining Jon's answer, you might want to look at querying whether the processing has ended.

I'm not familiar with Teradata, but supposedly there has to be a systems table listing active processes. It might possible to use pass through SQL to query get the current processes.

Answer 3

I would try the following:

1. Test if the DBCOMMIT= data set option can help

2. If 1. doesn't help, use a loop over the OPEN function to query if the table is ready, like this:

   data _null_;
     dsid=0;
     do i=1 to 3600 while(dsid<=0);
        ts=SLEEP(1,1);
        dsid=OPEN('TERADATA.MYTABLE','I');
     end;
     if dsid then dsid=CLOSE(dsid);
   run;