What query will work on any Teradata server? Testing basic connectivity

So you logon to a Teradata server and need to run a basic check to confirm that everything is working.  What might you use?  Well, you most-likely have access to a selection of view in the DBC database, so here’s a query you should be able to run to test connectivity:

SELECT    * 
FROM    dbc.dbcinfo;

When run you should see a few rows of basic info about the system, such as DBMS version.

seldbcinfo

On the off-chance that doesn’t work, the most basic query that will work for any user is a SELECT without referencing a table, such as:

SELECT 'Hello World';
Posted in Uncategorized | Leave a comment

See which user can access all tables and views in a database

Teradata Administrator gives you some very handy features.  One I find very useful is the “Rights” function when you right-click on a table or view, which will show you all users that can access a single table or view.  Or you can right-click on a Database and use “Right on DB/User” to list all users that have access to the Database.

But, what if you want to know who can access all tables and views in a Database individually?

I’ve taken the SQL from the “Rights” function and modified it to list all access rights on individual objects in the database, so this will show you what access has been granted individually to all tables, views, macros, and procedures within that database specified as “DatabaseName=’database'”:

SELECT *
FROM
(
SELECT UserName AS Grantee,TableName,ColumnName,AccessRight,GrantAuthority,
GrantorName,AllnessFlag,DatabaseName
FROM dbc.AllRightsV
UNION
SELECT RoleName AS Grantee,TableName,ColumnName,AccessRight,'R',
GrantorName,
'',DatabaseName
FROM dbc.AllRoleRightsV
) DbRights
--Modify the 'database' value to the name of the database you wish to view
WHERE DatabaseName = 'database'
-- AND TableName='TableName'
-- AND Grantee='Username"
ORDER BY Grantee, TableName

Note you can modify this further (note the commented-out lines) for different purposes, such as by Table or User.

If you want to see the SQL executed by functions within Teradata Administrator when you execute any function, click Window –> SQL History, or press F12.

Created from Teradata Administrator 13.10.0.2

 

 

Posted in dba, Teradata, Teradata Developer | Leave a comment

Aborting all sessions for a given user

SELECT AbortSessions (1, 'USERNAME', 0, 'N', 'N');

The above would abort all sessions for user “USERNAME”.  The third value is the “SessionNoIn” field, where you can either specify a specific session number, or specify 0 for all sessions that match the other conditions.

The function is defined as follows:

Parameter Name Type Comment Nullable Format Max Length Decimal Total Digits Decimal Fractional Digits Table/View? Char Type Parameter Type UDT Name
1 HostIdIn I2 _?_ Y -(5)9 2 _?_ _?_ F _?_ I _?_
2 UserNameIn CV _?_ Y X(128) 128 _?_ _?_ F 1 I _?_
3 SessionNoIn I _?_ Y -(10)9 4 _?_ _?_ F _?_ I _?_
4 LogoffSessions CV _?_ Y X(1) 1 _?_ _?_ F 1 I _?_
5 UserOverride CV _?_ Y X(1) 1 _?_ _?_ F 1 I _?_
6 RETURN0 I _?_ Y -(10)9 4 _?_ _?_ F _?_ O _?_

NB: For the uninitiated, “RETURNo” is the return number field, not an input field.  As such only 5 parameters are specified when calling the function.

The details for each field are as follows:

  • HostIdIn – The host ID (normally 1, as it’s only relevant to Z/OS clients)
  • UserNameIn – The username of the user that executed the session.
  • SessionNoIn – The session number of the session (or 0 if you want to kill all sessions for that user)
  • LogoffSessions – (This needs to be confirmed) Logoff session after abort (rather than leaving it idle)
  • UserOverride – …as below…

Indicator of whether to override an ABORT SESSION failure:
• Y = Override the ABORT SESSION request to fail in any of the following cases:
• An identified session is being session-switched.
• An identified session is executing its own ABORT SESSION request.
• An identified session has a PEState of IDLE: IN-DOUBT as a result of a 2PC.
Note: Sessions are marked IN-DOUBT by the 2PC protocol, which governs how transactions are committed by multiple systems that do not share memory. The protocol guarantees that either all systems commit or all roll back.
• N or NULL = Do not override.

