Archives of the TeradataForum

Message Posted: Thu, 30 May 2002 @ 13:26:08 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I asked our local SAS expert about Tony's question. Following is a reply from her contact at SAS.

-------------------------------------------
To: Doug Sedlak
Subject: SAS/ACCESS to Teradata and SELECT CreateTimeStamp from DBC.Columnsx

Hi Diane and Tony,

Thanks for the heads up, Diane. If you could forward this along to Geoffrey, we'd appreciate it. And if based on the information here, one of you can investigate, confirm, and followup to the listserv, that would be great.

We attempt to provide maximum functionality for all SAS/ACCESS to Teradata users with minimal overhead. Below explains why we use this functionality and why the cost is small.

On connecting to a Teradata server, SAS prepares the following SQL statement:

SELECT CreateTimeStamp from DBC.Columnsx

The key is that we only prepare the statement. Specifically, before calling DBCHCL with the DBFIRQ request type, in the DBCAREA, we set the Request Processing Option field to 'P' and Change Options to 'Y'. (See CLIv2 doc for details.)

A prepare returns information about the request, not the data itself (from Teradata CLIv2 doc: "... send back a time estimate and format information about the data that it would return if the request were executed ...").

Therefore, no result set of 30,000 (or whatever) rows should be created. Essentially, this prepare call should be quite inexpensive on the DBMS, since it returns only a nominal amount of "metadata" and not actual data rows.

The reason we prepare this statement is to determine if the server supports the TIME and TIMESTAMP datatypes (we still have customers running V2R2, and where Teradata does not support TIME and TIMESTAMP, we handle SAS times and datetimes differently, meaning we need to know up front the capabilities of the Teradata server in this respect).

If you turn on our extended tracing, you will see that we attempt to show that this is a prepare and not an execute:

1? options sastrace= ",,d,d" no$stsuffix;
2? libname x sasiotra user=sasdxs pass=******;

Entering dbiconi.
... lottsa stuff...
sasiotra/trcon(): prepare SELECT CreateTimeStamp from DBC.Columnsx
more stuff...
NOTE: Libref X was successfully assigned as follows:
     Engine:        TERADATA
     Physical Name:

As a further FYI, SAS submits statements in prepare mode elsewhere as well. For example, when accessing a table you will often see SELECT * from TABLE as the first statement in our trace. This is a prepare to see if the table exists. Here's an example trace statement:

Prepare SQL(trprep): SELECT * FROM "xx"

Doug

	<Prev	Next>		<<First	<Prev	Next>	Last>>