topic Re: Create Volatile Table in Teradata Using ODBC in SAS Programming

Create Volatile Table in Teradata Using ODBC

sasmaverick — Wed, 15 Jun 2022 03:06:51 GMT

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,

Re: Create Volatile Table in Teradata Using ODBC

Ksharp — Wed, 15 Jun 2022 11:56:23 GMT

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/697498
https://communities.sas.com/t5/SAS-Programming/Can-SAS-code-create-an-index-on-a-volatile-table-in-Teradata/m-p/275835#U275835

Re: Create Volatile Table in Teradata Using ODBC

LinusH — Wed, 15 Jun 2022 13:30:07 GMT

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

Re: Create Volatile Table in Teradata Using ODBC

Reeza — Wed, 15 Jun 2022 15:08:20 GMT

@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?

Re: Create Volatile Table in Teradata Using ODBC

SASKiwi — Thu, 16 Jun 2022 02:30:24 GMT

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.

Re: Create Volatile Table in Teradata Using ODBC

sasmaverick — Thu, 16 Jun 2022 03:12:24 GMT

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;

Re: Create Volatile Table in Teradata Using ODBC

Tom — Thu, 16 Jun 2022 03:19:54 GMT

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.

Re: Create Volatile Table in Teradata Using ODBC

sasmaverick — Thu, 16 Jun 2022 03:50:20 GMT

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:

Re: Create Volatile Table in Teradata Using ODBC

SASKiwi — Thu, 16 Jun 2022 04:23:35 GMT

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?

Re: Create Volatile Table in Teradata Using ODBC

sasmaverick — Thu, 16 Jun 2022 04:45:11 GMT

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;

Re: Create Volatile Table in Teradata Using ODBC

Ksharp — Fri, 17 Jun 2022 12:02:00 GMT

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;

Re: Create Volatile Table in Teradata Using ODBC

Tom — Thu, 16 Jun 2022 14:29:55 GMT

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.

Re: Create Volatile Table in Teradata Using ODBC

SASKiwi — Thu, 16 Jun 2022 20:01:50 GMT

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;