Creating a volatile table in Teradata to join with a real table

[jitb]

I am trying to create a volatile table in Teradata that I would like to join with a real teradata table on a common variable. I am able to create the volatile table, as below.

libname TDWORK teradata user="&user.@LDAP" password=&pword tdpid=bmg dbmstemp=yes connection=global;
/*proc sql; drop table TDWORK.FDR_TIN_jb; quit;*/

data TDWORK.FDR_TIN_jb ;
set one_per_good_ssn(obs=100);
run;

proc sql ;
connect to teradata (connection=global user="&user.@LDAP" password=&pword tdpid=bmg);
execute (
create volatile table test as (
select * from FDR_TIN_jb
) with data on commit preserve rows
) by teradata;
quit;

However, when I try to join this volatile table with a real table, it gives me an error.

ERROR: Teradata prepare: Object 'test' does not exist.

The code I am using is:

proc sql ;
connect to teradata(user="&user.@LDAP" password=&pword tdpid=bmg database=Bmgpcust mode=teradata);
create table Cust_Mstr_Wk_Curr_Cdb as select * from connection to teradata
( Select
/* a.* ,*/
b.Cust_Tin ,
b.Cust_Num ,
b.Cust_Co_Id ,
b.Cust_Tin_Cd
from test a ,
Bmgpcust.Cust_Mstr_Wk_Curr_Cdb b
where a.SSN = b.Cust_Tin
);
quit;

Any help would be much appreciated! Thanks.

Jit

[Tom]

Don't make a NEW connection for your SQL code.  That will probably result in a new volatile table space.

Use the one you already have instead.

data TDWORK.FDR_TIN_jb ;
  set one_per_good_ssn(obs=100);
run;

proc sql ;
connect using TDWORK ;
execute by tdwork
(create volatile table test as 
  (select * from FDR_TIN_jb
  ) with data on commit preserve rows
) ;
quit;

[jitb]

Hi Tom,

I tried this and your code works to create TEST, but it's still giving me the same error when I try to join.

ERROR: Teradata prepare: Object 'test' does not exist.

[SASKiwi]

Please post your SAS log in that case. Are you doing it in the same SQL step and connection?

[jitb]

Yes, in the same session. Here is the log. 

73 /* Temp Table of SSN to join with BMG */

74 libname TDWORK teradata

SYMBOLGEN: Macro variable USER resolves to 

SYMBOLGEN: Macro variable PWORD resolves to 

74 ! user="&user.@LDAP" password=&pword tdpid=bmg dbmstemp=yes connection=global;

NOTE: Libref TDWORK was successfully assigned as follows:

Engine: TERADATA

Physical Name: bmg

75 /*proc sql; drop table TDWORK.FDR_TIN_jb; quit;*/

76

77 data TDWORK.FDR_TIN_jb ;

78 set one_per_good_ssn(obs=100);

79 run;

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

NOTE: There were 100 observations read from the data set WORK.ONE_PER_GOOD_SSN.

NOTE: The data set TDWORK.FDR_TIN_jb has 100 observations and 3 variables.

NOTE: DATA statement used (Total process time):

real time 4.12 seconds

cpu time 0.02 seconds

80

81 proc sql ;

82 connect using TDWORK ;

83 execute by TDWORK (

84 create volatile table test as (

85 select * from FDR_TIN_jb

86 ) with data on commit preserve rows

87 );

88 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.05 seconds

cpu time 0.00 seconds

89

90 /*proc print data=tdwork.test; run;*/

91

92 /* Join on SSN to get CUST_NUM */

93 proc sql ;

93 !

94 connect to

94 ! teradata(user="&user.@LDAP" password=&pword tdpid=bmg database=Bmgpcust mode=teradata);

SYMBOLGEN: Macro variable USER resolves to 

SYMBOLGEN: Macro variable PWORD resolves to 

95 create table Cust_Mstr_Wk_Curr_Cdb as select * from connection to teradata

96 ( Select

97 a.SSN ,

98 b.Cust_Tin ,

99 b.Cust_Num ,

100 b.Cust_Co_Id

101 from test a, /* Problem in syntax here */

102 Bmgpcust.Cust_Mstr_Wk_Curr_Cdb b

103 where a.SSN = b.Cust_Tin

104 );

ERROR: Teradata prepare: Object 'test' does not exist. SQL statement was: Select a.SSN , b.Cust_Tin , b.Cust_Num , b.Cust_Co_Id

from test a, Bmgpcust.Cust_Mstr_Wk_Curr_Cdb b where a.SSN = b.Cust_Tin.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

105 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.39 seconds

cpu time 0.01 seconds

[Tom]

Why do you keep making NEW connections to Teradata?

* Make connection to teradata for volatile tables ; 
libname tdwork teradata ... dbmstemp=yes ... ;

* Make volatile table using SAS code ;
data TDWORK.FDR_TIN_jb ;
   set one_per_good_ssn(obs=100);
run;

* Make volatile table using Teradata code ;
proc sql ;
connect using TDWORK as teradata;
execute by teradata 
(create volatile table test as 
 (select * from FDR_TIN_jb
 ) with data on commit preserve rows
);
quit;

* Reference volatile table and permanent table using Teradata code ;
proc sql ;
connect using TDWORK as teradata;
create table Cust_Mstr_Wk_Curr_Cdb as 
select * from connection to teradata
( Select
    a.SSN 
  , b.Cust_Tin 
  , b.Cust_Num 
  , b.Cust_Co_Id
  from test a
     , Bmgpcust.Cust_Mstr_Wk_Curr_Cdb b
  where a.SSN = b.Cust_Tin
);

[jitb]

You are right. I created a lib name for the second database connection and
referred to the temp table with a TDWORK prefix. It worked. The only
concern is that it's taking a very long time to create the temp table with
the set statement. Don't know if that is due to my particular environment,
or due to SAS.

[SASKiwi]

Have a look at the DBCOMMIT and INSERTBUFF settings for Teradata. In my experience the default settings are not optimal and usually need changing to get way better table loading performance. INSERTBUFF = 10000 is my usual starting point but it pays to experiment.

[jitb]

Thank you so much for the tip. Will try these options to fine tune and post.