Create Volatile Table in Teradata Using ODBC

[sasmaverick]

Hello,

I am trying to create volatile table in Teradata using ODBC as I do not have license to access the Teradata engine. 

Below is the Teradata SQL query I am using in SAS:

CREATE VOLATILE TABLE base_acct_extract
(
account_id varchar(30)
)
PRIMARY INDEX(account_id)
ON COMMIT PRESERVE ROWS;


INSERT INTO base_acct_extract VALUES ('ABC1200');

 Can I even use the ODBC engine to create a volatile table? If so, how do I create and access the table?

@Reeza @Ksharp 

Thanks,

NT

[Ksharp]

Sorry. I can't help you. Maybe @LinusH @SASKiwi could give you a hand.

https://communities.sas.com/t5/SAS-Programming/Access-Volatile-Teradata-Table/m-p/293573
https://communities.sas.com/t5/SAS-Programming/How-to-load-data-to-volatile-table-in-teradata/m-p/69...
https://communities.sas.com/t5/SAS-Programming/Can-SAS-code-create-an-index-on-a-volatile-table-in-T...

[LinusH]

SAS/ACCESS to ODBC has support for temporay tables:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n0irpkyp22l7vzn1il9lx6f4wmx9.htm

I think the exmple  @Ksharp posted should work, but replace teradata with odbc as engine.

https://communities.sas.com/t5/SAS-Programming/Access-Volatile-Teradata-Table/m-p/293573

[Reeza]

---

@sasmaverick wrote:

Hello,

I am trying to create volatile table in Teradata using ODBC as I do not have license to access the Teradata engine. 

---

You do require permissions and access on the Teradata server to be able to do this. When you say you do not have a license to access the Teradata engine, does that mean you don't have the SAS license for a Teradata ODBC driver?

[SASKiwi]

If you are using SAS/ACCESS to ODBC, something like this should work:

libname teradata odbc noprompt = "<Put your Teradata connection string here>";

proc sql;
 connect using teradata;
 execute(
 CREATE VOLATILE TABLE base_acct_extract
(
account_id varchar(30)
)
PRIMARY INDEX(account_id)
ON COMMIT PRESERVE ROWS;

INSERT INTO base_acct_extract VALUES ('ABC1200');
)
  ;
quit;

 Edit: As long as you don't clear the LIBNAME, the Teradata database connection should remain active and the volatile table remain available. That's my best guess anyway as I've never used Teradata but I assume they work similarly to temporary tables in other RDBMSs.

[sasmaverick]

I think I was able to create the volatile table using the below syntax. Thanks to @SASKiwi for the inspiration.  However, I am not sure how to access this table in SAS now?

/*create volatile table*/
proc sql;
 CONNECT TO ODBC AS TDP5 (DSN="TDP5DBC" UID="&NT_UID." PWD="&NT_PWD." );
 execute(
 create volatile table temp as (
 select top 10 * from teralib.customers
 )
 with data primary index (Cust_ID)
 on commit preserve rows
 ) by TDP5;
quit;

[Tom]

You need to use one connection that stays open.  Otherwise Teradata will erase the temporary tables when the connection closes.

Make a LIBREF and then use that for everything.

libname td odbc .... connection details ... ;
proc sql;
connect using td;
execute by td 
( create volatile table .... )
;
quit;

Make sure to include the DBMSTEMP=yes option.

[sasmaverick]

I am using the below code:

/*create volatile table*/

libname td odbc dsn = "TDP5DBC" uid = &my_uid. pwd = &my_pwd.  
    connection=global dbmstemp=yes;

proc sql;
 connect using td;
 execute by td(create volatile table temp as (
 select top 10 * from teralib.customers
 )
 with data primary index (cust_id)
 on commit preserve rows);
quit;

However, still nothing seems to be created in TD:

[SASKiwi]

Did you refresh the TD library after running the CREATE TABLE? Do you have access to a Teradata tool to independently check if the table gets created or not?

[sasmaverick]

Yes I did refresh the TD library in SAS. I have Teradata SQL Assistant. When I use the below code in that, it's able to create the volatile table

create volatile table temp as (
 select top 10 cust_id from teralib.customers
 )
 with data primary index (cust_id)
 on commit preserve rows

However,  when I run the same code below in SAS, I don't see the table. All I am looking for is the colatile table to be brought into a SAS library.

proc sql;
 CONNECT TO ODBC AS TDP5 (DSN="TDP5DBC" UID="&SGB_UID." PWD="&SGB_PWD." connection=global);
 execute(
 create volatile table temp as ( select top 10 cust_id from teralib.customers ) with data primary index (cust_id) on commit preserve rows
 ) by TDP5;
 
quit;

[SASKiwi]

How about creating the volatile table using SAS then checking if it exists with Teradata SQL Assistant? I'm able to do this with SQL Server using MS SQL Server Management Studio so I'm thinking it should work in Teradata SQL Assistant. Also try PROC CONTENTS in SAS:

proc contents data=TD._all_;
run;

[Ksharp]

Try add SCHEMA= option in LIBNAME:
libname td odbc dsn = "TDP5DBC" uid = &my_uid. pwd = &my_pwd.
connection=global dbmstemp=yes SCHEMA=xxxxxxx ;


If you don't know SCHEMA, you could check it by :

proc sql;
connect to odbc.....
select * from connection to odbc(ODBC::SQLTables);
quit;

[Tom]

And how did you check whether the table was there?

Did you include the photograph because you thought the GUI would show the volatile tables.  I don't think that is a reasonable thing to expect.  To test if the table is there query it.  Query with pass through Teradata code.  Try to query it via your SAS libref.