The AbortSessions function returns the number of sessions aborted as the resultset.

Instructions created for Teradata 14.10

References:

http://forums.teradata.com/forum/database/parameter-values-in-syslib-abortsessions – viewed 2015-06-12

Posted in Uncategorized | Leave a comment

SQLJ permissions for Teradata Java UDF (JUDF) Compilation and Creation

If you receive the following error message, you need some permissions on your SQLJ database in your Teradata RDBMS:

“User does not have permission to access SQLJ on the current database”

Image

This simply means that the user that is creating the JUDF needs “EXECUTE PROCEDURE” access on the “SQLJ” database within Teradata.  A statement such as the following will fix this for you:

GRANT EXECUTE PROCEDURE ON SQLJ TO <username>;

By default, only the DBC user has permission to do this, so as good practice you should first use DBC to grant permission to your DBC/super-user account, rather than having to use DBC every time:

GRANT ALL ON SQLJ TO <super-user> WITH GRANT OPTION;

References:

http://developer.teradata.com/tools/articles/creating-a-table-java-user-defined-function-using-the-teradata-plug-in-for-eclipse

http://developer.teradata.com/extensibility/articles/hadoop-dfs-to-teradata

This information was based on a Teradata 14.0 system.

Posted in Uncategorized | Tagged , , , , , | Leave a comment

Monitoring Rollback Sessions from Viewpoint

If you abort a query and want to see tha status of the table roll-back, you can monitor the status/progress of the roll-back from Viewpoint.

Viewpoint has a Remote Console portlet available for admins to access a restricted version of the console.

You can access the Recovery Manager (the equivalent of the rcvmanager utility) from within the Remote Console portlet.

From within the Recovery Manager you can issue the following command to see the status of tables being rolled-back:

 LIST ROLLBACK TABLES;

This is an example of the output (albeit with nothing currently being rolled-back):

LIST ROLLBACK TABLES;
TABLES BEING ROLLED BACK AT 15:50:47 13/06/27

ONLINE USER ROLLBACK TABLE LIST

Host  Session   User ID     Performance Group               AMP W/Count
----  --------  ---------   ------------------------------  -----------

TJ Rows Left   TJ Rows Done   Time Est.
-------------  -------------  ---------

Table ID   Name
---------  ------------------------------------------------------------------

SYSTEM RECOVERY ROLLBACK TABLE LIST

Host  Session   TJ Row Count
----  --------  -------------

Table ID   Name
---------  ------------------------------------------------------------------

Enter command, "QUIT;" or "HELP;" :
This information was derived from Teradata 14.0 and Teradata Viewpoint 14.10
Posted in Uncategorized | Leave a comment

A simple macro to find a username based on part of a user’s full name

This macro will retrieve a person’s username, based on a provided text string (wildcards included for the LIKE operator).  This assumes that you have your user’s full name stored in the user comment string.

CREATE MACRO getUserID
(
Name VARCHAR(255)
)
AS (
   SELECT DatabaseName AS Username
   ,CommentString AS Name
   FROM DBC.Databases
   WHERE Name LIKE :Name
   ;
   );

Example:

exec getuserid ('%smith%');

Written for Teradata 13.10

Posted in Teradata | Tagged , , , , | Leave a comment

A macro to identify a user by username

This is a short macro I wrote to find out who a user is by returning the comment string for the given user (as it is common for the username to be a unique identifier, while the user’s real name is stored in the comment string).  I find myself doing this a lot when receiving user revoke requests from the service desk.

CREATE MACRO whois
(
Username VARCHAR(255)
)
AS (
   SELECT DatabaseName AS Username
   ,CommentString AS Name
   FROM DBC.Databases
   WHERE DatabaseName = :Username
   ;
   );
exec whois ('username');

Written for Teradata 13.10

Posted in Teradata, Uncategorized | Tagged , , , , | Leave a comment