Teradata SQL Assistant – List Tables Under DBC in Database Explorer

teradata

I am able to query, but under DBC, in the Database Explorer, I do not see the list of tables. That also makes it impossible for me to qualify name and get any kind of ide support while writing my queries.

Best Answer

By default, Teradata SQL Assistant will attempt to query the views DBC.Databses, DBC.Tables, DBC.Columns to populate the Database Explorer. It is possible in your environment that those objects are not accessible to developers or end users via the PUBLIC user. Instead, you may need to modify your connection settings ODBC or .Net Provider to use the X-Views in DBC. These are a collection of views which restrict the rows returned based on the privileges your user account has been granted to access or which you have created.

The ODBC DSN, .Net Provider, and JDBC drivers for Teradata have a means to use the X-Views by default to enable database tools such as SQL Assistant or Teradata Studio/Studio Express to populate the database explorer controls "transparently". Try this and see if it works.

Related Solutions

SQL MAX function

I believe Teradata supports window functions, so you can use them to pick the larges value.

select name, age
from (
   select name, age, 
          dense_rank() over (order by age desc) as rn
   from MyTable
) t
where rn = 1;

If there are multiple names with the same age, all would be shown. If you only want to pick one, use row_number() instead of dense_rank()

Using window functions is very often faster than sub-selects.

Teradata – Unable to Increase Database Size in Permanent Space

Your query and the results don't match, but you're probably right.

This will return the actual available space (modify it accordingly for TBs or 1024**3):

SELECT DatabaseName, 
   CAST(SUM(    MaxPerm                  / 1000**3) AS DEC(9,2)) AS MaxPermGB, 
   CAST(SUM(CurrentPerm                  / 1000**3) AS DEC(9,2)) AS CurrentPermGB, 
   CAST(MAX(CurrentPerm) * (HASHAMP()+1) / 1000**3  AS DEC(9,2)) AS SkewedPermGB, -- due to skewed tables
   MaxPermGB - SkewedPermGB AS AvailablePermGB -- this space can be assigned to other databases
FROM dbc.DiskSpaceV
WHERE MaxPerm > 0
GROUP BY 1
ORDER BY AvailablePermGB DESC

MODIFY DATABASE will only work if enough perm is available in the parent/immediate owner. If this database is not in the 2nd level of the hierarchy dbc is not the direct owner and then it's more complicated. Now you better use Tools -> Move Space, but this might fail if you don't have appropriate access rights.

Best Answer

Related Solutions

SQL MAX function

Teradata – Unable to Increase Database Size in Permanent Space

Related Question