05-19-2016 05:18 AM
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.
CONNECT TO TERADATA as con1
REPLACE VIEW TeraB.test_view AS
LOCK TeraA.source_view FOR ACCESS
SELECT f1, f2, f3
execute(commit) by con1;
DISCONNECT FROM con1;
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?
05-19-2016 05:42 AM
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.
05-19-2016 06:57 AM
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.
05-19-2016 07:58 AM
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.
05-19-2016 02:01 PM
05-20-2016 01:42 AM
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