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.

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';

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

 

 

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

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”

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.

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

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

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

Modified DBC.Users view to display all users

I have modified the existing DBC.Users view to display details of all users, rather than just of the user executing the query (which is how DBC.Users works).  This is handy to keep in your own database, or a shared DBA database, because it has more informtion that DBC.Databases.

REPLACE VIEW Users
AS
SELECT CAST(TRANSLATE(dbase.DatabaseName USING UNICODE_TO_LOCALE WITH ERROR)
            AS CHAR(30)) (NAMED UserName),
       CAST(TRANSLATE(dbase.CreatorName USING UNICODE_TO_LOCALE WITH ERROR)
            AS CHAR(30)) (NAMED CreatorName),
       /* DR101935-jw180009-01-> */
       CAST(dbase.PasswordModTime AS DATE) AS PasswordLastModDate,
       CAST(dbase.PasswordModTime AS TIME(0)) AS PasswordLastModTime,
       /* <-DR101935-jw180009-01 */
       CAST(TRANSLATE(dbase.OwnerName USING UNICODE_TO_LOCALE WITH ERROR)
            AS CHAR(30)) (NAMED OwnerName),
       dbase.PermSpace(FORMAT '---,---,---,---,--9'),
       COALESCE(PF.SpoolSpace, dbase.SpoolSpace)(FORMAT '---,---,---,---,--9')
       (NAMED SpoolSpace),
       COALESCE(PF.TempSpace, dbase.TempSpace)(FORMAT '---,---,---,---,--9')
       (NAMED TempSpace),
       dbase.ProtectionType,
       dbase.JournalFlag,
       dbase.StartupString,
       CAST(TRANSLATE(COALESCE(PF.DefaultAccount, dbase.AccountName) USING
                      UNICODE_TO_LOCALE WITH ERROR)
            AS CHAR(30)) (NAMED DefaultAccount),
       CAST(TRANSLATE(COALESCE(PF.DefaultDataBase, dbase.DefaultDataBase)
                      USING UNICODE_TO_LOCALE WITH ERROR)
            AS CHAR(30)) (NAMED DefaultDataBase),
       dbase.CommentString,
       dbase.DefaultCollation,
       /*+-----------------------------------------------------+
         | PasswordChgDate converted from Julian to YY/MM/DD   |
         +-----------------------------------------------------+ */
       CASE WHEN DBASE.PasswordChgDate < 0
       THEN NULL
       ELSE
       (((100 * ((4 * NULLIFZERO(DBASE.PasswordChgDate) - 1) / 146097)
       + (4 * (((4 * DBASE.PasswordChgDate - 1) MOD 146097) / 4)
       + 3) / 1461 - 1900) + ((5 * (((4 * (((4 * DBASE.PasswordChgDate
       - 1) MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2)
       / 12) * 10000 + (((5 * (((4 * (((4 * DBASE.PasswordChgDate - 1)
       MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2) MOD 12
       + 1) * 100 + ((5 * (((4 * (((4 * DBASE.PasswordChgDate - 1) MOD
       146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) MOD 153 +5) / 5
       (DATE, FORMAT 'yy/mm/dd'))
       END AS PasswordChgDate,
       /*+------------------------------------------------+
         | LockedDate converted from Julian to YY/MM/DD   |
         +------------------------------------------------+ */
       ((100 * ((4 * DBASE.LockedDate - 1) / 146097)
       + (4 * (((4 * DBASE.LockedDate - 1) MOD 146097) / 4)
       + 3) / 1461 - 1900) + ((5 * (((4 * (((4 * DBASE.LockedDate
       - 1) MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2)
       / 12) * 10000 + (((5 * (((4 * (((4 * DBASE.LockedDate - 1)
       MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2) MOD 12
       + 1) * 100 + ((5 * (((4 * (((4 * DBASE.LockedDate - 1) MOD
       146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) MOD 153 +5) / 5
       (DATE, FORMAT 'yy/mm/dd', NAMED LockedDate),
       /*+------------------------------------------------+
         | Lockedtime converted from minutes to HH:MM     |
         +------------------------------------------------+*/
       (Dbase.LockedTime / 60 ) * 100 +(Dbase.LockedTime MOD 60)
       (INTEGER, FORMAT '99:99', NAMED LockedTime),
       dbase.LockedCount,
       dbase.TimeZoneHour,
       dbase.TimeZoneMinute,
       dbase.DefaultDateForm,
       dbase.CreateTimeStamp,
       CAST(TRANSLATE(DB2.DatabaseName USING UNICODE_TO_LOCALE WITH ERROR)
            AS CHAR(30)) (NAMED LastAlterName),
       dbase.LastAlterTimeStamp,
       dbase.DefaultCharType,
       CAST(TRANSLATE(dbase.RoleName USING UNICODE_TO_LOCALE WITH ERROR)
            AS CHAR(30)) (NAMED RoleName),
       CAST(TRANSLATE(dbase.ProfileName USING UNICODE_TO_LOCALE WITH ERROR)
            AS CHAR(30)) (NAMED ProfileName),
       dbase.AccessCount,
       dbase.LastAccessTimeStamp
FROM DBC.dbase
         LEFT OUTER JOIN DBC.Profiles PF
                      ON DBC.Dbase.ProfileName = PF.ProfileNameI
         LEFT OUTER JOIN DBC.Dbase DB2
                      ON DBC.dbase.LastAlterUID = DB2.DatabaseID
WHERE  dbase.DatabaseId IN
        (/* IDs of users controlled by this USER */
SELECT dbase.DatabaseId
        FROM DBC.dbase
        WHERE  dbase.RowType = 'U' 
--        AND
--             (/* I AM the user */
--                   (dbase.DataBaseNameI = USER))
--UNION  ALL
--SELECT dbase.DatabaseId
--        FROM DBC.dbase, DBC.owners
--        WHERE  dbase.RowType = 'U' 
--        AND
--              /* I own the user */
--                   (dbase.DatabaseId = owners.owneeid
--                    AND owners.ownerid = (SEL DISTINCT
--                     databaseid FROM DBC.dbase WHERE
--                     databasenamei=USER))
--UNION ALL
--SELECT dbase.DatabaseId
--        FROM DBC.dbase, DBC.accessrights, DBC.userdbV
--        WHERE    dbase.RowType = 'U'
--        AND      (
--                  /* I have explicit modify rights on user */
--                   (dbase.DatabaseId = accessrights.DatabaseId
--                   AND (accessrights.accessright = 'DD'
--                   OR   accessrights.accessright = 'DU')
--                   AND accessrights.UserId = userdbV.id
--                   AND userdbV.name=USER))
       ) WITH CHECK OPTION;

Written for Teradata 13.10

Cloning users from one system to another

Here is a quick macro I threw together that creates the CREATE USER, COMMENT, and GRANT (role) statements for a given set of users, specified by the “UserString” parameter.  It can also replace environment specific parameters in the names and comments, for example where the username contained the string “PROD” but you wanted to replace it with”TEST” to clone users from one environment to the other.

NB: A pre-requisite to this is to create your own version of the DBC.Users view (which defaults to selecting only the details of your own user).

Modified DBC.Users view to display all users

NB: This macro creates the scripts to clone users which must then be executed manually.  It will not automatically clone users.

There is a “Clone User” utility in “Teradata Administrator” but it will only clone a single user.

This was written for my own purpose, so is not as versatile or elegant as it could be.  It’s a starting-point for anyone wanting to write their own.

CREATE MACRO CloneUsers
    (ExistingUser VARCHAR(257)        --The exact user ID string of the existing user (include string quotes)
    ,NewUser VARCHAR(257)        --The exact user ID string of the new user (include string quotes)
    ,ReplaceThisString VARCHAR(257) --The string in the fields to find (enter a blank string if nothing to replace)
    ,WithThisString VARCHAR(257)    --and the string in the fields to replace it with (enter a blank string if nothing to replace)
    ) 
    AS (
        SEL 'CREATE USER ' ||
            CAST(TRIM(:NewUser) AS VARCHAR(255)) ||
            ' FROM "' ||
            CAST(TRIM(u.OwnerName) AS VARCHAR(255)) ||
            '" AS PERM = 0 ' ||
            'PASSWORD = ";;;;initpass;;;;" ' ||
            'ACCOUNT = (''' ||
            TRIM(db.AccountName) ||
            ''') ' ||
            'STARTUP = '''' ' ||
            'NO FALLBACK ' ||
            'NO BEFORE JOURNAL ' ||
            'NO AFTER JOURNAL ' ||
            'PROFILE="' ||
            TRIM(u.ProfileName) ||
            '" ' ||
            'DEFAULT ROLE = ALL; ' ||
            'COMMENT ON ' ||
            CAST(TRIM(:NewUser) AS VARCHAR(255)) ||
            ' IS ''' ||
            :NewUser ||
            ', FirstName LastName, firstname.lastname@domain.com' ||
            '''; '    
        FROM myuser.users u
        INNER JOIN DBC.Databases db
        ON u.UserName = db.DatabaseName
        WHERE DatabaseName = :ExistingUser;
        
        SEL 'GRANT ' ||
            CAST(OREPLACE(TRIM(RoleName),:ReplaceThisString,:WithThisString) AS VARCHAR(255)) ||
            ' TO ' ||
            CAST(TRIM(:NewUser) AS VARCHAR(255)) ||
            ';'
        FROM DBC.RoleMembers
        WHERE Grantee = :ExistingUser
        ;
    )
;

--EXAMPLE EXECUTION STRING
exec CloneUsers ('ExistingUserID','NewUserID','Prod','Test');

Written for Teradata 13.10.