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?

I have had a similar problem with Sybase and have not found any other solution than waiting/doing some other stuff before accessing the database. — Ville Koskinen, Jul 15 '09 at 11:58
if you select from the target table right after the end of fastload you may be getting the "data is still being loaded" response. So as Mr.Koskinen suggested, loop with a wait, until a single row is returned.. — access_granted, Oct 10 '18 at 01:06

score 0 · Answer 1 · answered Jul 15 '09 at 12:05

0

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

answered Jul 15 '09 at 12:05

Jon

4,957
5
27
38

The problem is, queries don't error. So I have no condition to loop on. – AFHood Jul 18 '09 at 12:21

score 0 · Answer 2 · answered Jul 15 '09 at 12:16

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.

score 0 · Accepted Answer · answered Jul 17 '09 at 09:41

I would try the following:

Test if the DBCOMMIT= data set option can help

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;

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

3 Answers3