BookmarkSubscribeRSS Feed
KristineNavesta
Fluorite | Level 6

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

KristineNavesta
Fluorite | Level 6

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

LinusH
Tourmaline | Level 20
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
KristineNavesta
Fluorite | Level 6

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 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2030 views
  • 3 likes
  • 3 in conversation