I started working on a new project a few weeks ago, which requires me to use Teradata, Hadoop, and Aster. That being the case, I want to use Teradata Studio instead of Teradata SQL Assistant. Connections to Hadoop and Aster were created perfectly and are working fine. When I created my connection to Teradata, it worked fine and showed me all of my tables the first day, but upon opening it the 2nd day will not show me tables that I know I am authorized to query.
Using the exact same connection properties, I can see the tables and develop queries in Teradata SQL Assistant. In Teradata Studio, I am still not able to see the tables/views. However, if I run my queries, I get valid results back. Basically, the connection is there, but I'm not able to see anything.
Here's what I've attempted so far-
I'd really appreciate anybody's help who has seen something like this before. I'm really interested in upgrading to use Teradata Studio, but it isn't as useful to me if I can't even see the table names, column names, etc.
I'm running Teradata 15.10.01.01. LDAP authentication.
Mitch, Some areas that may affect the tables list: Have you changed the Teradata Datatools Preference to use VX views to load the tables lists? Are you using the cache option on your database where the cache is stale? Are there filters created on the Tables folder in the Data Source Explorer that has filtered out the tables from the list?
One difference between Studio and SQLA is the JDBC TMODE property setting (Studio defaults to ANSI and SQLA to TERA). You can change setting but I don't think that would cause the tables not to be displayed.
let me know if any of these apply to your issue.
Thank you for your recommendations. I tried changing the Datatools Preference to be VX, but that didn't change anything. I don't have anything cached on the database either.
I was attempting to find the Filter settings, but I couldn't find them. I remember coming along them once though, and I didn't have any set. Also, I believe that my attempts where I deleted the data source and reconnected may have worked.
I also changed the TMODE setting, but that didn't change anything.
It's quite strange how the views won't show up. One thing I didn't mention before-
When I expand the database in SQL Assistant, it shows me the "Views" folder (all I have permission for in this database are views). Each of the views I can see at that point I can interact with. In Studio, directly underneath the database I'm connected to is a folder that says "Databases [ROOT=DBC]". After this, I have user profiles I can expand (not my own) and a few random tables. When I first connected and it was working, I could expand the "All" user profile and see my views, but it is empty now.
Mitch, That is probably because you changed the Views to VX. I think you should stick with using the 'V' views. VX views are more restrictive as you only see what you are authorized to see in the DSE. At this point, it sounds like you want to see everything so switch back to the 'V' Views.
That should put you back to the issue of 'not showing all authorized Tables'. If you have a Filter created on the Tables folder in the DSE, it would be annotated with [Filtered]. So if you are not seeing that then you don't have filters on the folder. And you mentioned you are not using the cache in the connection profile, so are there certain tables you are not seeing?
The tables were set to V, I changed them to VX after reading your message before, and I have since set them to be V.
I don't see the [Filtered], so I don't believe it's an issue of filtering.
All that being said, I know that it isn't showing me all of the authorized tables through my connection, because I can query tables that aren't visible. I've made several queries in SQL Assistant that also work in Teradata.
So yes, you're right. I know for a fact there are tables I am authorized to use in the teradata connection, and I can query said tables, but they will not show up at all in the Data Source Explorer.
How about if you change the TMODE setting in the JDBC properties from ANSI to TERA? I had someone with a similar issue and it turned out that the queries were using Views not Tables and changing the TMODE to TERA solved the problem.
Yeah, that was a good suggestion. I think I have figured out what is going on.. It seems like Teradata Studio is for some reason, going all the way to the root of the database I am connected to. Diving way into the file structure, I eventually found the tables I need.
So the odd thing that Teradata is doing is ignoring the "Database" I assign it in the properties and is showing me all possible options from the root. I dug in and eventually found where the tables are, in the corresponding database folder that matches what I put in the "Database" property.
Alls well that ends well! If anybody else experiences this, realize that the database property may not be showing you what you think it is.
You can right-click on the "Databases" folder and use Teradata / Set Root rather than having to navigate down the tree.
Thanks Fred! That helps out a ton.
I appreciate all of your help. At this point, my issue has been resolved. You are all great!
Teradata has the ability to nest Databases and Users within other Databases and Users. You can also turn off the 'Show Databaseas and Users in Hierarchical Display' preference if you just want to see a flat list.