Creating a Teradata view using SAS pass through code

Reply
Occasional Contributor
Posts: 7

Creating a Teradata view using SAS pass through code

Hi

 

I am having a technical difficulty with creating a Teradata view. I am trying to create a view in one Teradata database as selected from a view in a different teradata database, and my only available tool to access both servers is Enterprise Guide!

 

I can select all the data I want from database A. I can select and write data in database B. But I want to save a view in database B, that actually points to the same view in database A, so that I can manage the changes that the administrators do to A in just my view in B, instead of in every single query against database A.

 

They have separate authentication domains, and schemas, but they are on the same server. I am able to write the sql.

 

Example SQL:

PROC SQL;

CONNECT TO TERADATA as con1

(SERVER=servername authdomain=AuthA);

execute(

REPLACE VIEW TeraB.test_view AS

LOCK TeraA.source_view FOR ACCESS

SELECT f1, f2, f3

FROM TeraA.source_view)

by con1;

execute(commit) by con1;

DISCONNECT FROM con1;

QUIT;

 

The code runs successfully, only my view does not work. I am getting a message stating I am have not been granted read access.

 

Help! Is this a super special case, wanting to access two different teradata databases at the same time?

 

Regards,

Kristine

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Creating a Teradata view using SAS pass through code

Sounds a bit crazy to me.  You have two databases, neither of which can connect to the other correct?  Then you are creating in one a view which points to the other via SAS?  If the databases cannot communicate between themselves how do you expect SAS to sort this out?  I would suggest you speak to your IT group and get the two databases communicating to each other, cut out the SAS part completely as this is nothing to do with the problem, so don't add extra layers of complexity.

Occasional Contributor
Posts: 7

Re: Creating a Teradata view using SAS pass through code

Yes, I know it sounds crazy. Problem is that I am dealing with several IT groups with several policies, meaning that those with best access and knowledge to this will not do and maintain it, another group of them will not give us tools to do it the right way (I cannot install anything anywhere near where I can access any data...) so I am basically trying everything I can possibly think of, even if it is just to prove that at least one of these groups have to cave and fix something /give us tools / some other magical solution that I haven't thought of yet.

 

It might be just that, that the two different databases won't talk properly with each other, and that this will pose an issue also in a different tool.

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Creating a Teradata view using SAS pass through code

Is there a reason why you need to communicate with both databases?  If there is a specific business need for one database to talk to another - say for entry purposes, then push it up to your management.  If there is no reason, then simply setup a data transfer agreement with each party, get the data either sent to you regularly, or extract it yourself by connecting via SAS and select * from database, then you can process both data sources internally for whatever purpose.

Esteemed Advisor
Posts: 5,198

Re: Creating a Teradata view using SAS pass through code

Either way, the problem doesn't lie in SAS. So this question should be directed to your Teradata DBA's, not this forum. And if the view should be permanent do it directly in Teradata, using SAS SQL just complicates things.
Data never sleeps
Occasional Contributor
Posts: 7

Re: Creating a Teradata view using SAS pass through code

As confirmed by this thread, yes, this was a teradata issue, and the view I had defined actually worked, when one of the DBAs turned the switch on to grant B read access to A.

 

Cool to see that SAS can do the teradata view thing Smiley Happy

Ask a Question
Discussion stats
  • 5 replies
  • 431 views
  • 3 likes
  • 3 in conversation